MySQL connections don't reconnect after idle timeout

[willie@…]

I tried to set up a new Trac using 0.10b1 and the MySQL backend. Installation went well and the install came up and worked exactly as expected. However, the next day, I'm receiving error 2006 (MySQL server has gone away) from the MySQLdb driver.

As far as I can tell, this is due to the idle timeout MySQL uses for client connections (8 hours by default, see http://dev.mysql.com/doc/refman/5.0/en/gone-away.html). Restarting the web server resurrects the connections. I can even refresh the browser view a few times and it will come back to life. I imagine this is because the handler now sees an invalid connection and creates a new one.

Here is the Python traceback:

Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 335, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 192, in dispatch
    req.perm = PermissionCache(self.env, req.authname)
  File "/usr/lib/python2.4/site-packages/trac/perm.py", line 267, in __init__
    self.perms = PermissionSystem(env).get_user_permissions(username)
  File "/usr/lib/python2.4/site-packages/trac/perm.py", line 231, in get_user_permissions
    for perm in self.store.get_user_permissions(username):
  File "/usr/lib/python2.4/site-packages/trac/perm.py", line 111, in get_user_permissions
    cursor.execute("SELECT username,action FROM permission")
  File "/usr/lib/python2.4/site-packages/trac/db/util.py", line 48, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.4/site-packages/trac/db/util.py", line 48, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (2006, 'MySQL server has gone away')

The suggested fix would be to trap error 2006 and immediately attempt to reconnect to the database. If the error is returned again, go ahead and fail.

[jnanney@…]

While I don't have a fix, this is a workaround I use and it works for me.

See http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html and http://dev.mysql.com/doc/refman/4.1/en/using-system-variables.html for explinations of the options below.

in /etc/my.cnf you can add this...

# 3 day timeout in seconds [mysqld] wait_timeout=259200 interactive_timeout=259200

The default value for wait_timeout is 28800 which is 8 hours. wait_timeout is for connections from TCP/IP.

I also found this.

http://sourceforge.net/tracker/index.php?func=detail&aid=1483074&group_id=22307&atid=374934

Which is a patch against python-mysqldb to allow reconnect=1 to be used in the connect method.

This may be an option to add into mysql_backend.py once the patch above is added to python-mysqldb.

[jnanney@…]

The wiki formatting messed me up above.

In /etc/my.cnf

[mysqld]
wait_timeout=259200
interactive_timeout=259200

[mgood]

Here's an (untested) patch that will use DBUtils if it's installed to create a SolidConnection which will automatically reconnect if the connection is lost. Based on the DBUtils code it looks like this is not as simple a problem to solve as it may seem. The DBUtils "SolidDB.py" file seems to be standalone, so it'd be nice to include it with Trac, but based on the Open Software License I'm not sure if that would be possible.

trac/db/mysql_backend.py

@@ -18 +18 @@
 
 from trac.core import *
 from trac.db.api import IDatabaseConnector
-from trac.db.util import ConnectionWrapper
+from trac.db.util import ConnectionWrapper, solid_connect
 
 _like_escape_re = re.compile(r'([/_%])')
 
@@ -136 +136 @@
         # some point, this hack should be removed, and a strict requirement
         # on 1.2.1 made.  -dilinger
         if (self._mysqldb_gt_or_eq((1, 2, 1))):
-            cnx = MySQLdb.connect(db=path, user=user, passwd=password,
-                                  host=host, port=port, charset='utf8')
+            cnx = connect(MySQLdb, db=path, user=user, passwd=password,
+                          host=host, port=port, charset='utf8')
         else:
-            cnx = MySQLdb.connect(db=path, user=user, passwd=password,
-                                  host=host, port=port, use_unicode=True)
+            cnx = connect(MySQLdb, db=path, user=user, passwd=password,
+                          host=host, port=port, use_unicode=True)
             self._set_character_set(cnx, 'utf8')
         ConnectionWrapper.__init__(self, cnx)
 

trac/db/util.py

@@ -71 +71 @@
 
     def cursor(self):
         return IterableCursor(self.cnx.cursor())
+
+try:
+    from DBUtils.SolidDB import connect as connect
+except ImportError:
+    def connect(dbapi, maxusage=0, setsession=None, *args, **kwargs):
+        return dbapi.connect(*args, **kwargs)

[cboos]

Probably this can wait for 0.10.1, as MySQL support is anyway deemed to be "experimental " for 0.10...

[anonymous]

So long as it doesn't keep getting bumped, lest "experimental" become a synonym for "unsupported"... ;-)

[cboos]

Isn't r3530 also addressing this issue?

[jjb@…]

Why does trac use a persistent connection in the first place? Why not use a connection per query or transaction?

[cboos]

Using a pool of database connections is a common way to limit the amount of resources used (never use more than the max specified number of concurrent connections) and at the same time to be more reactive (most of the time, there's already an open connection waiting to be used).

That being said, you can experiment with having a connection per query, and if this proves more convenient in the case of MySQL, we could make that configurable.

Index: trac/db/mysql_backend.py
===================================================================
--- trac/db/mysql_backend.py	(revision 3968)
+++ trac/db/mysql_backend.py	(working copy)
@@ -99,7 +99,7 @@
 class MySQLConnection(ConnectionWrapper):
     """Connection wrapper for MySQL."""
 
-    poolable = True
+    poolable = False
 
     def _mysqldb_gt_or_eq(self, v):
         """This function checks whether the version of python-mysqldb

[cboos]

Ah, and I forgot to say that the problem reported here is probably solved in trunk, but not yet ported to 0.10-stable (I'm waiting for feedback).

You could try to apply the corresponding patch on 0.10:

diff:trunk/trac/db@3918:trunk/trac/db@3934

[jjb@…]

tweaking title...

[mrbrush]

None of the patches referenced or listed here worked for me.

[anonymous]

Replying to jnanney@gmail.com:

The wiki formatting messed me up above. In /etc/my.cnf [mysqld]
wait_timeout=259200
interactive_timeout=259200

After adding this to my my.cnf the error occurs more often for me.

[cboos]

I'd be very interested to know if this problem still happens with 0.10.2

[bennet]

I applied 0.10.2 two days ago and I still have the error "Mysql has gone away" every morning. I also experimented with the timeout parameter in trac.ini which seems to have no effect.

[bennet]

Replying to bennet:

I applied 0.10.2 two days ago and I still have the error "Mysql has gone away" every morning. I also experimented with the timeout parameter in trac.ini which seems to have no effect.

A workaround for me is to restart the httpd service every morning through a cron job at 6 ;-)

[cboos]

Can you please post a backtrace for this issue happening with either trunk, or the 0.10.2 or 0.10.3dev code?

[Scott MacVicar <scott@…>]

MySQL has something built into its API to check for inactive connections, its called ping which MySQLdb provides a wrapper for.

The following patch will call the ping method prior to allocating out the DB connection though this is only going to happen on people who have a short wait_timeout or a fairly inactive Trac installation.

[Scott MacVicar <scott@…>]

Patch to ping a MySQL connection from a pool before using it.

[cboos]

Thanks for the patch. Maybe you also have an idea why the current code doesn't work... Just being curious: why doesn't the cnx.rollback() at line 58 fail if the connection has stalled?

Also, it would be useful to know which are the MySQL versions and MySQLdb versions supporting this.

[Scott MacVicar <scott@…>]

The ping API call has been available since MySQL 3.22.1 onwards so thats about 8 years, I didn't know off the top of my head when it was implemented in MySQLdb but according to their SVN browser its been there since 0.9.0b1.

I'm pretty sure the reason that rollback doesn't catch this is due to the table type / auto commit. If MyISAM (default type) is used then there is no transaction support so rollback is worthless, the same if auto_commit is enabled.

cnx.rollback() wraps around a MySQL api call called mysql_rollback() which could be doing some automagic checking for all of these rather than a simple SQL "Rollback" statement that would trigger the error.

[cboos]

In r4341 and r4342, I added the ping() call, but directly in the mysql_backend.py file, that's a bit cleaner.

I'll leave the ticket opened until we get confirmation that this indeed works.

[bennet]

Unfortunately this patch [4363] did not work for me. When I restart the httpd service the error is gone. Running FC5, mysql 5.0.22, httpd 2.2.2, mod_python.
Please find the following:

Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 387, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 191, in dispatch
    chosen_handler = self._pre_process_request(req, chosen_handler)
  File "/usr/lib/python2.4/site-packages/trac/web/main.py", line 263, in _pre_process_request
    chosen_handler = f.pre_process_request(req, chosen_handler)
  File "/usr/lib/python2.4/site-packages/trac/versioncontrol/api.py", line 73, in pre_process_request
    self.get_repository(req.authname) # triggers a sync if applicable
  File "/usr/lib/python2.4/site-packages/trac/versioncontrol/api.py", line 101, in get_repository
    repos = self._connector.get_repository(rtype, rdir, authname)
  File "/usr/lib/python2.4/site-packages/trac/versioncontrol/svn_fs.py", line 260, in get_repository
    crepos = CachedRepository(self.env.get_db_cnx(), repos, None, self.log)
  File "/usr/lib/python2.4/site-packages/trac/versioncontrol/cache.py", line 34, in __init__
    self.sync()
  File "/usr/lib/python2.4/site-packages/trac/versioncontrol/cache.py", line 56, in sync
    cursor.execute("SELECT value FROM system WHERE name='repository_dir'")
  File "/usr/lib/python2.4/site-packages/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.4/site-packages/trac/db/util.py", line 51, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 137, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
    raise errorclass, errorvalue
ReferenceError: weakly-referenced object no longer exists

[cboos]

And what is the version of the MySQLdb python bindings? If not the latest, you should upgrade and check if that error is still there in their latest package.

[anonymous]

Replying to cboos:

And what is the version of the MySQLdb python bindings? If not the latest, you should upgrade and check if that error is still there in their latest package.

MySQL-python 1.2.0-3.2.2, the version which is the latest in fedora core repository. I am going to upgrade to 1.2.2 and will let you know whether it worked, sorry if this is the issue.

[bennet]

I upgraded to MySQL-python 1.2.1_p2 yesterday and the trac was still alive this morning.

Note: MySQL-python 1.2.2b2 did not work at all! When entering unicode chars into a wiki page (e.g. ö) and submitting this page it cuts the whole page from this char on without giving an error message.

[bennet]

running this patch for more than a week now ... never had this timeout issue again.

[cboos]

Great, so the fix provided by Scott MacVicar works.

reopening for changing the resolution

[cboos]

So the issue was wixed by r4341 (trunk) and r4342 (0.10-stable).

I think we should start to document the specifics of the MySqlDb Python bindings for MySQL, in particular the version requirements reported by bennet in comment:24 and 25.

[Scott MacVicar <scott@…>]

Its a thread safe issue with MySQLDb, see http://wolfram.kriesing.de/blog/index.php/2006/multithreading-with-mysqldb-and-weakrefs for more information on the problem.

[anonymous]

Replying to bennet:

I upgraded to MySQL-python 1.2.1_p2 yesterday and the trac was still alive this morning. Note: MySQL-python 1.2.2b2 did not work at all! When entering unicode chars into a wiki page (e.g. ö) and submitting this page it cuts the whole page from this char on without giving an error message.

I know this issue is marked as closed but I am seeing this problem 2 years later. I am using:

Trac:  	0.11
Python: 	2.5.1 (r251:54863, Sep 21 2007, 22:46:31) [GCC 4.2.1 (SUSE Linux)]
setuptools: 	0.6c8
MySQL: 	server: "5.0.45", client: "5.0.45", thread-safe: 0
MySQLdb: 	1.2.2
Genshi: 	0.5
mod_python: 	3.3.1
Subversion: 	1.4.4 (r25188)
jQuery:	1.2.3

Here is the errors I receive when updating a wiki page and submitting the changes. The changes are stored but this page is displayed.

Traceback (most recent call last):
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/api.py", line 339, in send_error
    'text/html')
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/chrome.py", line 708, in render_template
    if not req.session or not int(req.session.get('accesskeys', 0)):
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/api.py", line 168, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/main.py", line 257, in _get_session
    return Session(self.env, req)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/session.py", line 142, in __init__
    self.get_session(req.authname, authenticated=True)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/session.py", line 156, in get_session
    super(Session, self).get_session(sid, authenticated)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/session.py", line 56, in get_session
    (sid, int(authenticated)))
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (2006, 'MySQL server has gone away')

I am not going to change the settings on this issue at this time. I want to research other issues first.

[info@…]

This issue must be reopend, as I have the same problem with 0.11.1 ...

mysql_ping.patch by Scott can be applied and then fixes the problem ... please patch the trunk + 0.11.1 ...

[info@…]

Well no ... the patch still doesn't fix the problem ...

[info@…]

http://trac.edgewall.org/ticket/3645#comment:8 is a workaround, but another patch should be provided ...

[jonas]

I'm moving this to 0.11.2 since I guess this is quite annoying. But until then the obvious workaround is to configure the mysql server not to automatically close open connections

[anonymous]

i see this in trunk very often.. can't find info on how to reconfigure mysql either.

[anonymous]

Replying to cboos:

Using a pool of database connections is a common way to limit the amount of resources used (never use more than the max specified number of concurrent connections) and at the same time to be more reactive (most of the time, there's already an open connection waiting to be used). That being said, you can experiment with having a connection per query, and if this proves more convenient in the case of MySQL, we could make that configurable. {{{ Index: trac/db/mysql_backend.py =================================================================== --- trac/db/mysql_backend.py (revision 3968) +++ trac/db/mysql_backend.py (working copy) @@ -99,7 +99,7 @@ class MySQLConnection(ConnectionWrapper?): """Connection wrapper for MySQL.""" - poolable = True + poolable = False def _mysqldb_gt_or_eq(self, v): """This function checks whether the version of python-mysqldb }}}

That doesn't do anything.. the connection still drops.