Red Hat Bugzilla – Bug 991070
php cannot connect (authenticate) to MariaDB using old-style passwords
Last modified: 2013-08-01 14:08:39 EDT
Description of problem:
I installed MariaDB on a Fedora 19 system recently and I cannot connect to it using PHP scripts. Both existing users from a previous upgrade, and users I newly create are not available to the PHP script. The mysql_connect() function returns "The server requested authentication method umknown to the client" (note the mis-spelling).
Note that the same
Version-Release number of selected component (if applicable):
Steps to Reproduce:
1. start up a MariaDB instance
2. create a new user
3. connect using e.g. mysql or Python
4. connect using 'php -a'
Other connect methods (mysql, python-MySQL) work correctly. PHP is unable to connect.
Here is a sample session:
MariaDB [(none)]> create user 'testuser'@'localhost' identified by 'testpass';
MariaDB [(none)]> select * from mysql.user where User LIKE 'testuser' \G
*************************** 1. row ***************************
1 row in set (0.00 sec)
$ mysql -utestuser -ptestpass
MariaDB [(none)]> select 1;
| 1 |
| 1 |
1 row in set (0.00 sec)
$ php -a
php > $link = @mysql_connect("localhost", "testuser", "testpass");
$link = @mysql_connect("localhost", "testuser", "testpass");
php > print $link;
php > print mysql_error();
The server requested authentication method umknown to the client
I'm attaching the output of 'show global variables'; see there that I turned off secure_auth and turned on old_passwords. The "new" passwords (41 character hashes) also do not work with PHP.
Created attachment 781619 [details]
output from mysql 'show global variables'
old_password (for pre < 4.1 client) are deprecated and no more accepted.
Please rerun the same test with old_password=0.
Please check if you have run mysql_upgrade.
also try using 127.0.0.1 (tcp connection) instead of localhost (local socket)
(In reply to Remi Collet from comment #2)
> old_password (for pre < 4.1 client) are deprecated and no more accepted.
> Please rerun the same test with old_password=0.
Same issue. The only difference is that new passwords generated with old_password=0 are of the 'new' type (41 character hash).
> Please check if you have run mysql_upgrade.
Yes it was upgraded.
> also try using 127.0.0.1 (tcp connection) instead of localhost (local socket)
Right, I played around with this a bit more and I think my problem is that I have some 'old' mysql passwords still (my mysql instance is very very old).
I reproduced the issue with mysqli instead, and got a more useful error:
HP Warning: mysqli::mysqli(): (HY000/2000): mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file in php shell code on line 1
I then tried to (1) re-hash the test password, and (2) point php to an existing account that I knew had a 'long' password. Both of those cases worked.
I think this resolves my php issue; perhaps this incompatibility should be in the release notes?
Note added in https://fedoraproject.org/wiki/Common_F19_bugs#PHP_cannot_connect_to_MariaDB.2FMySQL_using_old_password
Closing this one.