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.