Today I faced one frustrating obstacle that made implementation of a 20-line PHP script into a 2 hour googling session.
I wanted to set up a new PHP project using classic combination of local Apache for day-to-day development, SVN, post-commit and shared hosting for client testing. Everything was OK, until I met a very confusing error message: “mysqlnd cannot connect to MySQL 4.1+ using old authentication”.
The root of the problem was that I use shared hosting database for my local copy. This arrangement usually worked very well, because at the end of the day both copies are identical and I really don't like to synchronize databases.
Unfortunately, the shared hosting runs on PHP 5.2.x, whereas I keep PHP on my workstation up to date. The problem is that the entire 5.3 version line is compiled with mysqlnd instead of libmysql and only works with “new” password hashes (present since MySQL 4.1, so not really that new). The shared hosting machine on the other hand uses old hashing.
Google helped and eventually it seemed that all I have to do is to set password to “new” hash.
SET PASSWORD FOR 'myuser'@'%' = PASSWORD('mypassword');
It seems though that this solution would be too easy. After some more googling and an experiment I realized that shared hosting has directive old-password in my.cnf set to true. Therefore both OLD_PASSWORD and PASSWORD functions generate old hash.
SELECT PASSWORD('mypassword'), OLD_PASSWORD('mypassword');
Then I realized that SET PASSWORD lets me set the internal password hash column to any string value. In other words, I don't need to use PASSWORD in the command function to get the right hash. I then used my local MySQL instance to generate “new” hash and set it directly:
SET PASSWORD FOR 'myuser'@'%' = '*FABE5482D5AADF36D028AC443D117BE1180B9725';
Problem solved.