1drop table if exists t1, t2, t3; 2SET @save_key_buffer=@@key_buffer_size; 3SELECT @@key_buffer_size, @@small.key_buffer_size; 4@@key_buffer_size @@small.key_buffer_size 52097152 131072 6SET @@global.key_buffer_size=16*1024*1024; 7SET @@global.default.key_buffer_size=16*1024*1024; 8SET @@global.default.key_buffer_size=16*1024*1024; 9SET @@global.small.key_buffer_size=1*1024*1024; 10SET @@global.medium.key_buffer_size=4*1024*1024; 11SET @@global.medium.key_buffer_size=0; 12SET @@global.medium.key_buffer_size=0; 13SHOW VARIABLES like "key_buffer_size"; 14Variable_name Value 15key_buffer_size 16777216 16SELECT @@key_buffer_size; 17@@key_buffer_size 1816777216 19SELECT @@global.key_buffer_size; 20@@global.key_buffer_size 2116777216 22SELECT @@global.default.key_buffer_size; 23@@global.default.key_buffer_size 2416777216 25SELECT @@global.default.`key_buffer_size`; 26@@global.default.`key_buffer_size` 2716777216 28SELECT @@global.`default`.`key_buffer_size`; 29@@global.`default`.`key_buffer_size` 3016777216 31SELECT @@`default`.key_buffer_size; 32@@`default`.key_buffer_size 3316777216 34SELECT @@small.key_buffer_size; 35@@small.key_buffer_size 361048576 37SELECT @@medium.key_buffer_size; 38@@medium.key_buffer_size 390 40SET @@global.key_buffer_size=@save_key_buffer; 41SELECT @@default.key_buffer_size; 42ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'default.key_buffer_size' at line 1 43SELECT @@skr.default_storage_engine="test"; 44ERROR HY000: Variable 'default_storage_engine' is not a variable component (can't be used as XXXX.variable_name) 45select @@keycache1.key_cache_block_size; 46@@keycache1.key_cache_block_size 470 48select @@keycache1.key_buffer_size; 49@@keycache1.key_buffer_size 500 51set global keycache1.key_cache_block_size=2048; 52select @@keycache1.key_buffer_size; 53@@keycache1.key_buffer_size 540 55select @@keycache1.key_cache_block_size; 56@@keycache1.key_cache_block_size 572048 58set global keycache1.key_buffer_size=1*1024*1024; 59select @@keycache1.key_buffer_size; 60@@keycache1.key_buffer_size 611048576 62select @@keycache1.key_cache_block_size; 63@@keycache1.key_cache_block_size 642048 65set global keycache2.key_buffer_size=4*1024*1024; 66select @@keycache2.key_buffer_size; 67@@keycache2.key_buffer_size 684194304 69select @@keycache2.key_cache_block_size; 70@@keycache2.key_cache_block_size 711024 72set global keycache1.key_buffer_size=0; 73select @@keycache1.key_buffer_size; 74@@keycache1.key_buffer_size 750 76select @@keycache1.key_cache_block_size; 77@@keycache1.key_cache_block_size 782048 79select @@key_buffer_size; 80@@key_buffer_size 812097152 82select @@key_cache_block_size; 83@@key_cache_block_size 841024 85set global keycache1.key_buffer_size=1024*1024; 86create table t1 (p int primary key, a char(10)) delay_key_write=1; 87create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a)); 88show status like 'key_blocks_used'; 89Variable_name Value 90Key_blocks_used 0 91show status like 'key_blocks_unused'; 92Variable_name Value 93Key_blocks_unused KEY_BLOCKS_UNUSED 94insert into t1 values (1, 'qqqq'), (11, 'yyyy'); 95insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'), 96(3, 1, 'yyyy'), (4, 3, 'zzzz'); 97select * from t1; 98p a 991 qqqq 10011 yyyy 101select * from t2; 102p i a 1031 1 qqqq 1042 1 pppp 1053 1 yyyy 1064 3 zzzz 107update t1 set p=2 where p=1; 108update t2 set i=2 where i=1; 109show status like 'key_blocks_used'; 110Variable_name Value 111Key_blocks_used 4 112show status like 'key_blocks_unused'; 113Variable_name Value 114Key_blocks_unused KEY_BLOCKS_UNUSED 115cache index t1 key (`primary`) in keycache1; 116Table Op Msg_type Msg_text 117test.t1 assign_to_keycache status OK 118explain select p from t1; 119id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1201 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 2 100.00 Using index 121Warnings: 122Note 1003 /* select#1 */ select `test`.`t1`.`p` AS `p` from `test`.`t1` 123select p from t1; 124p 1252 12611 127explain select i from t2; 128id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1291 SIMPLE t2 NULL index NULL k1 5 NULL 4 100.00 Using index 130Warnings: 131Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` 132select i from t2; 133i 1342 1352 1362 1373 138explain select count(*) from t1, t2 where t1.p = t2.i; 139id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1401 SIMPLE t1 NULL index PRIMARY PRIMARY 4 NULL 2 100.00 Using index 1411 SIMPLE t2 NULL ref k1 k1 5 test.t1.p 2 100.00 Using index 142Warnings: 143Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`i` = `test`.`t1`.`p`) 144select count(*) from t1, t2 where t1.p = t2.i; 145count(*) 1463 147cache index t2 in keycache1; 148Table Op Msg_type Msg_text 149test.t2 assign_to_keycache status OK 150update t2 set p=p+1000, i=2 where a='qqqq'; 151cache index t2 in keycache2; 152Table Op Msg_type Msg_text 153test.t2 assign_to_keycache status OK 154insert into t2 values (2000, 3, 'yyyy'); 155cache index t2 in keycache1; 156Table Op Msg_type Msg_text 157test.t2 assign_to_keycache status OK 158update t2 set p=3000 where a='zzzz'; 159select * from t2; 160p i a 1611001 2 qqqq 1622 2 pppp 1633 2 yyyy 1643000 3 zzzz 1652000 3 yyyy 166explain select p from t2; 167id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1681 SIMPLE t2 NULL index NULL PRIMARY 4 NULL 5 100.00 Using index 169Warnings: 170Note 1003 /* select#1 */ select `test`.`t2`.`p` AS `p` from `test`.`t2` 171select p from t2; 172p 1732 1743 1751001 1762000 1773000 178explain select i from t2; 179id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1801 SIMPLE t2 NULL index NULL k1 5 NULL 5 100.00 Using index 181Warnings: 182Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` 183select i from t2; 184i 1852 1862 1872 1883 1893 190explain select a from t2; 191id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1921 SIMPLE t2 NULL index NULL k2 11 NULL 5 100.00 Using index 193Warnings: 194Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` 195select a from t2; 196a 197pppp 198qqqq 199yyyy 200yyyy 201zzzz 202cache index t1 in unknown_key_cache; 203ERROR HY000: Unknown key cache 'unknown_key_cache' 204cache index t1 key (unknown_key) in keycache1; 205Table Op Msg_type Msg_text 206test.t1 assign_to_keycache Error Key 'unknown_key' doesn't exist in table 't1' 207test.t1 assign_to_keycache status Operation failed 208select @@keycache2.key_buffer_size; 209@@keycache2.key_buffer_size 2104194304 211select @@keycache2.key_cache_block_size; 212@@keycache2.key_cache_block_size 2131024 214set global keycache2.key_buffer_size=0; 215select @@keycache2.key_buffer_size; 216@@keycache2.key_buffer_size 2170 218select @@keycache2.key_cache_block_size; 219@@keycache2.key_cache_block_size 2201024 221set global keycache2.key_buffer_size=1024*1024; 222select @@keycache2.key_buffer_size; 223@@keycache2.key_buffer_size 2241048576 225update t2 set p=4000 where a='zzzz'; 226update t1 set p=p+1; 227set global keycache1.key_buffer_size=0; 228select * from t2; 229p i a 2301001 2 qqqq 2312 2 pppp 2323 2 yyyy 2334000 3 zzzz 2342000 3 yyyy 235select p from t2; 236p 2372 2383 2391001 2402000 2414000 242explain select i from t2; 243id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2441 SIMPLE t2 NULL index NULL k1 5 NULL 5 100.00 Using index 245Warnings: 246Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` 247select i from t2; 248i 2492 2502 2512 2523 2533 254explain select a from t2; 255id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2561 SIMPLE t2 NULL index NULL k2 11 NULL 5 100.00 Using index 257Warnings: 258Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` 259select a from t2; 260a 261pppp 262qqqq 263yyyy 264yyyy 265zzzz 266select * from t1; 267p a 2683 qqqq 26912 yyyy 270select p from t1; 271p 2723 27312 274create table t3 (like t1); 275cache index t3 in small; 276Table Op Msg_type Msg_text 277test.t3 assign_to_keycache status OK 278insert into t3 select * from t1; 279cache index t3 in keycache2; 280Table Op Msg_type Msg_text 281test.t3 assign_to_keycache status OK 282cache index t1,t2 in default; 283Table Op Msg_type Msg_text 284test.t1 assign_to_keycache status OK 285test.t2 assign_to_keycache status OK 286drop table t1,t2,t3; 287show status like 'key_blocks_used'; 288Variable_name Value 289Key_blocks_used 4 290show status like 'key_blocks_unused'; 291Variable_name Value 292Key_blocks_unused KEY_BLOCKS_UNUSED 293create table t1 (a int primary key); 294cache index t1 in keycache2; 295Table Op Msg_type Msg_text 296test.t1 assign_to_keycache status OK 297insert t1 values (1),(2),(3),(4),(5),(6),(7),(8); 298set global keycache2.key_buffer_size=0; 299select * from t1; 300a 3011 3022 3033 3044 3055 3066 3077 3088 309drop table t1; 310set global keycache3.key_buffer_size=100; 311Warnings: 312Warning 1292 Truncated incorrect key_buffer_size value: '100' 313set global keycache3.key_buffer_size=0; 314create table t1 (mytext text, FULLTEXT (mytext)); 315insert t1 values ('aaabbb'); 316check table t1; 317Table Op Msg_type Msg_text 318test.t1 check status OK 319set @my_key_cache_block_size= @@global.key_cache_block_size; 320set GLOBAL key_cache_block_size=2048; 321check table t1; 322Table Op Msg_type Msg_text 323test.t1 check status OK 324drop table t1; 325set global key_cache_block_size= @my_key_cache_block_size; 326CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY); 327SET @my_key_cache_block_size= @@global.key_cache_block_size; 328SET GLOBAL key_cache_block_size=1536; 329INSERT INTO t1 VALUES (1); 330SELECT @@key_cache_block_size; 331@@key_cache_block_size 3321536 333CHECK TABLE t1; 334Table Op Msg_type Msg_text 335test.t1 check status OK 336DROP TABLE t1; 337CREATE TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int); 338CREATE TABLE t2(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int); 339SET GLOBAL key_cache_block_size=1536; 340INSERT INTO t1 VALUES (1,0); 341INSERT INTO t2(b) SELECT b FROM t1; 342INSERT INTO t1(b) SELECT b FROM t2; 343INSERT INTO t2(b) SELECT b FROM t1; 344INSERT INTO t1(b) SELECT b FROM t2; 345INSERT INTO t2(b) SELECT b FROM t1; 346INSERT INTO t1(b) SELECT b FROM t2; 347INSERT INTO t2(b) SELECT b FROM t1; 348INSERT INTO t1(b) SELECT b FROM t2; 349INSERT INTO t2(b) SELECT b FROM t1; 350INSERT INTO t1(b) SELECT b FROM t2; 351INSERT INTO t2(b) SELECT b FROM t1; 352INSERT INTO t1(b) SELECT b FROM t2; 353INSERT INTO t2(b) SELECT b FROM t1; 354INSERT INTO t1(b) SELECT b FROM t2; 355INSERT INTO t2(b) SELECT b FROM t1; 356INSERT INTO t1(b) SELECT b FROM t2; 357INSERT INTO t2(b) SELECT b FROM t1; 358INSERT INTO t1(b) SELECT b FROM t2; 359SELECT COUNT(*) FROM t1; 360COUNT(*) 3614181 362SELECT @@key_cache_block_size; 363@@key_cache_block_size 3641536 365CHECK TABLE t1; 366Table Op Msg_type Msg_text 367test.t1 check status OK 368DROP TABLE t1,t2; 369set global key_cache_block_size= @my_key_cache_block_size; 370set @@global.key_buffer_size=0; 371ERROR HY000: Cannot drop default keycache 372select @@global.key_buffer_size; 373@@global.key_buffer_size 3742097152 375SET @bug28478_key_cache_block_size= @@global.key_cache_block_size; 376SET GLOBAL key_cache_block_size= 1536; 377CREATE TABLE t1 ( 378id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 379c1 CHAR(150), 380c2 CHAR(150), 381c3 CHAR(150), 382KEY(c1, c2, c3) 383) ENGINE= MyISAM; 384INSERT INTO t1 (c1, c2, c3) VALUES 385('a', 'b', 'c'), ('b', 'c', 'd'), ('c', 'd', 'e'), ('d', 'e', 'f'), 386('e', 'f', 'g'), ('f', 'g', 'h'), ('g', 'h', 'i'), ('h', 'i', 'j'), 387('i', 'j', 'k'), ('j', 'k', 'l'), ('k', 'l', 'm'), ('l', 'm', 'n'), 388('m', 'n', 'o'), ('n', 'o', 'p'), ('o', 'p', 'q'), ('p', 'q', 'r'), 389('q', 'r', 's'), ('r', 's', 't'), ('s', 't', 'u'), ('t', 'u', 'v'), 390('u', 'v', 'w'), ('v', 'w', 'x'), ('w', 'x', 'y'), ('x', 'y', 'z'); 391INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; 392INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; 393INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1; 394CHECK TABLE t1; 395Table Op Msg_type Msg_text 396test.t1 check status OK 397SHOW VARIABLES LIKE 'key_cache_block_size'; 398Variable_name Value 399key_cache_block_size 1536 400SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size; 401DROP TABLE t1; 402# 403# Bug#12361113: crash when load index into cache 404# 405# Note that this creates an empty disabled key cache! 406SET GLOBAL key_cache_none.key_cache_block_size = 1024; 407CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) ) ENGINE = MYISAM; 408INSERT INTO t1 VALUES (1, 1); 409CACHE INDEX t1 in key_cache_none; 410ERROR HY000: Unknown key cache 'key_cache_none' 411# The bug crashed the server at LOAD INDEX below. Now it will succeed 412# since the default cache is used due to CACHE INDEX failed for 413# key_cache_none. 414LOAD INDEX INTO CACHE t1; 415Table Op Msg_type Msg_text 416test.t1 preload_keys status OK 417DROP TABLE t1; 418