MySQL remote connection fails using "unknown authentication method"

Question

I try to remotely connect to a MySQL server online from my local machine, but I get 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 version of MySQL server is 5.5.27, libmysql is mysqlnd 5.0.10. The remote version of MySQL server is 5.5.23, the mysqlnd version is not displayed.

I assume this is an incompatible password hash problem, but I don't know how to resolve it. The following is part of the 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(); } 

Answer 1

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

 SELECT PASSWORD('mypass') 

I changed the password hashing of the localhost database server to 16 bytes by running the following query

 SET GLOBAL old_passwords = 1; 

Then I edited the my.ini file and set old_password=1 to make sure that when the server restarts it will not return to the new password system. But that did not solve my problem.

I realized that it was PHP that handles authentication because I used the PHP MySQL API , so I downgraded to PHP 5.2.8 and I was able to successfully make a remote connection.

I hope this helps someone.

Answer 2

Assuming you are using PHP 5.3+, you may come across one of the Backward Compatibility Changes :

The new mysqlnd library requires the use of the MySQL 4.1 41-byte password format. Continued use of the old 16-byte passwords will cause mysql_connect () and similar functions to throw an error: "mysqlnd cannot connect to MySQL 4.1+ using the old authentication."

If this is the case, see https://stackoverflow.com/a/2128208/ for information on updating the password.

Answer 3

I ran into this problem and found out that the problem is really related to PHP.

The solution was simple for me: switch to mysqli instead of PDO. Using Zend_Db is as simple 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 the application does not use the Zend_Db abstraction layer (or any other), you may have problems.

Answer 4

I had this problem on a shared hosting service (bluehost.com). I simply reset the borking MySql user password via the web interface provided by Bluehost. I reused the old password, retyped it in the interface and saved, and everything was fine again.