Cannot connect to MySQL 4.1+ using old authentication

Question

I am trying to connect to 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 learned this, and this is due to some old password scheme used before MySQL 4.1. In newer versions, it is possible to use old passwords, which, as I read, can cause this problem.

I run php 5.3 and connect to mySQLi (new mysqli (...)). I hope that I can do something in the code to connect to the database on bluesql.net - it is clear that I do not control how their database is configured. Earlier versions of php are not an option.

Does anyone have any idea?

Answer 1

edit: This only applies if you control a MySQL server ... if you are not looking at the hash method Mysql old vs new

Check the SQL query first

 SHOW VARIABLES LIKE 'old_passwords' 

(in the MySQL HeidiSQL command line client or any other look that you like), is the server installed to use the old default password scheme. If this returns old_passwords,Off , you only have the old password entries in the user table. The MySQL server will use the old authentication procedure for these accounts. You can simply set a new password for the account and the new procedure will be used.

You can check which of the routines will be used by looking at the mysql.user table (with an account that has access to this 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 can also take care of them).
Either use the user management tools in the MySQL interface (if any), or

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

(replace user and Host with the values ​​you received from the previous request.) Then check the password length again. Now it should be 41, and your client (e.g. mysqlnd) should be able to connect to the server.

see also 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 over the server

I had this problem and managed to get around it.

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

Run these queries:

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

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

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

This allowed me to connect successfully.

Edit: according to a comment in Garland Pope , it may not be necessary to manually set CLIENT_LONG_PASSWORD in your PHP code with PHP 5.4!

Edit: courtesy of Antonio Bonifati , a PHP script to run 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"; ?> 

Answer 3

you can make this line in 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 work. I just decided too.

Answer 4

On OSX, I used MacPorts to solve the same problem when connecting to my on-site database. Siteground seems to be using 5.0.77mm0.1-log, but creating a new user account did not help solve this problem. This is what did

 sudo port install php5-mysql -mysqlnd +mysql5 

This lowers the mysql driver php will use.

Answer 5

If the same problem, but query execution would not help. To fix this, I did the following:

• Set old_passwords = 0 in my.cnf file
• Restart mysql
• Logging in to mysql as root user
• Run FLUSH PRIVILEGES;

Answer 6

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

1) Ask to set the old_passwords parameter to false on the MySQL server

2) Reduce PHP to 5.2.2 until option 1 appears.

From what I could find, the problem is how the passwords of the MySQL account are stored, and if the parameter "old_passwords" is set to true. This causes a compatibility problem between MySQL and newer versions of PHP (5.3+), where PHP tries to connect using a hash symbol with 41 characters, but the MySQL server still saves account passwords using a 16-character hash.

This incompatibility was caused by a change in the hashing method used in MySQL 4.1, which allows using both short and long hash lengths (scenario 2 on this page from the MySQL website: http://dev.mysql.com/doc/refman/5.5/ en / password-hashing.html ) and the inclusion of MySQL Native Driver in PHP 5.3 (backward compatibility problem registered on pool 7 of this page from the PHP documentation: http://www.php.net/manual/en/migration53.incompatible.php )

Answer 7

IF,

• You are using shared hosting and do not have root access.
• you get the specified error when connecting to a remote database, that is: not localhost.
• and use of xampp.
• and the code works fine on a real server, but the problem is only on your xampp development machine.

Then

Recommended install xampp 1.7.0 . Download link

Note. This is not a solution to the above problem, but FIX, which will allow you to continue development.