INSERT INTO ON DUPLICATE KEY UPDATE失效问题

[xxxxxxxx (Hacker Z Vu Xy Nh8)]

tidb 4.0.13

sql_mode STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

业务表：有一个自增列主键，有一个唯一联合索引uk_name(a,b,c)，数据类型分别是bigint(20) varchar(100) varchar(20)。

字符集：utf8mb4

排序集：utf8mb4_bin

现象：有个业务表出现部分 INSERT INTO ON DUPLICATE KEY UPDATE（执行的时候提示Query OK, 2 rows affected）失效问题（insert不包含主键字段，仅包含联合唯一索引及其他字段，update部分是非唯一索引字段的其他字段）。即，遇到重复值没有执行update部分（因业务数据具有敏感性，不便截图）。

但是在当前实例下新建一个库，使用create table like的方式将业务表在新库新建，然后将这行数据放进去，在新表执行相同的INSERT INTO ON DUPLICATE KEY UPDATE就能达到预期。

而且这个问题在该业务表只有部分数据有问题。

将INSERT INTO ON DUPLICATE KEY UPDATE换成replace也是一样的现象，另外在测试的时候发现，在事务内执行并查询是没问题的，具体流程如下

测试：
1、新开启一个事务，并通过select记录当前数据
2、执行INSERT INTO ON DUPLICATE KEY UPDATE（提示了Query OK, 2 rows affected）
3、再次查询，可以发现和预期的一致（update部分被执行，数据被更新了）
4、提交事务（Query OK, 0 rows affected ）
5、再次查询，发现数据没有落盘，即：还是原来的数据。

后来又测了另一个操作，将INSERT INTO ON DUPLICATE KEY UPDATE 去掉 ON DUPLICATE KEY UPDATE仅保留insert into，会发现直接报唯一键冲突，说明唯一索引约束没问题。

还测了update，以及delete，发现直接update(update table set col1=‘xxx’,col2=‘yyy’ where a = ‘’ and b = ‘’ and c = ‘’)也是不行，delete(delete from table where a = ‘’ and b = ‘’ and c = ‘’)的话也是一样，不同的是，delete执行以后自增列主键会变更。其他字段的数据不变。

所以想请教一下这个问题的排查思路。

追加材料
表结构

更新测试，更新时间字段，从2023-01-11更新为2023-01-12，先通过查询查出满足的结果是25行

可以看到更新语句返回结果是更新了25行，但是实际只更新了三行

在事务内操作

可以看到在事务内是符合预期的，但是提交以后重新再查就还是部分数据被更新。

执行计划

问题总结：
数据进行dml操作的时候，部分数据存在dml异常
1、insert into on duplicate update update ---- 基于唯一键
2、直接update — 基于唯一键的第一个字段进行更新，发现只有部分数据被变更
3、delete（delete执行后主键会变更） — 基于唯一键

只有部分数据有问题，不是所有数据，也不能在其他环境稳定复现

[db_user]

可否把完整ON DUPLICATE KEY UPDATE 语句提供下看看，感觉是语句写的可能有点问题

[特雷西-迈克-格雷迪]

1/2/3 这三步是乐观锁的现象吧

[xxxxxxxx (Hacker Z Vu Xy Nh8)]

数据比较敏感不方便看，语句没问题的，一样的sql格式，其他数据没问题，只是一部分数据有问题

[xxxxxxxx (Hacker Z Vu Xy Nh8)]

general log看到是悲观事务
txn_mode=PESSIMISTIC

[db_user]

请问可否提供下tidb日志可复现的表结构呢

是同一张表操作的时候，有些数据会成功，有些数据不会么

基于的唯一key是什么数据类型，长度多少，另外最好能把表结构，主要是主键和唯一索引的字段信息提供下

[xxxxxxxx (Hacker Z Vu Xy Nh8)]

远远达不到限制的数据长度，而且在同实例下的其他库的一样表结构的表就没问题，而且只是部分数据有问题，刚才又测试出新的问题，直接update也不会更新，而且直接delete也不会被删除掉

update返回 Rows matched: 1 Changed: 1 Warnings: 0
delete返回 Query OK, 1 row affected

[db_user]

用的唯一键是char,varchar,bigint还是其他，唯一索引是一个字段组成还是多个字段组成，集群是否开起新的排序规则，表的排序规则是_bin还是_ci，tidb日志这个操作是否相关日志都是什么，都不能提供的话可以按照这个查查，然后看看github上有没有对应的issue吧，或者都不能提供你自己能验证出最小复现步骤，提供上来也可以

[脚本小王子]

在TiDB v5.3.0按楼主步骤测试未发现问题，楼主能否使用测试数据复现？

我的测试如下：

###################################
#     建表及准备测试数据          #
###################################
mysql> create table t3(id int not null auto_increment primary key, name varchar(30) not null , sex tinyint);
Query OK, 0 rows affected (0.14 sec)

mysql> create unique index uniq_name on t3(name);
Query OK, 0 rows affected (2.92 sec)

mysql> insert into t3 values(1, 'a1', 0), (2,'a2', 1), (3,'a3',0);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | a1   |    0 |
|  2 | a2   |    1 |
|  3 | a3   |    0 |
+----+------+------+
3 rows in set (0.01 sec)


###################################
#     按楼主的测试步骤测试        #
###################################

# 1、新开启一个事务，并通过select记录当前数据
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t3;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | a1   |    0 |
|  2 | a2   |    0 |
|  3 | a3   |    0 |
+----+------+------+
3 rows in set (0.00 sec)


# 2、执行INSERT INTO ON DUPLICATE KEY UPDATE（提示了Query OK, 2 rows affected）
mysql> insert into t3(name, sex) values('a2', 1) ON DUPLICATE KEY UPDATE name=values(name), sex=values(sex); 
Query OK, 2 rows affected (0.01 sec)


# 3、再次查询，可以发现和预期的一致（update部分被执行，数据被更新了）
mysql> select * from t3;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | a1   |    0 |
|  2 | a2   |    1 |
|  3 | a3   |    0 |
+----+------+------+
3 rows in set (0.01 sec)

# 4、提交事务（Query OK, 0 rows affected ）
mysql> commit;
Query OK, 0 rows affected (0.01 sec)


# 5、再次查询，数据是【有落盘】，此处与楼主的不一致（楼主测试结论为：5、再次查询，发现数据没有落盘，即：还是原来的数据。）
mysql> select * from t3;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | a1   |    0 |
|  2 | a2   |    1 |
|  3 | a3   |    0 |
+----+------+------+
3 rows in set (0.00 sec)


# 测试环境
mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v5.3.0 |
+--------------------+
1 row in set (0.00 sec)

[xxxxxxxx (Hacker Z Vu Xy Nh8)]

更新了内容，烦请重新看一下帖子

[db_user]

辛苦看一下这个参数是否开启：
show config where name like ‘%new_collations_enabled_on_first_bootstrap%’;

另外看一下你不起作用的那条数据大小写，lower(),upper()的时候会不会不起作用，感觉像是长varchar在不同排序规则的时候没有识别出来，我再搜搜issue看看