Incremental Synchronization Exception in DM -- Triggered by Irrelevant Tables in Upstream MySQL Binlog

Translated
1

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

Original topic: DM增量同步异常 --上游mysql binlog 无关表触发

| username: 郑旭东石家庄

Bug Report
I set up a DM synchronization to sync 4 tables from the pt1 database in the upstream MySQL to TiDB, with incremental synchronization. I set the binlog file and position to the latest log file and starting point in MySQL. After starting the synchronization, an error occurred. The error message indicated that there was a table creation syntax error in the hp_user_1 table in the peixun database. However, my synchronization task is unrelated to the peixun database; it’s just that these two databases are in the same binlog. I had no choice but to use a filter in the configuration file, but the issue persisted.

【TiDB Version】 7.5.1
【Impact of the Bug】 Unable to perform incremental data synchronization

【Possible Steps to Reproduce the Issue】

【Observed Unexpected Behavior】
The binlog parser checked unrelated tables

【Expected Behavior】

【Related Components and Specific Versions】

【Other Background Information or Screenshots】
demo.yaml (926 bytes)
Query Task Error Information.txt (4.5 KB)

2
| username: Billmay表妹 | Original post link

Can it be stably reproduced?

3
| username: WalterWj | Original post link

It should be because the DDL parsing fails, you need to parse it first and then filter it :thinking:. 使用 TiDB Data Migration 处理出错的 DDL 语句 | PingCAP 文档中心 Manually skip it.

4
| username: 郑旭东石家庄 | Original post link

Stable reproduction

5
| username: Billmay表妹 | Original post link

Refer to the method used by the teacher above~

6
| username: 郑旭东石家庄 | Original post link

Using the above commands can indeed skip the previous table, but there are 3 similar tables. After executing the above commands multiple times, I found that the error cycles among these tables repeatedly. Currently, there is an error with table A, then after executing the above set of commands, it changes to table A2. Executing again changes the error to table A3, and executing once more changes the error back to table A2, and then back to table A1. This goes on back and forth.

7
| username: WalterWj | Original post link

Which database are these tables under?

8
| username: WalterWj | Original post link

Can I understand that this actually doesn’t need to be configured? I see you have configured do-tables. :face_with_peeking_eye:

9
| username: 郑旭东石家庄 | Original post link

Yes, this doesn’t work. I first configured the do-tables information. Later, an error occurred, so I added a filter, but I found that adding it didn’t work either. The error occurred directly when parsing the binlog. I tried to skip it, but the error kept recurring in a loop.

10
| username: WalterWj | Original post link

Which database are these tables under?

11
| username: 郑旭东石家庄 | Original post link

It’s within the same instance, no need to synchronize the database. schema-pattern: “peixun” This is the database name.

12
| username: IanWong | Original post link

Please provide the version of the upstream MySQL.

13
| username: 郑旭东石家庄 | Original post link

5.7.25

14
| username: 郑旭东石家庄 | Original post link

After testing, the first statement is generated after parsing by TiDB and cannot be executed in TiDB.
CREATE TABLE IF NOT EXISTS hp_user_7 ( id varchar(50) NOT NULL, phone varchar(50) DEFAULT NULL COMMENT ‘User phone number’, app_code varchar(50) DEFAULT NULL COMMENT ‘Function code’, app_type varchar(50) DEFAULT NULL COMMENT ‘Icon type: 1 large icon, 0 small icon, 2 medium icon, 3 shortcut area’, page varchar(8) DEFAULT NULL COMMENT ‘Location information: page’, row varchar(8) DEFAULT NULL COMMENT ‘Location information: row’, col varchar(8) DEFAULT NULL COMMENT ‘Location information: column’, sort int(10) DEFAULT NULL COMMENT ‘Sort’, create_time datetime DEFAULT NULL COMMENT ‘Creation time’, update_time datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘Update time’, PRIMARY KEY ( id ), UNIQUE KEY IDX_APPCODE ( phone, app_code )) ENGINE=InnoDB DEFAULT CHARSET=utf8]: parse DDL: CREATE TABLE IF NOT EXISTS enuo365_hp_user_7 ( id varchar(50) NOT NULL, phone varchar(50) DEFAULT NULL COMMENT ‘User phone number’, app_code varchar(50) DEFAULT NULL COMMENT ‘Function code’, app_type varchar(50) DEFAULT NULL COMMENT ‘Icon type: 1 large icon, 0 small icon, 2 medium icon, 3 shortcut area’, page varchar(8) DEFAULT NULL COMMENT ‘Location information: page’, row varchar(8) DEFAULT NULL COMMENT ‘Location information: row’, col varchar(8) DEFAULT NULL COMMENT ‘Location information: column’, sort int(10) DEFAULT NULL COMMENT ‘Sort’, create_time datetime DEFAULT NULL COMMENT ‘Creation time’, update_time datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘Update time’, PRIMARY KEY ( id ), UNIQUE KEY IDX_APPCODE ( phone, app_code )) ENGINE=InnoDB DEFAULT CHARSET=utf8"

The following is generated by MySQL 5.7.25 and can be executed in TiDB. It is suggested whether to add `` when parsing binlog.
CREATE TABLE hp_user_7 (
id varchar(50) NOT NULL,
phone varchar(50) DEFAULT NULL COMMENT ‘User phone number’,
app_code varchar(50) DEFAULT NULL COMMENT ‘Function code’,
app_type varchar(50) DEFAULT NULL COMMENT ‘Icon type: 1 large icon, 0 small icon, 2 medium icon, 3 shortcut area’,
page varchar(8) DEFAULT NULL COMMENT ‘Location information: page’,
row varchar(8) DEFAULT NULL COMMENT ‘Location information: row’,
col varchar(8) DEFAULT NULL COMMENT ‘Location information: column’,
sort int(10) DEFAULT NULL COMMENT ‘Sort’,
create_time datetime DEFAULT NULL COMMENT ‘Creation time’,
update_time datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘Update time’,
PRIMARY KEY (id),
UNIQUE KEY IDX_APPCODE (phone,app_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After the table is created, binlog parsing is still incorrect.

15
| username: WalterWj | Original post link

If it can be executed… it might be that the parser version in DM is too low. What version of DM are you using?

16
| username: IanWong | Original post link

The error in the statement is due to the keyword issue with the field “row”. Testing with MySQL 5.7.28, the table creation statement executes normally, and the binlog parsing result is as follows:

image
image1117×703 20.9 KB

In this statement, “row” is not enclosed in escape characters, causing DM parsing to fail due to syntax incompatibility with TiDB, resulting in an error.
When testing MySQL table creation with the “row” field enclosed in escape characters, the binlog also includes the escape characters. Parsing through DM succeeds, and it executes normally in the downstream TiDB.
image
image964×127 4.95 KB

Therefore, this is not a product bug but an issue of syntax incompatibility between MySQL and TiDB.

  1. In MySQL 5.7.28: the field name “row” or “row” are equivalent and both execute successfully.
  2. In TiDB: the field name “row” is a keyword and does not execute successfully unless enclosed in escape characters as “row”.
17
| username: 郑旭东石家庄 | Original post link

The real issue is that the data to be migrated to TiDB is production data, which means that the table creation syntax in the MySQL database cannot be adjusted.

18
| username: IanWong | Original post link

Current recommended solutions:

  1. Permanent solution: Investigate syntax compatibility issues, standardize MySQL statements at the source, and use statements compatible with both MySQL and TiDB.
  2. Temporary solution: Handle incompatibility issues with special treatment, referring to the solution mentioned above “Using TiDB Data Migration to handle erroneous DDL statements.”
19
| username: 郑旭东石家庄 | Original post link

Can’t skip, keeps repeating

tiup dmctl --master-addr 192.168.165.25:8261 binlog skip ycspt
tiup dmctl --master-addr 192.168.165.25:8261 resume-task ycspt
tiup dmctl --master-addr 192.168.165.25:8261 query-status ycspt
Using the above commands can indeed skip the previous table, but there are 3 similar tables. After executing the above commands multiple times, I found that the error cycles among these tables repeatedly. Now it’s table A with an error, then after executing the above set of commands, it changes to table A2, then after executing again it changes to table A3, then after executing again it changes back to table A2, and then after executing again it changes back to table A1. It goes back and forth like this.

Is there a problem with the way I’m executing this?

20
| username: IanWong | Original post link

Check if the binlog position has advanced. If it has, that means it was skipped. If the position is also looping, then there might still be an issue.