Getting MYSQL Error: "Error Code: 2006 - MySQL server has gone away"

Question

I am getting following error, when I try to import MYSQL database:

Error Code: 2013 - Lost connection to MySQL server during queryQuery:
Error Code: 2006 - MySQL server has gone away

Can someone let me know what is wrong?

Answer 1

Investigation shows many solutions correctly talking about setting the max_allowed_packet and wait_timeout for mysql in my.cnf; small addendum that the default install of mysql on mac osx doesn't appear to include this file. You may first need to create it at /etc/my.cnf (this is only an issue if you're using the default install of mysql instead of a mamp stack or similar)

contents of /etc/my.cnf that corrected this issue for me below:

[mysqld]
max_allowed_packet= 64M
wait_timeout= 6000

Answer 2

Here you can read more about this error and various ways to avoid/solve it

From the docs:

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection

Answer 3

It often happens, if your INSERT query is a too big single line statement with several rows.

Answer 4

Try to restart mysql server. It`s possible that server is not working correctly but sql notifier displaying that is running, as well.

Answer 5

Try following 2006 Error related fixes :

• Server timed out and closed the connection. How to fix: check that wait_timeout variable in your mysqld’s my.cnf configuration file is large enough.

• Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. You can increase the maximal packet size limit by increasing the value of max_allowed_packet in my.cnf file.

Answer 6

I tried all the solutions and nothing worked
I use the workbench to remotely connect hostgator
I realized that mysql server hostgator was the 5.5 version and in my workbench is set up to version 5.6
when I set the workbench to 5.5 he started to work

edit/preferences/mysql/default target version

Answer 7

Here's an alternative to editing my.cnf file. You can set the MySQL global variables value via logging into the MySQL server.

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.

mysql> SET GLOBAL max_allowed_packet=1072731894;
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)

Answer 8

there are many reason that this issue is happening to you, here you can find all of the possible reasons, personally I've been struggling with the packet size but I've updated my my.ini file but before to do that, I've checked the max_allowed_packet variable that had given me 1048576B = 1MB and I've updated to 5MB. show variables where variable_name like '%packet%'

select 1048576 / 1024 / 1024

select 5242880 / 1024 / 1024

Answer 9

Show your mySql Variables

• show variables like 'max%'

• set global max_allowed_packet={PacketRANGE}; // like this 10485760;

• show global variables like 'max_all%';

Enjoy Your Coding here

Answer 10

In MySQL 5.7 this error can be generated by a too large communication packet:

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

A Packet in MySQL is:

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

You can found the doc here: DOC
You should try to set the max_allowed_packet to a bigger value (default value is 4MB) to solve if your SQL script is greater than this size. You can set this value within an Option File so you do not have to set it up each time.
On Microsoft Windows Vista and greater, you can set max_allowed_packet into the file
%PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini
or
%PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnf

where PROGRAMDATA = C:\ProgramData
More info (also for other S.O.) HERE

Answer 11

AS mentioned by Tudor in his reaction:

 The most common reason for the MySQL server has gone away error is that the 
 server timed out and closed the connection

You need to change the maximum execution time and size you can use the following commands to do so:

SET GLOBAL wait_timeout = 6000;
SET GLOBAL max_allowed_packet= 64M;

They are sql commands so you can execute them like noramal commands.

Answer 12

it happens sometimes, those at your server are offline and you are using MySQL connection it was created before password changed.that's why you should create a new connection and use of SQL practice after the server password changed.

Answer 13

I'm finding this is happening a lot in My SQL using MySQL Workbench 8.0 when I try to add a column at the end of my table. In addition, the table is then locked and unusable. Trying to drop the table then doesn't work until a day or two later. I have found, however, that I see no problems when I insert a new column between existing columns...