flask_sqlalchemy `pool_pre_ping` only working sometimes

From the Flask-SQLAlchemy Configuration docs:

Certain database backends may impose different inactive connectiontimeouts, which interferes with Flask-SQLAlchemy’s connection pooling.

By default, MariaDB is configured to have a 600 second timeout. Thisoften surfaces hard to debug, production environment only exceptionslike

2013: Lost connection to MySQL server during query.

If you are using a backend (or a pre-configured database-as-a-service)with a lower connection timeout, it is recommended that you setSQLALCHEMY_POOL_RECYCLE to a value less than your backend’s timeout.

The script cited in the question shows discrepancies between its MySQL timeout-configs (wait_timeout, net_read_timeout) and its SQLAlchemy (pool_recycle, pool_timeout) and Flask-SQLAlchemy timeouts (SQLALCHEMY_POOL_RECYCLE, SQLALCHEMY_POOL_TIMEOUT).

We can resolve this by using the DevConfig helper-class to coordinate the db connection config constants across the app. To do that, we assign our config to static attributes and refer back to them so that there are no conflicting timeout expectations. Here is an implementation:

import osfrom flask import Flaskfrom flask_sqlalchemy import SQLAlchemy as _BaseSQLAlchemy# Coordinate DevConfig with SQLAlchemy and Flask-SQLAlchemy (don't repeat yourself!)class DevConfig():    SQLALCHEMY_POOL_RECYCLE = 35  # value less than backend’s timeout    SQLALCHEMY_POOL_TIMEOUT = 7  # value less than backend’s timeout    SQLALCHEMY_PRE_PING = True    SQLALCHEMY_ENGINE_OPTIONS = {'pool_recycle': SQLALCHEMY_POOL_RECYCLE, 'pool_timeout': SQLALCHEMY_POOL_TIMEOUT, 'pool_pre_ping': SQLALCHEMY_PRE_PING}    DEBUG = True    # SERVER_NAME = '127.0.0.1:5000'    SQLALCHEMY_DATABASE_URI = os.getenv('SQLALCHEMY_DATABASE_URI_DEV')    SQLALCHEMY_TRACK_MODIFICATIONS = Falseclass SQLAlchemy(_BaseSQLAlchemy):    def apply_pool_defaults(self, app, options):        super(SQLAlchemy, self).apply_pool_defaults(app, options)        options["pool_pre_ping"] = DevConfig.SQLALCHEMY_PRE_PING#        options["pool_recycle"] = 30#        options["pool_timeout"] = 35db = SQLAlchemy()config = dict(    dev=DevConfig,)app = Flask(__name__, instance_relative_config=True)app.config.from_object(config['dev'])# INIT DATABASEdb.init_app(app)with app.app_context():    db.create_all()

If you like, you can check the diff for the changes I made: diffchecker.com/Q1e85Hhc