Synopsis: 应用长时间无请求访问时，最近一次请求数据库将报错 Lost connection to MySQL server during query。这个过程有可能会很久，从而导致 Web 服务端返回响应时长过久（比如分钟级别）

1. 问题描述

MySQL 连接默认 8 小时没有使用时将被服务端关闭：

mysql> show global variables like 'wait_timeout%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show session variables like 'wait_timeout%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.00 sec)

如果你的应用超过这个时间都没有请求，再次查询数据库时将报错 Lost connection to MySQL server during query

准备测试环境：

python3 -m venv venv source venv/bin/activate pip install sqlalchemy pymysql

准备测试脚本：

import time from sqlalchemy.engine import create_engine url = 'mysql+pymysql://root:123456@127.0.0.1:3306/test' engine = create_engine(url) query = 'SELECT now();' while True: print('Query-01', engine.execute(query).fetchall()) engine.execute('SET wait_timeout=3') # set session variable `wait_timeout` time.sleep(5) print('Query-02', engine.execute(query).fetchall())

执行脚本：

(venv) [root@aliyun test]# python test.py  Query-01 [(datetime.datetime(2020, 12, 12, 11, 7, 20),)] Traceback (most recent call last): File "/root/test/venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 127 7, in _execute_context cursor, statement, parameters, context File "/root/test/venv/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute cursor.execute(statement, parameters) File "/root/test/venv/lib64/python3.6/site-packages/pymysql/cursors.py", line 163, in ex ecute result = self._query(query) File "/root/test/venv/lib64/python3.6/site-packages/pymysql/cursors.py", line 321, in _q uery conn.query(q) File "/root/test/venv/lib64/python3.6/site-packages/pymysql/connections.py", line 505, i n query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/root/test/venv/lib64/python3.6/site-packages/pymysql/connections.py", line 724, i n _read_query_result result.read() File "/root/test/venv/lib64/python3.6/site-packages/pymysql/connections.py", line 1069, in read first_packet = self.connection._read_packet() File "/root/test/venv/lib64/python3.6/site-packages/pymysql/connections.py", line 646, i n _read_packet packet_header = self._read_bytes(4) File "/root/test/venv/lib64/python3.6/site-packages/pymysql/connections.py", line 699, i n _read_bytes CR.CR_SERVER_LOST, "Lost connection to MySQL server during query") pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query') The above exception was the direct cause of the following exception: Traceback (most recent call last): File "test.py", line 15, in <module> print('Query-02', engine.execute(query).fetchall()) File "/root/test/venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 223 5, in execute return connection.execute(statement, *multiparams, **params) File "/root/test/venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 100 3, in execute return self._execute_text(object_, multiparams, params) File "/root/test/venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 117 8, in _execute_text parameters, File "/root/test/venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 131 7, in _execute_context e, statement, parameters, cursor, context File "/root/test/venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 151 1, in _handle_dbapi_exception sqlalchemy_exception, with_traceback=exc_info[2], from_=e File "/root/test/venv/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 182 , in raise_ raise exception File "/root/test/venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 127 7, in _execute_context cursor, statement, parameters, context File "/root/test/venv/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute cursor.execute(statement, parameters) File "/root/test/venv/lib64/python3.6/site-packages/pymysql/cursors.py", line 163, in ex ecute result = self._query(query) File "/root/test/venv/lib64/python3.6/site-packages/pymysql/cursors.py", line 321, in _q uery conn.query(q) File "/root/test/venv/lib64/python3.6/site-packages/pymysql/connections.py", line 505, i n query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/root/test/venv/lib64/python3.6/site-packages/pymysql/connections.py", line 724, i n _read_query_result result.read() File "/root/test/venv/lib64/python3.6/site-packages/pymysql/connections.py", line 1069, in read first_packet = self.connection._read_packet() File "/root/test/venv/lib64/python3.6/site-packages/pymysql/connections.py", line 646, i n _read_packet packet_header = self._read_bytes(4) File "/root/test/venv/lib64/python3.6/site-packages/pymysql/connections.py", line 699, i n _read_bytes CR.CR_SERVER_LOST, "Lost connection to MySQL server during query") sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to  MySQL server during query')[SQL: SELECT now();] (Background on this error at: http://sqlalche.me/e/13/e3q8)

2. 解决方案

只需给 create_engine() 方法传入 pool_recycle 参数即可：

:param pool_recycle=-1: this setting causes the pool to recycle connections after the given number of seconds has passed. It defaults to -1, or no timeout. For example, setting to 3600 means connections will be recycled after one hour. Note that MySQL in particular will disconnect automatically if no activity is detected on a connection for eight hours (although this is configurable with the MySQLDB connection itself and the server configuration as well). .. seealso:: :ref:`pool_setting_recycle`

我们的测试脚本中设置了 MySQL 的会话级别 wait_timeout=3，所以这里我们传入 pool_recycle=1(比 wait_timeout 小即可) 让连接保持存活

import time from sqlalchemy.engine import create_engine url = 'mysql+pymysql://root:123456@127.0.0.1:3306/test' engine = create_engine(url, pool_recycle=1) query = 'SELECT now();' while True: print('Query-01', engine.execute(query).fetchall()) engine.execute('SET wait_timeout=3') # set session variable `wait_timeout` time.sleep(5) print('Query-02', engine.execute(query).fetchall())

再次测试：

(venv) [root@aliyun test]# python test.py Query-01 [(datetime.datetime(2020, 12, 12, 11, 11, 59),)] Query-02 [(datetime.datetime(2020, 12, 12, 11, 12, 4),)] Query-01 [(datetime.datetime(2020, 12, 12, 11, 12, 4),)] Query-02 [(datetime.datetime(2020, 12, 12, 11, 12, 9),)] ...