During incremental synchronization, if the upstream creates sharded tables, can the downstream automatically create a table with additional columns?

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

Original topic: 增量同步过程中,上游创建分库分表,下游能自动创建一张带额外列的表吗?

| username: pandan

The upstream database contains both regular tables and sharded tables. After completing the full migration through DM, real-time incremental synchronization is performed. During the full migration of sharded tables, because it is necessary to record which sharded table from the upstream corresponds to the downstream, the corresponding downstream tables for the sharded tables can only be manually created before synchronization. If, during the incremental synchronization process, a new database is created upstream and new sharded tables are created in the new database (the new sharded tables are different from those during the full synchronization and are uniformly named starting with “s_”), can DM be configured to automatically create the corresponding downstream tables and add a field to record which upstream sharded table they came from?

| username: 连连看db | Original post link

You can achieve this through table routing and set rules according to your own requirements.

| username: pandan | Original post link

Why can’t tables with additional columns be automatically created during full migration, but can be during incremental migration?

| username: zhh_912 | Original post link

In the upstream database, change events can be transmitted to the downstream database using logical replication. At the same time, triggers can be set on the corresponding tables in the downstream database. When new sharded tables are created, the triggers can automatically execute the operation of creating tables with additional columns.

| username: yiduoyunQ | Original post link

| username: Hacker007 | Original post link

There should be an error, the number of fields on both sides does not match.

| username: pandan | Original post link

The answer to the original question should be that it cannot be achieved. The downstream table needs to be pre-created, and it is not possible to dynamically create a series of sub-tables upstream and have the downstream automatically create a merged table. There is another issue here: migrating sharded tables to a merged table with additional columns seems to have a bug. The extra columns cannot be recognized during incremental synchronization: dm: Column count doesn't match value count at row 1 when use extract-schema · Issue #10552 · pingcap/tiflow · GitHub

| username: Hacker007 | Original post link

This might be to maintain data consistency.

| username: Jasper | Original post link

You can configure it through the parameters extract-table, extract-schema, and extract-source.

For related documentation, refer to: TiDB Data Migration 表路由 | PingCAP 文档中心