Fix PHP 7.4 + MySQL 8 errors with server has gone away

Published On03 Dec 2019

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.

Recent Articles on PHP.Watch

All ArticlesFeed 
How to fix `mysql_native_password` not loaded errors on MySQL 8.4

How to fix mysql_native_password not loaded errors on MySQL 8.4

How to fix the SQLSTATE[HY000] [1524] Plugin 'mysql_native_password' is not loaded errors caused in MySQL 8.4 no longer enabling the mysql_native_password plugin by default.
How to fix PHP Curl HTTPS Certificate Authority issues on Windows

How to fix PHP Curl HTTPS Certificate Authority issues on Windows

On Windows, HTTPS requests made with the Curl extension can fail because Curl has no root certificate list to validate the server certificates. This article discusses the secure and effective solutions, and highlights bad advice that can leave PHP applications insecure.
AEGIS Encryption with PHP Sodium Extension

AEGIS Encryption with PHP Sodium Extension

The Sodium extension in PHP 8.4 now supports AEGIS-128L and AEGIS256 Authenticated Encryption ciphers. They are significantly faster than AES-GCM and CHACHA20-POLY1305. This article benchmarks them and explains how to securely encrypt and decrypt data using AEGIS-128L and AEGIS256 on PHP.
Subscribe to PHP.Watch newsletter for monthly updates

You will receive an email on last Wednesday of every month and on major PHP releases with new articles related to PHP, upcoming changes, new features and what's changing in the language. No marketing emails, no selling of your contacts, no click-tracking, and one-click instant unsubscribe from any email you receive.

Support PHP.Watch — If you find the articles, version information, Codex, and other PHP.Watch contributions useful, consider supporting through GitHub Sponsors. Your sponsorship helps dedicate more time to creating valuable content and improving the PHP community. Together, we can keep the momentum going — thank you for your support!

Thanks to the highest tier sponsor: @TomasVotruba for your generous support to keep PHP.Watch moving 💜