Extending pymssql dialect is_disconnect again (add error 20017)

[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:

  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:

  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

[Mike Bayer]

this is fine and as always I can work with a short pull request to add this in. thanks!

[Ken Robbins]

@zzzeek Thanks. I have submitted this PR https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/89/fixes-3791-add-message-20017-unexpected/diff

[Mike Bayer]

Add "message 20017" (unexpected EOF from the server) to mssql.

Fixes: #3791 Change-Id: I0dade4fe0ecbb53b4a66881594f362986ba73ae8 Pull-request: https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/89

→ <<cset 881369b949cf>>

[Mike Bayer]

Add "message 20017" (unexpected EOF from the server) to mssql.

Fixes: #3791 Change-Id: I0dade4fe0ecbb53b4a66881594f362986ba73ae8 Pull-request: https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/89 (cherry picked from commit 285d7bbb3e11abdc9a931e35e4ee08ef611af5d4)

→ <<cset 55bd3b0535d7>>

[Ken Robbins]

Thanks @zzzeek. Do you have a rough timeframe for when I might expect this released? I'll check back then. Thanks again.

[ericd]

Does anyone know if this affected usage with pyodbc + FreeTDS?

I'm seeing the is_disconnect function for pyodbc -

def is_disconnect(self, e, connection, cursor):
    if isinstance(e, self.dbapi.ProgrammingError):
        return "The cursor's connection has been closed." in str(e) or \
            'Attempt to use a closed connection.' in str(e)
    elif isinstance(e, self.dbapi.Error):
        return '[08S01]' in str(e)
    else:
        return False

This looks a lot less thorough than the pymssql one. Is there a reason - e.g. does pyodbc handle this for us in some way?

[Ken Robbins]

I don't know why, but my guess is the pymssql one looks more thorough because pymssql has more users or at least more users who are testing disconnections and failovers specifically. Or maybe pyodbc just works better. I've never used it.

[ericd]

Thanks for the thoughts Ken. I kind of figured it was something like that.

[Mike Bayer]

it's that the messages in pymssql.py are hardcoded to what we get from the low level DBAPI. The ODBC driver has totally different error codes. If you see one of those codes coming from your ODBC driver, just report it here and we'll add the correct regular expression.