2
# Test of multiple key caches
2
# Test of the MyISAM key cache
5
5
drop table if exists t1, t2, t3;
8
SET @save_key_buffer=@@key_buffer_size;
8
SET @save_myisam_key_cache_size=@@myisam_key_cache_size;
9
SET @save_myisam_key_cache_block_size=@@myisam_key_cache_block_size;
10
SET @save_myisam_key_cache_division_limit=@@myisam_key_cache_division_limit;
11
SET @save_myisam_key_cache_age_threshold=@@myisam_key_cache_age_threshold;
10
SELECT @@key_buffer_size;
13
# Show default variables for MyISAM key cache
14
SELECT @@myisam_key_cache_size;
15
SELECT @@myisam_key_cache_block_size;
16
SELECT @@myisam_key_cache_division_limit;
17
SELECT @@myisam_key_cache_age_threshold;
12
19
# Change default key cache size
13
SET @@global.key_buffer_size=16*1024*1024;
20
SET @@global.myisam_key_cache_size=16*1024*1024;
16
SET @@global.key_buffer_size=0;
22
# Drop Key Cache (warning generated)
23
SET @@global.myisam_key_cache_size=0;
18
SET @@global.key_buffer_size=0;
25
SET @@global.myisam_key_cache_size=0;
27
# Change default key cache size
28
SET @@global.myisam_key_cache_size=16*1024*1024;
20
30
# Print key buffer with different syntaxes
21
SHOW VARIABLES like "key_buffer_size";
22
SELECT @@key_buffer_size;
23
SELECT @@global.key_buffer_size;
24
SELECT @@global.key_buffer_size;
25
SELECT @@global.`key_buffer_size`;
26
SELECT @@global.`key_buffer_size`;
27
SELECT @@key_buffer_size;
29
SELECT @@key_buffer_size;
30
SELECT @@key_buffer_size;
32
SET @@global.key_buffer_size=@save_key_buffer;
31
SHOW VARIABLES like "myisam_key_cache_size";
32
SELECT @@myisam_key_cache_size;
33
SELECT @@global.myisam_key_cache_size;
34
SELECT @@global.myisam_key_cache_size;
35
SELECT @@global.`myisam_key_cache_size`;
36
SELECT @@global.`myisam_key_cache_size`;
37
SELECT @@myisam_key_cache_size;
39
SET @@global.myisam_key_cache_size=@save_myisam_key_cache_size;
38
SELECT @@key_buffer_size;
45
SELECT @@myisam_key_cache_size;
40
47
SELECT @@skr.storage_engine="test";
43
select @@keycache1.key_cache_block_size;
50
select @@keycache1.myisam_key_cache_block_size;
45
52
show status like 'key_blocks_used';
48
# We don't reset keycache2 as we want to ensure that mysqld will reset it
49
set global key_buffer_size=0;
51
54
# Test to set up a too small size for a key cache (bug #2064)
52
set global key_buffer_size=100;
53
set global key_buffer_size=0;
55
# Restore the changed variable value
56
set global key_cache_block_size= @my_key_cache_block_size;
55
set global myisam_key_cache_size=100;
59
# Bug #19079: corrupted index when key_cache_block_size is not multiple of
58
# Bug #19079: corrupted index when myisam_key_cache_block_size is not multiple of
60
59
# myisam_block_size
62
61
CREATE temporary TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=MYISAM;
63
SET @my_key_cache_block_size= @@global.key_cache_block_size;
64
SET GLOBAL key_cache_block_size=1536;
62
SET GLOBAL myisam_key_cache_block_size=1536;
65
63
INSERT INTO t1 VALUES (1);
66
SELECT @@key_cache_block_size;
64
SELECT @@myisam_key_cache_block_size;
70
68
CREATE TEMPORARY TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int) ENGINE=MYISAM;
71
69
CREATE TEMPORARY TABLE t2(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int) ENGINE=MYISAM;
72
SET GLOBAL key_cache_block_size=1536;
70
SET GLOBAL myisam_key_cache_block_size=1536;
73
71
INSERT INTO t1 VALUES (1,0);
74
72
INSERT INTO t2(b) SELECT b FROM t1;
75
73
INSERT INTO t1(b) SELECT b FROM t2;
91
88
INSERT INTO t2(b) SELECT b FROM t1;
92
89
INSERT INTO t1(b) SELECT b FROM t2;
93
90
SELECT COUNT(*) FROM t1;
94
SELECT @@key_cache_block_size;
91
SELECT @@myisam_key_cache_block_size;
97
94
# Restore changed variables
98
set global key_cache_block_size= @my_key_cache_block_size;
101
# Bug#10473 - Can't set 'key_buffer_size' system variable to ZERO
102
# (One cannot drop the default key cache.)
104
set @@global.key_buffer_size=0;
105
select @@global.key_buffer_size;
108
# Bug#28478 - Improper key_cache_block_size corrupts MyISAM tables
110
SET @bug28478_key_cache_block_size= @@global.key_cache_block_size;
111
SET GLOBAL key_cache_block_size= 1536;
95
set global myisam_key_cache_block_size= @save_myisam_key_cache_block_size;
98
# Bug#28478 - Improper myisam_key_cache_block_size corrupts MyISAM tables
100
SET GLOBAL myisam_key_cache_block_size= 1536;
112
101
CREATE TEMPORARY TABLE t1 (
113
102
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
128
117
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
129
118
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
131
SHOW VARIABLES LIKE 'key_cache_block_size';
132
SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size;
120
SHOW VARIABLES LIKE 'myisam_key_cache_block_size';
121
SET GLOBAL myisam_key_cache_block_size= @save_myisam_key_cache_block_size;
133
122
DROP TABLE t1,t2;
124
# Check division limit and age threshold
126
# Division Limit: Minimum 1, maximum 100
127
SET GLOBAL myisam_key_cache_division_limit= 0;
129
SELECT @@myisam_key_cache_division_limit;
131
SET GLOBAL myisam_key_cache_division_limit= 101;
133
SELECT @@myisam_key_cache_division_limit;
135
# Age Threshold: Minimum 100, maximum UINT32_MAX
136
SET GLOBAL myisam_key_cache_age_threshold= 98;
138
SELECT @@myisam_key_cache_age_threshold;
140
SET GLOBAL myisam_key_cache_age_threshold= 5000000000;
142
SELECT @@myisam_key_cache_age_threshold;