diogosilva30 opened a new issue, #66177: URL: https://github.com/apache/airflow/issues/66177
### Under which category would you file this issue? Airflow Core ### Apache Airflow version 3.2.1 (also reproduced on 3.1.8) ### What happened and how to reproduce it? **Issue Description** `airflow db clean --tables dag_version --skip-archive --yes` hangs **forever** on a MySQL backend when any old `dag_version` row is referenced by a live `task_instance` row (which is the normal case for any DAG with run history older than the cleanup horizon). The hang is silent — no error is logged, the CLI just blocks. In a Kubernetes `CronJob` this means stuck pods accumulate indefinitely; the default MySQL `lock_wait_timeout` is 31_536_000 seconds (one year). **Root cause** (verified against the source): 1. `task_instance.dag_version_id` has `ON DELETE RESTRICT`. 2. `_do_delete` in [`airflow-core/src/airflow/utils/db_cleanup.py`](https://github.com/apache/airflow/blob/3.2.1/airflow-core/src/airflow/utils/db_cleanup.py#L217-L293) issues a multi-table `DELETE FROM dag_version USING dag_version, _airflow_deleted__... WHERE ...`, which fails with MySQL error `1451 Cannot delete or update a parent row`. 3. The `except BaseException as e: raise e` block (lines 285–286) re-raises **without calling `session.rollback()`**. The session/connection therefore still holds `SHARED_WRITE` on `dag_version` and `SHARED_READ` on the archive table. 4. The `finally:` block (lines 288–291) calls `target_table.drop(bind=session.get_bind())`, which checks out a **second** connection from the pool. `DROP TABLE` requires `EXCLUSIVE` metadata-lock → blocked forever waiting for the first connection (still alive in the same call stack) to release its `SHARED_*` locks. The `_do_delete` body in 3.2.1 is byte-identical to 3.1.8. The only `dag_version`-related change in 3.2.1 was adding `keep_last=True, keep_last_group_by=["dag_id"]` ([lines 165–172](https://github.com/apache/airflow/blob/3.2.1/airflow-core/src/airflow/utils/db_cleanup.py#L165-L172)) which mitigates only the trivial case of a single `dag_version` row per DAG. **Steps to reproduce** Fully containerised reproducer (MySQL 8.4 in Docker + venv install of pinned Airflow with constraints file): https://github.com/diogosilva30/airflow-db-clean-dag-version-deadlock ```bash git clone https://github.com/diogosilva30/airflow-db-clean-dag-version-deadlock cd airflow-db-clean-dag-version-deadlock ./run.sh # AIRFLOW_VERSION=3.2.1 by default AIRFLOW_VERSION=3.1.8 ./run.sh # also reproduces ``` `run.sh` boots MySQL, installs Airflow with constraints, seeds two `dag_version` rows for one DAG (one back-dated to 2024-01-01, one current), inserts a `task_instance` pinning the old one, then runs `airflow db clean --tables dag_version --clean-before-timestamp 2026-01-01 --skip-archive --yes` with a 30s timeout and prints MySQL `processlist` + `metadata_locks`. Observed output during the hang (verbatim from a 3.2.1 run): ``` === processlist === id command secs state query 34 Query 16 Waiting for table metadata lock DROP TABLE _airflow_deleted__dag_version__20260430191651 35 Sleep 16 NULL ← failed-DELETE conn, locks held === metadata locks === TABLE airflow dag_version SHARED_WRITE GRANTED thread 662 TABLE airflow _airflow_deleted__dag_version__... SHARED_READ GRANTED thread 662 TABLE airflow _airflow_deleted__dag_version__... EXCLUSIVE PENDING thread 661 ← DROP, blocked ``` ### What you think should happen instead? The CLI should **fail fast and clearly** when it can't delete, not deadlock silently. A minimal two-line fix: ```python except BaseException: session.rollback() raise finally: if target_table is not None and skip_archive: target_table.drop(bind=session.connection()) session.commit() ``` I patched a live 3.2.1 install with this and re-ran the same reproducer: | Behaviour | Unpatched 3.2.1 | With two-line fix | |---|---|---| | Outcome | hangs forever | **fails in ~1 s** with `IntegrityError 1451` | | Leftover archive tables in DB | 1 (orphan) | **0** | | Other tables in same `db clean` run | poisoned session, may also fail | unaffected | This stops the hang and surfaces the real error. It does **not** by itself enable cleanup of FK-pinned `dag_version` rows; doing that needs a separate change to `_build_query` for `dag_version` to exclude rows still referenced by `task_instance.dag_version_id` (and `dag_run.created_dag_version_id`). Without such filtering, `dag_version` cannot be safely included in default `--tables` lists at all — any environment with run history older than the cleanup horizon will trip this. A diff of the proposed fix is in [`patch/db_cleanup_fix.diff`](https://github.com/diogosilva30/airflow-db-clean-dag-version-deadlock/blob/main/patch/db_cleanup_fix.diff) of the reproducer repo. ### Operating System Reproduced on macOS 14 (Docker Desktop) and Linux (GKE node, Container-Optimized OS) — not OS-specific; the hang is a MySQL metadata-lock issue. ### Deployment Other 3rd-party Helm chart ### Apache Airflow Provider(s) mysql ### Versions of Apache Airflow Providers ``` apache-airflow-providers-mysql==6.4.0 apache-airflow-providers-standard==1.7.0 ``` (Both versions resolved via the official `constraints-3.2.1` / `constraints-3.1.8` files for Python 3.12.) ### Official Helm Chart version Not Applicable ### Kubernetes Version Not Applicable (reproduces directly against MySQL; production hit was on GKE 1.30, but unrelated) ### Helm Chart configuration Not Applicable ### Docker Image customizations Not Applicable — bug reproduces on a clean `pip install apache-airflow==3.2.1` against an unmodified `mysql:8.4`. ### Anything else? - Reproduces 100% of the time on **3.1.8** and **3.2.1** when an old `dag_version` is FK-pinned by a `task_instance`. - Does not reproduce on Postgres (Postgres releases the failed-statement locks differently and the same-connection vs. pool issue doesn't manifest as MDL deadlock; not yet verified whether a different failure mode exists there). - The orphan `_airflow_deleted__dag_version__YYYYMMDD...` archive tables left behind after a forced kill are easy to spot and can be dropped manually. - Operational mitigation users can apply today: exclude `dag` and `dag_version` from `airflow db clean --tables` until this is fixed. ### Are you willing to submit PR? - [x] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
