2006: MySQL server has gone away

Question

I am getting 'Lost connection to MySQL server' and '2006: MySQL server has gone away' errors so frequently that the server is unusable. There are questions related to this issue, but none that apply to my situation; for instance, there is no network connection (the server is connected by a socket to the client process which is on the same machine) and there are no long queries (the machine is running a number of CMS - mediawiki, drupal and joomla - and PHPMyAdmin, but there is nothing exceptional about the queries generated, AFAIK).

I have increased all the timeouts to the point where they must surely be sufficient:

+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 60       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 6000     |
| net_write_timeout           | 6000     |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
| max_allowed_packet          | 16777216 |
+-----------------------------+----------+

All other settings are the default except

query_cache_size=64MB
max_allowed_packet=16M

I have also changed the connect_timeout setting in php.ini to unlimited:

; Maximum time (in seconds) for connect timeout. -1 means no limit
; http://php.net/mysql.connect-timeout
mysql.connect_timeout = -1

Restarting the server clears the problem for some hours. After that, every access through the CMSs (i.e. through PHP) and through a small PHP test program with a trivial query fails. However, I can still access the MySQL server and the databases through the MySQL client.

There are no error messages in the MySQL error log.

The server is running on a recent Mac Mini, using Mac OS X 10.9.5, MySQL v5.6.22 and PHP v5.6.3 installed using Mac Port.

Can you help with ideas where I should look to deal with this? (This query was originally posted on StackOverflow, but I was advised to repost here)

[In response to comments)

MYSQL is now exhibiting the usual symptoms, so I can report on its state. For @Nathan C, ps aux |grep mysql gives:

_mysql          34888   0.0  6.4  4219456 1068400   ??  S    11:00pm   2:09.44 /opt/local/lib/mysql56/bin/mysqld --user=_mysql
root            34887   0.0  0.0  2510772   1192   ??  Ss   11:00pm   0:00.22 /opt/local/bin/daemondo --label=mysql56-server --start-cmd /opt/local/lib/mysql56/bin/mysqld --user=_mysql ; --pid=exec
root            74518   0.0  0.0  2475116   2140 s000  S+   Wed12pm   0:00.02 mysql -u root -p
scs1ng           7293   0.0  0.0  2432784    616 s003  R+    8:04pm   0:00.00 grep mysql`

and top shows:

Processes: 247 total, 2 running, 8 stuck, 237 sleeping, 943 threads                                                                                                                      20:10:09
Load Avg: 1.50, 1.27, 1.13  CPU usage: 0.90% user, 1.69% sys, 97.39% idle  SharedLibs: 16M resident, 11M data, 0B linkedit. MemRegions: 54262 total, 4947M resident, 111M private, 1476M shared.
PhysMem: 11G used (1535M wired), 5133M unused. VM: 629G vsize, 1069M framework vsize, 128(0) swapins, 199(0) swapouts. Networks: packets: 309678079/54G in, 382978367/227G out.
Disks: 35242710/547G read, 72080598/1265G written.

PID    COMMAND      %CPU TIME     #TH  #WQ  #POR #MRE MEM    RPRVT  PURG CMPR VPRVT  VSIZE  PGRP  PPID  STATE    UID  FAULTS  COW  MSGS MSGR SYSBSD   SYSMAC CSW      PAGE KPRV KSHR IDLEW   POWE
34888  mysqld       0.1  02:09.61 24   0    45   223  1021M  1021M  0B   0B   1220M  4121M  34887 34887 sleeping 74   325413  567  76   17   8737931+ 18555  1618292+ 0    -4M  27M  291795+ 0.3

@zimmi I ran mysqlreport just after restarting the mysql server. The output looked entirely sensible. I have now run it again, but it fails to connect:

scs1ng$ perl5.12 mysqlreport --user root -password
Password for database user root: 
DBI connect('host=localhost','root',...) failed: Lost connection to MySQL server at 'sending authentication information', system error: 32 at mysqlreport line 249
Died at mysqlreport line 249, <STDIN> line 1.

Answer 1

See the mysql bug that could be applied to you.

Apparently you can get rid of this error limiting the "table_open_cache" configuration to a higher number (500)