Inconsistent Database Resolution Order sql_alchemy_conn vs sql_alchemy_conn_secret

[theis188]

Apache Airflow version

2.10.3

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

No response

What happened?

The Airflow scheduler does not choose consistently between sql_alchemy_conn and sql_alchemy_conn_secret. If both are set and point to different databases, this can cause errors.

For example, DAGs populated into the sql_alchemy_conn connection, but when trying to run a TriggerDagRunOperator, it would run into a bug because it would apparently connect through sql_alchemy_conn_secret and run into database errors (DAG not found etc).

These settings were misconfigured on my side, but a consistent resolution order would help to surface these misconfigurations.

What you think should happen instead?

There should be a consistent resolution order. Probably sql_alchemy_conn should be preferred, then the other options _cmd, then _secret, but the exact order would be less important than having it be consistent.

How to reproduce

Set sql_alchemy_conn and sql_alchemy_conn_secret to point to different databases.

In my case, sql_alchemy_conn_secret database was upgraded to a lower version (2.8.1) and the schema difference caused errors.

Try to trigger a DAG through TriggerDagRunOperator, eg:

import datetime
from airflow import DAG
from airflow.operators.trigger_dagrun import TriggerDagRunOperator
from airflow.operators.empty import EmptyOperator

with DAG(
    "my_automation_dag",
    start_date=datetime.datetime(year=2024, day=1, month=1),
) as dag:
    task = TriggerDagRunOperator(
        task_id="trigger_new_task",
        trigger_dag_id="my_task_dag",
    )

with DAG(
    "my_task_dag",
    start_date=datetime.datetime(year=2024, day=1, month=1),
) as task_dag:
    empty_task = EmptyOperator(
        task_id="my_empty_task"
    )

The DAGs will populate to the sql_alchemy_conn database but TriggerDagRunOperator should fail.

You might get the same error as I did, about a missing column, task_instance.task_display_name.

Operating System

Debian GNU/Linux 12 (bookworm)

Versions of Apache Airflow Providers

apache-airflow-providers-amazon==8.23.0
apache-airflow-providers-cncf-kubernetes==7.14.0
apache-airflow-providers-common-compat==1.2.2
apache-airflow-providers-common-io==1.4.2
apache-airflow-providers-common-sql==1.20.0
apache-airflow-providers-fab==1.5.1
apache-airflow-providers-ftp==3.11.1
apache-airflow-providers-http==4.13.3
apache-airflow-providers-imap==3.7.0
apache-airflow-providers-postgres==5.14.0
apache-airflow-providers-slack==8.9.2
apache-airflow-providers-smtp==1.8.1
apache-airflow-providers-sqlite==3.9.1

Deployment

Virtualenv installation

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

[potiuk]

I seriously doubt this is a problem Airflow uses the same resolution mechanism in consistent order.

Most likely what was different is that you had DIFFERENT configuration in two places where things were running. For example in worker you did not have SECRET configured. This is impossible expectation to use a configuration that you have no access to.

Generally speaking the expectation is that it's on yoy (deployment manager) to make sure your configuratoin is consistently set on all your nodes and in all processes of Airflow. That's very basic assumption of airflow environment documented in https://airflow.apache.org/docs/apache-airflow/stable/configurations-ref.html#configuration-reference

Use the same configuration across all the Airflow components. While each component does not require all, some configurations need to be same otherwise they would not work as expected. A good example for that is secret_key which should be same on the Webserver and Worker to allow Webserver to fetch logs from Worker.

Note that this is changing in Airflow 3, because the "workers" will no longer have database access and they wil not require most of configuration so the configuration for sql_alchemy_conn will only be needed in scheduler, webserver and triggerer (but it will still need to be consistent across all three components)

Converting it into discussion in case more concrete evidences of the error and inconsistency is on airflow side (but it's practically impossible).