vatsrahul1001 opened a new pull request, #54508: URL: https://github.com/apache/airflow/pull/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. ### 1. PostgreSQL - NOT NULL Violation in `task_reschedule` Table **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. ### 2. PostgreSQL - NOT NULL Violation in `task_instance_history` Table **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. ### 3. MySQL - Invalid NULL Value During Row Numbering **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; ``` ### 4. SQLite - Foreign Key Constraint During Batch Operations **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")) ``` ## Testing Verified successful downgrade from Airflow 3.0.5rc1 to 2.11 on: - [x] PostgreSQL - [x] MySQL - [x] SQLite -- 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]
