Extending pymssql dialect is_disconnect #3585
Description
Migrated issue, originally created by Mat Moore (@matmoore)
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?