SOLUTION: "mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client"

[mglatts]

Hello everyone, and cheers from sunny Brazil.

First of all, this will be a long post, so I apologize for that. Take a cup of coffee ;)
I’m a newbie, so I’ll write this for newbies, as much as I can in non-technical terms.

I had the same problem that several guys had with phpMyAdmin, the weird error:

“#1251 - mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client”

But after some considerable effort and a lot of tries I managed to solve it, so I decided to share here what I did in order to help other newbies as I am.

I have divided this post in three parts:
1. The environment
2. The problem
3. The solution

Let’s go.

1. The environment

=>Installed, up and running:

ZoneAlarm 5.1.011.000 Firewall
Panda Platinum 7.07.01 Anti-Virus

Windows XP SP 1
Apache server 2.0.52 (see Notes about Apache server installation below)
mySQL server 4.1.7
php 4.3.10RC1

The last 4 components form the so-called WAMP configuration (acronym of their initial letters).

Notes about Apache server installation:
a) If you’re running XP, you MUST have at least SP1 installed, or Apache 2.x will not work properly.
b) If you have ZoneAlarm (any version) installed, UNINSTALL it PRIOR to installing Apache 2.x. Failing to do so will make Apache behave oddly and be unstable, or sometimes even not be installed or simply stop responding and can’t be started anymore. After a successful Apache 2.x installation, you can re-install ZoneAlarm with no worries.
c) If you’re running Skype, SHUT IT DOWN (there’s no need to uninstall it) PRIOR to installing Apache 2.x. That’s because Skype uses ports above 1024 for outgoing traffic, but, if that range of ports is not available, it goes for port 80. If you use a firewall and have it properly configured, likely this range will not be available for Skype, so it will use port 80 and enter in conflict with your Apache web server, because the web server is set by default to use (listen to) port 80. After successfully installing Apache, you can restart Skype, and you should have no problems. Just a tip: there’s a small free tool available from Foundstone called Fport that tells you who is the service that is using which port. It is a simple command line tool. Use it in a command line window. Get it here:
http://www.foundstone.com/knowledge/termsofuse.html?filename=FPortNG.zip
(look for Free Tools -> Intrusion detection Tools -> Fport).

d) When configuring Apache to use php, I STRONGLY suggest you use php as an Apache module instead of CGI. That’s because module handling is much more stable than CGI handling. This is particularly applicable if you intend to serve Xoops, SugarCRM, OSCommerce or any php-based application such these ones.

=>Initial values set for mySQL server:

user: root
password: (none)

Note: Yes, I know that it is an enormous security risk not setting a password for mySQL server, but this was done in a test station with no access to the Internet at all. It was just to test the first configuration.

=>Last one installed:

phpMyAdmin 2.6.0-pl3

No problem until then.

2. The problem

The problem started when I used phpMyAdmin to change mySQL’s root password (actually to put a password on it, as I had no password initially).

For some reason, when you change the mySQL root's password in phpMyAdmin, the password is correctly changed in the mySQL server, but the new password is not updated to the config.inc.php file (phpMyAdmin's configuration file, read every time you start phpMyAdmin).

I’ve even edited manually the config.inc.php in the “$cfg['Servers'][$i]['password']” field (without the quotes), changing the password to the new one I had set, but nothing happened. I tried stopping mySQL server and restarting it, but no luck so far.

So I got the error:

“#1251 - mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client”

and then also its brother:

“#1045 – Access denied for user ‘root’@’localhost’ (using password: YES)”


3. The solution

My first measure was downloading and installing MySQL Administrator (get it freely at mySQL’s site).

I run MySQL Administrator (I used “localhost” to fill in the “Connection” and “Hostname” fields, port 3306) and I could connect to my mySQL database. Then, I set the root’s password to (none) again, applied the changes, edited back “$cfg['Servers'][$i]['password']” field of config.inc.php to have no password and went to verify if phpMyAdmin was working now. Yes, it was working perfectly.

“Damn”, I thought, “so I can not have a password in my database? That’s not possible!”.
So I started browsing the Internet looking for help and reading, reading, reading…

I found out the solution. Find below a step-by-step guide:

STEP 1: Go to your my.ini file in your mySQL directory (e.g., C:\Program Files\MySQL\my.ini), using the Notepad or any editor to open it up.
Add the following :

#Use old password encryption method (needed for 4.0 and older clients).
old_passwords

Save and close the file. (pay attention to not save it as a .txt! It must be saved as its original extension type, .ini . Select “All files” in the “Save as” field of the dialog box)
Note: I have added it two lines below of

# The TCP/IP Port the MySQL Server will listen on
port=3306

but where you add these lines really doesn’t matter, as it seems.

STEP 2: Use MySQL Administrator to connect to your database and change the root’s password to something other than (none). Press the Apply Changes button.

STEP 3: Edit your phpMyAdmin’s config.inc.php file with any text editor, like Notepad. You’ll go to the line “$cfg['Servers'][$i]['password']” (there are no quotes) and fill in your new password after the equal signal ( = ), inside the two single quotes ( it is ‘’ , should be ‘yourpassword’ ). This password must be the same one you set up in MySQL Administrator. Save and close the file.

STEP 4: Stop Apache and stop mySQL. You can use MySQL Administrator to stop mySQL (the option Service Control) and use Apache Monitor (that sits in your system tray) to stop Apache. The order in which you stop them doesn’t matter.

STEP 5: Restart Apache.

STEP 6: Restart mySQL. Now, if you try to restart mySQL with MySQL Administrator, you’ll get an error message. You have changed mySQL’s password and then stopped the database server, so now even if you put the right password in MySQL Administrator it is, of course, unable to restart mySQL. So, you’ll have to restart the database manually. Go to the Windows’ Start button and click on “Run”. Type “services.msc” (no quotes). Look for mySQL in the list, click once over it with your mouse’s right button, and choose “Start”. Now mySQL should start again. You’ll confirm this looking at the column “Status”, that should show “Started”.

IMPORTANT!
Re-start FIRST the Apache Server, and THEN the mySQL Server.
If you do it vice-versa, you will continue with the same error.

Now press the reload button in your browser. phpMyAdmin should connect normally, and your database is protected by a password.

Note: Now, you should be done. But if the error persists you simply leave everything working (does NOT stop any server) and go to your MySQL Administrator, re-writing the same password and pushing the button “Apply Changes”. Go to your browser, press the reload button, and the problem is gone.

==== END OF POST =====