This is an automated email from the ASF dual-hosted git repository.
kaxilnaik pushed a commit to branch v3-0-test
in repository https://gitbox.apache.org/repos/asf/airflow.git
The following commit(s) were added to refs/heads/v3-0-test by this push:
new b41a5d51b62 Fix downgrade migrations failures (#54508)
b41a5d51b62 is described below
commit b41a5d51b62e86dd2e1c4fb6e378e3c79ff81328
Author: Rahul Vats <[email protected]>
AuthorDate: Thu Aug 14 23:14:42 2025 +0530
Fix downgrade migrations failures (#54508)
Fixes critical blocking issues when downgrading Airflow from 3.x to 2.x
across PostgreSQL, MySQL, and SQLite databases. These issues were discovered
after [PR #54399](https://github.com/apache/airflow/pull/54399) unblocked the
downgrade process.
**Error:**
```
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation)
column "try_number" of relation "task_reschedule" contains null values
```
**Root Cause:** Migration
[`0068_3_0_0_ti_table_id_unique_per_try.py:L99`](https://github.com/apache/airflow/blob/main/airflow-core/src/airflow/migrations/versions/0068_3_0_0_ti_table_id_unique_per_try.py#L99)
uses `default="1"` instead of `server_default="1"`, causing existing NULL
values to remain NULL when the column is made NOT NULL.
**Solution:** Replace `default="1"` with `server_default="1"` to ensure
database-level default value assignment.
**Error:**
```
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation)
column "task_instance_id" of relation "task_instance_history" contains null
values
```
**Root Cause:** The `task_instance_id` column is made NOT NULL during
downgrade but contains NULL values, even though this column gets dropped
entirely in the same migration.
**Solution:** Make `task_instance_id` column nullable since it's
immediately dropped and not needed in 2.x schema.
**Error:**
```
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1138, 'Invalid
use of NULL value')
```
**Root Cause:** MySQL query attempts to JOIN on NULL `id` values in
`task_instance_history`:
```sql
UPDATE task_instance_history tih
JOIN (
SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num
FROM task_instance_history
) AS temp ON tih.id = temp.id
SET tih.id = temp.row_num;
```
The `id` column was just re-added as nullable with all NULL values, making
the JOIN fail.
**Solution:** Replace with MySQL variable-based sequential numbering:
```sql
SET @row_number = 0;
UPDATE task_instance_history
SET id = (@row_number := @row_number + 1)
ORDER BY try_id;
```
**Error:**
```
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY
constraint failed
```
**Root Cause:** SQLite's strict foreign key checking prevents dropping the
`dag` table during `batch_alter_table` operations when other tables reference
it.
**Solution:** Add SQLite-specific handling to temporarily disable foreign
key constraints:
```python
if dialect_name == "sqlite":
conn.execute(text("PRAGMA foreign_keys=OFF"))
try:
# batch operations
finally:
conn.execute(text("PRAGMA foreign_keys=ON"))
```
Verified successful downgrade from Airflow 3.0.5rc1 to 2.11 on:
- [x] PostgreSQL
- [x] MySQL
- [x] SQLite
(cherry picked from commit bc18493d64edda6ecc3aebd9013a7d1355fa63d6)
---
airflow-core/docs/img/airflow_erd.sha256 | 2 +-
.../migrations/versions/0060_3_0_0_add_try_id_to_ti_and_tih.py | 10 ++++------
.../versions/0068_3_0_0_ti_table_id_unique_per_try.py | 4 ++--
3 files changed, 7 insertions(+), 9 deletions(-)
diff --git a/airflow-core/docs/img/airflow_erd.sha256
b/airflow-core/docs/img/airflow_erd.sha256
index 076b590dedd..9ede5601f58 100644
--- a/airflow-core/docs/img/airflow_erd.sha256
+++ b/airflow-core/docs/img/airflow_erd.sha256
@@ -1 +1 @@
-47bc0fb61cdb68be06d02c7f7d88bf39c0d454bd23e9ad924e218999c40fc121
\ No newline at end of file
+9650b3ecb443b35b5626dede72e6235683f616f16c8c8f5ab113fa08d1a27ff7
\ No newline at end of file
diff --git
a/airflow-core/src/airflow/migrations/versions/0060_3_0_0_add_try_id_to_ti_and_tih.py
b/airflow-core/src/airflow/migrations/versions/0060_3_0_0_add_try_id_to_ti_and_tih.py
index 9263748f5fd..82a80cc8903 100644
---
a/airflow-core/src/airflow/migrations/versions/0060_3_0_0_add_try_id_to_ti_and_tih.py
+++
b/airflow-core/src/airflow/migrations/versions/0060_3_0_0_add_try_id_to_ti_and_tih.py
@@ -143,12 +143,10 @@ def downgrade():
elif dialect_name == "mysql":
op.execute(
"""
- UPDATE task_instance_history tih
- JOIN (
- SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num
- FROM task_instance_history
- ) AS temp ON tih.id = temp.id
- SET tih.id = temp.row_num;
+ SET @row_number = 0;
+ UPDATE task_instance_history
+ SET id = (@row_number := @row_number + 1)
+ ORDER BY try_id;
"""
)
else:
diff --git
a/airflow-core/src/airflow/migrations/versions/0068_3_0_0_ti_table_id_unique_per_try.py
b/airflow-core/src/airflow/migrations/versions/0068_3_0_0_ti_table_id_unique_per_try.py
index 80a3e252745..da67f0e4f9e 100644
---
a/airflow-core/src/airflow/migrations/versions/0068_3_0_0_ti_table_id_unique_per_try.py
+++
b/airflow-core/src/airflow/migrations/versions/0068_3_0_0_ti_table_id_unique_per_try.py
@@ -96,7 +96,7 @@ def downgrade():
dialect_name = conn.dialect.name
with op.batch_alter_table("task_reschedule", schema=None) as batch_op:
batch_op.add_column(
- sa.Column("try_number", sa.INTEGER(), autoincrement=False,
nullable=False, default=1)
+ sa.Column("try_number", sa.INTEGER(), autoincrement=False,
nullable=False, server_default="1")
)
with op.batch_alter_table("task_instance_note", schema=None) as batch_op:
@@ -117,5 +117,5 @@ def downgrade():
# (and on non sqlite batching isn't "a thing", it issue alter tables fine)
with op.batch_alter_table("task_instance_history", schema=None) as
batch_op:
batch_op.add_column(
- sa.Column("task_instance_id", UUIDType(binary=False),
autoincrement=False, nullable=False)
+ sa.Column("task_instance_id", UUIDType(binary=False),
autoincrement=False, nullable=True)
)
