0

Since I moved my database from a mssql docker container to an azure database as a service my web application( flask) is being disconnected after several minutes.

It is simply solved by a refresh of the page but still, there is an issue here.

The error raised by SQLAlchemy is an Operational Error (https://docs.sqlalchemy.org/en/14/errors.html#error-e3q8)

I tried to implement the pool_pre_ping=True option in the create_engine function the error is not solved.

I think the disconnect is initiated by the database but I do not know if there is an option to change that. (database is SQL Server 12.0)

Gabin Legrand
  • 65
  • 8

2 Answers2

1

I had same issue before. I solved it by pool_recycle option of create_engine function.

The database has default connection maintain time itself. When the connection is not used during certain period (DB setting), then DB close that connection.

So, You need set pool_recycle time lower then db connection maintain time.

Example,

create_engine("...", pool_recycle=3600, pool_pre_ping=True)

Check document for more infomation

SEUNGFWANI
  • 140
  • 10
  • Unfortunately, this is not working working for me even with a `pool_recycle` time lower than one minute. – Gabin Legrand Jan 06 '22 at 12:08
  • Uhm… did you check ‘wait-time’ of your DataBase setting? Not engine connection of python – SEUNGFWANI Jan 06 '22 at 14:40
  • 1
    Hope this will help you. https://stackoverflow.com/questions/60885946/timeouts-in-azure-sql-database – SEUNGFWANI Jan 06 '22 at 14:54
  • The `pool_recycle` of the create engine is not supposed to create a new connection with the database to avoid the timeout if there is no queries for more than 30min? – Gabin Legrand Jan 07 '22 at 08:43
  • Maybe I don't understand your question, well. but I think there are two closing way of connection. One is `create_engine of sqlalchemy in python`. Another is DBMS connection pool(?). So you should consider each settings. Example, db setting is 30*60(s), then you should set `pool_recycle` option **less than** 30*60(s). – SEUNGFWANI Jan 10 '22 at 02:23
  • the code I am using is : `engine = create_engine(app.config["SQLALCHEMY_DATABASE_URI"], pool_recycle=60, pool_pre_ping=True)` which is far less than 30min. – Gabin Legrand Jan 11 '22 at 12:22
0

As I am using Flask-SQLAlchemy there are configuration parameters to recycle the pool.

I solved my problem by removing the create_engine function.

I simply add SQLALCHEMY_POOL_RECYCLE = 300 to my configuration file.

Gabin Legrand
  • 65
  • 8