Extending pymssql dialect is_disconnect
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?
Comments (8)
-
repo owner -
repo owner at the moment if you were to say directly "connection.invalidate()" upon encountering this error, that would cause Connection to re-connect to the pool with a new connection to resolve the issue.
-
reporter Hi Mike,
Thanks for clarifying that, I hadn't considered whether it should kill the single connection or the entire pool, but it sounds like responding to a failed write in is_disconnect is not always the right thing to do.
Is there any other change you would suggest making within sqlalchemy itself to do this?
It sounds like I can always handle this at the application level by hooking into the dbapi_error event and calling invalidate there, but I am happy to work on a pull request if there is a better solution.
-
repo owner I think we should just add it to the list of disconnect situations. It's a disconnect, the feature of SQLA differentating between system-wide and connection-local disconnects is just not there for now.
-
repo owner - changed milestone to 1.0.xx
-
repo owner - changed status to resolved
- Added the error "20006: Write to the server failed" to the list
of disconnect errors for the pymssql driver, as this has been observed
to render a connection unusable.
fixes
#3585
→ <<cset ab6946769742>>
-
repo owner - Added the error "20006: Write to the server failed" to the list
of disconnect errors for the pymssql driver, as this has been observed
to render a connection unusable.
fixes
#3585
(cherry picked from commit ab6946769742602e40fb9ed9dde5f642885d1906)
→ <<cset b30390bfb3a6>>
- Added the error "20006: Write to the server failed" to the list
of disconnect errors for the pymssql driver, as this has been observed
to render a connection unusable.
fixes
-
repo owner - changed component to mssql
- Log in to comment
that is very reasonable because if a DBAPI connection goes into a "permanently broken" state, it needs to be invalidated and recycled. However in this case it's a little less than ideal that only that one connection is actually impacted; the "is disconnect" logic currently assumes that all DB connections must be recycled on next checkout, e.g. because the database was restarted or similar. So this would cause your connection pool to recycle everything it has on next checkout (typically no more than 5 connections).