1# 2# Test of multiple key caches, simple and segmented 3# 4--disable_warnings 5drop table if exists t1, t2, t3; 6--enable_warnings 7 8SET @save_key_buffer_size=@@key_buffer_size; 9SET @save_key_cache_block_size=@@key_cache_block_size; 10SET @save_key_cache_segments=@@key_cache_segments; 11SET @save_key_cache_file_hash_size=@@key_cache_file_hash_size; 12 13SELECT @@key_buffer_size, @@small.key_buffer_size; 14 15# Change default key cache size 16SET @@global.key_buffer_size=16*1024*1024; 17SET @@global.default.key_buffer_size=16*1024*1024; 18SET @@global.default.key_buffer_size=16*1024*1024; 19 20SET @@global.small.key_buffer_size=1*1024*1024; 21SET @@global.medium.key_buffer_size=4*1024*1024; 22# Drop buffer 23SET @@global.medium.key_buffer_size=0; 24# Test double drop 25SET @@global.medium.key_buffer_size=0; 26 27# Print key buffer with different syntaxes 28SHOW VARIABLES like "key_buffer_size"; 29SELECT @@key_buffer_size; 30SELECT @@global.key_buffer_size; 31SELECT @@global.default.key_buffer_size; 32SELECT @@global.default.`key_buffer_size`; 33SELECT @@global.`default`.`key_buffer_size`; 34SELECT @@`default`.key_buffer_size; 35 36SELECT @@small.key_buffer_size; 37SELECT @@medium.key_buffer_size; 38 39SET @@global.key_buffer_size=@save_key_buffer_size; 40 41# 42# Errors 43# 44 45--error 1064 46SELECT @@default.key_buffer_size; 47--error ER_VARIABLE_IS_NOT_STRUCT 48SELECT @@skr.storage_engine="test"; 49 50select @@keycache1.key_cache_block_size; 51select @@keycache1.key_buffer_size; 52set global keycache1.key_cache_block_size=2048; 53select @@keycache1.key_buffer_size; 54select @@keycache1.key_cache_block_size; 55set global keycache1.key_buffer_size=1*1024*1024; 56select @@keycache1.key_buffer_size; 57select @@keycache1.key_cache_block_size; 58set global keycache2.key_buffer_size=4*1024*1024; 59select @@keycache2.key_buffer_size; 60select @@keycache2.key_cache_block_size; 61set global keycache1.key_buffer_size=0; 62select @@keycache1.key_buffer_size; 63select @@keycache1.key_cache_block_size; 64select @@key_buffer_size; 65select @@key_cache_block_size; 66select @@key_cache_file_hash_size; 67 68set global keycache1.key_buffer_size=1024*1024; 69 70let org_key_blocks_unused=`select unused_blocks as unused from information_schema.key_caches where key_cache_name="default"`; 71--disable_query_log 72eval set @org_key_blocks_unused=$org_key_blocks_unused; 73--enable_query_log 74 75create table t1 (p int primary key, a char(10)) delay_key_write=1; 76create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a)); 77 78select @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"; 79 80insert into t1 values (1, 'qqqq'), (11, 'yyyy'); 81insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 82 (3, 1, 'yyyy'), (4, 3, 'zzzz'); 83select * from t1; 84select * from t2; 85 86update t1 set p=2 where p=1; 87update t2 set i=2 where i=1; 88 89select @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"; 90 91cache index t1 key (`primary`) in keycache1; 92 93explain select p from t1; 94select p from t1; 95explain select i from t2; 96select i from t2; 97explain select count(*) from t1, t2 where t1.p = t2.i; 98select count(*) from t1, t2 where t1.p = t2.i; 99 100cache index t2 in keycache1; 101update t2 set p=p+1000, i=2 where a='qqqq'; 102cache index t2 in keycache2; 103insert into t2 values (2000, 3, 'yyyy'); 104cache index t2 in keycache1; 105update t2 set p=3000 where a='zzzz'; 106select * from t2; 107explain select p from t2; 108select p from t2; 109explain select i from t2; 110select i from t2; 111explain select a from t2; 112select a from t2; 113 114# Test some error conditions 115--error 1284 116cache index t1 in unknown_key_cache; 117cache index t1 key (unknown_key) in keycache1; 118 119select @@keycache2.key_buffer_size; 120select @@keycache2.key_cache_block_size; 121set global keycache2.key_buffer_size=0; 122select @@keycache2.key_buffer_size; 123select @@keycache2.key_cache_block_size; 124set global keycache2.key_buffer_size=1024*1024; 125select @@keycache2.key_buffer_size; 126 127update t2 set p=4000 where a='zzzz'; 128update t1 set p=p+1; 129 130set global keycache1.key_buffer_size=0; 131select * from t2; 132select p from t2; 133explain select i from t2; 134select i from t2; 135explain select a from t2; 136select a from t2; 137 138select * from t1; 139select p from t1; 140 141# Use the 'small' key cache 142create table t3 (like t1); 143cache index t3 in small; 144insert into t3 select * from t1; 145cache index t3 in keycache2; 146cache index t1,t2 in default; 147drop table t1,t2,t3; 148 149select @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"; 150 151create table t1 (a int primary key); 152cache index t1 in keycache2; 153insert t1 values (1),(2),(3),(4),(5),(6),(7),(8); 154# delete keycache2, t1 is reassigned to default 155set global keycache2.key_buffer_size=0; 156select * from t1; 157drop table t1; 158 159# Test to set up a too small size for a key cache (bug #2064) 160set global keycache3.key_buffer_size=100; 161set global keycache3.key_buffer_size=0; 162 163# Test case for bug 6447 164 165create table t1 (mytext text, FULLTEXT (mytext)); 166insert t1 values ('aaabbb'); 167 168check table t1; 169set @my_key_cache_block_size= @@global.key_cache_block_size; 170set GLOBAL key_cache_block_size=2048; 171check table t1; 172drop table t1; 173# Restore the changed variable value 174set global key_cache_block_size= @my_key_cache_block_size; 175 176# 177# Bug #19079: corrupted index when key_cache_block_size is not multiple of 178# myisam_block_size 179 180CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY); 181SET @my_key_cache_block_size= @@global.key_cache_block_size; 182SET GLOBAL key_cache_block_size=1536; 183INSERT INTO t1 VALUES (1); 184SELECT @@key_cache_block_size; 185CHECK TABLE t1; 186DROP TABLE t1; 187 188CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int); 189CREATE TABLE t2(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int); 190SET GLOBAL key_cache_block_size=1536; 191INSERT INTO t1 VALUES (1,0); 192INSERT INTO t2(b) SELECT b FROM t1; 193INSERT INTO t1(b) SELECT b FROM t2; 194INSERT INTO t2(b) SELECT b FROM t1; 195INSERT INTO t1(b) SELECT b FROM t2; 196INSERT INTO t2(b) SELECT b FROM t1; 197INSERT INTO t1(b) SELECT b FROM t2; 198INSERT INTO t2(b) SELECT b FROM t1; 199INSERT INTO t1(b) SELECT b FROM t2; 200INSERT INTO t2(b) SELECT b FROM t1; 201INSERT INTO t1(b) SELECT b FROM t2; 202INSERT INTO t2(b) SELECT b FROM t1; 203INSERT INTO t1(b) SELECT b FROM t2; 204INSERT INTO t2(b) SELECT b FROM t1; 205INSERT INTO t1(b) SELECT b FROM t2; 206INSERT INTO t2(b) SELECT b FROM t1; 207INSERT INTO t1(b) SELECT b FROM t2; 208INSERT INTO t2(b) SELECT b FROM t1; 209INSERT INTO t1(b) SELECT b FROM t2; 210SELECT COUNT(*) FROM t1; 211SELECT @@key_cache_block_size; 212CHECK TABLE t1; 213DROP TABLE t1,t2; 214# Restore changed variables 215set global key_cache_block_size= @my_key_cache_block_size; 216 217# 218# Bug#10473 - Can't set 'key_buffer_size' system variable to ZERO 219# (One cannot drop the default key cache.) 220# 221--error ER_WARN_CANT_DROP_DEFAULT_KEYCACHE 222set @@global.key_buffer_size=0; 223select @@global.key_buffer_size; 224 225# 226# Bug#28478 - Improper key_cache_block_size corrupts MyISAM tables 227# 228SET @bug28478_key_cache_block_size= @@global.key_cache_block_size; 229SET GLOBAL key_cache_block_size= 1536; 230CREATE TABLE t1 ( 231 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 232 c1 CHAR(150), 233 c2 CHAR(150), 234 c3 CHAR(150), 235 KEY(c1, c2, c3) 236 ) ENGINE= MyISAM; 237INSERT INTO t1 (c1, c2, c3) VALUES 238 ('a', 'b', 'c'), ('b', 'c', 'd'), ('c', 'd', 'e'), ('d', 'e', 'f'), 239 ('e', 'f', 'g'), ('f', 'g', 'h'), ('g', 'h', 'i'), ('h', 'i', 'j'), 240 ('i', 'j', 'k'), ('j', 'k', 'l'), ('k', 'l', 'm'), ('l', 'm', 'n'), 241 ('m', 'n', 'o'), ('n', 'o', 'p'), ('o', 'p', 'q'), ('p', 'q', 'r'), 242 ('q', 'r', 's'), ('r', 's', 't'), ('s', 't', 'u'), ('t', 'u', 'v'), 243 ('u', 'v', 'w'), ('v', 'w', 'x'), ('w', 'x', 'y'), ('x', 'y', 'z'); 244INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; 245INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; 246INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; 247CHECK TABLE t1; 248SHOW VARIABLES LIKE 'key_cache_block_size'; 249SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size; 250DROP TABLE t1; 251 252# End of 4.1 tests 253 254--echo # 255--echo # Bug#12361113: crash when load index into cache 256--echo # 257 258--echo # Note that this creates an empty disabled key cache! 259SET GLOBAL key_cache_none.key_cache_block_size = 1024; 260CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) ) ENGINE = MYISAM; 261INSERT INTO t1 VALUES (1, 1); 262--error ER_UNKNOWN_KEY_CACHE 263CACHE INDEX t1 in key_cache_none; 264--echo # The bug crashed the server at LOAD INDEX below. Now it will succeed 265--echo # since the default cache is used due to CACHE INDEX failed for 266--echo # key_cache_none. 267LOAD INDEX INTO CACHE t1; 268DROP TABLE t1; 269 270# End of 5.1 tests 271 272# 273# Test cases for segmented key caches 274# 275 276# Test usage of the KEY_CACHE table from information schema 277# for a simple key cache 278 279set global key_buffer_size=@save_key_buffer_size; 280set global key_cache_block_size=@save_key_cache_block_size; 281select @@key_buffer_size; 282select @@key_cache_block_size; 283select @@key_cache_segments; 284 285create table t1 ( 286 p int not null auto_increment primary key, 287 a char(10)); 288create table t2 ( 289 p int not null auto_increment primary key, 290 i int, a char(10), key k1(i), key k2(a)); 291 292select @@key_cache_segments; 293--replace_column 7 # 294select * from information_schema.key_caches where segment_number is null; 295 296insert into t1 values (1, 'qqqq'), (2, 'yyyy'); 297insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 298 (3, 1, 'yyyy'), (4, 3, 'zzzz'); 299select * from t1; 300select * from t2; 301update t1 set p=3 where p=1; 302update t2 set i=2 where i=1; 303 304select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused'; 305select variable_value into @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; 306--replace_column 7 # 307select * from information_schema.key_caches where segment_number is null; 308 309delete from t2 where a='zzzz'; 310--replace_column 7 # 311select * from information_schema.key_caches where segment_number is null; 312 313delete from t1; 314delete from t2; 315--replace_column 7 # 316select * from information_schema.key_caches where segment_number is null; 317 318# For the key cache with 2 segments execute the same sequence of 319# statements as for the simple cache above. 320# The statistical information on the number of i/o requests and 321# the number of is expected to be the same. 322 323set global key_cache_segments=2; 324select @@key_cache_segments; 325--replace_column 7 # 326select * from information_schema.key_caches where segment_number is null; 327 328insert into t1 values (1, 'qqqq'), (2, 'yyyy'); 329insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 330 (3, 1, 'yyyy'), (4, 3, 'zzzz'); 331select * from t1; 332select * from t2; 333update t1 set p=3 where p=1; 334update t2 set i=2 where i=1; 335 336 337select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused'; 338select variable_value < @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; 339--replace_column 7 # 340select * from information_schema.key_caches where segment_number is null; 341 342delete from t1; 343delete from t2; 344--replace_column 7 # 345select * from information_schema.key_caches where segment_number is null; 346 347# Check that we can work with one segment with the same results 348 349set global key_cache_segments=1; 350select @@key_cache_segments; 351--replace_column 7 # 352select * from information_schema.key_caches where segment_number is null; 353 354insert into t1 values (1, 'qqqq'), (2, 'yyyy'); 355insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 356 (3, 1, 'yyyy'), (4, 3, 'zzzz'); 357select * from t1; 358select * from t2; 359update t1 set p=3 where p=1; 360update t2 set i=2 where i=1; 361 362select * from information_schema.session_status where variable_name like 'key_%' and variable_name != 'Key_blocks_unused'; 363select variable_value = @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; 364--replace_column 7 # 365select * from information_schema.key_caches where segment_number is null; 366 367delete from t1; 368delete from t2; 369--replace_column 7 # 370select * from information_schema.key_caches where segment_number is null; 371 372flush tables; flush status; 373--replace_column 7 # 374select * from information_schema.key_caches where segment_number is null; 375 376# Switch back to 2 segments 377 378set global key_buffer_size=32*1024; 379set global key_cache_file_hash_size=128; 380select @@key_buffer_size; 381set global key_cache_segments=2; 382select @@key_cache_segments; 383--replace_column 7 # 384select * from information_schema.key_caches where segment_number is null; 385 386insert into t1 values (1, 'qqqq'), (2, 'yyyy'); 387insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 388 (3, 1, 'yyyy'), (4, 3, 'zzzz'); 389select * from t1; 390select * from t2; 391update t1 set p=3 where p=1; 392update t2 set i=2 where i=1; 393 394--replace_column 7 # 395select * from information_schema.key_caches where segment_number is null; 396 397# Add more rows to tables t1 and t2 398 399insert into t1(a) select a from t1; 400insert into t1(a) select a from t1; 401insert into t1(a) select a from t1; 402insert into t1(a) select a from t1; 403insert into t1(a) select a from t1; 404insert into t1(a) select a from t1; 405insert into t1(a) select a from t1; 406insert into t1(a) select a from t1; 407 408insert into t2(i,a) select i,a from t2; 409insert into t2(i,a) select i,a from t2; 410insert into t2(i,a) select i,a from t2; 411insert into t2(i,a) select i,a from t2; 412insert into t2(i,a) select i,a from t2; 413insert into t2(i,a) select i,a from t2; 414insert into t2(i,a) select i,a from t2; 415insert into t2(i,a) select i,a from t2; 416 417--replace_column 6 # 7 # 10 # 418select * from information_schema.key_caches where segment_number is null; 419 420select * from t1 where p between 1010 and 1020 ; 421select * from t2 where p between 1010 and 1020 ; 422--replace_column 6 # 7 # 10 # 423select * from information_schema.key_caches where segment_number is null; 424 425flush tables; flush status; 426update t1 set a='zzzz' where a='qqqq'; 427update t2 set i=1 where i=2; 428--replace_column 6 # 7 # 429select * from information_schema.key_caches where segment_number is null; 430 431# Now test how we can work with 7 segments 432 433set global keycache1.key_buffer_size=256*1024; 434select @@keycache1.key_buffer_size; 435set global keycache1.key_cache_segments=7; 436select @@keycache1.key_cache_segments; 437 438--replace_column 6 # 7 # 439select * from information_schema.key_caches where segment_number is null; 440--replace_column 7 # 441select * from information_schema.key_caches where key_cache_name like "key%" 442 and segment_number is null; 443 444cache index t1 key (`primary`) in keycache1; 445 446explain select p from t1 where p between 1010 and 1020; 447select p from t1 where p between 1010 and 1020; 448explain select i from t2 where p between 1010 and 1020; 449select i from t2 where p between 1010 and 1020; 450explain select count(*) from t1, t2 where t1.p = t2.i; 451select count(*) from t1, t2 where t1.p = t2.i; 452 453--replace_column 6 # 7 # 454select * from information_schema.key_caches where segment_number is null; 455--replace_column 7 # 456select * from information_schema.key_caches where key_cache_name like "key%" 457 and segment_number is null; 458 459cache index t2 in keycache1; 460update t2 set p=p+3000, i=2 where a='qqqq'; 461--replace_column 7 # 462select * from information_schema.key_caches where key_cache_name like "key%" 463 and segment_number is null; 464 465set global keycache2.key_buffer_size=1024*1024; 466cache index t2 in keycache2; 467insert into t2 values (2000, 3, 'yyyy'); 468--replace_column 7 # 469select * from information_schema.key_caches where key_cache_name like "keycache2" 470 and segment_number is null; 471--replace_column 7 # 472select * from information_schema.key_caches where key_cache_name like "key%" 473 and segment_number is null; 474 475cache index t2 in keycache1; 476update t2 set p=p+5000 where a='zzzz'; 477select * from t2 where p between 1010 and 1020; 478explain select p from t2 where p between 1010 and 1020; 479select p from t2 where p between 1010 and 1020; 480explain select i from t2 where a='yyyy' and i=3; 481select i from t2 where a='yyyy' and i=3; 482explain select a from t2 where a='yyyy' and i=3; 483select a from t2 where a='yyyy' and i=3 ; 484--replace_column 6 # 7 # 485select * from information_schema.key_caches where segment_number is null; 486 487set global keycache1.key_cache_block_size=2*1024; 488insert into t2 values (7000, 3, 'yyyy'); 489--replace_column 6 # 7 # 490select * from information_schema.key_caches where segment_number is null; 491 492set global keycache1.key_cache_block_size=8*1024; 493--replace_column 6 # 7 # 494select * from information_schema.key_caches where segment_number is null; 495insert into t2 values (8000, 3, 'yyyy'); 496--replace_column 6 # 7 # 497select * from information_schema.key_caches where segment_number is null; 498 499set global keycache1.key_buffer_size=64*1024; 500--replace_column 6 # 7 # 501select * from information_schema.key_caches where segment_number is null; 502 503set global keycache1.key_cache_block_size=2*1024; 504--replace_column 6 # 7 # 505select * from information_schema.key_caches where segment_number is null; 506 507set global keycache1.key_cache_block_size=8*1024; 508--replace_column 6 # 7 # 509select * from information_schema.key_caches where segment_number is null; 510 511set global keycache1.key_buffer_size=0; 512--replace_column 6 # 7 # 513select * from information_schema.key_caches where segment_number is null; 514 515set global keycache1.key_cache_block_size=8*1024; 516--replace_column 6 # 7 # 517select * from information_schema.key_caches where segment_number is null; 518 519set global keycache1.key_buffer_size=0; 520--replace_column 6 # 7 # 521select * from information_schema.key_caches where segment_number is null; 522 523set global keycache1.key_buffer_size=128*1024; 524--replace_column 6 # 7 # 525select * from information_schema.key_caches where segment_number is null; 526 527set global keycache1.key_cache_block_size=1024; 528--replace_column 6 # 7 # 529select * from information_schema.key_caches where segment_number is null; 530 531drop table t1,t2; 532 533set global keycache1.key_buffer_size=0; 534set global keycache2.key_buffer_size=0; 535 536set global key_buffer_size=@save_key_buffer_size; 537set global key_cache_segments=@save_key_cache_segments; 538set global key_cache_file_hash_size=@save_key_cache_file_hash_size; 539 540# End of 5.2 tests 541