mysql access problem

[Lou Hevly]

At 08:02 AM 9/7/2009 -0500, Tony Aberenthy wrote:
>obuch at localhost         grant ALL on obuch.* to obuch at localhost 
>identified
>by 'xxxxxxx';
>obuchfot at localhost      mysql -u obuchfot -p
>?? the user name

No, sorry, that should have been obuch; I miscopied.


>See what you get from:
>SELECT Host, User, Password, Select_priv FROM mysql.user;
>SELECT Host, Db, User, Select_priv FROM mysql.db;


SELECT Host, User, Password, Select_priv FROM mysql.user;
...
localhost       | obuch    | 130a5f060ae5aee8 | N
localhost       | obuch    | 0ec6a87e091493c8 | N

mysql> SELECT Host, Db, User, Select_priv FROM mysql.db;
...
localhost       | obuch   | obuch   | Y
localhost       | obuch   | obuch   | Y

This seems to be it.  The "obuch" user (and database) are listed twice, 
and the user has 2 different passwords.

Now I've run:
/usr/local/bin/mysql_upgrade
and everything goes fine until it gets to here:

Running 'mysql_fix_privilege_tables'...
ERROR 1062 (23000) at line 94: Duplicate entry 'localhost-simi' for key 
1
ERROR 1062 (23000) at line 163: Duplicate entry 'localhost-simi' for 
key 1
ERROR 1062 (23000) at line 202: Duplicate entry 'localhost-simi' for 
key 1
ERROR 1062 (23000) at line 207: Duplicate entry 'localhost-simi' for 
key 1
FATAL ERROR: Upgrade failed

And it's true that there is also a duplicate entry for 'simi'.

When I ran /usr/local/bin/mysql_fix_privilege_tables --password=xxxxxx
no error showed.

So I tried to drop the user obuch:

mysql> drop user obuch at localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> drop user obuch at localhost;
Query OK, 0 rows affected (0.00 sec)

It says 0 rows affected, which is the case, since both obuch entries 
are still in the mysql.user table.  In fact, I can't drop any users at 
all using that syntax.

I suppose this is something pretty basic, but I haven't been able to 
Google anything useful.  I can't seem to drop users.

Thanks for your help.

Lou


> > -----Original Message-----
> > From: openbsd-newbies-bounces at sfobug.org
> > [mailto:openbsd-newbies-bounces at sfobug.org] On Behalf Of Lou Hevly
> > Sent: Monday, September 07, 2009 5:40 AM
> > To: openbsd-newbies at sfobug.org
> > Subject: mysql access problem
> >
> > Hello:
> >
> > I'm running MYSQL mysql-server-5.0.51a on 4.3.
> >
> > One of my users called this morning and said he could no
> > longer connect to his database.  It was working fine last
> > Tuesday and he claims to have made no changes.  The error is
> > "Access denied".  So I tried regranting his privileges (the
> > obuch database exists at
> > /var/mysql/obuch):
> >
> > **************************************************************
> > $ mysql -u root -p
> > Enter password: *******
> > Welcome to the MySQL monitor.  Commands end with ; or \g.
> > Your MySQL connection id is 516738
> > Server version: 5.0.51a-log OpenBSD port: mysql-server-5.0.51a
> >
> > Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> >
> > mysql> grant ALL on obuch.* to obuch at localhost identified by
> > 'xxxxxxx';
> > Query OK, 0 rows affected (0.00 sec)
> >
> > mysql> flush privileges;
> > Query OK, 0 rows affected, 1 warning (0.00 sec)
> >
> > mysql> quit
> > Bye
> >
> > $ mysql -u obuchfot -p
> > Enter password: xxxxxxx
> > ERROR 1045 (28000): Access denied for user 'obuchfot'@'localhost'
> > (using password: YES)
> > ******************************************************************
> >
> > --
> > All the best (Adéu-siau),
> > Lou Hevly
> > xyz at visca.com
> > http://visca.com
> >
> > _______________________________________________
> > Openbsd-newbies mailing list
> > Openbsd-newbies at sfobug.org
> > http://mailman.theapt.org/listinfo/openbsd-newbies

-- 
All the best (Adéu-siau),
Lou Hevly
xyz at visca.com
http://visca.com