[Sqlalchemy-tickets] Issue #3585: Extending pymssql dialect is_disconnect (zzzeek/sqlalchemy)

[Mat M. <iss...@bi...>]

New issue 3585: Extending pymssql dialect is_disconnect
https://bitbucket.org/zzzeek/sqlalchemy/issues/3585/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

```
#!python

  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:

```
#!python

 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:

```
#!python
            "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?