"ProgrammingError: closing a closed connection" when Trac has been running for a while

[deryl.seale@…]

I am using the latest stable version of Trac with Apache2 (mod_python) and MySQL (5.0.27). Everything seems to be running fine, but after Trac has been runnig a while (~day) I get the following error when I log in:

Traceback (most recent call last):
  File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 398, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 183, in dispatch
    req.perm = PermissionCache(self.env, req.authname)
  File "/usr/lib/python2.3/site-packages/trac/perm.py", line 263, in __init__
    self.perms = PermissionSystem(env).get_user_permissions(username)
  File "/usr/lib/python2.3/site-packages/trac/perm.py", line 227, in get_user_permissions
    for perm in self.store.get_user_permissions(username):
  File "/usr/lib/python2.3/site-packages/trac/perm.py", line 109, in get_user_permissions
    db = self.env.get_db_cnx()
  File "/usr/lib/python2.3/site-packages/trac/env.py", line 203, in get_db_cnx
    return DatabaseManager(self).get_connection()
  File "/usr/lib/python2.3/site-packages/trac/db/api.py", line 75, in get_connection
    return self._cnx_pool.get_cnx(self.timeout or None)
  File "/usr/lib/python2.3/site-packages/trac/db/pool.py", line 96, in get_cnx
    if try_rollback(cnx):
  File "/usr/lib/python2.3/site-packages/trac/db/pool.py", line 61, in try_rollback
    cnx.close()
ProgrammingError: closing a closed connection

If I restart Apache, then everything returns to normal, but the problem crops up over and over after the site has been running for a while.

[asouzis@…]

I'm seeing this too quite often running trac .11dev (rev 4459 from 12-15-06). I started seeing this when I recently upgraded from Mysql 4 to 5.0.27. I see this both when using with MySQLdb 1.2.0final and 1.2.1PR. I just installed 1.2.2b3 (and now my pages no longer display non-latin1 characters unfortunatly) -- we'll see if it still happens (that release add some sort of reconnection logic but not by default for later versions of mysql 5.0.x).

[asouzis@…]

ok, about 12 or so hours after upgrading to MySQLdb 1.2.2b3 I'm seeing these errors again. I just added an exception handler around "cnx.close()" in trac.db.pool.try_rollback -- we'll see if that helps.

[anonymous]

Replying to asouzis@users.sourceforge.net:

ok, about 12 or so hours after upgrading to MySQLdb 1.2.2b3 I'm seeing these errors again. I just added an exception handler around "cnx.close()" in trac.db.pool.try_rollback -- we'll see if that helps.

Could you post the code you're trying out -- I'd like to give it a whirl myself. I also tried setting a cron job to restart Apache, in order to solve this problem, but it still happens.

[asouzis@…]

Replying to anonymous:

Replying to asouzis@users.sourceforge.net:

ok, about 12 or so hours after upgrading to MySQLdb 1.2.2b3 I'm seeing these errors again. I just added an exception handler around "cnx.close()" in trac.db.pool.try_rollback -- we'll see if that helps.

Could you post the code you're trying out -- I'd like to give it a whirl myself. I also tried setting a cron job to restart Apache, in order to solve this problem, but it still happens.

Here's my "fix". I haven't seen the error since and it doesn't seem to cause any other problems. (also I downgraded back to MySQLdb 1.2.1pr)

def try_rollback(cnx):

try:

cnx.rollback() # resets the connection
return True

except Exception:

+ try:

cnx.close()

+ except Exception:
+ return False

return False

I imagine a cleaner fix would check to if the connection was open before calling close...
(btw, i don't seem to be getting cc notification emails)

[anonymous]

Replying to asouzis@users.sourceforge.net:

oops, forgot about the wiki syntax. here's change reformatted:

 def try_rollback(cnx):
     try:
         cnx.rollback() # resets the connection
         return True
     except Exception:
 +       try:
            cnx.close()
 +       except Exception:
 +          return False
         return False  

[cboos]

Do the ping before the rollback (as originally proposed in #3645) and don't attempt to close if we know the connection is already lost.

[cboos]

Replying to asouzis@users.sourceforge.net:

Can you please try instead this patch: attachment:mysql_safe_close-r4937.diff​ (with your change reverted)?

I'd like to know if this is enough to prevent the exception to happen. If not, I'll add the try/except, but then only in the mysql specific backend.

[cboos]

Patch applied in r4962 (trunk) and r4963 (0.10-stable).

I would have preferred some confirmation beforehand, but I believe the change improves over the past situation anyway (and should be enough to fix the issue).