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

Clay Gerrard clay.gerrard at rackspace.com
Wed Jul 22 08:06:35 MDT 2009


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 at twistedmatrix.com [mailto:twisted-python-bounces at 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 at rackspace.com <mailto:clay.gerrard at 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 at twistedmatrix.com
>     <mailto:twisted-python-bounces at twistedmatrix.com>
>     [mailto:twisted-python-bounces at twistedmatrix.com
>     <mailto:twisted-python-bounces at 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 at twistedmatrix.com
>     <mailto:Twisted-Python at twistedmatrix.com>
>     > http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>
>
>     _______________________________________________
>     Twisted-Python mailing list
>     Twisted-Python at twistedmatrix.com
>     <mailto: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 <mailto:abuse at rackspace.com>, and delete
>     the original message.
>     Your cooperation is appreciated.
>
>
>     _______________________________________________
>     Twisted-Python mailing list
>     Twisted-Python at twistedmatrix.com
>     <mailto:Twisted-Python at twistedmatrix.com>
>     http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Twisted-Python mailing list
> Twisted-Python at twistedmatrix.com
> http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>

_______________________________________________
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.





More information about the Twisted-Python mailing list