1

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.
4
  • What's your server resources look like when it happens? Post the header from top as well as ps aux entries. There's definitely something going on. Also, are you using Apache with mod_php or php-fpm?
    – Nathan C
    Commented Dec 24, 2014 at 12:53
  • Have you gone through these 2 pages? dev.mysql.com/doc/refman/5.6/en/error-lost-connection.html and dev.mysql.com/doc/refman/5.6/en/gone-away.html You already have done a part of it. There are also some tools like mysqlreport (sometimes shipped with the server package) that can help summarize the state of the server, you need then to read the report together with the relevant doc: hackmysql.com/mysqlreportguide
    – Zimmi
    Commented Dec 24, 2014 at 13:18
  • Further info added above
    – Nigel
    Commented Dec 26, 2014 at 20:05
  • Can you verify that your socket still exists when this happens?
    – Tom Kyle
    Commented Dec 30, 2014 at 21:32

1 Answer 1

0

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)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .