1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
drop table if exists t1, t2, t3;
SET @save_key_buffer=@@key_buffer_size;
SELECT @@key_buffer_size;
@@key_buffer_size
1048576
SET @@global.key_buffer_size=16*1024*1024;
SET @@global.key_buffer_size=0;
Warnings:
Warning 1438 Cannot drop default keycache
SET @@global.key_buffer_size=0;
Warnings:
Warning 1438 Cannot drop default keycache
SHOW VARIABLES like "key_buffer_size";
Variable_name Value
key_buffer_size 16777216
SELECT @@key_buffer_size;
@@key_buffer_size
16777216
SELECT @@global.key_buffer_size;
@@global.key_buffer_size
16777216
SELECT @@global.key_buffer_size;
@@global.key_buffer_size
16777216
SELECT @@global.`key_buffer_size`;
@@global.`key_buffer_size`
16777216
SELECT @@global.`key_buffer_size`;
@@global.`key_buffer_size`
16777216
SELECT @@key_buffer_size;
@@key_buffer_size
16777216
SELECT @@key_buffer_size;
@@key_buffer_size
16777216
SELECT @@key_buffer_size;
@@key_buffer_size
16777216
SET @@global.key_buffer_size=@save_key_buffer;
SELECT @@key_buffer_size;
@@key_buffer_size
1048576
SELECT @@skr.storage_engine="test";
ERROR HY000: Variable 'storage_engine' is not a variable component (can't be used as XXXX.variable_name)
select @@keycache1.key_cache_block_size;
ERROR HY000: Variable 'key_cache_block_size' is not a variable component (can't be used as XXXX.variable_name)
show status like 'key_blocks_used';
Variable_name Value
Key_blocks_used 0
set global key_buffer_size=0;
Warnings:
Warning 1438 Cannot drop default keycache
set global key_buffer_size=100;
Warnings:
Error 1292 Truncated incorrect key_buffer_size value: '100'
set global key_buffer_size=0;
Warnings:
Warning 1438 Cannot drop default keycache
set global key_cache_block_size= @my_key_cache_block_size;
ERROR 42000: Incorrect argument type to variable 'key_cache_block_size'
CREATE temporary TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=MYISAM;
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;
@@key_cache_block_size
1536
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
DROP TABLE t1;
CREATE TEMPORARY TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int) ENGINE=MYISAM;
CREATE TEMPORARY TABLE t2(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int) ENGINE=MYISAM;
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;
COUNT(*)
4181
SELECT @@key_cache_block_size;
@@key_cache_block_size
1536
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
DROP TABLE t1,t2;
set global key_cache_block_size= @my_key_cache_block_size;
set @@global.key_buffer_size=0;
Warnings:
Warning 1438 Cannot drop default keycache
select @@global.key_buffer_size;
@@global.key_buffer_size
8
SET @bug28478_key_cache_block_size= @@global.key_cache_block_size;
SET GLOBAL key_cache_block_size= 1536;
CREATE TEMPORARY TABLE t1 (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c1 CHAR(50),
c2 CHAR(50),
c3 CHAR(50),
KEY(c1, c2, c3)
) ENGINE= MyISAM;
INSERT INTO t1 (c1, c2, c3) VALUES
('a', 'b', 'c'), ('b', 'c', 'd'), ('c', 'd', 'e'), ('d', 'e', 'f'),
('e', 'f', 'g'), ('f', 'g', 'h'), ('g', 'h', 'i'), ('h', 'i', 'j'),
('i', 'j', 'k'), ('j', 'k', 'l'), ('k', 'l', 'm'), ('l', 'm', 'n'),
('m', 'n', 'o'), ('n', 'o', 'p'), ('o', 'p', 'q'), ('p', 'q', 'r'),
('q', 'r', 's'), ('r', 's', 't'), ('s', 't', 'u'), ('t', 'u', 'v'),
('u', 'v', 'w'), ('v', 'w', 'x'), ('w', 'x', 'y'), ('x', 'y', 'z');
CREATE TABLE t2 AS SELECT * FROM t1;
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t2;
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
SHOW VARIABLES LIKE 'key_cache_block_size';
Variable_name Value
key_cache_block_size 1536
SET GLOBAL key_cache_block_size= @bug28478_key_cache_block_size;
DROP TABLE t1,t2;
|