This is generally a fairly simple problem, the password in the database is using the older short hash method instead of the newer method.
First: Let's check what size the password is:
C:\mysql\bin>mysql.exe --database=dbname --password=pass123 --user=user123 --host=127.0.0.1
mysql> use mysql;
Database changed
mysql> select Password from user where User = 'user123';
+------------------+
| Password |
+------------------+
| 0123456789abcdef |
+------------------+
1 rows in set (0.03 sec)
You'll notice the password is 16 digits long, not 41 as the new passwords are in MySQL 4.1 and above.
The following is what the output SHOULD look like:
mysql> select Password from user where User = 'user123';
+-------------------------------------------+
| Password |
+-------------------------------------------+
| *DA1234567890ABCDEF0123456789012345678901 |
+-------------------------------------------+
1 row in set (0.05 sec)
To fix you just need to reset your password:
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('mypassword');
There are no shortage of pages and/or blog entries talking about this fix, however what made this confusing is after resetting the password was still short (i.e. 16 digits).
Another method to check if the short password is used is by selecting PASSWORD('test') (any password can be substituted for the word test):
mysql> select PASSWORD('test');
+-------------------------------------------+
| PASSWORD('test') |
+-------------------------------------------+
| 0123456789ABCDEF |
+-------------------------------------------+
1 row in set (0.05 sec)
This may be happening because you have old-passwords = 1 in your my.cnf file - This tells MySQL to run in it's old mode - However the newer versions of PHP 5.3.0 and up (including PHP 5.3.1) will refuse to talk to a MySQL server with this setting enabled.
Unfortunately what stumped me today was working with a customer's dba to access MySQL on their server elsewhere for a new web site hosted at BitShop - they didn't do the required :
Until that was done the password update didn't take effect.
I hope this helps someone..
References / More Reading: