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.

[anonymous]

Can someone describe what the problem is here? MySQL is so common that it seems very silly that the MySQL backend of trac is completly broken?

[anonymous]

Every X hours trac needs to be restarted. o_O

[cboos]

I've seen this today on the jQuery Trac.

Looking back at this issue (also at #5783), it seems that we miss a call to ping() when the connection is retrieved from the pool. So far we only do a ping() before the rollback(), which happens just before the connection is returned to the pool. But after that, the connection could stay in the pool for a while and timeout. At the next use (source:trunk/trac/db/pool.py@7479#L92), it would then raise the "MySQL server has gone away".

Quick untested hack to verify this theory (patch on 0.11-stable, r7551):

trac/db/pool.py

@@ -95 +95 @@
                     self._pool_key.pop(idx)
                     self._pool_time.pop(idx)
                     cnx = self._pool.pop(idx)
+                    if hasattr(cnx.cnx, 'ping'):
+                        cnx.cnx.ping()
                 # Third best option: Create a new connection
                 elif len(self._active) + len(self._pool) < self._maxsize:
                     cnx = connector.get_connection(**kwargs)

Could someone having the issue test this approach?

[dale.miller@…]

Maybe this will help.

I get the following error if I edit the startup wiki page and submit the change:

Traceback (most recent call last):
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.1-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.1-py2.5.egg/trac/web/chrome.py", line 702, 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.1-py2.5.egg/trac/web/api.py", line 169, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.1-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.1-py2.5.egg/trac/web/session.py", line 150, in __init__
    self.get_session(req.authname, authenticated=True)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.1-py2.5.egg/trac/web/session.py", line 164, in get_session
    super(Session, self).get_session(sid, authenticated)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.1-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.1-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')

The change is stored but I get the above.

My configuration is:

System Information
Trac: 	0.11.1
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.1
mod_python: 	3.3.1
Subversion: 	1.5.2 (r32768)
jQuery:	1.2.6

Here is "uname -a" of the processor I am using:

Linux mistichp 2.6.22.5-31-default #1 SMP 2007/09/21 22:29:00 UTC x86_64 x86_64 x86_64 GNU/Linux

This is what was appended to the trac log file:

2008-09-22 13:49:25,242 Trac[main] ERROR: (2013, 'Lost connection to MySQL server during query')
Traceback (most recent call last):
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.1-py2.5.egg/trac/web/main.py", line 423, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.1-py2.5.egg/trac/web/main.py", line 197, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.1-py2.5.egg/trac/wiki/web_ui.py", line 162, in process_request
    return self._render_view(req, versioned_page)
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.1-py2.5.egg/trac/wiki/web_ui.py", line 487, in _render_view
    for hist in latest_page.get_history():
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.1-py2.5.egg/trac/wiki/model.py", line 167, in get_history
    "ORDER BY version DESC", (self.name, self.version))
  File "/usr/local/lib64/python2.5/site-packages/Trac-0.11.1-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: (2013, 'Lost connection to MySQL server during query')

This is what was appended to the mysqld.log file:

080922 13:49:25 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=258048
max_used_connections=12
max_connections=100
threads_connected=10
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x236cb90
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x440c9fe0, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
New value of fp=0x236cb90 failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x2372d00 = SELECT version,time,author,comment,ipnr FROM wiki WHERE name='WikiStart' AND version<=28 ORDER BY version DESC
thd->thread_id=914
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Then a new mysqld.log file is started with:

Number of processes running now: 0
080922 13:49:25  mysqld restarted
080922 13:49:25  InnoDB: Started; log sequence number 0 2056477
080922 13:49:25 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  SUSE MySQL RPM

We have experienced several other occurrences causing the mysql to restart but they are seldom. Submitted a change to the wiki page does it every time for me. I have looked at permissions in the mysql area and everything looks good.

This is the only problem I have seen with Trac with our configuration. Hope this is helpful. Thank you.

[cboos]

Replying to dale.miller@…:

MySQL: server: "5.0.45", client: "5.0.45", thread-safe: 0

thread-safe: 0 seems to be a good hint here. Looks like MySQLdb was built in non-thread-safe mode.

What happens when you execute the following query at the mysql prompt?

SELECT version,time,author,comment,ipnr FROM
 wiki WHERE name='WikiStart' AND version<=28 ORDER BY version DESC

Does that also trigger a crash? If not, then it's definitely the thread-safety issue. Please rebuild your MySQLdb python bindings with thread-safety support enabled.

From the python mysqldb  README:

Building and installing
-----------------------

...
edit the [options] section of site.cfg:

    threadsafe
        thread-safe client library (libmysqlclient_r) if True (default);
	otherwise use non-thread-safe (libmysqlclient). You should
	always use the thread-safe library if you have the option;
	otherwise you *may* have problems.

Indeed ;-)

Now that I think about it, I see that comment:29 had the same thread-safe: 0 info, and same goes for the jQuery Trac ( #3130).

[jonas]

Replying to cboos:

Quick untested hack to verify this theory (patch on 0.11-stable, r7551):

*snip*

Could someone having the issue test this approach?

I've tested this patch now and it doesn't fix the problem but it's almost there. cnx.ping() seems to to raise an exception if the connection is no longer available, so we'll just need to catch that exception and discard the connection if that happens. I'll have a working patch and will commit it as soon as I've done some further testing.

[jonas]

Fixed in [7567] and [7568].

[anonymous]

Replying to cboos:

Replying to dale.miller@…:

MySQL: server: "5.0.45", client: "5.0.45", thread-safe: 0

thread-safe: 0 seems to be a good hint here. Looks like MySQLdb was built in non-thread-safe mode. What happens when you execute the following query at the mysql prompt? {{{ #!sql SELECT version,time,author,comment,ipnr FROM wiki WHERE name='WikiStart' AND version<=28 ORDER BY version DESC }}} Does that also trigger a crash? If not, then it's definitely the thread-safety issue. Please rebuild your MySQLdb python bindings with thread-safety support enabled.

You last statement "Does that also trigger a crash? If not, then it's definitely the thread-safety issue."

I did the statement you suggested and it did crash.

using mysql I did the following:

mysql> use trac;
Database changed

mysql> select version, time, author,comment,ipnr from wiki where name='WikeStart' and version<=28 order by version desc;

Which gave this error:

ERROR 2013 (HY000): Lost connection to MySQL server during query

I looked at the /var/lib/mysql and it contained:

tail -200 /var/lib/mysql/mysqld.log-20081004

Number of processes running now: 0
081003 15:17:40  mysqld restarted
081003 15:17:41  InnoDB: Started; log sequence number 0 2056477
081003 15:17:41 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  SUSE MySQL RPM
081003 15:20:33 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=258048
max_used_connections=5
max_connections=100
threads_connected=5
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x2262730
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x4418cfe0, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
New value of fp=0x2262730 failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x226a140 = SELECT version,time,author,comment,ipnr FROM wiki WHERE name='WikiStart' AND version<=33 ORDER BY version DESC
thd->thread_id=5
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
081003 15:20:33  mysqld restarted
081003 15:20:34  InnoDB: Started; log sequence number 0 2056477
081003 15:20:34 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  SUSE MySQL RPM
081003 15:21:07 - mysqld got signal 7;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=258048
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x21ccdc0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x44088fe0, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
New value of fp=0x21ccdc0 failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x22063b0 = SELECT version,time,author,comment,ipnr FROM wiki WHERE name='WikiStart' AND version<=33 ORDER BY version DESC
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
081003 15:21:07  mysqld restarted
081003 15:21:07  InnoDB: Started; log sequence number 0 2056477
081003 15:21:07 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  SUSE MySQL RPM
081007  8:26:07 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=258048
max_used_connections=12
max_connections=100
threads_connected=10
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x2324bd0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x442d1fe0, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
New value of fp=0x2324bd0 failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x232ad40 = select version, time, author,comment,ipnr from wiki where name='WikeStart' and version<=28 order by version desc
thd->thread_id=195
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

It definitely looks like a thread issue. I installed MySQL-python-1.2.2 in May when converting from Mantis to Trac and my site.cfg had:

[options]
# embedded: link against the embedded server library
# threadsafe: use the threadsafe client
# static: link against a static library (probably required for embedded)

embedded = False
threadsafe = True
static = False

So I am not sure why it is using 0 threads?

[anonymous]

Replying to anonymous:

Replying to cboos:

Replying to dale.miller@…:

MySQL: server: "5.0.45", client: "5.0.45", thread-safe: 0

thread-safe: 0 seems to be a good hint here. Looks like MySQLdb was built in non-thread-safe mode. What happens when you execute the following query at the mysql prompt?

I had a typo in what I tested

SELECT version, time, author, comment, ipnr FROM wiki 
    WHERE name='WikeStart' AND version<=28 ORDER BY version DESC;

Note the "WikeStart"

When I correct the typo the command errors with

ERROR 1105 (HY000): Unknown error

However, if I using "ORDER BY version ASC" it works.

When I tail the mysqld.log file following the "ORDER BY version DESC" error it contains:

Number of processes running now: 0
081007 11:13:21  mysqld restarted
081007 11:13:21  InnoDB: Started; log sequence number 0 2056477
081007 11:13:21 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  SUSE MySQL RPM
081007 11:28:07 - mysqld got signal 7;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=258048
max_used_connections=10
max_connections=100
threads_connected=10
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x22a9fb0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x442d1fe0, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
New value of fp=0x22a9fb0 failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x22b0120 = select version,time,author,comment,ipnr from wiki where name='WikiStart' and version <=28 order by version desc
thd->thread_id=16
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
081007 11:28:07  mysqld restarted
081007 11:28:07  InnoDB: Started; log sequence number 0 2056477
081007 11:28:07 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  SUSE MySQL RPM

I am not convinced that it is not running with threads and that the "About Trac" is showing 0 threads incorrectly.

MySQL:  	server: "5.0.45", client: "5.0.45", thread-safe: 0

There are some reports of the DESC problem with MySQL 5.0.45 which this test may be running into.

What does "About Trac" show on your system?

[cboos]

Replying to dale:

using mysql I did the following: {{{ mysql> use trac; Database changed mysql> select version, time, author,comment,ipnr from wiki where name='WikeStart?' and version<=28 order by version desc; }}}
<crash>

Sorry, it's probably even worse than just a broken MySQLdb, looks like either you've hit a bug in your version of MySQL (maybe this DESC problem you mention).

I couldn't reproduce the issue, but at least I verified that when MySQLdb is correctly set-up (i.e. [options] threadsafe = True in site.cfg), the version info should indeed show that thread-safe support is on:

MySQL:	server: "5.0.67-community-nt", client: "5.0.67", thread-safe: 1
MySQLdb:	1.2.2

To summarize:

• that this SELECT query also crashes when executed on the mysql command line shows that there's a quite serious issue in your MySQL server
• your MySQLdb is definitely not thread-safe, rebuild it yourself to be sure

[cboos]

PS: just to be really clear, setting the flag in site.cfg is not enough, the bindings need to be rebuilt (e.g. python setup.py install).

[cboos]

If after a rebuild and reinstallation of you MySQLdb bindings you still see thread-safe: 0, then chances are that you end up using another MySQL library at run-time.

Be sure to read:  http://code.google.com/p/modwsgi/wiki/ApplicationIssues#MySQL_Shared_Library_Conflicts

[Dale Miller <dale.miller@…>]

Replying to cboos:

If after a rebuild and reinstallation of you MySQLdb bindings you still see thread-safe: 0, then chances are that you end up using another MySQL library at run-time. Be sure to read:  http://code.google.com/p/modwsgi/wiki/ApplicationIssues#MySQL_Shared_Library_Conflicts

I restarted apache2 with php not in the startup and now have thread-safe on:

MySQL: 	server: "5.0.67-community", client: "5.0.67", thread-safe: 1

However, I still could not look at history on a wiki page, and in mysql itself, I tried "ORDER BY version DESC" and it returned with an error:"

mysql> SELECT version, time, author, comment, ipnr FROM wiki WHERE name='WikiStart' AND version<=5 ORDER BY version DESC;
ERROR 2013 (HY000): Lost connection to MySQL server during query

I finally found bug  36639 on the MySQL web page that said the problem I am seeing was unique to 64 bit pentium 4 under openSUSE 10.3 and gcc-4.2.1. The problem is not repeatable on any other machine (32/64 bit, intel/amd, suse or other distribution). I installed mysql 5.0.77 and the problem is gone. I also ALTERED my Trac mysql tables from MyISAM to InnoDB and have seen no problems. I have trac 0.11.3 installed at this time.

[cboos]

Perfect! Thanks for the update.