Python脚本在try块内意外终止？MySQL连接丢失问题排查

问题背景 #

脚本持续运行约一周后，因以下错误终止：

Traceback (most recent call last):  
  File  "/opt/dba/home/swechsler/library/mysql/bin/./kill_duplicate_processes", line 208, in <module>  
    main()  
  File "/opt/dba/home/swechsler/library/mysql/bin/./kill_duplicate_processes", line 194, in main  
    cnx.commit()  
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/connection_cext.py", line 487, in commit  
    self._cmysql.commit()  
_mysql_connector.MySQLInterfaceError: Lost connection to MySQL server during query  

用户疑惑：明明代码包含try-except块，为何脚本还是终止了？相关代码如下：

相关代码 #

try:
    cursor = cnx.cursor()
    cursor.execute(f"SET SESSION MAX_EXECUTION_TIME={query_timeout}")
    query = (f"SELECT * FROM information_schema.processlist WHERE command in('Query','Execute') AND STATE IS NOT NULL AND info LIKE 'SELECT%' AND TIME > {kill_time} and user not in('debezium','system user','snaplogic')")
    # print(f"running {query} on {server}")
    cursor.execute(query)
    processes = cursor.fetchall()
    cursor.close()

    # Group processes by INFO column
    grouped_processes = {}
    for process in processes:
        pid = process[0]
        info = process[7]
        if info not in grouped_processes:
            grouped_processes[info] = []
        grouped_processes[info].append(pid)

    # Kill all but the most recent process for each group
    oldinfo = ''
    for info, pids in grouped_processes.items():
        process_killed = False;
        pids.sort(reverse=True)
        num_processes = len(pids)
        for pid in pids[1:]:
            kill_query = f"CALL mysql.rds_kill({pid})"
            cursor = cnx.cursor()
            cursor.execute(kill_query)
            cursor.close()
            print(f"[{get_current_datetime()}]: Killed long running process on {server}: {pid}, info: {info}")
            process_killed = True;

        if process_killed:
            message = f"Killed {num_processes - 1} duplicate long running processes on {server} {' '.join(map(str, pids[1:]))}: ```{info}```" if num_processes > 2 else f"killed duplicate long running process on {server} {pids[1:]}: ```{info}```"
            send_msg(message, args.slack)
    cnx.commit()

except mysql.connector.Error as err:
    if err.errno == mysql.connector.errorcode.CR_COMMANDS_OUT_OF_SYNC:
        # MySQL Connector/Python may raise this exception when a query times out
        print(f"[{current_time}]: Query on {server} timed out. Disconnecting...")
    else:
        print(f"[{get_current_datetime()}]: Error communicating with {server}: {err}")
    cnx.close()
    connections[server] = None

原因解析 #

1. 异常类型不匹配：当前except块仅捕获mysql.connector.Error类型的异常，但抛出的_mysql_connector.MySQLInterfaceError是底层C扩展抛出的异常，不属于mysql.connector.Error的子类，因此无法被当前捕获逻辑拦截，导致脚本直接终止。
2. commit阶段无额外防护：cnx.commit()放在try块末尾，若之前的数据库操作已经导致连接丢失，commit时触发的连接错误会直接抛出未被捕获的异常。

修复建议 #

• 扩展异常捕获范围，新增对_mysql_connector.MySQLInterfaceError的捕获，或者直接捕获更宽泛的Exception（需谨慎处理，避免掩盖其他问题）：

  except (mysql.connector.Error, _mysql_connector.MySQLInterfaceError) as err:
  

• 在执行cnx.commit()前，先检查连接状态（比如通过cnx.is_connected()），若连接已断开则跳过commit并触发重连逻辑。
• 考虑将数据库操作的连接管理逻辑封装，确保每次操作前连接有效，避免因长时间闲置导致连接被MySQL服务器主动断开。

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