昔我往矣

Python连接 MySQL 数据库的超时问题

2022年04月8日

当使用Pytohn的 Flask-SQLAlchemy库操作 MySQL 数据时,出现'MySQL server has gone away' 了,是怎么回事呢?又该怎么办呢?分别从MySQL服务端和Python客户端来排查相关问题。

Time Clock

MySQL 服务端的现象

MySQL服务端的连接超时时间是由wait_timeout变量决定的,超时时间有两种:当前会话超时和全局默认超时时间。MySQL的默认超时时间是8h,可以通过set wait_timeout进行变更。通过以下的语句,可以修改MySQL的wait_timeout变量。

> show variables like "%wait_timeout%";  # 查看当前会话的超时时间
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| wait_timeout             | 28800 |
+--------------------------+-------+
> show global variables like "%wait_timeout%";  # 查看全局的会话超时时间      
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| wait_timeout             | 800   |
> set global wait_timeout=600;   # 修改全局 wait_timeout

wait_timeout的含义:当会话持续空闲,达到wait_timeout 规定的超时时间,MySQL 会关闭这个会话。通过show processlist 查看当前的会话,和会话空闲时间。

> show processlist;   
+-----+-------------+-----------+-------------------+---------+--------+--------------------------+------------------+----------+
| Id  | User        | Host      | db                | Command | Time   | State                    | Info             | Progress |
+-----+-------------+-----------+-------------------+---------+--------+--------------------------+------------------+----------+
| 99  | root        | localhost | <null>            | Query   | 0      | Init                     | show processlist | 0.0      |
| 111 | root        | localhost | test_database | Sleep   | 2      |                          | <null>           | 0.0      |
+-----+-------------+-----------+-------------------+---------+--------+--------------------------+------------------+----------+

其中,Time字段记录了会话的空闲时间,当Time字段持续增长到 wait_timeout的大小时,MySQL 就关闭这个会话,如果在达到wait_timeout之前,会话上产生了新的请求,Time 会重新开始计时。

Flask-SQLAlchemy 客户端的情况

Flask-SQLAlchemy 中和超时相关的主要配置是SQLALCHEMY_POOL_RECYCLE,含义是连接被自动回收的秒数,Flask-SQLAlchemy 把这个数值默认设置为2h,如果连接空闲2h,则客户端回收该连接。

根据Flask-SQLALchemy文档的描述,SQLALCHEMY_POOL_RECYCLE配置将会在后续版本中将会删除,可以使用SQLALCHEMY_ENGINE_OPTIONS 这个新配置项进行指定。

问题的产生

在企业中,一般会将MySQL的wait_timout变量调低,比如我们公司设置为600s,以防止连接数过大,长久不释放。

这就导致了一个问题:当一个连接空闲600s后,MySQL一端就会将这个连接关闭,而 Flask-SQLAlchemy 一端维护的连接还未关闭,此时,当Flask-SQLAlchemy使用老的连接向数据库发起请求时,由于MySQL一端已经关闭连接,此时Flask-SQLAlchemy就出现如下的异常,显示MySQL 服务走丢了。

……
  File "/home/lihz/.local/share/virtualenvs/site-verification-0gTRisYk/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2006, 'MySQL server has gone away')

如果,连接空闲时间达到了Flask-SQLAlchemySQLALCHEMY_POOL_RECYCLE配置所规定的数值,该连接会被Flask-SQLAlchemy主动回收,此时,若Flask-SQLAlchemy发起数据库操作,会创建新的连接,成功连接上数据库。

其实,产生这个错误的可能性还有很多,比如人为在MySQL上kill了连接,以上描述的是比较常见的一种可能性。

问题的解决

针对这种默认超时配置不匹配导致的问题,我们有多种解决办法:

  1. 方法一:在发起数据库操作前,先向MySQL发起一个无意义的select请求,比如select now() 或者 select 1=1。该操作的作用是,帮助Flask-SQLAlchemy 预先确认连接是否可用。如果连接不可用,通过抛出的异常,Flask-SQLAlchemy就可以发现该连接已经被服务端关闭,从而主动回收该连接,当发起正式的数据库操作时,就会创建新的连接了。
  2. 方法二:修改Flask-SQLAlchemySQLALCHEMY_POOL_RECYCLE配置大小。将其修改为低于服务端wait_timout变量即可。由于SQLALCHEMY_POOL_RECYCLE 配置处于废弃阶段,可以使用新的配置方法,如下:
SQLALCHEMY_DATABASE_URI = "mysql://test_user:xxx@localhost:3306/test_db?charset=utf8"
SQLALCHEMY_ENGINE_OPTIONS = {
    "pool_recycle": 60
}
  1. 方法三:使用完连接后,主动关闭。

当然,为了方便,我选择了第二种方案。

参考:

https://flask-sqlalchemy.palletsprojects.com/en/2.x/config/

当前暂无评论 »

添加新评论 »