Resolving "[function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication" errors, Part Three: Configuring PHP 5.3 for MySQL
August 13, 2009
Note: this is Part III of the article 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.
As discussed in Part II, downgrading to a previous version of PHP may allow you to use simpler fixes to this problem.
I do not recommend this approach.
Newer versions of PHP are typically more secure, more robust. They add functionality that you may well wish to use at a future date.
If you use a hosted server, I would never assume that its administrator will stick with older versions as newer become available: admins are appropriately obsessed with security. Clients want the latest and greatest. You should assume that PHP will be upgraded, frequently. And PHP will never revert to the old password authentication protocols.
Do not downgrade, unless, nothing - nothing - else works.
Here we go ...
Configuring PHP 5.3 for MySQL
Step one: back up your stuff!
Too easy, and yet, we forget to do so all too often. If you haven't already, back up your MySQL databases to a secure location. Create a complete backup, in SQL format.
Next, copy your old MySQL and PHP directories to some archive or backup directory. You may never need the actual data files; you may never need to check how something was configured in the PHP.INI or MY.INI files - but if you do, and these have been overwritten by a new install, you're out of luck.
Step two: install PHP 5.3
The basic PHP install process is, of course, not so basic. Are you installing on Unix? linux? Windows? OS X? What server system are you using? Apache? Sun? Microsoft IIS? or any of a half-dozen others?
This part is - fortunately - extremely well described in the PHP documentation, available from www.php.net. I cannot cover the entire process in one short article, and it would be inappropriate to suggest otherwise. Fortunately, the documentation is excellent, and there are many articles out there describing this part of the process.
Step three: enable MySQL in PHP
With PHP 5.x, MySQL is no longer enabled by default. It's not even bundled with PHP. Something about too many versions of the client library ending up on the system, if it's reinstalled with each PHP install, and a license issue: new versions of the MySQL library fall under the GPL license, which is incompatible with PHP's licensing model.
The simplest way to check if PHP is enabled is to use the phpinfo() function, i.e.
<?php phpinfo(); ?>
Scroll down to see whether MySQL (both mysql and mysqlnd) support is enabled. If they are, great - on to Part IV: Installing MySQL with new password authentication. If they aren't, read on.
For a linux system, you'll need to download the library from MySQL.com, and configure PHP with the --with-mysql[=DIR] option. Check the PHP and MySQL documentation for specifics on how to do this.
On Windows servers, the php_mysql.dll DLL must be enabled in the PHP config file, php.ini. This means uncommenting the extension=php_mysql.dll line by removing the preceding semi-colon, i.e. from
Next, PHP needs to be able to find this file to be able to load it. You'll need to set the extension_dir directive to the directory where your PHP extensions are located, i.e.
extension_dir = "C:/PHP/ext"
(Optional?) Step Four: Reference the MySQL client library
PHP also claims to need the MySQL client library: a file named libmysql.dll. (I've found it unnecessary, but can't vouch it would always work without, so best to go with their assertions.)
According to the PHP documentation, this file is part of the Windows PHP distribution. It wasn't in my full install distro! This may be for the same reasons that it's no longer bundled with PHP in the linux version: license issues, clutter issues, keeping it up-to-date issues. Or maybe, it's no longer needed.
Either way, libmysql.dll is easy enough to find - it's part of the MySQL distro. Find it in the "bin" (for binaries, i.e. compiled code libraries) subdirectory.
Finally, for PHP to interact with MySQL this libmysql.dll file needs to be accessible to Windows, i.e. on the system's PATH. (Personally, I copy it to my PHP directory, and reference this directory in the system PATH, but you could keep it in your MySQL install directory and reference that instead.) The steps:
Now, retest using phpinfo(). If both mysql and mysqlnd are enabled, on to Part IV: Installing MySQL with new password authentication.