mssql+pymssql does not recover from connection failure due to timeout or TCP closed

[Former user]

On Ubuntu Linux 10.04 using FreeTDS to connect to mssql using pymssql, we observed that if a query failed due to a timeout or TCP socket being closed, the entire SQLAlchemy connection pool would be horked; any subsequent queries would fail. If we use e.g. mssql+mxodbc, it was able to recover. This is for SQLAlchemy 0.6.5, but appears to be an issue in more recent versions including 0.7b4.

The issue appears to be that the is_disconnect() method for pymssql does not check for timeout and TCP socket disconnects, so the connection is not treated as disconnected.

The easiest way to reproduce is to edit /etc/freetds/freetds.conf and set timeout=1, then run an expensive query. We get exceptions like:

... Module sqlalchemy.engine.base:1394 in _cursor_execute

context) Module sqlalchemy.engine.default:299 in do_execute cursor.execute(statement, parameters) Module pymssql:196 in execute raise OperationalError, e0 OperationalError: (OperationalError) DB-Lib error message 20004, severity 9: Read from the server failed Net-Lib error during Connection reset by peer Error 104 - Connection reset by peer ...

A patch to fix is attached.

[Mike Bayer]

The pymssql dialect is being overhauled by the current maintainers of PyMSSQL. I've forwarded this ticket onto the sqlalchemy development list.

[Mike Bayer]

we should have committed this patch a long time ago, however both of these messages have ultimately been added by a production shop, see 3147ca30401aa and eefa4b68f5dce.

[Mike Bayer]

"net lib reset by peer" in 0.8 is 4211207abfd96

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)