MySQL Bugs: #117086: replace into can cause 1062 error

Bug #117086	replace into can cause 1062 error
Submitted:	31 Dec 2024 9:52	Modified:	2 Jan 1:51
Reporter:	dakun li	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	8.0.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
in this case,will happen 1062 error.

How to repeat:
in this case, must open writeset:
set global transaction_write_set_extraction        = XXHASH64;
set global binlog_transaction_dependency_tracking  = WRITESET;
set global slave_parallel_workers= 16;

create table:
 CREATE TABLE `tb` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT ,
  `c1` bigint unsigned NOT NULL DEFAULT '0',
  `c2` bigint unsigned NOT NULL DEFAULT '0' ,
  `c3` bigint unsigned NOT NULL DEFAULT '0' ,
  `c4` bigint unsigned NOT NULL DEFAULT '0',
  `c5` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' ,
  `c6` tinyint unsigned NOT NULL DEFAULT '0' ,
  `c7` bigint unsigned NOT NULL DEFAULT '0' COMMENT ,
  `c8` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT,
  `c9` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  `c10` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_idx` (`c4`,`c6`,`c7`,`c8`),
  KEY `idx_up` (`last_update_time`),
  KEY `idx_bizId` (`biz_id`)
) ENGINE=InnoDB AUTO_INCREMENT=105927153 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

insert follow data:

flush logs;

insert into tb values(105927150,100001376,100073011,100199610,100518101,'4609706095',2,888888,'2024-08-28 14:22:51','2024-12-27 18:04:00','');

insert into tb values(105927151,100001376,100073011,100199611,100518101,'4609706095',2,888888,'2024-08-28 14:22:51','2024-12-27 18:04:00','');

replace into tb values(105927151,100001376,100073011,100199611,100518101,'4609706095',2,105927151,'2024-08-28 14:22:51','2024-12-27 18:04:00','');
insert into tb values(105927152,100001376,100073011,100199611,100518101,'4609706095',2,888888,'2024-08-28 14:22:51','2024-12-27 18:04:00','');

in binlog:
last_committed=0        sequence_number=1
last_committed=1        sequence_number=2
last_committed=2        sequence_number=3
last_committed=2        sequence_number=4

in slave,the last two sql will execute PARALLEL executed:
the problem is,if slave first execute the last sql will happen 1062 error,because the uk_idx:100199611:4609706095:2:888888 have not be delete by the     
third sql.

Suggested fix:
put the 100199611:4609706095:2:888888,into the write_set_unique

CREATE TABLE `tb` (
  `c1` bigint unsigned NOT NULL AUTO_INCREMENT ,
  `c2` bigint unsigned NOT NULL DEFAULT '0',
  `c3` bigint unsigned NOT NULL DEFAULT '0' ,
  `c4` bigint unsigned NOT NULL DEFAULT '0' ,
  `c5` bigint unsigned NOT NULL DEFAULT '0',
  `c6` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' ,
  `c7` tinyint unsigned NOT NULL DEFAULT '0' ,
  `c8` bigint unsigned NOT NULL DEFAULT '0' COMMENT ,
  `c9` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT,
  `c10` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  `c11` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_idx` (`c4`,`c6`,`c7`,`c8`),
  KEY `idx_up` (`last_update_time`),
  KEY `idx_bizId` (`biz_id`)
) ENGINE=InnoDB AUTO_INCREMENT=105927153 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

the table schema as this,sorry;
CREATE TABLE `tb` (
  `c1` bigint unsigned NOT NULL AUTO_INCREMENT ,
  `c2` bigint unsigned NOT NULL DEFAULT '0',
  `c3` bigint unsigned NOT NULL DEFAULT '0' ,
  `c4` bigint unsigned NOT NULL DEFAULT '0' ,
  `c5` bigint unsigned NOT NULL DEFAULT '0',
  `c6` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' ,
  `c7` tinyint unsigned NOT NULL DEFAULT '0' ,
  `c8` bigint unsigned NOT NULL DEFAULT '0'  ,
  `c9` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `c10` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  `c11` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' ,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `uk_idx` (`c4`,`c6`,`c7`,`c8`),
  KEY `idx_up` (`last_update_time`),
  KEY `idx_bizId` (`biz_id`)
) ENGINE=InnoDB AUTO_INCREMENT=105927153 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Hello dakun li,

Thank you for the report and feedback.
I tried to reproduce in 8.0.40 but not seeing any issues. Could you please check in 8.0.40? Thank you.

regards,
Umesh

in 8.0.40 ，have not repeat，because the last commit as follow：
last_committed=0        sequence_number=1
last_committed=1        sequence_number=2
last_committed=2        sequence_number=3
last_committed=3        sequence_number=4

in 8.0.26  https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-26.html
we have see this descripe:
Replication: Replication could stop on a multithreaded replica if a unique secondary key was omitted from the writeset hashes used to compute transaction dependencies, leading to errors when executing the transactions on the multithreaded replica. Write set hashes now always include unique secondary keys even if they are not included in the read set and write set. (Bug #31636339)

so ,i think ,MySQL have repair this bug in 8.0.26!!!