4

I use PHP 5.4.3 and I want to connect to a MySQL database (DB for brevity) remotely (the PHP script resides on a different server than the DB) with normal object-oriented programming (OOP) way:

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

and I get the error:

Warning: mysqli::mysqli(): The server requested authentication method unknown to the client [mysql_old_password] Connect Error (2054) The server requested authentication method unknown to the client

I found this and this but they don't seem to help. I used PHP 5.2.9 and everything worked OK, but downgrading the PHP version is not a solution for me.

I also have to mention that I don't have full access to DB's settings cause the client doesn't give me that kind of access. I told him to ask Hostgator for a newer password format support, but they answered that they cannot do anything which was not what I was expecting. I did a new effort from another remote server and I get now this:

Connect Error (1045) Access denied for user 'user'@'host' (using password: YES)

by using PHP 5.3.9. It looks like the DB doesn't "let me in" because of the remote server's PHP script, but with Oracle's Workbench everything works fine.

Thanks for your time.

2
  • But this doesnt work on a share hosting account, also with hostgator and having similar problem
    – user24248
    May 27, 2013 at 18:48
  • See also: serverfault.com/questions/385378/…
    – MrWhite
    Nov 1, 2016 at 17:33

3 Answers 3

5

PHP must be expecting to use the old MySQL authentication plugin algorithms. You do not have to restart mysql. Since old_passwords is a globally dynamic option, all you have to do is run the following:

SET GLOBAL old_passwords = 1;

In addition, please add this to /etc/my.cnf:

[mysqld]
old_passwords=1

to have future restarts retain this setting.

To further verify the need to do this, the next time you login to mysql, run this

SELECT LENGTH(password) password_length,COUNT(1) length_count
FROM mysql.user GROUP BY LENGTH(password);

If none of the passwords are of length 16, this may explain PHP's reluctance to login.

Sad to say, but the alternative would be to setup 16-character passwords, but you cannot reverse-engineer 41-character passwords. You would have to manually setup the 16-character passwords using the original plain-text values.

For example, if root@localhost had 'helloworld' as the password, it would have convert it using the OLD_PASSWORD function. Here is a comparison:

mysql> select password('helloworld');
+-------------------------------------------+
| password('helloworld')                    |
+-------------------------------------------+
| *D35DB127DB631E6E27C6B75E8D376B04F64FAF83 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select old_password('helloworld');
+----------------------------+
| old_password('helloworld') |
+----------------------------+
| 6c755a9e66debe8a           |
+----------------------------+
1 row in set (0.00 sec)

mysql>

Once you activate old_passwords, to convert it you would have to do something like this:

UPDATE mysql.user
SET password = OLD_PASSWORD('helloworld')
WHERE password = PASSWORD('helloworld');
FLUSH PRIVILEGES;

UPDATE 2013-02-02 22:44 EDT

If changing the authentication style on the MySQL side has not worked for you at this point, I have some bad news for you. You stated earlier:

I used php 5.2.9 and everything worked ok but lowering php is not a solution for me. I also have to mention that i dont have full access to DB's settings cause the client doesn't give me that full access.

You may need to downgrade PHP after all. If your hosting provider isn't in position or unwilling to adjust MySQL or give you a downgraded PHP, you will have to go with a hosting provider that will.

As an alternative you may want to look into Amazon EC2 when you can have full control of all software upgrades after running sudo -s. The links you mentioned before gave you what you needed to know. I gave you what you can do to accommodate the MySQL side. Do what you know has to be done to get this solved.

1
  • 1
    This seems to be exactly backward. The server is already asking for old passwords, but PHP mysqli only supports new passwords. So he needs to turn old_passwords off. Jan 24, 2013 at 23:45
0

There are multiple resolutions however in most cases you will not be able to do them unless you have access and the ability to:

  1. Downgrade PHP
  2. Upgrade the MYSQL Libraries to match the version of MYSQL on the server.
  3. Change the my.cnf file

Comment out OLD_PASSWORDS = 1

restart mysql

Create or modify a user with the password desired

Uncomment out OLD_PASSWORDS = 1

restart mysql

0

I know this is quite old issue, but seems still not solved fully. I met the same problem, and I solved this in following way.

In my.ini file of MySQL, I changed default_authentication_plugin to mysql_native_password

' default_authentication_plugin=caching_sha2_password  ' comment out
default_authentication_plugin=mysql_native_password

And then, at the MySQL Workbench, I set the password type of schema to standard, not sha2-something. Then it works.

MySQL is version 8.0, and PHP is 7.26 running on Window 7.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.