MySQL Bugs: #45029: MRR does not work with InnoDB

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å	CPU Architecture:	Any

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;

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 ...)

This has a duplicate bug #45026, but as much more info is present on this bug I leave this one as the main bug.

BUG#43360 should be in the list too, it also goes away if disabling ICP for InnoDB.

We will need to keep this bug open till the final solution for the MRR is in place.

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.

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.

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)

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.

Bug#43447 closed as a duplicate to this one.

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?

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?

Right,
this does need to be kept open.
-Sanjay

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.

WL 5692 is private
http://forge.mysql.com/worklog/task.php?id=5692