Cannot connect to MySQL 4.1+ using old authentication

Question

I'm trying to connect to a mySQL database at http://bluesql.net, but when I try to connect, it gives this error:

Connect Error (2000) mysqlnd cannot connect to MySQL 4.1+ using old authentication 

I've looked into this, and it has to do with some old password scheme used before MySQL 4.1. Newer versions have the option to use old passwords, which I've read may cause this problem.

I'm running php 5.3, and connecting with mySQLi (new mysqli(...)). I'm hoping I can do something in the code to connect to the DB at bluesql.net - clearly I don't control how their database is set up. Downgrading php versions isn't an option.

Anyone have any ideas?

Answer 1

edit: This only applies if you are in control of the MySQL server... if you're not take a look at Mysql password hashing method old vs new

First check with the SQL query

SHOW VARIABLES LIKE 'old_passwords' 

(in the MySQL command line client, HeidiSQL or whatever front end you like) whether the server is set to use the old password schema by default. If this returns old_passwords,Off you just happen to have old password entries in the user table. The MySQL server will use the old authentication routine for these accounts. You can simply set a new password for the account and the new routine will be used.

You can check which routine will be used by taking a look at the mysql.user table (with an account that has access to that table)

SELECT `User`, `Host`, Length(`Password`) FROM mysql.user 

This will return 16 for accounts with old passwords and 41 for accounts with new passwords (and 0 for accounts with no password at all, you might want to take care of those as well).
Either use the user management tools of the MySQL front end (if there are any) or

SET PASSWORD FOR 'User'@'Host'=PASSWORD('yourpassword'); FLUSH Privileges; 

(replace User and Host with the values you got from the previous query.) Then check the length of the password again. It should be 41 now and your client (e.g. mysqlnd) should be able to connect to the server.

see also the MySQL documentation: * http://dev.mysql.com/doc/refman/5.0/en/old-client.html
* http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
* http://dev.mysql.com/doc/refman/5.0/en/set-password.html

Answer 2

If you do not have control of the server

I just had this issue, and was able to work around it.

First, connect to the MySQL database with an older client that doesn't mind old_passwords. Connect using the user that your script will be using.

Run these queries:

SET SESSION old_passwords=FALSE; SET PASSWORD = PASSWORD('[your password]'); 

In your PHP script, change your mysql_connect function to include the client flag 1:

define('CLIENT_LONG_PASSWORD', 1); mysql_connect('[your server]', '[your username]', '[your password]', false, CLIENT_LONG_PASSWORD); 

This allowed me to connect successfully.

Edit: as per Garland Pope's comment, it may not be necessary to set CLIENT_LONG_PASSWORD manually any more in your PHP code as of PHP 5.4!

Edit: courtesy of Antonio Bonifati, a PHP script to run the queries for you:

<?php const DB = [ 'host' => '...', # localhost may not work on some hosting      'user' => '...',     'pwd' => '...', ];   if (!mysql_connect(DB['host'], DB['user'], DB['pwd'])) {     die(mysql_error()); } if (!mysql_query($query = 'SET SESSION old_passwords=FALSE')) {     die($query); } if (!mysql_query($query = "SET PASSWORD = PASSWORD('" . DB['pwd'] . "')")) {     die($query); }  echo "Excellent, mysqli will now work";  ?>