If you’ve recently upgraded from php 5.3 to a newer version (5.5, 5.6, 7.0) you may have encountered a nasty bug on your WordPress or Magento website:

SQLSTATE[HY000][2054]
The server requested authentication method unknown to the client [mysql_old_password]

Your application is no longer able to connect to the database! So, what’s going on here? In version 4.1 MySQL started introducing a new internal storage mechanism for passwords. When you connect to mysql in newer versions, it assumes your password hash will be 41 bytes long. The old versions assume they are 16 bytes long.

When you upgrade MySQL, it maintains a compatibility mode called old_passwords allowing you to continue using the 16 byte passwords. However, new versions of php do not allow you to continue using the 16 byte passwords. See this link.

To fix the problem, you need to force MySQL to use the new password format and update any old passwords which were stored using the incorrect format. If you’re on shared hosting, you will need to contact your hosting company as this change may require root access to both the server and mysql

Disable MySQL old_passwords

The first thing you want to do is update /etc/mysql/my.cnf. (This file may also be stored at /etc/my.cnf).
You should see some text like the below, which you will need to change:

## Change the below:

old_passwords=1;

## Change to:

old_passwords=0;

Once you’ve made this configuration change, you need to restart mysql:

sudo service mysql restart

Now you have instructed MySQL to process passwords in the new format.

Update Existing User Passwords

Now comes the harder part — you will need to re-hash existing user passwords to use the new format. Note that you must have the non-hashed (plaintext) password in order to update it. Otherwise you will need to simply reset the password to something new.

First, run the below command to get a list of current users and their password hashes:

SELECT host,user,password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | 5ea2f2a3436125ss9                         |
| localhost | alan | *11ADAA0470F690CC585086B716E32F4222294A59 |
| 127.0.0.1 | root | 5ea2f2a3436125ss9                         |
+-----------+------+-------------------------------------------+

Notice the output of the above command. Do you see anything different between alan and root? Alan’s password is clearly in the new format (longer text) and root’s is in the old format (shorter text). So, we know that we need to update the root user to use the new password format. Also, due to the way mysql stores users, we need to update 2 entries for the hosts “localhost” and “127.0.0.1” (yes they’re considered to be different).

Run the below commands to update:

UPDATE mysql.user SET Password = PASSWORD('plaintext_password_here') WHERE User = 'root';
Flush Privileges

Those two commands will change the root user’s password to whatever plaintext_password_here is, and will ensure it’s in the new format. We then flush the privileges to ensure the update takes effect.

Once you’ve completed both of the above steps, you should see php be able to connect to mysql again over mysql, mysqli, or pdo!

Alan Barber is the Lead Web Developer at Cadence Labs and a Magento Certified developer.