AWS RDS: Lost connection to MySQL server during query ([Errno 54] Connection reset by peer)')

Question

I have a Flask Application running locally and there is using `pymysql` to query a `MySQL` DB hosted on AWS RDS. After the application has been idle for a few minutes (>5), I get an error: ` Lost connection to MySQL server during query ([Errno 54] Connection reset by peer)') `

```
 File "/Applications/Anaconda/anaconda3/lib/python3.9/site-packages/pymysql/connections.py", line 738, in _read_bytes
            raise err.OperationalError(
        sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query ([Errno 54] Connection reset by peer)')
        [SQL: SELECT users.id AS users_id, users.fullname AS users_fullname, users.email AS users_email, users.password AS users_password, users.name AS users_name, users.jobtitle AS users_jobtitle, users.country AS users_country, users.emailverified AS users_emailverified, users.created_at AS users_created_at 
        FROM users 
        WHERE users.id = %(pk_1)s]
        [parameters: {'pk_1': 1}]
        (Background on this error at: https://sqlalche.me/e/14/e3q8)
```

I tried updating the parameters on AWS RDS - specifically, the `timeout` params, but it didn't work. What else can I try? Is this an issue with the Application code (mysql queries) or with the DB configuration in AWS RDS?

Answer

The error : Lost connection to MySQL server during query generally occurs when the queries are trying to work with large set of rows and times-out due to default net_read_timeout (set to 30s) configuration.

[+] Lost connection to MySQL server - https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html

Ensure that your queries are using optimal execution plan and are properly tuned, you can refer to the doc below to troubleshoot slow select queries on your Amazon Aurora DB cluster:-

[+] https://aws.amazon.com/premiumsupport/knowledge-center/aurora-mysql-slow-select-query/

Once you have ensured that query is using optimal execution plan, and still requires bigger time out value, you may configure "net_read_timeout" parameter to sufficient value (bigger than 30s).

[+] net_read_timeout - https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_net_read_timeout

Therefore, to get resolution from the lost connection issue, you can set below "instance - level" parameters on Aurora cluster instances.

connect_timeout
net_read_timeout
net_write_timeout

You would need to tune the parameters until it matches your application need. 
All these 3 parameters are dynamic parameters, so you will not require a reboot to your cluster.

You can refer to the AWS doc on How do I resolve the error "MySQL server has gone away" when connecting to my Amazon RDS MySQL DB instance?

[+] https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-server-gone-away/

I would recommend you to check your DB server logs for any errors in the time frame you face the issue.

Also, kindly assure that your client applications have sufficient time out values to allow connection and query to run successfully. You could refer to this documentation for detail information.

[+] https://dev.mysql.com/doc/workbench/en/wb-preferences-sql-editor.html
[+] https://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query

Note:- The 3rd party articles are shared on a best effort basis and with the intention to help, they are not endorsed by AWS, hence I would recommend you to test them in a testing environment first before implementing it in production.

If you still face any issues, please reach out to AWS Support via a case so that they can help you after checking your resources

AWS RDS: Lost connection to MySQL server during query ([Errno 54] Connection reset by peer)')

Relevant content