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.

Weakminded

I have the same problem. :/ Did you fix it? – Chinn

Yep. Downgrading mysql to previous version – Weakminded

I fixed it with setting default auth method mysql_native_password in my.cnf – Chinn

Answer 1

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

Melindamelinde

Answer 2

the problem is good for safety, mybe.

but I use the command solve the problem:

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

Hope you will not see this error again.

Rickart

Answer 3

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

Dougdougal

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. – Brink