Twisted adbapi cp_reconnect does not work

Question

I have a cyclone web service that is negotiating with a MySQL database. If there is no activity for some time (more than 8 hours, I guess), I get the following error until I restart the web service:

_mysql_exceptions.OperationalError: (2006, "MySQL server is gone")

I saw this post regarding cp_reconnect , and I implemented this when creating the 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 think that this would fix it, and it seemed for a while, but now I see that the "MySQL server has gone away" error again after there is no activity on the server for a while.

I read this MySQL documentation regarding wait_timeout , and I could fix it this way, I suppose, but why does the cp_reconnect function work for me? I interpret adbapi docs to indicate that if you specify the cp_reconnect parameter, adbadpi will handle the error sent by MySQL, and try the query again for you. So basically you do not need to handle the error directly in your code. I dont understand what?

Answer 1

After some discussion with a bound mailing list and looking at a distorted error finder, 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 a problem with garbled error errors .

Possible solutions:

• Increase the wait_timeout value on the server.
• Override the methods in the ConnectionPool class mentioned above .
• Set up a periodic "ping" in each connection to save it.
• Fix the error in adbapi.

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

Answer 2

I was looking for a solution for this a couple of years ago. The solution I found somewhere on the Internet and installed it was subclassed by ConnectionPool and overrides _runInteraction , where you forcefully connect to specific error codes. A quick Google search leads me to this site: http://www.gelens.org/2009/09/13/twisted-connectionpool-revisited/ with code included. I no longer had to think about killing MySQL connections.

Answer 3

Link http://www.gelens.org/2009/09/13/twisted-connectionpool-revisited/ no longer works, even the version of the web archive. Here's an excerpt from Twistar 1.3, which alludes to the same solution:

https://pypi.python.org/pypi/twistar

 class ReconnectingMySQLConnectionPool(adbapi.ConnectionPool): """ This connection pool will reconnect if the server goes away. This idea was taken from: http://www.gelens.org/2009/09/13/twisted-connectionpool-revisited/ """ def _runInteraction(self, interaction, *args, **kw): try: return adbapi.ConnectionPool._runInteraction(self, interaction, *args, **kw) except MySQLdb.OperationalError, e: if e[0] not in (2006, 2013): raise log.err("Lost connection to MySQL, retrying operation. If no errors follow, retry was successful.") conn = self.connections.get(self.threadID()) self.disconnect(conn) return adbapi.ConnectionPool._runInteraction(self, interaction, *args, **kw)