Ars Informatica
December 15, 2017
Home
Health Care Informatics
Web-based MySQL/PHP Databasing
Programming
Web Development
Favourite Software
Hardware for the Frugal Fanatic
Graphic Design and Image Processing
Free Scripts and Software
About Us
Contact Us
Links
 
 

 Article Feed for this site

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.

Convinced? Committed?

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

;extension=php_mysql.dll

to

extension=php_mysql.dll

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:

  • Click on Start, Settings, Control Panel, System
  • Select Advanced
  • Select Environment Variables
  • Find PATH under System Variables, and double-click on it
  • Add the directory containing libmysql.dll to the end of the PATH entry, i.e.
    C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\PHP;
    
    

Now, retest using phpinfo(). If both mysql and mysqlnd are enabled, on to Part IV: Installing MySQL with new password authentication.


Copyright © 2017 Ars Informatica. All Rights Reserved.