Twisted adbapi cp_reconnect not working

Question

I have a cyclone web service that talks to a MySQL database. When there is no activity for a while (more than 8 hours I'm guessing), I get the following error unti I restart the web service:

_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

I've seen this post regarding cp_reconnect, and I have implemented this when creating my connection pool:

pool = adbapi.ConnectionPool("MySQLdb", host=self.host, user=self.user,
    passwd=self.password, db=self.database, cursorclass=MySQLdb.cursors.DictCursor,
    cp_reconnect=True)

I would have thought this would have fixed it, and it seemed to for a while, but now I'm seeing the "MySQL server has gone away" error again after there is no activity on the server for a while.

I have read this MySQL documentation regarding wait_timeout, and I could fix it that way I suppose, but why doesn't the cp_reconnect feature work for me? I am interpreting the adbapi docs to mean that if you specify the cp_reconnect parameter, that adbadpi will handle the error sent by MySQL and re-attempt the query for you. So basically you don't have to handle the error directly in your code. Am I misunderstanding that?

Answer 1

I was looking for the solution for this a couple of years ago. The solution I found somewhere on the web and settled with was subclassing ConnectionPool and overriding _runInteraction, where you force reconnect on specific error codes. A quick google search lead me to this website: http://www.gelens.org/2009/09/13/twisted-connectionpool-revisited/ with a code included. I haven't had to think about dying MySQL connections ever again.

Answer 2

After some discussions on the twisted mailing list and looking through the twisted bug tracker, it turns out that this is a known bug that has not yet been fixed.

Here is a link to the mailing list discussion.

Here is a link to the twisted bug tracker issue.

Possible solutions:

1. Increase the wait_timeout value on the server.
2. Override methods in the ConnectionPool class, referenced above.
3. Setup a periodic "ping" in each connection to keep it alive.
4. Fix the bug in adbapi myself.

Of these, I'm probably going to go with #1.