Failure while downgrading from v3.0.0 to v2.x.x (Revision ID: 29ce7909c52b)

[MASTREX]

Apache Airflow version

main (development)

If "Other Airflow 2 version" selected, which one?

No response

What happened?

airflow/airflow-core/src/airflow/migrations/versions/0068_3_0_0_ti_table_id_unique_per_try.py

Line 120 in a1aa273

sa.Column("task_instance_id", UUIDType(binary=False), autoincrement=False, nullable=False)

While downgrading my airflow version from v3.0.0 to v2.10.4 I have facing an error on line
ALTER TABLE task_instance_history ADD COLUMN task_instance_id UUID NOT NULL

This mapped to above code which seem to add a column with not null property

This has failed as the table already contains some data and adding a new column with no data is not possible

(Sorry cant include any log or screenshot as it happened on my work laptop)

What you think should happen instead?

Downgrade to be reversible and symmetrical

How to reproduce

Do the downgrade when data exist in task_instance_history table

Operating System

rhel9

Versions of Apache Airflow Providers

(probably not required)

Deployment

Docker-Compose

Deployment details

No response

Anything else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg]

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.

[MASTREX]

Additional thing to note:
In the upgrade part we are losing actual data here

airflow/airflow-core/src/airflow/migrations/versions/0068_3_0_0_ti_table_id_unique_per_try.py

Line 54 in a1aa273

batch_op.drop_column("task_instance_id")

[vatsrahul1001]

@MASTREX can you verify this again should have been fixed by #54508

[MASTREX]

I think you did not understood what I was trying to say

Here is the AI explanation

The problem lies in the handling of the task_instance_id column.

• Upgrade: The original task_instance_id column is deleted, and a different column (try_id) is renamed to task_instance_id. This means the data that was in the original task_instance_id is permanently lost.

• Downgrade: The downgrade script assumes it can just rename the newly named task_instance_id back to try_id and then add a new task_instance_id column. The data from the original task_instance_id is not recovered because it was dropped in the upgrade.

This will lead to an inconsistency. If a user runs the upgrade and then the downgrade, the task_instance_history table will have a different set of columns and potentially different data than it had before the upgrade. The original task_instance_id column and its data are gone forever.

To avoid inconsistency and failure, the upgrade and downgrade scripts should be designed to be symmetrical and reversible.

[MASTREX]

What I can see in your PR is you have set nullable=True which on downgrade will create a empty column. This will just resolve the error, its not the solution.

This should be good enough if we only care about the schema during the upgrade and downgrade and have no requirement for the data in task_instance_history table

[MASTREX]

What I would suggest instead is to keep the data in some backup column and move it along with some minor version upgrades until there is no need to downgrade. And till then we can show a warning that this data will be deleted at some 'future' version and downgrade to v2.x will not be possible

something like that 😄