1

I have checked the answers at MySQL error 2006: mysql server has gone away None of them seem to fit my problem.

I am getting the error MySQL server has gone away frequently.

It is not the connection timeout. The default timeout of 8 hours seems plenty.

I have tried upping the max_allowed_packet to no avail. This then seemed irrelevant when I began printing out the offending SQL statement which was in my case: SELECT url FROM crawled WHERE frontier = 1 ORDER BY id. Hardly a large statement which warrants upping max_allowed_packet.

So, none of the given answers seem to fit my scenario. Any other reasons why this error may occur? Any possible fixes?

James Read
  • 419
  • 2
  • 7
  • 13

1 Answers1

1

Two common possibilities come to mind:

1) Out Of Memory error. Check syslog for evidence of it.

2) Bug or some other crash in mysqld thread. Check your MySQL error log.

The "server has gone away" almost always means a back end thread crash. And that should leave something obvious in the logs.

Gordan Bobić
  • 1,748
  • 13
  • 16
  • Thanks. I checked. Nothing in syslog. No OOM or anything like that. I also checked /var/log/mysql/error.log Nothing there. Last entry was over 2 hours ago. – James Read Jun 09 '20 at 22:39
  • The query ```SELECT url FROM crawled WHERE frontier = 1 ORDER BY id``` can return a very large set from the DB. Could this be something to do with it? – James Read Jun 09 '20 at 23:01
  • 1
    What is your `log_error_verbosity` set to? If less than 3, set it to 3 and see if it becomes more informative. – Gordan Bobić Jun 10 '20 at 00:27
  • 1033 [Note] [MY-010914] [Server] Aborted connection 1033 to db: 'crawl' user: 'crawler' host: 'localhost' (Got timeout writing communication packets). – James Read Jun 10 '20 at 01:00
  • That would imply you are getting network packet loss. Possibly a router or a firewall between the machines dropping tracked connections. – Gordan Bobić Jun 10 '20 at 08:48
  • Any way to fix this? I have handled the error by detecting the error code and reconnecting as a quick fix but would really like to fix this properly. Note, I get this error both on my desktop and dedicated server so whatever is happening is happening on both machines. – James Read Jun 10 '20 at 12:25
  • Short of fixing whatever router/firewall on the way is causing it? No. You may be able to work around it by using a UDP based VPN to the database server in order to avoid TCP connection resets. If it is happening even when you connect to localhost, it is most likely a big in your database driver or a misconfiguration somewhere in your local firewall or TCP stack. In the local case, have you tried connecting via the UNIX domain socket instead of TCP? – Gordan Bobić Jun 10 '20 at 14:16
  • No. I don't know how to do that. But yes I am connecting to the localhost. What is interesting is that the behaviour seems to be reproducible on my dedicated server. And the behaviour is always triggered by the same query. My application makes many different queries. Why does the behaviour only happen on one particular query ```SELECT url FROM crawled WHERE frontier = 1 ORDER BY id``` ? Also I recently switched the code to use ```mysql_use_result()``` The behaviour only started after the change. On both machines. – James Read Jun 10 '20 at 15:43
  • Interesting. Sounds like a bug in the mysql driver library on the application side. – Gordan Bobić Jun 10 '20 at 18:08