[Sqlalchemy-tickets] Issue #4164: Disconnect Handling not working with MSSQL (zzzeek/sqlalchemy)

[Boris Z. <iss...@bi...>]

New issue 4164: Disconnect Handling not working with MSSQL
https://bitbucket.org/zzzeek/sqlalchemy/issues/4164/disconnect-handling-not-working-with-mssql

Boris Zyranov:

SqlAlchemy VERSION 1.2.1
Microsoft SQL Server 2014
Python 3.6.3

SqlAlchemy fails to detect disconnect as per http://docs.sqlalchemy.org/en/latest/core/pooling.html#dealing-with-disconnects


```
#!python

engine_connect_str = "mssql+pyodbc://{uid}:{pwd}@{srv}/{db}?driver=SQL Server".format(...)
en = create_engine(engine_connect_str)
print(list(en.execute("select * from t")))
```


[(11, 'eleven'), (12, 'twelve')]

... MSSQL server restart ...


```
#!python

try:
    print(list(en.execute("select * from t")))
except exc.DBAPIError as e:
    print(type(e))
    print("connection invalidated", e.connection_invalidated)
    print(e)

```

<class 'sqlalchemy.exc.DBAPIError'>

connection invalidated **False**

(pyodbc.Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Driver]
[DBNETLIB]ConnectionWrite (send()). (10054) (SQLExecDirectW)') (Background on this error at: http://sqlalche.me/e/dbapi)


Third call works without me having to manually refresh engine:


```
#!python

print(list(en.execute("select * from t")))
```

[(11, 'eleven'), (12, 'twelve')]

Pessimistic approach also does not work:


```
#!python

en = create_engine(engine_connect_str, pool_pre_ping=True)
print(list(en.execute("select * from t")))
```

[(11, 'eleven'), (12, 'twelve')]


```
#!python

try:
    print(list(en.execute("select * from t")))
except exc.DBAPIError as e:
    print(type(e))
    print("connection invalidated", e.connection_invalidated)
    print(e)
```

    
<class 'sqlalchemy.exc.DBAPIError'>

connection invalidated **False**

(pyodbc.Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()). (10054) (SQLExecDirectW)') (Background on this error at: http://sqlalche.me/e/dbapi)


```
#!python

print(list(en.execute("select * from t")))
```

[(11, 'eleven'), (12, 'twelve')]