Database Connection Failed Mysqlnd Cannot Connect To MySQL

Many people who are upgrading their databases, and PHP version of late will have come across an error such as this

Database connection fialed: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password').This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file.

Or, if using PDO, like good programmers should, the error is prefixed with the error

Could not connect to database. SQLSTATE[HY000] [2000] mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file.

Why is this happening to me!?

The error occurs for a multiplicity of reasons, which conspire to give this error. PHP version 5.3 and above, now uses the mysqlnd driver by default, and as such uses the newer password hashing algorithm. A quick check will show the issue quite clearly.

SELECT @@global.old_passwords, @@session.old_passwords, Length(PASSWORD('bogus'));

The above query will show

1 1 16

As you can see, the password lengths are 16, and the new password hashing for MySQL versions 4.1 and greater uses a 41 character hash. The PHP mysqlnd extension requires a 41 character password hash. Oh the humanity!

To see a list of password which are in the new and old formats, use this command

SELECT user, Length(Password) FROM mysql.user;

The results will look something like this..

+----------+--------------------+
| user     | Length(`Password`) |
+----------+--------------------+
| root     |                 41 |
| root     |                 16 |
| kevin    |                 16 |
| billy    |                 16 |
+----------+--------------------+

OK, great, how do I fix it?

Some folks have reported varying result using various methods. Here is what worked for me.

Firstly, open up your my.conf file and comment out the following line

old_passwords=1

Restart MySQL. this will ensure MySQL never uses the old passwords again.

This is one way to update the passwords in the user table. Be sure to flush the priviges after.

UPDATE mysql.user SET Password = PASSWORD('my_password') WHERE user = 'my_db_username';
FLUSH PRIVILEGES;

Another fix is to things this way.

SET SESSION old_passwords=0;
SET PASSWORD=PASSWORD('my_password');

Now you should be able to connect with php and the mysqlnd driver