pingcap / pingcap/tidb

Assertion Key failed when loading data from file with replace

Open
#37,311 0 comments 0 reactions 0 assignees View on GitHub
affects-6.1 affects-6.3 affects-6.4 affects-6.5 severity/moderate sig/transaction type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

`load replace grammar` can not take the right action.
MySQL actions are below:
![image](https://user-images.githubusercontent.com/76909042/186116875-9ea9adcb-9163-4618-ad6f-d6167311540d.png)

### 1. Minimal reproduce step (Required)
```
CREATE TABLE `account` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into account values (123456789,'aaa');
insert into account values (12345678,'bbb');
insert into account values (1234567,'ccc');
insert into account values (123456,'ddd');
insert into account values (12345,'eee');

SELECT * FROM account INTO OUTFILE '/home/tidb/mysql57/runoob3.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
mysql> truncate table test.account ;
Query OK, 0 rows affected (0.09 sec)

mysql> LOAD DATA local INFILE '/home/tidb/mysql57/runoob3.txt' replace INTO TABLE test.account FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

mysql> LOAD DATA local INFILE '/home/tidb/mysql57/runoob3.txt' replace INTO TABLE test.account FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
ERROR 8141 (HY000): assertion failed: key: 74800000000000007b5f728000000000003039, assertion: NotExist, start_ts: 435485041765122052, existing start ts: 435485040415080450, existing commit ts: 435485040415080451
```

### 2. What did you expect to see? (Required)
```
mysql> LOAD DATA local INFILE '/home/tidb/mysql57/runoob3.txt' replace INTO TABLE test.account FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
```

### 3. What did you see instead (Required)
```
mysql> LOAD DATA local INFILE '/tmp/runoob3.txt' replace INTO TABLE test.account FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
ERROR 8141 (HY000): assertion failed: key: 7480000000000000795f728000000000003039, assertion: NotExist, start_ts: 435484900482613250, existing start ts: 435484893837524993, existing commit ts: 435484893837524994
```
### 4. What is your TiDB version? (Required)

tidb version == v6.1.0
anyway mysql client (v5.7 or v8)

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the reported sequence on TiDB v6.1.0: create the account table, export its rows, truncate it, then run the LOAD DATA LOCAL INFILE ... REPLACE command twice. Trace the LOAD DATA REPLACE path when the second load raises assertion 8141. Done means the repeated load succeeds with five affected rows and no assertion failure.

Written by the indexing model from the issue text.

Assessment

Tech stack
mysql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.