Bug #30104 Root can't connect (Access denied for user 'root'@'localhost')
Submitted: 27 Jul 2007 17:45 Modified: 8 Aug 2007 8:53
Reporter: very frustrated Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version: 5.0.41 OS:Linux
Assigned to: CPU Architecture:Any
Tags: ERROR 1045 (28000)

[27 Jul 2007 17:45] very frustrated
Description:
The administrator allows you to change the connection network for root to '%' - supposedly meaning root can connect from anywhere. However, right after the change, the system refuses to allow root to log in from anywhere using any method and constantly comes back with:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Please no editorials on this. I already know this is a bad idea for production but this is purely a development environment for now - and currently an extremely furstrating one because of the above.

How to repeat:
Go to SQL-Administrator
Add "%" to root's network
Remove the local host and linux from root's network

Suggested fix:
Either allow root to sign in using "%" - just like any other account, or DO NOT allow the user to add "%" to root's network.
[27 Jul 2007 18:03] very frustrated
Correcting server version
[30 Jul 2007 6:03] Valeriy Kravchuk
Thank you for a problem report. Are you connectin as root locally (I think so)? if yes, please, try to connect to host 127.0.0.1 and provide port number explicitely. Inform about the results.
[30 Jul 2007 15:21] very frustrated
Thanks for the commnet. Root can't connect either remotely or locally, with the same error message. I have tried localhost, 127.0.0.1, the local IP, as well as all the entries from the hosts file. I also tried to remove the mysql directory to force it into the initial state (as some web postings suggested) and that failed also. I have since re-installed from scratch and while that meant I lost some work, it had become clear to me that I wouldn't be able to get access. It appears to me that there is a bug in the code segment that matches "%" to the network address.

Thanks again.
[30 Jul 2007 19:14] Valeriy Kravchuk
If you do suspect this is a bug in server or MySQL Administrator, please, try to describe all the exact steps I have to perform to repeat it.
[30 Jul 2007 19:20] very frustrated
Thanks for your post. In my opinion this is absolutely a bug and a severe problem since it forces re-insall of the server which causes a lot of recovery headaches and, as was in our case, data loss since the previous backup. I don't know if it is an administrator bug or a server bug. In my opinion the server should not allow this to happen no matter what a/the GUI does.

The steps to duplicate the problem were in my original post.

Thanks again.
[30 Jul 2007 21:17] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behaviour in the way you indicated in the initial comment and current version of MySQL Administrator. Please indicate version of MySQL Administrator you use.
[30 Jul 2007 21:18] Sveta Smirnova
Also please indicate version of operating system you are running MySQL Administrator on.
[31 Jul 2007 3:39] very frustrated
Thanks for your post. The results are the same on Windows XP (MySQL admin 1.2.11) and Linux (MySQL admin 1.2.12). As for Linux Version

uname -a
Linux GoldenRetriever 2.6.11-1.1369_FC4smp #1 SMP Thu Jun 2 23:08:39 EDT 2005 i686 i686 i386 GNU/Linux

I have re-created the problem (several times) using the same steps I had posted. I am not sure what more I can tell you.

Thanks.
[31 Jul 2007 6:34] Sveta Smirnova
Thank you for the feedback.

> I have tried localhost, 127.0.0.1, the local IP, as well as all the entries from
the hosts file.

Do you mean you tried mysql --host=127.0.0.1/mysql --host=localIP or did you really connect from differnt machines?
[31 Jul 2007 13:24] very frustrated
Both. The machine where MySQL is installed is on a local area network. I tried to connect on the server machine itself by running Mysql-administrator AS WELL AS running the mysql command from Linux prompt. Neither worked and both gave the same error message. I used localhost, the machine's assigned IP address, 127.0.0.1, the machine's name (as reported by hostname), and localhost.localadmin as hostname (in -h or in the GUI). All reported the same 1045 error messasge.

I also tried to connect from another machine on the network using both Mysql administrator and Mysql query browser and got the same error message.
[7 Aug 2007 10:31] Sveta Smirnova
thank you for the feedback.

Please start server with option --skip-grant-tables, connect ot it and provide output of SELECT user, host from mysql.user. Then stop server and restart it with options --log-error --log-warnings, try to connect as root and provide us error log. See also http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html
[7 Aug 2007 15:32] very frustrated
Thanks for your post.

I am sorry but I can not provide you with what you requested. We re-installed the server. While we did lose some data after re-install, we could not wait for a potential fix any longer.
[8 Aug 2007 8:53] Sveta Smirnova
Thank you for the feedback.

I'll set status of the report to "Can't repeat". If you meet same situation in feature feel free to reopen the report.
[22 Apr 2010 8:08] mariusz mariusz
Hello

Was this bug fixed? I am having the same issue and server reinstall is not exactly what I would like to perform to get rid of this.
[3 May 2011 14:26] Bradley Ward
I had a similar issue ( MySQL Server 5.1) and could not access the db with the root account.

Luckily I had another account I could use to access it, its looks as the root password became corrupted somehow, well I assume corruption as I do not normally see an asterisk in the encrypted password...

eg: *B2112437C22397C11E06CBBF57596DA04DB29E03

So used:

UPDATE mysql.user SET Password = OLD_PASSWORD('password')
WHERE User = 'root';

FLUSH PRIVILEGES;

You could also changed the host from '%' to 'localhost' if thats a concern.

I stopped the service and restarted and everything was fine.

Possibly if you have no other account to access it with, copy the mysql.user table over to another machine running the same version of the server, plop it in to a temporary database and reset the password as above. Then re-copy it back over to the original (ensuring you have stopped the MySQL service first). 

Re-start it and you should hopefully be able to log in again as root.

Hope this helps someone.
[3 May 2011 14:52] Bradley Ward
The above is also a serious flaw in MySQL's security :P

If that is of concern put your/configure your MySQL data folder to be in your windows profile area, ie My Documents and configure Windows to use encryption for your personal data, this can be configured in control panel -> user accounts.