"ERROR 1062 (23000): Duplicate entry" on data insert for the truncated table restored with TiDB BR restore

[jyteh407]

Bug Report

1. Minimal reproduce step (Required)

1. Backup a database with tidb br backup to AWS S3 .
2. Truncate a table of the database .
3. Restore the table from backup stored on AWS S3 with tidb br restore .
4. restoration succeeded , however , encountered error when inserting data into the table after restoration . It shows error as below . After several attempts, insert data only can happened .
   

• There ain't any primary /unique key for the testing table .

5. Check from the tidb log , it shows as below.

[2023/03/14 15:11:17.552 +08:00] [INFO] [conn.go:1152] ["command dispatched failed"] [conn=359373176515330469] [connInfo="id:359373176515330469, addr:172.23.99.214:59182 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="insert into t3 values ("22")"] [txn_mode=PESSIMISTIC] [timestamp=440081547929321473] [err="[kv:1062]Duplicate entry '1' for key 't3.PRIMARY'"]
[2023/03/14 15:11:20.477 +08:00] [INFO] [conn.go:1152] ["command dispatched failed"] [conn=359373176515330469] [connInfo="id:359373176515330469, addr:172.23.99.214:59182 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="insert into t3 values ("23")"] [txn_mode=PESSIMISTIC] [timestamp=440081548689276929] [err="[kv:1062]Duplicate entry '2' for key 't3.PRIMARY'"]
[2023/03/14 15:11:27.935 +08:00] [INFO] [conn.go:1152] ["command dispatched failed"] [conn=359373176515330469] [connInfo="id:359373176515330469, addr:172.23.99.214:59182 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="insert into t3 values ("24")"] [txn_mode=PESSIMISTIC] [timestamp=440081550642249732] [err="[kv:1062]Duplicate entry '3' for key 't3.PRIMARY'"]
[2023/03/14 15:11:31.369 +08:00] [INFO] [conn.go:1152] ["command dispatched failed"] [conn=359373176515330469] [connInfo="id:359373176515330469, addr:172.23.99.214:59182 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="insert into t3 values ("25")"] [txn_mode=PESSIMISTIC] [timestamp=440081551546646531] [err="[kv:1062]Duplicate entry '4' for key 't3.PRIMARY'"]
[2023/03/14 15:11:34.019 +08:00] [INFO] [conn.go:1152] ["command dispatched failed"] [conn=359373176515330469] [connInfo="id:359373176515330469, addr:172.23.99.214:59182 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="insert into t3 values ("26")"] [txn_mode=PESSIMISTIC] [timestamp=440081552241328132] [err="[kv:1062]Duplicate entry '5' for key 't3.PRIMARY'"]
[2023/03/14 15:11:35.730 +08:00] [INFO] [conn.go:1152] ["command dispatched failed"] [conn=359373176515330469] [connInfo="id:359373176515330469, addr:172.23.99.214:59182 status:10, collation:utf8_general_ci, user:root"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="insert into t3 values ("26")"] [txn_mode=PESSIMISTIC] [timestamp=440081552686972929] [err="[kv:1062]Duplicate entry '6' for key 't3.PRIMARY'"]

2. What did you expect to see? (Required)

For the truncated table , after br restore , insert data onto the table should be working as normal.

3. What did you see instead (Required)

It shows error as "ERROR 1062 (23000): Duplicate entry 'x' for key 'xxx'

4. What is your TiDB version? (Required)

6.5.0

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.5.0
Edition: Community
Git Commit Hash: 706c3fa
Git Branch: heads/refs/tags/v6.5.0
UTC Build Time: 2022-12-27 03:50:44
GoVersion: go1.19.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

MySQL [bookshop]>

[magicJie]

I reported the same bug:
https://asktug.com/t/topic/1006530/2

[3pointer]

it's a bug, you can try upgrade br to v6.5.4. see details at #46093

[BornChanger]

@magicJie can you please verify it nightly br can fix the problem?

[magicJie]

@magicJie can you please verify it nightly br can fix the problem?

I am currently using version 6.5.3, and this version does not have this bug.

[BornChanger]

/close

[ti-chi-bot]

@BornChanger: Closing this issue.

In response to this:

/close

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.

[JasonWu0506]

/label affects-6.5