- changed milestone to 1.0.xx
- changed title to Extending pymssql dialect is_disconnect again (add error 20017)
Extending pymssql dialect is_disconnect again (add error 20017)
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
Comments (9)
-
repo owner -
reporter @zzzeek Thanks. I have submitted this PR https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/89/fixes-3791-add-message-20017-unexpected/diff
-
repo owner - changed status to resolved
Add "message 20017" (unexpected EOF from the server) to mssql.
Fixes:
#3791Change-Id: I0dade4fe0ecbb53b4a66881594f362986ba73ae8 Pull-request: https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/89→ <<cset 881369b949cf>>
-
repo owner Add "message 20017" (unexpected EOF from the server) to mssql.
Fixes:
#3791Change-Id: I0dade4fe0ecbb53b4a66881594f362986ba73ae8 Pull-request: https://bitbucket.org/zzzeek/sqlalchemy/pull-requests/89 (cherry picked from commit 285d7bbb3e11abdc9a931e35e4ee08ef611af5d4)→ <<cset 55bd3b0535d7>>
-
reporter Thanks @zzzeek. Do you have a rough timeframe for when I might expect this released? I'll check back then. Thanks again.
-
Account Deactivated 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?
-
reporter 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.
-
Account Deactivated Thanks for the thoughts Ken. I kind of figured it was something like that.
-
repo owner 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.
- Log in to comment
this is fine and as always I can work with a short pull request to add this in. thanks!