[SQLObject] Handling lost MySQL connections...

[Chris G. <ch...@il...>]

I was looking into fixing the problem with MySQL connections in the pool 
timing-out if you don't use SQLObject for a while (for example, with a 
long-running process that doesn't get constant activity). Here's an 
example of the problem:

  File "SQLObject\SQLObject.py", line 1228, in count
    count = conn.countSelect(self)
  File "SQLObject\DBConnection.py", line 177, in countSelect
    val = int(self.queryOne(q)[0])
  File "SQLObject\DBConnection.py", line 156, in queryOne
    return self._runWithConnection(self._queryOne, s)
  File "SQLObject\DBConnection.py", line 74, in _runWithConnection
    val = meth(conn, *args)
  File "SQLObject\DBConnection.py", line 149, in _queryOne
    c.execute(s)
  File "MySQLdb\cursors.py", line 95, in execute
    return self._execute(query, args)
  File "MySQLdb\cursors.py", line 114, in _execute
    self.errorhandler(self, exc, value)
  File "MySQLdb\connections.py", line 33, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (2013, 'Lost connection to MySQL server during query')


The obvious spot to try to catch the exception and try to reconnect is 
line 149 of DBConnection, where it does c.execute(s). Reconnecting isn't 
going to matter though, since you can't return the changed connection to 
the caller.

I was thinking that I could handle the exception by subclassing the 
connection object that MySQLdb.connect() returns, which would have an 
overloaded connection.execute(). It would have a try/except block around 
the superclass' .execute() statement, and be able to reconnect if it got 
OperationalError 2013.

Do you think adding another level to the class hierarchy is going to add 
too much overhead for the queries? It's probably not a very common bug, 
and maybe it should even be something that MySQLdb itself handles.