Bug #94126 The description of "13.7.6.2 CACHE INDEX Syntax" chapter is ambiguous
Submitted: 30 Jan 3:01 Modified: 25 Apr 14:39
Reporter: JianJun Shi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:5.7 OS:Linux
Assigned to: CPU Architecture:Any

[30 Jan 3:01] JianJun Shi
Description:
"13.7.6.2 CACHE INDEX Syntax" chapter in the "MySQL 5.7 Reference Manual" was describe as following:

CACHE INDEX
tbl_index_list [, tbl_index_list] ...
[PARTITION (partition_list | ALL)]
IN key_cache_name

tbl_index_list:
tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]

partition_list:
partition_name[, partition_name][, ...]

From my point of view, "[INDEX|KEY]" mains: the keyword "INDEX" or "KEY" is not necessary, we can use "INDEX" or "KEY" or null. Actually, if we do not point out keyword "INDEX" or "KEY". We will get back a error message from the "CACHE INDEX" syntax. 

How to repeat:
use test;
CREATE TABLE IF NOT EXISTS Table_CACHE_INDEX_102 (C_ID INT DEFAULT 99 REFERENCES CREATE_TABLE_000 (C_ID) MATCH PARTIAL ON DELETE NO ACTION ,C_D_ID INTEGER NOT NULL,C_W_ID BIGINT NOT NULL,C_DOUBLE DOUBLE NOT NULL,C_DECIMAL DECIMAL NOT NULL,C_FIRST VARCHAR(64) NOT NULL,C_MIDDLE CHAR(2),C_LAST VARCHAR(64) NOT NULL,C_STREET_1 VARCHAR(20) NOT NULL,C_STREET_2 VARCHAR(20),C_CITY VARCHAR(64) NOT NULL,C_STATE CHAR(2) NOT NULL,C_ZIP CHAR(9) NOT NULL,C_PHONE CHAR(16) NOT NULL,C_SINCE TIMESTAMP,C_CREDIT CHAR(2) NOT NULL,C_CREDIT_LIM NUMERIC(12,2),C_DISCOUNT NUMERIC(4,4),C_BALANCE NUMERIC(12,2),C_YTD_PAYMENT REAL NOT NULL,C_PAYMENT_CNT FLOAT NOT NULL,C_DELIVERY_CNT BOOLEAN NOT NULL,C_END DATE NOT NULL,C_VCHAR VARCHAR(1000),C_DATA TEXT,C_TEXT BLOB,C_TINYTEXT TINYTEXT,C_MEDIUMBLOB MEDIUMBLOB,C_LONGBLOB LONGBLOB, C_LINESTRING LINESTRING not null, PRIMARY KEY USING HASH (C_ID,C_D_ID,C_W_ID) COMMENT 'string', UNIQUE KEY UNIQUE_index_CACHE_INDEX_102 (C_ID,C_D_ID,C_W_ID ) USING BTREE, SPATIAL index_CACHE_INDEX_102 (C_LINESTRING ASC) KEY_BLOCK_SIZE 10, CHECK ('agdj56!#54'IS UNKNOWN)) AUTO_INCREMENT 9, AVG_ROW_LENGTH = 8, CHARACTER SET = utf8, CHECKSUM 0, DEFAULT COLLATE utf8_general_ci, COMPRESSION 'NONE', CONNECTION 'connect_string', ENGINE = MyISAM, INSERT_METHOD FIRST, KEY_BLOCK_SIZE = 0, MAX_ROWS 99, MIN_ROWS = 99, PACK_KEYS = 0, STATS_AUTO_RECALC DEFAULT, STATS_PERSISTENT 0, STATS_SAMPLE_PAGES = 10 ;

CACHE INDEX Table_CACHE_INDEX_102 KEY ( index_CACHE_INDEX_102) IN keycache1_CACHE_INDEX_102;
CACHE INDEX Table_CACHE_INDEX_102 INDEX ( index_CACHE_INDEX_102) IN keycache1_CACHE_INDEX_102;
CACHE INDEX Table_CACHE_INDEX_102 ( index_CACHE_INDEX_102) IN keycache1_CACHE_INDEX_102;
[31 Jan 9:07] Umesh Shastry
Hello JianJun,

Thank you for the report and feedback!

regards,
Umesh
[25 Apr 14:39] Paul Dubois
Posted by developer:
 
You're correct, [INDEX|KEY] should be {INDEX|KEY}. This affects the LOAD INDEX INTO CACHE syntax as well.

Updated syntax:

CACHE INDEX {
      tbl_index_list [, tbl_index_list] ...
    | tbl_name PARTITION (partition_list | ALL)
  }
  IN key_cache_name

tbl_index_list:
  tbl_name [{INDEX|KEY} (index_name[, index_name] ...)]

partition_list:
  partition_name[, partition_name] ...