Incorrect(?) use of db.session in flask_appbuilder.AppBuilder

[Atheuz]

Environment

Flask-Appbuilder version: 4.1.3

Describe the expected results

We have an Azure SQL database that we use with flask-appbuilder. This database requires that we request a new token every hour or so (expiry on the token is 3600 seconds). To do this, we use an event listener of the form: @event.listens_for(engine, "do_connect"), that requests a new token and sets this in the connection parameters for the engine when creating a new connection. The expected behaviour would be that once the token has expired, and it needs to create a new connection to the database, it runs the event from above and acquires a new token that can be used for connections.

Describe the actual results

We're facing an issue where after an hour (when the token expires) if you perform a request to the application you'll get an Internal Server Error with an error like this: sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x20 (32) (SQLExecDirectW)'). Subsequent requests after this will be fine, until the token expires again at which point it'll happen again. My suspicion is that there is a problem with the use of the db.session when initializing the appbuilder object: flask_appbuilder.AppBuilder(app, db.session, ...) because the db.session in my understanding is not meant to be long-lived, it's meant to be a short-lived object that you use for a transaction and then close afterwards: see here. I further don't know if engine events are triggered for sessions at all (and that this may be the cause of the token expiry -> connection failure issue that I'm seeing).

app|ERROR|Exception on / [GET]
Traceback (most recent call last):
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x20 (32) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/app/.venv/lib/python3.9/site-packages/flask/app.py", line 2073, in wsgi_app
    response = self.full_dispatch_request()
  File "/app/.venv/lib/python3.9/site-packages/flask/app.py", line 1519, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/app/.venv/lib/python3.9/site-packages/flask/app.py", line 1517, in full_dispatch_request
    rv = self.dispatch_request()
  File "/app/.venv/lib/python3.9/site-packages/flask/app.py", line 1503, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/app/app.py", line 72, in index
    return self.render_template(index, appbuilder=self.appbuilder)
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/baseviews.py", line 322, in render_template
    return render_template(
  File "/app/.venv/lib/python3.9/site-packages/flask/templating.py", line 154, in render_template
    return _render(
  File "/app/.venv/lib/python3.9/site-packages/flask/templating.py", line 128, in _render
    rv = template.render(context)
  File "/app/.venv/lib/python3.9/site-packages/jinja2/environment.py", line 1301, in render
    self.environment.handle_exception()
  File "/app/.venv/lib/python3.9/site-packages/jinja2/environment.py", line 936, in handle_exception
    raise rewrite_traceback_stack(source=source)
  File "/app/webapp/templates/index_not_auth.html", line 1, in top-level template code
    {% extends "appbuilder/base.html" %}
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/templates/appbuilder/base.html", line 1, in top-level template code
    {% extends base_template %}
  File "/app/webapp/templates/custom_base.html", line 1, in top-level template code
    {% extends 'appbuilder/baselayout.html' %}
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/templates/appbuilder/baselayout.html", line 2, in top-level template code
    {% import 'appbuilder/baselib.html' as baselib %}
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/templates/appbuilder/init.html", line 37, in top-level template code
    {% block body %}
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/templates/appbuilder/baselayout.html", line 8, in block 'body'
    {% block navbar %}
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/templates/appbuilder/baselayout.html", line 10, in block 'navbar'
    {% include 'appbuilder/navbar.html' %}
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/templates/appbuilder/navbar.html", line 29, in top-level template code
    {% include 'appbuilder/navbar_menu.html' %}
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/templates/appbuilder/navbar_menu.html", line 11, in top-level template code
    {% if item1 | is_menu_visible %}
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/filters.py", line 136, in is_menu_visible
    return self.security_manager.has_access("menu_access", item.name)
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/security/manager.py", line 1526, in has_access
    return self.is_item_public(permission_name, view_name)
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/security/manager.py", line 1406, in is_item_public
    permissions = self.get_public_permissions()
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/security/sqla/manager.py", line 322, in get_public_permissions
    role = self.get_public_role()
  File "/app/.venv/lib/python3.9/site-packages/flask_appbuilder/security/sqla/manager.py", line 316, in get_public_role
    self.get_session.query(self.role_model)
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2845, in one_or_none
    return self._iter().one_or_none()
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2903, in _iter
    result = self.session.execute(
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1696, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/app/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x20 (32) (SQLExecDirectW)')
...
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Steps to reproduce

Unclear.

[ThomasP0815]

db.session is not a normal session it is a scoped_session https://docs.sqlalchemy.org/en/14/orm/contextual.html#unitofwork-contextual have a look at paragraph "Implicit Method Access"

[Atheuz]

No idea how to properly fix this. I added a readiness/liveness check in my Kubernetes/Docker-Compose config that forces my application to connect to the underlying database every 30 seconds. This seems to work.

[dpgaspar]

Please check if this can help on your case: https://github.com/sqlalchemy/sqlalchemy/issues/5148