DROP TABLE IF EXISTS t1, t2, v, x;
# Actual test of key caches
# Verifing that reads/writes use the key cache correctly
SET @org_key_cache_buffer_size= @@global.default.key_buffer_size;
# Minimize default key cache (almost disabled).
SET @@global.default.key_buffer_size = 4096;
CREATE TABLE t1 (
a INT,
b INT,
c INT NOT NULL,
PRIMARY KEY (a),
KEY `inx_b` (b))
PARTITION BY RANGE (a)
SUBPARTITION BY HASH (a)
(PARTITION p0 VALUES LESS THAN (1167602410)
(SUBPARTITION sp0,
SUBPARTITION sp1),
PARTITION p1 VALUES LESS THAN MAXVALUE
(SUBPARTITION sp2,
SUBPARTITION sp3));
CREATE TABLE t2 (
a INT,
b INT,
c INT NOT NULL,
PRIMARY KEY (a),
KEY `inx_b` (b));
FLUSH TABLES;
FLUSH STATUS;
SET @a:=1167602400;
CREATE VIEW v AS SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4;
CREATE VIEW x AS SELECT 1 FROM v,v a,v b;
FLUSH STATUS;
INSERT t1 SELECT @a, @a * (1 - ((@a % 2) * 2)) , 1167612400 - (@a:=@a+1) FROM x, x y;
reads vs requests
reads == requests
writes vs requests
writes == requests
# row distribution:
SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' and TABLE_NAME='t1';
PARTITION_NAME	SUBPARTITION_NAME	TABLE_ROWS
p0	sp0	5
p0	sp1	5
p1	sp2	2043
p1	sp3	2043
DROP VIEW x;
DROP VIEW v;
FLUSH TABLES;
FLUSH STATUS;
SELECT COUNT(b) FROM t1 WHERE b >= 0;
COUNT(b)
2048
Zero key reads?
Yes!
INSERT t2 SELECT a,b,c FROM t1;
reads vs requests
reads == requests
writes vs requests
writes == requests
FLUSH STATUS;
SELECT COUNT(b) FROM t2 WHERE b >= 0;
COUNT(b)
2048
Zero key reads?
Yes!
FLUSH TABLES;
# Setting the default key cache to 1M
SET GLOBAL key_buffer_size = 1024*1024;
FLUSH STATUS;
# All these have to read the indexes
LOAD INDEX INTO CACHE t1 PARTITION (p1);
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	status	OK
Zero key reads?
No!
SELECT COUNT(b) FROM t1 WHERE b >= 0;
COUNT(b)
2048
Zero key reads?
No!
SELECT COUNT(b) FROM t2 WHERE b >= 0;
COUNT(b)
2048
Zero key reads?
No!
# All these should be able to use the key cache
SELECT COUNT(b) FROM t1 WHERE b >= 0;
COUNT(b)
2048
Zero key reads?
Yes!
SELECT COUNT(b) FROM t2 WHERE b >= 0;
COUNT(b)
2048
Zero key reads?
Yes!
FLUSH TABLES;
LOAD INDEX INTO CACHE t1 PARTITION (p1,p0);
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	status	OK
Zero key reads?
No!
# should not be zero
SELECT COUNT(b) FROM t1 WHERE b >= 0;
COUNT(b)
2048
Zero key reads?
Yes!
LOAD INDEX INTO CACHE t2;
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	status	OK
Zero key reads?
No!
# should not be zero
SELECT COUNT(b) FROM t2 WHERE b >= 0;
COUNT(b)
2048
Zero key reads?
Yes!
FLUSH TABLES;
LOAD INDEX INTO CACHE t1 PARTITION (p1,p0) IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	status	OK
Zero key reads?
No!
# should not be zero
SELECT COUNT(b) FROM t1 WHERE b >= 0;
COUNT(b)
2048
Zero key reads?
No!
LOAD INDEX INTO CACHE t2 IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	status	OK
Zero key reads?
No!
# should not be zero
SELECT COUNT(b) FROM t2 WHERE b >= 0;
COUNT(b)
2048
Zero key reads?
No!
TRUNCATE TABLE t2;
INSERT t2 SELECT a,b,c FROM t1;
reads vs requests
reads != requests
writes vs requests
writes != requests
DROP TABLE t1,t2;
SET GLOBAL hot_cache.key_buffer_size = 1024*1024;
SET GLOBAL warm_cache.key_buffer_size = 1024*1024;
SET @@global.cold_cache.key_buffer_size = 1024*1024;
SELECT @@global.default.key_buffer_size a, @@global.default.key_cache_block_size b, @@global.default.key_cache_age_threshold c, @@global.default.key_cache_division_limit d;
a	b	c	d
1048576	1024	300	100
SELECT @@global.hot_cache.key_buffer_size a, @@global.hot_cache.key_cache_block_size b, @@global.hot_cache.key_cache_age_threshold c, @@global.hot_cache.key_cache_division_limit d;
a	b	c	d
1048576	1024	300	100
SELECT @@global.warm_cache.key_buffer_size a, @@global.warm_cache.key_cache_block_size b, @@global.warm_cache.key_cache_age_threshold c, @@global.warm_cache.key_cache_division_limit d;
a	b	c	d
1048576	1024	300	100
SELECT @@global.cold_cache.key_buffer_size a, @@global.cold_cache.key_cache_block_size b, @@global.cold_cache.key_cache_age_threshold c, @@global.cold_cache.key_cache_division_limit d;
a	b	c	d
1048576	1024	300	100
CREATE TABLE t1 (
a INT,
b VARCHAR(257),
c INT NOT NULL,
PRIMARY KEY (a),
KEY `inx_b` (b),
KEY `inx_c`(c))
PARTITION BY RANGE (a)
SUBPARTITION BY HASH (a)
(PARTITION p0 VALUES LESS THAN (10)
(SUBPARTITION sp0,
SUBPARTITION sp1),
PARTITION p1 VALUES LESS THAN MAXVALUE
(SUBPARTITION sp2,
SUBPARTITION sp3));
CREATE TABLE t2 (
a INT,
b VARCHAR(257),
c INT NOT NULL,
PRIMARY KEY (a),
KEY `inx_b` (b),
KEY `inx_c`(c));
SET @a:=1167602400;
CREATE VIEW v AS SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4;
CREATE VIEW x AS SELECT 1 FROM v,v a,v b;
INSERT t1 SELECT @a, CONCAT('X_', @a, ' MySQL'), 1167612400 - (@a:=@a+1) FROM x, x a;
DROP VIEW x;
DROP VIEW v;
INSERT t2 SELECT a, b, c FROM t1;
SELECT COUNT(*) FROM t1;
COUNT(*)
4096
SELECT COUNT(*) FROM t2;
COUNT(*)
4096
FLUSH TABLES;
# Restrict partitioned commands to partitioned tables only
CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache;
ERROR HY000: Partition management on a not partitioned table is not possible
CACHE INDEX t2 PARTITION (p0,`p1`) INDEX (`PRIMARY`) IN hot_cache;
ERROR HY000: Partition management on a not partitioned table is not possible
CACHE INDEX t2 PARTITION (`p1`) INDEX (`PRIMARY`,`inx_b`) IN hot_cache;
ERROR HY000: Partition management on a not partitioned table is not possible
CACHE INDEX t2 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN hot_cache;
ERROR HY000: Partition management on a not partitioned table is not possible
# Basic key cache testing
# The manual correctly says: "The syntax of CACHE INDEX enables you to
# specify that only particular indexes from a table should be assigned
# to the cache. The current implementation assigns all the table's
# indexes to the cache, so there is no reason to specify anything
# other than the table name."
# So the most of the test only tests the syntax 
CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	status	OK
CACHE INDEX t2 KEY (`PRIMARY`) IN warm_cache;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	status	OK
CACHE INDEX t2 KEY (`PRIMARY`,`inx_b`) IN cold_cache;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	status	OK
CACHE INDEX t2 INDEX (inx_b,`PRIMARY`) IN default;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	status	OK
CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN cold_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
CACHE INDEX t1 PARTITIONS (p0) KEY (`inx_b`) IN cold_cache;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PARTITIONS (p0) KEY (`inx_b`) IN cold_cache' at line 1
# only one table at a time if specifying partitions
CACHE INDEX t1,t2 PARTITION (p0) KEY (`inx_b`) IN cold_cache;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PARTITION (p0) KEY (`inx_b`) IN cold_cache' at line 1
CACHE INDEX t1 PARTITION (`p0`,p1) INDEX (`PRIMARY`) IN warm_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
CACHE INDEX t1 PARTITION (`p1`) INDEX (`PRIMARY`,inx_b) IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
CACHE INDEX t1 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN default;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
CACHE INDEX t1 PARTITION (ALL) IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
CACHE INDEX t1 INDEX (`inx_b`) IN default;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
CACHE INDEX t1 KEY (`PRIMARY`) IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
CACHE INDEX t1 KEY (`PRIMARY`,`inx_b`) IN warm_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
CACHE INDEX t1 INDEX (`inx_b`,`PRIMARY`) IN cold_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
CACHE INDEX t1 IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
# Test of non existent key cache:
CACHE INDEX t1 IN non_existent_key_cache;
ERROR HY000: Unknown key cache 'non_existent_key_cache'
# Basic testing of LOAD INDEX
LOAD INDEX INTO CACHE t2;
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	status	OK
# PRIMARY and secondary keys have different block sizes
LOAD INDEX INTO CACHE t2 ignore leaves;
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	error	Indexes use different block sizes
test.t2	preload_keys	status	Operation failed
# Must have INDEX or KEY before the index list
LOAD INDEX INTO CACHE t2 (`PRIMARY`);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(`PRIMARY`)' at line 1
# Test of IGNORE LEAVES
LOAD INDEX INTO CACHE t2 INDEX (`PRIMARY`);
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	status	OK
LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`) IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	error	Indexes use different block sizes
test.t2	preload_keys	status	Operation failed
CACHE INDEX t2 IN warm_cache;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	status	OK
CACHE INDEX t1 IN cold_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	status	OK
LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`) IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	error	Indexes use different block sizes
test.t2	preload_keys	status	Operation failed
CACHE INDEX t2 INDEX (`inx_b`, `inx_c`) IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	status	OK
LOAD INDEX INTO CACHE t2 KEY (`inx_b`, `inx_c`) IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	error	Indexes use different block sizes
test.t2	preload_keys	status	Operation failed
CACHE INDEX t2 IN warm_cache;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	status	OK
CACHE INDEX t2 INDEX (`PRIMARY`, `inx_c`) IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	status	OK
LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_c`) IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	error	Indexes use different block sizes
test.t2	preload_keys	status	Operation failed
CACHE INDEX t2 INDEX (`inx_b`,`PRIMARY`) IN default;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	status	OK
LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`);
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	status	OK
CACHE INDEX t2 IN default;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	status	OK
LOAD INDEX INTO CACHE t2 IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t2	preload_keys	error	Indexes use different block sizes
test.t2	preload_keys	status	Operation failed
LOAD INDEX INTO CACHE t2 PARTITION (p1) INDEX (`PRIMARY`);
ERROR HY000: Partition management on a not partitioned table is not possible
LOAD INDEX INTO CACHE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	status	OK
test.t2	preload_keys	status	OK
# only one table at a time if specifying partitions
LOAD INDEX INTO CACHE t1 PARTITION (p0), t2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' t2' at line 1
LOAD INDEX INTO CACHE t1 IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	error	Indexes use different block sizes
test.t1	preload_keys	error	Subpartition sp2 returned error
test.t1	preload_keys	status	Operation failed
LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`);
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	status	OK
LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`) IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	error	Indexes use different block sizes
test.t1	preload_keys	error	Subpartition sp2 returned error
test.t1	preload_keys	status	Operation failed
LOAD INDEX INTO CACHE t1 INDEX (`inx_b`) IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	error	Indexes use different block sizes
test.t1	preload_keys	error	Subpartition sp2 returned error
test.t1	preload_keys	status	Operation failed
LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`) IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	error	Indexes use different block sizes
test.t1	preload_keys	error	Subpartition sp2 returned error
test.t1	preload_keys	status	Operation failed
LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`);
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	status	OK
LOAD INDEX INTO CACHE t1 PARTITION (p1) INDEX (`PRIMARY`);
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	status	OK
LOAD INDEX INTO CACHE t1 PARTITION (`p1`,p0) KEY (`PRIMARY`) IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	error	Indexes use different block sizes
test.t1	preload_keys	error	Subpartition sp2 returned error
test.t1	preload_keys	status	Operation failed
LOAD INDEX INTO CACHE t1 PARTITION (ALL);
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	status	OK
LOAD INDEX INTO CACHE t1 PARTITIONS ALL;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PARTITIONS ALL' at line 1
LOAD INDEX INTO CACHE t1 PARTITION (p1,`p0`) IGNORE LEAVES;
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	error	Indexes use different block sizes
test.t1	preload_keys	error	Subpartition sp2 returned error
test.t1	preload_keys	status	Operation failed
DROP INDEX `inx_b` on t1;
DROP INDEX `inx_b` on t2;
CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache;
ERROR HY000: Partition management on a not partitioned table is not possible
CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t2	assign_to_keycache	Error	Key 'inx_b' doesn't exist in table 't2'
test.t2	assign_to_keycache	status	Operation failed
CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	error	Subpartition sp0 returned error
test.t1	assign_to_keycache	Error	Key 'inx_b' doesn't exist in table 't1'
test.t1	assign_to_keycache	status	Operation failed
CACHE INDEX t1 INDEX (`inx_b`) IN hot_cache;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	error	Subpartition sp0 returned error
test.t1	assign_to_keycache	Error	Key 'inx_b' doesn't exist in table 't1'
test.t1	assign_to_keycache	status	Operation failed
DROP TABLE t1,t2;
#
# Bug#12361113: crash when load index into cache
#
# Note that this creates an empty disabled key cache!
SET GLOBAL key_cache_none.key_cache_block_size = 1024;
CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) )
ENGINE = MYISAM
PARTITION BY HASH(a) PARTITIONS 2;
INSERT INTO t1 VALUES (1, 1);
CACHE INDEX t1 IN key_cache_none;
ERROR HY000: Unknown key cache 'key_cache_none'
CACHE INDEX t1 PARTITION (p0) IN key_cache_none;
ERROR HY000: Unknown key cache 'key_cache_none'
CACHE INDEX t1 PARTITION (p1) IN key_cache_none;
ERROR HY000: Unknown key cache 'key_cache_none'
CACHE INDEX t1 PARTITION (p0) KEY (`b`) IN key_cache_none;
ERROR HY000: Unknown key cache 'key_cache_none'
CACHE INDEX t1 PARTITION (p1) KEY (`b`) IN key_cache_none;
ERROR HY000: Unknown key cache 'key_cache_none'
# The bug crashed the server at LOAD INDEX below. Now it will succeed
# since the default cache is used due to CACHE INDEX failed for
# key_cache_none.
LOAD INDEX INTO CACHE t1;
Table	Op	Msg_type	Msg_text
test.t1	preload_keys	status	OK
DROP TABLE t1;
# Clean up
SET GLOBAL hot_cache.key_buffer_size = 0;
SET GLOBAL warm_cache.key_buffer_size = 0;
SET @@global.cold_cache.key_buffer_size = 0;
SELECT @@global.default.key_buffer_size a, @@global.default.key_cache_block_size b, @@global.default.key_cache_age_threshold c, @@global.default.key_cache_division_limit d;
a	b	c	d
1048576	1024	300	100
SELECT @@global.hot_cache.key_buffer_size a, @@global.hot_cache.key_cache_block_size b, @@global.hot_cache.key_cache_age_threshold c, @@global.hot_cache.key_cache_division_limit d;
a	b	c	d
0	1024	300	100
SELECT @@global.warm_cache.key_buffer_size a, @@global.warm_cache.key_cache_block_size b, @@global.warm_cache.key_cache_age_threshold c, @@global.warm_cache.key_cache_division_limit d;
a	b	c	d
0	1024	300	100
SELECT @@global.cold_cache.key_buffer_size a, @@global.cold_cache.key_cache_block_size b, @@global.cold_cache.key_cache_age_threshold c, @@global.cold_cache.key_cache_division_limit d;
a	b	c	d
0	1024	300	100
SET @@global.default.key_buffer_size = @org_key_cache_buffer_size;