freebie freebie - 1 year ago 59
MySQL Question

Diagnosing 2013 Lost connection to MySQL

I am using SQLAlchemy with two MySQL databases. One of these being my development database hosted locally on my machine and the other is a MySQL server provided by ClearDB on Heroku for production.

I have a long running session open with the database while it performs a syncing operation with another service. On my local machine this finishes fine but on production I get the error (2013, 'Lost connection to MySQL server during query').

I've read other posts that say it can either be the size of the request is too big or the pool refresh variable that needs to be adjusted. I don't believe the transaction payload is relatively that large and setting a

variable when calling the SQLAlachemy
hasn't seemed to work.

Has anyone else experienced this problem or able to help me narrow down what the underlying reason is for that error - it seems like a catch all and I'm not sure where to go from here.

As requested in comments, both systems return the same values for
select @@interactive_timeout, @@wait_timeout
: 28800, 28800.


Answer Source

Even though the two databases appear to be using the same timeouts and config, generally. It turned out to be a timeout performed somewhere else by ClearDB.

ClearDB monitor connections and kill them when they are open for over a minute. I wasn't originally able to find this docuemnted.

The fix was actually setting the pool_recycle parameter to pool_recycle=60 on creating the engine. My previous attempt at this I was using an arbitrary number (because I didn't know ClearDB's timeout) higher than this.