'How to prevent ERROR 2013 (HY000) using Aurora Serverless

When performing long-running queries on Aurora Serverless I have seen the following errors after a few minutes:

  • ERROR 1080 (08S01) Forcing close of thread
  • ERROR 2013 (HY000) Lost connection to MySQL server during query

The queries are using mysql LOAD DATA LOCAL INFILE to load large (multi-GB) data files into the database.

How can I avoid these errors?

mysqlamazon-auroraaws-serverless


Solution 1:[1]

To solve this, you can change the parameter group item net_write_timeout to a more suitable value. Here's instructions for completing the steps from the console:

  1. Go to RDS Console
  2. Click "Parameter Groups" in the left pane
  3. Click "Create Parameter Group"
  4. On the Parameter Group Details page, for Type, choose DB Cluster Parameter Group; then give it a name and description, and click "Create"
  5. Click on the name of the parameter group you created in step 4
  6. Search for "net_write_timeout"
  7. Click on the checkbox next to the parameter and click "Edit Parameters"
  8. Change the value to an integer between 1-31536000 for the number of seconds you want it to wait before timing out, and click "save changes"
  9. Click on Databases in the left pane
  10. Click on the database and click "modify"
  11. Under Additional Configuration > Database Options > DB Cluster Parameter Group, select the parameter group you created in step 4, and click "Continue"
  12. Select "Apply Immediately" and click "Modify Cluster"

Solution 2:[2]

Break up your large, multi-GB uploads into smaller chunks. Aurora works better (and faster) loading one hundred 10MB files at once rather than one 1GB file. Assuming your data is already in a loadable format:

  1. Split the file into parts using split
split -n l/100 --additional-suffix="_small" big_file.txt

This results in 100 files like xaa_small xab_small, etc.

  1. find files that match the split suffixes using find
files=$(find . -name 'x*_small')
  1. loop through the files and load each in parallel
for file in $files; do
echo "load data local infile '$file' into table my_table;" | 
     mysql --defaults-file=/home/ubuntu/.my.cnf --defaults-group-suffix=test &
done

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 enharmonic
Solution 2 enharmonic

Related Questions