1 files changed, 229 insertions, 0 deletions
diff --git a/mysql-test/suite/pbxt/t/key_cache.test b/mysql-test/suite/pbxt/t/key_cache.test
new file mode 100644
index 00000000000..10dbdda6068
--- /dev/null
+++ b/mysql-test/suite/pbxt/t/key_cache.test
@@ -0,0 +1,229 @@
+#
+# Test of multiple key caches
+#
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+
+SET @save_key_buffer=@@key_buffer_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;
+
+#
+# Errors
+#
+
+--error 1064
+SELECT @@default.key_buffer_size;
+--error 1272
+SELECT @@skr.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;
+
+set global keycache1.key_buffer_size=1024*1024;
+
+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));
+
+# PBXT: this is irrelevant for PBXT and gives different results on different systems
+#show status like 'key_blocks_used';
+
+# Following results differs on 64 and 32 bit systems because of different
+# pointer sizes, which takes up different amount of space in key cache
+
+# PBXT: this is irrelevant for PBXT and gives different results on different systems
+#--replace_result 1812 KEY_BLOCKS_UNUSED 1793 KEY_BLOCKS_UNUSED 1674 KEY_BLOCKS_UNUSED 1818 KEY_BLOCKS_UNUSED 1824 KEY_BLOCKS_UNUSED
+#show status like 'key_blocks_unused';
+
+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;
+
+# PBXT: this is irrelevant for PBXT and gives different results on different systems
+#show status like 'key_blocks_used';
+#--replace_result 1808 KEY_BLOCKS_UNUSED 1789 KEY_BLOCKS_UNUSED 1670 KEY_BLOCKS_UNUSED 1814 KEY_BLOCKS_UNUSED 1820 KEY_BLOCKS_UNUSED
+#show status like 'key_blocks_unused';
+
+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 order by p; # PBXT required ORDER for consistant result
+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 order by p; # PBXT required ORDER for consistant result
+select p from t2 order by p; # PBXT required ORDER for consistant result
+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;
+
+# PBXT: this is irrelevant for PBXT and gives different results on different systems
+#show status like 'key_blocks_used';
+#--replace_result 1812 KEY_BLOCKS_UNUSED 1793 KEY_BLOCKS_UNUSED 1674 KEY_BLOCKS_UNUSED 1818 KEY_BLOCKS_UNUSED 1824 KEY_BLOCKS_UNUSED
+#show status like 'key_blocks_unused';
+
+
+# Cleanup
+# We don't reset keycache2 as we want to ensure that mysqld will reset it
+set global keycache2.key_buffer_size=0;
+
+# 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)) engine=myisam; # PBXT: Only MyISAM can do FULLTEXT
+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.)
+#
+set @@global.key_buffer_size=0;
+select @@global.key_buffer_size;
+
+--disable_query_log
+drop database pbxt;
+--enable_query_log
+# End of 4.1 tests