- Newest
- Most votes
- Most comments
Hello Sébastien, I'm sorry to hear that your DB session disconnected.
When executing long-running operations (DCLs like OPTIMIZE, DDLs like ALTER, and even large DMLs or selects) the database can go an extended period of time where it does not send/receive anything from the network. This can cause the session to hit network-level or DB-level timeouts, which can happen on the client, on a network device (think firewalls for example) in the network path, or on the server.
To prevent such disconnections from happening, we recommend
- Do not run long-running operations from your personal computer.
- Reduce the network "distance" between the client machine that connects to the database, and the database. In RDS and Aurora's case we recommend this client be an EC2 machine on the same VPC and AZ as the database.
- Ensure the client machine has TCP keepalives enabled. Amazon Redshift has a good document on how to do this.
- Connect to the client machine in a way where your personal computer disconnecting from the client does not interrupt the MySQL session. In Linux there are a few options for this, I use and recommend "screen". This web page has a good how-to on screen.
- If the client is not on the same VPC, check also the maximum transmission unit (MTU) of the whole network path. Again, Redshift has good documentation on the topic.
Also, not related to the connection drop error, but with long-running ALTER operations in Aurora MySQL, I'd recommend keeping an eye in the Aurora Instance's local storage space - this link shows how, and this Aurora MySQL document has the storage size per instance class/size.
Hope these help. Should you still see disconnects after implementing the above, please open a support case with us, for an engineer to help you troubleshoot your specific scenario.
