When I run a select statement on a big table (8GB+) via the mysql client for a long time I get the following error:

ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
ERROR 2006 (HY000) at line 1: MySQL server has gone away

I made sure that the network connection to the server is not interrupted during that time by running a similar query with a smaller resultset in parallel, which does not loose connection. So it is related to the size of the resultset.

I also checked AWS for any logs and monitoring and there are no connection losses either in the logs or in the monitoring metrics.

It's not a timeout. I set a timeout via the command line and it fails before that timeout is reached. I also ran a similar command on a smaller table which due to the slow consumer takes just as long to complete and did not receive the error in that case. I also changed all timeouts to 8 hours using the following option just in case:

--init-command='SET @connect_timeout:=28800;SET @net_read_timeout:=28800;SET @wait_timeout:=28800;SET @interactive_timeout:=28800;'

To reproduce the issue you can run the following command:

set -o pipefail;    
mysql mydatabase --xml --compress --quick --batch  --host=myhost --port=42 --user=myuser -p --execute="SELECT * FROM bigtable" | (l=0;while read i; do sleep 1; l=$(($l+1)); done;echo line $l;);

This will consistently give you the lost connection error after about 2 and maximum 3 hours. The while-loop simulates a slow consumer (transforming and exporting to another database). Increasing the sleep statement parameter to 2 seconds increases the time until the error occurs. The error does not occur on a small table.

set -o pipefail; will make the command fail if the first part of the pipe fails. It should not be necessary since we only have a simple pipe here, but I added it for completeness.

I tried removing the --quick option which is there so that the consumer can immediately start processing the output rather than wait for the whole select statement to be executed. But in that case the connection just seems to hang at some point and I can't see any error or progress.

Running it without the pipe seems to work perfectly fine without any error so far. Also running it with a faster consumer (without the sleep statement) seems to work fine.

It would be great if you could advise on how to fix this issue or confirm that you can reproduce it.

mysql Ver 14.14 Distrib 5.7.40

mysql database is the latest v.8 on Amazon RDS

  • Why the downvote? Please share so I can improve the question.
    – phobic
    Jan 23 at 11:28

1 Answer 1


You need perhaps to increase your timeout values.

From the command line you can increase the number of seconds to wait for a connection response using the --connect-timeout option. The default is 10 seconds, and to increase to 120 seconds run for example:

mysql -uroot -proot --connect-timeout 120

You may also change these parameters in Workbench Edit → Preferences → SQL Editor:

enter image description here

Global variables in the server can be displayed using the following query:


The most likely to help is the connect_timeout variable. This sets its value to 600 seconds:

SET GLOBAL connect_timeout = 600;

You may also edit the MySQL configuration file on the server, section [mysqld] like this:

connect_timeout = 600

Sources :

  • I tried that. I didn't mention it since the error does not indicate it's a timeout, so I didn't know it's relevant. Updated in the post.
    – phobic
    Jan 23 at 11:25
  • Have you tried all the above methods, to be sure it's not a timeout?
    – harrymc
    Jan 23 at 11:32
  • The error occurs during different times. I had one occur after over six hours with the exact same query by using a longer duration in the sleep statement. So a timeout does not seem to be a reasonable explanation for this behavior. I tried --connect-timeout. I can't change the timeout in the server due to a policy (tbh I don't see why this is necessary if I can do it on a per session basis). Are you reasonably sure that this is a timeout and if so why? I'll also take your word for it if you are convinced due to your intuition and try your suggestions out locally in that case.
    – phobic
    Jan 23 at 16:19
  • I can't be positive. The only other explanation I can come up with is that the connection was cut by another party (firewall, security software etc).
    – harrymc
    Jan 23 at 16:22
  • That would have cut the connection to the query I ran in parallel as well, or?
    – phobic
    Jan 23 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.