[Twisted-Python] OT - adbapi, connection timeouts, mysql - OT

[Clay Gerrard]

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-bounces at twistedmatrix.com [mailto:twisted-python-bounces at 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 at 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 abuse at rackspace.com, and delete the original message. 
Your cooperation is appreciated.