airflow db upgrade failed from 2.1.4 to 2.2.0

[EugeneChung]

Apache Airflow version

2.2.0 (latest released)

Operating System

CentOS 7

Versions of Apache Airflow Providers

apache-airflow-providers-ftp 2.0.1
apache-airflow-providers-http 1.1.1
apache-airflow-providers-imap 2.0.1
apache-airflow-providers-mysql 1.1.0
apache-airflow-providers-sqlite 2.0.1

Deployment

pip3

Deployment details

No response

What happened

I executed airflow db upgrade from 2.1.4 to 2.2.0 and met the error below.
FYI, My database is 8.0.26 MySQL Community Server.

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (3780, "Referencing column 'dag_id' and referenced column 'dag_id' in foreign key constraint 'task_reschedule_ti_fkey' are incompatible.")
[SQL: ALTER TABLE task_reschedule ADD CONSTRAINT task_reschedule_ti_fkey FOREIGN KEY(dag_id, task_id, run_id) REFERENCES task_instance (dag_id, task_id, run_id) ON DELETE CASCADE]

This is the full error logs.

$ airflow version
2.2.0
$ airflow db upgrade
/.../.local/lib/python3.7/site-packages/airflow/configuration.py:361 DeprecationWarning: The dag_concurrency option in [core] has been renamed to max_active_tasks_per_dag - the old setting has been used, but please update your config.
/.../.local/lib/python3.7/site-packages/airflow/configuration.py:361 DeprecationWarning: The processor_poll_interval option in [scheduler] has been renamed to scheduler_idle_sleep_time - the old setting has been used, but please update your config.
DB: mysql 8.0.26 at local
[2021-10-20 10:09:35,183] {db.py:823} INFO - Creating tables
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
/.../.local/lib/python3.7/site-packages/airflow/providers_manager.py:523 DeprecationWarning: The provider apache-airflow-providers-http uses `hook-class-names` property in provider-info and has no `connection-types` one. The 'hook-class-names' property has been deprecated in favour of 'connection-types' in Airflow 2.2. Use **both** in case you want to have backwards compatibility with Airflow < 2.2
/.../.local/lib/python3.7/site-packages/airflow/providers_manager.py:523 DeprecationWarning: The provider apache-airflow-providers-mysql uses `hook-class-names` property in provider-info and has no `connection-types` one. The 'hook-class-names' property has been deprecated in favour of 'connection-types' in Airflow 2.2. Use **both** in case you want to have backwards compatibility with Airflow < 2.2
INFO  [alembic.runtime.migration] Running upgrade ccde3e26fe78 -> 83f031fd9f1c, improve mssql compatibility
INFO  [alembic.runtime.migration] Running upgrade 83f031fd9f1c -> e9304a3141f0, make xcom pkey columns non-nullable
INFO  [alembic.runtime.migration] Running upgrade e9304a3141f0 -> 30867afad44a, Rename concurrency column in dag table to max_active_tasks
INFO  [alembic.runtime.migration] Running upgrade 30867afad44a -> 54bebd308c5f, Add trigger table and task info
INFO  [alembic.runtime.migration] Running upgrade 54bebd308c5f -> 142555e44c17, Add data_interval_[start|end] to DagModel and DagRun.
INFO  [alembic.runtime.migration] Running upgrade 142555e44c17 -> 7b2661a43ba3, TaskInstance keyed to DagRun
Traceback (most recent call last):
  File "/.../.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/.../.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
  File "/.../.local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/.../.local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/.../.local/lib/python3.7/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (3780, "Referencing column 'dag_id' and referenced column 'dag_id' in foreign key constraint 'task_reschedule_ti_fkey' are incompatible.")

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

Traceback (most recent call last):
  File "/.../.local/bin/airflow", line 8, in <module>
    sys.exit(main())
  File "/.../.local/lib/python3.7/site-packages/airflow/__main__.py", line 40, in main
    args.func(args)
  File "/.../.local/lib/python3.7/site-packages/airflow/cli/cli_parser.py", line 48, in command
    return func(*args, **kwargs)
  File "/.../.local/lib/python3.7/site-packages/airflow/utils/cli.py", line 92, in wrapper
    return f(*args, **kwargs)
  File "/.../.local/lib/python3.7/site-packages/airflow/cli/commands/db_command.py", line 48, in upgradedb
    db.upgradedb()
  File "/.../.local/lib/python3.7/site-packages/airflow/utils/session.py", line 70, in wrapper
    return func(*args, session=session, **kwargs)
  File "/.../.local/lib/python3.7/site-packages/airflow/utils/db.py", line 824, in upgradedb
    command.upgrade(config, 'heads')
  File "/usr/local/lib/python3.7/site-packages/alembic/command.py", line 320, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.7/site-packages/alembic/script/base.py", line 563, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 108, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 728, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/.../.local/lib/python3.7/site-packages/airflow/migrations/env.py", line 107, in <module>
    run_migrations_online()
  File "/.../.local/lib/python3.7/site-packages/airflow/migrations/env.py", line 101, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.7/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.7/site-packages/alembic/runtime/migration.py", line 612, in run_migrations
    step.migration_fn(**kw)
  File "/.../.local/lib/python3.7/site-packages/airflow/migrations/versions/7b2661a43ba3_taskinstance_keyed_to_dagrun.py", line 264, in upgrade
    ondelete=ondelete,
  File "/usr/local/lib/python3.7/contextlib.py", line 119, in __exit__
    next(self.gen)
  File "/usr/local/lib/python3.7/site-packages/alembic/operations/base.py", line 369, in batch_alter_table
    impl.flush()
  File "/usr/local/lib/python3.7/site-packages/alembic/operations/batch.py", line 107, in flush
    fn(*arg, **kw)
  File "/usr/local/lib/python3.7/site-packages/alembic/ddl/impl.py", line 337, in add_constraint
    self._exec(schema.AddConstraint(const))
  File "/usr/local/lib/python3.7/site-packages/alembic/ddl/impl.py", line 197, in _exec
    return conn.execute(construct, multiparams)
  File "/.../.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/.../.local/lib/python3.7/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/.../.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1073, in _execute_ddl
    compiled,
  File "/.../.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/.../.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/.../.local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/.../.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/.../.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
  File "/.../.local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/.../.local/lib/python3.7/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/.../.local/lib/python3.7/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (3780, "Referencing column 'dag_id' and referenced column 'dag_id' in foreign key constraint 'task_reschedule_ti_fkey' are incompatible.")
[SQL: ALTER TABLE task_reschedule ADD CONSTRAINT task_reschedule_ti_fkey FOREIGN KEY(dag_id, task_id, run_id) REFERENCES task_instance (dag_id, task_id, run_id) ON DELETE CASCADE]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

What you expected to happen

No response

How to reproduce

airflow db upgrade

Anything else

No response

Are you willing to submit PR?

No response

Code of Conduct

• I agree to follow this project's Code of Conduct by

[potiuk]

I think it's another manifestation of #18939 - which I believe might (should?) be fixed in the upcoming 2.2.1