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

[Garret Heaton]

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>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] 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
> > 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.
>
>
> _______________________________________________
> Twisted-Python mailing list
> Twisted-Python at twistedmatrix.com
> http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://twistedmatrix.com/pipermail/twisted-python/attachments/20090721/3410ca11/attachment-0001.htm