1
drop table if exists t1, t2, t3;
2
SET @save_myisam_key_cache_size=@@myisam_key_cache_size;
3
SET @save_myisam_key_cache_block_size=@@myisam_key_cache_block_size;
4
SET @save_myisam_key_cache_division_limit=@@myisam_key_cache_division_limit;
5
SET @save_myisam_key_cache_age_threshold=@@myisam_key_cache_age_threshold;
6
SELECT @@myisam_key_cache_size;
7
@@myisam_key_cache_size
9
SELECT @@myisam_key_cache_block_size;
10
@@myisam_key_cache_block_size
12
SELECT @@myisam_key_cache_division_limit;
13
@@myisam_key_cache_division_limit
15
SELECT @@myisam_key_cache_age_threshold;
16
@@myisam_key_cache_age_threshold
18
SET @@global.myisam_key_cache_size=16*1024*1024;
19
SET @@global.myisam_key_cache_size=0;
21
Error 1292 Truncated incorrect key_cache_size value: '0'
22
SET @@global.myisam_key_cache_size=0;
24
Error 1292 Truncated incorrect key_cache_size value: '0'
25
SET @@global.myisam_key_cache_size=16*1024*1024;
26
SHOW VARIABLES like "myisam_key_cache_size";
28
myisam_key_cache_size 16777216
29
SELECT @@myisam_key_cache_size;
30
@@myisam_key_cache_size
32
SELECT @@global.myisam_key_cache_size;
33
@@global.myisam_key_cache_size
35
SELECT @@global.myisam_key_cache_size;
36
@@global.myisam_key_cache_size
38
SELECT @@global.`myisam_key_cache_size`;
39
@@global.`myisam_key_cache_size`
41
SELECT @@global.`myisam_key_cache_size`;
42
@@global.`myisam_key_cache_size`
44
SELECT @@myisam_key_cache_size;
45
@@myisam_key_cache_size
47
SET @@global.myisam_key_cache_size=@save_myisam_key_cache_size;
48
SELECT @@myisam_key_cache_size;
49
@@myisam_key_cache_size
51
SELECT @@skr.storage_engine="test";
52
ERROR HY000: Variable 'storage_engine' is not a variable component (can't be used as XXXX.variable_name)
53
select @@keycache1.myisam_key_cache_block_size;
54
ERROR HY000: Variable 'myisam_key_cache_block_size' is not a variable component (can't be used as XXXX.variable_name)
55
show status like 'key_blocks_used';
58
set global myisam_key_cache_size=100;
60
Error 1292 Truncated incorrect key_cache_size value: '100'
61
CREATE temporary TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=MYISAM;
62
SET GLOBAL myisam_key_cache_block_size=1536;
63
INSERT INTO t1 VALUES (1);
64
SELECT @@myisam_key_cache_block_size;
65
@@myisam_key_cache_block_size
68
Table Op Msg_type Msg_text
69
test.t1 check status OK
71
CREATE TEMPORARY TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int) ENGINE=MYISAM;
72
CREATE TEMPORARY TABLE t2(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int) ENGINE=MYISAM;
73
SET GLOBAL myisam_key_cache_block_size=1536;
74
INSERT INTO t1 VALUES (1,0);
75
INSERT INTO t2(b) SELECT b FROM t1;
76
INSERT INTO t1(b) SELECT b FROM t2;
77
INSERT INTO t2(b) SELECT b FROM t1;
78
INSERT INTO t1(b) SELECT b FROM t2;
79
INSERT INTO t2(b) SELECT b FROM t1;
80
INSERT INTO t1(b) SELECT b FROM t2;
81
INSERT INTO t2(b) SELECT b FROM t1;
82
INSERT INTO t1(b) SELECT b FROM t2;
83
INSERT INTO t2(b) SELECT b FROM t1;
84
INSERT INTO t1(b) SELECT b FROM t2;
85
INSERT INTO t2(b) SELECT b FROM t1;
86
INSERT INTO t1(b) SELECT b FROM t2;
87
INSERT INTO t2(b) SELECT b FROM t1;
88
INSERT INTO t1(b) SELECT b FROM t2;
89
INSERT INTO t2(b) SELECT b FROM t1;
90
INSERT INTO t1(b) SELECT b FROM t2;
91
INSERT INTO t2(b) SELECT b FROM t1;
92
INSERT INTO t1(b) SELECT b FROM t2;
93
SELECT COUNT(*) FROM t1;
96
SELECT @@myisam_key_cache_block_size;
97
@@myisam_key_cache_block_size
100
Table Op Msg_type Msg_text
101
test.t1 check status OK
103
set global myisam_key_cache_block_size= @save_myisam_key_cache_block_size;
104
SET GLOBAL myisam_key_cache_block_size= 1536;
105
CREATE TEMPORARY TABLE t1 (
106
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
112
INSERT INTO t1 (c1, c2, c3) VALUES
113
('a', 'b', 'c'), ('b', 'c', 'd'), ('c', 'd', 'e'), ('d', 'e', 'f'),
114
('e', 'f', 'g'), ('f', 'g', 'h'), ('g', 'h', 'i'), ('h', 'i', 'j'),
115
('i', 'j', 'k'), ('j', 'k', 'l'), ('k', 'l', 'm'), ('l', 'm', 'n'),
116
('m', 'n', 'o'), ('n', 'o', 'p'), ('o', 'p', 'q'), ('p', 'q', 'r'),
117
('q', 'r', 's'), ('r', 's', 't'), ('s', 't', 'u'), ('t', 'u', 'v'),
118
('u', 'v', 'w'), ('v', 'w', 'x'), ('w', 'x', 'y'), ('x', 'y', 'z');
119
CREATE TABLE t2 AS SELECT * FROM t1;
120
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
121
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
122
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
124
Table Op Msg_type Msg_text
125
test.t1 check status OK
126
SHOW VARIABLES LIKE 'myisam_key_cache_block_size';
128
myisam_key_cache_block_size 1536
129
SET GLOBAL myisam_key_cache_block_size= @save_myisam_key_cache_block_size;
131
SET GLOBAL myisam_key_cache_division_limit= 0;
133
Error 1292 Truncated incorrect key_cache_division_limit value: '0'
134
SELECT @@myisam_key_cache_division_limit;
135
@@myisam_key_cache_division_limit
137
SET GLOBAL myisam_key_cache_division_limit= 101;
139
Error 1292 Truncated incorrect key_cache_division_limit value: '101'
140
SELECT @@myisam_key_cache_division_limit;
141
@@myisam_key_cache_division_limit
143
SET GLOBAL myisam_key_cache_age_threshold= 98;
145
Error 1292 Truncated incorrect key_cache_age_threshold value: '98'
146
SELECT @@myisam_key_cache_age_threshold;
147
@@myisam_key_cache_age_threshold
149
SET GLOBAL myisam_key_cache_age_threshold= 5000000000;
151
Error 1292 Truncated incorrect key_cache_age_threshold value: '5000000000'
152
SELECT @@myisam_key_cache_age_threshold;
153
@@myisam_key_cache_age_threshold