MySQL PHP incompatibility

Question

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.

mysqlnd isn't part of 5.2, i.e. you're using another connector/lib now that you've reverted to php 5.2. — VolkerK, Aug 27 '09 at 12:38

score 45 · Accepted Answer · edited Dec 09 '11 at 08:40

45

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 dev_mysql_set_password

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?

edited Dec 09 '11 at 08:40

Chandra Sekhar

16,256
10
67
90

answered Aug 27 '09 at 11:40

VolkerK

95,432
20
163
226

no both authentications are the same and not as lengthy as that. – Evernoob Aug 27 '09 at 11:43
"not as lengthy"? I do not mean the plain password but the hash stored in the user table ;-) The old algorithm used 16 characters, the new ...uhm... 40? – VolkerK Aug 27 '09 at 11:46
yeah... still not working sorry. This is a weird error it's annoying me. – Evernoob Aug 27 '09 at 12:00
Ok, let's test whether you still have old passwords. see edit2. – VolkerK Aug 27 '09 at 12:09
getting guys at work to check if this is the case, updates in a sec. But for now the older PHP version seems to work for whatever reason. – Evernoob Aug 27 '09 at 12:52
I would have never guessed this answer. You rock! I owe you dinner some day. – gknauth Feb 02 '10 at 19:11
is there no way to tell the client to use the old passwords? I can't change what is happening on the host. I'm trying to develop towards an existing app. I have no idea how to modify xampp to use an older version of php. – JDPeckham Apr 06 '13 at 15:55

score 7 · Answer 2 · answered Jun 11 '10 at 03:07

7

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.

answered Jun 11 '10 at 03:07

Matt

71
1
1

1

Simplest fix! Updating the password to the same old password (Password=PASSWORD('oldpass')) works as well though. – kehers May 26 '13 at 06:47
2

If the server still creates 16-character passwords by default, you can fix this still: create the 41-character hash on your local machine with `SELECT PASSWORD('yourpass');`, then copy the printed hash into a command like this and execute it on the server machine: `UPDATE mysql.user SET Password='yourhash' WHERE User='root'; FLUSH PRIVILEGES;`. – tanius Aug 23 '14 at 20:29

score 4 · Answer 3 · answered Aug 27 '09 at 12:05

4

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.

answered Aug 27 '09 at 12:05

Guss

30,470
17
104
128

I do not have ol-passwords = 1 in my my.ini so how do I change the database server to not use old passwords – Sid Sep 28 '16 at 12:18
1

@sid: MySQL can load multiple configuration files, and you need to make sure none of them have old passwords enabled. Another possibility is that you have the global variable `old_passwords` set to 1. To check, run `SELECT @@global.old_passwords;` – Guss Sep 28 '16 at 13:22

score 3 · Answer 4 · answered Apr 01 '14 at 18:37

3

A more simple solution is to delete the database user and create a new one with the same username and password.

answered Apr 01 '14 at 18:37

abbas

6,453
2
40
36

This happened to me when i decided to update my `ppa:ondrej/php`. I also had to update my apache2 service. Long story short, this mysql issue happened to me. I just wanted to say that this solution worked for me, and I didn't even need to go through the hassle of creating a new user. I simply had to just update the password of my existing user. I hope this helps someone else down the road. Thank you for your brief and working solution. – Casper Wilkes May 20 '17 at 21:31

kenorb · Answer 5 · 2019-08-01T19:43:36.767

If you're using MySQL 8 or above, the default authentication plugin is caching_sha2_password, so to downgrade to mysql_native_password (in case of PHP incompatibility), set it in my.cfg, e.g.

[mysqld]
default-authentication-plugin=mysql_native_password

then restart MySQL service.

See: Upgrading to MySQL 8.0 : Default Authentication Plugin Considerations & Native Pluggable Authentication.

If still won't work, change the root password as suggested in the accepted answer, e.g.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'root';
FLUSH PRIVILEGES;

score 2 · Answer 6 · answered Sep 03 '10 at 06:43

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.

score 1 · Answer 7 · answered Aug 27 '09 at 12:02

1

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.

answered Aug 27 '09 at 12:02

user97410

714
1
6
22

score 1 · Answer 8 · answered Nov 04 '10 at 22:31

Reverting to PHP 5.2.* did the trick! THANK YOU!

If you're using WAMP... left click wamp > php > version> get more>

select the version you want and download.

install/run the exe

left click wamp > php > version> PHP 5.2.*

That fixed this issue. I couldn't run any of these SQL commands without getting a "command denied to user 'XXX'@'localhost'" error. Good look trying to log on as SU 'Root'. Maybe on a personal server, but not going to happen on a real host.

score 1 · Answer 9 · answered Jan 29 '17 at 17:13

I just had this problem with a Wordpress site that suddenly stopped working and displayed this error.

I found a GUI in the website's Control Panel that allowed me to change the password for the database user. I tried changing it to the current password and this was not allowed because the current password was not strong enough.

I changed the database password to a new, stronger password and edited my wp-config.php file to have the new password.

This worked!

So my guess is, the hosting provider upgraded something, maybe MySQL, to a version that requires stronger passwords. The fix for me was to use the cpanel GUI to change the database user's password, and update wp-config.php to match.

score 1 · Answer 10 · answered Dec 13 '18 at 13:04

After lots of search and tries I found a way without needage to any downgrade/upgrade of MySQL and any affect on other users of running instance of MySQL.

The solution is to resetting password by:

for MySQL versions newer than 5.7.5 ( > 5.7.5):

ALTER USER 'mysqlUsername'@'hostname' IDENTIFIED WITH mysql_native_password BY 'mysqlUsernamePassword';

for older versions of MySQL: ( <= 5.7.5)

SET PASSWORD FOR 'mysqlUsername'@'hostname' = PASSWORD('mysqlUsernamePassword');

I found the point here!

It made mine working well!

MySQL PHP incompatibility

10 Answers10

Linked