Does mysqli have support for caching_sha2_password in PHP 7.4?
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.
There's a couple of ways this could happen
- 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. 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.