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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
