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: | |
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
[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)