Friday, June 30, 2006

MySql 5: Error No. 1045 Access denied for user 'root'@'localhost' (using password: NO)

I have been driven crazy by this issue, trying to get a MySQL 5.0.21 database created from the wizard in WinXP.

The error message was this:
#1045 - Access denied for user 'root'@'localhost' (using password: NO)

It also talks about making sure port 3306 can be opened. In one of my installations, I was using Norton Firewall and I did have to open up port 3306, but I also had this error on a machine without a software firewall and that was not the issue.

The core problem seems to be that even though you supply a root password, it does not get applied. Does this happen for everyone? I don't know. But the "using password: NO" for me suggests this is what was happening.

Solution #1

The solution in that case is to ignore the error about applying security details, log in with an empty string root password, and change the password. MySql bug#6891 tracks this for version 4.1.7. The workaround given there is this (credit Freek Bos):

So when you start the MySQL command line client.
You simply press ENTER when asked for a password.

Now you can change the password by running the following line.
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

Solution #2

I didn't try this route. The solution that worked for me was

1. Uninstall MySQL
2. Erase the MySQL installation directory, as the old database files are left around.
3. Install MySQL.
4. Choose "Standard Configuration" rather than Detailed.

Somebody had suggested (sorry, I've lost the link now) that there was a problem when using the "Detailed" configuration to create your database initially. They said to choose "Standard" initially to create it and then go back and reconfigure it as desired with the Server Instance Config Wizard. I wonder if the Detailed configuration wizard path has the problem of not applying the given password? If I get a chance I'll try these two options in a clean system and update this post if I confirm anything.

Some people run the Server Instance Config Wizard after getting this error once and find that it now can no longer start the service. This happens if the service was actually started the first time. But don't go this route! Re-running the wizard won't solve the password problem. Follow one of the two steps given here instead.

If this was helpful to you, or you have a correction, leave a comment and let me know! It looks like this is a very common error.


lehee said...

thank you ! it worked well...

Anonymous said...

Than Q So much saved my time .From last three days I am struggling with the same error.

Thanks alot

SWAN said...

Thanks a lot to you.
Even though My error was a bit different, the soulution 1 suggested by you worked well for me.
I did not try the Solution 2.
For my case the "Using password" portion of the error was YES.

I am running MYSql version "5.1.32-community" on Windows Vista Home Basic along with Service Pack 1.

Windows Firewall is default on Vista and thus it is necessary to have the 3306 TCP port included in the exclusions list.

Further the service does not start the first time due to the problem of permissions on Vista. Thus, it may be necessary to run the configuration wizard the second time. It may be needed to allow the program across the virus scanners also. I had Norton Online Virus scanner on my machine and I had to disable it to make the service start in the beginning.

Thnaks anyway.

Anonymous said...

Thanks a lot sol1 really worked

Anonymous said...

this was really helpful, i had been searching for a solution for about 2 days, thanks a lot

Visakh said...

thanks first solution worked for me

Anonymous said...

thanks 1st solution worked for me

Ramratan Gupta said...

Thanks a lot....

Joseph said...

I'm having a little problem myself and was wondering if you could help me out as these solutions didn't work for me. thank you

xainjeff said...

#2 worked for me. this error has been driving me nuts for hours. thanks!
what happened is i used detailed installation then i uninstalled without deleting the directory folder, then reinstalled. :)