Description:
The encrypted password generated by mysqladmin is not the same as that generated using the PASSWORD() function in the MySQL Monitor.
In particular, using mysqladmin to change the root password effectively locks out the root user, as reported here:
http://forums.mysql.com/read.php?10,4872,4872#msg-4872
I've seen this problem reported elsewhere in the user forums.
I have found this to be true with 4.1.7 on Windows 2000, I'm not sure about other versions/platforms.
Note: MySQL was installed to a new installation of Windows using the Windows Essentials installer.
How to repeat:
1. Do this:
shell> mysqladmin -u [user] -p[password] password '[newpassword]'
or
shell> mysqladmin -u [user] -p[password] reload password '[newpassword]'
2. Now try this:
shell> mysql -u[user] -p[newpassword]
The result is an error 1045 (access denied), even after stopping and restarting the server.
Here is how I tested to determine that mysqladmin password and mysql PASSWORD() produce different results. Starting point was logged in as root using a different password which I then set to 'test'. What follows (except for comments) is a copy/paste from the CL:
mysql> UPDATE user SET Password=PASSWORD('test') WHERE User='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
C:\>mysql -u root -ptest
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.7-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE mysql;
Database changed
mysql> SELECT Password FROM user WHERE User='root' AND Host='localhost';
+-------------------------------------------+
| Password |
+-------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> \q
Bye
C:\>mysqladmin -u root -ptest flush-privileges password 'test'
C:\>mysql -u root -ptest
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
/* Stopped service, restarted with --skip-grant-tables */
C:\>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.7-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE mysql;
Database changed
mysql> SELECT Password FROM user WHERE User='root' AND Host='localhost';
+-------------------------------------------+
| Password |
+-------------------------------------------+
| *C6F86BF4B76027CBC9FCF0A54C0114CE9022ECFE |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> UPDATE user SET Password=PASSWORD('test') WHERE User='root' AND Host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT Password FROM user WHERE User='root' AND Host='localhost';
+-------------------------------------------+
| Password |
+-------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
/* Stopped and restarted service with grants re-enabled */
C:\>mysql -u root -ptest
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.7-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> \q
Bye
/* end test procedure */
---
I repeated this procedure three times, with exactly the same results (and password hashes) produced each time.
Suggested fix:
Make sure that mysqladmin uses the same password-hashing routine as the PASSWORD() function and GRANT ... IDENTIFIED BY ... statement do in the MySQL Monitor.
