[Twisted-Python] Exception handling in t.e.adbapi
I've been using adbapi (with pyodbc talking to MS SQL Server) successfully in a Windows service. I've discovered that occasionally, the network between the service and the database machine goes down and comes back up (either that or the database server itself cycles down and back up). When this happens, any pyodbc connections hanging around are corrupted, so that the next query gets an exception (class pyodbc.Error). Currently, the best thing I can do is to restart the service. I'm trying to rewrite the service to be able to catch, analyze, and respond to exceptions from pyodbc. In particular, in the case above, it's possible to reconnect and retry the query. If the network connection is still down, I'd like to log it, send a message to alert someone, or whatever. I've rewritten a ConnectionPool.runQuery call to use runInteraction to call a function (in a thread) that works with the cursor created in runInteraction to execute the query. The function wraps the cursor.execute(...) in a try-except. I can successfully catch the exception, and I try to recover by doing cursor.reconnect(), then cursor.reopen(), and finally cursor.execute(...) again. I'm finding that this fails with a "wrong connection for thread" exception. I haven't been able to figure out why that should happen; more importantly, I'm not sure that I'm going about this in the right way. Any insights or pointers to code that does this kind of thing would be appreciated. Thanks, -- Don Dwiggins Advanced Publishing Technology
On Wed, 19 Nov 2008 16:38:54 -0800, Don Dwiggins <ddwiggins@advpubtech.com> wrote:
How do you tell the difference between a network error which prevented a statement from being executed by the SQL server and a network error which only prevented the response indicating that the statement was successfully executed from being returned to you? If you can't tell the difference, how do you ensure that you don't re-execute statements which modify the database causing corruption of your data?
I've never used pyodbc, but presumably the exception indicates you're using the objects in a thread where they're not allowed to be used. You should find out what the threading restrictions of the module are and then see where you're violating them. One thing to keep in mind is that ConnectionPool uses a ThreadPool. That means you're never guaranteed that two different functions will run in the same thread. Whichever thread in the pool is free will run the next task. Jean-Paul
Jean-Paul, thanks for the reply:
Well, I was a bit cryptic in my description. Based on experience with SQL Server and experimentation with different conditions, I'm pretty sure I can tell from the exception data whether the statement completed successfully. You're right, though, I do need to be careful about this.
I've never used pyodbc, but presumably the exception indicates you're using the objects in a thread where they're not allowed to be used.
By tracing through the operation of adbapi, I figured out why I was getting that message when the network is down: Connection.reconnect does a ConnectionPool.disconnect followed by a ConnectionPool.connect, which does a dbapi.connect; this fails, since the network is still down. The exception is caught in _runInteraction, which tries to do a conn.rollback, which calls ConnectionPool.disconnect. Since there's no connection at this point, I get the "wrong connection" exception. The moral of the story seems to be that I need to rethink how to detect the difference between a "stale" connection with the network back up, and a network down condition. Another possibility would be to adapt adbapi to work in one-connection-per-operation mode, so there'd never be an open connection hanging around. (This sort of defeats the point of ConnectionPool, but the API of the module would be preserved.) -- Don Dwiggins Advanced Publishing Technology
participants (2)
-
Don Dwiggins
-
Jean-Paul Calderone