Oops! MySQL just showed the error ‘The server requested authentication method unknown to the client‘.
Usually, the error shows up when a user tries to connect to the database.
The reason for this error is improper authentication plugin settings in MySQL.
At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.
Today, let’s see how our Support Engineers fix this error.
How does the MySQL server authenticate a client?
A PHP application connects to the MySQL server using the username provided by the server/hosting provider.
The server validates the user and returns the connection status. MySQL uses caching_sha2_password and auth_socket plugins for validation.
The caching_sha2_password plugin uses an SHA-2 algorithm with 256-bit password encryption. MySQL 8 prefers this auth method.
Whereas the auth_socket plugin checks if the socket username matches with the MySQL username. If the names don’t match, it checks for the socket username of the mysql.user. If a match is found, the plugin permits the connection.
But to serve the pre 8.0 clients and avoid compatibility errors, it is preferred to revert back the auth method. Older versions of MySQL use mysql_native_password plugin for validation.
Why does MySQL show the authentication method unknown to the client?
Recently one of our customers approached us with a MySQL auth error. The error showed up in MySQL 8. But the user had a PHP version 7.0. The default authentication plugin used by the MySQL is auth_socket.
Here MySQL client like PHPMyAdmin authenticates the user to login to the database by a password. Hence when a user tries to access the database using PHPMyAdmin ends up in the auth error. But the actual reason was compatibility error.
The error message is ‘The server requested authentication method unknown to the client‘. In PHPMyAdmin, the error appears as,
Now let’s see how our Support Engineers fix this error.
How we fix the error authentication method unknown to the client?
To fix this error we changed the default authentication plugin used by MySQL. For this, we logged in to MySQL as the root user. Then we run the command,
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';Here we replaced the ‘password‘ with the password of the root user. If the database user is not root user, replace the username and password respectively.
Finally, this fixed the error. And the user was able to log in to PHPMyAdmin successfully.
But this is a temporary fix as the MySQL 8 uses PHP 7.0. The preferred auth plugin for this version is caching_sha2_password.
Hence we also recommend the users to upgrade the pre 8.0 clients to avoid further errors.
[Still, having trouble in fixing MySQL errors? – We can help you.]
Conclusion
In short, MySQL error the server requested authentication method unknown to the client occurs due to the default authentication plugin used. Today, we saw how our Support Engineers changes this to fix the error.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
As every developer will know (or is learning) – mysqli_connect() can be a finicky thing to work with despite it’s undoubted power. It’s finicky because it hides a lot of complexity under the hood. Most of the time that’s amazing – however, one time it is not amazing is error time where it cause a ton of frustration!
The error we are looking at today is:
mysqli_connect(): The server requested authentication method unknown to the clientCause
The cause of this error is quite simple in many ways and occurs as you are trying to authenticate (or provide your credentials) to the mysql_connect() function. This is obviously critical to any application development using mySQLi as without establishing the connection you can do nothing with the database – whether that be read, insert, update or delete.
At it’s most fundamental, mySQLi is trying to communicate to you hear that the authentication method you are trying to use is not understood (unknown) to the client and therefore cannot be used to connect.
It is NOT(!) saying that the credentials you are providing for your user or database name etc are incorrect (they could be, but you’ll only know this AFTER you fix this first issue.
Essentially, you’re at a front door and trying to use a keycard to let you in a traditional key lock entry. You might in theory be authorised to go in where you want to however the way you’re trying to get in isn’t correct. It’s the same whether we’re talking houses in the real world or, in this case, the digital front door to your mySQLi database.
Default Configuration
The default configuration that PHP uses is “mysql_native_password”. You can very this in your server.ini file:
[mysqld]
# The default authentication plugin to be used when connecting to the server
default_authentication_plugin=mysql_native_passwordAll is well using the default, technically speaking, and has been in use since MySQL v4.1. So why are people trying to change something so frequently if it’s not broken? As the old adage goes, “if it isn’t broke, don’t fix it”…
It is broke (security wise!)
However, mysql_native_password isn’t considered secure enough by many for sensitive data anymore so, unsurprisingly, many developers attempt to change away from this dated default authentication method and implement a newer, more secure, default authentication plugin. After all, most database driven websites and apps are handling sensitive data and with the number of data breaches going through the roof in the recent decade it’s very wise to look to prevent any issues before they occur.
And it’s not just MySQL – MariaDB have implemented scary warnings to encourage developers to move away – “It is not recommended to use the mysql_native_password authentication plugin for new installations that require high password security. If someone is able to both listen to the connection protocol and get a copy of the mysql.user table, then the person would be able to use this information to connect to the MariaDB server.”
For many, this is where the issues with the dreaded ‘authentication method unknown’ error begins. As you can see MariaDB recommends the ed25519 plugin. MySQL have, since version 8.0, implemented SHA256 as the implementation of choice using either caching_sha2_password or sha256_password as the default authentication method.
Caching_SHA2_Password
Once you’ve updated your server.ini file to use caching_sha2_password it should look something like this:
[mysqld]
# The default authentication plugin to be used when connecting to the server
default_authentication_plugin=caching_sha2_password
And… this is where your errors have started. Quite simply, as explained by MySQL at the launch of v8.0 – not all clients/connectors supported caching_sha2_password at the time. Fortunately, in the time since the blog post, client/connector support has increased however this error can still be encountered in one of the two following circumstances:
- You’re using a client/connector that still doesn’t support caching_sha2_password
- You’re using an outdated client/connector.
The Solution
You could go back to the default mysql_native_password – that will work, but as discussed above, will leave you with the underlying security issues. A better idea is to upgrade your PHP version as PHP does now support caching_sha2_password. Many websites out there will still be running PHP 7.2.3 or below. In addition to getting access to patches and new features, upgrading will allow you to use caching_sha2_password as your authentication method.
Without changing your configuration, if you upgrade to PHP 7.4 this should instantly resolve your issue – as long as you remember to compile PHP with the mysqli extension of course! As it was introduced in MySQL v8.0 this also needs to be your minimum version there.
Summary Checklist
- Upgrade PHP to version 7.4 or above (v8.1.5 is the latest at the time of writing).
- Ensure you are running MySQL version 8.
- Change your server configuration file (server.ini) to use the caching_sha_2_password plugin for enhanced security over mysql_native_password
Hope that helps! Let us know in the comments below and post any questions you may have. Happy coding!
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 restartNow 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 PrivilegesThose 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.