On Thu, Sep 28, 2017 at 3:58 AM, David Moore <[email protected]> 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 <+27%2021%20762%201440> > > [email protected] <[email protected]> > > www.j5int.com > > [image: j5InternationalLogo_small.png] > > ------------------------------ > > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
