pyodbc and is_disconnect

[David Moore]

Hi,

I've recently had an issue with pyodbc not correctly identifying a disconnect exception when connected to a replicating SQL Server cluster with failover. As far as I've been able to ascertain, what happened is that the database failed over, leaving all connections in a weird state. Since sqlalchemy didn't correctly identify the errors as disconnect exceptions, it kept trying to use the connections which never became usable again. We recycle connections after an hour, but that left us with an hour of no database functionality. Production SQLAlchemy version is 1.0.6, but there is no relevant change I can see on latest master.

So, I went digging into how sqlalchemy classifies these errors. It seems the only disconnect condition that pyodbc takes special note of is [08S01] (in lib/sqlalchemy/connectors/pyodbc.py). Back before we used sqlalchemy, we collected a more comprehensive set of errors which imply a disconnection event, and I'd love to see these in sqlalchemy. These are '01002', '08003', '08007', '08S01','08S02', '08001', 'HYT00' and 'HY010'.

So, two questions (assuming that these additions will be welcome):

  * I notice 08S01 is handled in lib/sqlalchemy/connectors/pyodbc.py, whereas pymssql looks for disconnect errors in lib/sqlalchemy/dialects/pymssql.py. Where is the correct place to patch this? The former, or lib/sqlalchemy/dialects/pyodbc.py?

  * Is there a place I can hook or extend or override our current setup to get this detection into production without a full sqlalchemy upgrade testing cycle?

Kind regards,

David Moore Support Technical Lead j5 Software South Africa Skype: Phone: Email: Web: davidm.j5int +27 21 762 1440 dav...@j5int.com www.j5int.com

This message is confidential. It may also be privileged or otherwise protected by work product immunity or other legal rules. If you have received it by mistake, please let us know by e-mail reply and delete it from your system; you may not copy this message or disclose its contents to anyone. Please send us by fax any message containing deadlines as incoming e-mails are not screened for response deadlines. The integrity and security of this message cannot be guaranteed on the Internet.You should carry out your own virus checks before opening any attachments. Opinions, conclusions and other information that do not relate to the official business of the company are neither given nor endorsed by it.

[Mike Bayer]

On Thu, Sep 28, 2017 at 3:58 AM, David Moore <dav...@j5int.com> wrote:

Hi,

I've recently had an issue with pyodbc not correctly identifying a disconnect exception when connected to a replicating SQL Server cluster with failover. As far as I've been able to ascertain, what happened is that the database failed over, leaving all connections in a weird state. Since sqlalchemy didn't correctly identify the errors as disconnect exceptions, it kept trying to use the connections which never became usable again. We recycle connections after an hour, but that left us with an hour of no database functionality. Production SQLAlchemy version is 1.0.6, but there is no relevant change I can see on latest master.

So, I went digging into how sqlalchemy classifies these errors. It seems the only disconnect condition that pyodbc takes special note of is [08S01] (in lib/sqlalchemy/connectors/pyodbc.py). Back before we used sqlalchemy, we collected a more comprehensive set of errors which imply a disconnection event, and I'd love to see these in sqlalchemy. These are '01002', '08003', '08007', '08S01','08S02', '08001', 'HYT00' and 'HY010'.

So, two questions (assuming that these additions will be welcome):

  * I notice 08S01 is handled in lib/sqlalchemy/connectors/pyodbc.py, whereas pymssql looks for disconnect errors in lib/sqlalchemy/dialects/pymssql.py. Where is the correct place to patch this? The former, or lib/sqlalchemy/dialects/pyodbc.py?

I think the presence of 08S01 in connectors/pyodbc.py is a bug -this error code is specific to SQL Server, so should be in dialects/mssql/pyodbc.   For that and adding the error codes https://bitbucket.org/zzzeek/sqlalchemy/issues/4095/sql-server-close-connection-codes-for is added should be pushed today.

 

  * Is there a place I can hook or extend or override our current setup to get this detection into production without a full sqlalchemy upgrade testing cycle?

big time, there's an exception catch hook with deep functionality:

http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=handle_error#sqlalchemy.events.ConnectionEvents.handle_error

per the example we can illustrate your codes:

@event.listens_for(Engine, "handle_error")
def handle_exception(context):
    if isinstance(context.original_exception,
        pyodbc.Error):

        for code in ('08S01', '01002', '08003',
    '08007', '08S02', '08001', 'HYT00', 'HY010'):

            if code in str(context.original_exception):

                context.is_disconnect = True

 

Kind regards,

David Moore Support Technical Lead j5 Software South Africa Skype: Phone: Email: Web: davidm.j5int +27 21 762 1440 dav...@j5int.com www.j5int.com

This message is confidential. It may also be privileged or otherwise protected by work product immunity or other legal rules. If you have received it by mistake, please let us know by e-mail reply and delete it from your system; you may not copy this message or disclose its contents to anyone. Please send us by fax any message containing deadlines as incoming e-mails are not screened for response deadlines. The integrity and security of this message cannot be guaranteed on the Internet.You should carry out your own virus checks before opening any attachments. Opinions, conclusions and other information that do not relate to the official business of the company are neither given nor endorsed by it.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[David Moore]

Fantastic, thanks so much for the help, Mike.

Kind regards,

David Moore Support Technical Lead j5 Software South Africa Skype: Phone: Email: Web: davidm.j5int +27 21 762 1440 dav...@j5int.com www.j5int.com

This message is confidential. It may also be privileged or otherwise protected by work product immunity or other legal rules. If you have received it by mistake, please let us know by e-mail reply and delete it from your system; you may not copy this message or disclose its contents to anyone. Please send us by fax any message containing deadlines as incoming e-mails are not screened for response deadlines. The integrity and security of this message cannot be guaranteed on the Internet.You should carry out your own virus checks before opening any attachments. Opinions, conclusions and other information that do not relate to the official business of the company are neither given nor endorsed by it.