[Twisted-Python] OT - adbapi, connection timeouts, mysql - OT

[Phil Christensen]

On Aug 24, 2009, at 9:14 PM, Garret Heaton wrote:
> On Mon, Aug 24, 2009 at 3:53 PM, Phil Christensen <phil at bubblehouse.org 
> > wrote:
>> I feel like I can think of any number of ways running the same  
>> series of queries twice would be a bad thing. I have some very  
>> important queries that are run inside a interaction because they do  
>> a series of operations in sequence. I can't use transactions  
>> because my tables are MyISAM.
>>
>> Perhaps I'm jumping at shadows, but automatic re-querying just  
>> seems dangerous to me. Only the particular application knows when  
>> it's safe.
>
> This is very true, and I should have mentioned it in my reply. Re- 
> running queries is only possible when you know your application can  
> do it safely.
>
> How would you solve the issue of a lost connection in your case?

Honestly, I have never actually solved it. I pretty much only write  
webapps these days, so when a ConnectionLost happens, it just  
propagates up the stack and displays an error to the user. Not ideal,  
by any means.

However, in my case, the only time I really get ConnectionLost errors  
is when the MySQL connection timeout closes idle connections in the  
pool. I accidentally fixed this months ago by setting up Nagios to  
monitor the web services, and it runs often enough that it usually  
hits every open connection within that timeout (8hrs).

The only safe way I can think of to do this is run a simple query  
(like SELECT 1) before every interaction. Then you would only  
reconnect/requery if that fails.

The only problem with this is that it still doesn't protect you from  
lost connections that occur during a multi-query interaction.

-phil