Does mysqli have support for caching_sha2_password in PHP 7.4?

Question

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?

Answer 1

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.

Answer 2

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.