Using DM to Upgrade MySQL 5.7 to 8.0, Incremental DDL Reports sql_mode Related Errors

Translated
1

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

Original topic: 使用DM升级MySQL5.7到8.0，增量DDL报sql_mode相关错误

| username: cqzwx

[TiDB Usage Environment] Poc
[TiDB DM Version] 6.5.0
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Phenomenon and Impact] Using DM to upgrade MySQL 5.7 to 8.0, full synchronization mode works fine, but incremental synchronization encounters a sql_mode error when creating tables in downstream MySQL 8. Disabling sql_mode in downstream MySQL 8 also results in an error, as shown below:

“Message”: “startLocation: [position: (mysql-bin.000003, 237639569), gtid-set: 84c1354f-419e-11ee-af95-fa163ecab0ce:1-1513], endLocation: [position: (mysql-bin.000003, 237639640), gtid-set: 84c1354f-419e-11ee-af95-fa163ecab0ce:1-1513]: gen insert sqls failed, sourceTable: test.repl5_8, targetTable: test.repl5_8: failed to set default downstream sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION in schema tracker”,
“RawCause”: “Error 1231: Variable ‘sql_mode’ can’t be set to the value of ‘NO_AUTO_CREATE_USER’”,

[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

img_v2_415926a5-b2f4-4c02-8cfd-e8842eb451fg
img_v2_415926a5-b2f4-4c02-8cfd-e8842eb451fg1380×178 35.1 KB

2
| username: WalterWj | Original post link

:thinking: I tried it with TiDB v7.1.1 and it can be set.

3
| username: Kongdom | Original post link

The original poster is talking about an error in MySQL8, right?

4
| username: 有猫万事足 | Original post link

This is a resolved issue.

The resolution date is close to DM 6.6, making it difficult to determine if 6.6 includes this fix, but it is certain that DM 7.0 and above will not have this problem.
So, upgrade DM.

5
| username: WalterWj | Original post link

Okay, DM will also set the upstream SQL_mode? That’s amazing.

6
| username: 有猫万事足 | Original post link

It should be an error when transferring the sql_mode from upstream to downstream.

7
| username: cqzwx | Original post link

Thank you very much, I’ll take a look :grinning:

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

Here’s a temporary solution: set the sql_mode of the downstream to be the same as the upstream, and remember to remove NO_AUTO_CREATE_USER because this has been removed in MySQL 8. Then, use binlog skip in DM to bypass this error.

9
| username: system | Original post link

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