1

I have a Flask App running on an Ubuntu WebApp on Azure. Every morning my queries to the app fail with the below error:

sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)')

I am using SQLAlchemy ORM to query my Azure SQL Server Instance. I believe that my connections are becoming stale for the following reasons.

  1. It happens every morning after no one uses the app
  2. After X many failed returns, it starts working, until the next morning.

However to make things more weird, when I check sys.dm_exec_sessions on the sql server, it does not show any active connections (outside of the one I'm executing to check).

In addition, when I run the dockerized app on my local and connect to the DB I get no such error.

If anyone has had a similar issue I'd love some insights, or at least a recommendation on where to drill down.

https://azure.github.io/AppService/2018/03/01/Deep-Dive-into-TCP-Connections-in-App-Service-Diagnostics.html

This link helped me, but the solution is only for Windows Apps, not Linux.

Robert Riley
  • 389
  • 1
  • 7
  • 31
  • 1
    This may help https://docs.sqlalchemy.org/en/14/core/pooling.html#pool-disconnects – snakecharmerb Jan 19 '22 at 18:45
  • 1
    You can check the similar issues [pyodbc: How to prevent Communication link failure, ConnectionWrite, and ConnectionRead errors?](https://stackoverflow.com/a/68329142/15969115), [SQLSTATE 08S01: Microsoft ODBC Driver 17 for SQL Server TCP Provider](https://github.com/Microsoft/msphpsql/issues/835) and https://github.com/mkleehammer/pyodbc/issues/427 – Ecstasy Jan 20 '22 at 04:33

1 Answers1

1

With help from @snakecharmerb:

The application was in-fact holding on to a pool of dead connections, setting pool_recycle to a greater time solved the issue.

engine = create_engine(
        key, echo=False, future=True,
        echo_pool=True,
        #  connection pool will log informational output such as when connections are invalidated.
        pool_recycle=3600
        #  causes the pool to recycle connections after the given number of seconds has passed.
    )
Robert Riley
  • 389
  • 1
  • 7
  • 31