python - flask_sqlalchemy `pool_pre_ping` 有时只工作

Question

为了测试，我将MYSQL(RDS)参数修改如下；

wait_timeout = 40 (default was 28800)

max_allowed_packet = 1GB (max - just to be sure issue not caused by small packets)

net_read_timeout = 10

interactive_timeout unchanged

然后在没有 pool_pre_ping 的情况下测试了我的应用程序选项设置(默认为 False)，使应用程序保持非事件状态 40 秒，尝试登录，然后我得到

Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]: Traceback (most recent call last):
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]:   File "/var/www/api_server/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]:     context)
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]:   File "/var/www/api_server/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]:     cursor.execute(statement, parameters)
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]:   File "/var/www/api_server/venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]:     res = self._query(query)
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]:   File "/var/www/api_server/venv/lib/python3.6/site-packages/MySQLdb/cursors.py", line 312, in _query
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]:     db.query(q)
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]:   File "/var/www/api_server/venv/lib/python3.6/site-packages/MySQLdb/connections.py", line 224, in query
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]:     _mysql.connection.query(self, query)
Nov 14 20:05:20 ip-172-31-33-52 gunicorn[16962]: MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

添加了 pool_pre_ping像这样(使用flask_sqlalchamy 2.4.1版)；

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy as _BaseSQLAlchemy


class SQLAlchemy(_BaseSQLAlchemy):
    def apply_pool_defaults(self, app, options):
        super(SQLAlchemy, self).apply_pool_defaults(app, options)
        options["pool_pre_ping"] = True
#        options["pool_recycle"] = 30
#        options["pool_timeout"] = 35

db = SQLAlchemy()


class DevConfig():
    SQLALCHEMY_ENGINE_OPTIONS = {'pool_recycle': 280, 'pool_timeout': 100, 'pool_pre_ping': True} # These configs doesn't get applied in engine configs :/
    DEBUG = True
    # SERVER_NAME = '127.0.0.1:5000'
    SQLALCHEMY_DATABASE_URI = os.getenv('SQLALCHEMY_DATABASE_URI_DEV')
    SQLALCHEMY_TRACK_MODIFICATIONS = False

config = dict(
    dev=DevConfig,
)

app = Flask(__name__, instance_relative_config=True)
app.config.from_object(config['dev'])

# INIT DATABASE
db.init_app(app)
with app.app_context():
    db.create_all()

-----------run.py
app.run(host='127.0.0.1', port=5000)

有了这个，即使在 MySQL 服务器关闭了以前的连接之后，webapp 现在也设法获得新的连接。当我在服务器关闭数据库后立即访问数据库时，它总是正常工作(最多尝试 50 秒后)...我看到同样的错误。
根据文档，(尤其是 Dealing with disconnects 部分)， pool_pre_ping选项应该在后台仪式中处理这种情况吗？或者我需要在 MySQL 服务器中更改任何其他超时变量吗？

Answer 1

来自 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.

如果您使用后端(或预先配置的数据库即服务)
连接超时时间较短，建议设置 SQLALCHEMY_POOL_RECYCLE小于后端超时的值。

问题中引用的脚本显示其 MySQL 之间存在差异。 timeout-configs ( wait_timeout , net_read_timeout ) 及其 SQLAlchemy ( pool_recycle , pool_timeout ) 和 Flask-SQLAlchemy 超时( SQLALCHEMY_POOL_RECYCLE ， SQLALCHEMY_POOL_TIMEOUT )。
我们可以通过使用 DevConfig 来解决这个问题。 helper-class 来协调整个应用程序的数据库连接配置常量。为此，我们将我们的配置分配给静态属性并返回到它们，以便没有冲突的超时期望。这是一个实现:

import os
from flask import Flask
from 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 = False

class 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"] = 35

db = SQLAlchemy()

config = dict(
    dev=DevConfig,
)

app = Flask(__name__, instance_relative_config=True)
app.config.from_object(config['dev'])

# INIT DATABASE
db.init_app(app)
with app.app_context():
    db.create_all()

如果您愿意，可以查看 the diff对于我所做的更改: diffchecker.com/Q1e85Hhc