Bug #55626 MIN and MAX reading a delete-marked record from secondary index
Submitted: 29 Jul 2010 13:20 Modified: 14 Dec 2010 18:52
Reporter: Mikhail Izioumtchenko 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: consistent read, MAX, min, repeatable read

[29 Jul 2010 13:20] Mikhail Izioumtchenko
Description:
a rarely reproducible assert:

100728 12:16:36  InnoDB: Assertion failure in thread 1257187648 in file row/row0sel.c line 2693
InnoDB: Failing assertion: !rec_get_deleted_flag(rec, rec_offs_comp(offsets))
100728 12:16:36 - mysqld got signal 6 ;

 #5  0x0000003acbc31d10 in abort () from /lib64/libc.so.6
 #6  0x00002aaaabc9c7ae in row_sel_store_mysql_rec (
     mysql_rec=0x74d8110 "\377\213", prebuilt=0x72b7338,
     rec=0x2aaab04b04b6 "\200", offsets=0x4aeee5c0) at row/row0sel.c:2693
 #7  0x00002aaaabc9f9a8 in row_search_for_mysql (buf=0x74d8110 "\377\213",
     mode=3, prebuilt=0x72b7338, match_mode=0, direction=0)
     at row/row0sel.c:4431
 #8  0x00002aaaabc2aec6 in ha_innodb::index_read (this=0x74d7f20,
     buf=0x74d8110 "\377\213", key_ptr=0x0, key_len=0,
     find_flag=HA_READ_BEFORE_KEY) at handler/ha_innodb.cc:5439
 #9  0x00002aaaabc22d8a in ha_innodb::index_last (this=0x74d7f20,
     buf=0x74d8110 "\377\213") at handler/ha_innodb.cc:5767
 #10 0x00000000006f75a5 in get_index_max_value (table=0x768fa60,
     ref=0x4aeefbe0, range_fl=3) at opt_sum.cc:206
 #11 0x00000000006f8e43 in opt_sum_query (tables=0x795cd88, all_fields=...,
     conds=0x0) at opt_sum.cc:382
 #12 0x000000000066fdb2 in JOIN::optimize (this=0x2aaabc189050)
     at sql_select.cc:959
 #13 0x0000000000673ea5 in mysql_select (thd=0x2aaabc1cc070,
     rref_pointer_array=0x2aaabc1ce0f8, tables=0x795cd88, wild_num=0,
     fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0,
     proc_param=0x0, select_options=2148289024, result=0x795d158,
     unit=0x2aaabc1cdb00, select_lex=0x2aaabc1cdf28) at sql_select.cc:2503
 #14 0x0000000000678d51 in handle_select (thd=0x2aaabc1cc070,
     lex=0x2aaabc1cda60, result=0x795d158, setup_tables_done_option=0)
     at sql_select.cc:269
 #15 0x00000000005ee2db in execute_sqlcom_select (thd=0x2aaabc1cc070,
     all_tables=0x795cd88) at sql_parse.cc:5093
 #16 0x00000000005eed54 in mysql_execute_command (thd=0x2aaabc1cc070)
     at sql_parse.cc:2287
 #17 0x00000000005f6d5a in mysql_parse (thd=0x2aaabc1cc070,
     inBuf=0x795c630 "select (max(a) + min(a))/2 from ibstd_7", length=39,
     found_semicolon=0x4aef1f30) at sql_parse.cc:6017

How to repeat:
InnoDB's internal stress/recovery testing, low reproducibility, plugin
[11 Aug 2010 7:12] Marko Mäkelä
I added this assertion recently. InnoDB is returning a delete-marked record to MySQL. That should never happen. Either a locking conflict should occur, or the delete-marked records should be skipped or an earlier (undeleted) version should be constructed.

The failing statement is "select (max(a) + min(a))/2 from ibstd_7". Michael, which scripts are accessing this table, and how did you invoke them? This bug should be reproducible with just the subset of the tests that involves the table ibstd_7.
[12 Aug 2010 12:46] Marko Mäkelä
Some investigation of core.31017:

Consistent reads (TRX_ISO_REPEATABLE_READ) are being used.
The record belongs to index A of table ibstd_7.
The record is being delivered from the only B-tree page of the index (space 7,page 5,N_RECS=0x38), not from a memory buffer that would contain an earlier version. Why was an earlier version not constructed? Because it is a secondary index, constructing an earlier version would require a trip to the clustered index and the undo log.

I seem to remember that there is something special about secondary index records. The secondary index is allowed to contain "orphan" records that point to already deleted rows in the clustered index. These records would be removed by purge. There could be some special considerations that I have missed.

It is possible that my added assertion is too strong. If not, then the MIN/MAX functions should likely scan forward/backward until the first non-deleted record is found.

This bug should be repeatable with a small table where records are deleted or updated, and and MIN or MAX functions on an indexed column. It is worth noting that the secondary index is only one page (56 records, some of which are delete-marked) in this failing case.
[12 Aug 2010 12:48] Marko Mäkelä
This probably affects all InnoDB versions.
[16 Aug 2010 8:58] Marko Mäkelä
This simple test case and variations thereof failed to repeat this bug. I tried also without the TEXT column c, and also with an auto-increment counter:

CREATE TABLE bug55626 (a INT, b INT, c TEXT, PRIMARY KEY(b,a), INDEX(a))
ENGINE=InnoDB;

INSERT INTO bug55626 VALUES (RAND(12345)*100000,RAND()*100000,REPEAT('a',1000));
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;
INSERT INTO bug55626 SELECT RAND()*100000,RAND()*100000,c FROM bug55626;

SELECT MIN(a),MAX(a) FROM bug55626;
UPDATE bug55626 SET a=a/2;
SELECT MIN(a),MAX(a) FROM bug55626;
UPDATE bug55626 SET a=a*4;
SELECT MIN(a),MAX(a) FROM bug55626;
UPDATE bug55626 SET a=a/8;
SELECT MIN(a),MAX(a) FROM bug55626;
UPDATE bug55626 SET a=a*16;
SELECT MIN(a),MAX(a) FROM bug55626;

I think I will have to slim down the original test case.
[17 Aug 2010 14:01] Mikhail Izioumtchenko
Marko, maybe just a DELETE should be tried. The script has something
called 'delete about half the records from the table'. I think
if we delete the rows from the lower end of the table then we'll have something
like 10s before the purge kicks in, to repeat the bug on MIN. Or the purge could be
disabled or stalled somehow to gives us a better chance.
[18 Aug 2010 3:55] Sunny Bains
Marko, 

I encountered this bug today while testing an unrelated change. It is easy
to reproduce without UNIV_DEBUG and ibtests. Failed twice within 5-10 minutes
of testing. I changed the failing assertion to a non-debug assertion.

Regards,
-sunny
[18 Aug 2010 10:19] Marko Mäkelä
Sorry, this was a bogus debug assertion that I added recently.

A comment in row_undo_mod_upd_exist_sec(), row0umod.c, says: “in row0sel.c, in queries we always retrieve the clustered index record or an earlier version of it, if the secondary index record through which we do the search is delete-marked.”

Indeed, the function row_search_for_mysql() does “goto requires_clust_rec” when needed. Once it has established that the delete-marked secondary index record points to a clustered index record that exists in the transaction’s read view, it is safe to ignore the delete-mark bit in the secondary index record.
[18 Aug 2010 11:01] 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/116075
[18 Aug 2010 11:01] 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/116076
[18 Aug 2010 11:40] 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/116088
[18 Aug 2010 11:40] 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/116089
[18 Aug 2010 11:54] Marko Mäkelä
This was a debug assertion that was fixed in the InnoDB Plugin 1.0 and MySQL 5.5. No change in documentation is needed, because this bug only affected UNIV_DEBUG builds.
[9 Sep 2010 18:20] Marko Mäkelä
It seems that the correct approach would be to reconstruct the secondary index record from the relevant version of the clustered index record, after all. See Bug #56680.
[28 Sep 2010 8:48] Bugs System
Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)
[28 Sep 2010 15:39] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)
[28 Sep 2010 15:42] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)
[28 Sep 2010 15:44] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)
[14 Oct 2010 8:27] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:42] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 8:57] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[25 Oct 2010 19:56] Paul DuBois
No changelog entry needed.
[9 Nov 2010 19:43] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)
[13 Nov 2010 16:10] 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:29] 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)
[16 Dec 2010 22:34] 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)