8

When I tried upgrading from PHP 7.3 to PHP 7.4, I received this error:

Unexpected server response while doing caching_sha2 auth 109

As I see it, this indicates that PHP 7.4 MySQLi is trying to use the caching_sha2_password plugin. This article points out that PHP MySQLi does not support the plugin (it also hints future support for it), but since PHP 7.4 is new and seems to be trying to use it, I guess it should work. Also the error message is different, than if it wasn't supported (access denied vs. authentication method unknown).

So I changed my MySQL authentication plugin to caching_sha2_password (using the same password as before):

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '';
FLUSH PRIVILEGES;

But this caused another error:

Access denied for user 'root'@'localhost' (using password: YES).

Switching back to PHP 7.3 and mysql_native_password it works again.

I used the same password for both plugins, the same website and applied the same php.ini changes. I however did not change any mysqli configuration. The logs for MySQL show nothing, the apache2 log only shows the 'Access Denied' error message.

Does php7.4-mysqli have support for caching_sha2_password? YES

Why is my password being denied and how can I fix it? See my follow-up question

Also, if MySQLi still doesn't support the plugin: How can I use mysql_native_password with it?

Minding
  • 1,383
  • 1
  • 17
  • 29

2 Answers2

7

I tested this, and mysqli in PHP 7.4 does support the caching_sha2_password.

php -v
PHP 7.4.0 (cli) (built: Nov 29 2019 16:18:44) ( NTS )

Here's a simple PHP script I wrote to connect to MySQL 8.0.17 running in a docker container (using port 6603):

<?php
error_reporting(E_ALL);
$conn = new mysqli('127.0.0.1', 'root', 'root', '', 6603);
$result = $conn->query("SELECT NOW()");
$now = $result->fetch_row()[0];
echo "$now\n";

I confirmed that my MySQL instance is using caching_sha2_password:

mysql> select user,host,plugin from mysql.user where user='root';
+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
| root | %         | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------+-----------+-----------------------+

mysql> select @@default_authentication_plugin;
+---------------------------------+
| @@default_authentication_plugin |
+---------------------------------+
| caching_sha2_password           |
+---------------------------------+

Running my PHP script, it was successful.

php my.php
2019-12-08 18:11:58

However, when I tried to change the password to '' like you did, I was able to reproduce the same error:

PHP Warning: mysqli::__construct(): Unexpected server response while doing caching_sha2 auth: 0 in /Users/bkarwin/Documents/SO/my.php on line 5

When I restored the password, setting it to a non-blank string, it fixed the issue.

Don't use blank passwords.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Interesting. I didn't use a blank password (sry, should have made that clearer). But knowing that it can work already helps. I'll try again, see if I can avoid `Access Denied` and then come back to you. – Minding Dec 08 '19 at 19:14
  • You set a blank password with `ALTER USER ... BY ''`. When I tried the same thing, it set a blank password. – Bill Karwin Dec 08 '19 at 19:20
  • `... BY '';` was just the shortend version, I actually entered `... BY 'mySecretPassword';` – Minding Dec 08 '19 at 22:16
  • I tried again, and I still have the same issue `Access Denied` eventhough I literally copy the password over. My PHP was actually build one day before yours: `PHP 7.4.0 (cli) (built: Nov 28 2019 07:27:06) ( NTS )` maybe it's just a bug? – Minding Dec 08 '19 at 22:29
  • 1
    I can't reproduce that, so I don't know. I really hope the _same_ PHP version compiled one day apart doesn't make a difference. – Bill Karwin Dec 09 '19 at 15:10
  • Did you use `mysqlnd` or `mysql_client`? (see Machavity's answer) – Minding Dec 12 '19 at 15:37
  • `php -i` tells me it's mysqlnd. – Bill Karwin Dec 12 '19 at 17:30
  • Would be nice, if you could check out [my follow-up question](https://stackoverflow.com/questions/59432704/php-7-4-mysql-caching-sha2-password-randomly-denying-passwords). – Minding Dec 21 '19 at 00:43
3

There's a couple of ways this could happen

  1. You're using the mysql_client method of authentication. This is how PHP used to do it natively, by connecting to MySQL through the MySQL CLI interface. MySQL's own client will always support its own authentication methods.

  2. You're using MySQLND under 7.4. Apparently this was an improvement to the native driver that went unannounced

    Yes, this is only in 7.4 right now, due to the hard dependency on ext/hash.

    There are still some possible issues with it.

Machavity
  • 30,841
  • 27
  • 92
  • 100
  • What really confuses me, is that everything works fine on the transmission part,but my password gets denied. Eventhough I literally copy paste it for `mysql_native_password` and `caching_sha2_password` and only the first one works. – Minding Dec 08 '19 at 22:24
  • Do you know how to switch between native driver and mysql client? – Minding Dec 09 '19 at 15:58
  • Depends how your server is set up. The [PHP Manual](https://www.php.net/manual/en/mysqlinfo.library.choosing.php) goes over some of the common setups. – Machavity Dec 09 '19 at 16:01
  • Thanks, I am using `mysqlnd`, which might be the issue here so I guess I'll wait for full support. – Minding Dec 09 '19 at 16:13
  • Would be nice, if you could check out [my follow-up question](https://stackoverflow.com/questions/59432704/php-7-4-mysql-caching-sha2-password-randomly-denying-passwords). – Minding Dec 21 '19 at 00:52