ERROR 2006 HY000 MySQL server has gone away

Question

I get this error when I try to source a large SQL file  a big INSERT query    mysql gt   source file sql ERROR 2006  HY000   MySQL server has gone away No connection  Trying to reconnect    Connection id     2 Current database      NONE      ERROR 2006  HY000   MySQL server has gone away No connection  Trying to reconnect    Connection id     3 Current database      NONE       Nothing in the table is updated   I ve tried deleting and undeleting the table database  as well as restarting MySQL   None of these things resolve the problem   Here is my max-packet size    -------------------- ---------    Variable name        Value      -------------------- ---------    max allowed packet   1048576    -------------------- ---------    Here is the file size     ls -s file sql  79512 file sql   When I try the other method         mysql -u root -p my db  lt  file sql Enter password   ERROR 2006  HY000  at line 1  MySQL server has gone away

User · Answer

For Drupal 8 users looking for solution for DB import failure:

At end of sql dump file there can commands inserting data to "webprofiler" table. That's I guess some debug log file and is not really important for site to work so all this can be removed. I deleted all those inserts including LOCK TABLES and UNLOCK TABLES (and everything between). It's at very bottom of the sql file. Issue is described here:

https://www.drupal.org/project/devel/issues/2723437

But there is no solution for it beside truncating that table.

BTW I tried all solutions from answers above and nothing else helped.

User · Answer

How about using the mysql client like this   mysql -h  lt hostname gt  -u username -p  lt databasename gt   lt  file sql

User · Answer

This is more of a rare issue but I have seen this if someone has copied the entire  var lib mysql directory as a way of migrating their DB to another server   The reason it doesn t work is because the database was running and using log files     It doesn t work sometimes if there are logs in  var log mysql   The solution is to copy the  var log mysql files as well

User · Answer

In general the error      Error  2006  CR SERVER GONE ERROR  - MySQL server has gone away   means that the client couldn t send a question to the server     mysql import  In your specific case while importing the database file via mysql  this most likely mean that some of the queries in the SQL file are too large to import and they couldn t be executed on the server  therefore client fails on the first occurred error   So you ve the following possibilities    Add force option  -f  for mysql to proceed and execute rest of the queries   This is useful if the database has some large queries related to cache which aren t relevant anyway  Increase max allowed packet and wait timeout in your server config  e g     my cnf   Dump the database using --skip-extended-insert option to break down the large queries  Then import it again  Try applying --max-allowed-packet option for mysql      Common reasons  In general this error could mean several things  such as    a query to the server is incorrect or too large   Solution  Increase max allowed packet variable    Make sure the variable is under  mysqld  section  not  mysql   Don t afraid to use large numbers for testing  like 1G   Don t forget to restart the MySQL MariaDB server  Double check the value was set properly by   mysql -sve  SELECT   max allowed packet    or  mysql -sve  SHOW VARIABLES LIKE  max allowed packet     You got a timeout from the TCP IP connection on the client side   Solution  Increase wait timeout variable  You tried to run a query after the connection to the server has been closed   Solution  A logic error in the application should be corrected  Host name lookups failed  e g  DNS server issue   or server has been started with --skip-networking option   Another possibility is that your firewall blocks the MySQL port  e g  3306 by default   The running thread has been killed  so retry again  You have encountered a bug where the server died while executing the query  A client running on a different host does not have the necessary privileges to connect  And many more  so learn more at  B 5 2 9 MySQL server has gone away      Debugging  Here are few expert-level debug ideas    Check the logs  e g   sudo tail -f   mysql -Nse  SELECT   GLOBAL log error    Test your connection via mysql  telnet or ping functions  e g  mysql ping in PHP   Use tcpdump to sniff the MySQL communication  won t work for socket connection   e g    sudo tcpdump -i lo0 -s 1500 -nl -w- port mysql   strings  On Linux  use strace  On BSD Mac use dtrace dtruss  e g   sudo dtruss -a -fn mysqld 2 gt  amp 1   See  Getting started with DTracing MySQL   Learn more how to debug MySQL server or client at  26 5 Debugging and Porting MySQL   For reference  check the source code in sql-common client c file responsible for throwing the CR SERVER GONE ERROR error for the client command   MYSQL TRACE SEND COMMAND  mysql   command  header length  arg length  header  arg    if  net write command net  uchar  command  header  header length            arg  arg length       set mysql error mysql  CR SERVER GONE ERROR  unknown sqlstate     goto end

User · Answer

This error message also occurs when you created the SCHEMA with a different COLLATION than the one which is used in the dump  So  if the dump contains  CREATE TABLE  mytab         ENGINE InnoDB DEFAULT CHARSET utf8 COLLATE utf8 unicode ci    you should also reflect this in the SCHEMA collation   CREATE SCHEMA myschema COLLATE utf8 unicode ci    I had been using utf8mb4 general ci in the schema  cause my script came from a fresh V8 installation  now loading a DB on old 5 7 crashed and drove me nearly crazy   So  maybe this helps you saving some frustating hours     -    MacOS 10 3  mysql 5 7

User · Answer

If you have tried all these solutions  esp  increasing max allowed packet up to the maximum supported amount of 1GB and you are still seeing these errors  it might be that your server literally does not have enough free RAM memory available    The solution   upgrade your server to more RAM memory  and try again  Note  I m surprised this simple solution has not been mentioned after 8  years of discussion on this thread    sometimes we developers tend to overthink things

User · Answer

You can increase Max Allowed Packet   SET GLOBAL max allowed packet 1073741824    http   dev mysql com doc refman 5 5 en server-system-variables html sysvar max allowed packet

User · Answer

I had the same problem but changeing max allowed packet in the my ini my cnf file under  mysqld  made the trick   add a line  max allowed packet 500M   now restart the MySQL service once you are done

User · Answer

The solution is increasing the values given the wait timeout and the connect timeout parameters in your options file  under the  mysqld  tag    I had to recover a 400MB mysql backup and this worked for me  the values I ve used below are a bit exaggerated  but you get the point     mysqld  port 3306 explicit defaults for timestamp   TRUE connect timeout   1000000 net write timeout   1000000 wait timeout   1000000 max allowed packet   1024M interactive timeout   1000000 net buffer length   200M net read timeout   1000000 set GLOBAL delayed insert timeout 100000      Blockquote

User · Answer

For amazon RDS  it s my case   you can change the max allowed packet parameter value to any numeric value in bytes that makes sense for the biggest data in any insert you may have  e g   if you have some 50mb blob values in your insert  set the max allowed packet to 64M   67108864   in a new or existing parameter-group  Then apply that parameter-group to your MySQL instance  may require rebooting the instance

User · Answer

I encountered this error when I use Mysql Cluster  I do not know this question is from a cluster usage or not  As the error is exactly the same  so give my solution here  Getting this error because the data nodes suddenly crash  But when the nodes crash  you can still get the correct result using cmd   ndb mgm -e  ALL REPORT MEMORYUSAGE    And the mysqld also works correctly So at first  I can not understand what is wrong  And about 5 mins later  ndb mgm result shows no data node working  Then I realize the problem  So  try to restart all the data nodes  then the mysql server is back and everything is OK    But one thing is weird to me  after I lost mysql server for some queries  when I use cmd like show tables  I can still get the return info like 33 rows in set  5 57 sec   but no table info is displayed

User · Answer

If it s reconnecting and getting connection ID 2  the server has almost definitely just crashed   Contact the server admin and get them to diagnose the problem  No non-malicious SQL should crash the server  and the output of mysqldump certainly should not   It is probably the case that the server admin has made some big operational error such as assigning buffer sizes of greater than the architecture s address-space limits  or more than virtual memory capacity  The MySQL error-log will probably have some relevant information  they will be monitoring this if they are competent anyway

User · Answer

Just in case  to check variables you can use     gt  mysqladmin variables -u user -p    This will display the current variables  in this case max allowed packet  and as someone said in another answer you can set it temporarily with   mysql gt  SET GLOBAL max allowed packet 1072731894   In my case the cnf file was not taken into account and I don t know why  so the SET GLOBAL code really helped

User · Answer

max allowed packet 64M   Adding this line into my cnf file solves my problem   This is useful when the columns have large values  which cause the issues  you can find the explanation here      On Windows this file is located at   C  ProgramData MySQL MySQL Server   5 6       On Linux  Ubuntu    etc mysql

User · Answer

I ve tried all of above solutions  all failed   I ended up with using -h 127 0 0 1 instead of using default var run mysqld mysqld sock

User · Answer

A couple things could be happening here     Your INSERT is running long  and client is disconnecting  When it reconnects it s not selecting a database  hence the error  One option here is to run your batch file from the command line  and select the database in the arguments  like so         mysql db name  lt  source sql    Another is to run your command via php or some other language  After each long - running statement  you can close and re-open the connection  ensuring that you re connected at the start of each query

User · Answer

if none of this answers solves you the problem  I solved it by removing the tables and creating them again automatically in this way   when creating the backup  first backup structure and be sure of add  DROP TABLE   VIEW   PROCEDURE   FUNCTION   EVENT CREATE PROCEDURE   FUNCTION   EVENT IF NOT EXISTS AUTO INCREMENT   then just use this backup with your db and it will remove and recreate the tables you need   Then you backup just data  and do the same  and it will work

User · Answer

If you are on Mac and installed mysql through brew like me  the following worked    cp   brew --prefix mysql  support-files my-default cnf  usr local etc my cnf   Source  For homebrew mysql installs  where  39 s my cnf    add max allowed packet 1073741824 to  usr local etc my cnf mysql server restart

User · Answer

You can also log into the database as root  or SUPER privilege  and do   set global max allowed packet 64 1024 1024    doesn t require a MySQL restart as well  Note that you should fix your my cnf file as outlined in other solutions    mysqld  max allowed packet 64M   And confirm the change after you ve restarted MySQL   show variables like  max allowed packet     You can use the command-line as well  but that may require updating the start stop scripts which may not survive system updates and patches   As requested  I m adding my own answer here  Glad to see it works

User · Answer

I solved the error ERROR 2006  HY000  at line 97  MySQL server has gone away and successfully migrated a  5GB sql file by performing these two steps in order    Created  etc my cnf as others have recommended  with the following contents    mysql  connect timeout   43200 max allowed packet   2048M net buffer length   512M debug-info   TRUE  Appending the flags --force --wait --reconnect to the command  i e  mysql -u root -p -h localhost my db  lt  file sql --verbose --force --wait --reconnect     Important Note  It was necessary to perform both steps  because if I didn t bother making the changes to  etc my cnf file as well as appending those flags  some of the tables were missing after the import   System used  OSX El Capitan 10 11 5  mysql Ver 14 14 Distrib 5 5 51 for osx10 8  i386

User · Answer

The global update and the my cnf settings didn t work for me for some reason  Passing the max allowed packet value directly to the client worked here   mysql -h  lt hostname gt  -u username -p --max allowed packet 1073741824  lt databasename gt   lt  db sql

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

Examples related to mysql