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

Question

I just installed my dev environnement.

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

Authentication plugin 'caching_sha2_password' cannot be loaded

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

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

Then I could connect my DB via SequelPro

But when I execute Laravel Command:

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.

I fixed it with setting default auth method mysql_native_password in my.cnf — Buglinjo
– Buglinjo, Commented Aug 23, 2018 at 18:50

abkrim · Accepted Answer · 2018-12-22 17:19:53Z

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

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

Zenith · Accepted Answer · 2019-06-12 09:57:16Z

the problem is good for safety, mybe.

but I use the command solve the problem:

ALTER USER root@localhost IDENTIFIED WITH caching_sha2_password BY 'password';
restart mysql
try now

Hope you will not see this error again.

Christopher · Accepted Answer · 2018-09-01 20:56:52Z

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

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

When changing to a native password PHP says

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

With caching sha2 password active i always get:

mysqli::real_connect(): (HY000/1045): Access denied for user 'User'@'localhost' (using password: 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.

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.

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

Hope this helps.

Greetings

This is really a "quick and dirty" solution and it's not appropriate for bigger applications. There will probably come an official fix for this.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related