# [Critical bug] INSERT ON DUPLICATE插入多行重复数据可能索引与表数据不一致的问题声明

## Issue

<https://github.com/pingcap/tidb/pull/14383> 中对 union scan 进行了性能优化。在显式开启的事务内，当一条 INSERT ON DUPLICATE KEY UPDATE 语句同时插入多行数据，并且这些数据中存在重复的唯一键/主键时，创建索引的函数只读取了语句之前事务内的数据，没有读取语句本身也要写入的数据，当数据需要更新，并且更新后的值与原值不同后会发生漏写索引的现象，造成索引与表数据不一致问题。详见触发条件举例。

### 触发条件如下

1. 非 autocommit=1 的事务。（即使用 begin/commit 或者 autocommit=0，autocommit=1 来提交的事务）

2. 使用 insert on duplicate key update 语句 a. 单条 insert 语句内写入多行重复数据，且重复数据在写入前在表中不存在 b. 且多行数据中有唯一索引冲突，且冲突的数据是之前不存在的

示例如下：

```sql
create table t (a varchar(10) primary key, b int);

begin;
insert into t values ('a', 1), ('b', 3), ('a', 2) on duplicate key update b = b + 1;
commit;

-- 或者

set autocommit=0;
insert into t values ('a', 1), ('b', 3), ('a', 2) on duplicate key update b = b + 1;
set autocommit=1;

-- 结果：

-- SQL 预期结果 实际结果 说明

select * from t ignore index (`primary`);
+---+------+
| a | b    |
+---+------+
| a |    2 |
| b |    3 |
+---+------+
2 rows in set (0.00 sec)
+---+------+
| a | b    |
+---+------+
| a |    2 |
| b |    3 |
+---+------+
2 rows in set (0.00 sec)

-- 通过表查，数据满足预期，均读到 2 条

select a from t use index (`primary`);
+---+
| a |
+---+
| a |
| b |
+---+
2 rows in set (0.00 sec)
+---+
| a |
+---+
| b |
+---+
1 row in set (0.00 sec)
```

通过主键或者唯一键查询时，只读索引列，实际只能读到 1 条

### 检查步骤

1. 检查是否使用 insert on duplicate key update 语句（包括 insert ignore on duplicate key update）
2. 检查是否 insert on duplicate key update 语句一次插入多行数据
3. 检查单条 insert on duplicate key update 语句插入的多行数据之间是否可能有主键或唯一索引重复的数据
4. 检查是否是显式开启事务或在 autocommit = 0 的状态执行 insert on duplicate key update 语句 如果上述都是的话，执行 admin check table table_name; 检查是否报错。

## Root Cause

在显式开启的事务内，当一条 INSERT ON DUPLICATE KEY UPDATE 语句同时插入多行数据，并且这些数据中存在重复的唯一键/主键时，创建索引的函数只读取了语句之前事务内的数据，没有读取语句本身也要写入的数据，当数据需要更新，并且更新后的值与原值不同后会发生漏写索引的现象，造成索引与表数据不一致问题。

## Diagnostic Steps

具体步骤示例

```sql
-- 情况 1

create table t (a varchar(10), b int, unique index idx(a));
Begin;
insert into t values ('a', 1), ('b', 3), ('a', 2) on duplicate key update b = b + 1;
commit;

-- 上面最终写入了 ('b', 3), ('a', 2) 两行数据，但是索引只写入了 ('b', 3) 行的索引。

-- 情况 2

-- 接着情况 1， 由于 ('a', 2) 行没有写索引，所以继续写入 ('a', 3) 行能成功，不会触发唯一索引的冲突：

insert into t values ('a', 3);
select * from t;
+---+---+
| a | b |
+---+---+
| a | 2 |
| b | 3 |
| a | 3 |
+---+---+

select * from t use index(idx);
+---+---+
| a | b |
+---+---+
| b | 3 |
| a | 3 |
+---+---+

-- ('a', 3) 行的写入成功后打破了唯一索引的约束，目前有 3 行数据，但只有 2 条索引。
```

### 手动恢复

步骤如下，其中步骤 2 用于恢复问题情况 1， 步骤 3,4 用于恢复问题情况 2.

1. 导出表数据进行备份

2. admin recover index [table_name] [index_name]; 恢复丢失的索引。

3. 查找不符合唯一索引约束的重复行。 a. 首先查找没有 idx 索引的重复数据行，因为重复行不符合唯一索引约束，导致第一步补索引失败。示例 SQL 如下：

```sql
select *, _tidb_rowid from t where _tidb_rowid not in 
(select _tidb_rowid from t use index (`idx`));

+---+---+-------------+
| a | b | _tidb_rowid |
+---+---+-------------+
| a | 2 | 1           |
+---+---+-------------+
```

b. 根据上面查找出的重复行，用唯一索引的 column 值作为过滤条件查找有索引的重复行，示例如下：

```sql
select *,_tidb_rowid from t use index(idx) where a='a';

+---+---+-------------+
| a | b | _tidb_rowid |
+---+---+-------------+
| a | 3 | 3           |
+---+---+-------------+
```

用步骤3找出所有不符合唯一索引 idx 约束的行，如 ('a', 3) 和 ('a', 2)，其 _tidb_rowid 分别是 1 和 3，需要先删除这 2 行重复的数据，然后用户根据业务逻辑判断重新写入 ('a', 3) 或者 ('a', 2)，或者其他的行数据。 delete from t where _tidb_rowid in (1,3); #删除重复行

用户根据实际业务场景，对重复行的数据进行处理后再重新导入数据库。

```sql
insert into t values ('a', ?);  ## 重新写入数据
```

### 工具恢复

手动恢复的步骤有些繁琐，可以用 脚本程序 来恢复。

运行脚本的前需要创建一张和故障表一样的表结构的临时表，但需要把临时表的主键索引，唯一索引的列给删除，并且这个临时表是空表。临时表用于保存出问题的行数据。

如对于上面表 t, 移除相应的索引定义后，新建临时表 tmp_t 如下：

```sql
create table tmp_t (a varchar(10), b int);
````

然后用以下命令执行脚本：

```shell
go run fix.go -addr=127.0.0.1:4000 -db=test -table=t -index=idx --tmp-table=tmp_t
```

参数说明：

```yaml
addr: TiDB 的地址

user: 连接数据库的用户名

passwd: 用户密码

db: 表所在的 database 名

table: 表名

index: 问题索引的索引名

Tmp-table：临时表表名
```

另外，如果故障表的 primary key 是 int/bigint 类型, 需要指定 --handle-column=主键列名。假如故障表 t1 表结构是 create table t1 (id int primary key, b varchar(10), unique index idx(b)), 则执行脚本命令如下：

```shell
go run fix.go -addr=127.0.0.1:4000 -db=test -table=t2 -index=idx --tmp-table=tmp_t2 --handle-column=id
```

以上脚本做几下几件事：

1. 查找有问题的行数据，包括重复行和缺失索引的行数据
2. 将查找出来的有问题的数据写入临时表
3. 将有问题的行数据在原表中删除
4. 使用 insert ignore 将临时表中的数据重新写回原表
    1. 对于重复行，会保留最新写入的行数据
    2. 对于缺失索引的行，删除后重新该行会重建索引

如用户需要对于重复行进行特殊的处理，可以修改临时表中的重复行数据后，再将临时表中的数据重新导入。

> **注意**
>
> 如需并行修复多个表，不要再同一个目录下面并行运行该脚本，需要在不同的文件目录下执行工具脚本。

## Resolution

1. 导出表数据进行备份。

    备份时注意通过表数据来备份，因为索引是存在缺失的，表数据是完整的。最简单的方式是直接 select * from t。

2. 恢复缺失的索引，执行

    ```sql
    admin recover index [table_name] [index_name];
    ```

    对于相同唯一索引列的数据，因为打破了唯一约束，无法自动补齐。

3. 执行如下 SQL 得到所有没有成功恢复的唯一索引的数据

    ```sql
    select *, _tidb_rowid from [table_name] where _tidb_rowid not in 
    (select _tidb_rowid from [table_name] use index ([index_name]));
    ```

   1. 导出所有重复行数据，再用 _tidb_rowid 将重复行数据删除，

   2. 用户根据实际业务场景，对重复行的数据进行处理后再重新导入数据库。

## Workaround

N/A