Unable to log in with MySQL 8.0.11; caching_sha2_password authentication method unknown to the client

[ibennetch]

With MySQL 8.0.11 I'm unable to log in with users that had been working fine under previous versions (at least 8.0.0, perhaps even more recent than that).

mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]

mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client

See also https://dev.mysql.com/doc/refman/8.0/en/authentication-plugins.html

I'm using QA_4_8.

[ibennetch]

See also https://stackoverflow.com/questions/49948350/phpmyadmin-on-mysql-8-0

[ibennetch]

Possibly related: #14217

[ibennetch]

A point release seems like a bad time to make changes to the authentication protocols. I've been testing with 8.0.4-rc which works fine, and now that 8.0.11 is out it causes problems.

[Gemorroj]

https://bugs.php.net/bug.php?id=76243

[ozeer]

I met this issue a few days before, is there anyone who solve this problem? thanks a lot~

[MauricioFauth]

There is not much we can do about it right now, as apparently PHP still does not support the caching_sha2_password authentication method.

However, I was able to connect successfully using other authentication methods such as mysql_native_password and sha256_password.

[szabacsik]

https://stackoverflow.com/questions/49948350/phpmyadmin-on-mysql-8-0/50437307#50437307

[MauricioFauth]

PHP developers started adding caching_sha2_password support. Once a version of PHP with this support is released, we can add support for caching_sha2_password on our side as well.

[rmanibus]

Upgrading to PHP7.2.7 solved the issue for me.

[donpaul120]

@rmanibus i have php 7.2.7 and mysql Ver 8.0.11 but im wondering why it doesn't work still.

[Apollo725]

I expired root user and reinstall all of the mysql workspace, then I got this error.
Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /Volumes/Workspace/workspace/test/php_test/Simple-PHP-Login/add.php on line 3

I was using mac and didn't use xampp.

[ggets]

php-fpm 7.2.11 and mysql 8.0.12 both on centos 7.5.1804 x64 and not working with phpmyadmin 4.8.3

[MauricioFauth]

As mentioned earlier, there is not much we can do about it at this time.
The mysqlnd library does not support the caching_sha2_password authentication method yet.
An alternative would be to use the libmysqlclient library, however PHP does not support the libmysqlclient library of MySQL 8 yet.

So the real alternative, for now, is to use another authentication method other than caching_sha2_password.

[SugarD-x]

I'm not sure if this helps anything, but on MySQL Server 8.0.12, I'm getting this error ONLY when upgrading to PHP 7.2.11. (It doesn't just affect PHPMyAdmin). When I revert back to PHP 7.2.8, I have no problems. Seems a bit odd that a newer version of PHP would cause this.

I know downgrading PHP is probably not the greatest idea, but if this helps anyone as a temporary solution, it is there.

[ibennetch]

@yanickrochon It's odd to me that you'd see the same message repeated so many times. I suggest clearing your phpMyAdmin cookies (they start with 'pma_') or trying with private browsing mode; you can also try with a minimal config.inc.php file and see if that improves the situation. You could try logging in directly as the pma user, to make sure that part works correctly as well.

[wildone]

docker container still on 7.2 :(

[ibennetch]

Max, I’m glad you mentioned that. I noticed it earlier today and plan to upload a fix soon. See phpmyadmin/docker#258 to monitor the issue.

[SugarD-x]

From what I'm hearing from multiple sources, this was fixed in PHP 7.4.0, however after upgrading, I'm getting the caching_sha2_password issues again. I'm at a loss as to why PHP and MySQL refuse to communicate through this authentication method, even in a version that supposedly supports it. I can't even get into PHPMyAdmin to see what is going on either. It works fine in PHP 7.2.8, (the last one to support it until 7.4.0).

I'm beginning to think this isn't actually fixed like PHP claims.

[ibennetch]

SugarD-x, could you send us a list of your exact PHP, MySQL, and phpMyAdmin versions - or screenshot from the home page that shows that information (obscuring anything sensitive like the server hostname)?

[SugarD-x]

PHP: 7.4.0
MySQL: Community Server 8.0.18
PHPMyAdmin: 4.9.2

They are all the latest production versions as of this posting. I upgraded everything except PHP and it was still working fine. (I was on PHP 7.2.8 prior to this issue, which was the last version before PHP 7.4.0 to support caching_sha2_password). As soon as I updated PHP, the issues popped up.

I've literally spent hours trying to hunt down the cause to no avail. MySQL's Command Line is reporting that my root account is still using caching_sha2_password properly.

The only reference I can find online of any issues with PHP 7.4.0 and caching_sha2_password was this answer here, under which I fall into the category of point 2, which references "possible issues":
https://stackoverflow.com/a/59239183

Nothing came up as reported on PHP's bug tracker.

[SugarD-x]

Looks like I finally got an answer. It is not officially "fully" supported as of PHP 7.4.0 yet. Looks like I'll have to revert back to PHP 7.2.8 until the next release.

php/php-src@4f06e67#commitcomment-36398518

[SugarD-x]

Just a quick heads up for everyone, this was not fixed in PHP 7.4.1, as originally planned. I'm still waiting for them to update the tag on the issue too. (They haven't created one for PHP 7.4.2 yet, and PHP 7.4.1 hasn't been listed as released online on the main website either. It is only available on the Windows PHP one right now).

[cplaiu]

PHP 5.4.16
MySQL: Community Server 8.0.18
PHPMyAdmin: 4.4.15.10
Centos: 7

I added default-authentication-plugin=mysql_native_password to the file /etc/my.cfg and use ALTER USER root IDENTIFIED WITH mysql_native_password BY 'PASSWORD'; but still not work.

[SugarD-x]

You need to change "PASSWORD" to the password you want. You just changed your root password literally to "PASSWORD" without the quotes if you used that command as is.

[ibennetch]

cplaiu That is a generic error message that doesn’t apply to this specific solution. Are you sure the MySQL server process is running? Can you connect from the command line client? Perhaps try changing between host “localhost” and “127.0.0.1” in config.inc.php (in case your user has access through one connection type but not the other).

[cplaiu]

You need to change "PASSWORD" to the password you want. You just changed your root password literally to "PASSWORD" without the quotes if you used that command as is.

It's what I do.

ALTER USER root@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';

cplaiu That is a generic error message that doesn’t apply to this specific solution. Are you sure the MySQL server process is running? Can you connect from the command line client? Perhaps try changing between host “localhost” and “127.0.0.1” in config.inc.php (in case your user has access through one connection type but not the other).

Yes I'm sure that mysql process is running, because I can connect from the CLI. I try to change “localhost” to 127.0.0.1”.

netstat -atnlp|grep -i "3306|mysql" returns:

tcp6 0 0 :::33060 :::* LISTEN 27697/mysqld
tcp6 0 0 :::3306 :::* LISTEN 27697/mysqld

I try to bind-address=0.0.0.0 in /etc/my.cfg

But still not working.

[ibennetch]

We've added a FAQ about this (1ae14b5), and since this isn't a bug with phpMyAdmin I'm going to suggest we close this issue.