After DM Merge, Synchronization Error: Column count doesn't match value count: 27 (columns) vs 26 (values)

Translated
1

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

Original topic: DM 合并以后同步报错: Column count doesn’t match value count: 27 (columns) vs 26 (values)"

| username: LKLK

In the online V5.4.1 version, the upstream MySQL has multiple tables created annually (with the same table structure, 26 fields). These tables are merged into a downstream summary table through DM (with an additional field, an implicit primary key ID set to AUTO_RANDOM). During synchronization, it reports “Column count doesn’t match value count: 27 (columns) vs 26 (values).” Manually using the command tiup dmctl --master-addr xxxx operate-schema set -s pay-replica-01 pay -d snspaycenter -t t_order_2018 snspaycenter.t_order_2018-schema.sql to specify the table structure that matches the binlog event. However, the error still occasionally occurs, requiring repeated execution of the above command to fix it. Is there any way to fix this type of error once and for all?

[Attachment]

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: db_user | Original post link

Is it possible to modify the downstream table structure by adding a nullable field to see if it resolves the issue?

If the upstream is RDS, you can also set it to not use implicit primary keys on the RDS side.

3
| username: LKLK | Original post link

Currently, the downstream table has one more field than the upstream table, and the upstream table is a self-built MySQL.

4
| username: db_user | Original post link

Sorry, I didn’t see it clearly. Could you provide a reproducible table structure? What are the table structures upstream and downstream like?

5
| username: buchuitoudegou | Original post link

You are dealing with a scenario of merging tables, which means there are more than one table with the same structure upstream. However, the operate-schema will only set the specified table (t_order_2018) of the specified database (snspaycenter) from the specified source (pay-replica-01).

When parsing the binlog of other tables, it still needs to be set again. Could this be the reason?

6
| username: Hacker007 | Original post link

Pay attention to comparing the table structures of upstream and downstream. If the upstream uses databases like Alibaba Cloud’s RDB and the table does not have a primary key, there will be a hidden primary key, which may also cause such anomalies to occur.