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