Bug #115946 Slave applies stop in XA trx with NULL UK and implicit PK
Submitted: 28 Aug 2024 9:45 Modified: 28 Aug 2024 12:16
Reporter: Yichang SONG (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:8.0, 8.0.39 OS:Any
Assigned to: CPU Architecture:Any

[28 Aug 2024 9:45] Yichang SONG
Description:
Based on two facts below, something wrong happens.
1. During the apply on the slave, the choice of index is hard-coded in the function sql/log_event.cc:search_key_in_table(). 
2. Unlike the master, the slave does not call unlock_row().

Consider a scenario with a table that lacks an explicit primary key but contains a default NULL unique key. When performing a DML operation with a NULL value for the unique key, the slave will choose to search for the unique key but will not unlock the rows that do not meet the WHERE conditions. Consequently, this results in the slave locking numerous records with NULL unique keys, potentially leading to blocking issues for subsequent DML operations.

How to repeat:
1. prepare the table and data
CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL,
  `col` varchar(255) DEFAULT NULL
  UNIQUE KEY `uk` (`col`) USING BTREE,
  KEY `sk` (`id`) USING BTREE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

delimiter $$
create procedure auto_insert(IN times int)
BEGIN
    declare i int default 1;
    truncate table t1;
    while(i<times)do
        insert into t1 values(i,null);
        set i=i+1;
    end while;
END$$
delimiter ;
call auto_insert(1000);

2. Do XA trx 1 first
xa begin 'x1'; 
delete from t1 where id=100 and col is null;
xa end 'x1';
xa prepare 'x1';

3. Do trx 2 next:
delete from t1 where id=99 and col is null;

Result:
In the master, the second transaction will proceed without being blocked by the first. However, on the slave, the first transaction will hold many NULL unique key records and block the second transaction.
[28 Aug 2024 12:16] MySQL Verification Team
Hello Yichang Song,

Thank you for the report and test case.
Verified as described.

regards,
Umesh
[28 Aug 2024 12:22] MySQL Verification Team
8.0.39 test results

Attachment: 115946.results.txt (text/plain), 19.91 KiB.