How to fix: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

After upgrading a PHP server from PHP 5.3.3 to PHP 5.6.x, working with MySQL Server 5.1.x on a plain CentOS 6.x 64 bits box, there were a few new web apps were showing this mysqlnd connection error: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

This was the complete and exact error:

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

This happens because the new mysqlnd php client, refuses to work with old style passwords from MySQL. So, even if you have old_passwords=1, this won’t work for this new web apps, who require stronger password hash security.

How can I fix error mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication ?

This error can be fixed easily if you follow the next steps:

Edit main MYSQL configuration file:

nano -w /etc/my.cnf

Change:

old_passwords=1

to

old_passwords=0

Apply the changes:

service mysqld restart

Connect to MySQL console as root:

mysql -u root -p

Now reset the user password to use the new hash:

SET PASSWORD FOR 'mysqluser'@'localhost'=PASSWORD('passwordhere');
flush privileges;

Replace ‘mysqluser’ and ‘passwordhere’ with your real username and password.

Now go back to the /etc/my.cnf and revert the changes made previously, set:

old_password=1

Restart MySQL to apply changes:

service mysqld restart

All done, now your user should be using the stronger MySQL password hash.
This can be verified from the MySQL console using this command:

mysql> SELECT user, Length(Password) FROM mysql.user;
+------------------+------------------+
| user | Length(Password) |
+------------------+------------------+
| root | 16 |
| forum_user | 16 |
| user2 | 41 |
| lorex | 41 |
| m3user | 16 |

If you see ’41’, then that user is using the new hash, and ’16’ means it is using the old hash.

All done, at this point your error mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication should be fixed and you should be able to work normally with your web applications.