使用FastAPI和SQLAlchemy上传音频时遭遇MySQL OperationalError 2006

问题概述 #

使用FastAPI和SQLAlchemy开发音频上传功能，流程包含用户认证、用户ID验证及元数据保存至MySQL数据库，但在执行audio_processor.save_audio_file_to_db方法时持续抛出以下异常：

pymysql.err.OperationalError: (2006, "MySQL server has gone away (ConnectionAbortedError(10053, 'An established connection was aborted by the software in your host machine', None, 10053, None))")

已排查MySQL服务器状态、错误日志及连接参数，问题仍未解决。相关核心代码如下：

def save_audio_file_to_db(self, user_id: str, audio_file: UploadFile) -> str:
    log.info("Inside save_audio_file_to_db")
    try:
        # Generate a unique ID for the audio file
        audio_id = str(uuid4())

        # Create user-specific and audio-specific folders
        user_folder = f"user_{user_id}"
        audio_folder = os.path.join(COMMON_AUDIO_FOLDER, user_folder, audio_id)
        os.makedirs(audio_folder, exist_ok=True)

        # Save the uploaded audio file to the specified path
        audio_path = os.path.join(audio_folder, audio_file.filename)
        with open(audio_path, "wb") as audio_file_object:
            shutil.copyfileobj(audio_file.file, audio_file_object)

        # Call remove_silence function to remove silence from the audio before saving it to the path
        remove_silence(audio_path)

        # Calculate audio length using pydub
        audio = AudioSegment.from_file(audio_path)
        audio_length_in_seconds = len(audio) / 1000.0  # Convert milliseconds to seconds

        # Get current UTC time and convert it to Sri Lankan time
        utc_now = datetime.now(pytz.utc)
        sri_lankan_timezone = pytz.timezone('Asia/Colombo')
        lk_now = utc_now.astimezone(sri_lankan_timezone)

        # Metadata for the audio file
        metadata = {
            "size": os.path.getsize(audio_path),
            "utc_date": str(utc_now),
            "lk_date": str(lk_now),
            "audio_length_seconds": audio_length_in_seconds,
        }

        # Log metadata information to a file
        log_path = os.path.join(audio_folder, "metadata_log.txt")
        with open(log_path, "w") as log_file:
            for key, value in metadata.items():
                log_file.write(f"{key}: {value}\n")

        # Create an instance of the Audio model
        audio_instance = Audio(
            id=audio_id,
            user_id=user_id,
            path=audio_path,
            utc_date_created=metadata["utc_date"],
            lk_date_created=metadata["lk_date"],
            audio_length_seconds=metadata["audio_length_seconds"]
        )
        # Add the audio instance to the database and commit the changes
        self.db.add(audio_instance)
        self.db.commit()
        log.info("Save Audio in Db audio_id: "+audio_id)
        return audio_id

排查与解决方法 #

1. 调整MySQL连接超时参数 #

该异常常见原因是MySQL主动断开闲置过久的连接。检查并修改以下参数：

• 查看当前参数值：

  SHOW VARIABLES LIKE 'wait_timeout';
  SHOW VARIABLES LIKE 'interactive_timeout';
  

• 若参数值过小（如默认300秒），临时调整为更长时间（如3600秒）：

  SET GLOBAL wait_timeout=3600;
  SET GLOBAL interactive_timeout=3600;
  

• 永久生效需修改my.cnf/my.ini配置文件，添加或修改：

  wait_timeout = 3600
  interactive_timeout = 3600
  

  保存后重启MySQL服务。

2. 优化SQLAlchemy连接池配置 #

在创建SQLAlchemy引擎时，设置pool_recycle参数，让SQLAlchemy主动回收超时连接，避免被MySQL断开：

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+pymysql://username:password@host:port/dbname",
    pool_recycle=300  # 每300秒回收一次连接
)

3. 减少数据库连接持有时间 #

当前代码中，音频处理（去静音、计算时长）属于耗时操作，但全程持有数据库会话连接，极易触发超时。优化逻辑：先完成所有音频处理，再临时获取会话执行数据库操作，示例修改如下：

# 替换原有的数据库操作部分
from sqlalchemy.orm import Session
from your_module import SessionLocal  # 假设这是你创建会话的方法

# ... 前面的音频处理、元数据生成代码保持不变 ...

# 最后临时创建会话保存数据
db: Session = SessionLocal()
try:
    audio_instance = Audio(
        id=audio_id,
        user_id=user_id,
        path=audio_path,
        utc_date_created=metadata["utc_date"],
        lk_date_created=metadata["lk_date"],
        audio_length_seconds=metadata["audio_length_seconds"]
    )
    db.add(audio_instance)
    db.commit()
    log.info(f"Save Audio in Db audio_id: {audio_id}")
finally:
    db.close()  # 确保会话关闭，释放连接

4. 添加连接异常重试机制 #

针对连接断开异常，增加重试逻辑，避免单次失败导致请求失败：

from sqlalchemy.exc import OperationalError

# ... 音频处理、元数据生成代码 ...

max_retries = 2
for attempt in range(max_retries):
    try:
        self.db.add(audio_instance)
        self.db.commit()
        log.info(f"Save Audio in Db audio_id: {audio_id}")
        break
    except OperationalError as e:
        if "MySQL server has gone away" in str(e) and attempt < max_retries - 1:
            log.warning("数据库连接断开，正在重试...")
            self.db.rollback()
            # 重新创建会话
            self.db = SessionLocal()
        else:
            raise

5. 检查本地防火墙/杀毒软件 #

错误信息中提到ConnectionAbortedError(10053, 'An established connection was aborted by the software in your host machine'，说明本地安全软件可能拦截了MySQL连接。检查防火墙、杀毒软件规则，将MySQL进程或端口加入信任列表。

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