Fix PHP 7.4 + MySQL 8 errors with server has gone away
PHP 7.4 is now released ???, and it comes with support for MySQL 8's new password authentication plugin: caching_sha2_password
.
If you have been using MySQL 8 with a PHP version older than 7.3, you probably were authenticating to the database using mysql_native_password
. With the upgrade to 7.4, PHP natively supports the new default authentication plugin caching_sha2_password
. The two following errors might pop up if you have been using MySQL 8 with PHP 7.3 or older, and using mysql_native_password
plugin to deal with PHP's then-lack of support for the new caching_sha2_password
plugin.
Unexpected server response while doing caching_sha2 auth: 109
mysqli_real_connect(): (HY000/2006): MySQL server has gone away
This post is to provide some quick information on how to fix this.
From PHP 7.4, PHP natively supports caching_sha2_password
. The easiest solution is to change the existing users that you connect to the database to use the new plugin.
1. Connect to the database as root
Depending on your root username, you will be prompted to enter the password, a hostname, etc.
By default, typing mysql
in your server terminal should work. If you have trouble logging in, try mysql -p -u root
, and entering the root password when asked.
2. Check existing authentication plugin:
Replace USERNAME
and YOUR_PASSWORD
with your application database username and the password.
mysql> SELECT user,plugin from mysql.user;
Sample output
+------------------+-----------------------+
| user | plugin |
+------------------+-----------------------+
| Ayesh | caching_sha2_password |
| debian-sys-maint | mysql_native_password |
| mysql.infoschema | caching_sha2_password |
| mysql.session | mysql_native_password |
| mysql.sys | caching_sha2_password |
| root | auth_socket |
| testuser | mysql_native_password |
+------------------+-----------------------+
If you see mysql_native_password
next to the database user's name, you can now change it to caching_sha2_password
.
3. Change MySQL authentication plugin to caching_sha2_password
mysql> ALTER USER 'USERNAME'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'YOUR_PASSWORD';
mysql> FLUSH PRIVILEGES;
This will change the user USERNAME's authentication plugin to caching_sha2_password
with YOUR_PASSWORD.