`airflow db downgrade --to-version=2.10.5` fails, claiming NULLs that don't actually exist

[Chais]

Apache Airflow version

Other Airflow 2/3 version (please specify below)

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

3.0.2

What happened?

I wanted to upgrade our 2.10.5 to 2.11.0 but neglected to add --version 1.16.0 to helm upgrade, which resulted in it using the 1.18.0 chart. It ran the DB migrations to 3.0.2.
I'm now stuck trying to downgrade the DB back to 2.10.5, but get the following error:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) column "task_instance_id" of relation "task_instance_history" contains null values

[SQL: ALTER TABLE task_instance_history ADD COLUMN task_instance_id UUID NOT NULL]

which would be fine if it were actually true. I went into the shell to check:

airflow_db=> select count(*) from task_instance_history;
 count 
-------
  4450
(1 row)

airflow_db=> select count(*) from task_instance_history where task_instance_id is null;
 count 
-------
     0
(1 row)

airflow_db=> select count(*) from task_instance_history where task_instance_id is not null;
 count 
-------
  4450
(1 row)

So I'm not sure what Alembic is complaining about.

What you think should happen instead?

The migration 2 → 3 worked fine, so the reverse should work, as well.

How to reproduce

I don't think k8s has anything to do with this, so airflow standalone should do the trick. Just set up a 2.10.5 and upgrade to 3.0.2. A postgres DB might be necessary, though.
Having some entries in task_instance_history may be helpful.

Operating System

Worker runs Debian 12, host runs CentOS 7

Versions of Apache Airflow Providers

apache-airflow-providers-common-sql==1.27.1
apache-airflow-providers-odbc==4.10.0
apache-airflow-providers-postgres==6.2.0

Deployment

Official Apache Airflow Helm Chart

Deployment details

helm version
version.BuildInfo{Version:"v3.17.2", GitCommit:"cc0bbbd6d6276b83880042c1ecb34087e84d41eb", GitTreeState:"clean", GoVersion:"go1.23.7"}

Extended image:

FROM apache/airflow:slim-2.11.0-python3.12

USER root
RUN apt-get update \
    && apt-get install -y --no-install-recommends \
        libkrb5-dev \
        htop \
    && apt-get autoremove -yqq --purge \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/*

USER airflow
RUN pip install --no-cache-dir python-socks apache-airflow[celery,imap,postgres,samba,sftp,ssh,statsd,tableau]==${AIRFLOW_VERSION} elasticsearch presto-python-client confluent-kafka

USER root

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

[Chais]

The database appears to be in an inconsistent state, too.

airflow_db=> \d task_instance_history

Table "public.task_instance_history"

Column	Type	Collation	Nullable	Default
task_id	character varying(250)		not null
dag_id	character varying(250)		not null
run_id	character varying(250)		not null
map_index	integer		not null	'-1'::integer
try_number	integer		not null
start_date	timestamp with time zone
end_date	timestamp with time zone
duration	double precision
state	character varying(20)
max_tries	integer			'-1'::integer
hostname	character varying(1000)
unixname	character varying(1000)
pool	character varying(256)		not null
pool_slots	integer		not null
queue	character varying(256)
priority_weight	integer
operator	character varying(1000)
custom_operator_name	character varying(1000)
queued_dttm	timestamp with time zone
queued_by_job_id	integer
pid	integer
executor	character varying(1000)
executor_config	bytea
updated_at	timestamp with time zone
rendered_map_index	character varying(250)
external_executor_id	character varying(250)
trigger_id	integer
trigger_timeout	timestamp without time zone
next_method	character varying(1000)
next_kwargs	json
task_display_name	character varying(2000)
dag_version_id	uuid
scheduled_dttm	timestamp with time zone
task_instance_id	uuid		not null
context_carrier	jsonb
span_status	character varying(250)		not null	'not_started'::character varying

Indexes:
"task_instance_history_pkey" PRIMARY KEY, btree (task_instance_id)
"idx_tih_dag_run" btree (dag_id, run_id)
"task_instance_history_dtrt_uq" UNIQUE CONSTRAINT, btree (dag_id, task_id, run_id, map_index, try_number)
Foreign-key constraints:
"task_instance_history_ti_fkey" FOREIGN KEY (dag_id, task_id, run_id, map_index) REFERENCES task_instance(dag_id, task_id, run_id, map_index) ON UPDATE CASCADE ON DELETE CASCADE

As you see I have the column task_instance_id, which was added in version 60, if I'm reading that correctly. However, I'm lacking the try_id column, which was added in the same version.
I really hope I can salvage this without data loss.

[Chais]

Also possibly related to #54071

[Chais]

I was able to fix this by manually applying the patch from #54508.