Migrating MySQL 8.0.28 to TiDB v6.5.0 using DM v6.5.0, collation is not same error

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 使用DMv6.5.0将 Mysql 8.0.28迁移到TiDBv6.5.0,collation is not same错误

| username: wzf0072

[Test Environment for TiDB] Testing
[TiDB Version] v6.5.0
[Reproduction Path] Using DM v6.5.0 to migrate MySQL 8.0.28 to TiDB v6.5.0
[Encountered Issue: Phenomenon and Impact] The upstream is a business database, and TiDB is used for BI and reporting in the test. An error occurred during table structure migration, and the table structure has been pre-created in TiDB. Starting the DM task resulted in an error, and no data was synchronized.
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]
[tidb@TiTest opt]$ tiup dmctl --master-addr=172.16.86.60:8261 start-task dm-task.yaml
tiup is checking updates for component dmctl …
A new version of dmctl is available:
The latest version: v6.6.0
Local installed version: v6.5.0
Update current component: tiup update dmctl
Update all components: tiup update --all

Starting component dmctl: /home/tidb/.tiup/components/dmctl/v6.5.0/dmctl/dmctl --master-addr=172.16.86.60:8261 start-task dm-task.yaml
{
“result”: false,
“msg”: “[code=46014:class=scheduler:scope=internal:level=medium], Message: subtasks with name dm-taskX for sources [mysql-replica-sit8641w mysql-replica-uat8721w] already exist, Workaround: Please use query-status command to see tasks.”,
“sources”: [
],
“checkResult”: "fail to check synchronization configuration with type: no errors but some warnings
…
{
“id”: 22,
“name”: “table structure compatibility check”,
“desc”: “check compatibility of table structure”,
“state”: “warn”,
“errors”: [
{
“severity”: “warn”,
“short_error”: “table master.mdc_invoice_address collation is not same, upstream: (mdc_invoice_address utf8mb4_0900_ai_ci), downstream: (mdc_invoice_address utf8mb4_bin)”
}

2
| username: dba-kit | Original post link

This is a warning and should not affect the operation. Additionally, TiDB does not support the utf8mb4_0900_ai_ci character set. Theoretically, the task should report an error during the load phase rather than automatically replacing it with utf8mb4_bin. Someone must have manually created this table in the downstream TiDB in advance.

3
| username: dba-kit | Original post link

The error is because the task_name is duplicated, right?

4
| username: ShawnYan | Original post link

Additionally, if you are concerned about character collation rules, you can also consider converting MySQL 8 tables to COLLATE=utf8mb4_bin.

5
| username: tidb菜鸟一只 | Original post link

Character set issue, suggest modifying the source character set.

6
| username: wzf0072 | Original post link

I manually created the table in the downstream TiDB. It affected the operation, and the table data was not synchronized.

7
| username: dba-kit | Original post link

Try executing query-status dm-taskX to check the current task status. It seems like the first task execution failed previously, but instead of stopping the task, it was directly started again.

8
| username: wzf0072 | Original post link

The image is not visible. Please provide the text content for translation.

9
| username: 考试没答案 | Original post link

The official documentation states that MySQL 8 is currently an experimental feature. This is clearly a character set issue.

Changing the character set on the source side is obviously a significant amount of work, and this is the default character set for MySQL 8.

10
| username: 考试没答案 | Original post link

The data initialization tool for DM also uses Dumpling. You can manually export, manually complete the initialization, and proceed with the incremental updates.

In the database creation script, change the character set encoding.

11
| username: dba-kit | Original post link

If it is not exported to S3, by default it is exported to a directory starting with task-name in the Deploy Dir of the dm-worker. You can use sed -i to globally replace utf8mb4_0900_ai_ci with utf8mb4_unicode_ci in files ending with -schema.sql. Then use resume-task dm-taskX to continue the load phase.

12
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.