Topic: Can't connect to mysql on v7.0rc2 from phpMyAdmin on Windows

[johnwmcc]

I am trying to move my SME server from v6.0.1-01 to v7. I can't connect to mysql (v4.1.12) on the new server using phpMyAdmin, though I still can to the old server (mysql v4.0.22), and I CAN connect to the new server using MySQL Administrator (v1.1.. What can be going on?

 I've done a fresh install of SME 7.0rc2 onto a new PC, and used the command line mysql program to set up a user called dbadmin, with permissions to access remotely and all privileges (including Grant option) on the mysql database configuration tables. I've changed the my.cnf file to remark out skip-networking (and also updated the config file to make this survive a reboot) as I was initially getting no response from the mysql connection at all and this allows remote connections (though I thought local network connections should be allowed anyway, they weren't apparently being seen as local).

I still CANNOT connect to this new server with phpMyAdmin (v2.8.1 - using 'config' type authentication as before) running on Windows XP SP2, Apache 2.0.48), on the same local network (though I can connect to the older SME 6 server from phpMyAdmin). I get the error message:

#1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client.

I don't understand which MySQL 'client' to update. The old SME 6 server is running mysql 4.0.22, and the SME 7 server is running the LATER mysql v4.1.12. The Windows mysql client version is 5.0.

I can't connect from phpMyAdmin using any other available authentication protocol (http or cookie) either - entering the correct user and password just cycles back to a repeated "user/password" entry form.

Is it the PHP mysql_connect() version that needs to be updated? I.E, is it the PHP program (v4.3.10) on Windows I need to update? Or something else altogether?

SLIGHTLY LATER - no, I don't think it's PHP - I've updated that to v4.4.2 on Windows (at least, I ran the new version Windows installer), and it still gives the same problem. Maybe I need to re-do the install using the recommended manual method?

I wish v7 weren't so hard to get working after the installation! A direct attempt to upgrade by clean install followed by restore from v6 backup just sent the v7 server into an endless rebooting loop! So I'm trying a clean install followed by manual reconfiguration, and everything  is just proving very difficult. I can't get Gallery 2 to work without mysql, and now I can't manage mysql either, at least not with the more convenient phpMyAdmin.

I have tried Google searches and SME Forum searches for the error message (searching for "mysql error 1251" without the quotes. I did find a reference (http://lists.mysql.com/mysql/179529) to this problem, but as far as I can see, the client version at the Windows end (at least of the mysql client) is higher than at the server end already. And I have neither the tools nor the knowledge to compile PHP from source on Windows, as seems to be suggested there.

Suggestions welcome, please. In the meanwhile, I'll try reinstalling the later version of PHP on Windows, using the manual method.

John McClenahan

[johnwmcc]

After further Googling, I found some posts that suggest that the problem is a change from mysql 4.1x onwards to the encryption of passwords. They suggest that older PHP versions of mysql_connect() and related functions should be able to connect by putting a line in the my.cnf [mysqld] section saying 'old-passwords', and/or resetting the user password on the mysql command line as

SET PASSWORD FOR username = OLD_PASSWORD('password');

I've tried both of these in succession, followed by a restart of mysqld after each change. No dice - still the same error message.

John McClenahan

[johnwmcc]

Well, I've finally resolved the issue myself. It seems you have to use the mysql command line

SET PASSWORD FOR 'username'@'host'  = OLD_PASSWORD('password');

for EACH separate instance of the username on different hosts, and for both the phpMyAdmin use 'pma' and the regular user of MySQL data.

This sets the hashed password to a short (16 bit) version, which older clients DO understand, rather than the 41-bit password used by mysql v4.1 and later servers and clients.

I still don't understand why upgrading PHP on the Windows client didn't do the trick, but the show-stopping problem of not connecting is now resolved.

I hope that anyone else who has had this problem may find this post useful.

John McClenahan

[johnwmcc]

It seems I was misled by MySQL Administrator into thinking that the Windows MySQL client version is 5.0. Once I got phpMyAdmin to connect at all, it is showing the MySQL client version as 3.23.49 - older, not newer, than the SME Server v7 MySQL Server version.

John McClenahan

[mike_mattos]

Another gotcha when updating MySQL access from Windows machines is the MySQL odbc connector, it too fails on newer installs, easily fixed by getting the .net version from the MySQL site

[johnwmcc]

I haven't hit that 'gotcha' yet, but I'm sure I will! Thanks for the advance warning.

John