DM v6.0.0 Task Startup Setting sql_mode Invalid

Translated
1

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

Original topic: dm v6.0.0 启动任务设置sql_mode无效

| username: TiDBer_zKukjQgZ

[TiDB Usage Environment] Test
[TiDB Version] v6.0.0-master-dirty
CentOS Version: CentOS Linux release 8.5.2111
DM Binary Information:

[root@9ab1a07de5e3 bin]# pwd
/opt/dm-nightly-linux-amd64/bin

[root@9ab1a07de5e3 bin]# ll -h
total 458M
drwxr-xr-x. 3 root root  161 Dec  5 09:49 .
drwxr-xr-x. 4 1000 1000   44 May 31  2022 ..
drwx------. 3 root root   20 Dec  4 17:02 default.master1
-rwxr-xr-x. 1 root root 101M May 31  2022 dmctl
-rwxr-xr-x. 1 root root 114M May 31  2022 dm-master
-rwxr-xr-x. 1 root root 110M May 31  2022 dm-syncer
-rwxr-xr-x. 1 root root 121M May 31  2022 dm-worker
-rwxr-xr-x. 1 root root  13M May 31  2022 mydumper

[root@9ab1a07de5e3 bin]# /opt/dm-nightly-linux-amd64/bin/dmctl --version
Release Version: v6.0.0-master-dirty
Git Commit Hash: 728c693c9266f1c7d96c74bbd4e8cba4cbd6160c
Git Branch: master
UTC Build Time: 2022-05-30 11:36:06
Go Version: go version go1.18.2 linux/amd64

[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact]
An error regarding sql_mode occurred during migration. Although sql_mode was configured in the task.yaml file, it did not take effect during execution.
[Resource Configuration]
Source Configuration Information

source-id: "mysql-replica-01"

from:
  host: "127.0.0.1"
  user: "HH"
  password: "S2fpXs8MEHesXUQjj6YH0ECO3Et+ZteB3Q=="
  port: 3306

Task Configuration:

name: testdm
task-mode: all

target-database:
  host: "10.88.0.11"
  port: 3306
  user: "HH"
  password: "S2fpXs8MEHesXUQjj6YH0ECO3Et+ZteB3Q=="
  session:
    sql_mode: "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

mysql-instances:
  - source-id: "mysql-replica-01"
    block-allow-list:  "ba-rule1"

block-allow-list:
  ba-rule1:
    do-dbs: ["employees"]

[Attachments: Screenshots/Logs/Monitoring]
Task Error File:
error.log (2.6 KB)

Target Database General Log File, each connection executed the sql_mode configured in the task configuration during creation, but executed other sql_mode during statement execution. Please see the log for session id 4140:
general_log.log (8.4 KB)

2
| username: buchuitoudegou | Original post link

This is an error from the schema tracker, which is used to parse the downstream create table statements. It uses the default SQL mode (instead of the one set in task.yaml, FYI SQL 模式 | PingCAP 文档中心). You can see that other queries in the general log do not set this value.

May I ask what version of TiDB you are using downstream? Additionally, this DM version is not a release version; you might want to use an official release version.