"ErrCode": 44016. Synchronization Error in DM v6.0.0

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

Original topic: “ErrCode”: 44016. dm v6.0.0 同步报错

| username: TiDBer_mpc31bXK

【TiDB Usage Environment】Production Environment / Testing / Poc
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Phenomenon and Impact】
【Resource Configuration】
【Attachments: Screenshots / Logs / Monitoring】
mysql8->mysql8 full data synchronization completed, upstream and downstream and task sql_mode are consistent.
Worker log:
[2023/01/31 11:40:41.449 +08:00] [INFO] [streamer_controller.go:77] [“last slave connection”] [task=alydm] [unit=“binlog replication”] [“connection ID”=5178652]
[2023/01/31 11:40:41.449 +08:00] [INFO] [mode.go:101] [“change count”] [task=alydm] [unit=“binlog replication”] [“previous count”=0] [“new count”=0]
[2023/01/31 11:40:41.449 +08:00] [INFO] [mode.go:101] [“change count”] [task=alydm] [unit=“binlog replication”] [“previous count”=0] [“new count”=1]
[2023/01/31 11:40:41.449 +08:00] [INFO] [safe_mode.go:70] [“enable safe-mode because of task initialization”] [task=alydm] [unit=“binlog replication”] [“duration in seconds”=60]
[2023/01/31 11:40:41.465 +08:00] [INFO] [tracker.go:436] [“Downstream schema tracker init. “] [task=alydm] [unit=“binlog replication”] [tableID=]
[2023/01/31 11:40:41.469 +08:00] [ERROR] [baseconn.go:184] [“execute statement failed”] [task=alydm] [unit=“binlog replication”] [query=“SET SESSION 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’”] [argument=””] [error=“Error 1231: Variable ‘sql_mode’ can’t be set to the value of ‘NO_AUTO_CREATE_USER’”]

Same issue as this one:

| username: dba-kit | Original post link

Please provide the TiDB version, complete error message, and DM task configuration.

| username: dba-kit | Original post link

Looking at the post you referenced, it seems to be about synchronizing from MySQL to MySQL, and the downstream doesn’t support TiDB’s default sql_mode. Are you in the same scenario? Are you trying to synchronize data from MySQL 8.0 to 5.7?

| username: TiDBer_mpc31bXK | Original post link

Is it possible to set this sql_mode? The worker log has been posted.

| username: dba-kit | Original post link

Looking at the release notes, MySQL removed NO_AUTO_CREATE_USER after 8.0.11. But if it’s 8 -> 8, why not use MySQL replication directly instead of adding a DM cluster?
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

| username: TiDBer_mpc31bXK | Original post link

Actually, the issue is that normally the sql_mode is taken from the target database, but in some cases, it is taken from the default. There are other synchronization solutions, this is just a trial to see if there are any problems. :grin:

| username: dba-kit | Original post link

@buchuitoudegou Based on this response, when obtaining the downstream table structure, the value of the predefined variable defaultSQLMode hardcoded in the code is used. Wouldn’t it be more appropriate to use the sql_mode specified by the user in task.yaml?

| username: TiDBer_mpc31bXK | Original post link

This configurability allows for expansion.

| username: TiDBer_mpc31bXK | Original post link

Downgrading to 5.3.0 solved the problem. :see_no_evil:

| username: lance6716 | Original post link

Thank you for the feedback. We will update the progress of the fix at DM uses a SQL mode which is removed in MySQL 8.0 · Issue #8149 · pingcap/tiflow · GitHub.

| username: system | Original post link

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