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