A short while ago I was creating a new database on a second MySQL server to run another content-managed site. All goes well with the user privilege assigned to the newly created database. However, I came across this message (generated by PHP4):
Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client
The suggested solution on the MySQL forums was to edit my.ini and insert the following lines (First line is just a comment, it can be ommited) then restart:
#Use old password encryption method (needed for 4.0 and older clients).
old_passwords
So I did that, but the error still occurred. Out of curiosity, I re-set the password again for that user, and amazingly it worked!
What had happened was MySQL now uses the old method to encyrpt passwords when they are set. But because the original password was still encrypted using the new method, the client still thought it was incompatible (even though the old_passwords directive was set in my.ini). By resetting the user password, the old encryption method is used, so the stored password can be used by the older client.
On the MySQL forum, one particular user kicked up a fuss about how crap MySQL was. He reset the password, found it worked, so blamed MySQL without realising the above.