How To Fix MySQL error “mysql server has gone away”
You usually get the “mysql has gone away” error while trying to execute huge queries in mysql. In my case, I was trying to import data consisting of over 1,000 records into a table and got the error.
The cause of the error is that the default setting of max_allowed_packet in your mysql config is very low. The default value is 16M, which means 16Megabytes. Basically if the size of your query exceeds this value, you’ll get a mysql has gone away error thrown in your face.
How To Fix “MySQL Has Gone Away”
All you need to do is edit the MySQL configuration file. The location of this file depends on your server, I will explain in a bit.
There are two ways to increase the max_allowed_packet value. You can either edit the configuration file and make it a permanent setting, or change the setting for only that session if you only want to increase it temporarily.
Change The Value For One Session
SET GLOBAL max_allowed_packet=100M
OR
SET GLOBAL max_allowed_packet=104857600
You can simply add the line above to the top of your sql query if you need to run it for one query. You could also run the query on its own in mysql. Note that you may have to disconnect and reconnect to mysql if it doesn’t work at first.
Editing The MySQL Configuration File
The location of this file depends on your server, and probably version of mysql or mariadb
XAMPP: xampp/mysql/bin/my.ini
Centos 7 / MariaDB: etc/my.cnf
Ubuntu / MySQL: /etc/mysql/my.cnf
If you know of any others, let me know in the comments, so I can include it, thanks.
Look for [mysqld], under it, find max_allowed_packet = xxx and increase its value to 100M
Important: You should only edit the max_allowed_packet line that you’ll find under [mysqld], don’t touch the one under [mysqldump].
Also, your my.cnf file might look scanty, something like this:
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
The !includedir line is supposed to point to another directory containing the actual configuration file, so navigate to that directory to find your file and edit your config from there. In my case, I found my config file in /etc/my.cnf.d/mysqld.cnf
[mysqld]
max_allowed_packet = 100M
Restart apache.
