mysqlnd cannot connect to MySQL 4.1+ using old authentication - Apache, Nginx, Lighttpd and other web server software

Php 5.3 introduces a cool new alternative to the old libmysql library: mysqlnd (mysql native driver).

How to compile php with this driver or more details about mysqlnd are not scope of this post, but authentication method is.

By default, Mysql is using old_passwords method of storing passwords in 16 char length. Mysqlnd usses a new authentication engine which stores passwords in 41 character length.

To check how mysql currently stores passwords:

Code:

mysql> show variables where Variable_name like '%old_pas%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SELECT Host, User, Password FROM mysql.user WHERE LENGTH(Password) > 16;
0 rows in set (0.00 sec)

In the above case, mysql is using old_passwords engine and there is no user with password length longer than 16 characters.

For this, edit your my.cnf file (depends on your installation where this is) and go to [mysqld] section and add following line:

Code:

old_passwords = 0

Restart mysql (if it's production system, maybe you should wait untill night) and check the variable again:

Code:

mysql> show variables where Variable_name like '%old_pas%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Now that mysql stores password in new format, all php website passwords need to be updated (not changed, just updated). This a sample on how to do it for root (in your case, probably, no php web client will use root):

Code:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('old-password');

And to check the outcome:

Code:

mysql> SELECT Host, User, Password FROM mysql.user WHERE LENGTH(Password) > 16;
+-----------+--------------+-------------------------------------------+
| Host      | User         | Password                                  |
+-----------+--------------+-------------------------------------------+
| localhost | root         | *TY893FY733447B3341A392F69Z9A9DE72988F363 |

As you can see, after updating the password, there is one username with the length of the password field larger than 16 characters. This must be done for all php web clients.

Ivorde Unix/Linux/Database/Web/Mail Forum

This forum is dedicated to Apache, Nginx, Lighttpd, Squid and other HTTP transport protocol related software