MySQL Bugs: #56680: InnoDB may return wrong results from a case-insensitive covering index

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ä	CPU Architecture:	Any
Tags:	innodb ibuf

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.

Marko, a good catch. The bug was made by me in the 1990s.

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.

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;

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.

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

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

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

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

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

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

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

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

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

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

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)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

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)

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.

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)

This bug fix introduced Bug#69122 InnoDB doesn't redo-log insert buffer merge operation if it is done in-place