Getting MYSQL Error: “Error Code: 2006 - MySQL ser

问题:

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?

回答1:

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

回答2:

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

回答3:

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.

回答4:

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

回答5:

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

回答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

回答7:

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

回答8:

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

回答9:

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

回答10:

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.

回答11:

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)