Re: [SQLObject] Handling lost MySQL connections...

[Ian B. <ia...@co...>]

Chris Gahan wrote:
> 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.

Another way to do this would be to ping the connection everytime you do 
DBAPI.getConnection.  I believe there's a ping method of some sort for 
MySQL, and perhaps equivalents for other connections (though it's not 
part of the DBAPI).

This wouldn't save you if the connection was lost part way through a 
transaction, or somewhere else where you hang onto a connection for a 
long time.  And it may be less efficient than catching the exception, 
which isn't much overhead unless you actually get an error, and lost 
connection errors shouldn't be too common (relative to queries).  So I 
don't think the overhead of a subclass would be too bad.

   Ian