[Twisted-Python] 2006, 'MySQL server has gone away'
Phil Christensen
phil at bubblehouse.org
Mon Oct 15 10:46:38 EDT 2007
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
More information about the Twisted-Python
mailing list