There is a solution to the reconnection issue posted here which one of my friends improved upon. He wasn't on the list at the time of this posting so I'm replying to this on his behalf. The improved solution is:
class ReconnectingConnectionPool(adbapi.ConnectionPool):



 
    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.msg("RCP: got error %s, retrying operation" %(e))



            conn = self.connections.get(self.threadID())



            self.disconnect(conn)


            # try the interaction again
            return adbapi.ConnectionPool._runInteraction(self, interaction, *args, **kw)
Also on GitHub: http://gist.github.com/174056

It's working well for both of us so far.


On Wed, Jul 22, 2009 at 7:06 AM, Clay Gerrard <clay.gerrard@rackspace.com> wrote:
In my experience "re-running the query" has not been sufficient.

All of the connections in the pool time out around the same time.

Using cp_reconnect just forces the "idle" connection (cursor) to be removed from the pool before raising the generic "ConnectionLost":

   def rollback(self):
       if not self._pool.reconnect:
           self._connection.rollback()
           return

       try:
           self._connection.rollback()
           curs = self._connection.cursor()
           curs.execute(self._pool.good_sql)
           curs.close()
           self._connection.commit()
           return
       except:
           log.err(None, "Rollback failed")

       self._pool.disconnect(self._connection)

       if self._pool.noisy:
           log.msg("Connection lost.")

       raise ConnectionLost()

But when I go to re-run the query it's very unlikely that I'll get that same thread id again (and therefore a fresh connection).  More than likely I'll get another stale connection which will also get dropped.  Repeat ad infinitum

I'm fairly sure the right thing to do is to make adbapi.Connection objects aware of their created time, and allow adbapi.ConnectionPool.connect to potentially refresh "old" connections based on an optional kwarg when creating the connection pool:

"pool_recycle"

That's what I'm working on anyway...

Clay Gerrard
Office: 210-312-3443
Mobile: 210-788-9431
-----Original Message-----
From: twisted-python-bounces@twistedmatrix.com [mailto:twisted-python-bounces@twistedmatrix.com] On Behalf Of Gabriel Rossetti
Sent: Wednesday, July 22, 2009 1:34 AM
To: Twisted general discussion
Subject: Re: [Twisted-Python] OT - adbapi, connection timeouts, mysql - OT

Hello Garret,

yes, I finally did did something like that, I am currently testing the code.

Thanks,
Gabriel

Garret Heaton wrote:
> Instead of trying to keep the connection alive you can also just
> reconnect when necessary. Example code here:
> http://stackoverflow.com/questions/207981/how-to-enable-mysql-client-auto-re-connect-with-mysqldb/982873#982873
>
> On Tue, Jul 21, 2009 at 2:18 PM, Clay Gerrard
> <clay.gerrard@rackspace.com <mailto:clay.gerrard@rackspace.com>> wrote:
>
>     Regarding the original question:
>     "how to make mysql's idle timeouts shorter so that I can debug my
>     code?"
>
>     You should be able to do that in the mysql shell:
>     mysql> show variables like '%timeout%';
>     +----------------------------+-------+
>     | Variable_name              | Value |
>     +----------------------------+-------+
>     | connect_timeout            | 5     |
>     | delayed_insert_timeout     | 300   |
>     | innodb_lock_wait_timeout   | 50    |
>     | innodb_rollback_on_timeout | OFF   |
>     | interactive_timeout        | 600   |
>     | net_read_timeout           | 30    |
>     | net_write_timeout          | 60    |
>     | slave_net_timeout          | 3600  |
>     | table_lock_wait_timeout    | 50    |
>     | wait_timeout               | 600   |
>     +----------------------------+-------+
>     10 rows in set (0.00 sec)
>
>     > set global variable interactive_timeout = 5;
>
>     But in my experience MySQLdb makes the idle connection timeout
>     very difficult to debug effectively.
>
>     Will twisted.adbapi.ConnectionPool ever offer a pool_recycle kw
>     like sqlalchemy?
>
>     Clay Gerrard
>     Office: 210-312-3443
>     Mobile: 210-788-9431
>     -----Original Message-----
>     From: twisted-python-bounces@twistedmatrix.com
>     <mailto:twisted-python-bounces@twistedmatrix.com>
>     [mailto:twisted-python-bounces@twistedmatrix.com
>     <mailto:twisted-python-bounces@twistedmatrix.com>] On Behalf Of
>     Werner Thie
>     Sent: Tuesday, July 21, 2009 4:05 PM
>     To: Twisted general discussion
>     Subject: Re: [Twisted-Python] OT - adbapi, connection timeouts,
>     mysql - OT
>
>     Hi Gabriel
>
>     had the same problem, solved it by having keepalive() called in a
>     LoopingCall(), MySQL sitting at defaults timingwise.
>
>     DB_DRIVER = "MySQLdb"
>
>     USERDB_ARGS = {
>       'host': '',
>       'db': '',
>       'user': '',
>       'passwd': '',
>       'cp_reconnect': True
>     }
>
>     storekeeper = StoreKeeper(DB_DRIVER, **USERDB_ARGS)
>
>     ka = task.LoopingCall(storekeeper.store.keepAlive)
>     ka.start(300)
>
>     class StoreKeeper(object):
>       def __init__(self, dbapiName, **params):
>         self.store = Store(dbapiName, **params)
>
>       def dbdisconn(self, reason):
>         print 'db disconnected for ', reason
>
>       def keepAlive(self):
>         d = self.store.runQuery('SELECT 1')
>         d.addErrback(self.dbdisconn)
>
>
>     #with store being something like:
>
>     class Store(object):
>       def __init__(self, dbapiName, **params):
>         self.__pool   = adbapi.ConnectionPool(dbapiName, **params)
>         print self.__pool.__getstate__()
>         self.runOperation('SET autocommit = %s', 1)
>
>       def runQuery(self, query, *args):
>         d = self.__pool.runInteraction(self.mapQuery, query, args)
>         return d
>
>       def mapQuery(self, curs, query, *args):
>         try:
>           curs.execute(query, *args)
>         except adbapi.ConnectionLost:
>           print
>           print '++++++++++++ rerunning query'
>           print
>           curs.execute(query, *args)                    #simply resend
>     query, assuming cp_reconnect=True
>         result = curs.fetchall()
>         columns = [d[0] for d in curs.description]
>         return [dict(zip(columns, r)) for r in result]
>
>       def runOperation(self, query, *args):
>         d = self.__pool.runOperation(query, args)
>         return d
>
>       def runInteraction(self, fun, queries=(), args=()):
>         d = self.__pool.runInteraction(fun, queries, args)
>         return d
>
>
>     HTH, Werner
>
>     Gabriel Rossetti wrote:
>     > Hello everyone,
>     >
>     > I have been experiencing the ConnectionError with adbapi &
>     > cp_reconnect=True. I know that because of the cp_reconnect=True
>     param
>     > tha is reconnects and that the query is not re-run. I have
>     written some
>     > code that should re-run the query in that case (if I get a
>     Failure back
>     > because of a ConnectionError), but it doesn't seem to work. My
>     question
>     > is if anyone knows how to make mysql's idle timeouts shorter so
>     that I
>     > can debug my code? I searched google and the mysql site with no
>     luck.
>     >
>     > thank you,
>     > Gabriel
>     >
>     > _______________________________________________
>     > Twisted-Python mailing list
>     > Twisted-Python@twistedmatrix.com
>     <mailto:Twisted-Python@twistedmatrix.com>
>     > http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>
>
>     _______________________________________________
>     Twisted-Python mailing list
>     Twisted-Python@twistedmatrix.com
>     <mailto: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 abuse@rackspace.com <mailto:abuse@rackspace.com>, and delete
>     the original message.
>     Your cooperation is appreciated.
>
>
>     _______________________________________________
>     Twisted-Python mailing list
>     Twisted-Python@twistedmatrix.com
>     <mailto:Twisted-Python@twistedmatrix.com>
>     http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Twisted-Python mailing list
> Twisted-Python@twistedmatrix.com
> http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>

_______________________________________________
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 abuse@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