PHP can't connect to MySQL

[cbfnz]

I've been running older versions of XAMPP for years, and thought I'd start the update process by trying out 1.8.1 Portable version.
I've used the installer [xampp-portable-win32-1.8.1-VC9-installer.exe] to put it into \XAMPP on a removable drive, no problems, XAMPP status page is ok etc.

Now I've copied some existing websites and databases (including mysql db) onto same removable drive as XAMPP, into \WWW and \Databases respectively (this is my standard server setup pattern) and edited my.ini, php.,ini, vhosts.conf etc minimally to get everything working.

I manually start mysql and apache using the scripts in \XAMPP, and both fire up ok. I can talk to mysql ok using the command line - root login is fine, I can see my databases, no problems. Apache is also running fine - I can access any of my websites.

However, the sites (built in PHP) can't talk to mysql - connection is refused. I have a couple of different phpMyAdmin versions installed; running either of those (2.11.11 and 3.5.3) throws the same error - #2054 - The server requested authentication method umknown to the client . Both phpMyAdmins are set to auth=config, using root credentials; as I said, I can login ok as root on the mysql command line, so am scratching my head to see where the problem can be for PHP, unless I've missed a crucial setting somewhere, or a default has changed that I'm not aware of.

Just to check, I've tried this on two different systems (one WIndows XP, one Server 2003) and results are identical.

I'd be grateful if anyone can suggest what the problem might be!

[Altrea]

Hi cbfnz (a short salutation is a matter of politeness),

I never had this issue myself, but maybe this will help:
http://stackoverflow.com/questions/1340 ... patibility

best wishes,
Altrea

[cbfnz]

Thanks Altrea, that was the clue I needed - embarrassingly, all my local DB passwords were still in the old 16-char format. It seems that the mysql client included in PHP 5.4 now takes a rather fascist line on that, and insists on authenticating in new-format 41-char passwords. I have no idea why I got that weird error message though, there's a much more informative one which should normally be used in this situation.

For anyone else who hits this, you need to use the command-line client (mysql.exe)to manually update the password for each user , e.g.
UPDATE mysql.user SET password=PASSWORD('xxx') WHERE User='yyy';

However one gotcha stalled me for a bit, as when I tried this I got 0 rows updated. Apparently, mysql reverts to old passwords (including how the PASSWORD() function works) if the password field in mysql.user is only 16 chars wide - you don't get a warning, it just works in 16-char mode.

I'd already run a full mysql_upgrade.exe on all my databases, but that didn't pick up on this. So, before you update your passwords, you must execute
ALTER TABLE mysql.user MODIFY password CHAR(41) ;
and then restart mysql.

After that, PASSWORD() will return the new 41-char passwords, and the update statements will work.

Hope this helps others who haven't ever updated their passwords from mysql installs that are as old as mine :8