【python】Flask-SQLAlchemy的Lost connection问题

一、问题现象

在使用flask_sqlalchemy的时候，几分钟未请求时，当再一次使用连接的时候，报如下错误：

[2021-12-02 09:48:44,009] ERROR in app: Exception on /xray/build [POST]
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/app/controller/xrayServer.py", line 18, in xray_build
    task_info = XrayHandle().xray_build()
  File "/app/module/xrayHandle.py", line 60, in xray_build
    XrayJobDao().init_xray_job(xray_job_dto)
  File "/app/dao/xrayJobDao.py", line 33, in init_xray_job
    db.session.commit()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 163, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1046, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 502, in commit
    self._assert_active(prepared_ok=True)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 296, in _assert_active
    code="7s2a",
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')

二、问题分析

项目配置：flask_sqlalchemy自动回收连接的秒数，默认设置的是 -1，即永远不超时

SQLALCHEMY_POOL_RECYCLE =-1

而MySQL的wait_timeout默认是28800，也就是超过8小时的连接就会自动失效，而要连接的mysql通过查看发现是120秒

show variables like '%timeout%'

可以发现：

wait_timeout=120

三、问题原因

使用数据库连接池来获取连接，连接池里的连接可能会较长时间不关闭，等待被使用，这就与mysql连接超时机制起冲突，当连接池配置永不关闭或者关闭时间超过8小时就会出现Lost connection的问题

当超过8个小时没有新的数据库请求的时候，数据库连接就会断开，如果连接池的配置是用不关闭或者关闭时间超过8小时，这个时候连接池没有回收并且还认为连接池与数据库之间的连接还存在，就会继续连接，但是数据库连接断开了，就会报错数据库连接失败

四、解决方案

4.1 修改mysql的超时时间

修改mysql配置文件里wait_timeout参数，让这个时间大于连接池的回收时间

修改配置文件的方式不推荐

4.2 修改数据库连接池回收时间

修改数据库连接池的配置，数据库连接池都会带有一个参数：
回收时间(就是一定时间内不使用就会回收)，修改这个参数的值，不要大于wait_timeout的值即可

在flask-SQLAlchemy中有个配置是SQLALCHEMY_POOL_RECYCLE(多久后对线程池中的线程进行一次连接的回收)，如果这个值是-1代表永不回收
Flask-SQLALchemy自动设定这个值为-1，可以将这个值设置的小于wait_timeout参数的值即小于120即可

可以查看Flask-SQLAlchemy中的配置值

4.3 禁用SQLAlchemy数据库连接池

只需要在调用create_engine是指定连接池为NullPool，SQLAlchemy就会在执行 session.close()后立刻断开数据库连接
当然，如果session对象被析构但是没有被调用session.close()，则数据库连接不会被断开，直到程序终止

五、问题解决

使用方案4.2解决问题

SQLALCHEMY_POOL_RECYCLE = 100
SQLALCHEMY_POOL_TIMEOUT = 20

重启服务后自测长时间连接仍可正常使用，问题解决