[ACCEPTED]-MySQL PHP incompatibility-mysql

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

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

see 5 dev_mysql_set_password

edit:
You should also check if the server 4 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 3 the 5.0.22 server (the one that's "failing"). Replace 2 username by the account you're using in mysql_connect().
What 1 does that return?

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

SELECT Host, User, Password FROM mysql.user;

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

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

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

SELECT Host, User, Password FROM mysql.user;

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

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

Make 7 sure your my.cnf does not have

old-passwords = 1 

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

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

If you're using MySQL 8 or above, the default authentication plugin is 4 caching_sha2_password, so to downgrade to mysql_native_password (in case of PHP incompatibility), set 3 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 2 still won't work, change the root password 1 as suggested in the accepted answer, e.g.

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

Related:

• MySQL remote connection fails with “unknown authentication method”
• mysql_connect(): The server requested authentication method unknown to the client
• Multiple MySQL Versions for Development
• Mysql. The server requested authentication method unknown to the client

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 6 of connecting to a shared hose (in our case 5 DreamHost), who is using the oldpassword 4 option, we could not modify the users table. These 3 suggested options will work in other scenarios, just 2 not with shared web hosting.

Of note, we 1 are running WAMP.

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

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

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

select the version 8 you want and download.

install/run the exe

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

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

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

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

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

This worked!

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

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

The solution is 7 to resetting password by:

for MySQL versions 6 newer than 5.7.5 ( > 5.7.5):

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

for older versions 3 of MySQL: ( <= 5.7.5)

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

I 1 found the point here!

It made mine working well!