steveahnahn opened a new pull request, #71339: URL: https://github.com/apache/airflow/pull/71339
## Why `airflow db clean` selects `trigger` rows by age alone, with no check for rows still in use. The triggerer already removes every unreferenced trigger on each loop, so an old trigger row that survives is almost always still doing work. Three foreign keys point at `trigger`, and cleaning by age hits all three: | referencing table | ON DELETE | effect of the delete | | --- | --- | --- | | `task_instance` | `CASCADE` | a running deferred task is deleted | | `asset_watcher` | `CASCADE` | an event-driven watcher is deleted | | `callback` | no rule | the delete fails and stops the command | Only the `trigger` rows are copied to the archive table, so the cascade-deleted task instance reaches no archive and `db export-archived` cannot recover it. Asset watchers are created once and reused, so their `created_date` ages past any retention window and every clean removes them until the next Dag parse recreates them. Verified on Postgres. Four old triggers, three of them in use, and the cleanup reports `Found 4 rows meeting deletion criteria`: | | before | after | | --- | --- | --- | | deferred `task_instance` rows | 0 | 1 | | `asset_watcher` rows | 0 | 1 | `dag_version` already solves this with `skip_if_referenced` (#68339). This applies the same guard to `trigger`, so only genuinely orphaned rows are purged. ## MySQL `trigger` is a reserved word on MySQL, and the archive step interpolated the table name into `CREATE TABLE ... LIKE` unquoted. That is a syntax error, so the `trigger` table could never be cleaned on MySQL at all. `ProgrammingError` is suppressed per table, so it surfaced only as a warning. The two changes ship together because they share a code path: the archive table is always created, so the reference guard alone would still fail on MySQL. ## Tests Both new tests fail without the change and pass with it. Full file: 74 pass on Postgres, 73 on MySQL, 72 on SQLite. related: #56192 --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes — Claude Code (Opus 5) Generated-by: Claude Code (Opus 5) following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) -- 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]
