Upgrade to 2.3.4 DB Upgrade issue

[a246530]

Apache Airflow version

2.3.4

What happened

When running airflow db upgrade , we encounter the following error:
`[2022-09-05 07:04:10,182] {db.py:939} WARNING - Found 273 duplicates in table task_fail. Will attempt to move them.

[2022-09-05 07:08:23,703] {db.py:1466} INFO - Creating tables

INFO [alembic.runtime.migration] Context impl MySQLImpl.

INFO [alembic.runtime.migration] Will assume non-transactional DDL.

INFO [alembic.runtime.migration] Running upgrade 587bdf053233 -> 5e3ec427fdd3, Increase length of email and username in ab_user and ab_register_user table to 256 characters

INFO [alembic.runtime.migration] Running upgrade 5e3ec427fdd3 -> 786e3737b18f, Add timetable_description column to DagModel for UI.

INFO [alembic.runtime.migration] Running upgrade 786e3737b18f -> f9da662e7089, Add LogTemplate table to track changes to config values log_filename_template

INFO [alembic.runtime.migration] Running upgrade f9da662e7089 -> e655c0453f75, Add map_index column to TaskInstance to identify task-mapping,
and a task_map table to track mapping values from XCom.

INFO [alembic.runtime.migration] Running upgrade e655c0453f75 -> a3bcd0914482, add data_compressed to serialized_dag

INFO [alembic.runtime.migration] Running upgrade a3bcd0914482 -> c306b5b5ae4a, Switch XCom table to use run_id and add map_index.

INFO [alembic.runtime.migration] Running upgrade c306b5b5ae4a -> c97c2ab6aa23, add callback request table

INFO [alembic.runtime.migration] Running upgrade c97c2ab6aa23 -> 4eaab2fe6582, Migrate RTIF to use run_id and map_index

Traceback (most recent call last):

File "/opt/python3.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(

File "/opt/python3.9/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 719, in do_execute
cursor.execute(statement, parameters)

File "/opt/python3.9/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)

File "/opt/python3.9/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)

File "/opt/python3.9/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)

MySQLdb._exceptions.IntegrityError: (1215, 'Unknown error 1215')`

We are currently attempting to migrate from 2.2.4 to 2.3.4

What you think should happen instead

No response

How to reproduce

No response

Operating System

CentOS Linux 7

Versions of Apache Airflow Providers

No response

Deployment

Composer

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

[boring-cyborg[bot]]

Thanks for opening your first issue here! Be sure to follow the issue template!

[internetcoffeephone]

What is your database and table collation? My team had similar issues because the database was not initialized properly years ago, so the collation was set to Swedish. Switching it to utf8 fixed it.

Check out the docs for more information on the collation.

[a246530]

Thanks @internetcoffeephone , I checked and we are following the collation as defined in the documentation. We are using utf8mb4_unicode_ci.

[potiuk]

You might want to follow this guidelines for collation @a246530 -> it is not yet released so you will not find it in the official docs but it is supposed to be out in 2.4.0 https://github.com/apache/airflow/blob/main/docs/apache-airflow/installation/upgrading.rst#handling-migration-problems

It's not certain if it is the problem, but you will likely find out if you follow it. Unfortunately, there are a number of reasons for 1215 error, but collation might be one of them, but - unfortunately - MySQL has so many configurations options on the back-end (including which engine is used in the backend, what are the sizes of various fields etc. that there might be multiple reasons - specific to your deployment and there is no way we can help with solving all of them.

Soem reasons are explained here: https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint but this is just first result from a google query I run - you will likely find out more if you search more @a246530 . Follow them, you will likely find a solutiuon if "how to fix my collation" problem guideline does not fix it for you - then it is likely another problem.

At this stage (you are not the first person for me to reach that stage) I have the usual advice. Drop MySQL. Switch to postgres. more than 80 % of our users use Postgres (see https://airflow.apache.org/blog/airflow-survey-2022/) and almost exclusively we have similar kind of problems only with MySQL - coming from some ways of collaction/encoding changes in MySQL but also deadlocks and plenty other things.

Converting it into dicussion. And I would love to hear what is the result of your investigations @a246530

[a246530]

Thanks @potiuk , I have followed the guide and am still having issues. Going to take your suggestion for Postgres and see how that plays out. Thank!