Resolving "[function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication" errors, Part Two: Fixing MySQL Password Authentication on PHP before version 5.3
August 13, 2009
Note: this is Part II 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.
In versions of PHP before 5.3, you have the option of keeping your older-style passwords, in an old-style user table. The new password protocols are better and more secure, but - you can't pull the old passwords from a database. Passwords are hashed, a one-way encryption. You can't convert old passwords to new, or old user tables to new.
Note that you should bite the bullet and go with the newer protocols if you need maximum security. Do not go with the instructions below, unless "pretty good" security suffices.
The main reason for not upgrading: if you're upgrading formats, you either need to re-enter the old assigned passwords (if you know them, if you have them still, if your users haven't changed their passwords), or re-assign new passwords. Depending on the number of users involved, either could be painful.
Now, if you're still inclined to keep your old user table and "force" the old password format.
Note again: this does not work with PHP version 5.3 and above.
Forcing the MySQL server to use the older password algorithms
You will need to start the MySQL server (the mysqld service) with the --old-passwords option. There are several ways to do this:
Option 1. From the command line
From a command line interface, in a console window or DOS window, enter something like:
C:\Users\Admin>"C:\Program Files\MySQL Server 5.0\bin\mysqld" --old-passwords
and with whatever other command line parameters you require.
Option 2. With MySQL as a Windows service to start automatically
Using the MySQL Server Instance Config Wizard, under setting the Windows options, check "Install as a Windows service."
Next, using the MySQL Administrator program, under the Security tab, select "Use old passwords". This is a great program, part of the free MySQL GUI Tools available from mysql.com.
Alternately - and this is easier, but again requires the command line - you can install the service from the command line:
C:\Users\Admin>"C:\Program Files\MySQL Server 5.0\bin\mysqld" --install
which will cause this service to load the next time Windows boots up.
Unfortunately, this prevents you from installing the service with a default config file specified. For some reason, it just won't work if you specify both.
Finally, if you've already got the MySQL service installed, you can edit my.ini to configure MySQL to use old passwords when restarted, by setting variable old_password to 1 in the [mysqld] section in this file, i.e.:
Option 3. Configuring MySQL with old_passwords under linux
As above, setting variable old_password, this time in file my.cnf, will instruct MySQL to use the old password format, i.e.
The linux MySQL RPM installation and use of the (recommended) mysqld_safe startup script are beyond the scope of this article, but are well described in the documentation available at mysql.com.
If you're confident that no passwords have been logged to the database in the new format - you're done.
If not, your final step is to Find and fix incompatible passwords.