Recently I ran into a situation when upgrading from MySQL 4.x to 5.x on a dev box of mine. I actually ran into this a year or two ago too, but I forgot to document it (I know, I know) and so I didn’t remember it. I thought I would post it here now to share with all of you in case you run across it yourself. Once the upgrade was done, the application that connected to the database would connect no longer. I kept getting authentication errors no matter what I did. This was a fresh install, I just imported MySQL dump files for my databases, so I thought it was a setting or something that I had forgotten. I checked everything out and could find nothing wrong with the settings, user accounts, passwords, you name it. The app I was launching ran on Windows (specifically, this is my EQEmu server, check it out daBoneyard), and it’s a console app so the Dos window flies by and I hadn’t seen the error message. Finally I decided to put a pause in the batch file and see what the heck was going on. It was then that I was presented with the following error:

Error no. 1251: "Client does not support authentication protocol
requested byserver - consider upgrading MySQL client"

Aha! That’s different. I couldn’t imagine upgrading the MySQL client. First off, it’s part of the EQEmu server build so there wasn’t much I could do with it anyway, plus from what I have read and experienced in the past, the client is compatible from something like 3.x on up. The documentation from EQEmu says that MySQL 5.x is supported, so I didn’t think that was the issue. I did some digging and found that the error 1251 occurs when the hashing-method for storing passwords used by the client differs from the one of the server. Typically it occurs when trying to connect to MySQL 4.1 or 5.x with a client compiled for 3.x or 4.0.

MySQL explains this problem in more detail here:
http://dev.mysql.com/doc/mysql/en/old-client.html

As for my problem, that would be why it worked before and not after my upgrade. It also jogged my memory and I realized that I had seen this before. Luckilly, the fix for this is actually rather simple. You can tell MySQL to hash passwords the “old” way, and therefore make them compatible with older clients. You do that with the following command that you run from the sql prompt:

mysql> SET PASSWORD FOR some_user@some_host = OLD_PASSWORD('newpwd');

That should allow your account to log in from your older client, it did for me. In the past when I ran into this, it was a PHP driven app, and it was the user in the PHP MySQL connection that had the problem. New versions of PHP might not have that problem now, but if you have a version that does, this should fix it. Enjoy!