[GitHub] [airflow] eRadical commented on a diff in pull request #24926: Troubleshooting MySQL Exceptions at upgrade

[GitBox <gi...@apache.org>]

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: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org