php - Error: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

Question

I am getting the following error:

Database connection failed: 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

I using PHP 5.3.8 and MySQL 5.5.16 on my local machine and my host (media temple) is running PHP 5.3 MySQL 5.0.51a. The version on the live server is older than the one on my machine.

How do I fix this error and connect to MySQL from my local machine?

I know there are similar posts to this one but I have tried them all and none are working.

Answer 1

• Remove or comment old_passwords = 1 in my.cnf

Restart MySQL. If you don’t, MySQL will keep using the old password format, which will mean that you cannot upgrade the passwords using the builtin PASSWORD() hashing function.

The old password hashes are 16 characters, the new ones are 41 characters.

• Connect to the database, and run the following query:

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

This will show you which passwords are in the old format, e.g.:

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

Notice here that each user can have multiple rows (one for each different host specification).

To update the password for each user, run the following:

UPDATE mysql.user SET Password = PASSWORD('password') WHERE user = 'username';

Finally, flush privileges:

FLUSH PRIVILEGES;

Source: How to fix "mysqlnd cannot connect to MySQL 4.1+ using old authentication" on PHP5.3