Resolving the "OK packet 6 bytes shorter than expected" and "[function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication" errors in MySQL/PHP
August 13, 2009
This one gave me a great deal of grief. On-line help was useless. Hence, this article.
You have a functional MySQL/PHP install, whether self-administered or on a hosted server. Then, for whatever reason, you upgraded.
Or worse, your server administrator upgraded PHP versions without advising you, and without a full awareness of MySQL database connection issues. But when you checked your site - or some unwary visitor - you suddenly realize that things ain't working.
Instead of your usual data, you get the following cryptic messages:
Warning: mysql_connect() [function.mysql-connect]: OK packet 6 bytes shorter
than expected in index.php on line 29
Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to
MySQL 4.1+ using old authentication in index.php on line 29
Warning: mysql_select_db() expects parameter 2 to be resource, boolean given
in index.php on line 30
Warning: mysql_query() [function.mysql-query]: Access denied for user
'SYSTEM'@'localhost' (using password: NO) in index.php on line 33
Warning: mysql_query() [function.mysql-query]: A link to the server could not
be established in index.php on line 33
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given
in index.php on line 34
mysqlnd cannot connect to MySQL 4.1+ using old authentication
The clues are there, of course. "mysqlnd cannot connect to MySQL 4.1+ using old authentication" - pretty obvious, isn't it?
Except that the error message is very misleading. The problem is usually PHP.
Usually, you'll run into this set of error messages when upgrading to a newer version of PHP. It's the new PHP driver that can no longer connect to older databases. Even if your MySQL install consists of the latest release downloaded today, if it uses the older password authentication protocols, PHP won't use them.
Alternately, a new MySQL install may also trigger this issue, if PHP 5.x has already been installed. MySQL versions of 4.1 or later will use the new protocols, of course. However, if you're importing your old databases into the new install, you could inadvertently over-write the new user tables with the old ones. Old password authentical procedure - and PHP breaks.
There a several ways of fixing this problem, and these depend largely on the version of PHP you're running.
Regardless of the actual solution, please back up all of your data before continuing.
None of the solutions are for the faint of heart.
Those unfamiliar with PHP/MySQL installation and configuration are strongly advised to find folks with expertise in these areas rather than trying to solve these problems themselves.
Analyzing the problem
This one's simple - which versions of PHP and MySQL are you running, and how are they configured? The simplest way to check if PHP is enabled is to use the phpinfo() function, i.e.
<?php phpinfo(); ?>
Note the PHP version. Scroll down. Note the loaded configuration file (PHP.ini) location. Note whether MySQL (both mysql and mysqlnd) support is enabled, and which versions are used.
Your results should look something like:
PHP version: 5.3.0
Loaded configuration file: C:\PHP\php.ini
MySQL support: enabled
MySQL Client API version: mysqlnd 5.0.5-dev - 081106 - $Revision: 22.214.171.124 $
mysqlnd version: mysqlnd 5.0.5-dev - 081106 - $Revision: 126.96.36.199 $
Resolving the Problem: Use a safe process
Back up your data to a SQL file, and store in a secure location.
If you run multiple environments, I'd strongly urge that PHP and MySQL versions be the same, and configured identically across them. Further - any developer should have at least a Development system independent of the Production server, with possibly a third independent Test environment.
If your Production or live environment is a hosted server, you may have little choice in the versions of PHP/MySQL available to you, though your administrator may well upgrade versions at your request.
Resolve any problems in your Development and Test environments before promoting them to a live server.
Resolving the Problem: the fix depends on your specific problem
The Pre-PHP 5.3 System
Solutions are simpler, potentially, for systems still using PHP versions before 5.2. You should consider them temporary, however, as you or your server administrator will want to upgrade at some time in the future.
Though it's more work, one option is to upgrade to the latest PHP and MySQL versions and update your passwords to the new protocols. See Parts III and IV of this article: Configuring PHP 5.3 for MySQL and Installing MySQL with new password authentication.
To proceed with 'fixing' a pre-version 5.3 PHP installation, proceed to Part II: Fixing MySQL Password Authentication on PHP before version 5.3.
Again, please make sure that you've backed up your data, and that you've tested your process before inflicting it on a live server.
PHP version 5.3 and beyond
Downgrading to a previous version of PHP may allow you to use 'simpler' fixes - for a time. I would not recommend this, ever. Newer versions of PHP are more secure and more robust, and add functionality that you may well need at a later date.
If you use a hosted server: never assume that its administrator will stick with older versions as newer become available. You should assume that PHP will be upgraded, frequently. And it will never revert to the old password protocols.
Unfortunately, when you upgrade password formats you'll need to re-enter them. You'll either need the original passwords or reassign passwords at this stage - there's just no way to recover a password from the encrypted version in the database. For security reasons, it's one-way, irreversible encryption only, folks.
If you don't already have PHP 5.3 installed, or you want to make sure it's configured correctly for MySQL, on to Part III: Configuring PHP 5.3 for MySQL.
If you're happy with your PHP install - phpinfo() reports that mysql and mysqlnd are installed properly, then on to Part IV: Installing MySQL with new password authentication.