mysql consistently loses connection on long running big select statements

Question

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

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:

---

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

SHOW VARIABLES LIKE "%timeout";

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:

[mysqld]
connect_timeout = 600

Sources :

• Fixing lost connection to MySQL server during query error
• Error Code: 2013. Lost connection to MySQL server during query