MySQL Bugs: #23133: LOAD INDEX INTO CACHE fails

Bug #23133	LOAD INDEX INTO CACHE fails
Submitted:	10 Oct 2006 11:27	Modified:	26 Oct 2006 18:06
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.26-standard-log	OS:	Linux (Linux radium.net45.com 2.6.9-34.)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Preloading indexes with LOAD INDEX INTO CACHE can fail.

The table

mysql> show create table B_Hotel\G
*************************** 1. row ***************************
       Table: B_Hotel
Create Table: CREATE TABLE `B_Hotel` (
  `id` int(11) NOT NULL default '0',
  `hotel_id` int(11) NOT NULL default '0',
  `name` varchar(255) character set utf8 default NULL,
  `address` varchar(255) character set utf8 NOT NULL default '',
  `city` varchar(255) character set utf8 NOT NULL default '',
  `district` varchar(255) character set utf8 default NULL,
  `cc1` char(2) NOT NULL default '',
  `ufi` int(11) default NULL,
  `ufi_island` int(11) default NULL,
  `ufi_adm2` int(11) default NULL,
  `hoteltype_id` int(11) default NULL,
  `class` tinyint(4) NOT NULL default '0',
  `class_is_estimated` tinyint(4) NOT NULL default '0',
  `currencycode` varchar(20) default NULL,
  `minrate` double NOT NULL default '0',
  `maxrate` double NOT NULL default '0',
  `zip` varchar(20) character set utf8 NOT NULL default '',
  `url` varchar(255) default NULL,
  `pagename` varchar(255) default NULL,
  `avg_availability_next_month` float NOT NULL default '0',
  `public_ranking` int(11) unsigned NOT NULL default '0',
  `follow_proposed_ranking` tinyint(3) unsigned NOT NULL default '1',
  `preferred` tinyint(4) NOT NULL default '0',
  `picture_url` varchar(255) default NULL,
  `nr_rooms` mediumint(9) default NULL,
  `bp_rank` mediumint(9) default NULL,
  `commission` float NOT NULL default '10',
  `commission1` float default NULL,
  `commission2` float default NULL,
  `commission3` float default NULL,
  `commission4` float default NULL,
  `commission5` float default NULL,
  `commission6` float default NULL,
  `commission7` float default NULL,
  `longitude` double default NULL,
  `latitude` double default NULL,
  `geo_done` tinyint(4) NOT NULL default '0',
  `geo_woe` tinyint(4) NOT NULL default '0',
  `geo_map` tinyint(4) NOT NULL default '0',
  `path` varchar(255) character set utf8 NOT NULL default '',
  `city_cleaned` varchar(255) default NULL,
  `checkin` varchar(5) NOT NULL default '',
  `checkin_until` varchar(5) NOT NULL default '',
  `checkout` varchar(5) NOT NULL default '',
  `checkout_until` varchar(5) NOT NULL default '',
  `phone` varchar(60) default NULL,
  `fax` varchar(60) default NULL,
  `email` varchar(100) default NULL,
  `last_change` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `changed_by` varchar(255) default NULL,
  `created` timestamp NOT NULL default '0000-00-00 00:00:00',
  `username` varchar(30) default NULL,
  `password` varchar(30) default NULL,
  `conv_result` mediumint(9) NOT NULL default '0',
  `default_language` char(2) NOT NULL default 'en',
  `migration_status` enum('old','freeze','new') NOT NULL default 'old',
  `migration_status_date` datetime default NULL,
  `remarks` text,
  `main_photo_id` int(11) default NULL,
  `logo_photo_id` int(11) default NULL,
  `is_closed` tinyint(4) NOT NULL default '0',
  `_bookable_direct` tinyint(4) NOT NULL default '0',
  `_region_old` varchar(255) default NULL,
  `_district_old` varchar(255) default NULL,
  `constraints` set('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S') NOT NULL default '',
  `constraints_comment` varchar(127) default NULL,
  `min_days_before_arrival` smallint(6) NOT NULL default '0',
  `_variables_old` varchar(32) default NULL,
  `cc_cvc_required` tinyint(4) NOT NULL default '1',
  `nomaps` tinyint(4) NOT NULL default '0',
  `mayhide` tinyint(3) unsigned NOT NULL default '0',
  `include_pin_on_confirmation` tinyint(4) NOT NULL default '1',
  `default_communication_language` char(2) default NULL,
  `remarks_general` text,
  `remarks_internal` text character set utf8,
  `name_unidecode` varchar(255) default NULL,
  `ranking` int(10) unsigned NOT NULL default '0',
  `am_staff_id` int(11) NOT NULL default '0',
  `builder_staff_id` int(11) default NULL,
  `own_url` varchar(255) default NULL,
  `is_test_hotel` tinyint(4) default '0',
  `contract_signed` timestamp NOT NULL default '0000-00-00 00:00:00',
  `contract_hotelname` varchar(255) character set utf8 default NULL,
  `contract_signer_name` varchar(255) character set utf8 default NULL,
  `contract_signer_position` varchar(255) character set utf8 default NULL,
  `contracted_by` enum('Bookings','ActiveHotels') NOT NULL default 'Bookings',
  `crs` enum('','ActiveHotels') NOT NULL default '',
  `id_at_crs` varchar(20) default NULL,
  `floors` tinyint(3) unsigned NOT NULL default '0',
  `built` smallint(5) unsigned NOT NULL default '0',
  `aarating` tinyint(3) unsigned NOT NULL default '0',
  `aaredrating` tinyint(3) unsigned NOT NULL default '0',
  `mrrating` tinyint(3) unsigned NOT NULL default '0',
  `racrating` tinyint(3) unsigned NOT NULL default '0',
  `tbrating` tinyint(3) unsigned NOT NULL default '0',
  `release` int(10) unsigned NOT NULL default '0',
  `policy_url` varchar(255) NOT NULL default '',
  `breakfast_from` varchar(5) NOT NULL default '',
  `breakfast_until` varchar(5) NOT NULL default '',
  `breakfast_full_price` float default NULL,
  `breakfast_buffet_price` float default NULL,
  `lunch_from` varchar(5) NOT NULL default '',
  `lunch_until` varchar(5) NOT NULL default '',
  `dinner_from` varchar(5) NOT NULL default '',
  `dinner_until` varchar(5) NOT NULL default '',
  `cancel_chargetype` char(1) NOT NULL default 'P',
  `cancel_value` tinyint(3) unsigned NOT NULL default '0',
  `cancel_hours` smallint(5) unsigned NOT NULL default '24',
  `max_age_free` tinyint(3) unsigned NOT NULL default '0',
  `max_age_not_adult` tinyint(3) unsigned NOT NULL default '0',
  `review_score` smallint(5) unsigned default NULL,
  `review_nr` smallint(5) unsigned default NULL,
  `rate_guaranteed` enum('none','guaranteed','exclusive') NOT NULL default 'none',
  `webmarketing` tinyint(4) NOT NULL default '0',
  `policy_group_container_id` int(10) unsigned default NULL,
  `default_policy_group_id` int(10) unsigned default NULL,
  `policy_edit` char(1) default 'I',
  `policy_display` char(1) default 'I',
  `policy_multiple` char(1) default 'A',
  `policy_modified` timestamp NOT NULL default '0000-00-00 00:00:00',
  `policy_modified_by` varchar(20) default NULL,
  `renovated` tinyint(4) default NULL,
  `renovated_until` date default NULL,
  `review_nr_recommend` smallint(6) default NULL,
  `review_nr_not_recommend` smallint(6) default NULL,
  `no_override` tinyint(3) unsigned NOT NULL default '0',
  `use_generated_brochures` tinyint(3) unsigned default '0',
  `dont_show_rack_rate` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `cc1_2` (`cc1`,`pagename`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `url` (`url`),
  KEY `path` (`path`),
  KEY `hotel_id` (`hotel_id`),
  KEY `ufi` (`ufi`),
  KEY `cc1` (`cc1`,`preferred`),
  KEY `conv_result` (`conv_result`),
  KEY `migration_status` (`migration_status`),
  KEY `constraints` (`constraints`,`hotel_id`),
  KEY `am_staff_id` (`am_staff_id`),
  KEY `longitude` (`longitude`,`latitude`,`ranking`),
  KEY `id_at_crs` (`id_at_crs`),
  KEY `is_closed` (`is_closed`,`ufi`,`cc1`),
  KEY `review_score` (`review_score`,`preferred`,`ranking`),
  KEY `policy_group_container_id` (`policy_group_container_id`),
  KEY `default_policy_group_id` (`default_policy_group_id`),
  KEY `use_generated_brochures` (`use_generated_brochures`),
  FULLTEXT KEY `_region_old` (`_region_old`),
  FULLTEXT KEY `_district_old` (`_district_old`),
  FULLTEXT KEY `name` (`name`),
  FULLTEXT KEY `name_unidecode` (`name_unidecode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

How to repeat:
mysql> load index into cache B_Hotel;
+------------+--------------+----------+-----------------------------------+
| Table      | Op           | Msg_type | Msg_text                          |
+------------+--------------+----------+-----------------------------------+
| bp.B_Hotel | preload_keys | error    | Indexes use different block sizes |
| bp.B_Hotel | preload_keys | status   | Operation failed                  |
+------------+--------------+----------+-----------------------------------+
2 rows in set (0.00 sec)

Suggested fix:
A comment in http://dev.mysql.com/doc/refman/5.0/en/load-index.html suggests this is happening when there are variable length keys defined. It is unclear according to that comment if these indexes are completely uncacheable (which would be bad) or just not preloadable.

In any case the error message is misleading and these indexes should be preloadable.

LOAD INDEX can only work if all indexes in a table have the same block size. This limitation apparently is not documented.

This is a verified documentation request then.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.