[Twisted-web] adpapi cp_reconnect problem

[Phil Christensen]

On Oct 10, 2007, at 1:02 PM, Werner Thie wrote:
> Hi all
>
> From what I was able to google and what I could deduce from the  
> source/docs this is what I think the right way to get reconnects  
> after all:
>
> def runQuery(self, query, *args):
>   try:
>     d = self.pool.runInteraction(self.mapQuery, query, args)
>   except adbapi.ConnectionLost:
>     #simply resend query, assuming cp_reconnect=True
>     d = self.pool.runInteraction(self.mapQuery, query, args)
>   return d
>
> Am I on the right track?

Is this intended to be kept in a trivial subclass of ConnectionPool?  
I think I'd want to avoid that...

The only issue that could arise here is that subsequent connections  
might also be down. In a similar app I'm working on, my pool might  
have 5-10 broken connections if it's been sitting unused for awhile.  
Although ConnectionPool will remove the broken connection instances  
when they raise ConnectionLost, your followup query could also raise  
the same exception.

I would hesitate to put the code in some kind of loop, since it's  
difficult to tell at this point (i.e., in runQuery) **why** the  
connection is lost, and how critical that fact is to the rest of the  
application

I think the approach I'm going to take is to create a separate  
delayed event loop that periodically runs "SELECT 1;" or similar  
every hour or so. This will keep the connections live, and in the  
event that one of them goes down for some other reason, it will make  
sure they are removed from the pool.

Of course, the best solution would be to figure out how to turn off  
those disconnects in MySQL, but I haven't had much luck finding that  
info in the endless quagmire of mysql.com documentation.

-phil