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 <em...@j5int.com> 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. On Thu, Sep 28, 2017 at 3:46 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > > > 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 <+27%2021%20762%201440> >> >> dav...@j5int.com <em...@j5int.com> >> >> 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 sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
