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

• 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

I had a issue where the old passwords had been enable by the server by default, so a simple SET PASSWORD FOR 'some-user'@'%' = PASSWORD ('XXXX'); wouldn't work(for reason due to old software and legacy which I won't go into....)

Solution :

SET old_passwords = 0;
SET PASSWORD FOR 'some-user'@'%' = PASSWORD ('XXXX');
FLUSH PRIVILEGES;

Details :

Doing this as the logged in user

SET Password = PASSWORD('password')

Simply didn't work

Eg testable here

SHOW CREATE TABLE `mysql`.`user`;

The password wouldn't shift to from

| HOST | USER | PASS |

OLD PASSWORD

| % | some-user | 7fa559aa33d844b4 |

WHAT I WANTED, EG NEW PASSWORD

| % | some-user | *CF04AECA892176E6C0C8206F965C03374D12F93E |

So I looked up the variables for old passwords

SHOW VARIABLES;

...
old_passwords = ON
...

So basically I had to set the mysql server var first to old_password=OFF, eg this worked for me

SET old_passwords = 0;
SET PASSWORD FOR 'some-user'@'%' = PASSWORD ('XXXX');
FLUSH PRIVILEGES;