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

Warning: mysql_connect() [function.mysql-connect]: Premature end of data (mysqlnd_wireprotocol.c:554) in path/to/the/file/where/connection/script/is/written/

Warning: mysql_connect() [function.mysql-connect]: OK packet 1 bytes shorter than expected in path/to/the/file/where/connection/script/is/written/

Warning: mysql_connect() [function.mysql-connect]: 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 in path/to/the/file/where/connection/script/is/written/

As you will see, the core issue here is that MySQL can have passwords with hashes stored in the old 16-character format, which is not supported by PHP 5.3′s new mysqlnd library. Since I couldn’t find a good solution with a quick Google, here is how I solved this without having to downgrade PHP or MySQL (as some of the solutions suggested):

  1. Change MySQL to NOT to use old_passwords

It seems that even MySQL 5.x versions still default to the old password hashes. You need to change this in “my.cnf” (e.g. /etc/my.cnf): remove or comment out the line that says

old_passwords = 1 
//old_passwords = 1为16位
//SET old_passwords = 0;

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. You can test this by running:

mysql> SELECT Length(PASSWORD('xyz')); 


enter image description here

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

  1. Change the format of all the passwords in the database to the new format

Connect to the database, and run the following query:

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

This will show you which passwords are in the old format, ex: 如图:

enter image description here

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';  


enter image description here

Finally, flush privileges:



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


enter image description here

成功后退出exit 最后重新启动一下mysql

service mysqld restart