PDOException::("PDO::__construct(): Unexpected server respose while doing caching_sha2 auth: 109") 使用 MySQL 8 / PHP 7.2 / Larave

PDOException::("PDO::__construct(): Unexpected server respose while doing caching_sha2 auth: 109") with MySQL 8 / PHP 7.2 / Laravel

提问by Juliatzin

I just installed my dev environnement.

我刚刚安装了我的开发环境。

When I try to connect mysql db via SequelPro, I get:

当我尝试通过 SequelPro 连接 mysql db 时，我得到：

Authentication plugin 'caching_sha2_password' cannot be loaded

As stated in: Authentication plugin 'caching_sha2_password' cannot be loaded, I ran:

如：无法加载身份验证插件“caching_sha2_password”，我运行：

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

Then I could connect my DB via SequelPro

然后我可以通过 SequelPro 连接我的数据库

But when I execute Laravel Command:

但是当我执行 Laravel 命令时：

php artisan migrate

I get:

我得到：

PDOException::("PDO::__construct(): Unexpected server respose while doing caching_sha2 auth: 109")

What should I do now ?

我现在该怎么办 ？

PD: I use Laravel Valet on Mac, and Laravel 5.6.

PD：我在 Mac 上使用 Laravel Valet，而 Laravel 5.6。

回答by abkrim

You must alter use in your laravel app via shell with mysql command

您必须使用 mysql 命令通过 shell 更改在 Laravel 应用程序中的使用

ALTER USER 'user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your password';
ALTER USER 'user'@'%' IDENTIFIED WITH caching_sha2_password BY 'your password';

回答by Zenith

the problem is good for safety, mybe.

这个问题对安全有好处，我的。

but I use the command solve the problem:

但我使用命令解决问题：

1. ALTER USER root@localhostIDENTIFIED WITH caching_sha2_password BY 'password';
2. restart mysql
3. try now

1. ALTER USER root@ localhostIDENTIFIED WITH caching_sha2_password BY 'password';
2. 重启mysql
3. 现在试试

Hope you will not see this error again.

希望您不会再看到此错误。

回答by Christopher

After using $ mysql -uUser -pPa$$w0rdthe MySQL connection could be opened with PHP using mysqli_real_connect()or PDO

使用后$ mysql -uUser -pPa$$w0rd可以用PHP usingmysqli_real_connect()或PDO打开MySQL连接

Make sure that the User has caching sha2 password active, not a native password. (PHP 7.2.9-1, MySQL 8.0.12)

确保用户的缓存 sha2 密码处于活动状态，而不是本机密码。（PHP 7.2.9-1，MySQL 8.0.12）

When changing to a native password PHP says

当更改为本机密码时 PHP 说

mysqli::real_connect(): Unexpected server respose while doing caching_sha2 auth: 109

mysqli::real_connect()：在执行 caching_sha2 auth 时意外的服务器响应：109

With caching sha2 password active i always get:

随着缓存 sha2 密码处于活动状态，我总是得到：

mysqli::real_connect(): (HY000/1045): Access denied for user 'User'@'localhost' (using password: YES)

mysqli::real_connect(): (HY000/1045): 用户 'User'@'localhost' 访问被拒绝（使用密码：YES）

So tried to login from Shell using $ mysql, which succeeded, after this, i was able to connect with PHP. Seems like a Bug in the PHP mysqlnd.

所以尝试从 Shell 登录$ mysql，成功了，在此之后，我能够与 PHP 连接。似乎是 PHP mysqlnd 中的一个 Bug。

Implementing a shell_exec('mysql -u'.$user.' -p"'.$password.'" -e quit > /dev/null 2>&1');right before opening the MySQL connection, solves the issue, by now, for me.

shell_exec('mysql -u'.$user.' -p"'.$password.'" -e quit > /dev/null 2>&1');在打开 MySQL 连接之前实现一个权限，现在对我来说解决了这个问题。

Rebooting or restarting the MySQL Service, resets the cached password and the access denied message will pop again.

重新启动或重新启动 MySQL 服务，重置缓存的密码，访问被拒绝消息将再次弹出。

Hope this helps.

希望这可以帮助。

Greetings

你好

回答by rowad

there is a way to solve this,

有办法解决这个问题

1. remove MySQL
2. Install it and choose use legacy authentication method ( Retain MySQL 5.x compatibility )

1. 删除 MySQL
2. 安装它并选择使用旧的身份验证方法（保留 MySQL 5.x 兼容性）