MySQL server has gone away - in exactly 60 seconds

Question

I recently discovered that a sql query that was running fine earlier is now timing out after 60 seconds and throwing an error  The query is slow but runs as part of a nightly job so that s not a problem in itself  so please don t suggest I optimize it     I m able to reproduce the error consistently by running  select SLEEP 120    from PHP as shown below  However  running the same statement from a MySQL client is successful  returns 0   I ve tried adjusting wait timeout  set to 28800   but have had no luck  I ve also rebooted both the db server and machine itself   The fact that it always times out at exactly 60 seconds suggests to me that it is likely to be a setting and not a limited resources issue   I m running  Windows Server 2003 MySql 5 1 36-community PHP 5 3  Below are my test code  the output and the results from SHOW VARIABLES  Thanks   CODE   set error handler  sqlErrorHandler    set time limit 12000      link   mysql connect   MYSQL Host    MYSQL User    MYSQL Pass    mysql select db  MYSQL db   link    echo  mysql ping        mysql ping  link     LIVE     DEAD       lt br   gt  lt br   gt       sql    SELECT SLEEP 120       start   microtime true   mysql query  sql   link    echo    query done   lt br   gt    allDone     function allDone     global  start   sql     end   microtime true    echo  sql    sql lt br   gt     echo  elapsed         end -  start      lt br   gt     echo   lt br   gt       function sqlErrorHandler  errno   errstr   errfile   errline    global  link   echo  Error    errno lt br   gt  errstr lt br   gt     echo  mysql ping        mysql ping  link     LIVE     DEAD       lt br   gt      echo   lt br   gt      allDone        OUTPUT    mysql ping   LIVE  Error   2 mysql query    function mysql-query   MySQL server has gone away mysql ping   DEAD  sql   SELECT SLEEP 120   elapsed   60 051116943359  Error   2 mysql query    function mysql-query   Error reading result set s header mysql ping   DEAD  sql   SELECT SLEEP 120   elapsed   60 0511469841    query done   sql   SELECT SLEEP 120   elapsed   60 051155090332   SHOW VARIABLES   Variable name Value auto increment increment 1 auto increment offset 1 autocommit ON automatic sp privileges ON back log 50 basedir C   Program Files  MySQL  MySQL Server 5 1   big tables OFF binlog cache size 32768 binlog format STATEMENT bulk insert buffer size 8388608 character set client utf8 character set connection utf8 character set database latin1 character set filesystem binary character set results utf8 character set server latin1 character set system utf8 character sets dir C   Program Files  MySQL  MySQL Server 5 1  share  charsets   collation connection utf8 general ci collation database latin1 swedish ci collation server latin1 swedish ci completion type 0 concurrent insert 1 connect timeout 10 datadir C   Documents and Settings  All Users  Application Data  MySQL  MySQL Server 5 1  Data   date format  Y- m- d datetime format  Y- m- d  H  i  s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1000 div precision increment 4 engine condition pushdown ON error count 0 event scheduler OFF expire logs days 0 flush OFF flush time 1800 foreign key checks ON ft boolean syntax   - gt  lt         amp   ft max word len 84 ft min word len 4 ft query expansion limit 20 ft stopword file  built-in  general log OFF general log file C   Documents and Settings  All Users  Application Data  MySQL  MySQL Server 5 1  Data  p1 log group concat max len 1024 have community features YES have compress YES have crypt NO have csv YES have dynamic loading YES have geometry YES have innodb YES have ndbcluster NO have openssl DISABLED have partitioning YES have query cache YES have rtree keys YES have ssl DISABLED have symlink YES identity 0 ignore builtin innodb OFF init connect  init file  init slave  innodb adaptive hash index ON innodb additional mem pool size 2097152 innodb autoextend increment 8 innodb autoinc lock mode 1 innodb buffer pool size 96468992 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1 10M autoextend innodb data home dir D   MySQL Datafiles   innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method  innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log buffer size 1048576 innodb log file size 19922944 innodb log files in group 2 innodb log group home dir     innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb rollback on timeout OFF innodb stats on metadata ON innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10000 innodb use legacy cardinality algorithm ON insert id 0 interactive timeout 28800 join buffer size 131072 keep files on create OFF key buffer size 50331648 key cache age threshold 300 key cache block size 1024 key cache division limit 100 language C   Program Files  MySQL  MySQL Server 5 1  share  english   large files support ON large page size 0 large pages OFF last insert id 0 lc time names en US license GPL local infile ON log OFF log bin OFF log bin trust function creators OFF log bin trust routine creators OFF log error C   Documents and Settings  All Users  Application Data  MySQL  MySQL Server 5 1  Data  p1 err log output FILE log queries not using indexes OFF log slave updates OFF log slow queries OFF log warnings 1 long query time 10 000000 low priority updates OFF lower case file system ON lower case table names 1 max allowed packet 1048576 max binlog cache size 4294963200 max binlog size 1073741824 max connect errors 10 max connections 800 max delayed threads 20 max error count 64 max heap table size 16777216 max insert delayed threads 20 max join size 18446744073709551615 max length for sort data 1024 max prepared stmt count 16382 max relay log size 0 max seeks for key 4294967295 max sort length 1024 max sp recursion depth 0 max tmp tables 32 max user connections 0 max write lock count 4294967295 min examined row limit 0 multi range count 256 myisam data pointer size 6 myisam max sort file size 107374182400 myisam recover options OFF myisam repair threads 1 myisam sort buffer size 12582912 myisam stats method nulls unequal myisam use mmap OFF named pipe OFF net buffer length 16384 net read timeout 30 net retry count 10 net write timeout 80 new OFF old OFF old alter table OFF old passwords OFF open files limit 2048 optimizer prune level 1 optimizer search depth 62 optimizer switch index merge on index merge union on index merge sort union on index merge intersection on pid file C   Documents and Settings  All Users  Application Data  MySQL  MySQL Server 5 1  Data  p1 pid plugin dir C   Program Files  MySQL  MySQL Server 5 1  lib plugin port 3306 preload buffer size 32768 profiling OFF profiling history size 15 protocol version 10 pseudo thread id 3230 query alloc block size 8192 query cache limit 1048576 query cache min res unit 4096 query cache size 33554432 query cache type ON query cache wlock invalidate OFF query prealloc size 8192 rand seed1  rand seed2  range alloc block size 4096 read buffer size 65536 read only OFF read rnd buffer size 262144 relay log  relay log index  relay log info file relay-log info relay log purge ON relay log space limit 0 report host  report password  report port 3306 report user  rpl recovery rank 0 secure auth OFF secure file priv  server id 0 shared memory OFF shared memory base name MYSQL skip external locking ON skip networking OFF skip show database OFF slave compressed protocol OFF slave exec mode STRICT slave load tmpdir C   WINDOWS  TEMP slave net timeout 3600 slave skip errors OFF slave transaction retries 10 slow launch time 2 slow query log OFF slow query log file C   Documents and Settings  All Users  Application Data  MySQL  MySQL Server 5 1  Data  p1-slow log sort buffer size 262144 sql auto is null ON sql big selects ON sql big tables OFF sql buffer result OFF sql log bin ON sql log off OFF sql log update ON sql low priority updates OFF sql max join size 18446744073709551615 sql mode  sql notes ON sql quote show create ON sql safe updates OFF sql select limit 18446744073709551615 sql slave skip counter  sql warnings OFF ssl ca  ssl capath  ssl cert  ssl cipher  ssl key  storage engine InnoDB sync binlog 0 sync frm ON system time zone Eastern Daylight Time table definition cache 256 table lock wait timeout 50 table open cache 619 table type InnoDB thread cache size 38 thread handling one-thread-per-connection thread stack 196608 time format  H  i  s time zone SYSTEM timed mutexes OFF timestamp 1256827484 tmp table size 16777216 tmpdir C   WINDOWS  TEMP transaction alloc block size 8192 transaction prealloc size 4096 tx isolation REPEATABLE-READ unique checks ON updatable views with limit YES version 5 1 36-community version comment MySQL Community Server  GPL  version compile machine ia32 version compile os Win32 wait timeout 28800 warning count 0

User · Answer

I was having trouble on a database restore using mysqldumper  php program   I was able to get it working by changing the  mssql timeout  setting in the php ini   It was defaulted to 60 and I changed it to 300

User · Answer

There s a whole bunch of things that can cause this  I d read through these and try each of them  http   dev mysql com doc refman 5 1 en gone-away html  I ve worked for several web hosting companies over the years and generally when I see this  it is the wait timeout on the server end though this doesn t appear to be the case here   If you find the solution  I hope you post it  I d like to know

User · Answer

In our case  the culprit was the global  not  local   MySQL variable  wait timeout    Compare the results of the following queries   SHOW VARIABLES LIKE   wait      to  SHOW GLOBAL VARIABLES WHERE Variable name LIKE   wait      In our case the first query showed a wait timeout of 28800  but the 2nd query showed a value of 10  seconds    We verified that changing the global variable fixed the problem   Here is a simple PHP script that reproduced our condition    lt  php      db   mysqli connect  host    user    password    database     sleep 10      number of seconds to sleep     MySQL server has gone away   obj   mysqli query  db   SELECT   FROM some table      results   mysqli fetch object  obj    print r  results     As soon as the sleep time exceeded the global wait timeout value  we would get the error   Warning  mysqli query    MySQL server has gone away    To change the value  we had to edit the setting in our Amazon RDS dashboard

User · Answer

It happens if the connection was open for quite sometime but no action was done in the MySQL server  In that case  connection timeout occurs with the error  MySQL server has gone away   The answers above may work and may not work  Even the accepted answer did not work for me  So I tried a trick and it worked fine for me  Logically  in order to avoid this error  we have to keep the MySQL connection running or in short  keep it alive  Assume that we are trying to Bulk insert 250k records  Generally it takes time to create parse data from somewhere and make Bulk query and then insert  In this scenario  most of us use a loop to create the SQL string  So let s count the iteration number and make a dummy database call after a certain iteration  It will keep the connection alive    for int i   0  size   somedatalist length  i  lt  size    i            build the Bulk insert query string       if  i 10000   0               make a dummy call like  SELECT   FROM log LIMIT 1              it will keep the connection alive             Execute bulk insert

User · Answer

I noticed something perhaps relevant   I had two scripts running  both doing rather slow queries  One of them locked a table and the other had to wait  The one that was waiting had default socket timeout   300  Eventually it quit with  MySQL server has gone away   However  the mysql process list continued to show both query  the slow one still running and the other locked and waiting   So I don t think mysqld is the culprit  Something has changed in the php mysql client  Quite possibly the default socket timeout which I will now set to -1 to see if that changes anything

User · Answer

My case was a database corruption after a minor upgrade in mysql basically 5 0 x to 5 1 x with the DB in myisam  The same lines on query  MySQL server has gone away  Error reading result set s header  After repairing  amp  optimizing it with mysqlcheck  it returned to normal  without the need to change the socket timeout

User · Answer

By my experiences when it happens on light queries there is a way to solve the problem  It seems when you start or restart mysql after apache this problem starts to appear and the source of the problem is confused open sockets in the php process   To solve it    First restart mysql service Then restart apache service

User · Answer

I solved this problem with  if   mysql ping  link     link   mysql connect   MYSQL Host    MYSQL User    MYSQL Pass   true

User · Answer

The php option mysql connect timeout is the reason for this  It s not only used for connect timeout  but as well as waiting for the first answer from the server  You can increase it like this   ini set  mysql connect timeout   300   ini set  default socket timeout   300

User · Answer

I had this problem recently  I stumbled across an option  default authentication plugin  For some reason it had set it to caching sha2 password  but updating the value to mysql native password fixed it for me  I m not sure what the differences are  though  so be careful   Hope this helps someone

User · Answer

When I ran into this problem  it wasn t caused by wait timeout  which was set to the default 8 hours  but by max allowed packet with a large INSERT statement  Changing max allowed packet from PHP had no effect  but when I changed it in the mysqld section of  etc my cnf and restarted the MySQL server  the problem disappeared

User · Answer

This is something I do   but usually with the MySQLi class     link   mysql connect   MYSQL Host    MYSQL User    MYSQL Pass    mysql select db  MYSQL db   link       RUN REALLY LONG QUERY HERE     Reconnect if needed  if   mysql ping  link     link   mysql connect   MYSQL Host    MYSQL User    MYSQL Pass   true       RUN ANOTHER QUERY

User · Answer

Please see this link http   bugs php net bug php id 45150 seems like they moved to native MYSQL support in PHP5 3 and it has some trouble working with IPV6  Try using  127 0 0 1  instead of  localhost

User · Answer

Increasing SQL-Wait-Timeout worked for me in this case  try this   mysql query  SET   session wait timeout 900    link     before you first  normal  SQL queries

User · Answer

I have the same problem with mysqli   My solution is https   www php net manual en mysqli configuration php  mysqli reconnect   On

[php] MySQL server has gone away - in exactly 60 seconds

Examples related to php

Examples related to mysql

Examples related to timeout