Bug #56680 InnoDB may return wrong results from a case-insensitive covering index
Submitted: 9 Sep 2010 9:25 Modified: 14 Dec 2010 19:01
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:all OS:Any
Assigned to: Marko Mäkelä
Tags: innodb ibuf
Triage: Triaged: D2 (Serious) / R2 (Low) / E4 (High)

[9 Sep 2010 9:25] Marko Mäkelä
Description:
When InnoDB merges a buffered insert to a case-insensitive secondary index, and there is a delete-marked record in the index already, InnoDB should insert the record by updating the delete-marked record, like row_ins_sec_index_entry_by_modify() does it. Instead, ibuf_insert_to_index_page() just clears the delete-mark flag by calling btr_cur_set_deleted_flag_for_ibuf(). 

Because of the failure to update case-insensitive columns when merging the buffered insert, the values in the secondary index are only useable for identifying clustered index records. Because of this, a case-insensitive secondary index can return wrong results to queries, even if is a "covering" index.

This bug exists in the insert buffering. It should be easier to trigger it in 5.5 than in older versions, because in older versions, deletes and updates would force in-place updates

How to repeat:
Something like this, on a small buffer pool, so that the operations will be more likely to be covered by the change buffer:

CREATE TABLE t(a INT AUTO_INCREMENT PRIMARY KEY, b CHAR(100) KEY)
ENGINE=InnoDB CHARSET latin1 COLLATE latin1_swedish_ci;

-- Load the table with upper-case values, forcing it to the insert buffer
INSERT INTO t VALUES(0,…),(0,…),…;
-- Update some values in a collation-neutral way
UPDATE t SET b=LOWER(b) WHERE …;

-- Read some values. At least some of them should be in the wrong case.
SELECT b FROM t;
SELECT b FROM t WHERE b BETWEEN '…' AND '…';

Suggested fix:
Make ibuf_insert_to_index_page() update the columns, similar to how row_ins_sec_index_entry_by_modify() does it.
[9 Sep 2010 11:34] Heikki Tuuri
Marko, a good catch. The bug was made by me in the 1990s.
[9 Sep 2010 18:18] Marko Mäkelä
The bug noted in the Description may cause permanent inconsistency in the secondary index. I suspect that there is another bug, which affects all queries  of case-insensitive columns from a covering secondary index.

As noted in Bug #55626, when InnoDB notices that a secondary index record might not be ‘live’ in the current transaction’s read view, it will consult the clustered index and optionally some undo log records. Once it has established that the record exists in the read view, it will return the secondary index record to MySQL, even though it should return the relevant columns of the clustered index record.

For example, if a record (a=1,b='x') has been updated to (a=1,b='X'), a case-sensitive secondary index on column b would contain the tuple ('X',1). A repeatable-read transaction that started before the record was updated, will have to consult the clustered index and the undo log to form the record (a=1,b='x') and to determine that it does exist in the read view. At this point, InnoDB should return ('x',1) to MySQL instead of the ('X',1) that it retrieved from the secondary index.
[28 Sep 2010 6:23] Marko Mäkelä
Here is a test case for the intermittent inconsistency, which does not involve the insert buffer. I repeated this in 5.1, but I believe that this bug exists in all InnoDB versions.

In the test case, all SELECT statements should return b='a', but between UPDATE and ROLLBACK in the other connection, the SELECT b returns b='A'. The SELECT * correctly returns b='a', because INDEX(b) is not a covering index. If column c were removed from the table definition, also the SELECT * would incorrectly return b='A'.

#
# Bug #56680 InnoDB may return wrong results from a case-insensitive index
#
-- source include/have_innodb.inc

CREATE TABLE bug56680(a INT PRIMARY KEY,b CHAR(1),c INT,INDEX(b))ENGINE=InnoDB;

INSERT INTO bug56680 VALUES(1,'a',1);

BEGIN;
SELECT b FROM bug56680;

connect (con1,localhost,root,,);
connection con1;
BEGIN;
UPDATE bug56680 SET b='A';

connection default;
SELECT b FROM bug56680;
SELECT * FROM bug56680;

connection con1;
ROLLBACK;

connection default;
disconnect con1;

SELECT b FROM bug56680;
DROP TABLE bug56680;
[29 Sep 2010 11:31] Marko Mäkelä
I was finally able to trigger the buggy function btr_cur_del_unmark_for_ibuf() in a repeatable test case, using a patch that tries to evict pages from the buffer pool when insert buffering is attempted. Next, I will extend my patch to fix this bug as well.
[19 Oct 2010 6:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121043
[19 Oct 2010 6:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121044
[19 Oct 2010 6:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121045
[19 Oct 2010 6:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121046
[19 Oct 2010 6:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121051
[19 Oct 2010 6:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121052
[21 Oct 2010 8:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121479
[21 Oct 2010 8:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121480
[21 Oct 2010 9:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121497
[21 Oct 2010 9:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/121498
[13 Nov 2010 16:25] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:36] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[18 Nov 2010 15:55] Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
[14 Dec 2010 19:01] John Russell
Added to change log:

A query for an InnoDB table could return
the wrong value if a column value was changed to a different case, 
and the column had a case-insensitive index.
[16 Dec 2010 22:29] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
[2 May 2013 6:19] Marko Mäkelä
This bug fix introduced Bug#69122 InnoDB doesn't redo-log insert buffer merge operation if it is done in-place