执行6分钟长查询时SQLAlchemy连接MySQL丢失错误求助

问题描述 #

我正在执行一个时长6分钟的读取查询，代码如下：

source_connection = source_db_connection.connect().execution_options(stream_results=True, max_row_buffer=1000)

for df_from_db in pd.read_sql_query(raw_data_query_pandas, source_db_connection, params=(...), chunksize=1000):
    # 数据处理逻辑

但运行时出现以下错误：

Exception during reset or similar
Traceback (most recent call last):
  File "%PATH%\venv\lib\site-packages\sqlalchemy\pool\base.py", line 986, in _finalize_fairy
    fairy._reset(
  File "%PATH%\venv\lib\site-packages\sqlalchemy\pool\base.py", line 1432, in _reset
    pool._dialect.do_rollback(self)
  File "%PATH%\venv\lib\site-packages\sqlalchemy\engine\default.py", line 692, in do_rollback
    dbapi_connection.rollback()
  File "%PATH%\venv\lib\site-packages\mysql\connector\connection_cext.py", line 538, in rollback
    self._cmysql.rollback()
_mysql_connector.MySQLInterfaceError: Lost connection to MySQL server during query

已尝试过pool_recycle=240、pool_pre_ping=True、poolclass=NullPool这些连接参数，问题仍未解决，求有效解决方案。

有效解决方案 #

1. 修改MySQL服务器超时配置 #

错误核心是长查询过程中连接被MySQL主动断开，需要调整服务器端的超时参数：

• 打开MySQL配置文件（my.cnf或my.ini），设置wait_timeout = 3600（1小时，确保大于你的查询时长）
• 同步设置interactive_timeout = 3600，保持和wait_timeout一致
  修改后重启MySQL服务生效。

2. 在代码中添加连接心跳 #

在处理chunk的循环里，定期发送简单查询维持连接活跃：

source_connection = source_db_connection.connect().execution_options(stream_results=True, max_row_buffer=1000)
chunk_count = 0
for df_from_db in pd.read_sql_query(raw_data_query_pandas, source_connection, params=(...), chunksize=1000):
    # 处理数据逻辑
    chunk_count += 1
    # 每处理10个chunk发送一次心跳
    if chunk_count % 10 == 0:
        source_connection.execute("SELECT 1")
        source_connection.commit()

3. 优化查询缩短执行时间 #

从根源解决超时问题，优化查询本身：

• 检查查询涉及的表是否有合适的索引，避免全表扫描
• 只查询需要的字段，去掉不必要的列
• 将大查询拆分为多个小查询（比如按日期分段），减少单次查询的耗时

4. 调整SQLAlchemy连接参数 #

创建引擎时增加连接层面的超时设置，确保覆盖所有超时场景：

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+mysqlconnector://user:password@host/database",
    pool_recycle=3600,  # 回收周期设为1小时
    connect_args={
        "connect_timeout": 3600,
        "socket_timeout": 3600
    }
)

5. 显式管理连接生命周期 #

使用上下文管理器手动控制连接的创建和关闭，避免连接池的闲置连接干扰：

with source_db_connection.connect().execution_options(stream_results=True, max_row_buffer=1000) as conn:
    for df_from_db in pd.read_sql_query(raw_data_query_pandas, conn, params=(...), chunksize=1000):
        # 数据处理逻辑
# 上下文管理器会自动关闭连接，避免池中的连接因闲置超时被断开

内容的提问来源于stack exchange，提问作者Pythonist