Connect error 2054 the server requested authentication method unknown to the client

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.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

It seems as if with PHP 7.4 the issue is solved.

The following minimalist Docker set up does the trick without even touching the configuration in my.cnf.

docker-compose.yml:

version: "3.6"

services:
  php_fpm:
      build: .
      container_name: cheetah_php_fpm
      working_dir: /cheetah
      volumes:
          - .:/cheetah
          - ./docker/php/php.ini:/usr/local/etc/php/php.ini

  mysql:
      image: mysql:8.0
      container_name: cheetah_mysql
      volumes:
          - ./docker/mysql/conf.d/mysql.cnf:/etc/mysql/conf.d/mysql.cnf
      ports:
          - "3306:3306"
      environment:
          - MYSQL_DATABASE=${DB_DATABASE}
          - MYSQL_ROOT_PASSWORD=${DB_PASSWORD}

Dockerfile:

FROM php:7.4-fpm

RUN apt-get update && apt-get install -y 
    git

RUN docker-php-ext-install pdo_mysql

RUN curl --silent --show-error https://getcomposer.org/installer | php && 
    mv composer.phar /usr/local/bin/composer

RUN pecl install mailparse

docker/mysql/conf.d/mysql.cnf:

# Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA

#
# The MySQL  Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysql]