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?

Turbot

@Hill I'm sorry you feel that way : ( . But we'd basically be still living in cave man times if it weren't for open source. No open source project is perfect (except mine of course ; ) but in a lot of cases, open source software libraries are more trustworthy than closed source things. Its certainly different in the case of end-user software. – Turbot

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

Myology

Thanks for the detailed answer. I was able to connect with a gui client, and old_passwords is indeed "ON". I thought I had something when i "set old_password = OFF", but when i started a new session it was back on again. – Turbot

Oops, I didn't realize that you're not in control of the server (some kind of latent text blindness). In that case I think Charles is right. Or... maybe not all of the servers at bluesql.net are configured to use short password hashes. And maybe, if you ask them, you can be transferred to another server. – Myology

Great answer. I needed to do SET old_passwords = 0; to get this to work, taken from Elliot Yap's answer below. – Civilly

I did all the above steps but still getting 16 for password that i have changed. – Chan

You have to SET old_passwords=0 before you SET PASSWORD FOR 'user'@'host' = password('yourpassword'); – Our

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"; 
?>

Thickness

I only had to execute the two SET statements to fix mine (PHP 5.4, MySQL 5.0). The PHP code required no change: $this->dbh = mysql_connect(DB_HOST, DB_USER, DB_PASSWD); – Heidy

I imagine without [] right? SET SESSION old_passwords=FALSE; SET PASSWORD = PASSWORD('mypass'); Instead of SET SESSION old_passwords=FALSE; SET PASSWORD = PASSWORD('[mypass]'); – Taille

This is really the best solution if you don't have access rights to the user's table. – Corena

I'm using the mysqli module (PHP 5.6, MySQL 5.0) and I'm experiencing the same problem. I followed your procedure, but I've got the same warning: the MySQL queries are executed correctly using phpmyadmin, but I can't find an equivalent for the PHP part using the mysqli module. What can I do? (my website is hosted by aruba on a windows vm if it's important...) Thanks :) – Pargeting

PHP script to fix the problem, without shell access or connect to the db server externally: <?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"; ?> – Oloroso

I am so lost... I hate open source. – Arteriole

Answer 3

you can do these line on your mysql query browser or something

SET old_passwords = 0;
UPDATE mysql.user SET Password = PASSWORD('testpass') WHERE User = 'testuser' limit 1;
SELECT LENGTH(Password) FROM mysql.user WHERE User = 'testuser';
FLUSH PRIVILEGES;

note:your username and password

after that it should able to work. I just solved mine too

Cither

This solution only works if you are the database administrator, or someone with access to the internal mysql database. – Thickness

Answer 4

On OSX, I used MacPorts to address the same problem when connecting to my siteground database. Siteground appears to be using 5.0.77mm0.1-log, but creating a new user account didn't fix the problem. This is what did

sudo port install php5-mysql -mysqlnd +mysql5

This downgrades the mysql driver that php will use.

Sex

How to upgrade back to latest in case you need to later on? – Barter

Also any suggestions on Windows side? Install older version of PHP (standalone or with WAMP, etc.)? I don't suppose one could do downgrade of mySQL driver for PHP on Windows like you can with Mac here. – Barter

Note that the downgrade may change your system's reference path to PHP. Prior to downgrade, calling which php, reveals /usr/bin/php. After the downgrade, it changed to /opt/local/bin/php, possibly because of use of MacPorts, where before it was using Mac system default PHP. My PHPUnit is still under /usr/bin/phpunit though. It also looks like my PHP was downgraded from 5.x (whatever was default on Mac OS X 10.6) to 2.3.0. But the one plus side is that I was able to use mysql connect after this downgrade. Hopefully the downgrade didn't bring other issues along though. – Barter

Disregard my comment about PHP version. Misread the Zend engine version for PHP version. For me /usr/bin/php is 5.3.4 and /opt/local/bin/php is 5.3.12. – Barter

Answer 5

Had the same issue, but executing the queries alone will not help. To fix this I did the following,

1. Set old_passwords=0 in my.cnf file
2. Restart mysql
3. Login to mysql as root user
4. Execute FLUSH PRIVILEGES;

Culminant

This solution only works if you are the database administrator with access to the my.cnf file. – Thickness

Answer 6

If you do not have Administrator access to the MySQL Server configuration (i.e. you are using a hosting service), then there are 2 options to get this to work:

1) Request that the old_passwords option be set to false on the MySQL server

2) Downgrade PHP to 5.2.2 until option 1 occurs.

From what I've been able to find, the issue seems to be with how the MySQL account passwords are stored and if the 'old_passwords' setting is set to true. This causes a compatibility issue between MySQL and newer versions of PHP (5.3+) where PHP attempts to connect using a 41-character hash but the MySQL server is still storing account passwords using a 16-character hash.

This incompatibility was brought about by the changing of the hashing method used in MySQL 4.1 which allows for both short and long hash lengths (Scenario 2 on this page from the MySQL site: http://dev.mysql.com/doc/refman/5.5/en/password-hashing.html) and the inclusion of the MySQL Native Driver in PHP 5.3 (backwards compatibility issue documented on bullet 7 of this page from the PHP documentation: http://www.php.net/manual/en/migration53.incompatible.php).

Excurved

Answer 7

IF,

1. You are using a shared hosting, and don't have root access.
2. you are getting the said error while connecting to a remote database ie: not localhost.
3. and your using Xampp.
4. and the code is running fine on live server, but the issue is only on your development machine running xampp.

Then,

It is highly recommended that you install xampp 1.7.0 . Download Link

Note: This is not a solution to the above problem, but a FIX which would allow you to continue with your development.

Ferrari