Percona mysql 8.0.21 unique key column but still insert duplicate data

[anly_zhang]

version 8.0.21
transaction_isolation=READ-COMMITTED

table create statement

CREATE TABLE `t_level_log` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'PK',
`log_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL ,
`login_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL ,
`level_change_before` tinyint NOT NULL COMMENT 'level before',
`level_change_after` tinyint NOT NULL COMMENT 'level after',
`level_integral` bigint NOT NULL ,
`reviced_time` datetime DEFAULT NULL ,
`create_time` datetime NOT NULL ,
`creator` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
`update_time` datetime DEFAULT NULL ,
`updater` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
`approval_time` datetime DEFAULT NULL ,
`approval_by` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
`integral_order_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
PRIMARY KEY (`id` ),
UNIQUE KEY `uk_ll_li` (`log_id` ),
) ENGINE=InnoDB AUTO_INCREMENT=127810 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC

already had data

select * from t_level_log where log_id='LLL25635165';
+--------+-------------+-----------------+---------------------+--------------------+----------------+--------------+---------------------+---------+---------------------+---------+---------------------+-------------+---------------------+
| id     | log_id      | login_name      | level_change_before | level_change_after | level_integral | reviced_time | create_time         | creator | update_time         | updater | approval_time       | approval_by | integral_order_id   |
+--------+-------------+-----------------+---------------------+--------------------+----------------+--------------+---------------------+---------+---------------------+---------+---------------------+-------------+---------------------+
| 118806 | LLL25635165 | 0ix4pk |                   0 |                  1 |             10 | NULL         | 2023-10-11 19:30:26 | SYSTEM  | 2023-10-11 19:30:26 | SYSTEM  | 2023-10-11 19:30:26 | SYSTEM      | 1712068130404188160 |
+--------+-------------+-----------------+---------------------+--------------------+----------------+--------------+---------------------+---------+---------------------+---------+---------------------+-------------+---------------------+
1 row in set (0.01 sec)

an new data insert sucessfull.

# at 7934293
#231017  5:42:05 server id 642064311  end_log_pos 7934698   Rows_query
# INSERT INTO t_level_log  ( log_id,
# login_name,
# integral_order_id,
# level_change_before,
# level_change_after,
# level_integral,
# create_time,
# creator,
# update_time,
# updater,
# approval_time,
# approval_by )  VALUES  ( 'LLL25635165',
# '0k4edt',
# '1714034000538021888',
# 0,
# 1,
# 10,
# '2023-10-17 05:42:05.656',
# 'SYSTEM',
# '2023-10-17 05:42:05.656',
# 'SYSTEM',
# '2023-10-17 05:42:05.656',
# 'SYSTEM' )
# at 7934698
#231017  5:42:05 server id 642064311  end_log_pos 7934792   Table_map: c66_integral.t_level_log mapped to number 165
# at 7934792
#231017  5:42:05 server id 642064311  end_log_pos 7934930   Write_rows: table id 165 flags: STMT_END_F
### INSERT INTO c66_integral.t_level_log
### SET
###   @1=127788 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='LLL25635165' /* VARSTRING(256) meta=256 nullable=0 is_null=0 */
###   @3='0k4edt' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @4=0 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6=10 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
###   @8='2023-10-17 05:42:06' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
###   @9='SYSTEM' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @10='2023-10-17 05:42:06' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @11='SYSTEM' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @12='2023-10-17 05:42:06' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @13='SYSTEM' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @14='1714034000538021888' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */

can anyone give me some help ?

[yunus.uyanik]

Hi anly_zhang,
I couldn’t reproduce it with the same version.
Can you show a little demonstration?
Do you have two records now and are you using some triggers?

[anly_zhang]

I can’t reproduce this problem either, but there is such a situation in the production database.

But it was found that the data was indeed written.
as shown below：

I tried the following but couldn’t reproduce it:

1. Parse the SQL from the binlog and re-execute it, but the data cannot be written.
2. First write a different data, and then update the log_id to existing one, and it will also prompt rejection.

[anly_zhang]

In addition, if you use equivalent query, you can only find one of the data

[anly_zhang]

Trying to use md5 to verify whether the data is the same, the result shows that the data is the same, I can’t find the reason.