Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 279 insertions, 0 deletions

diff --git a/mysql-test/main/partition_key_cache.test b/mysql-test/main/partition_key_cache.test
new file mode 100644
index 00000000..365a571a
--- /dev/null
+++ b/mysql-test/main/partition_key_cache.test
@@ -0,0 +1,279 @@
+# Test of key cache with partitions
+--source include/have_partition.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, v, x;
+--enable_warnings
+
+--echo # Actual test of key caches
+--echo # Verifing that reads/writes use the key cache correctly
+SET @org_key_cache_buffer_size= @@global.default.key_buffer_size;
+--echo # 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;
+
+# Genereate 4096 rows. Idea from:
+# http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html
+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;
+# due to I_S performance, this was substituted with include files which
+# uses SHOW STATUS
+#DELIMITER |;
+#CREATE PROCEDURE was_zero_reads()
+#BEGIN
+#  SELECT IF(VARIABLE_VALUE = 0,"Yes!","No!") as 'Was zero reads?'
+#  FROM INFORMATION_SCHEMA.SESSION_STATUS
+#  WHERE VARIABLE_NAME = 'KEY_READS';
+#  FLUSH STATUS;
+#END|
+#DELIMITER ;|
+
+FLUSH STATUS;
+INSERT t1 SELECT @a, @a * (1 - ((@a % 2) * 2)) , 1167612400 - (@a:=@a+1) FROM x, x y;
+--source include/check_key_req.inc
+--echo # row distribution:
+SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' and TABLE_NAME='t1';
+DROP VIEW x;
+DROP VIEW v;
+FLUSH TABLES;
+FLUSH STATUS;
+SELECT COUNT(b) FROM t1 WHERE b >= 0;
+--source include/check_key_reads.inc
+INSERT t2 SELECT a,b,c FROM t1;
+--source include/check_key_req.inc
+FLUSH STATUS;
+SELECT COUNT(b) FROM t2 WHERE b >= 0;
+--source include/check_key_reads.inc
+FLUSH TABLES;
+--echo # Setting the default key cache to 1M
+SET GLOBAL key_buffer_size = 1024*1024;
+FLUSH STATUS;
+--echo # All these have to read the indexes
+LOAD INDEX INTO CACHE t1 PARTITION (p1);
+--source include/check_key_reads.inc
+SELECT COUNT(b) FROM t1 WHERE b >= 0;
+--source include/check_key_reads.inc
+SELECT COUNT(b) FROM t2 WHERE b >= 0;
+--source include/check_key_reads.inc
+--echo # All these should be able to use the key cache
+SELECT COUNT(b) FROM t1 WHERE b >= 0;
+--source include/check_key_reads.inc
+SELECT COUNT(b) FROM t2 WHERE b >= 0;
+--source include/check_key_reads.inc
+FLUSH TABLES;
+LOAD INDEX INTO CACHE t1 PARTITION (p1,p0);
+--source include/check_key_reads.inc
+--echo # should not be zero
+SELECT COUNT(b) FROM t1 WHERE b >= 0;
+--source include/check_key_reads.inc
+LOAD INDEX INTO CACHE t2;
+--source include/check_key_reads.inc
+--echo # should not be zero
+SELECT COUNT(b) FROM t2 WHERE b >= 0;
+--source include/check_key_reads.inc
+FLUSH TABLES;
+LOAD INDEX INTO CACHE t1 PARTITION (p1,p0) IGNORE LEAVES;
+--source include/check_key_reads.inc
+--echo # should not be zero
+SELECT COUNT(b) FROM t1 WHERE b >= 0;
+--source include/check_key_reads.inc
+LOAD INDEX INTO CACHE t2 IGNORE LEAVES;
+--source include/check_key_reads.inc
+--echo # should not be zero
+SELECT COUNT(b) FROM t2 WHERE b >= 0;
+--source include/check_key_reads.inc
+TRUNCATE TABLE t2;
+INSERT t2 SELECT a,b,c FROM t1;
+--source include/check_key_req.inc
+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;
+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;
+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;
+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;
+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;
+# Genereate 4096 rows. Idea from:
+# http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html
+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;
+SELECT COUNT(*) FROM t2;
+FLUSH TABLES;
+
+--echo # Restrict partitioned commands to partitioned tables only
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache;
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+CACHE INDEX t2 PARTITION (p0,`p1`) INDEX (`PRIMARY`) IN hot_cache;
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+CACHE INDEX t2 PARTITION (`p1`) INDEX (`PRIMARY`,`inx_b`) IN hot_cache;
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+CACHE INDEX t2 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN hot_cache;
+--echo # Basic key cache testing
+--echo # The manual correctly says: "The syntax of CACHE INDEX enables you to
+--echo # specify that only particular indexes from a table should be assigned
+--echo # to the cache. The current implementation assigns all the table's
+--echo # indexes to the cache, so there is no reason to specify anything
+--echo # other than the table name."
+--echo # So the most of the test only tests the syntax 
+CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache;
+CACHE INDEX t2 KEY (`PRIMARY`) IN warm_cache;
+CACHE INDEX t2 KEY (`PRIMARY`,`inx_b`) IN cold_cache;
+CACHE INDEX t2 INDEX (inx_b,`PRIMARY`) IN default;
+CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN cold_cache;
+--error ER_PARSE_ERROR
+CACHE INDEX t1 PARTITIONS (p0) KEY (`inx_b`) IN cold_cache;
+--echo # only one table at a time if specifying partitions
+--error ER_PARSE_ERROR
+CACHE INDEX t1,t2 PARTITION (p0) KEY (`inx_b`) IN cold_cache;
+CACHE INDEX t1 PARTITION (`p0`,p1) INDEX (`PRIMARY`) IN warm_cache;
+CACHE INDEX t1 PARTITION (`p1`) INDEX (`PRIMARY`,inx_b) IN hot_cache;
+CACHE INDEX t1 PARTITION (ALL) KEY (`inx_b`,`PRIMARY`) IN default;
+CACHE INDEX t1 PARTITION (ALL) IN hot_cache;
+CACHE INDEX t1 INDEX (`inx_b`) IN default;
+CACHE INDEX t1 KEY (`PRIMARY`) IN hot_cache;
+CACHE INDEX t1 KEY (`PRIMARY`,`inx_b`) IN warm_cache;
+CACHE INDEX t1 INDEX (`inx_b`,`PRIMARY`) IN cold_cache;
+CACHE INDEX t1 IN hot_cache;
+--echo # Test of non existent key cache:
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 IN non_existent_key_cache;
+--echo # Basic testing of LOAD INDEX
+LOAD INDEX INTO CACHE t2;
+--echo # PRIMARY and secondary keys have different block sizes
+LOAD INDEX INTO CACHE t2 ignore leaves;
+--echo # Must have INDEX or KEY before the index list
+--error ER_PARSE_ERROR
+LOAD INDEX INTO CACHE t2 (`PRIMARY`);
+
+--echo # Test of IGNORE LEAVES
+LOAD INDEX INTO CACHE t2 INDEX (`PRIMARY`);
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`) IGNORE LEAVES;
+CACHE INDEX t2 IN warm_cache;
+CACHE INDEX t1 IN cold_cache;
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`) IGNORE LEAVES;
+CACHE INDEX t2 INDEX (`inx_b`, `inx_c`) IN hot_cache;
+LOAD INDEX INTO CACHE t2 KEY (`inx_b`, `inx_c`) IGNORE LEAVES;
+CACHE INDEX t2 IN warm_cache;
+CACHE INDEX t2 INDEX (`PRIMARY`, `inx_c`) IN hot_cache;
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_c`) IGNORE LEAVES;
+CACHE INDEX t2 INDEX (`inx_b`,`PRIMARY`) IN default;
+LOAD INDEX INTO CACHE t2 KEY (`PRIMARY`,`inx_b`);
+CACHE INDEX t2 IN default;
+LOAD INDEX INTO CACHE t2 IGNORE LEAVES;
+
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+LOAD INDEX INTO CACHE t2 PARTITION (p1) INDEX (`PRIMARY`);
+LOAD INDEX INTO CACHE t1, t2;
+--echo # only one table at a time if specifying partitions
+--error ER_PARSE_ERROR
+LOAD INDEX INTO CACHE t1 PARTITION (p0), t2;
+LOAD INDEX INTO CACHE t1 IGNORE LEAVES;
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`);
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`) IGNORE LEAVES;
+LOAD INDEX INTO CACHE t1 INDEX (`inx_b`) IGNORE LEAVES;
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`) IGNORE LEAVES;
+LOAD INDEX INTO CACHE t1 INDEX (`PRIMARY`,`inx_b`);
+LOAD INDEX INTO CACHE t1 PARTITION (p1) INDEX (`PRIMARY`);
+LOAD INDEX INTO CACHE t1 PARTITION (`p1`,p0) KEY (`PRIMARY`) IGNORE LEAVES;
+LOAD INDEX INTO CACHE t1 PARTITION (ALL);
+--error ER_PARSE_ERROR
+LOAD INDEX INTO CACHE t1 PARTITIONS ALL;
+LOAD INDEX INTO CACHE t1 PARTITION (p1,`p0`) IGNORE LEAVES;
+DROP INDEX `inx_b` on t1;
+DROP INDEX `inx_b` on t2;
+--error ER_PARTITION_MGMT_ON_NONPARTITIONED
+CACHE INDEX t2 PARTITION (p0) KEY (`inx_b`) IN hot_cache;
+CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache;
+CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN hot_cache;
+CACHE INDEX t1 INDEX (`inx_b`) IN hot_cache;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug#12361113: crash when load index into cache
+--echo #
+--echo # 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);
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 IN key_cache_none;
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 PARTITION (p0) IN key_cache_none;
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 PARTITION (p1) IN key_cache_none;
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 PARTITION (p0) KEY (`b`) IN key_cache_none;
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 PARTITION (p1) KEY (`b`) IN key_cache_none;
+--echo # The bug crashed the server at LOAD INDEX below. Now it will succeed
+--echo # since the default cache is used due to CACHE INDEX failed for
+--echo # key_cache_none.
+LOAD INDEX INTO CACHE t1;
+DROP TABLE t1;
+
+
+--echo # 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;
+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;
+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;
+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;
+--disable_warnings
+SET @@global.default.key_buffer_size = @org_key_cache_buffer_size;
+--enable_warnings