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.