Upgrading from version 2.1.4 to 2.2.0 fails during postgres db upgrade with error The task_instance table has 1392 rows without a corresponding dag_run row. You must manually correct this problem (possibly by deleting the problem rows)

[gbonazzoli]

Apache Airflow version

2.2.0 (latest released)

Operating System

Ubuntu 20.04.3 LTS

Versions of Apache Airflow Providers

apache-airflow-providers-oracle==2.0.1
apache-airflow-providers-ssh==2.1.1

Deployment

Virtualenv installation

Deployment details

No response

What happened

During the the command airflow db upgrade we got this error:

[2021-10-12 06:25:10,721] {db.py:815} ERROR - Automatic migration is not available
[2021-10-12 06:25:10,721] {db.py:817} ERROR - The task_instance table has 1392 rows without a corresponding dag_run row. You must manually correct this problem (possibly by deleting the problem rows).

What you expected to happen

It would be nice if the output of airflow db upgrade is the list of objects' ID that needs to be deleted, in order to preserve future's version consistency.

Or even better the SQL statement to manually remove them.

I tried to identify those rows but it is not a trivial task.

How to reproduce

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

[gbonazzoli]

I found the solution to delete the offending records:

delete FROM task_instance where (execution_date, task_id, dag_id) in (
SELECT task_instance.execution_date,
       task_instance.task_id,
       task_instance.dag_id
FROM task_instance
LEFT JOIN dag_run ON task_instance.dag_id = dag_run.dag_id
AND task_instance.execution_date = dag_run.execution_date
WHERE dag_run.run_id IS NULL
);
commit;

And I successfully migrated to 2.2.0

[potiuk]

Duplicate of #18894