There is a solution to the reconnection issue posted
herehttp://www.gelens.org/2008/09/12/reinitializing-twisted-connectionpool/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
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...
On Tue, Jul 21, 2009 at 2:18 PM, Clay Gerrard
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