MySQL remote connection fails with "unknown authentication method"

Question

I am trying to remotely connect to MySQL server online from my local machine, but I am getting the following error:

Warning: PDO::__construct(): The server requested authentication 
method unknown to the client [mysql_old_password] in 
C:\xampp\htdocs\ticket\terminal\sync.php

SQLSTATE[HY000] [2054] The server requested authentication method 
umknown to the client

My local MySQL server version is 5.5.27, libmysql - mysqlnd 5.0.10 The remote MySQL server version is 5.5.23, the mysqlnd version isn't exposed.

I guess it's an incompatible password hash issue, but I do not know how to resolve it. Below is part of my connection code

$dsn = 'mysql:host=184.173.209.193;dbname=my_db_name';
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
); 

try {
    $online_dbh = new PDO($dsn, 'myusername', 'mypassword', $options);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Congratulations!";
} catch (PDOException $e) {
    echo $e->getMessage();
}

This worked for me : http://stackoverflow.com/a/22794125/1875434 — abbas, Apr 01 '14 at 18:38
The problem solved by making a new user with the same credentials. — Moxet Khan, Aug 23 '15 at 08:52
Related: [MySQL PHP incompatibility](https://stackoverflow.com/q/1340488/55075). — kenorb, Aug 01 '19 at 16:34

score 13 · Answer 1 · edited May 23 '17 at 12:31

Assuming you're using PHP 5.3+, you could be experiencing one of the Backward Incompatibility Changes:

The new mysqlnd library necessitates the use of MySQL 4.1's newer 41-byte password format. Continued use of the old 16-byte passwords will cause mysql_connect() and similar functions to emit the error, "mysqlnd cannot connect to MySQL 4.1+ using old authentication."

If so, see https://stackoverflow.com/a/1340538/187954 for information on updating your password.

score 5 · Answer 2 · answered May 24 '18 at 10:04

5

This may help someone with this issue. This is how I fixed it in my situation. From the MySQL PHP API (PDO_MYSQL) website

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release. In the meantime, the mysql_xdevapi extension does support it.

answered May 24 '18 at 10:04

Rob

75
2
7

where is this "my.cnf" file located in macos ? – Abdul-Elah JS Jun 23 '18 at 03:55
@Haboosh If you installed via brew it's at `/usr/local/etc/my.cnf` – cjohansson Jul 04 '18 at 06:31
This answer worked for me but I had to re-create the user and it's password to make it work – cjohansson Jul 04 '18 at 06:32
Im my case, after I have edited conf file, I also send command as following: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; – Velaro Dec 11 '18 at 06:20

score 3 · Accepted Answer · answered Mar 10 '13 at 09:59

3

I overcame the challenge. I found out that my remote MySQL database host still uses the old MySQL password hash which is 16-byte, while my localhost database server uses 41-byte password hash. I used the following query to find the password length:

SELECT PASSWORD('mypass')

I changed my localhost database server password hash to 16-byte by running the following query

SET GLOBAL old_passwords = 1;

Then I edited my.ini file, and set the old_password=1 to ensure that when the server restarts, it won't revert to the new password system. But that didn't solve my problem.

I figured out that it was PHP that handles the authentication, since I was using PHP's MySQL API, so I downgraded to PHP 5.2.8 and I was able to make the remote connection successfully.

I hope this helps someone.

answered Mar 10 '13 at 09:59

Chibuzo

6,112
3
29
51

@ian so just telling us that downgrading doesn't seem right, rather seems right to you? – Chibuzo Aug 01 '14 at 10:18
@Chibuzo yep downgrading for me is always not a correct solution. I was able to fix mine by making sure my mysql client version is up to date with the server version. Then set old_password = 0 and set a new root password. – ian Aug 01 '14 at 14:34
@ian How about when the server isn't yours and you run a higher version than the server? That was my case. What would have been the solution? – Chibuzo Aug 13 '14 at 16:51
Just to make it clear: You do not have to change anything in your my.ini MySql server config, nor downgrade PHP version. It is sufficient that you reset the password of the account of interest, after issuing a `set old_passwords=0` in your current session. Once the password is renewed, PDO can connect, no matter the value of `@@old_passwords` on the server. – Gabriel Dec 31 '15 at 15:27

score 3 · Answer 4 · answered Nov 13 '18 at 19:23

3

alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it.

answered Nov 13 '18 at 19:23

Adrián Prieto

177
1
4

score 2 · Answer 5 · answered Sep 09 '14 at 08:11

I ran into this same problem and figured out the problem had indeed to do with PHP.

The solution was simple for me: switch to mysqli instead of PDO. When using Zend_Db, this is as easy as changing 1 line:

$db = Zend_Db::factory('pdo_mysql',array('host' => MYSQL_HOST, 'username' => MYSQL_USER, 'password' => MYSQL_PASS, 'dbname' => MYSQL_SCHEMA));

Becomes

$db = Zend_Db::factory('mysqli',array('host' => MYSQL_HOST, 'username' => MYSQL_USER, 'password' => MYSQL_PASS, 'dbname' => MYSQL_SCHEMA));

But if you application is not using the Zend_Db abstraction (or any other) layer, you could be in trouble.

score 1 · Answer 6 · answered Sep 14 '17 at 10:40

1

I had this problem on a shared hosting service (bluehost.com). I just reset the borking MySql user's password via the web interface provided by Bluehost. I re-used the old password, retyped it at the interface and saved and everything was fine again.

answered Sep 14 '17 at 10:40

metatron

896
7
14

score 0 · Answer 7 · answered Apr 29 '21 at 23:39

If you are using Bitbucket Pipelines and MySQL 5.8+, you can add the known argument --default-authentication-plugin=mysql_native_password to your service like this:

definitions:
  services:
    mysql:
      image: mysql:8.0
      environment:
        MYSQL_DATABASE: 'app'
        MYSQL_ROOT_PASSWORD: 'root'
        MYSQL_DEFAULT_AUTH: 'mysql_native_password'

score -1 · Answer 8 · answered May 02 '18 at 03:59

-1

I got this problem when running bitbucket pipelines with laravel 5.6 and default mysql database (set is services:).

solution was to use older version of mysql

definitions:
  services:
    mysql:
     image: mysql:5.6

PS: it was PHP 7.1

answered May 02 '18 at 03:59

Yevgeniy Afanasyev

37,872
26
173
191

changed a file bitbucket-pipelines.yml – Yevgeniy Afanasyev Jun 28 '18 at 23:46
I found a solution and I shared it. Please, add another answer if you know the better way. – Yevgeniy Afanasyev Apr 29 '21 at 23:31

MySQL remote connection fails with "unknown authentication method"

8 Answers8

Linked

Related