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, 563 insertions, 0 deletions

diff --git a/mysql-test/main/key_cache.test b/mysql-test/main/key_cache.test
new file mode 100644
index 00000000..0829719a
--- /dev/null
+++ b/mysql-test/main/key_cache.test
@@ -0,0 +1,563 @@
+#
+# Test of multiple key caches, simple and segmented
+#
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+
+SET @save_key_buffer_size=@@key_buffer_size;
+SET @save_key_cache_block_size=@@key_cache_block_size;
+SET @save_key_cache_segments=@@key_cache_segments;
+SET @save_key_cache_file_hash_size=@@key_cache_file_hash_size;
+
+SELECT @@key_buffer_size, @@small.key_buffer_size;
+
+# Change default key cache size
+SET @@global.key_buffer_size=16*1024*1024;
+SET @@global.default.key_buffer_size=16*1024*1024;
+SET @@global.default.key_buffer_size=16*1024*1024;
+
+SET @@global.small.key_buffer_size=1*1024*1024;
+SET @@global.medium.key_buffer_size=4*1024*1024;
+# Drop buffer
+SET @@global.medium.key_buffer_size=0;
+# Test double drop
+SET @@global.medium.key_buffer_size=0;
+
+# Print key buffer with different syntaxes
+SHOW VARIABLES like "key_buffer_size";
+SELECT @@key_buffer_size;
+SELECT @@global.key_buffer_size;
+SELECT @@global.default.key_buffer_size;
+SELECT @@global.default.`key_buffer_size`;
+SELECT @@global.`default`.`key_buffer_size`;
+SELECT @@`default`.key_buffer_size;
+
+SELECT @@small.key_buffer_size;
+SELECT @@medium.key_buffer_size;
+
+SET @@global.key_buffer_size=@save_key_buffer_size;
+
+#
+# Errors
+#
+
+--error 1064
+SELECT @@default.key_buffer_size;
+--error ER_VARIABLE_IS_NOT_STRUCT
+SELECT @@skr.default_storage_engine="test";
+
+select @@keycache1.key_cache_block_size;
+select @@keycache1.key_buffer_size;
+set global keycache1.key_cache_block_size=2048;
+select @@keycache1.key_buffer_size;
+select @@keycache1.key_cache_block_size;
+set global keycache1.key_buffer_size=1*1024*1024;
+select @@keycache1.key_buffer_size;
+select @@keycache1.key_cache_block_size;
+set global keycache2.key_buffer_size=4*1024*1024;
+select @@keycache2.key_buffer_size;
+select @@keycache2.key_cache_block_size;
+set global keycache1.key_buffer_size=0;
+select @@keycache1.key_buffer_size;
+select @@keycache1.key_cache_block_size;
+select @@key_buffer_size;
+select @@key_cache_block_size;
+select @@key_cache_file_hash_size;
+
+set global keycache1.key_buffer_size=1024*1024;
+
+let org_key_blocks_unused=`select unused_blocks as unused from information_schema.key_caches where key_cache_name="default"`;
+--disable_query_log
+eval set @org_key_blocks_unused=$org_key_blocks_unused;
+--enable_query_log
+
+create table t1 (p int primary key, a char(10)) delay_key_write=1;
+create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a));
+
+select @org_key_blocks_unused-unused_blocks as key_blocks_unused, used_blocks as key_blocks_used from information_schema.key_caches where key_cache_name="default";
+
+insert into t1 values (1, 'qqqq'), (11, 'yyyy');
+insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
+                      (3, 1, 'yyyy'), (4, 3, 'zzzz');
+select * from t1;
+select * from t2;
+
+update t1 set p=2 where p=1;
+update t2 set i=2 where i=1;
+
+select @org_key_blocks_unused-unused_blocks as key_blocks_unused, used_blocks as key_blocks_used from information_schema.key_caches where key_cache_name="default";
+
+cache index t1 key (`primary`) in keycache1;
+
+explain select p from t1;
+select p from t1;
+explain select i from t2;
+select i from t2;
+explain select count(*) from t1, t2 where t1.p = t2.i;
+select count(*) from t1, t2 where t1.p = t2.i;
+
+cache index t2 in keycache1;
+update t2 set p=p+1000, i=2 where a='qqqq';
+cache index t2 in keycache2;
+insert into t2 values (2000, 3, 'yyyy');
+cache index t2 in keycache1;
+update t2 set p=3000 where a='zzzz';
+select * from t2;
+explain select p from t2;
+select p from t2;
+explain select i from t2;
+select i from t2;
+explain select a from t2;
+select a from t2;
+
+# Test some error conditions
+--error 1284
+cache index t1 in unknown_key_cache;
+cache index t1 key (unknown_key) in keycache1;
+
+select @@keycache2.key_buffer_size;
+select @@keycache2.key_cache_block_size;
+set global keycache2.key_buffer_size=0;
+select @@keycache2.key_buffer_size;
+select @@keycache2.key_cache_block_size;
+set global keycache2.key_buffer_size=1024*1024;
+select @@keycache2.key_buffer_size;
+
+update t2 set p=4000 where a='zzzz';
+update t1 set p=p+1;
+
+set global keycache1.key_buffer_size=0;
+select * from t2;
+select p from t2;
+explain select i from t2;
+select i from t2;
+explain select a from t2;
+select a from t2;
+
+select * from t1;
+select p from t1;
+
+# Use the 'small' key cache
+create table t3 (like t1);
+cache index t3 in small;
+insert into t3 select * from t1;
+cache index t3 in keycache2;
+cache index t1,t2 in default;
+drop table t1,t2,t3;
+
+select @org_key_blocks_unused-unused_blocks as key_blocks_unused, used_blocks as key_blocks_used from information_schema.key_caches where key_cache_name="default";
+
+create table t1 (a int primary key);
+cache index t1 in keycache2;
+insert t1 values (1),(2),(3),(4),(5),(6),(7),(8);
+# delete keycache2, t1 is reassigned to default
+set global keycache2.key_buffer_size=0;
+select * from t1;
+drop table t1;
+
+# Test to set up a too small size for a key cache (bug #2064)
+set global keycache3.key_buffer_size=100;
+set global keycache3.key_buffer_size=0;
+
+# Test case for bug 6447
+
+create table t1 (mytext text, FULLTEXT (mytext));
+insert t1 values ('aaabbb');
+
+check table t1;
+set @my_key_cache_block_size= @@global.key_cache_block_size;
+set GLOBAL key_cache_block_size=2048;
+check table t1;
+drop table t1;
+# Restore the changed variable value
+set global key_cache_block_size= @my_key_cache_block_size;
+
+#
+# Bug #19079: corrupted index when key_cache_block_size is not multiple of
+#             myisam_block_size
+
+CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY);
+SET @my_key_cache_block_size= @@global.key_cache_block_size;
+SET GLOBAL key_cache_block_size=1536;
+INSERT INTO t1 VALUES (1);
+SELECT @@key_cache_block_size;
+CHECK TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int);
+CREATE TABLE t2(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int);
+SET GLOBAL key_cache_block_size=1536;
+INSERT INTO t1 VALUES (1,0);
+INSERT INTO t2(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t2;
+INSERT INTO t2(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t2;
+INSERT INTO t2(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t2;
+INSERT INTO t2(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t2;
+INSERT INTO t2(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t2;
+INSERT INTO t2(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t2;
+INSERT INTO t2(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t2;
+INSERT INTO t2(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t2;
+INSERT INTO t2(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t2;
+SELECT COUNT(*) FROM t1;
+SELECT @@key_cache_block_size;
+CHECK TABLE t1;
+DROP TABLE t1,t2;
+# Restore changed variables
+set global key_cache_block_size= @my_key_cache_block_size;
+
+#
+# Bug#10473 - Can't set 'key_buffer_size' system variable to ZERO
+# (One cannot drop the default key cache.)
+#
+--error ER_WARN_CANT_DROP_DEFAULT_KEYCACHE
+set @@global.key_buffer_size=0;
+select @@global.key_buffer_size;
+
+#
+# Bug#28478 - Improper key_cache_block_size corrupts MyISAM tables
+#
+SET @bug28478_key_cache_block_size= @@global.key_cache_block_size;
+SET GLOBAL key_cache_block_size= 1536;
+CREATE TABLE t1 (
+  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  c1 CHAR(150),
+  c2 CHAR(150),
+  c3 CHAR(150),
+  KEY(c1, c2, c3)
+  ) ENGINE= MyISAM;
+INSERT INTO t1 (c1, c2, c3) VALUES
+  ('a', 'b', 'c'), ('b', 'c', 'd'), ('c', 'd', 'e'), ('d', 'e', 'f'),
+  ('e', 'f', 'g'), ('f', 'g', 'h'), ('g', 'h', 'i'), ('h', 'i', 'j'),
+  ('i', 'j', 'k'), ('j', 'k', 'l'), ('k', 'l', 'm'), ('l', 'm', 'n'),
+  ('m', 'n', 'o'), ('n', 'o', 'p'), ('o', 'p', 'q'), ('p', 'q', 'r'),
+  ('q', 'r', 's'), ('r', 's', 't'), ('s', 't', 'u'), ('t', 'u', 'v'),
+  ('u', 'v', 'w'), ('v', 'w', 'x'), ('w', 'x', 'y'), ('x', 'y', 'z');
+INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1;
+INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1;
+INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1;
+CHECK TABLE t1;
+SHOW VARIABLES LIKE 'key_cache_block_size';
+SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size;
+DROP TABLE t1;
+
+# End of 4.1 tests
+
+--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;
+INSERT INTO t1 VALUES (1, 1);
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 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;
+
+# End of 5.1 tests
+
+#
+# Test cases for segmented key caches
+#
+
+# Test usage of the KEY_CACHE table from information schema
+# for a simple key cache
+ 
+--disable_ps2_protocol
+set global key_buffer_size=@save_key_buffer_size;
+set global key_cache_block_size=@save_key_cache_block_size;
+select @@key_buffer_size;
+select @@key_cache_block_size;
+select @@key_cache_segments;
+
+create table t1 (
+  p int not null auto_increment primary key,
+  a char(10));
+create table t2 (
+  p int  not null auto_increment primary key,
+  i int, a char(10), key k1(i), key k2(a));
+
+select @@key_cache_segments;
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+insert into t1 values (1, 'qqqq'), (2, 'yyyy');
+insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
+                      (3, 1, 'yyyy'), (4, 3, 'zzzz');
+select * from t1;
+select * from t2;
+update t1 set p=3 where p=1;
+update t2 set i=2 where i=1;
+
+select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused';
+select variable_value into @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused';
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+delete from t2 where a='zzzz';
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+delete from t1;
+delete from t2;
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+# For the key cache with 2 segments execute the same sequence of
+# statements as for the simple cache above.
+# The statistical information on the number of i/o requests and
+# the number of is expected to be the same.    
+
+set global key_cache_segments=2;
+select @@key_cache_segments;
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+insert into t1 values (1, 'qqqq'), (2, 'yyyy');
+insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
+                      (3, 1, 'yyyy'), (4, 3, 'zzzz');
+select * from t1;
+select * from t2;
+update t1 set p=3 where p=1;
+update t2 set i=2 where i=1;
+
+
+select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused';
+select variable_value < @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused';
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+delete from t1;
+delete from t2;
+--replace_column 7 # 
+select * from information_schema.key_caches where segment_number is null;
+
+# Check that we can work with one segment with the same results
+
+set global key_cache_segments=1;
+select @@key_cache_segments;
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+insert into t1 values (1, 'qqqq'), (2, 'yyyy');
+insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
+                      (3, 1, 'yyyy'), (4, 3, 'zzzz');
+select * from t1;
+select * from t2;
+update t1 set p=3 where p=1;
+update t2 set i=2 where i=1;
+
+select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused';
+select variable_value = @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused';
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+delete from t1;
+delete from t2;
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+flush tables; flush status;
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+# Switch back to 2 segments
+
+set global key_buffer_size=32*1024;
+set global key_cache_file_hash_size=128;
+select @@key_buffer_size;
+set global key_cache_segments=2;
+select @@key_cache_segments;
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+insert into t1 values (1, 'qqqq'), (2, 'yyyy');
+insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),
+                      (3, 1, 'yyyy'), (4, 3, 'zzzz');
+select * from t1;
+select * from t2;
+update t1 set p=3 where p=1;
+update t2 set i=2 where i=1;
+
+--replace_column 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+# Add more rows to tables t1 and t2 
+
+insert into t1(a) select a from t1;
+insert into t1(a) select a from t1;
+insert into t1(a) select a from t1;
+insert into t1(a) select a from t1;
+insert into t1(a) select a from t1;
+insert into t1(a) select a from t1;
+insert into t1(a) select a from t1;
+insert into t1(a) select a from t1;
+
+insert into t2(i,a) select i,a from t2;
+insert into t2(i,a) select i,a from t2;
+insert into t2(i,a) select i,a from t2;
+insert into t2(i,a) select i,a from t2;
+insert into t2(i,a) select i,a from t2;
+insert into t2(i,a) select i,a from t2;
+insert into t2(i,a) select i,a from t2;
+insert into t2(i,a) select i,a from t2;
+
+--replace_column 6 # 7 # 10 #
+select * from information_schema.key_caches where segment_number is null;
+
+select * from t1 where p between 1010 and 1020 ;
+select * from t2 where p between 1010 and 1020 ;
+--replace_column 6 # 7 # 10 #
+select * from information_schema.key_caches where segment_number is null;
+
+flush tables; flush status;
+update t1 set a='zzzz' where a='qqqq';
+update t2 set i=1 where i=2;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+# Now test how we can work with 7 segments 
+
+set global keycache1.key_buffer_size=256*1024;
+select @@keycache1.key_buffer_size;
+set global keycache1.key_cache_segments=7;
+select @@keycache1.key_cache_segments;
+
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+--replace_column 7 #
+select * from information_schema.key_caches where key_cache_name like "key%"
+                                                  and segment_number is null;
+
+cache index t1 key (`primary`) in keycache1;
+
+explain select p from t1 where p between 1010 and 1020;
+select p from t1 where p between 1010 and 1020;
+explain select i from t2 where p between 1010 and 1020;
+select i from t2 where p between 1010 and 1020;
+explain select count(*) from t1, t2 where t1.p = t2.i;
+select count(*) from t1, t2 where t1.p = t2.i;
+
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+--replace_column 7 #
+select * from information_schema.key_caches where key_cache_name like "key%"
+                                                  and segment_number is null;
+
+cache index t2 in keycache1;
+update t2 set p=p+3000, i=2 where a='qqqq';
+--replace_column 7 #
+select * from information_schema.key_caches where key_cache_name like "key%"
+                                                  and segment_number is null;
+
+set global keycache2.key_buffer_size=1024*1024;
+cache index t2 in keycache2;
+insert into t2 values (2000, 3, 'yyyy');
+--replace_column 7 #
+select * from information_schema.key_caches where key_cache_name like "keycache2"
+                                                  and segment_number is null;
+--replace_column 7 #
+select * from information_schema.key_caches where key_cache_name like "key%" 
+                                                  and segment_number is null;
+
+cache index t2 in keycache1;
+update t2 set p=p+5000 where a='zzzz';
+select * from t2 where p between 1010 and 1020;
+explain select p from t2  where p between 1010 and 1020;
+select p from t2 where p between 1010 and 1020;
+explain select i from t2 where a='yyyy' and i=3;
+select i from t2 where a='yyyy' and i=3;
+explain select a from t2 where a='yyyy' and i=3;
+select a from t2 where a='yyyy' and i=3 ;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+set global keycache1.key_cache_block_size=2*1024;
+insert into t2 values (7000, 3, 'yyyy');
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+set global keycache1.key_cache_block_size=8*1024;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+insert into t2 values (8000, 3, 'yyyy');
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+set global keycache1.key_buffer_size=64*1024;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+set global keycache1.key_cache_block_size=2*1024;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+set global keycache1.key_cache_block_size=8*1024;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+set global keycache1.key_buffer_size=0;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+set global keycache1.key_cache_block_size=8*1024;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+set global keycache1.key_buffer_size=0;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+set global keycache1.key_buffer_size=128*1024;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+set global keycache1.key_cache_block_size=1024;
+--replace_column 6 # 7 #
+select * from information_schema.key_caches where segment_number is null;
+
+drop table t1,t2;
+
+set global keycache1.key_buffer_size=0;
+set global keycache2.key_buffer_size=0;
+
+set global key_buffer_size=@save_key_buffer_size;
+set global key_cache_segments=@save_key_cache_segments;
+set global key_cache_file_hash_size=@save_key_cache_file_hash_size;
+--enable_ps2_protocol
+
+# End of 5.2 tests
+
+--echo #
+--echo # SIGSEGV in flush_all_key_blocks when changing
+--echo # key_buffer_size / ASAN: heap-use-after-free in flush_all_key_blocks
+--echo #
+
+SET GLOBAL keycache1.key_cache_segments=7;
+SET GLOBAL keycache1.key_buffer_size=1*1024*1024;
+SET GLOBAL keycache1.key_buffer_size=0;
+SET GLOBAL keycache1.key_buffer_size=128*1024;
+create table t1 (p int primary key, a char(10)) delay_key_write=1;
+cache index t1 key (`primary`) in keycache1;
+insert into t1 values (1, 'qqqq'), (11, 'yyyy');
+select * from t1;
+drop table t1;
+SET GLOBAL keycache1.key_buffer_size=0;
+
+
+--echo #
+--echo # End of 10.3 tests
+--echo #