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),)]
...