search

apache / airflow

Apache Airflow - A platform to programmatically author, schedule, and monitor workflows
https://airflow.apache.org/
Apache License 2.0
37k stars 14.27k forks source link

Error migrating database from any lower Airflow version 2.x to 2.9.2 #43690

Open JayalakshmiH opened 2 hours ago

JayalakshmiH commented 2 hours ago

Apache Airflow version

Other Airflow 2 version (please specify below)

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

2.9.2

What happened?

When upgrading the database from 2.8.2 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.2, an empty mysql database. The initial new creation of new database objects works well. Now run, for example

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

Run airflow db migrate

Operating System

Debian GNU/Linux 11 (bullseye)

Versions of Apache Airflow Providers

No response

Deployment

Other Docker-based deployment

Deployment details

Mysql version (docker) mysql:8.0.28. Issue occurs in other versions of mysql as well i.e 8.0.32, 8.0.36

Anything else?

Though this issue is marked as closed https://github.com/apache/airflow/issues/40928, it is still occuring as reported by multiple users.

Are you willing to submit PR?

Code of Conduct

boring-cyborg[bot] commented 2 hours ago

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

xia-stan commented 1 hour ago

Can confirm that this error happens on Airflow 2.10.3 with MySQL 8.0.32.