Laravel and MySQL 8: Fixing MySQL Server Has Gone Away Error

Laravel and MySQL 8: Fixing MySQL Server Has Gone Away Error

Laravel and MySQL 8: Fixing MySQL Server Has Gone Away Error

If you’ve tried to upgrade your Laravel applications to use MySQL 8, you might have run into the following error that left you scratching your head:

SQLSTATE[HY000] [2006] MySQL server has gone away 

The manual has an outdated (but still relevant explanation):

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server’s default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release. In the meantime, the mysql_xdevapi extension does support it.

We should expect a future version of PHP to have support for caching_sha2_password authentication. However, in the meantime, you can fix it quickly with the following changes:

First, we need to find the paths MySQL will look for a configuration file. We can find out by running mysql --help. The command prints out a lot of info, but you’re looking for something like the following:

mysql --help ... Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf 

Take the paths from your command output and adapt the following with the paths you get from mysql --help:

ls -la \ /etc/my.cnf \ /etc/mysql/my.cnf \ /usr/local/etc/my.cnf \ ~/.my.cnf ls: /etc/my.cnf: No such file or directory ls: /etc/mysql/my.cnf: No such file or directory -rw-r--r-- 1 paul staff 61 Dec 5 19:40 /Users/paul/.my.cnf -rw-r--r-- 1 paul admin 113 Oct 28 2017 /usr/local/etc/my.cnf 

I like to manage the file from ~/.my.cnf, but unless you’ve previously done that, you’ll probably have to create the file if you want to manage configuration from that path.

I suggest you use the ~/.my.cnf path, but whatever path you determine you need to add the following:

[mysqld] default_authentication_plugin=mysql_native_password 

If you’re using Homebrew and you want to update the /usr/local/etc/my.cnf file, add the following at the end of the file under [mysqld]:

# Default Homebrew MySQL server config [mysqld] # Only allow connections from localhost bind-address = default_authentication_plugin=mysql_native_password 

Last, you need to restart MySQL using whatever method is appropriate for your system. If you’re using Homebrew on a Mac, you can run brew services:

brew services restart mysql 

If you’re using Docker, here’s an example Docker Compose configuration to get MySQL 8 working with Laravel and other PHP projects:

services: # ... mysql: image: mysql:8.0 # PDO Doesn't support MySQL 8 caching_sha2_password Authentication # @see command: --default-authentication-plugin=mysql_native_password ports: - 13306:3306 volumes: - mysql:/var/lib/mysql environment: MYSQL_DATABASE: my_database MYSQL_ROOT_PASSWORD: root volumes: mysql: driver: "local" 

With that change in place, you should be able to connect to MySQL 8 from PHP applications!

Filed in: News

Enjoy this? Get Laravel News delivered straight to your inbox every Sunday.

No Spam, ever. We’ll never share your email address and you can opt out at any time.


via Laravel News

December 6, 2019 at 09:29AM