Airflow 2.5.1调度器启动失败，报MySQL server has gone away错误

在Deb9实例部署Airflow 2.5.1调度器（使用LocalExecutor）与webserver，MySQL数据库位于另一实例。已通过PING和airflow db check验证连接正常，且airflow db init成功创建所有表，但执行airflow scheduler启动调度器时出现报错。

初始报错日志 #

airflow-scheduler.err： #

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (2006, 'MySQL server has gone away')
(Background on this error at: https://sqlalche.me/e/14/e3q8)
[2023-02-09 01:12:34 +0530] [15451] [ERROR] Connection in use: ('0.0.0.0', 8793)
[2023-02-09 01:12:34 +0530] [15451] [ERROR] Retrying in 1 second.
[2023-02-09 01:12:35 +0530] [15451] [ERROR] Connection in use: ('0.0.0.0', 8793)
[2023-02-09 01:12:35 +0530] [15451] [ERROR] Retrying in 1 second.
[2023-02-09 01:12:36 +0530] [15451] [ERROR] Connection in use: ('0.0.0.0', 8793)
[2023-02-09 01:12:36 +0530] [15451] [ERROR] Retrying in 1 second.
[2023-02-09 01:12:37 +0530] [15451] [ERROR] Can't connect to ('0.0.0.0', 8793)

airflow-scheduler.log： #

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (2006, 'MySQL server has gone away')
(Background on this error at: https://sqlalche.me/e/14/e3q8)
2023-02-09 01:12:33,040 INFO - Shutting down LocalExecutor; waiting for running tasks to finish.  Signal again if you don't want to wait.
2023-02-09 01:12:34,066 INFO - Sending Signals.SIGTERM to group 15492. PIDs of all processes in the group: [15492]
2023-02-09 01:12:34,066 INFO - Sending the signal Signals.SIGTERM to group 15492
2023-02-09 01:12:34,158 INFO - Process psutil.Process(pid=15492, status='terminated', exitcode=0, started='01:12:32') (15492) terminated with exit code 0
2023-02-09 01:12:34,159 INFO - Exited execute loop

更新情况 #

已杀掉占用8793端口的进程，但仍持续出现MySQL server has gone away错误，报错日志如下：

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/airflow/airflow_venv/bin/airflow", line 8, in <module>
    sys.exit(main())
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/airflow/__main__.py", line 39, in main
    args.func(args)
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/airflow/cli/cli_parser.py", line 52, in command
    return func(*args, **kwargs)
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/airflow/utils/cli.py", line 108, in wrapper
    return f(*args, **kwargs)
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/airflow/cli/commands/scheduler_command.py", line 68, in scheduler
    _run_scheduler_job(args=args)
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/airflow/cli/commands/scheduler_command.py", line 43, in _run_scheduler_job
    job.run()
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/airflow/jobs/base_job.py", line 258, in run
    self._execute()
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/airflow/jobs/scheduler_job.py", line 759, in _execute
    self._run_scheduler_loop()
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/airflow/jobs/scheduler_job.py", line 840, in _run_scheduler_loop
    self.adopt_or_reset_orphaned_tasks()
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/airflow/utils/session.py", line 75, in wrapper
    return func(*args, session=session, **kwargs)
  File "/usr/local/lib/python3.7/contextlib.py", line 119, in __exit__
    next(self.gen)
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/airflow/utils/session.py", line 36, in create_session
    session.commit()
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1451, in commit
    self._transaction.commit(_to_root=self.future)
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 836, in commit
    trans.commit()
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2459, in commit
    self._do_commit()
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2649, in _do_commit
    self._connection_commit_impl()
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2620, in _connection_commit_impl
    self.connection._commit_impl()
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1091, in _commit_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2125, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1089, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
  File "/home/airflow/airflow_venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 686, in do_commit
    dbapi_connection.commit()
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (2006, 'MySQL server has gone away')
(Background on this error at: https://sqlalche.me/e/14/e3q8)

问题原因分析 #

• MySQL连接超时回收：MySQL默认的wait_timeout和interactive_timeout设置会自动断开闲置连接，若Airflow调度器的连接长时间未使用，会被MySQL主动回收。而SQLAlchemy连接池默认未开启连接自动回收机制，复用失效连接时就会触发该错误。
• 数据包大小超限：调度器在同步任务状态、写入元数据时，可能生成超过MySQLmax_allowed_packet限制的数据包，服务器会直接断开连接并返回报错。
• 网络链路不稳定：跨实例的网络存在丢包、抖动等情况，导致数据库连接中途中断，Airflow连接池未配置异常重连逻辑，无法自动恢复连接。
• SQLAlchemy连接池参数配置不足：未设置pool_recycle参数（建议值小于MySQL的wait_timeout），无法定时回收即将超时的连接；或未开启pool_pre_ping，无法在使用连接前验证其有效性。

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