[SOLVED] ERROR 2006 (HY000): MySQL server has gone away

This article provides the solution to the following MySQL error:

ERROR 2006 (HY000): MySQL server has gone away

We generally get this kind of error when we try to import or insert a large volume of data into the MySQL database.

To solve this error, we can:

– either update the MySQL’s configuration file: my.cnf
– or, update the global variables directly by logging into the MySQL server

Update my.cnf file

  • Open terminal
  • Find the my.cnf file by running the following command:

mysql --help | grep my.cnf
  • This should output something like this:

order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
  • Open your my.cnf file in any editor and add/update the value of the following variables:

[mysqld]
max_allowed_packet= 1024M
  • In your my.cnf file, you may also try increasing the wait_timeout value:

[mysqld]
max_allowed_packet= 1024M
wait_timeout= 60000
  • Restart MySQL server

LINUX


sudo /etc/init.d/mysqld restart

//OR
sudo service mysqld restart

//OR
sudo service mysql restart

MACOS


sudo /usr/local/mysql/support-files/mysql.server restart

Now, you should be able to work fine with large MySQL datasets.

Update the global variables directly by logging into the MySQL server

First of all, you need to restart the MySQL server. You can run the commands listed above to restart the MySQL server.

After that, you can check the list of all the MySQL Global Variables and their values with the following command:


$> mysqladmin variables -u YourMysqlUsername -p

You can also check for these variables value by first logging into MySQL server:


$> mysql -u YourMysqlUsername -p

mysql> SHOW VARIABLES;

To check specific variable value:


mysql> SHOW VARIABLES LIKE 'max_allowed_packet';

To solve MySQL Server Gone Away error, you need to increase the value of max_allowed_packet variable. To do so, you have to run the following command:


mysql> SET GLOBAL max_allowed_packet=1072731894;

After that, quit MySQL login:


mysql> quit

Now, when you again login to MySQL and check for the max_allowed_packet value, you should see the updated value.


$> mysql -u YourMysqlUsername -p

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

Now, you should be able to work fine with large MySQL datasets.

Hope this helps. Thanks.