[Twisted-Python] 2006, 'MySQL server has gone away'

[Phil Christensen]

On Oct 15, 2007, at 8:06 AM, Yoann Aubineau wrote:

> 2007/9/20, Phil Christensen <phil at bubblehouse.org>:
>> On Sep 20, 2007, at 7:39 AM, Werner Thie wrote:
>>
>>> Hi all
>>>
>>> While using mySQL V 5.0.33 and MySQL-python-1.2.2 with twisted/
>>> adbapi with the following connection params
>> [snip snip snip]
>>> I noticed (2006, 'MySQL server has gone away') errors, which seem
>>> to be not recoverable from an adbapi standpoint.
>> [snip snip snip]
>>> Questions:
>>> - are there any adverse effects in applying this patch and setting
>>> reconnect: 1 in DB_ARGS?
>>>
>>> - is there a better, safer way to avoid this nasty error?
>>>
>>> Thxs, Werner
>>
>> I believe this is what the 'cp_reconnect' keyword argument to the
>> ConnectionPool constructor does.
>
> In case it helps:
>
> The cp_reconnect keyword is mandatory but not sufficient for what I've
> experienced. At least with MySQL server version 4.1.7, a disconnection
> raises a generic OperationalError which one has to parse to know what
> actually happened.
>
> Hence the need to subclass ConnectionPool and surcharge
> _runInteraction for adding the ability to retry on MySQL connection
> lost. Or maybe is there a better way to do that?

Are the lost connections you're experiencing due to server idle  
timeouts, or actual network issues? I believe Werner was referring to  
MySQL 5.0+ "security feature" that automatically closes idle  
connections after 8 hours. We discussed this a little further in this  
thread:

	http://twistedmatrix.com/pipermail/twisted-web/2007-October/003541.html

If it is an idle timeout you're running into, you might be able to  
turn it off. Apparently in older versions of MySQL it's possible to  
turn this feature off, although that depends on your take as to  
whether leaving a db connection open is a significant security risk  
or not.

> ---- CODE ----
>
> import twisted.enterprise.adbapi
> try:
>     from MySQLdb import OperationalError
> except ImportError:
>     OperationalError = None
>
> class EnhancedConnectionPool(adbapi.ConnectionPool):
>     def _runInteraction(self, *args, **kwargs):
>         try:
>             d = abdapi.ConnectionPool._runInteraction(self, *args,  
> **kwargs)
>         except OperationalError, e:
>             errormsg = str(e).lower()
>             messages = (
>                 "lost connection to mysql server during query",
>                 "server has gone away"
>             )
>             for msg in messages:
>                 if msg in errormsg:
>                     d = abdapi.ConnectionPool._runInteraction(self,
> *args, **kwargs)
>                     return d
>             else:
>                 raise
>         return d
>
> ---- / CODE ----

The only issue with this approach -- what happens if you've got more  
than one dead connection in the pool? You're still going to need code  
further up the call stack that can deal with the possibility of a  
failed query.

The other thing to consider is that the adbapi layer already detects  
broken connections in a database-agnostic fashion. If a query fails  
for any reason, a rollback occurs (if possible) and then a known good  
query ("SELECT 1") is attempted. If either of those steps fails,  
ConnectionLost is raised.

If this behavior is different for you, I'd be interested to know your  
MySQLdb version...I had some very strange issues with prior versions  
that were only fixed with 1.2.2.

Granted, your method is slightly more efficient at the expense of the  
MySQLdb dependency, but if you're going to need to detect the lost  
connections at the application level anyways, you might as well stick  
with the stock ConnectionPool.

-phil