mysqli是否在PHP 7.4中支持caching_sha2_password? [英] Does mysqli have support for caching_sha2_password in PHP 7.4?

问题描述

当我尝试从PHP 7.3升级到PHP 7.4时，收到此错误:

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

执行caching_sha2身份验证109时服务器意外响应

Unexpected server response while doing caching_sha2 auth 109

正如我所看到的，这表明PHP 7.4 MySQLi正在尝试使用caching_sha2_password插件. 本文指出PHP MySQLi不支持该插件(它也暗示了对它的未来支持)，但是由于PHP 7.4是新的并且似乎正在尝试使用它，因此我认为它应该可以工作.错误消息也与不支持的错误消息不同(拒绝访问与身份验证方法未知).

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).

因此，我将MySQL身份验证插件更改为caching_sha2_password(使用与以前相同的密码):

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:

拒绝用户'root'@'localhost'的访问(使用密码:是).

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

切换回PHP 7.3和mysql_native_password后，它将再次起作用.

Switching back to PHP 7.3 and mysql_native_password it works again.

我对两个插件使用了相同的密码，对相同的网站使用了相同的密码，并对php.ini进行了相同的更改. 但是，我没有更改任何mysqli配置. MySQL的日志未显示任何内容，而apache2日志仅显示访问被拒绝"错误消息.

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.

php7.4-mysqli是否支持caching_sha2_password? 是

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

为什么我的密码被拒绝以及如何解决? 此外，如果MySQLi仍然不支持该插件:我该如何使用mysql_native_password?

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

推荐答案

我对此进行了测试，PHP 7.4中的mysqli确实支持caching_sha2_password.

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 )

这是我编写的一个简单的PHP脚本，用于连接在docker容器(使用端口6603)中运行的MySQL 8.0.17:

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";

我确认我的MySQL实例正在使用caching_sha2_password:

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           |
+---------------------------------+

运行我的PHP脚本，成功了.

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警告:mysqli :: __ construct():执行caching_sha2身份验证时服务器意外响应:在第5行的/Users/bkarwin/Documents/SO/my.php中为0

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.

请勿使用空密码.

这篇关于mysqli是否在PHP 7.4中支持caching_sha2_password?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！