DM synchronization does not support adding partitions

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

Original topic: dm同步不支持增加分区

| username: foxchan

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] Adding a new partition upstream, DM synchronization error

The downstream is a partitioned table, downstream table structure:
CREATE TABLE leader_order (
id bigint(20) NOT NULL AUTO_INCREMENT,
tenant_id int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Leader tenant primary key’,
salesman_user_id int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Influencer ID. rbac_common_user table ID’,
salesman_user_info_id int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Primary key ID of the influencer account’,
promote_platform_type int(2) NOT NULL COMMENT ‘Promotion platform type 1 Douyin, 2 Youzan, 3 Kuaishou, 6 Video Number’,
relate_shop_salesman_id varchar(100) DEFAULT ‘0’ COMMENT ‘Third-party influencer number or ID’,
relate_shop_salesman_name varchar(100) DEFAULT ‘’ COMMENT ‘Third-party promotion name’,
relate_order_no varchar(100) NOT NULL COMMENT ‘Third-party order number’,
relate_activity_id varchar(100) DEFAULT ‘’ COMMENT ‘Third-party activity ID’,
amount int(11) NOT NULL COMMENT ‘Purchase quantity’,
status tinyint(2) NOT NULL COMMENT ‘0 Pending payment, 1 Paid, 2 Settled, 3 Returned/Refunded, 4 Received’,
relate_shop_id varchar(255) DEFAULT NULL COMMENT ‘Third-party shop ID’,
relate_shop_name varchar(255) DEFAULT NULL COMMENT ‘Third-party shop name’,
relate_product_id varchar(255) DEFAULT NULL COMMENT ‘Third-party product ID’,
relate_product_name varchar(255) DEFAULT NULL COMMENT ‘Third-party product name’,
relate_product_img varchar(500) DEFAULT NULL COMMENT ‘Third-party product main image’,
buyer_paid bigint(20) DEFAULT ‘0’ COMMENT ‘Amount paid by the buyer’,
sys_relate_service_money_level_one bigint(20) DEFAULT ‘0’ COMMENT ‘Estimated first-level leader (ordinary leader) service fee’,
actual_relate_service_money_level_one bigint(20) DEFAULT ‘0’ COMMENT ‘Actual first-level leader (ordinary leader) service fee’,
relate_service_ratio_level_one decimal(15,5) DEFAULT NULL COMMENT ‘First-level leader (ordinary leader) service fee rate’,
sys_relate_service_money_level_sec bigint(20) DEFAULT ‘0’ COMMENT ‘Estimated second-level leader service fee’,
actual_relate_service_money_level_sec bigint(20) DEFAULT ‘0’ COMMENT ‘Actual second-level leader service fee’,
relate_service_ratio_level_sec decimal(15,5) DEFAULT NULL COMMENT ‘Second-level leader service fee rate’,
leader_sys_service_money decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Estimated service fee for leader income’,
leader_actual_service_money decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Actual service fee for leader income (first-level leader - second-level leader)’,
leader_service_ratio decimal(15,5) DEFAULT ‘0.00000’ COMMENT ‘Service fee rate for leader income’,
relate_technical_service_ratio decimal(15,5) DEFAULT NULL COMMENT ‘Technical service fee rate for leader’,
sys_relate_technical_service_money bigint(20) DEFAULT ‘0’ COMMENT ‘Estimated technical service fee for leader’,
actual_relate_technical_service_money bigint(20) DEFAULT ‘0’ COMMENT ‘Actual technical service fee for leader’,
sys_settle_money bigint(20) DEFAULT ‘0’ COMMENT ‘Estimated settlement amount’,
actual_settle_money bigint(20) DEFAULT ‘0’ COMMENT ‘Actual settlement amount’,
relate_salesman_commission bigint(20) DEFAULT ‘0’ COMMENT ‘Estimated third-party influencer commission’,
actual_relate_salesman_commission bigint(20) DEFAULT ‘0’ COMMENT ‘Actual third-party influencer commission’,
relate_salesman_commission_ratio decimal(15,5) DEFAULT NULL COMMENT ‘Third-party influencer commission rate’,
link_id bigint(20) DEFAULT ‘0’ COMMENT ‘Product link ID’,
order_settle_time datetime DEFAULT NULL COMMENT ‘Order settlement time’,
order_pay_time datetime DEFAULT NULL COMMENT ‘Buyer payment time’,
order_refund_time datetime DEFAULT NULL COMMENT ‘Order refund time’,
order_confirm_time datetime DEFAULT NULL COMMENT ‘Order confirmation time’,
remark varchar(1000) DEFAULT NULL COMMENT ‘Merchant remarks’,
leasing_manager_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Leasing manager ID’,
media_director_id bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘Media manager ID’,
institution_id varchar(100) DEFAULT NULL COMMENT ‘Current order main leader institution ID (ordinary, first-level for first-level leader, second-level for second-level leader)’,
institution_id_level_one varchar(100) DEFAULT NULL COMMENT ‘First-level leader institution ID’,
institution_name_level_one varchar(500) DEFAULT NULL COMMENT ‘First-level leader institution name’,
institution_id_level_sec varchar(100) DEFAULT NULL COMMENT ‘Second-level leader institution ID’,
institution_name_level_sec varchar(500) DEFAULT NULL COMMENT ‘Second-level leader institution name’,
relate_order_source tinyint(2) DEFAULT ‘1’ COMMENT ‘Third-party order source: 1 Other, 2 Window, 3 Video, 4 Live’,
colonel_type tinyint(1) DEFAULT ‘0’ COMMENT ‘Leader type. 0: Ordinary; 1: First-level leader; 2: Second-level leader’,
sales_role tinyint(2) DEFAULT ‘1’ COMMENT ‘Influencer account role 1 Influencer, 2 Second-level leader’,
platform_order_xxg_sample_apply tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘Platform order type (sample delivery, ordinary leader influencer sample delivery, first-level leader leader sample delivery), 0 No, 1 Yes.’,
platform_order_xxg_apply_activity_sec tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘Platform order type (second-level leader registration, first-level leader active registration, second-level leader passive registration), 0 No, 1 Yes.’,
media_id varchar(255) DEFAULT NULL COMMENT ‘Video/Live room ID’,
promote_terminal varchar(100) DEFAULT NULL COMMENT ‘Promotion terminal: Douyin, Huoshan’,
relate_user_no varchar(100) DEFAULT ‘0’ COMMENT ‘Third-party influencer number’,
relate_user_id varchar(100) DEFAULT ‘0’ COMMENT ‘Third-party influencer ID’,
operator varchar(100) NOT NULL COMMENT ‘Operator’,
create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘Creation time’,
update_time datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘Update time’,
display_to_user tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘Display to user, -1 Do not display, 0 Display’,
platform_sys_service_money decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Estimated public leader service fee (before deducting agency service fee)’,
platform_actual_service_money decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Actual public leader service fee (before deducting agency service fee)’,
platform_service_ratio decimal(15,5) DEFAULT ‘0.00000’ COMMENT ‘Public leader service fee ratio (before deducting agency service fee)’,
pusher_sys_service_money decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Estimated pusher service fee’,
pusher_actual_service_money decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Actual pusher service fee’,
pusher_divide_ratio decimal(15,5) DEFAULT ‘0.00000’ COMMENT ‘Pusher service fee ratio (agency leader service fee rate * pusher share ratio)’,
virtual_leader tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘Is it an agency leader, 1 Yes, 0 No’,
pusher_order tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘Is it a pusher order, 1 Yes, 0 No’,
platform_settle_status tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘Xingxuan settlement status: Unsettled: 0, Settled: 1, No need to settle: 2’,
platform_settle_time datetime DEFAULT NULL COMMENT ‘Xingxuan order settlement time’,
team_pusher_sys_service_money decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Total estimated service fee for team pushers (excluding order pushers)’,
team_pusher_actual_service_money decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Total actual service fee for team pushers (excluding order pushers)’,
team_pusher_divide_ratio decimal(15,5) DEFAULT ‘0.00000’ COMMENT ‘Total service fee share ratio for team pushers (excluding order pushers)’,
all_pusher_sys_service_money decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Estimated service fee for all pushers (including order pushers)’,
all_pusher_actual_service_money decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Actual service fee for all pushers (including order pushers)’,
all_pusher_divide_ratio decimal(15,5) DEFAULT ‘0.00000’ COMMENT ‘Service fee share ratio for all pushers (including order pushers)’,
virtual_leader_divide_ratio decimal(15,5) DEFAULT ‘0.00000’ COMMENT ‘Agency leader share ratio’,
xxg_sys_profit decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Estimated profit of Xingxuan officer = Estimated public leader service fee - Estimated agency service fee’,
xxg_actual_profit decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Actual profit of Xingxuan officer = Actual public leader service fee - Actual agency service fee’,
virtual_leader_sys_profit decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Estimated profit of agency leader = Estimated agency service fee - Estimated order pusher service fee - Estimated team pusher service fee’,
virtual_leader_actual_profit decimal(15,4) DEFAULT ‘0.0000’ COMMENT ‘Actual profit of agency leader = Actual agency service fee - Actual order pusher service fee - Actual team pusher service fee’,
PRIMARY KEY (id,tenant_id) /*T![clustered_index] NONCLUSTERED */,
UNIQUE KEY relate_order_no_tenant_id (relate_order_no,tenant_id,promote_platform_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=111818750 /*T! SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=2 */ COMMENT=‘Leader order’
PARTITION BY RANGE (tenant_id)
(PARTITION p3 VALUES LESS THAN (4),
PARTITION p4 VALUES LESS THAN (6),
PARTITION p6 VALUES LESS THAN (9),
PARTITION p9 VALUES LESS THAN (16),
PARTITION p16 VALUES LESS THAN (24),
PARTITION p24 VALUES LESS THAN (25),
PARTITION p25 VALUES LESS THAN (29),
PARTITION p29 VALUES LESS THAN (30),
PARTITION p30 VALUES LESS THAN (39),
PARTITION p39 VALUES LESS THAN (46),
PARTITION p46 VALUES LESS THAN (47),
PARTITION p47 VALUES LESS THAN (49),
PARTITION p49 VALUES LESS THAN (50),
PARTITION p50 VALUES LESS THAN (51),
PARTITION p51 VALUES LESS THAN (57),
PARTITION p57 VALUES LESS THAN (58),
PARTITION p58 VALUES LESS THAN (61),
PARTITION p61 VALUES LESS THAN (62),
PARTITION p62 VALUES LESS THAN (63),
PARTITION p63 VALUES LESS THAN (65),
PARTITION p65 VALUES LESS THAN (67),
PARTITION p67 VALUES LESS THAN (68),
PARTITION p68 VALUES LESS THAN (70),
PARTITION p70 VALUES LESS THAN (74),
PARTITION p74 VALUES LESS THAN (85),
PARTITION p85 VALUES LESS THAN (90),
PARTITION p90 VALUES LESS THAN (97),
PARTITION p97 VALUES LESS THAN (99),
PARTITION p99 VALUES LESS THAN (101),
PARTITION p101 VALUES LESS THAN (104),
PARTITION p104 VALUES LESS THAN (105),
PARTITION p105 VALUES LESS THAN (106),
PARTITION p106 VALUES LESS THAN (107),
PARTITION p107 VALUES LESS THAN (109),
PARTITION p109 VALUES LESS THAN (110),
PARTITION p110 VALUES LESS THAN (114),
PARTITION p114 VALUES LESS THAN (115),
PARTITION p115 VALUES LESS THAN (118),
PARTITION p118 VALUES LESS THAN (120),
PARTITION p120 VALUES LESS THAN (121),
PARTITION p121 VALUES LESS THAN (123),
PARTITION p123 VALUES LESS THAN (129),
PARTITION p129 VALUES LESS THAN (130),
PARTITION p130 VALUES LESS THAN (135),
PARTITION p135 VALUES LESS THAN (142),
PARTITION p142 VALUES LESS THAN (147),
PARTITION p147 VALUES LESS THAN (152),
PARTITION p152 VALUES LESS THAN (155),
PARTITION p155 VALUES LESS THAN (157),
PARTITION p157 VALUES LESS THAN (158),
PARTITION p158 VALUES LESS THAN (161),
PARTITION p161 VALUES LESS THAN (165),
PARTITION p165 VALUES LESS THAN (170),
PARTITION p170 VALUES LESS THAN (175),
PARTITION p175 VALUES LESS THAN (180),
PARTITION p180 VALUES LESS THAN (184),
PARTITION p184 VALUES LESS THAN (187),
PARTITION p187 VALUES LESS THAN (190),
PARTITION p190 VALUES LESS THAN (194),
PARTITION p194 VALUES LESS THAN (200),
PARTITION p200 VALUES LESS THAN (204),
PARTITION p204 VALUES LESS THAN (207),
PARTITION p207 VALUES LESS THAN (210),
PARTITION p210 VALUES LESS THAN (214),
PARTITION p214 VALUES LESS THAN (217),
PARTITION p217 VALUES LESS THAN (220),
PARTITION p220 VALUES LESS THAN (224),
PARTITION p224 VALUES LESS THAN (227),
PARTITION p227 VALUES LESS THAN (230),
PARTITION p230 VALUES LESS THAN (234),
PARTITION p234 VALUES LESS THAN (237),
PARTITION p237 VALUES LESS THAN (240))

| username: dba-kit | Original post link

Actually, the error message is quite clear. You executed a partition statement on a non-partitioned table. It seems that on the downstream TiDB, this table is not a partitioned table, right?

| username: dba-kit | Original post link

You can refer to: TiDB Data Migration Binlog 事件过滤 | PingCAP 文档中心, to filter out all partition-related operations from the upstream MySQL.

| username: foxchan | Original post link

The downstream table is a partitioned table, but DM does not recognize it.

| username: WalterWj | Original post link

In TiDB, create a table using “CREATE LIKE” and then execute this DDL on the newly created table to see if there is an error. If there is no error, it means that the sync parser code in DM might be relatively old. If there is an error, it means the syntax is not supported.

| username: dba-kit | Original post link

Try executing binlog-schema update to refresh the schema cache and see if it works?

| username: foxchan | Original post link

I found the reason. A colleague modified the field update using a regular table. The schema in DM is outdated.

| username: dba远航 | Original post link

Executing partition statements on non-partitioned tables is not supported. Check the table structure.

| username: WinterLiu | Original post link

Thank you for sharing, I have learned a lot.

| username: wangccsy | Original post link

Ensuring that the synchronized tables are consistent should prevent issues. Either partition all of them or none of them.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.