mysql access problem

[soc at visca.com]

At 12:16 PM 9/7/2009 -0500, Tony Abernethy wrote:
>Try:
>DELETE FROM mysql.user WHERE User='obuch';
>DELETE FROM mysql.db WHERE User='obuch';
>REPAIR TABLE mysql.user, mysql.db;
>FLUSH PRIVILEGES;
>
>Which should maybe get both entries in both tables.
>
>After all the entries are deleted, do the
>GRANT ALL ON obuch.* to ...

That did it!

Thanks so much.

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 10:49 AM
> > To: tony at servasoftware.com; openbsd-newbies at sfobug.org
> > Subject: RE: mysql access problem
> >
> > 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
> >
> > _______________________________________________
> > Openbsd-newbies mailing list
> > Openbsd-newbies at sfobug.org
> > http://mailman.theapt.org/listinfo/openbsd-newbies
> >
>
>_______________________________________________
>Openbsd-newbies mailing list
>Openbsd-newbies at sfobug.org
>http://mailman.theapt.org/listinfo/openbsd-newbies

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