mysqlnd cannot connect to MySQL 4.1+ using old authentication

Enter Your Query:

Use '%' for wildcards and quotes for "exact phrases"

mysqlnd cannot connect to MySQL 4.1+ using old authentication

Last Modified: Dec 11, 2014, 10:21 pm

Related 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.

If php is throwing you this error, you've likely got an older system with:

old_passwords=1

set in your /etc/my.cnf file. The "old_passwords" is used tell MySQL to use the old password hash format, that was used back in the days of MySQL 4.1. It's not recommended anymore.

However, if you set:

old_passwords=0

and restart mysqld, you might still not be able to login to mysql via your php script because mysqlnd doesn't support the older method.

The recommended solution is to reset *all* of your mysql password, such that they use the newer format (after disabling old_passwords=1), but this is not likely going an option if you've got hundreds of clients, as you won't know their password (plaintext value required for the reset).

The simpler solution is to recompile php so that it does not use mysqlnd.
To do this with CustomBuild 2.0, (example: php 5.3):
1) type:

cd /usr/local/directadmin/custombuild
mkdir -p custom/ap2
cp -p configure/ap2/configure.php53 custom/ap2/configure.php53

Edit the custom/ap2/configure.php53 and remove the lines:

--with-mysql=mysqlnd \
--with-mysql-sock=/var/lib/mysql/mysql.sock \
--with-mysqli=mysqlnd \
--with-pdo-mysql=mysqlnd \

and replace them with:

--with-mysql=/usr \
--with-mysqli=/usr/bin/mysql_config \
--with-pdo-mysql=/usr \

2) Confirm the custom configure.php53 is being used:

./build used_configs

and recompile php:

./build php n

NOTE Although this will allow scripts to login to mysql, where the old password has exists for this account, any script that uses the mysql PASSWORD() function may need to either replace said function with OLD_PASSWORD(), OR update their passwords to the new format.

Either way, all accounts are recommended to be reset.
If you view the mysql.user table as da_admin or root, run the query:

SELECT user, length(password) as using_old_password FROM mysql.`user` WHERE length(password) = 16;

to view who has not reset their password. 16 byte password hashes are the old format, and 41 bytes are the new format. Once all accounts are using the 41 character length, then you can go back to using mysqlnd.