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;
Configure target Mysql server to allow old insecure auth.
http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_old-passwords
Simply in the my.cnf file on target Mysqld server comment out the old_passwords.
Maybe there is way to obtain PHP build (or build it yourself) which uses compatible (old) auth mode.