Bug #57063 InnoDB stores cursor position too often

• Submitted: 28 Sep 2010 10:57
• Modified: 10 Dec 2010 0:42
• Reporter: Marko Mäkelä
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0, 5.1, 5.5
• OS: Any
• Assigned to: Marko Mäkelä
• CPU Architecture: Any
• Tags: cursor, innodb, unique

[28 Sep 2010 10:57] Marko Mäkelä

Description:
When working on Bug #56680, I noticed that row_search_for_mysql() sets the variable unique_search_from_clustered_index only when it attempts an adaptive hash index lookup. The only place where this variable is checked is not related to the adaptive hash index, but to cursor repositioning.

How to repeat:
Monitor calls to btr_pcur_store_position() in row_search_for_mysql(). Note that the function is being called when at least one of the following conditions hold:

prebuilt->templ_contains_blob
prebuilt->mysql_row_len >= UNIV_PAGE_SIZE / 8

Suggested fix:
=== modified file 'storage/innobase/row/row0sel.c'
--- storage/innobase/row/row0sel.c	revid:[e-mail]-20100915165836-zydiy6urspd3xsva
+++ storage/innobase/row/row0sel.c	2010-09-28 10:52:33 +0000
@@ -3240,7 +3240,6 @@ row_search_for_mysql(
 	rec_t*		clust_rec;
 	ulint		err				= DB_SUCCESS;
 	ibool		unique_search			= FALSE;
-	ibool		unique_search_from_clust_index	= FALSE;
 	ibool		mtr_has_extra_clust_latch	= FALSE;
 	ibool		moves_up			= FALSE;
 	ibool		set_also_gap_locks		= TRUE;
@@ -3471,8 +3470,6 @@ row_search_for_mysql(
 
 		mode = PAGE_CUR_GE;
 
-		unique_search_from_clust_index = TRUE;
-
 		if (trx->mysql_n_tables_locked == 0
 		    && prebuilt->select_lock_type == LOCK_NONE
 		    && trx->isolation_level > TRX_ISO_READ_UNCOMMITTED
@@ -4323,7 +4320,9 @@ got_row:
 	HANDLER command where the user can move the cursor with PREV or NEXT
 	even after a unique search. */
 
-	if (!unique_search_from_clust_index
+	if (!unique_search
+	    || direction != 0
+	    || !index->type & DICT_CLUSTERED
 	    || prebuilt->select_lock_type != LOCK_NONE
 	    || prebuilt->used_in_HANDLER) {

[4 Nov 2010 12:30] Heikki Tuuri

Marko, a good catch. I have overlooked this inoptimality for years. Patch approved.

[5 Dec 2010 12:41] Bugs System

Pushed into mysql-trunk 5.6.1 (revid:[e-mail]-20101205122447-6x94l4fmslpbttxj) (version source revid:[e-mail]-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)