eRadical commented on code in PR #24926:
URL: https://github.com/apache/airflow/pull/24926#discussion_r950238399
##########
docs/apache-airflow/installation/upgrading.rst:
##########
@@ -118,3 +118,69 @@ Deleting a table:
.. code-block:: sql
DROP TABLE <table>;
+
+Troubleshooting MySQL Exceptions at upgrade
+===========================================
+
+How to analyse exceptions during MySQL database migration steps. In general
the `airflow db upgrade` handles the necessary migration steps but excceptions
can happen (backups before migration recommended).
+This description is based on issue due different charactersets and collation.
When the database was created the defaults on mysql were *utf8mb4*
*utf8mb4_0900_ai_ci*.
+Since the pull request [Automatically use utf8mb3_general_ci collation for
mysql](https://github.com/apache/airflow/pull/17729) *utf8* *utf8mb3_bin* also
called *utf8_bin* is used.
+
+At Figure out the sql-statement which caused the error. See exemplary Log
below.
+
+ .. code-block:: python
+
+ # other loglines ...
+
+ [2022-07-01 13:26:19,473[] {db.py:1448} 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 f9da662e7089 ->
e655c0453f75, Add ``map_index`` column to TaskInstance to identify task-mapping,
+ and a ``task_map`` table to track mapping values from XCom.
+
+ MySQLdb._exceptions.OperationalError: (1091, "Can't DROP
'task_reschedule_ti_fkey'; check that column/key exists")
+
+ # other loglines ...
+
+ File
"/home/airflow/.local/lib/python3.8/site-packages/airflow/migrations/versions/0100_2_3_0_add_taskmap_and_map_id_on_taskinstance.py",
line 49, in upgrade
+ batch_op.drop_index("idx_task_reschedule_dag_task_run")
+
+ # other loglines ...
+
+ sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError)
(1091, "Can't DROP 'task_reschedule_ti_fkey'; check that column/key exists")
+ [SQL: ALTER TABLE task_reschedule DROP FOREIGN KEY
task_reschedule_ti_fkey[]
+ (Background on this error at: http://sqlalche.me/e/14/e3q8)
+
+As you can see the `airflow db upgrade` command tries to do necessary sql
stuff to **identify task-mapping** and **track mapping values from XCom**.
+The `airflow db upgrade` command choose depending on **version_num** (col) in
the **alembic_version** (table) which migration steps/scripts are required.
+
+**Can't DROP 'task_reschedule_ti_fkey** - statement which is executed and
produces the error.
+**airflow/migrations/versions/0100_2_3_0_add_taskmap_and_map_id_on_taskinstance.py**
- here you can find the script in which the command is executed within the
airflow project.
+Also a good overview about migrations steps can be found
[here](https://github.com/apache/airflow/blob/main/docs/apache-airflow/migrations-ref.rst)
+
+But keep in mind it's may not the root cause, this log is from the 2nd try of
the migration and exception is raised of the missing fk droped by run before.
+The origin exception was from create table statement. The 2nd up 5th try was
created by migration job due k8s deployment.
+
+ .. code-block:: sql
+ CREATE TABLE task_map (
+ dag_id VARCHAR(250) COLLATE utf8mb3_bin NOT NULL,
+ task_id VARCHAR(250) COLLATE utf8mb3_bin NOT NULL,
+ run_id VARCHAR(250) COLLATE utf8mb3_bin NOT NULL,
+ map_index INTEGER NOT NULL,
+ length INTEGER NOT NULL,
+ `keys` JSON,
+ PRIMARY KEY (dag_id, task_id, run_id, map_index),
+ CONSTRAINT task_map_length_not_negative CHECK (length >= 0),
+ CONSTRAINT task_map_task_instance_fkey FOREIGN KEY(dag_id, task_id,
run_id, map_index) REFERENCES task_instance (dag_id, task_id, run_id,
map_index) ON DELETE CASCADE
+ )
+
+ [Code: 3780, SQL State: HY000] Referencing column 'task_id' and referenced
column 'task_id' in foreign key constraint 'task_map_task_instance_fkey' are
incompatible.
+
+You can explore by making use of **dry run** by ``airflow db upgrade -s
--from-version <VERSION> --to-version <VERSION>`` (see: Offline SQL migration
scripts) to produce sql statements for manual troubleshoot session.
+Now you'll have the possibility to run analyse and change sql-statements for
manual migration exception handling.
+
+If you encounter the same issue from the example, it can be fixed by changing
the charset collation for related tables to `task_instance` and `xcom` for
example `ALTER TABLE task_instance MODIFY task_id VARCHAR(255) CHARACTER SET
utf8 COLLATE utf8mb3_bin;` The dry-run.sql script for more details is shared
into related [issue
#24526](https://github.com/apache/airflow/issues/24526#issuecomment-1173582891)
Review Comment:
To correctly convert the table charset and/or collation you must use:
```
ALTER TABLE task_instance CONVERT TO CHARSET 'utf8mb3' COLLATE 'utf8mb3_bin';
```
Changing the charset/collation with the declaration above might result in
data loss as it only changes for the future data that is inserted. Using
convert it will correct the data already present in the table.
It is only ok if the table is empty.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
