How to handle inconsistent table structures when creating new tables in sharding pessimistic mode?

Translated
1

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

Original topic: 分库分表悲观模式如果新建的表的表结构不一致怎么处理？

| username: Hacker007

How can I restore the task when the structure of the newly created table is inconsistent with the original table?

2
| username: TiDBer_jYQINSnf | Original post link

I understand that DM does not support different table structures, right? How about making them the same first? After importing, you can modify the table structure.

3
| username: Hacker007 | Original post link

No, even after making the upstream table structure consistent with the downstream table, it still cannot be restored. Deleting the upstream table also doesn’t help.

4
| username: forever | Original post link

The DM version can be referred to here: Data Migration 常见问题 | PingCAP 文档中心

5
| username: Hacker007 | Original post link

In version 5.2.0, skip cannot bypass exceptions, and resume doesn’t work either.

6
| username: buchuitoudegou | Original post link

May I ask what your requirements are? If the upstream and downstream tables are inconsistent, you can try optimistic mode for merging databases and tables migration.

7
| username: Hacker007 | Original post link

Simulate creating a table with different structures that match the synchronization task regex upstream, how to handle this type of task exception.

8
| username: buchuitoudegou | Original post link

Let me understand your requirement:
There is an upstream table A that corresponds to a downstream table B, and the structures of tables A and B are different. However, except for the optimistic mode, other modes do not support such operations, so errors will occur. Your requirement is to simulate this type of error and then use the relevant dmctl commands to handle them?

9
| username: Hacker007 | Original post link

Yes, currently no solution has been found.

10
| username: buchuitoudegou | Original post link

Refer to this document: 下游存在更多列的迁移场景 | PingCAP 文档中心
In the example from the document, the upstream and downstream table structures are different, but the additional columns in the downstream are default null columns. You can make the upstream changes compatible with the downstream table structure by using the binlog-schema command to set the schema structure maintained internally by DM (without modifying the downstream table structure), so that the changes can be parsed and synchronized to the downstream as expected.

For detailed principles and more operational methods, refer to this document: 管理 TiDB Data Migration 迁移表的表结构 | PingCAP 文档中心