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

[Clay Gerrard]

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.