[Twisted-Python] Consensus on how to handle "MySQL server has gone away"

Phil Mayers p.mayers at imperial.ac.uk
Wed Oct 3 04:28:06 EDT 2012


On 10/02/2012 06:09 PM, E S wrote:
> I have a service running that occasionally connects to a MySQL
> database. When there is no activity on it for some time, I eventually
> get the the error
>
> _mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')
>
> I have found a few posts on this group related to this issue
>
> http://twistedmatrix.com/pipermail/twisted-python/2007-July/015788.html
> http://twistedmatrix.com/pipermail/twisted-python/2007-October/016178.html
>
> as well as some tickets on the twisted homepage:
>
> http://twistedmatrix.com/trac/ticket/4404
> http://twistedmatrix.com/trac/ticket/4964
>
> I have also seen this referenced as a potential fix:
>
> http://www.gelens.org/2009/09/13/twisted-connectionpool-revisited/
>
> I currently have the cp_reconnect parameter set to True, but it does
> not appear to be doing the job. I don't really see much consensus on
> how to properly handle this issue. Some people seem to think that the
> cp_reconnect parameter should take care of it for you, other people
> say that cp_reconnect is only part of the solution and that you have
> to write your own error handling.

For what it's worth - I think adbapi is seriously sub-optimal in this 
regard. We have continual low-level problems with Twisted apps getting 
stuck due to hung/dead ConnectionPool. And if you forget cp_reconnect, 
well you are basically committing suicide. Your Twisted app will need a 
restart.

In particular - it's not clear to my why CP isn't using "cp_good_sql" to 
probe a connection *before* starting the transaction, and to 
close/re-open it transparently if it has died and cp_reconnect==1.

Instead, the only place the "good" SQL is run is *after* a rollback, so 
the next N transactions into the pool (where N is the number of threads) 
all fail, because they don't get as far as "rollback".

I think the behaviour it should be aiming for is clear:

  1. Test each connection with "good_sql" before beginning the user 
interaction/query
  2. If execeptions occur inside the user interaction, either at cursor 
methods like execute, or connection methods like commit, then:
     1. rollback - if *this* raises an exception, throw the conn away
     2. propagate the original exception upwards unchanged (maybe 
wrapped, maybe not)

cp_reconnect should be the default.



More information about the Twisted-Python mailing list