jimchan-glitch opened a new issue, #73218:
URL: https://github.com/apache/airflow/issues/73218
### Under which category would you file this issue?
Providers
### Apache Airflow version
3.3.0 (apache-airflow-providers-fab 3.7.1)
### What happened and how to reproduce it?
FAB migration `02ca36b0235b` ("Fix fab db inconsistencies",
`0001_3_5_0_fix_fab_db_inconsistencies.py`) cannot execute against MySQL.
Its MySQL path
builds a **multi-statement** SQL string and passes it to a single
`op.execute()`. MySQL
rejects multiple statements in one query unless the connection sets
`CLIENT_MULTI_STATEMENTS`, which Airflow's SQLAlchemy/pymysql connection
does not.
The helper at fault:
```python
def _mysql_run_procedure(procedure_name: str, body: str) -> str:
return f"""
DROP PROCEDURE IF EXISTS {procedure_name};
CREATE PROCEDURE {procedure_name}()
BEGIN
{body}
END;
CALL {procedure_name}();
DROP PROCEDURE IF EXISTS {procedure_name};
"""
```
Five procedures route through it — `CreateIdxPermissionViewId`,
`CreateIdxRoleId`,
`DropEmailUqIfExists`, `DropUniqueIfExists`, `DropIndexIfExists` — each
executed as
`op.execute(sa.text(...))`. It fails at the second statement, every time:
```
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax;
... near
'CREATE PROCEDURE DropEmailUqIfExists() BEGIN ... DECLARE done INT D' at
line 2")
```
**Reproduce:** run `airflow db migrate` against a MySQL metadata database on
any version
where this revision is unapplied. It is the head of the FAB chain
(`None → 6709f7a774b9 → 02ca36b0235b`).
Notably this bites hardest on an **Airflow 2.x → 3.x upgrade**. Under 2.x,
FAB's tables were
managed by core's single `alembic_version` chain and the provider had no
separately-tracked
branch — a 2.x metadata DB has no `alembic_version_fab` table at all.
Airflow 3 promoted FAB
to a provider owning its own branch, so the upgrade applies the **entire**
FAB chain from
scratch and hits this revision regardless of which fab version was
previously installed.
### Operating System
Debian (official Airflow image), on Kubernetes
### Versions of Apache Airflow Providers
apache-airflow-providers-fab 3.7.1
The migration file is **byte-identical in 3.7.1 and 3.8.0** (sha256 prefix
`33b3527fd9ffa919`), so upgrading the provider does not avoid it.
### Deployment
Official Apache Airflow Helm Chart
### Deployment details
MySQL metadata database. Migrations run via the chart's
`run-airflow-migrations`
post-upgrade hook.
### Anything else?
**Impact.** This blocks `helm upgrade` entirely, and the surfaced error is
actively
misleading. Helm only watches the Job object, so it reports:
```
Error: UPGRADE FAILED: post-upgrade hooks failed: 1 error occurred:
* timed out waiting for the condition
```
Nothing indicates a SQL error. Diagnosis requires knowing to read the
`<release>-run-airflow-migrations` pod logs. New pods sit in `Init:*`
indefinitely because
`wait-for-airflow-migrations` never clears.
**Retrying does not help** — the Job auto-retried 5 times with identical
failures. The error
is deterministic.
**MySQL DDL is non-transactional**, so partial application occurs: some
index/FK changes
land while the revision stays unstamped, and the exact partial state differs
per database.
Any fix should be written to tolerate a partially-applied starting state.
**Suggested fix.** Execute the statements individually rather than as one
batched string —
e.g. split `_mysql_run_procedure` into separate `op.execute()` calls per
statement, or drop
the stored-procedure approach and guard the DDL with `information_schema`
checks in Python.
We worked around it by completing the migration by hand and stamping
`alembic_version_fab`,
but that is per-database remediation for something that will recur for every
MySQL user
crossing this revision.
### 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]
