How to resolve MySQL server has gone away error

Issue :-

Received below error while trying to import or insert a large volume of data into the MySQL database.

# mysql DB_name < DB_back.sql

Enter password:
--------
ERROR 2006 (HY000) at line 1253: MySQL server has gone away
--------

The list of errors related to “MySQL server has gone away” are as follows:-

Warning: Error while sending QUERY packet

PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

Error Code: 2013. Lost connection to MySQL server during query

To resolve this error, either you will need to update the MySQL’s configuration file (my.cnf) or update global variables directly by logging into the MySQL server.

To update the MySQL’s configuration file (my.cnf), you can follow below steps :-

• SFTP or SSH to the Server
• Locate the my.cnf file by running the following command

# mysql --help | grep my.cnf

Output :-

order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

• Open my.cnf file using any editor and add or update the value of the following variables:

# vi /etc/my.cnf

[mysqld]
max_allowed_packet= 1024M

• Save my.cnf file

• Restart MySQL service to reflect the changes

# sudo systemctl restart mysqld 

OR 

# sudo service mysqld restart

OR 

# sudo /etc/init.d/mysqld restart

OR

# sudo service mysql restart

OR
# sudo /etc/init.d/mysql restart

To update the global variables from MySQL prompt, you can follow below steps :-

• Logging into MySQL server

# mysqladmin variables -u DB_name -p

OR 

# mysql -u DB_name -p

OR 

# mysql

• To check the list of all the MySQL Global Variables and their values, you can run following command

mysql> SHOW VARIABLES;

• To check specific variable value (Here, ‘max_allowed_packet’ variable value), you can run following command

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';

• To resolve this MySQL Server Gone Away error, you will need to increase ‘max_allowed_packet’ variable value. To do the same, you can run following command

mysql> SET GLOBAL max_allowed_packet=1072731894;

• Quit MySQL login

mysql> quit

• Logging again to MySQL server and check the ‘max_allowed_packet’ value

$> mysql -u DB_name -p

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1072731894 |
+--------------------+------------+
1 row in set (0.00 sec)

That’s it. I hope this article will help you to solve these types of errors 🙂