MySQL PHP incompatibility.

Question

Ok maybe I've overlooked something really simple here, but I can't seem to figure this out.

I'm running WAMP locally, but connecting to a remote MySQL database. The local version of PHP is the latest 5.3.0.

One of the remote databases, being version 5.0.45 works fine. However, the other remote database I'm trying to connect to, which is version 5.0.22 throws the following error before dying:

Warning: mysql_connect() [function.mysql-connect]: OK packet 6 bytes shorter than expected. PID=5880 in ...

Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication in ...

WTF?

UPDATE:

Reverting to PHP 5.2.* i.e. anything lower than 5.3.0 resolves the problem completely. As long as I am not running 5.3.0 I can connect to both databases. I'm not sure what the explanation is for this weirdness.

Answer 1

The MySQL account you're using probably has an old 16 character long password (hash).
You can test that with a MySQL client (like HeidiSQL, the MySQL console client or any other client) and an account that has access to the mysql.user table. If the Password field contains 16 chars it's an old password and mysqlnd cannot use it to connect to the MySQL server.
You can set a new password for that user with

SET PASSWORD FOR 'username'@'hostmask' = PASSWORD('thepassword')

see http://dev.mysql.com/doc/refman/5.0/en/set-password.html

edit:
You should also check if the server is set to use/create old passwords by default.

edit2:
Please run the query

SELECT
  Length(`Password`),
  Substring(`Password`, 1, 1)
FROM
  `mysql`.`user`
WHERE
  `user`='username'

on the 5.0.22 server (the one that's "failing"). Replace username by the account you're using in mysql_connect().
What does that return?

Answer 2

My webhost has different versions of PHP/MySQL configured and to use a certain one I need to use the correct .php extension - notably .php5 It might be something as simple as that.

Answer 3

Your database server is set to use old passwords by default. The error message you get is mysqlnd seeing a database that can support the new (safer) authentication but refuses to do so. In such a case, mysqlnd aborts the connection and refuses to work.

Make sure your my.cnf does not have

old-passwords = 1

After you comment out that setting from my.cnf (or remove it from where else it might be set), and restart your server, make sure to re-set your password using the command VolkerK describes, otherwise you won't be able to log in.

Answer 4

I have been trying to find a simple fix for this problem. Try this approach. In MySQL type

SELECT Host, User, Password FROM mysql.user;

If your password is sixteen characters, this is because you have used OLD_PASSWORD on your user's or have been running an old version of MySQL. To update type in

UPDATE mysql.user SET Password=PASSWORD('newpass')
  WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

swapping root, localhost and newpass for your user, host and pass respectively. Now when you re-type

SELECT Host, User, Password FROM mysql.user;

Your password should have changed. This fixed it for me.

Answer 5

As the user Evernoob above said:

"Reverting to PHP 5.2.* i.e. anything lower than 5.3.0 resolves the problem completely. As long as I am not running 5.3.0 I can connect to both databases. I'm not sure what the explanation is for this weirdness"

In the case of connecting to a shared hose (in our case DreamHost), who is using the oldpassword option, we could not modify the users table. These suggested options will work in other scenarios, just not with shared web hosting.

Of note, we are running WAMP.

Answer 6

Thank you so much for this answer and info, i was desperate to get this to work! I opened the wamp menu and chose get more versions for php, and selected the 5.2.11, installed, then selected it from the menu and now everything works handy dandy :) Thanks again!