Python调用MySQL时出现MySQL Connection not available错误求助

问题场景 #

程序每秒都会被访问，此前正常运行数日，之后抛出如下错误：

Exception in store_price
[2023-02-03 05:02:56] - Traceback (most recent call last):
File “/x/db.py", line 86, in store_price
with connection.cursor() as cursor:
File "/root/.pyenv/versions/3.9.4/lib/python3.9/site-packages/mysql/connector/connection_cext.py", line 632, in cursor
raise OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available

问题代码 #

def store_price(connection, symbol, last_price, timestamp):
    """    
    :param connection:
    :param symbol:
    :param last_price:
    :param timestamp:
    :return:
    """
    table_name = 'deribit_price_{}'.format(symbol.upper())
    try:
        if connection is None:
            print('Null found..reconnecting')
            connection.reconnect()

        if connection is not None: # this is line # 86
            with connection.cursor() as cursor:
                sql = "INSERT INTO {} (last_price,timestamp) VALUES (%s,%s)".format(table_name)
                cursor.execute(sql, (last_price, timestamp,))
                connection.commit()
    except Exception as ex:
        print('Exception in store_perpetual_data')
        crash_date = time.strftime("%Y-%m-%d %H:%m:%S")
        crash_string = "".join(traceback.format_exception(etype=type(ex), value=ex, tb=ex.__traceback__))
        exception_string = '[' + crash_date + '] - ' + crash_string + '\n'
        print(exception_string)

错误原因分析 #

• 连接有效性检查缺失：仅判断connection是否为None，但未检查连接是否活跃。MySQL连接可能因超时、服务器重启、网络波动等原因断开，但此时connection对象不为None，只是已失效。
• 空连接处理逻辑错误：当connection为None时调用connection.reconnect()会直接抛出AttributeError，因为None对象没有reconnect方法，这部分逻辑完全无效。
• 异常未处理连接恢复：捕获异常后仅打印日志，未尝试重建连接并重试操作，导致错误持续。

修复方案 #

1. 增加连接有效性检测 #

使用connection.is_connected()方法检查连接状态，替代仅判断是否为None：

if connection and not connection.is_connected():
    connection.reconnect(attempts=3, delay=1)

2. 修正空连接处理逻辑 #

如果connection为None，需重新创建连接实例，而非调用reconnect：

# 先定义获取连接的工具函数
def get_db_connection():
    return mysql.connector.connect(
        host='你的数据库地址',
        user='用户名',
        password='密码',
        database='目标库'
    )

# 在store_price函数中调整连接检查逻辑
if connection is None or not connection.is_connected():
    print('Reconnecting to database...')
    connection = get_db_connection()

3. 增加操作重试机制 #

捕获OperationalError时，尝试重建连接并重试插入操作，避免单次失败中断功能：

def store_price(connection, symbol, last_price, timestamp):
    table_name = 'deribit_price_{}'.format(symbol.upper())
    max_retries = 3
    retry_count = 0
    
    while retry_count < max_retries:
        try:
            # 检查并修复连接
            if connection is None or not connection.is_connected():
                print('Reconnecting to database...')
                connection = get_db_connection()
            
            with connection.cursor() as cursor:
                sql = "INSERT INTO {} (last_price,timestamp) VALUES (%s,%s)".format(table_name)
                cursor.execute(sql, (last_price, timestamp,))
                connection.commit()
            return  # 操作成功，退出循环
        except mysql.connector.errors.OperationalError as ex:
            retry_count += 1
            print(f'Operational error, retry {retry_count}/{max_retries}: {ex}')
            time.sleep(1)
            connection = None  # 标记连接失效，下次循环重建
        except Exception as ex:
            print('Exception in store_price')
            crash_date = time.strftime("%Y-%m-%d %H:%M:%S")  # 修正原代码中分钟格式错误（%m改为%M）
            crash_string = "".join(traceback.format_exception(type(ex), ex, ex.__traceback__))
            exception_string = f'[{crash_date}] - {crash_string}\n'
            print(exception_string)
            break  # 非连接类错误，直接退出

4. 数据库配置优化 #

• 调整MySQL的wait_timeout和interactive_timeout参数，延长连接超时时间（例如设置为86400秒），减少因超时导致的连接断开。
• 考虑使用连接池（如mysql.connector.pooling），自动管理连接的创建、复用和销毁，避免手动管理连接的繁琐。

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