Error migrating database to Airflow version 2.9.3 #40928

robinaly · 2024-07-22T09:56:57Z

Apache Airflow version

2.9.3

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

No response

What happened?

When upgrading the database from 2.7.0 or 2.9.2 via airflow db migrate, I get the following error message

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 1949afb29106 -> bff083ad727d, Remove ``idx_last_scheduling_decision`` index on last_scheduling_decision in dag_run table
INFO  [alembic.runtime.migration] Running upgrade bff083ad727d -> 686269002441, Fix inconsistency between ORM and migration files.
Traceback (most recent call last):
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/venv/lib/python3.12/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
             ^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line 825, in _read_query_result
    result.read()
  File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/opt/venv/lib/python3.12/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/opt/venv/lib/python3.12/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'prepare stmt from @var;\n        execute stmt;\n        deallocate prepare stmt' at line 8")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/venv/bin/airflow", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/airflow/__main__.py", line 58, in main
    args.func(args)
  File "/opt/venv/lib/python3.12/site-packages/airflow/cli/cli_config.py", line 49, in command
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/airflow/utils/cli.py", line 114, in wrapper
    return f(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/airflow/utils/providers_configuration_loader.py", line 55, in wrapped_function
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/airflow/cli/commands/db_command.py", line 130, in migratedb
    db.upgradedb(
  File "/opt/venv/lib/python3.12/site-packages/airflow/utils/session.py", line 79, in wrapper
    return func(*args, session=session, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/airflow/utils/db.py", line 1632, in upgradedb
    command.upgrade(config, revision=to_revision or "heads")
  File "/opt/venv/lib/python3.12/site-packages/alembic/command.py", line 406, in upgrade
    script.run_env()
  File "/opt/venv/lib/python3.12/site-packages/alembic/script/base.py", line 582, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/opt/venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 995, in exec_module
  File "<frozen importlib._bootstrap>", line 488, in _call_with_frames_removed
  File "/opt/venv/lib/python3.12/site-packages/airflow/migrations/env.py", line 120, in <module>
    run_migrations_online()
  File "/opt/venv/lib/python3.12/site-packages/airflow/migrations/env.py", line 114, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/opt/venv/lib/python3.12/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/opt/venv/lib/python3.12/site-packages/alembic/runtime/migration.py", line 628, in run_migrations
    step.migration_fn(**kw)
  File "/opt/venv/lib/python3.12/site-packages/airflow/migrations/versions/0142_2_9_2_fix_inconsistency_between_ORM_and_migration_files.py", line 46, in upgrade
    conn.execute(
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/future/engine.py", line 286, in execute
    return self._execute_20(
           ^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/opt/venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/venv/lib/python3.12/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
             ^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line 825, in _read_query_result
    result.read()
  File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/venv/lib/python3.12/site-packages/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/opt/venv/lib/python3.12/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/opt/venv/lib/python3.12/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'prepare stmt from @var;\n        execute stmt;\n        deallocate prepare stmt' at line 8")
[SQL: 
        set @var=if((SELECT true FROM information_schema.TABLE_CONSTRAINTS WHERE
            CONSTRAINT_SCHEMA = DATABASE() AND
            TABLE_NAME        = 'connection' AND
            CONSTRAINT_NAME   = 'unique_conn_id' AND
            CONSTRAINT_TYPE   = 'UNIQUE') = true,'ALTER TABLE connection
            DROP INDEX unique_conn_id','select 1');

        prepare stmt from @var;
        execute stmt;
        deallocate prepare stmt;
        ]
(Background on this error at: https://sqlalche.me/e/14/f405)

What you think should happen instead?

The db migration should successfully finish.

How to reproduce

Start with airflow 2.9.3, an empty mysql database. The initial new creation of new database objects works well.
Now run, for example

airflow db downgrade --to-version=2.9.0 --yes

Run

airflow db migrate

Operating System

Debian GNU/Linux 11 (bullseye)

Versions of Apache Airflow Providers

apache-airflow-providers-amazon==8.26.0
apache-airflow-providers-common-io==1.3.2
apache-airflow-providers-common-sql==1.14.2
apache-airflow-providers-fab==1.2.1
apache-airflow-providers-ftp==3.10.0
apache-airflow-providers-hashicorp==3.7.1
apache-airflow-providers-http==4.12.0
apache-airflow-providers-imap==3.6.1
apache-airflow-providers-smtp==1.7.1
apache-airflow-providers-sqlite==3.8.1

Deployment

Other Docker-based deployment

Deployment details

Mysql version (docker) mysql:8.0.34

Anything else?

Found on staging, error logs come from development environment which seemed identical.

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

The text was updated successfully, but these errors were encountered:

eladkal · 2024-07-22T20:35:22Z

cc @ephraimbuddy I believe you are doing work around migrations ?

ephraimbuddy · 2024-08-01T07:44:06Z

This issue was fixed here: #40314. Instead of upgrading to 2.9.2 (since you are in old MySQL), go straight to 2.9.3.

eladkal · 2024-08-01T09:58:30Z

Thanks Ephraim!
closing as fixed

JayalakshmiH · 2024-09-25T12:40:27Z

I am facing the same issue while upgrading from Airflow 2.8.2 to 2.9.3.
Mysql version: 8.0.28

Note: It works only with an empty db

robinaly added area:core kind:bug This is a clearly a bug needs-triage label for new issues that we didn't triage yet labels Jul 22, 2024

dosubot bot added the area:db-migrations PRs with DB migration label Jul 22, 2024

RNHTTR added good first issue and removed area:core needs-triage label for new issues that we didn't triage yet good first issue labels Jul 22, 2024

eladkal added area:core affected_version:2.9 labels Jul 22, 2024

eladkal closed this as completed Aug 1, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Error migrating database to Airflow version 2.9.3 #40928

Error migrating database to Airflow version 2.9.3 #40928

robinaly commented Jul 22, 2024

eladkal commented Jul 22, 2024

ephraimbuddy commented Aug 1, 2024

eladkal commented Aug 1, 2024

JayalakshmiH commented Sep 25, 2024

Error migrating database to Airflow version 2.9.3 #40928

Error migrating database to Airflow version 2.9.3 #40928

Comments

robinaly commented Jul 22, 2024

Apache Airflow version

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

What happened?

What you think should happen instead?

How to reproduce

Operating System

Versions of Apache Airflow Providers

Deployment

Deployment details

Anything else?

Are you willing to submit PR?

Code of Conduct

eladkal commented Jul 22, 2024

ephraimbuddy commented Aug 1, 2024

eladkal commented Aug 1, 2024

JayalakshmiH commented Sep 25, 2024