database.sql_alchemy_schema setting is ignored in some cases

[okorienev]

Environment:
Apache Airflow version: 2.4.2, installed from PyPI
PostgreSQL version: 13.5.1 (docker-compose)

Airflow config:

AIRFLOW__CORE__EXECUTOR: CeleryExecutor
AIRFLOW__CORE__LOAD_EXAMPLES: False
AIRFLOW__DATABASE__SQL_ALCHEMY_CONN: 'postgresql://user@localhost:5433/postgres'
AIRFLOW__DATABASE__SQL_ALCHEMY_SCHEMA: data_sources_airflow

Postgres container env:

environment:
  - POSTGRES_DB=postgres
  - POSTGRES_USER=user
  - POSTGRES_PASSWORD=
  - POSTGRES_HOST_AUTH_METHOD=trust

Commands to init airflow:

airflow db init
airflow users create \
  --firstname admin \
  --lastname admin \
  --username airflow \
  --password airflow \
  --role Admin \
  --email [email protected]

Problems faced:
Unable to run any DAG:
After running the commands above I was able to access Airflow UI, sign in using a newly-created user & see the list of imported DAGs. But if I try to run any task, I get the following error from the celery worker:

 ERROR/ForkPoolWorker-15] [4ebf03e4-ea1d-4661-b427-f07fb00cc624] Failed to execute task No log_template entry found for ID None. Please make sure you set up the metadatabase correctly..

In the web server logs I saw the following lines:

[2022-10-27 13:09:06,451] {db.py:843} INFO - Log template table does not exist (added in 2.3.0); skipping log template sync.

For me, it looks like

airflow/airflow/utils/db.py

Line 848 in 5e6cec8

def log_template_exists():

is looking to the public schema rather than data_sources_airflow, at least when running as part of

airflow/airflow/utils/cli.py

Line 60 in 5e6cec8

def action_cli(func=None, check_db=True):

Some tables were created in the wrong schema
These tables were created in public rather than then in data_sources_airflow

• session table
• alembic_version table
• Celery result backend tables. I understand that celery is not a core part of Airflow but I think it would be convenient to use the same schema if using the same database URI

Other tables were created in the required schema

Webserver tries to re-create some Security DB after each restart despite users & roles work normally

[2022-10-28 15:29:09,599] {manager.py:103} INFO - Security DB not found Creating all Models from Base
[2022-10-28 15:29:09,599] {manager.py:103} INFO - Security DB not found Creating all Models from Base
[2022-10-28 15:29:09,607] {manager.py:103} INFO - Security DB not found Creating all Models from Base
[2022-10-28 15:29:09,617] {manager.py:103} INFO - Security DB not found Creating all Models from Base
[2022-10-28 15:29:09,862] {manager.py:105} INFO - Security DB Created
[2022-10-28 15:29:09,875] {manager.py:105} INFO - Security DB Created
[2022-10-28 15:29:09,904] {manager.py:105} INFO - Security DB Created
[2022-10-28 15:29:09,919] {manager.py:105} INFO - Security DB Created

Am I doing something wrong?
P.S. If I try to use Airflow 2.3, I get an error similar to #18836

[potiuk]

Sounds like a real bug - can you please open an issue for it?

[okorienev]

Thanks for the quick response!
I've created #27348.
Could You please take a look and say if I've missed some required info or should add some details?

[potiuk]

You will get feedback when someone looks at it - no worries :)

[prowler0305]

I have run into this same issue today with version 2.6.0 and it is still creating those mentioned tables in the public schema and not in my configured to airflow DEV schema.

What was the resolution to this if there was one?

[okorienev]

#27348 (comment)

setting ?options=-csearch_path=<schema_name> fixed the problem for me

[prowler0305]

thank you. Yeah after I posted that I saw the link to that discussion as well. That worked for me as well. However the alembic_version table and the session table is still getting created in the public schema. Is that normal?

[okorienev]

Unfortunately I don't remember and don't have access to that particular codebase anymore