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

[Ken R. <iss...@bi...>]

New issue 3791: Extending pymssql dialect is_disconnect again
https://bitbucket.org/zzzeek/sqlalchemy/issues/3791/extending-pymssql-dialect-is_disconnect

Ken Robbins:

Similar to #3585 except a different initial error message.

Using SQLAlchemy 1.0.12, pymssql 2.1.2, and SQL Server 2012. Here, we performed a DB failover, and the connection became unusable.

First error:

```python
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 149, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 934, in execute
    clause, params or {})
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 195, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
  File "pymssql.pyx", line 467, in pymssql.Cursor.execute (pymssql.c:7569)
    raise OperationalError, e.args[0]
OperationalError: (OperationalError) (20017, 'DB-Lib error message 20017, severity 9:\nUnexpected EOF from the server\nNet-Lib error during Resource temporarily unavailable (11)\n')
```

All subsequent queries resulted in this error:

```python
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 790, in fetchone
    self.cursor, self.context)
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1027, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 781, in fetchone
    row = self._fetchone_impl()
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 700, in _fetchone_impl
    self._non_result()
  File "/opt/webapp/svc/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 720, in _non_result
    "This result object does not return rows. "
ResourceClosedError: This result object does not return rows. It has been closed automatically.
```

It appears the DB server is hanging up on the client and the client is not attempting to reconnect, so it stays in this bad state. I propose "message 20017" be added to the list of errors considered to be a disconnect.

```
diff --git a/lib/sqlalchemy/dialects/mssql/pymssql.py b/lib/sqlalchemy/dialects/mssql/pymssql.py
index 32e3bd9..380418b 100644
--- a/lib/sqlalchemy/dialects/mssql/pymssql.py
+++ b/lib/sqlalchemy/dialects/mssql/pymssql.py
@@ -87,6 +87,7 @@ class MSDialect_pymssql(MSDialect):
             "Not connected to any MS SQL server",
             "Connection is closed",
             "message 20006",  # Write to the server failed
+            "message 20017",  # Unexpected EOF from the server
         ):
             if msg in str(e):
                 return True
```