Replace Insert Data Failure

Translated
1

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

Original topic: replace 插入数据失败

| username: TiDBer_yyy

[Overview] Using sync-diff-inspector to verify whether the data of two TiDB clusters is consistent
The result shows data inconsistency, and the replace statement fails to execute after being issued.

image
image1380×104 33.5 KB

Unique key:

  PRIMARY KEY (`I_ID`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `FSI` (`I_FILE_ID`,`I_CHANNEL_ID`),

ERROR 1105 (HY000) at line 40950: can not be duplicated row, due to old row not found. handle 2396698 not found
ERROR 1105 (HY000) at line 40951: can not be duplicated row, due to old row not found. handle 2396699 not found
ERROR 1105 (HY000) at line 40952: can not be duplicated row, due to old row not found. handle 2396700 not found
ERROR 1105 (HY000) at line 40953: can not be duplicated row, due to old row not found. handle 2396701 not found
ERROR 1105 (HY000) at line 40956: can not be duplicated row, due to old row not found. handle 2396704 not found
ERROR 1105 (HY000) at line 40957: can not be duplicated row, due to old row not found. handle 2396705 not found
ERROR 1105 (HY000) at line 40964: can not be duplicated row, due to old row not found. handle 2396712 not found
2
| username: gary | Original post link

Why use replace to insert when data is inconsistent?

3
| username: TiDBer_yyy | Original post link

The sync-diff-inspector verification results will generate replace statements.

4
| username: xiaohetao | Original post link

Is the data not found in the cluster? How is the check-struct-only parameter configured? Can you show the configuration file?

5
| username: gary | Original post link

Is it in the testing environment?
In the production environment, the statistics might be inaccurate. You should manually execute analyze table {table_name} and then compare again.

6
| username: TiDBer_yyy | Original post link

Got it! Please provide the Chinese text you need translated.

7
| username: TiDBer_yyy | Original post link

You can refer to another post: 双云灾备建设，使用sync-diff-inspector 工具校验后，replace补数据执行失败 - #2，来自 duzq - TiDB 的问答社区

8
| username: TiDBer_yyy | Original post link

2396701 is the primary key value.

9
| username: xiaohetao | Original post link

After reading the post link shared by the teacher above, admin check recover or rebuilding the index can solve the issue.

Which method did you use to solve it?

10
| username: TiDBer_yyy | Original post link

Rebuild index

11
| username: xiaohetao | Original post link

Got it.

12
| username: alfred | Original post link

The issue of inconsistency between the index and data, is it a bug?

13
| username: system | Original post link

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