Bug #45029 MRR does not work with InnoDB
Submitted: 22 May 2009 9:07 Modified: 10 Dec 2010 10:27
Reporter: Sanjay Manwani Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 codebase OS:Any
Assigned to: Olav Sandstå
Triage: Triaged: D1 (Critical)

[22 May 2009 9:07] Sanjay Manwani
Description:
There are multiple bugs that are arising due to enabling of the MRR on 
InnoDB. 
All the issues disappear if  the MRR is disabled as below:
set optimizer_use_mrr='disable';
set engine_condition_pushdown=off;
The following bugs are being closed as a duplicate of this one larger MRR-Innodb catch-all bug.
36981
34591
35080
37415
34590
37208
37842
40992
42580
43617
This MRR bug needs to be resolved by InnoDB and the optimizer team coordinating with each other.

How to repeat:
The info on repeating the bug is available in the bugs being closed as duplicate

Suggested fix:
set optimizer_use_mrr='disable';
set engine_condition_pushdown=off;
[22 May 2009 18:01] Shane Bester
to save everybody typing/cutting and pasting, heres that list again...

bug #36981 (innodb crash when selecting for update)
bug #34591 (Innob crash in row_sel_store_mysql_rec() on multi-table delete)
bug #35080 (Innodb crash at mem_block_get_len line 72)
bug #37415 (Crash in row_sel_field_store_in_mysql_format on multiple-table...)
bug #34590 (Innodb crash in build_template() on multi-table delete)
bug #37208 (Wrong query result for big innodb table when index condition...)
bug #37842 (Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307)
bug #40992 (InnoDB: Crash when engine_condition_pushdown is on)
bug #42580 (Innodb's ORDER BY ..LIMIT returns no rows for null-safe operator...)
bug #43617 (Innodb returns wrong results with timestamp's range value in IN ...)
[27 May 2009 16:35] Timothy Smith
This has a duplicate bug #45026, but as much more info is present on this bug I leave this one as the main bug.
[9 Jun 2009 15:05] Guilhem Bichot
BUG#43360 should be in the list too, it also goes away if disabling ICP for InnoDB.
[11 Jun 2009 14:24] Sanjay Manwani
We will need to keep this bug open till the final solution for the MRR is in place.
[19 Jun 2009 12:59] 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/76681

2803 Alexander Nozdrin	2009-06-19
      Bug#45029: fix test failures.
[19 Jun 2009 12:59] 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/76684

2802 Alexander Nozdrin	2009-06-18
      Bug#45029: MRR does not work with InnoDB.
      
      Disable MRR in 5.4.
[19 Jun 2009 18:58] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090619185552-7mxexfr1ammf6qbt) (version source revid:alik@sun.com-20090619091206-hl4zmn59px2v8ole) (merge vers: 5.4.4-alpha) (pib:11)
[25 Jun 2009 2:00] Paul Dubois
Noted in 5.4.4 changelog.

The Multi-Range Read access method does not work reliably for InnoDB
and has been disabled for InnoDB tables.
[1 Jul 2009 14:19] Evgeny Potemkin
Bug#43447 closed as a duplicate to this one.
[2 Jul 2009 6:43] John Embretsen
On 11-Jun Sanjay wrote: "We will need to keep this bug open till the final solution for the MRR is in place."

This bug is closed. Is that a mistake, or is the final solution to disable MRR?
[3 Jul 2009 14:45] Mark Callaghan
Why has this bug been closed? It is much easier for those of us on the outside to track this in one place.

Can batch key access be used when MRR is disabled?
[6 Jul 2009 11:02] Sanjay Manwani
Right,
this does need to be kept open.
-Sanjay
[10 Dec 2010 10:27] Olav Sandstå
I am setting this bug to closed as all bugs listed in this bug report are either fixed (with state Closed) or marked as Duplicates/Can't repeat. See also WL#5692 for more details about the status for MRR.
[10 Dec 2010 16:26] Mark Callaghan
WL 5692 is private
http://forge.mysql.com/worklog/task.php?id=5692