flask SQLAlchemy 连接失效

现象

我们使用 SQLAlchemy ORM 操作数据库，当 create_engine 使用默认参数的时候，连接池是打开着的。对大部分数据库来说，poolclass 默认为 QueuePool。当一个请求进来，SQLAlchemy 会创建一个数据库连接，执行结束后把连接放回池子里。下一个请求来的时候，就可以直接使用之前的连接。当然，如果同时进来多个不够分配的时候，会创建另外的连接用于使用，执行结束后又放回池子里。池子里的最大连接数是可以配置的。这种方式可以避免频繁创建、销毁连接，从而提高执行效率。

但是，这带来另一个问题。当数据库突然挂掉或者数据库过一定时间清理未活动连接的时候，SQLAlchemy 是不知道的。当一个请求进来时，会被分配一个失效的连接，自然会抛出一些异常。

MySQL 中使用如下命令查看未活动连接过期时间

show variables like "interactive_timeout";

结果类似这样，单位是秒。

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+

下面，我们重现下错误。启动应用程序后，我们首先访问下这个链接

http://127.0.0.1:8080

此时，连接池里有一个连接了。

在继续下一步之前，我们先关闭或者重启下数据库，然后请求，抛出了无法连接数据库的错误

sqlalchemy.exc.OperationalError:
(pymysql.err.OperationalError)
(2013, 'Lost connection to MySQL server during query')

因为被分配的连接由于数据库的重启已经失效了嘛。

解决

究其原因，就是连接池的连接在不知情的情况下在数据库服务器上被关掉了。这个大致分为两种情况：

一、数据库异常或者误操作，比如数据库挂掉、重启、有的连接被误操作给 kill 掉了等等。

> 这种情况下只好重启应用程序，让应用重新维护连接池；

二、连接长时间未活动。这又有两种情况：

1、create_engine 时指定的连接池中的连接的回收时间大于数据库配置的未活动连接过期时间，由于连接的回收时间一般都是设置的一两个小时，而数据库的未活动连接过期时间默认是八个小时，所以这种情况一般不会出现；

> 在flask-SQLAlchemy中有个配置是SQLALCHEMY_POOL_RECYCLE（多之后对线程池中的线程进行一次连接的回收），如果这个值是-1代表永不回收，Flask-SQLALchemy 的默认值为2小时，我们可以将这个值设置的小于wait_timeout参数的值也就是8小时即可。

2、应用程序里的逻辑代码有问题，在进行数据库的写入操作后，缺少 commit、rollback、close 的操作将连接放回连接池，连接池没法管理，当这个连接被数据库回收后，也就出现了上面的异常。这种情况，有以下几个解决办法：

1. 查看相关的业务，补充缺失的连接维护操作（推荐）；

2. 可以通过重启应用程序解决，不过指标不治本，运行一段时间后，问题还会出现；

3. SQLAlchemy 开启 autocommit，不过这样就不能手动 rollback 了，在很多插入、更新场景中，不大实用；

   # 自动 commit 提交到数据库
   SQLALCHEMY_COMMIT_ON_TEARDOWN = True
   SQLALCHEMY_TRACK_MODIFICATIONS = True

4. 使用请求扩展app.teardown_appcontext,每次请求结束之后，手动提交 session

   from yourapplication.database import db_session
   
   @app.after_request
   def after_clean(resp,*args,**kwargs):
       db.session.commit()
       return resp
   
   @app.teardown_appcontext
   def shutdown_session(exception=None):
       db_session.remove()

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

   create_engine(self.db_addr, poolclass=NullPool)

6. 如果不想开启自动提交，又要使用防止发生上面的错误，可以开启 SQLAlchemy 的预检功能，每次执行sql前 悲观检查db是否可用;虽然资源稍微额外的消耗,但是简单可靠

   app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {'pool_pre_ping': True}
   # 或者
   engine = create_engine("mysql+pymysql://user:pw@host/db", pool_pre_ping=True,pool_recycle=1800)