How to fix “Warning: PDO::__construct(): The server requested authentication method unknown to the client [mysql_old_password]” authentication error

This article describes how to fix MySQL connection error: (“Warning: PDO::__construct(): The server requested authentication method unknown to the client [mysql_old_password]”), experienced during MySQL or MariaDB authentication (typically during establishing a database connection).

Error typically looks like this (aside from the paths):

Warning PDO construct The server requested authentication method unknown to the client mysql_old_password

Or in CMS installs (Drupal, WordPress or similar) it often presents itself right in the browser’s window in a following way:

drupal-Warning PDO construct The server requested authentication method unknown to the client mysql_old_password

An actual error message text is pretty clear:

“Warning: PDO::__construct(): The server requested authentication method unknown to the client [mysql_old_password]”

Explanation: Reason for this error is due to database using an authentication method which is different from that used by the database client or application trying to connect to database. The fact that error mentions [mysql_old_password] is a sort of a giveaway in this case. It refers to a fact that database is using MySQL’s newer 40-byte password format (also referred to as SHA-1 160-bit or string of 40 hex digits), while the password used by the client/app is using an old 16-byte encoding format during authentication.

Simpy said: Password you’re using to connect is encoded in MySQL as a 16 character string, instead of  expected 40 character long string.

Note that a use of the old 16-byte passwords will always throw an error when using mysql_connect or similar MySQL or MariaDB connection functions. In such case you will see an error similar to this one:

We’ll use an example to explain this little more…

Let’s say your MySQL connection password is:  ‘123456‘.

If your server is using old password encoding, and if you were to open ‘mysql’ database and look inside the ‘users’ table, you’d see that it is not stored in a clear form format (that’s expected), but encoded as following 16 byte long string:

Let’s verify this using command line by forcing the MySQL session to use old 16 character long password encoding scheme:

If your server is using new password encoding (40 byte long character), password ‘123456‘ would have been stored as following 40 byte long string:

Again we can verify it through command line by forcing MySQL session to NOT to use old_password encoding scheme; and thus turn on 40 byte long encoding:

Note: You can reference following really nice article for more details about this.

So how do we fix the issue?

We’ve established that in order to resolve the issue, password encoding has to be changed from 16 byte to 40 bytes.

This can be done in two steps:

– Configure MySQL/MariaDB to use new password encoding (40 byte / 40 characters)

– Change mysql user password – overwriting 16 characters with 40 character long encoding in the process (or recreate mysql user)

 

So, assuming you have complete control over your MySQL/MariaDB install:

1. First – Open MySQL configuration file my.ini or my.cnf – depending on which one is used by your MySQL install. This file is usually stored in the installation directory of the MySQL server. In XAMPP installations it’s also found under mysql/bin directory.

Note: Make sure that you’ve identified a correct configuration file, because sometimes there is more than one placed on the system (refer to following article on how to find the config file used by your installation of MySQL/MariaDB)

2. Search inside the my.ini (or my.cnf) for the line containing: old-passwords. There is couple of scenarios you may encounter:

Scenario 1:

If it reads:

Change the line to:

Scenario 2:

If it reads:

Change the line to:

Scenario 3:

If you can’t find ‘old-passwords’ in the config file, locate the section marked as [mysqld]

And add following right below:

In all scenarios, end result should look like this:

mysql

3. Once this is done, save the configuration file and don’t forget to restart MYSQL database!

4. Next step is to change the user password. Connect to MySQL/MariaDB from a command line and run following command. Let’s assume that you have a user called ‘test’ and want to update the password use password ‘123456’:

And that’s it, if you were to look at the ‘mysql’ database ‘user’ table, you’d see that your encoded password has changed from 16 characters to 40 characters.

This should resolve the error. Let me know in the comment box below, if you experience any issues.

Note:

If you don’t want to use a command line, you can do everything above by using HeidiSQL graphical interface. HeidiSQL is a useful and reliable tool designed for web developers using the popular MySQL server. It basically enables you to browse and edit data, create and edit tables, views, procedures, triggers and scheduled events. One of the advantages for cases such as dealing with this particular error is, that if the new password scheme doesn’t work, it forces the session using old password. So it’ll always let’s you connect as long as your username and password for MySQL connection is correct.

HeidiSQL will not only allow you to run commands, but also lets you change the password for any database user directly from a graphical interface (located under Tools/User manager):

11-4-2014 10-25-05 AM

 

 

 

Comments

comments