flask_sqlalchemy `pool_pre_ping`有时只工作
为了进行测试，我将MYSQL (RDS)参数修改如下；
wait_timeout = 40 (缺省值为28800) max_allowed_packet = 1GB (最大-确保问题不是由小数据包引起的) net_read_timeout = 10 interactive_timeout不变
然后在没有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秒后).但是，当我长时间保持连接不活动(没有注意到，但~ >10-15分钟)时，我再次看到同样的错误。
根据文档(特别是处理断开部分)，pool_pre_ping选项应该在后台仪式上处理这种场景？或者，在MySQL服务器中是否还有其他需要更改的超时变量？
回答 2
Stack Overflow用户
发布于 2021-10-11 13:16:16
我设置了以下设置：
SQLALCHEMY_ENGINE_OPTIONS = {
'pool_size': 10,
'pool_recycle': 60,
'pool_pre_ping': True
}过去的几个月已经停止了.
Stack Overflow用户
发布于 2021-07-28 22:32:34
来自Flask-SQLAlchemy
某些数据库后端可能会施加不同的非活动连接超时，这会干扰的连接池。 默认情况下，MariaDB被配置为有600秒超时。这通常是很难调试的，生产环境只有一些异常，如
2013: Lost connection to MySQL server during query.如果使用的是后端(或预先配置的数据库作为服务)，并且连接超时较低，则建议将SQLALCHEMY_POOL_RECYCLE设置为小于后端超时的值。
问题中引用的脚本显示了它的MySQL之间的差异
超时差(wait_timeout，net_read_timeout)及其SQLAlchemy
(pool_recycle，pool_timeout)和水瓶-SQLAlchemy
超时(SQLALCHEMY_POOL_RECYCLE，SQLALCHEMY_POOL_TIMEOUT)。
我们可以通过使用DevConfig助手类来协调整个应用程序中的db连接配置常量来解决这个问题。为此，我们将配置分配给静态属性并引用它们，这样就不会出现冲突的超时期望。以下是一个实现：
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()如果您愿意，可以检查差异以查看我所做的更改：电子邮件:q1e85hc
https://stackoverflow.com/questions/58866560
复制相似问题
腾讯云开发者
