[Twisted-Python] 2006, 'MySQL server has gone away'
Hi all While using mySQL V 5.0.33 and MySQL-python-1.2.2 with twisted/adbapi with the following connection params DB_DRIVER = "MySQLdb" DB_ARGS = { 'host': 'xxx.xxx.xxx.xxx', 'db': 'db', 'user': 'dbuser', 'passwd': 'dbpasswd' } I noticed (2006, 'MySQL server has gone away') errors, which seem to be not recoverable from an adbapi standpoint. Further analysis and reading the mySQL manual showed, that the mySQL server in versions > 5 seems to close open connections after (default) 8 hours. The reconnect feature of the db is disabled by default and there is no parameter to preset it via MySQL-python. Further digging brought up this link http://sourceforge.net/tracker/index.php?func=detail&aid=1483074&group_id=22307&atid=374934 which patches the (also 1.2.2) version such, that a reconnect keyword is introduced in the DB_ARGS. 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
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. I'm currently using this feature with MySQL 5 in a long-running webapp, with much success. -phil
2007/9/20, Phil Christensen <phil@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? ---- 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 ----
On Oct 15, 2007, at 8:06 AM, Yoann Aubineau wrote:
2007/9/20, Phil Christensen <phil@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
participants (3)
-
Phil Christensen -
Werner Thie -
Yoann Aubineau