SQLAlchemy - Lost connection to MySQL server during query
Here's was my situation:
- The database was set up behind behind an AWS ELB and HAProxy.
- The idle connection timeout on the ELB was set to 60 mins.
- All the relevant timeouts on HAProxy seemed to be set to 60 mins too.
- The pool_recycle in SQLAlchemy was set to 30 mins.
- I was still seeing the occasional "Lost connection to MySQL server during query" when small queries were running after the connection had some time to sit around.
The solution ended up being setting my pool_recycle down to 5 mins, but I'm still not sure what was causing connections to time out after 5 mins.
There are definitely other things that can cause this problem too. For example, it can happen if your data exceeds max_allowed_packet. See this page for more details: https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html
Most of this also applies for "MySQL server has gone away".
You should also make sure your pool_recycle is set lower than your 'interactive_timeout' and 'wait_timeout' properties in the mysql config file to the values you need.