Bug #94127 The description of "13.7.6.2 CACHE INDEX Syntax" is ambiguous

• Submitted: 30 Jan 2019 3:32
• Modified: 25 Apr 2019 14:41
• Reporter: JianJun Shi
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S4 (Feature request)
• Version: 5.7
• OS: Linux
• CPU Architecture: Any

[30 Jan 2019 3:32] 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, "[PARTITION (partition_list | ALL)]" mains: the syntax of "PARTITION" is not in collision with the syntax of "tbl_index_list", we can use them at the same time. Actually, if we do like that. We will get back a error message from the "CACHE INDEX" syntax.

How to repeat:
use test;
CREATE TABLE IF NOT EXISTS Part_CACHE_INDEX_100 (C_ID INT NOT NULL DEFAULT 99 REFERENCES CREATE_TABLE_000 (C_ID) MATCH PARTIAL ,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, CONSTRAINT pk_CACHE_INDEX_100 PRIMARY KEY USING BTREE (C_ID DESC) USING HASH, UNIQUE INDEX UNIQUE_index_CACHE_INDEX_100 (C_D_ID,C_ID,C_W_ID DESC) USING BTREE, KEY (C_FIRST,C_MIDDLE,C_LAST (20),C_TINYTEXT (2) ASC) COMMENT 'string', CHECK ('agdj56!#54'IS UNKNOWN)) AUTO_INCREMENT 9, AVG_ROW_LENGTH = 8, CHARACTER SET = utf8, CHECKSUM 1, DEFAULT COLLATE utf8_general_ci, COMMENT 'string', CONNECTION 'connect_string', DELAY_KEY_WRITE 0, ENGINE = MyISAM, INSERT_METHOD = LAST, MAX_ROWS 99, MIN_ROWS 99, ROW_FORMAT = DYNAMIC, STATS_PERSISTENT DEFAULT, STATS_SAMPLE_PAGES 9, TABLESPACE MYISAM_tablespace_name_000 PARTITION BY HASH (C_ID) (PARTITION partition_CACHE_INDEX_100 STORAGE ENGINE = MyISAM COMMENT = 'string' MAX_ROWS = 100 MIN_ROWS = 20 TABLESPACE MYISAM_tablespace_name_000 ,PARTITION partition_CACHE_INDEX_1001 COMMENT = 'string' MAX_ROWS 100 MIN_ROWS 20 ) ;

CACHE INDEX Part_CACHE_INDEX_100 INDEX(UNIQUE_index_CACHE_INDEX_100) PARTITION (ALL) IN keycache1_CACHE_INDEX_100;
CACHE INDEX Part_CACHE_INDEX_100 PARTITION (ALL) IN keycache1_CACHE_INDEX_100;
CACHE INDEX Part_CACHE_INDEX_100 INDEX(UNIQUE_index_CACHE_INDEX_100) IN keycache1_CACHE_INDEX_100;

[31 Jan 2019 6:12] JianJun Shi

Change the category to "MySQL Server: Documentation", not cluster

[31 Jan 2019 9:07] MySQL Verification Team

Hello JianJun,

Thank you for the report and feedback!

regards,
Umesh

[25 Apr 2019 14:41] Paul DuBois

Posted by developer:
 
You're correct, the syntax is either a list of one or more tables OR a PARTITION clause. In addition, PARTITION accepts only a single table name.

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