Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Ticket #781 (closed defect: duplicate)

Opened 13 years ago

Last modified 12 years ago

SQLObject does not reconnect

Reported by: ghaering Owned by: anonymous
Priority: normal Milestone:
Component: SQLObject Version: 0.9a4
Severity: critical Keywords: sqlobject 'MySQL server has gone away'
Cc:

Description

A robust web application needs to recover from a database restart or a temporary database downtime. TurboGears does not, because SQLObject is not robust enough. SQLObject needs to detect if a database connection becomes invalid (either by looking at exceptions - driver specific, or by using driver-specific facilities like MySQL's ping, or by always executing dummy queries like "select 1" before doing the actual query to detect the connection status).

If the connection goes bad, SQLObject needs to transparently reopen it.

Change History

comment:1 Changed 13 years ago by Baruch <turbogears@…>

In my case the exception happens in the identity module, after that the cherrypy.request object is lost and the whole TurboGears app is hosed.

An exception in the identity part:

Page handler: 'MySQL server has gone away'
Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/CherryPy-2.2.0-py2.4.egg/cherrypy/_cphttptools.py", line 104, in _run
    applyFilters('before_main')
  File "/usr/lib/python2.4/site-packages/CherryPy-2.2.0-py2.4.egg/cherrypy/filters/__init__.py", line 151, in applyFilters
    method()
  File "/usr/lib/python2.4/site-packages/TurboGears-0.9a3-py2.4.egg/turbogears/visit.py", line 149, in before_main
    visit= _manager.visit_for_key( visit_key )
  File "/usr/lib/python2.4/site-packages/TurboGears-0.9a3-py2.4.egg/turbogears/visit.py", line 304, in visit_for_key
    visit= TG_Visit.lookup_visit( visit_key )
  File "/usr/lib/python2.4/site-packages/TurboGears-0.9a3-py2.4.egg/turbogears/visit.py", line 340, in lookup_visit
    return cls.by_visit_key( visit_key )
  File "<string>", line 1, in <lambda>
  File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/main.py", line 1266, in _SO_fetchAlternateID
    result, obj = cls._findAlternateID(name, dbName, value, connection)
  File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/main.py", line 1262, in _findAlternateID
    value), None
  File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/dbconnection.py", line 587, in _SO_selectOneAlt
    return self.queryOne("SELECT %s FROM %s WHERE %s = %s" %
  File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/dbconnection.py", line 755, in queryOne
    return self._dbConnection._queryOne(self._connection, s)
  File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/dbconnection.py", line 341, in _queryOne
    self._executeRetry(conn, c, s)
  File "/usr/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/mysql/mysqlconnection.py", line 60, in _executeRetry
    return cursor.execute(query)
  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
OperationalError: 2006

And the exception later in the request object:

Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/CherryPy-2.2.0-py2.4.egg/cherrypy/_cpwsgi.py", line 75, in wsgiApp
    environ['wsgi.input'])
  File "/usr/lib/python2.4/site-packages/CherryPy-2.2.0-py2.4.egg/cherrypy/_cphttptools.py", line 79, in run
    _cputil.get_special_attribute("_cp_log_access", "_cpLogAccess")()
  File "/home/myproj/apps/myproj/webapp/myproj/controllers.py", line 476, in _cp_log_access
    s = tmpl % {'h': cherrypy.request.remoteHost,
  File "/usr/lib/python2.4/site-packages/CherryPy-2.2.0-py2.4.egg/cherrypy/__init__.py", line 43, in __getattr__
    return getattr(childobject, name)
AttributeError: 'Request' object has no attribute 'identity'

comment:2 Changed 13 years ago by godoy

Test 1 (done with 1.0 branch):

  1. Start my application that uses identity in a PostgreSQL 8.1.x database
  2. Log in
  3. Open some page with dynamic information obtained from the database
  4. Stop the database
  5. Restart the database
  6. Reload the database

Test 2 (same branch):

  • After "4.", above, reload the page before restarting the database
  • Continue with 5 and 6.

Results:

  • Test 1: Failed, as described.
  • Test 2: Failed.

I believe that catching OperationalError? and reconnecting should solve the problem. Is it the same exception you get there?

comment:3 Changed 13 years ago by eastham@…

#872 talks about this problem specific to mysql. The fix for that issue does all the way down into mysqldb -- seems like this is a generic problem with SQLObject that needs per-backend changes. There is a fix available for this issue for mysql, see the other ticket.

Not sure if we should consolidate these tickets or not.

comment:4 Changed 13 years ago by nEO

  • Keywords sqlobject 'MySQL server has gone away' added

It's a realy annoying problem.

from mysql 5.0.3 the reconnect flag default is 0.

so, after mysql wait_timeout, the connection is lost.

 http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

If your app using tg.visit,tg.identity, and your app idle time > mysql connection wait_timeout (set in my.cnf wait_timeout), Then you will lost all mysqldb connections. and all identy required pages will return 500 server error.

It's fairly easy repeat this problem, just set mysql wait_timeout to 10 and quickstart a new project with identity. Then you will catch it.

The call trace looks like:

Exception in thread VisitManager:
Traceback (most recent call last):
  File "/usr/lib64/python2.4/threading.py", line 442, in __bootstrap
    self.run()
  File "/usr/lib64/python2.4/site-packages/TurboGears-0.9a8-py2.4.egg/turbogears/visit/api.py", line 256, in run
    self.update_queued_visits(queue)
  File "/usr/lib64/python2.4/site-packages/TurboGears-0.9a8-py2.4.egg/turbogears/visit/sovisit.py", line 73, in update_queued_visits
    conn.query( conn.sqlrepr(u) )
  File "/usr/lib64/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/dbconnection.py", line 305, in query
    return self._runWithConnection(self._query, s)
  File "/usr/lib64/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/dbconnection.py", line 219, in _runWithConnection
    val = meth(conn, *args)
  File "/usr/lib64/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/dbconnection.py", line 302, in _query
    self._executeRetry(conn, conn.cursor(), s)
  File "/usr/lib64/python2.4/site-packages/SQLObject-0.7.1dev_r1675-py2.4.egg/sqlobject/mysql/mysqlconnection.py", line 60, in _executeRetry
    return cursor.execute(query)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 137, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (2006, 'MySQL server has gone away')

So, I do a quick dirty hack on SQLObject to handle the mysql connection timeout and mysql database restart.

I think the best solution is SQLObject do a connection check before do raw sql query, and handle OperationalError? exception, if MySQL server is gone away then try to reconnect.

here is the sqlobject patch for who is borning with the same problem.


gentoo sqlobject # diff -Nura dbconnection.py.orig dbconnection.py
--- dbconnection.py.orig        2006-07-26 23:21:23.000000000 +0800
+++ dbconnection.py     2006-07-27 00:12:44.000000000 +0800
@@ -214,11 +214,27 @@
         self._binaryType = type(self.module.Binary(''))

     def _runWithConnection(self, meth, *args):
+        try:
+            import _mysql_exceptions
+        except ImportError:
+            MySQLdbExcept = ImportError
+        else:
+            MySQLdbExcept = _mysql_exceptions.OperationalError
+
         conn = self.getConnection()
         try:
             val = meth(conn, *args)
-        finally:
+        except MySQLdbExcept:
+            #print "make reconnection"
             self.releaseConnection(conn)
+            conn = self.makeConnection()
+            self._connectionNumbers[id(conn)] = self._connectionCount
+            self._connectionCount += 1
+            try:
+                val = meth(conn, *args)
+            finally:
+                self.releaseConnection(conn)
+        #finally:
         return val

     def getConnection(self):

comment:5 Changed 13 years ago by jorge.vargas

I have move this to the SO bug tracker ticket 1547004, http://tinyurl.com/nqqyv if you have new information on this please post it there.

I'm not closing this bug until that one is closed.

comment:6 Changed 13 years ago by jorge.vargas

  • Status changed from new to closed
  • Resolution set to duplicate

see #872

Note: See TracTickets for help on using tickets.