MySQL Bugs: #76927: Duplicate UK values in READ-COMMITTED (again)

Bug #76927	Duplicate UK values in READ-COMMITTED (again)
Submitted:	4 May 2015 12:09	Modified:	17 Aug 2015 13:38
Reporter:	Alexey Kopytov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1, 5.5, 5.6, 5.7, 8.0	OS:	Any
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
This looks similar, but not identical to bug #73170 and is also related
to bug #69979.

Even with bug #73170 fixed, it is still possible to get a silent
violation of the unique key constraint with the READ COMMITTED isolation
level or innodb_locks_unsafe_for_binlog=1, at least with REPLACE
INTO. Test case:

---
drop table if exists t1;
create table t1(a int not null, b int not null, who int, primary key(b), unique key(a)) engine=innodb;

drop procedure if exists p1;
delimiter $
create procedure p1(me int)
l1:
  begin
  declare continue handler for 1062 begin end;
  declare continue handler for 1213 begin end;
  set @cnt_a:=null;
  repeat
    select count(*) cnt_a into
     	   @cnt_a from t1 group by a having cnt_a > 1 limit 1;
    if @cnt_a is not null then
       select * from t1;
       leave l1;
    end if;
    replace into t1(a,b,who) values(floor(3*rand()), floor(3*rand()), me);
  until 1=2 end repeat;
end$
delimiter ;
---

session1> call p1(1); check table t1;
session2> call p1(2); check table t1;
session3> call p1(3); check table t1;

This is what I get after 3-10 minutes on both 5.6.11 and 5.6.24:

---

+---+---+------+
| a | b | who  |
+---+---+------+
| 0 | 0 |    3 |
| 0 | 2 |    1 |
+---+---+------+
2 rows in set (4 min 29.88 sec)

Query OK, 0 rows affected (4 min 29.88 sec)

+---------+-------+----------+-----------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                      |
+---------+-------+----------+-----------------------------------------------+
| test.t1 | check | Warning  | InnoDB: The B-tree of index "a" is corrupted. |
| test.t1 | check | error    | Corrupt                                       |
+---------+-------+----------+-----------------------------------------------+
2 rows in set (0.00 sec)

---

This is different from bug #69979, which was closed as Not a Bug due to
phantom duplicate UK values seen by a transaction which inserts a row
and then a SELECT in the same transaction may see unchanged (old)
versions of rows. The phantom duplicate values “disappear” as soon as the
transaction is committed.

However, the test case for bug #69979 results in both phantom and real
duplicate values. Real duplicate key violations are silent and can only
be discovered with an autocommit SELECT or CHECK TABLE.

I have modified the test case from bug #69979 to only use autocommit
statements to exclude phantom duplicates and do CHECK TABLE to prove
duplicate values are persistently stored in the table.

I did not test if the same corruption can be reproduced with INSERT or a
combination of DELETE + INSERT.

This is NOT a regression from the fix for bug #68021, because I can
repeat it even on MySQL 5.6.11.

How to repeat:
Start the server with --transaction-isolation="read-committed" and/or
--innodb-locks-unsafe-for-binlog=1

Create the following table and store procedure:

---

drop table if exists t1;
create table t1(a int not null, b int not null, who int, primary key(b), unique key(a)) engine=innodb;

drop procedure if exists p1;
delimiter $
create procedure p1(me int)
l1:
  begin
  declare continue handler for 1062 begin end;
  declare continue handler for 1213 begin end;
  set @cnt_a:=null;
  repeat
    select count(*) cnt_a into
     	   @cnt_a from t1 group by a having cnt_a > 1 limit 1;
    if @cnt_a is not null then
       select * from t1;
       leave l1;
    end if;
    replace into t1(a,b,who) values(floor(3*rand()), floor(3*rand()), me);
  until 1=2 end repeat;
end$
delimiter ;

---

Then execute in 3 different sessions:

session1> call p1(1); check table t1;
session2> call p1(2); check table t1;
session3> call p1(3); check table t1;

fwiw, 5.8.0-debug shows this in the error log:

Version: '5.8.0-m17-debug'  socket: ''  port: 3306  MySQL Community Server (GPL)
2015-05-04T12:39:06.072253Z 3 [ERROR] InnoDB: duplicate key in `a` of table `test`.`t1`: TUPLE (info_bits=0, 2 fields): {[4]    (0x00000000),[4]    (0x00000000)}, COMPACT RECORD(info_bits=0, 2 fields): {[4]    (0x00000000),[4]    (0x00000001)}
2015-05-04T12:39:06.072753Z 4 [ERROR] InnoDB: duplicate key in `a` of table `test`.`t1`: TUPLE (info_bits=0, 2 fields): {[4]    (0x00000000),[4]    (0x00000000)}, COMPACT RECORD(info_bits=0, 2 fields): {[4]    (0x00000000),[4]    (0x00000001)}
2015-05-04T12:39:06.073253Z 2 [ERROR] InnoDB: duplicate key in `a` of table `test`.`t1`: TUPLE (info_bits=0, 2 fields): {[4]    (0x00000000),[4]    (0x00000000)}, COMPACT RECORD(info_bits=0, 2 fields): {[4]    (0x00000000),[4]    (0x00000001)}

Verified on 5.6, 5.7, 5.8!

Also affects 5.5.43!

Verified on 5.1.73 - builtin!

Shane,

Thanks for verifying it against older versions. It looks like the problem has been there for a very long time.

Re: extra output in the error log for 5.8.0-debug, I guess it comes from CHECK TABLE. I see similar messages in 5.6 release builds.

5.0.96 was NOT affected, after running test overnight.

MTR test case

Attachment: bug76927.test (application/octet-stream, text), 2.00 KiB.

The MTR test case requires a new DEBUG_SYNC point. Patch against current 5.6 git HEAD:

diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc
index e31b447..f35d36b 100644
--- a/storage/innobase/row/row0ins.cc
+++ b/storage/innobase/row/row0ins.cc
@@ -2709,6 +2709,8 @@ row_ins_sec_index_entry_low(
 			goto func_exit;
 		}
 
+		DEBUG_SYNC_C("row_ins_sec_index_entry_dup_locks_created");
+
 		/* We did not find a duplicate and we have now
 		locked with s-locks the necessary records to
 		prevent any insertion of a duplicate by another

The problem is with lock_rec_inherit_to_gap(() assuming that only
S-locks can be set by a consistency constraint and thus, only those kind
of locks should be inherited in the gap mode when an index record is
removed.

However, REPLACE, LOAD DATA REPLACE and INSERT ON DUPLICATE KEY UPDATE
set exclusive record locks. So when a record is removed by a purge, all
record locks acquired by in-progress concurrent statements of those
types are lost which leads to a UK constraint violation. This is the
scenario reproduced by the MTR case.

Contribution submitted via Github - Bug #76927: Duplicate UK values in READ-COMMITTED (again) (*) Contribution by Alexey Kopytov (Github akopytov, mysql-server/pull/23#issuecomment-122257474): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_40130846.txt (text/plain), 7.02 KiB.

Posted by developer:
 
Fixed as of the upcoming 5.5.46, 5.6.27, 5.7.9, 5.8.0 release, and here's the changelog entry:

In READ COMMITTED mode, a REPLACE operation on a unique secondary index
resulted in a constraint violation. Thanks to Alexey Kopytov for the
patch. 

Thank you for the bug report.

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0