32

I'm using php 7.2.2 and mysql 8.0.

When I try to connect with the right credential I get this error:

PDOException::("PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]")

Need help to troubleshoot the problem.

Michele Carino
  • 1,043
  • 2
  • 11
  • 25
  • 1
    MySQL 8.0 isn't production ready jet. PHP devs might have not implemented the MySQL's 8.0 connection protocol ( auth with caching_sha2_password ) within the newer PHP versions. You might be able to fix this problem if you configure MySQL 8.0 to run in mysql_native_password mode. – Raymond Nijland Mar 03 '18 at 12:13
  • That's ok. It seems there is no option mysql_native_password=1 configurable into mycnf, also using it from mysql client is possible by command line, and seems also available in pdo as option. Just cannot figure out how to compose things together. – Michele Carino Mar 03 '18 at 13:18
  • In MySQL 8, a new auth plugin was introduced: caching_sha2_password. That plugin does NOT work with PHP because the PHP's mysqlnd has NOT been updated. PHP 7.2.4 or higher will work with the OLD auth plugin (mysql_native_password) if you convert the mysql user account PHP connects to the db with to the old authentication! ALTER USER 'user_name'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password'; In your /etc/my.cnf file you can add: default_authentication_plugin = mysql_native_password But this default only works for all new accounts created going forward. – James Jul 10 '19 at 01:50

4 Answers4

42
alter user 'username'@'localhost' identified with mysql_native_password by 'password';

As suggested by ailionx here : https://github.com/laradock/laradock/issues/1390

Szekelygobe
  • 2,309
  • 1
  • 19
  • 25
  • 1
    This is what solved for me. – VBobCat Mar 25 '19 at 17:23
  • 4
    That is correct, I had to do a mix of that answer and edit of `[mysqld] default-authentication-plugin=mysql_native_password` in `my.cnf` file as described here https://mysqlserverteam.com/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/ and here https://stackoverflow.com/questions/50026939/php-mysqli-connect-authentication-method-unknown-to-the-client-caching-sha2-pa – nyluje Aug 03 '19 at 11:26
  • I also did the same as @nyluje – Noah Gary Jan 10 '20 at 23:18
31

Found this somewhere, don't remember where (or I would credit), but it works for me until MySQL 8 gets better.

In MySQL shell (command prompt, whatever):

CREATE USER username@localhost identified with mysql_native_password by 'password';

Where username is the username and password is the password for that user.

Then you can add permissions in the shell - or in the Workbench as the user now exists with Authentication Type: Standard.

Reid
  • 3,170
  • 2
  • 23
  • 37
  • Thanks for this! I am tinkering with PHP 7.2.5 along with MySQL 8. Couldn't connect by creating a user through the workbench interface but running this query did the job for me. – Thanos Paravantis May 13 '18 at 22:52
  • Thanks for this, it worked for me on MySQL 8.0.13. – NaijaProgrammer Dec 03 '18 at 11:03
  • This should be the accepted answer. – Larry Turtis Mar 21 '19 at 14:02
  • 2
    Just start your mysql with mysqld --default-authentication-plugin=mysql_native_password --console – TSG anti SO dark forces Apr 18 '19 at 06:57
  • @Reid " And PHP is ready for MySQL's caching_sha2_password." This sentence is wrong/misleading. I advise you to remove that sentence from your answer. In fact, you go on to create a new user using the mysql_native_password plugin, not the caching_sha2_password. PHP does NOT work with the caching_sha2_password plugin, but it does work with the mysql_native_password plugin. References: https://www.php.net/manual/en/ref.pdo-mysql.php https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html – James Feb 28 '20 at 15:24
  • not work for me – iwind Mar 01 '20 at 10:01
  • @TSGantiSOdarkforces Thanks for your comment. Running `mysql.server start --default-authentication-plugin=mysql_native_password --console` solved this for me. – Derek Kurth Apr 23 '21 at 12:13
10

If you want to use MySQL >= 8 and you don't care about caching_sha2_password, just select the second option when you install.

enter image description here

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
D0rm1nd0
  • 1,333
  • 1
  • 15
  • 19
  • 3
    I would choose this answer if you provide instructions to obtain same result using bash on Linux. – Michele Carino Jul 27 '18 at 08:07
  • 1
    After hours of attempting various things, this was the only thing that worked for me – Chris Fremgen Nov 13 '18 at 17:05
  • 3
    METHOD WITHOUT UNISTALLING MYSQL: Just download MySql Installer only and then execute it. Follow the instructions, Select "reconfigure MySql Server" and change the autenthication method to "Use Legacy Auth Method". (you don't need to stop mysql service. easy and fast) – Karmagy Mar 12 '20 at 10:14
8

In order to use mySQL 8 (with the new authentication method) you could just upgrade your PHP.

MySQL 8

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

reference: https://www.php.net/manual/en/ref.pdo-mysql.php

Leonardo Beal
  • 724
  • 11
  • 24
  • 2
    MySQL 8's caching_sha2_password authentication plugin will not work at all with PHP as of yet. PHP 7.2.4 or higher will work with MySQL 8 if you convert the mysql user account PHP uses to the OLD authentication: ALTER USER 'user_name'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password'; Or you can create a new account referencing the Old authentication. The [mysqld] default_authentication_plugin = mysql_native_password directive only affects new accounts. It doesn't affect existing user accounts. – James Jul 10 '19 at 01:56
  • 1
    @James any updates to the status of `caching_sha2_password` in PHP PDO? – Meglio Feb 27 '20 at 04:07
  • @Meglio MySQL is still reporting: " PHP: the PDO_MySQL and ext/mysqli extensions do not support caching_sha2_password." Follow this link https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html and search for PDO. – James Feb 28 '20 at 13:59
  • @Meglio Also, PHP documentation indicates PDO does not support the caching_sha2_password. Reference: https://www.php.net/manual/en/ref.pdo-mysql.php – James Feb 28 '20 at 15:04