I see this issue (ConnectionLost on MySQL) come up at least once a month. It's
actually the reason I originally joined this discussion list, but I've yet to
see a wholly satisfactory solution.
Since there is no one true perfect generic way to safely rerun a failed
interaction after ConnectionLost - the smart thing to do is just raise the
exception and let the application deal with (or bomb out on) what *should* be a
rare occurrence indicating some sort of problem.
adabapi does exactly this.
However, with MySQL (>=v5.0 at least) you HAVE to make plans to deal with
ConnectionLost (or "MySQL server has gone away" if cp_reconnect != True) or
your twisted app WILL be quite broken. Unfortunately, there's no obvious way
to separate a real connection "problem" from the perfectly normal behavior of
the MySQL server closing an idle connection without making your code provider
specific.
e.g.
from MySQLdb import OperationalError
I've seen (and even begrudgingly implemented) non provider specific solutions
that just create a new connection every time, or that qualify connections
returned from the pool with "good_sql" before handing them over to the app.
But, that overhead is obviously not acceptable in many situations.
I suggest "the right way(tm)" to fix this is with an optional pool_recycle
kwarg that sets the maximum time that a connection can be reused before it must
be recycled (many ORM's provide precedent for this solution - sqlalchemy,
dajngo.db, etc.)
reconnectionpool.py
'''
Created on Aug 26, 2009
@author: clayg
'''
from time import time
from twisted.enterprise import adbapi
from twisted.python import log
class ReConnectionPool(adbapi.ConnectionPool):
'''
subclass of adbapi.ConnectionPool that supports pool_recycle
pool_recycle disabled by default (-1)
'''
def __init__(self, *args, **kwargs):
self.pool_recycle = kwargs.pop('pool_recycle', -1)
self.conn_starttime = {} # connections starttime, hashed on thread id
adbapi.ConnectionPool.__init__(self, *args, **kwargs)
def connect(self, *args, **kwargs):
# ask ConnectionPool for a connection
conn = adbapi.ConnectionPool.connect(self, *args, **kwargs)
# if pool_recycling is enabled
if self.pool_recycle > -1:
# get the start time for this connection
tid = self.threadID()
starttime = self.conn_starttime.get(tid)
now = time()
if not starttime:
# init starttime for new conn
self.conn_starttime[tid] = starttime = now
log.msg("Connection %s was created at %s." % (tid, now))
# if the connection is older than limit in pool_recycle
if (now - starttime >= self.pool_recycle):
self.disconnect(conn)
log.msg("Connection %s was recycled at %s." % (tid, now))
conn = adbapi.ConnectionPool.connect(self, *args, **kwargs)
self.conn_starttime[tid] = now
return conn
I think it be quite a bit less messy if it was implemented inside the
ConnectionPool class instead of a subclass. Someone else could probably do a
much better job than I, although I wouldn't mind taking a crack at it.
Thoughts?
Clay Gerrard
Office: 210-312-3443
Mobile: 210-788-9431
-----Original Message-----
From: twisted-python-boun...@twistedmatrix.com
[mailto:twisted-python-boun...@twistedmatrix.com] On Behalf Of Phil Christensen
Sent: Tuesday, August 25, 2009 10:30 AM
To: Twisted general discussion
Subject: Re: [Twisted-Python] OT - adbapi, connection timeouts, mysql - OT
On Aug 25, 2009, at 11:25 AM, Phil Mayers wrote:
> Phil Christensen wrote:
>> Honestly, I have never actually solved it. I pretty much only write
>> webapps these days, so when a ConnectionLost happens, it just
>> propagates up the stack and displays an error to the user. Not ideal,
>> by any means.
>
> It's hard to apply in the general case, but I like the way Zope
> handles
> this using the per-request transaction machinery.
>
> Basically, if any processing generates a "retry"able exception, all
> transactions attached to the request are rolled back and the entire
> request re-submitted.
That's definitely the preferable solution, but an additional problem
is you still can't use transactions with MyISAM-backed tables.
Yeah yeah, we should be using postgres, sqllite, or even InnoDB ;-)
Of course sometimes that's just not an option...
-phil
_______________________________________________
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Confidentiality Notice: This e-mail message (including any attached or
embedded documents) is intended for the exclusive and confidential use of the
individual or entity to which this message is addressed, and unless otherwise
expressly indicated, is confidential and privileged information of Rackspace.
Any dissemination, distribution or copying of the enclosed material is
prohibited.
If you receive this transmission in error, please notify us immediately by
e-mail
at ab...@rackspace.com, and delete the original message.
Your cooperation is appreciated.
_______________________________________________
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
