Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: V6.5.3 BR备份、PITR恢复无主键、唯一索引的NONCLUSTERED表完成后，插入数据触发ERROR 1062 (23000): Duplicate entry ‘xxx’ for key ‘xxx.PRIMARY’
|
username: IanWong
Bug Report
Clearly and accurately describe the issue you found. Providing any steps to reproduce the issue can help the development team address it promptly.
[TiDB Version] V6.5.3
[Bug Impact] Error when inserting new data
mysql> insert into aaa values(9000,now());
ERROR 1062 (23000): Duplicate entry ‘30001’ for key ‘aaa.PRIMARY’
[Possible Steps to Reproduce the Issue]
BR backup and restore of a NONCLUSTERED table without primary key or unique index, incorrect setting of hidden primary key _tidb_rowid causing errors when inserting new data.
- Original table data
mysql> show create table aaa;
±------±-------------+
| Table | Create Table |
±------±--------------+
| aaa | CREATE TABLEaaa(
idint(11) DEFAULT NULL,
namevarchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
±------±---------------+
1 row in set (0.18 sec)
mysql> select *,_tidb_rowid from aaa;
±-----±--------------------±------------+
| id | name | _tidb_rowid |
±-----±--------------------±------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 4 | 30001 |
| 5 | 5 | 30002 |
| 6 | 2024-03-18 11:07:54 | 30003 |
| 7 | 2024-03-18 11:08:05 | 30004 |
| 88 | 2024-03-18 11:11:58 | 30005 |
| 89 | 2024-03-18 11:12:03 | 30006 |
| 890 | 2024-03-18 11:17:20 | 30007 |
| 891 | 2024-03-18 11:17:24 | 30008 |
| 892 | 2024-03-18 11:17:27 | 30009 |
| 892 | 2024-03-18 11:22:28 | 30010 |
±-----±--------------------±------------+
13 rows in set (0.01 sec)
mysql> show table aaa next_row_id\G
*************************** 1. row ***************************
DB_NAME: test
TABLE_NAME: aaa
COLUMN_NAME: _tidb_rowid
NEXT_GLOBAL_ROW_ID: 60001
ID_TYPE: _TIDB_ROWID
1 row in set (0.00 sec)
2. Perform BR:V6.5.3 full backup
3. Perform BR restore
4. Query data after restoring BR database or table
mysql> select *,_tidb_rowid from aaa;
±-----±--------------------±------------+
| id | name | _tidb_rowid |
±-----±--------------------±------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 4 | 30001 |
| 5 | 5 | 30002 |
| 6 | 2024-03-18 11:07:54 | 30003 |
| 7 | 2024-03-18 11:08:05 | 30004 |
| 88 | 2024-03-18 11:11:58 | 30005 |
| 89 | 2024-03-18 11:12:03 | 30006 |
| 890 | 2024-03-18 11:17:20 | 30007 |
| 891 | 2024-03-18 11:17:24 | 30008 |
| 892 | 2024-03-18 11:17:27 | 30009 |
| 892 | 2024-03-18 11:22:28 | 30010 |
±-----±--------------------±------------+
13 rows in set (0.00 sec)
mysql> show table aaa next_row_id\G
*************************** 1. row ***************************
DB_NAME: test
TABLE_NAME: aaa
COLUMN_NAME: _tidb_rowid
NEXT_GLOBAL_ROW_ID: 30001
ID_TYPE: _TIDB_ROWID
1 row in set (0.00 sec)
[Observed Unexpected Behavior]
_tidb_rowid greater than the maximum value of the original table, subsequent insertions succeed;
[Expected Behavior]
Normal data insertion and _tidb_rowid advancement after BR backup and restore
[Related Components and Specific Versions]
TIDB: V6.5.3
BR: V6.5.3
[Other Background Information or Screenshots]
Tested BR: V6.5.8, same result after restore
Such as cluster topology, system and kernel version, application app information, etc.; if the issue is related to SQL, please provide SQL statements and related table schema information; if there are critical errors in node logs, please provide relevant node log content or files; if some business-sensitive information is inconvenient to provide, please leave contact information for private communication.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: