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

[E S]

On Wed, Oct 3, 2012 at 3:39 PM, Glyph <glyph at twistedmatrix.com> wrote:
>
> Le Oct 3, 2012 à 6:36 AM, Augusto Mecking Caringi <augustocaringi at gmail.com> a écrit :
>
>> On Wed, Oct 3, 2012 at 8:27 AM, Itamar Turner-Trauring
>> <itamar at itamarst.org> wrote:
>>> On 10/03/2012 04:28 AM, Phil Mayers wrote:
>>>> I think the behaviour it should be aiming for is clear:
>>>> 1. Test each connection with "good_sql" before beginning the user
>>>> interaction/query
>>>
>>> The problem is that this adds latency; this can add up to quite a
>>> slowdown if your database server is on a remote server and you're doing
>>> lots of single queries (as opposed to runInteraction).
>>
>> Hi!
>>
>> Sometime ago I faced some related problems and I found this code,
>> "ReconnectingConnectionPool":
>>
>> http://www.gelens.org/2009/09/13/twisted-connectionpool-revisited/
>>
>> What are your opinion about it?
>
> If there are bugs in Twisted, or important missing features, step 1 should be to file a bug – first, of course, searching for duplicates – at <http://twistedmatrix.com/trac/newticket>.  Especially if the workaround involves calling or overriding some private, internal implementation detail.  If you're going to blog about a hack that fixes the problem for you, that post should link to the ticket, so that Twisted can move forward and provide a good experience for people getting started with it.
>
> I hate the idea that step 1 for some poor new Twisted user would be to go searching around a couple dozen external websites to apply undocumented hacks to try to just get something basic, like relational database communication, to work acceptably for their application.
>
> So, my opinion is that either this isn't a real problem, in which case you shouldn't use it, or it is a real problem, in which case Jeffrey Gelens and powdahound should open a ticket and explain why it's necessary :).
>
> -glyph
>
>
> _______________________________________________
> Twisted-Python mailing list
> Twisted-Python at twistedmatrix.com
> http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python

Well, I did reference two bugs already in the twisted bug tracker that
I believe are related to this issue (4964 definitely is anyway). These
issues have been in the bug tracking system for years now so I'm not
holding my breath on fixes any time soon. I appreciate your desire to
make twisted as good as it can be, but we all have schedules to meet
and hacks, documented or not, are sometimes necessary to move the
project along. So I definitely will consider input from people who
have run into this situation and found a solution that has worked for
them.

If you're not convinced it's a real problem, it's pretty easy to
recreate. Assuming your MySQL wait_timeout = 2, you can run the
following code and get the (2006, 'MySQL server has gone away') error:


from twisted.internet import reactor, defer
from twisted.enterprise import adbapi
import MySQLdb
import MySQLdb.cursors
from time import sleep

pool = adbapi.ConnectionPool("MySQLdb", host="1.2.3.4",
user="someuser", passwd="xxx", db="someschema",
cursorclass=MySQLdb.cursors.DictCursor, cp_reconnect=True)

def gopherIt(txn):
   txn.execute("UPDATE Table1 SET Field1 = 123 WHERE Field2 = 456")
   sleep(3)
   txn.execute("UPDATE Table1 SET Field1 = 123 WHERE Field2 = 456")

def done(x):
   print "done"

def error(f):
   print f.value

d = pool.runInteraction(gopherIt)
d.addCallback(done)
d.addErrback(error)

reactor.callLater(4, reactor.stop)
reactor.run()


So, unless I have a misunderstanding about what cp_reconnect is
supposed to do, this is a documented bug. The question is what, if
anything, can be done about it in the short term. Possible solutions
are:

1. Increase the wait_timeout value on the server
2. Override methods in the ConnectionPool class
(http://www.gelens.org/2009/09/13/twisted-connectionpool-revisited/)
3. Setup a periodic "ping" in each connection to keep it alive
4. Man up and fix adbapi myself

I'm leaning towards #1 since it seems to be least invasive, but I am
open to other opinions.