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?
This question is related to
php
mysql
authentication
connection
Had the same issue, but executing the queries alone will not help. To fix this I did the following,
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
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";
?>
IF,
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.
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.
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).
Source: Stackoverflow.com