There is a solution to the reconnection issue posted <a href="http://www.gelens.org/2008/09/12/reinitializing-twisted-connectionpool/">here</a> which one of my friends improved upon. He wasn&#39;t on the list at the time of this posting so I&#39;m replying to this on his behalf. The improved solution is:<br>

<pre><div class="line" id="LC5"><span class="k">class</span> <span class="nc">ReconnectingConnectionPool</span><span class="p">(</span><span class="n">adbapi</span><span class="o">.</span><span class="n">ConnectionPool</span><span class="p">):</span></div>

<div class="line" id="LC6"> </div><div class="line" id="LC7">    <span class="k">def</span> <span class="nf">_runInteraction</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">interaction</span><span class="p">,</span> <span class="o">*</span><span class="n">args</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span></div>

<div class="line" id="LC8">        <span class="k">try</span><span class="p">:</span></div><div class="line" id="LC9">            <span class="k">return</span> <span class="n">adbapi</span><span class="o">.</span><span class="n">ConnectionPool</span><span class="o">.</span><span class="n">_runInteraction</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">interaction</span><span class="p">,</span> <span class="o">*</span><span class="n">args</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span></div>

<div class="line" id="LC10">        <span class="k">except</span> <span class="n">MySQLdb</span><span class="o">.</span><span class="n">OperationalError</span><span class="p">,</span> <span class="n">e</span><span class="p">:</span></div>

<div class="line" id="LC11">            <span class="k">if</span> <span class="n">e</span><span class="p">[</span><span class="mf">0</span><span class="p">]</span> <span class="ow">not</span> <span class="ow">in</span> <span class="p">(</span><span class="mf">2006</span><span class="p">,</span> <span class="mf">2013</span><span class="p">):</span></div>

<div class="line" id="LC12">                <span class="k">raise</span></div><div class="line" id="LC13">            <span class="n">log</span><span class="o">.</span><span class="n">msg</span><span class="p">(</span><span class="s">&quot;RCP: got error </span><span class="si">%s</span><span class="s">, retrying operation&quot;</span> <span class="o">%</span><span class="p">(</span><span class="n">e</span><span class="p">))</span></div>

<div class="line" id="LC14">            <span class="n">conn</span> <span class="o">=</span> <span class="bp">self</span><span class="o">.</span><span class="n">connections</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">threadID</span><span class="p">())</span></div>

<div class="line" id="LC15">            <span class="bp">self</span><span class="o">.</span><span class="n">disconnect</span><span class="p">(</span><span class="n">conn</span><span class="p">)</span></div><div class="line" id="LC16">

            <span class="c"># try the interaction again</span></div><div class="line" id="LC17">            <span class="k">return</span> <span class="n">adbapi</span><span class="o">.</span><span class="n">ConnectionPool</span><span class="o">.</span><span class="n">_runInteraction</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">interaction</span><span class="p">,</span> <span class="o">*</span><span class="n">args</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span></div>

</pre>Also on GitHub: <a href="http://gist.github.com/174056">http://gist.github.com/174056</a><br><br>It&#39;s working well for both of us so far.<br><br><br><div class="gmail_quote">On Wed, Jul 22, 2009 at 7:06 AM, Clay Gerrard <span dir="ltr">&lt;<a href="mailto:clay.gerrard@rackspace.com">clay.gerrard@rackspace.com</a>&gt;</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;">In my experience &quot;re-running the query&quot; has not been sufficient.<br>
<br>
All of the connections in the pool time out around the same time.<br>
<br>
Using cp_reconnect just forces the &quot;idle&quot; connection (cursor) to be removed from the pool before raising the generic &quot;ConnectionLost&quot;:<br>
<br>
    def rollback(self):<br>
        if not self._pool.reconnect:<br>
            self._connection.rollback()<br>
            return<br>
<br>
        try:<br>
            self._connection.rollback()<br>
            curs = self._connection.cursor()<br>
            curs.execute(self._pool.good_sql)<br>
            curs.close()<br>
            self._connection.commit()<br>
            return<br>
        except:<br>
            log.err(None, &quot;Rollback failed&quot;)<br>
<br>
        self._pool.disconnect(self._connection)<br>
<br>
        if self._pool.noisy:<br>
            log.msg(&quot;Connection lost.&quot;)<br>
<br>
        raise ConnectionLost()<br>
<br>
But when I go to re-run the query it&#39;s very unlikely that I&#39;ll get that same thread id again (and therefore a fresh connection).  More than likely I&#39;ll get another stale connection which will also get dropped.  Repeat ad infinitum<br>


<br>
I&#39;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 &quot;old&quot; connections based on an optional kwarg when creating the connection pool:<br>


<br>
&quot;pool_recycle&quot;<br>
<br>
That&#39;s what I&#39;m working on anyway...<br>
<div class="im"><br>
Clay Gerrard<br>
Office: 210-312-3443<br>
Mobile: 210-788-9431<br>
-----Original Message-----<br>
</div><div><div></div><div class="h5">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 Gabriel Rossetti<br>


Sent: Wednesday, July 22, 2009 1:34 AM<br>
To: Twisted general discussion<br>
Subject: Re: [Twisted-Python] OT - adbapi, connection timeouts, mysql - OT<br>
<br>
Hello Garret,<br>
<br>
yes, I finally did did something like that, I am currently testing the code.<br>
<br>
Thanks,<br>
Gabriel<br>
<br>
Garret Heaton wrote:<br>
&gt; Instead of trying to keep the connection alive you can also just<br>
&gt; reconnect when necessary. Example code here:<br>
&gt; <a href="http://stackoverflow.com/questions/207981/how-to-enable-mysql-client-auto-re-connect-with-mysqldb/982873#982873" target="_blank">http://stackoverflow.com/questions/207981/how-to-enable-mysql-client-auto-re-connect-with-mysqldb/982873#982873</a><br>


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