复用MySQL全局连接执行批量插入时出现Connection not available错误

问题原因 #

• 你在单个execute()调用中执行了多条SQL语句（事务开启、插入、提交），而mysql.connector默认不支持多语句执行（除非显式开启），这种用法会导致连接内部状态紊乱，前两次执行可能侥幸成功，但后续会直接触发连接失效。
• 延长连接超时无法解决这个问题，因为错误根源是非法的多语句执行破坏了连接的可用性，和连接超时设置无关。

修复方案 #

1. 正确使用事务API，避免单execute执行多语句 #

不要手动拼接start transaction和commit，改用mysql.connector内置的事务控制方法，同时注意必须通过游标（cursor）执行SQL语句，而非直接调用连接的execute：

def test():
    mysql_conn = mysql.connect(host=server['host'], user=server['user'], password=server['passwd'])
    cursor = mysql_conn.cursor()
    try:
        while True:
            insert_statement = 'INSERT INTO test VALUES (1,2,3),(2,3,4),(3,4,5);'
            # 手动控制事务流程
            mysql_conn.start_transaction()
            cursor.execute(insert_statement)
            mysql_conn.commit()
    except mysql.connector.errors.OperationalError:
        # 连接失效时重新初始化连接
        mysql_conn = mysql.connect(host=server['host'], user=server['user'], password=server['passwd'])
        cursor = mysql_conn.cursor()
    finally:
        cursor.close()
        mysql_conn.close()

2. 添加连接保活与自动重连机制 #

长时间运行的循环中，即使正确使用事务，连接也可能因网络波动、MySQL服务器端的wait_timeout设置等原因失效，可通过ping方法自动检查并修复连接：

def get_mysql_conn():
    return mysql.connect(host=server['host'], user=server['user'], password=server['passwd'])

def test():
    mysql_conn = get_mysql_conn()
    cursor = mysql_conn.cursor()
    while True:
        try:
            # 检查连接有效性，失效则自动重连
            mysql_conn.ping(reconnect=True)
            insert_statement = 'INSERT INTO test VALUES (1,2,3),(2,3,4),(3,4,5);'
            mysql_conn.start_transaction()
            cursor.execute(insert_statement)
            mysql_conn.commit()
        except Exception:
            # 异常时重建连接
            cursor.close()
            mysql_conn.close()
            mysql_conn = get_mysql_conn()
            cursor = mysql_conn.cursor()

3. （不推荐）启用多语句执行支持 #

如果一定要在单个execute中执行多语句，可在初始化连接时添加multi=True参数，但这种方式会大幅提升SQL注入风险，仅适用于完全信任SQL语句来源的场景：

mysql_conn = mysql.connect(host=server['host'], user=server['user'], password=server['passwd'], multi=True)

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