DM should sync `TRUNCATE TABLE` statement to downstream

[dbakit]

What did you do?

execute statement TRUNCATE TABLE test.t1; on upstream mysql

What did you expect to see?

DM sync TRUNCATE TABLE statement to downstream as expect

What did you see instead?

TRUNCATE TABLE statement isn't synced to downstream

Versions of the cluster

DM version (run dmctl -V or dm-worker -V or dm-master -V):

Upstream MySQL/MariaDB server version:
tidb@dev-tidb-db-e01:~/dm/deploy/dm-worker-8271/log$ tiup dmctl -V
Starting component `dmctl`: /home/tidb/.tiup/components/dmctl/v2.0.6/dmctl/dmctl -V
Release Version: v2.0.6
Git Commit Hash: d2051ed75511e069a512f54fce25a02f6d5a73fd
Git Branch: heads/refs/tags/v2.0.6
UTC Build Time: 2021-08-13 09:19:33
Go Version: go version go1.16.4 linux/amd64

current status of DM cluster (execute query-status <task-name> in dmctl)

[2022/03/29 09:22:55.190 +08:00] [INFO] [syncer.go:2236] [task=test_task] [unit="binlog replication"] [event=query] [statement="truncate table `test`.`t1`"] [schema=] ["last location"="position: (mysql-bin.000005, 4), gtid-set: 6e196a56-a4d6-11ec-be82-0242ac110004:1-7,fc17aa25-a4d5-11ec-9595-0242ac110003:1-4501"] [location="position: (mysql-bin.000005, 45248908), gtid-set: 6e196a56-a4d6-11ec-be82-0242ac110004:1-7,fc17aa25-a4d5-11ec-9595-0242ac110003:1-4502"]
[2022/03/29 09:22:55.190 +08:00] [INFO] [syncer.go:2248] ["resolve sql"] [task=test_task] [unit="binlog replication"] [event=query] ["raw statement"="truncate table `test`.`t1`"] [statements="[\"TRUNCATE TABLE `test`.`t1`\"]"] [schema=] ["last location"="position: (mysql-bin.000005, 45248908), gtid-set: 6e196a56-a4d6-11ec-be82-0242ac110004:1-7,fc17aa25-a4d5-11ec-9595-0242ac110003:1-4502"] [location="position: (mysql-bin.000005, 45248908), gtid-set: 6e196a56-a4d6-11ec-be82-0242ac110004:1-7,fc17aa25-a4d5-11ec-9595-0242ac110003:1-4502"]
[2022/03/29 09:22:55.190 +08:00] [INFO] [syncer.go:2316] ["ignore truncate table statement in shard group"] [task=test_task] [unit="binlog replication"] [event=query] [statement="TRUNCATE TABLE `test`.`t1`"]
[2022/03/29 09:22:55.190 +08:00] [INFO] [syncer.go:2347] ["prepare to handle ddls"] [task=test_task] [unit="binlog replication"] [event=query] [ddls="[]"] ["raw statement"="truncate table `test`.`t1`"] [location="position: (mysql-bin.000005, 45248908), gtid-set: 6e196a56-a4d6-11ec-be82-0242ac110004:1-7,fc17aa25-a4d5-11ec-9595-0242ac110003:1-4502"]
[2022/03/29 09:22:55.190 +08:00] [INFO] [syncer.go:2349] ["skip event, need handled ddls is empty"] [task=test_task] [unit="binlog replication"] [event=query] ["raw statement"="truncate table `test`.`t1`"] [location="position: (mysql-bin.000005, 45248908), gtid-set: 6e196a56-a4d6-11ec-be82-0242ac110004:1-7,fc17aa25-a4d5-11ec-9595-0242ac110003:1-4502"]

[lance6716]

Thanks for feedback. I think we should allow TRUNCATE TABLE to be replicated to downstream. And for pessimistic shard merging, TRUNCATE is like other DDL that should be executed in all shard. For optimistic shard merging we simply report an error. Does this look good for you? @dba-kit

/cc @sunzhaoyang @GMHDBJD

ref https://asktug.com/t/topic/603793

[GMHDBJD]

I prefer add the default truncate rule in filter but allow user to remove that rule.

[buchuitoudegou]

I prefer add the default truncate rule in filter but allow user to remove that rule.

Did you mean add a rule to the config, such as something like "allow-truncate", which defaults to "false"?

[GMHDBJD]

Did you mean add a rule to the config, such as something like "allow-truncate", which defaults to "false"?

exactly! cc @sunzhaoyang

[dbakit]

That's fine for me too.

[sunzhaoyang]

I think it makes sense.

[lance6716]

another user case https://asktug.com/t/topic/812993

[lance6716]

another user report https://github.com/pingcap/tidb/issues/55650

[lance6716]

PTAL @alastori

[alastori]

Yes, it makes sense. We should support the TRUNCATE TABLE statement in DM (Data Migration). Implementing a configuration option like allow-truncate, which defaults to false, is a good approach. This way, users can enable TRUNCATE TABLE synchronization when needed, but it will be off by default to avoid any unintended consequences. These unintended consequences include ensuring the safe deployment of this feature without compromising the safety of data for existing customers who rely on the current behavior. We must be cautious to maintain data integrity and stability for users who do not enable this option.

Additionally, we should ensure that when TRUNCATE TABLE statements are ignored, this action is properly logged (if not already implemented). This will help users and support teams track and diagnose issues more effectively.