[BUG] DM synchronization does not support the operation of renaming table t1 to t1_bak and _new_t1 to t1 (TiDB 6.1 already supports it)

Translated
1

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

Original topic: [BUG] DM同步不支持rename table t1 to t1_bak, _new_t1 to t1的操作(TiDB 6.1已经支持)

| username: dba-kit

Background

The DM synchronization task reported an error with the following content:

"startLocation: [position: (mysql-bin.007702, 409498399), gtid-set: 2873b8da-f7c6-11ea-97a4-00163e1092a8:1-722134916,777234c5-87b7-11ea-9279-00163e1054e6:1-1444325082,784b7fc9-87b7-11ea-a0b7-00163e068fb5:1-1724587836,abc7d256-e94d-11ec-a600-00163e0a892d:1-57,adc3c038-e94d-11ec-8ce1-00163e0489e6:1-2393:9215-43490743], endLocation: [position: (mysql-bin.007702, 409498652), gtid-set: 2873b8da-f7c6-11ea-97a4-00163e1092a8:1-722134917,777234c5-87b7-11ea-9279-00163e1054e6:1-1444325082,784b7fc9-87b7-11ea-a0b7-00163e068fb5:1-1724587836,abc7d256-e94d-11ec-a600-00163e0a892d:1-57,adc3c038-e94d-11ec-8ce1-00163e0489e6:1-2393:9215-43490743], origin SQL: [rename table ying99_fundtxn.fund_order_offline to ying99_fundtxn.fund_order_offline_bak_20221017, ying99_fundtxn._tmp_fund_order_offline to ying99_fundtxn.fund_order_offline]: ddl on multiple table: rename table ying99_fundtxn.fund_order_offline to ying99_fundtxn.fund_order_offline_bak_20221017, ying99_fundtxn._tmp_fund_order_offline to ying99_fundtxn.fund_order_offline not supported"

However, TiDB 6.X and later versions already support multi-rename operations, but DM still blocks such statements during parsing.

MySQL [(none)]> rename table ying99_fundtxn.fund_order_offline to ying99_fundtxn.fund_order_offline_bak_20221017, ying99_fundtxn._tmp_fund_order_offline to ying99_fundtxn.fund_order_offline;
Query OK, 0 rows affected (0.51 sec)
MySQL [(none)]> show variables like 'version';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| version       | 5.7.25-TiDB-v6.1.1 |
+---------------+--------------------+
1 row in set (0.00 sec)
2
| username: Hacker007 | Original post link

It can only be changed to incremental synchronization, right? Modify the table name of the target table.

3
| username: tidb狂热爱好者 | Original post link

Are you synchronizing TiDB with TiDB?

4
| username: xfworld | Original post link

Go straight to the new version~ :cowboy_hat_face:

5
| username: dba-kit | Original post link

MySQL is synchronized to TiDB. Now both MySQL and TiDB support the multi-rename syntax, but DM in the middle does not support it, resulting in synchronization still not working.

6
| username: dba-kit | Original post link

The Placement Rule in SQL is amazing. We are using our summary TiDB as an archive database, and now the bottleneck is in capacity. We have to expand TiKV nodes periodically. The boss often complains about the high cost. After upgrading to 6.1.1, we can use hot and cold separation to store historical data on SATA disks, which is expected to cut costs by half.

7
| username: lance6716 | Original post link

This error is not because RENAME multiple tables is not supported, but because under pessimistic/optimistic coordination, a single statement cannot contain multiple shard tables/non-shard tables.

What is the scenario for this statement? Is it generated by an online DDL tool?

8
| username: dba-kit | Original post link

However, I didn’t set up routing; it’s a single MySQL synchronization task. But indeed, this afternoon when I used binlog skip DDL for this task, I was prompted that a shard DDL was blocked, and I even forcibly removed the shard lock. Is there any command to see if the task is in shard status?

9
| username: dba-kit | Original post link

Got it, I set the shard-mode according to the default configuration in the documentation. I’ll remove it and try again :rofl:

10
| username: tidb狂热爱好者 | Original post link

The new version will support it.

11
| username: dba-kit | Original post link

Indeed, this is the reason. After I changed the shard-mode in the task configuration from “pessimistic” to “”, it worked fine.

12
| username: lance6716 | Original post link

Could you provide the configuration file? We want to see if there are any optimizations we can make to the code.

13
| username: dba-kit | Original post link

Actually, there isn’t much optimization in the code. It’s just that the default value for the “shard-mode” parameter in the official documentation template is “pessimistic”. Adding this parameter will trigger the sharding logic. When I was copying the template, I forgot to remove this line…

14
| username: tidb狂热爱好者 | Original post link

Solving the problem is good. Wishing TiDB prosperity.