Extending pymssql dialect is_disconnect

[Mat Moore]

Hi,

I am using SQLAlchemy 1.0.9 with pymssql, and recently I encountered an occasional issue where I run into a database write error.

The last time this happened, the error was

  File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.py", line 1034, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
  File "pymssql.pyx", line 465, in pymssql.Cursor.execute (pymssql.c:7527)
OperationalError: (pymssql.OperationalError) (20006, 'DB-Lib error message 20006, severity 9:\nWrite to the server failed\n'

And all future queries from the same engine gave an error like:

 File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/result.py", line 1038, in first
    return self._non_result(None)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/result.py", line 920, in _non_result
    "This result object does not return rows. "
ResourceClosedError: This result object does not return rows. It has been closed automatically.

When this occurred I was able to verify in SQL server's activity monitor that the connection wasn't there any more, but it seems like SQLAlchemy still treated it as open.

I am creating my engine through flask_sqlalchemy which doesn't seem to pass any special options for SQL server by default. This may be fixable by setting a pool recycle (as documented here http://docs.sqlalchemy.org/en/latest/core/pooling.html#setting-pool-recycle) but it led me to wonder about how the disconnect decision is reached for this driver, since in my case it didn't close the connection.

The pymssql dialect currently considers these messages as errors that should result in a disconnect:

            "Adaptive Server connection timed out",
            "Net-Lib error during Connection reset by peer",
            "message 20003",  # connection timeout
            "Error 10054",
            "Not connected to any MS SQL server",
            "Connection is closed"

Like 20003, my 20006 error code comes from db-lib, inside freetds, and there is a lot of other error codes defined here: https://github.com/brianb/FreeTDS/blob/master/include/sybdb.h

My question is: is it appropriate to extend this list so that SQLAlchemy can recover from this kind of situation, and if so exactly what scenarios should be treated as a disconnect?

[Mike Bayer]

that is very reasonable because if a DBAPI connection goes into a "permanently broken" state, it needs to be invalidated and recycled. However in this case it's a little less than ideal that only that one connection is actually impacted; the "is disconnect" logic currently assumes that all DB connections must be recycled on next checkout, e.g. because the database was restarted or similar. So this would cause your connection pool to recycle everything it has on next checkout (typically no more than 5 connections).

[Mike Bayer]

at the moment if you were to say directly "connection.invalidate()" upon encountering this error, that would cause Connection to re-connect to the pool with a new connection to resolve the issue.

[Mat Moore]

Hi Mike,

Thanks for clarifying that, I hadn't considered whether it should kill the single connection or the entire pool, but it sounds like responding to a failed write in is_disconnect is not always the right thing to do.

Is there any other change you would suggest making within sqlalchemy itself to do this?

It sounds like I can always handle this at the application level by hooking into the dbapi_error event and calling invalidate there, but I am happy to work on a pull request if there is a better solution.

[Mike Bayer]

I think we should just add it to the list of disconnect situations. It's a disconnect, the feature of SQLA differentating between system-wide and connection-local disconnects is just not there for now.

[Mike Bayer]

• Added the error "20006: Write to the server failed" to the list of disconnect errors for the pymssql driver, as this has been observed to render a connection unusable. fixes #3585

→ <<cset ab6946769742>>

[Mike Bayer]

• Added the error "20006: Write to the server failed" to the list of disconnect errors for the pymssql driver, as this has been observed to render a connection unusable. fixes #3585

(cherry picked from commit ab6946769742602e40fb9ed9dde5f642885d1906)

→ <<cset b30390bfb3a6>>