Airflow db migrate AttributeError: 'Session' object has no attribute 'scalars'

[aipatr]

Apache Airflow version

2.7.0

What happened

I try to execute airflow db migrate

/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/configuration.py:751 UserWarning: Config scheduler.max_tis_per_query (value: 512) should NOT be greater than core.parallelism (value: 32). Will now use core.parallelism as the max task instances per query instead of specified value.
/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/configuration.py:857 FutureWarning: The 'log_id_template' setting in [elasticsearch] has the old default value of '{dag_id}-{task_id}-{execution_date}-{try_number}'. This value has been changed to '{dag_id}-{task_id}-{run_id}-{map_index}-{try_number}' in the running config, but please update your config before Apache Airflow 3.0.
/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/cli/cli_config.py:974 DeprecationWarning: The namespace option in [kubernetes] has been moved to the namespace option in [kubernetes_executor] - the old setting has been used, but please update your config.
DB: postgresql+psycopg2://airflow:***@localhost/airflow
Performing upgrade to the metadata database postgresql+psycopg2://airflow:***@localhost/airflow
Traceback (most recent call last):
  File "/opt/miniconda3/envs/pytorch/bin/airflow", line 8, in <module>
    sys.exit(main())
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/__main__.py", line 60, in main
    args.func(args)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/cli/cli_config.py", line 49, in command
    return func(*args, **kwargs)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/utils/cli.py", line 113, in wrapper
    return f(*args, **kwargs)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/utils/providers_configuration_loader.py", line 56, in wrapped_function
    return func(*args, **kwargs)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/cli/commands/db_command.py", line 104, in migratedb
    db.upgradedb(
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/utils/session.py", line 77, in wrapper
    return func(*args, session=session, **kwargs)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/utils/db.py", line 1616, in upgradedb
    for err in _check_migration_errors(session=session):
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/utils/db.py", line 1499, in _check_migration_errors
    yield from check_fn(session=session)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/utils/db.py", line 979, in check_conn_id_duplicates
    dups = session.scalars(
AttributeError: 'Session' object has no attribute 'scalars'

What you think should happen instead

No response

How to reproduce

Execute airflow db migrate with PostgresSql existing database

Operating System

Ubuntu

Versions of Apache Airflow Providers

No response

Deployment

Other

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

[Taragolis]

@aipatr which version of sqlalchemy you have installed in Airflow environment? I guess it less then 1.4.24

Seems like you install Airflow into virtual environment, just wondering did you follow Installation from PyPI?

[potiuk]

Yeah. Constraints woudl work as @Taragolis mentione.. Also manually bumping sqlalchemy should help. However we shoudl indeed have a minimum version set if we are relying on sesion.scalar added in 1.4.24, Added it in #33892

[aipatr]

After upgrading sqlalchemy to 1.4.24 I get the following error.
I would strongly suggest that you add in the guide for pip that you wrote a section regarding UPDATE EXISTING AIRFLOW INSTALLATION IN VIRTUAL ENVIRONMENT:
To make it work I needed to run the following command:
pip install apache-airflow==2.7.0 --upgrade --constraint "https://raw.githubusercontent.com/apache/airflow/constraints-2.7.0/constraints-3.8.txt"

/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/configuration.py:751 UserWarning: Config scheduler.max_tis_per_query (value: 512) should NOT be greater than core.parallelism (value: 32). Will now use core.parallelism as the max task instances per query instead of specified value.
/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/configuration.py:857 FutureWarning: The 'log_id_template' setting in [elasticsearch] has the old default value of '{dag_id}-{task_id}-{execution_date}-{try_number}'. This value has been changed to '{dag_id}-{task_id}-{run_id}-{map_index}-{try_number}' in the running config, but please update your config before Apache Airflow 3.0.
/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/cli/cli_config.py:974 DeprecationWarning: The namespace option in [kubernetes] has been moved to the namespace option in [kubernetes_executor] - the old setting has been used, but please update your config.
DB: postgresql+psycopg2://airflow:***@localhost/airflow
Performing upgrade to the metadata database postgresql+psycopg2://airflow:***@localhost/airflow
[2023-08-30T07:37:15.944+0000] {migration.py:164} INFO - Context impl PostgresqlImpl.
[2023-08-30T07:37:15.944+0000] {migration.py:167} INFO - Will assume transactional DDL.
[2023-08-30T07:37:15.956+0000] {db.py:1633} INFO - Creating tables
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade c804e5c76e3e -> 937cbd173ca1, Add index to task_instance table
Traceback (most recent call last):
  File "/opt/miniconda3/envs/pytorch/bin/airflow", line 8, in <module>
    sys.exit(main())
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/__main__.py", line 60, in main
    args.func(args)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/cli/cli_config.py", line 49, in command
    return func(*args, **kwargs)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/utils/cli.py", line 113, in wrapper
    return f(*args, **kwargs)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/utils/providers_configuration_loader.py", line 56, in wrapped_function
    return func(*args, **kwargs)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/cli/commands/db_command.py", line 104, in migratedb
    db.upgradedb(
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/utils/session.py", line 77, in wrapper
    return func(*args, session=session, **kwargs)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/utils/db.py", line 1640, in upgradedb
    command.upgrade(config, revision=to_revision or "heads")
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/command.py", line 294, in upgrade
    script.run_env()
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/script/base.py", line 490, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 97, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/migrations/env.py", line 117, in <module>
    run_migrations_online()
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/migrations/env.py", line 111, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/runtime/environment.py", line 813, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/runtime/migration.py", line 561, in run_migrations
    step.migration_fn(**kw)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/airflow/migrations/versions/0126_2_7_0_add_index_to_task_instance_table.py", line 40, in upgrade
    op.create_index(
  File "<string>", line 8, in create_index
  File "<string>", line 3, in create_index
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/operations/ops.py", line 829, in create_index
    return operations.invoke(op)
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/operations/base.py", line 354, in invoke
    return fn(self, operation)
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/operations/toimpl.py", line 88, in create_index
    operations.impl.create_index(idx)
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/ddl/impl.py", line 300, in create_index
    self._exec(schema.CreateIndex(index))
  File "/home/sysgen/.local/lib/python3.8/site-packages/alembic/ddl/impl.py", line 146, in _exec
    return conn.execute(construct, multiparams)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/future/engine.py", line 280, in execute
    return self._execute_20(
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1611, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 77, in _execute_on_connection
    return connection._execute_ddl(
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1375, in _execute_ddl
    compiled = ddl.compile(
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 489, in compile
    return self._compiler(dialect, **kw)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 29, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 454, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 489, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
    return meth(self, **kw)
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2626, in visit_create_index
    inclusions = [
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/base.py", line 2627, in <listcomp>
    index.table.c[col]
  File "/opt/miniconda3/envs/pytorch/lib/python3.8/site-packages/sqlalchemy/sql/base.py", line 1196, in __getitem__
    return self._index[key]
KeyError: 'start_date'