MySQL error 2006 mysql server has gone away

Question

I m running a server at my office to process some files and report the results to a remote MySQL server   The files processing takes some time and the process dies halfway through with the following error    2006  MySQL server has gone away   I ve heard about the MySQL setting  wait timeout  but do I need to change that on the server at my office or the remote MySQL server

User · Answer

This might be a problem of your  sql file size   If you are using xampp  Go to the xampp control panel -  Click MySql config -  Open my ini   Increase the packet size   max allowed packet   2M - gt  10M

User · Answer

uncomment the ligne below in your my ini my cnf  this will split your large file into smaller portion    binary logging format - mixed recommended   binlog format mixed   TO     binary logging format - mixed recommended binlog format mixed

User · Answer

There are several causes for this error   MySQL MariaDB related    wait timeout - Time in seconds that the server waits for a connection to become active before closing it  interactive timeout - Time in seconds that the server waits for an interactive connection  max allowed packet - Maximum size in bytes of a packet or a generated intermediate string  Set as large as the largest BLOB  in multiples of 1024    Example of my cnf    mysqld    8 hours wait timeout   28800   8 hours interactive timeout   28800 max allowed packet   256M   Server related    Your server has full memory - check info about RAM with free -h   Framework related    Check settings of your framework  Django for example use CONN MAX AGE  see docs    How to debug it    Check values of MySQL MariaDB variables    with sql  SHOW VARIABLES LIKE   time    command line  mysqladmin variables  Turn on verbosity for errors    MariaDB  log warnings   4 MySQL  log error verbosity   3  Check docs for more info about the error

User · Answer

I used following command in MySQL command-line to restore a MySQL database which size more than 7GB  and it works   set global max allowed packet 268435456

User · Answer

If you know you re going offline for a while  you can close your connection  do your processing  reconnect and write your reports

User · Answer

On windows those guys using xampp should use this path xampp mysql bin my ini    and change max allowed packet under section mysqld  to your choice size  e g  max allowed packet 8M   Again on php ini xampp php php ini  change upload max filesize the choice size  e g  upload max filesize 8M   Gave me a headache for sometime till i discovered this  Hope it helps

User · Answer

MAMP 5 3  you will not find my cnf and adding them does not work as that max allowed packet is stored in variables   One solution can be    Go to http   localhost phpmyadmin Go to SQL tab Run SHOW VARIABLES and check the values  if it is small then run with big values Run the following query  it set max allowed packet to 7gb   set global max allowed packet 268435456    For some  you may need to increase the following values as well   set global wait timeout   600  set innodb log file size  268435456

User · Answer

There is an easier way if you are using XAMPP   Open the XAMPP control panel  and click on the config button in mysql section    Now click on the my ini and it will open in the editor  Update the max allowed packet to your required size       Then restart the mysql service  Click on stop on the Mysql service click start again  Wait for a few minutes     Then try to run your Mysql query again  Hope it will work

User · Answer

Error  2006  CR SERVER GONE ERROR      Message  MySQL server has gone away   Generally you can retry connecting and then doing the query again to solve this problem - try like 3-4 times before completely giving up   I ll assuming you are using PDO   If so then you would catch the PDO Exception  increment a counter and then try again if the counter is under a threshold   If you have a query that is causing a timeout you can set this variable by executing   SET   GLOBAL wait timeout 300  SET   LOCAL wait timeout 300   -- OR current session only   Where 300 is the number of seconds you think the maximum time the query could take   Further information on how to deal with Mysql connection issues   EDIT  Two other settings you may want to also use is net write timeout and net read timeout

User · Answer

I had the same problem in docker adding below setting in docker-compose yml  db      image  mysql 8 0     command  --wait timeout 800 --max allowed packet 256M --character-set-server utf8 --collation-server utf8 general ci --default-authentication-plugin mysql native password     volumes        -   docker mysql data  var lib mysql       -   docker mysql dump  docker-entrypoint-initdb d     ports        - 3306 3306     environment        MYSQL ROOT PASSWORD    MYSQL ROOT PASSWORD        MYSQL DATABASE    MYSQL DATABASE        MYSQL USER    MYSQL USER        MYSQL PASSWORD    MYSQL PASSWORD

User · Answer

I found the solution to   2006 - MySQL server has gone away  this error  Solution is just you have to check two files   config inc php config sample inc php   Path of these files in windows is   C  wamp64 apps phpmyadmin4 6 4   In these two files the value of this    cfg  Servers    i   host  must be  localhost      In my case it was    cfg  Servers    i   host      127 0 0 1     change it to     cfg  Servers    i   host       localhost     Make sure in both    config inc php config sample inc php files it must be  localhost     And last set    cfg  Servers    i   AllowNoPassword     true    Then restart Wampserver     To change phpmyadmin user name and password  You can directly change the user name and password of phpmyadmin through config inc php file  These two lines   cfg  Servers    i   user      root    cfg  Servers    i   password           Here you can give new user name and password  After changes save the file and restart WAMP server

User · Answer

It was RAM problem for me   I was having the same problem even on a server with 12 CPU cores and 32 GB RAM  I researched more and tried to free up RAM  Here is the command I used on Ubuntu 14 04 to free up RAM   sync  amp  amp  echo 3   sudo tee  proc sys vm drop caches   And  it fixed everything  I have set it under cron to run every hour   crontab -e  0         bash  root ram sh    And  you can use this command to check how much free RAM available   free -h   And  you will get something like this                total       used       free     shared    buffers     cached Mem            31G        12G        18G        59M       1 9G       973M -   buffers cache        9 9G        21G Swap          8 0G       368M       7 6G

User · Answer

For Vagrant Box  make sure you allocate enough memory to the box  config vm provider  virtualbox  do  vb    vb memory    4096  end

User · Answer

For users using XAMPP   there are 2 max allowed packet parameters in C  xampp mysql bin my ini

User · Answer

In my case it was low value of open files limit variable  which blocked the access of mysqld to data files   I checked it with    mysql gt  SHOW VARIABLES LIKE  open     ------------------ -------    Variable name      Value    ------------------ -------    open files limit   1185     ------------------ -------  1 row in set  0 00 sec    After I changed the variable to big value  our server was alive again     mysqld  open files limit   100000

User · Answer

If you are using the 64Bit WAMPSERVER  please search for multiple occurrences of max allowed packet because WAMP uses the value set under  wampmysqld64  and not the value set under  mysqldump   which for me was the issue  I was updating the wrong one  Set this to something like max allowed packet   64M    Hopefully this helps other Wampserver-users out there

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

I was getting this same error on my DigitalOcean Ubuntu server   I tried changing the max allowed packet and the wait timeout settings but neither of them fixed it   It turns out that my server was out of RAM  I added a 1GB swap file and that fixed my problem   Check your memory with free -h to see if that s what s causing it

User · Answer

Just in case this helps anyone   I got this error when I opened and closed connections in a function which would be called from several parts of the application  We got too many connections so we thought it might be a good idea to reuse the existing connection or throw it away and make a new one like so     public static function getConnection  database   host   user   password      if   self   instance      return self  newConnection  database   host   user   password      elseif   database    host    user    self   connectionDetails      self   instance- query  KILL CONNECTION ID        self   instance   null        return self  newConnection  database   host   user   password               return self   instance        Well turns out we ve been a little too thorough with the killing and so the processes doing important things on the old connection could never finish their business  So we dropped these lines    self   instance- gt query  KILL CONNECTION ID        self   instance   null    and as the hardware and setup of the machine allows it we increased the number of allowed connections on the server by adding   max connections   500   to our configuration file  This fixed our problem for now and we learned something about killing mysql connections

User · Answer

In MAMP  non-pro version  I added   --max allowed packet 268435456   to     MAMP bin startMysql sh  Credits and more details here

User · Answer

I ve encountered this a number of times and I ve normally found the answer to be a very low default setting of max allowed packet    Raising it in  etc my cnf  under  mysqld   to 8 or 16M usually fixes it   The default in MySql 5 7 is 4194304  which is 4MB     mysqld  max allowed packet 16M     Note  Just create the line if it does not exist  Note  This can be set on your server as it s running    Use set global max allowed packet 104857600  This sets it to 100MB

User · Answer

This error happens basically for two reasons    You have a too low RAM  The database connection is closed when you try to connect    You can try this code below     Simplification to execute an SQL string of getting a data from the database def get self  sql string  sql vars     debug sql 0       try                      self cursor execute sql string  sql vars          return self cursor fetchall       except  AttributeError  MySQLdb OperationalError           self   init             self cursor execute sql string  sql vars          return self cursor fetchall     It mitigates the error whatever the reason behind it  especially for the second reason   If it s caused by low RAM  you either have to raise database connection efficiency from the code  from the database configuration  or simply raise the RAM

User · Answer

It s always a good idea to check the logs of the Mysql server  for the reason why it went away   It will tell you

User · Answer

This generally indicates MySQL server connectivity issues or timeouts   Can generally be solved by changing wait timeout and max allowed packet in my cnf or similar   I would suggest these values   wait timeout   28800  max allowed packet   8M

User · Answer

It may be easier to check if the connection and re-establish it if needed   See PHP mysqli ping for info on that

User · Answer

If you are using xampp server    Go to xampp -  mysql -  bin -  my ini  Change below parameter     max allowed packet   500M  innodb log file size   128M  This helped me a lot

User · Answer

The unlikely scenario is you have a firewall between the client and the server that forces TCP reset into the connection    I had that issue  and I found our corporate F5 firewall was configured to terminate inactive sessions that is idle for more than 5 mins   Once again  this is the unlikely scenario

User · Answer

This error is occur due to expire of wait timeout   Just go to mysql server check its wait timeout    mysql gt  SHOW  VARIABLES  LIKE   wait timeout  mysql gt  set global wait timeout   600   10 minute or maximum wait time out you need  http   sggoyal blogspot in 2015 01 2006-mysql-server-has-gone-away html

User · Answer

I got Error 2006 message in different MySQL clients software on my Ubuntu desktop  It turned out that my JDBC driver version was too old

[mysql] MySQL error 2006: mysql server has gone away

Examples related to mysql

Examples related to mysql-error-2006