There is a solution to the reconnection issue posted
here<http://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 <clay.gerr...@rackspace.com>wrote:
> 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-boun...@twistedmatrix.com [mailto:
> twisted-python-boun...@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.gerr...@rackspace.com <mailto:clay.gerr...@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-boun...@twistedmatrix.com
> > <mailto:twisted-python-boun...@twistedmatrix.com>
> > [mailto:twisted-python-boun...@twistedmatrix.com
> > <mailto:twisted-python-boun...@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 ab...@rackspace.com <mailto:ab...@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 ab...@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
>
_______________________________________________
Twisted-Python mailing list
Twisted-Python@twistedmatrix.com
http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
