You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "GyuhoonK (via GitHub)" <gi...@apache.org> on 2026/08/28 08:27:04 UTC

[I] Scheduler crash-loops on MySQL-compatible backends without CTE-in-DML support (e.g. Vitess): asset orphanage DELETE uses WITH [airflow]

GyuhoonK opened a new issue, #72187:
URL: https://github.com/apache/airflow/issues/72187

   ### Apache Airflow version
   
   3.3.0 (also present in 3.3.1 and current `main`)
   
   ### What happened?
   
   `SchedulerJobRunner._update_asset_orphanage` builds its asset reference-count query as a CTE (`.cte()`) and hands it to `_orphan_unreferenced_assets`, which executes:
   
   ```sql
   WITH anon_1 AS (
     SELECT asset.id, asset.name, asset.uri, ...
     FROM asset
     LEFT OUTER JOIN dag_schedule_asset_reference ON ...
     LEFT OUTER JOIN task_outlet_asset_reference ON ...
     LEFT OUTER JOIN task_inlet_asset_reference ON ...
     GROUP BY asset.id
     HAVING count(...) + count(...) + count(...) = %s
   )
   DELETE FROM asset_active WHERE EXISTS (SELECT * FROM anon_1 WHERE asset_active.name = anon_1.name AND asset_active.uri = anon_1.uri)
   ```
   
   MySQL-compatible backends whose query planner does not support CTEs in DML statements reject this at plan time. On Vitess (and PlanetScale, which is built on Vitess) this fails with:
   
   ```
   sqlalchemy.exc.NotSupportedError: (MySQLdb.NotSupportedError)
   (1235, 'VT12001: unsupported: WITH expression in DELETE statement')
   ```
   
   Because `_update_asset_orphanage` runs on the scheduler-loop timer (`[scheduler] parsing_cleanup_interval`, default 60s), the exception propagates out of `_run_scheduler_loop` and the scheduler crash-loops every ~60 seconds. CTEs in DML are unsupported in every Vitess release to date (SELECT-side CTEs landed in v19/v21, but DML has none as of v24.0.2), so there is no backend-side upgrade path.
   
   ### What you think should happen instead?
   
   The CTE is not semantically required here: `orphan_query` and `activate_query` are each consumed by a single statement, so a plain derived-table subquery (`.subquery()`) is equivalent. Compiled against the MySQL dialect that produces:
   
   ```sql
   DELETE FROM asset_active WHERE EXISTS (SELECT * FROM (SELECT ... GROUP BY ... HAVING ...) AS anon_1 WHERE ...)
   ```
   
   which the Vitess planner accepts. The DELETE target (`asset_active`) is not referenced inside the subquery, so MySQL's "can't delete from a table referenced in a subquery" restriction does not apply either. This is a small compatibility widening in the spirit of #40349 (dialect-compat fix for the same method on Postgres).
   
   I understand Vitess is not an officially supported metadata backend — this proposal does not add support for it, it just removes an unnecessary SQL construct that blocks an otherwise MySQL-compatible family of backends (similar planner limits have bitten other generic tools, e.g. prisma/studio#1398, Metabase in planetscale/discussion#161).
   
   ### How to reproduce
   
   1. Point `[database] sql_alchemy_conn` at any Vitess keyspace (`mysql+mysqldb://...:.../keyspace?charset=utf8mb4`), e.g. a PlanetScale database.
   2. Run `airflow db migrate` (passes) and start the scheduler.
   3. Wait one `parsing_cleanup_interval` (60s) — the scheduler exits with the traceback above.
   
   ### Are you willing to submit PR?
   
   Yes — a PR replacing the two `.cte()` calls with `.subquery()` (plus the corresponding type hints) is ready.
   
   ### Anything else?
   
   Airflow 2.x is unaffected (the dataset-orphan path there does not use a CTE in DML).
   


-- 
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: commits-unsubscribe@airflow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Scheduler crash-loops on MySQL-compatible backends without CTE-in-DML support (e.g. Vitess): asset orphanage DELETE uses WITH [airflow]

Posted by "boring-cyborg[bot] (via GitHub)" <gi...@apache.org>.
boring-cyborg[bot] commented on issue #72187:
URL: https://github.com/apache/airflow/issues/72187#issuecomment-5450241549

   Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.
   


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Scheduler crash-loops on MySQL-compatible backends without CTE-in-DML support (e.g. Vitess): asset orphanage DELETE uses WITH [airflow]

Posted by "shahar1 (via GitHub) via commits" <co...@airflow.apache.org>.
shahar1 commented on issue #72187:
URL: https://github.com/apache/airflow/issues/72187#issuecomment-6687092213

   Solved by https://github.com/apache/airflow/pull/75404


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] Scheduler crash-loops on MySQL-compatible backends without CTE-in-DML support (e.g. Vitess): asset orphanage DELETE uses WITH [airflow]

Posted by "shahar1 (via GitHub) via commits" <co...@airflow.apache.org>.
shahar1 closed issue #72187: Scheduler crash-loops on MySQL-compatible backends without CTE-in-DML support (e.g. Vitess): asset orphanage DELETE uses WITH
URL: https://github.com/apache/airflow/issues/72187


-- 
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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org