I recently upgraded to PHP 5.3 on Windows, and ran into this problem:


Warning: mysql_connect(): OK packet 6 bytes shorter than expected in ...
Warning: mysql_connect(): mysqlnd cannot connect to MySQL 4.1+ using old authentication in ...

I run my Linux servers on VMWare when I do development, so the MySQL database itself was from quite a while ago. 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

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:

 SELECT Length(PASSWORD('xyz'));
+-------------------------+
| Length(PASSWORD('xyz')) |
+-------------------------+
|                      16 |
+-------------------------+
1 row in set (0.00 sec)

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

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

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, ex:

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

Comments

mark: That was a big help - we had some trouble with this same issue today while migrating to a new server. It was not clear why the install worked on the old system but not a very similar new system until we found your post.

Mahbub: Hey Mixu, That was really a relief from the stupid error. I recently upgraded XAMPP which is 1.7.4 (beta) and php5.3.3. It killed my 1.5 hrs yesterday.

For windows users, please edit [xampp installation dir]mysqlbinmy.ini to set the value old_passwords = 1

Khupcom: Oh my God... I've been 2 days stuck with this problem and the solution only : UPDATE mysql.user SET Password = PASSWORD('mynewpass') WHERE user = 'myusername';

FLUSH PRIVILEGES;

I just want to say you're my hero

Really big thanx

Alex: I did everything you said, and I still get the same error... Something's wrong lol

Sailesh: Hey! Mixu.... Thanks man! I took 2 days to find this solution. It helped me a lot. I've added your post onto my blog.

Thanks again! :)

neverstopdreaming: Thanks!

Luis: How can I change this settings if I'm working in a remote server with their own control panel and phpMyAdmin. Can I change the setting of MySQL in the remote site?

Adam: Allelujah. My evening is saved. Thanks!

Pedro: Thank you

Ebow: Your a life saver