Why does MySQL_connect() fail with \'OK packet 6 bytes shorter than expected\' when connecting to a remote MySQL 5.0.22 database from PHP 5.3.0?

MySQL PHP Incompatibility: Understanding the Error

The issue described in the question arises when attempting to connect to a remote MySQL database from a local WAMP installation running PHP 5.3.0. While connecting to a database running MySQL 5.0.45 is successful, connecting to a database with version 5.0.22 results in the following error:

Warning: mysql_connect() [function.mysql-connect]: OK packet 6 bytes shorter than expected. PID=5880 in ...
Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication in ...

Explanation

The root cause of this incompatibility stems from the password length of the MySQL account used for connecting. Typically, MySQL accounts created with older versions used 16-character passwords. However, newer versions of PHP, such as PHP 5.3.0, no longer support authenticating with these old passwords.

Solution

To resolve the issue, the password for the affected MySQL account needs to be reset using a command like:

SET PASSWORD FOR 'username'@'hostmask' = PASSWORD('thepassword')

Additionally, it is advisable to check if the server is configured to use or create old passwords by default.

Diagnostic Query

To further diagnose the issue, the following query can be run on the problematic MySQL server to determine the password length:

SELECT
  Length(`Password`),
  Substring(`Password`, 1, 1)
FROM
  `mysql`.`user`
WHERE
  `user`='username'

Replacing 'username' with the actual username being used for database connection will provide information on the password length and the first character of the password, which can help confirm if it is an old-style password.

The above is the detailed content of Why does MySQL_connect() fail with \'OK packet 6 bytes shorter than expected\' when connecting to a remote MySQL 5.0.22 database from PHP 5.3.0?. For more information, please follow other related articles on the PHP Chinese website!