Resolving "[function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication" errors, Part Four: Installing MySQL and restoring data, with new password authentication
August 13, 2009
Note: this is Part IV 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.
This continues from Part III: Configuring PHP 5.3 for MySQL. If you haven't already checked that PHP has been properly configured for MySQL, please do so before proceeding.
Please note that 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. For security reasons, passwords are logged using one-way, irreversible encryption, and cannot be 'recovered'.
Why re-install MySQL?
Unfortunately, as of PHP version 5.3, the new PHP mysql driver is no longer compatible with the old password format. If you're getting the "OK packet 6 bytes shorter than expected" and "[function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication" errors - your passwords are in the old format.
If your passwords are in the old format - only a fresh install will create a new user table with passwords in the new format.
You could, possibly, keep your old MySQL install so long as you used PHP version 5.2 or older. I cover the rationale for not doing so in Part I of this article, and the possible solutions in this situation, in Part II.
In this section, we walk you through the steps needed to create a new MySQL database with the new password structure, and restoring all the other old data.
Installing MySQL with the new password authentication protocols
Step One: Back up your MySQL databases
Before doing anything as drastic as re-installing MySQL, you'll need to make sure your data is safe. Back it up.
While I like the command line as much as the next person, this is where I recommend you download the free MySQL Administrator program from www.mysql.com. It's a very clean program that gives you easy access to server info, startup variables, database health, database connections. It allows you to stop and restart the service with a single click. Its user administration component is excellent - adding, updating, and deleting users, assigning privileges across databases, couldn't be easier. And its backup and restore tools are equally good.
Select Backup, then New Project. Add all your databases, including the mysql database. Select Complete Backup under Advanced Options, and set Backup Type to SQL. Select any other options you might need. Finally, click on Execute Backup Now.
Save it to a directory of your choice. Personally, I keep a directory called backups for this purpose, and consider it holy. Untouchable. I will add new files to this directory, but never overwrite or delete them.
Never compromise your data.
Step Two: Re-install MySQL
You heard right.
Again, only a new, clean install of MySQL version 4.1 or above will create a new user table with passwords in the new format.
If you're using a hosted server, I would suggest that you upgrade your Development and Testing servers to the same version of MySQL installed on the hosted server. If you manage all of your own servers, I would use the most recent, stable release, but that's up to you. However, you'll need at least version 4.1 to use the new password format.
As mentioned before, make sure that you back up your data, first!
The reinstall and reconfiguration process is beyond the scope of this article.
Please, please, please make sure that you read the MySQL documentation before you start. Follow their instructions.
Step Three: Re-Import your data from the .SQL backup file
First, a word of warning. DO NOT just import the whole backup file. If you re-import the 'mysql' database, you will be re-importing the old, no-longer-supported mysql database structure!
The mysql database itself contains the configuration data for your MySQL installation. When you re-installed MySQL, you got a clean copy of this database with the default structure required for the new format. Don't over-write it.
I recommended www.mysql.com's MySQL Administrator program above, and do so again. It makes the import process a snap.
Click on Restore, then Open Backup File. Select your file. Do not select Start Restore, yet.
Click on the Restore Content tab, near the top.
Select Analyze Backup File Content. You should see all of the backed-up databases selected by default.
Unselect the 'mysql' database. (See above!)
Select Start Restore.
Presto. Data should import beautifully, minus your user information - the next step.
Step Four: User Administration
Again, in MySQL Administrator, click on User Administration in the top left pane. A new pane appears below it, labeled 'User Accounts'.
Right-click in this area to Add a new user, Clone an existing user, or Delete the selected user. Click on Add User, and a 'New User' appears in the User Information pane. Rename this user to whatever you like, and assign a password. Click on Apply Changes to proceed.
Next, click on the Schema Privileges tab. Select each database the user should have access to, then select and add the privileges you wish to grant the user. Easy.
(Unless, of course, you have hundreds of users. Then this process must be automated - but that's beyond the scope of this article. You'll also need to have the original passwords to be able to re-enter them, or will need to reassign passwords at this stage.)
And a final note on this - if you went through this process and cannot re-enter or reassign your passwords, do not despair. Re-import the .SQL backup file, but this time with the 'mysql' database, as in Step Three. Old database structure with old passwords and old password format reappear. As does the mysql-connect error. You'll need to use an earlier version of PHP to get around it.
Step Five: Test
Go back to the page that created the error. Reload.