~drizzle-trunk/drizzle/development

drop table if exists t1, t2, t3;

SET @save_key_buffer=@@key_buffer_size;

SELECT @@key_buffer_size, @@small.key_buffer_size;

@@key_buffer_size	@@small.key_buffer_size

1048576	0

SET @@global.key_buffer_size=16*1024*1024;

SET @@global.default.key_buffer_size=16*1024*1024;

SET @@global.default.key_buffer_size=16*1024*1024;

SET @@global.small.key_buffer_size=1*1024*1024;

SET @@global.medium.key_buffer_size=4*1024*1024;

SET @@global.medium.key_buffer_size=0;

SET @@global.medium.key_buffer_size=0;

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.default.key_buffer_size;

@@global.default.key_buffer_size

16777216

SELECT @@global.default.`key_buffer_size`;

@@global.default.`key_buffer_size`

16777216

SELECT @@global.`default`.`key_buffer_size`;

@@global.`default`.`key_buffer_size`

16777216

SELECT @@`default`.key_buffer_size;

@@`default`.key_buffer_size

16777216

SELECT @@small.key_buffer_size;

@@small.key_buffer_size

1048576

SELECT @@medium.key_buffer_size;

@@medium.key_buffer_size

0

SET @@global.key_buffer_size=@save_key_buffer;

SELECT @@default.key_buffer_size;

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'default.key_buffer_size' at line 1

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;

@@keycache1.key_cache_block_size

0

select @@keycache1.key_buffer_size;

@@keycache1.key_buffer_size

0

set global keycache1.key_cache_block_size=2048;

select @@keycache1.key_buffer_size;

@@keycache1.key_buffer_size

0

select @@keycache1.key_cache_block_size;

@@keycache1.key_cache_block_size

2048

set global keycache1.key_buffer_size=1*1024*1024;

select @@keycache1.key_buffer_size;

@@keycache1.key_buffer_size

1048576

select @@keycache1.key_cache_block_size;

@@keycache1.key_cache_block_size

2048

set global keycache2.key_buffer_size=4*1024*1024;

select @@keycache2.key_buffer_size;

@@keycache2.key_buffer_size

4194304

select @@keycache2.key_cache_block_size;

@@keycache2.key_cache_block_size

1024

set global keycache1.key_buffer_size=0;

select @@keycache1.key_buffer_size;

@@keycache1.key_buffer_size

0

select @@keycache1.key_cache_block_size;

@@keycache1.key_cache_block_size

2048

select @@key_buffer_size;

@@key_buffer_size

1048576

select @@key_cache_block_size;

@@key_cache_block_size

1024

set global keycache1.key_buffer_size=1024*1024;

create table t1 (p int primary key, a char(10)) delay_key_write=1 ENGINE=myisam;

create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a)) ENGINE=myisam;

show status like 'key_blocks_used';

Variable_name	Value

Key_blocks_used	0

show status like 'key_blocks_unused';

Variable_name	Value

Key_blocks_unused	837

insert into t1 values (1, 'qqqq'), (11, 'yyyy');

insert into t2 values (1, 1, 'qqqq'), (2, 1, 'pppp'),

(3, 1, 'yyyy'), (4, 3, 'zzzz');

select * from t1;

p	a

1	qqqq

11	yyyy

select * from t2;

p	i	a

1	1	qqqq

2	1	pppp

3	1	yyyy

4	3	zzzz

update t1 set p=2 where p=1;

update t2 set i=2 where i=1;

show status like 'key_blocks_used';

Variable_name	Value

Key_blocks_used	4

show status like 'key_blocks_unused';

Variable_name	Value

Key_blocks_unused	833

cache index t1 key (`primary`) in keycache1;

Table	Op	Msg_type	Msg_text

test.t1	assign_to_keycache	status	OK

explain select p from t1;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	2	Using index

select p from t1;

p

2

11

explain select i from t2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	index	NULL	k1	5	NULL	4	Using index

select i from t2;

i

2

2

2

3

explain select count(*) from t1, t2 where t1.p = t2.i;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index

1	SIMPLE	t2	ref	k1	k1	5	test.t1.p	2	Using index

select count(*) from t1, t2 where t1.p = t2.i;

count(*)

3

cache index t2 in keycache1;

Table	Op	Msg_type	Msg_text

test.t2	assign_to_keycache	status	OK

update t2 set p=p+1000, i=2 where a='qqqq';

cache index t2 in keycache2;

Table	Op	Msg_type	Msg_text

test.t2	assign_to_keycache	status	OK

insert into t2 values (2000, 3, 'yyyy');

cache index t2 in keycache1;

Table	Op	Msg_type	Msg_text

test.t2	assign_to_keycache	status	OK

update t2 set p=3000 where a='zzzz';

select * from t2;

p	i	a

1001	2	qqqq

2	2	pppp

3	2	yyyy

3000	3	zzzz

2000	3	yyyy

explain select p from t2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	5	Using index

select p from t2;

p

2

3

1001

2000

3000

explain select i from t2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	index	NULL	k1	5	NULL	5	Using index

select i from t2;

i

2

2

2

3

3

explain select a from t2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	index	NULL	k2	43	NULL	5	Using index

select a from t2;

a

pppp

qqqq

yyyy

yyyy

zzzz

cache index t1 in unknown_key_cache;

ERROR HY000: Unknown key cache 'unknown_key_cache'

cache index t1 key (unknown_key) in keycache1;

Table	Op	Msg_type	Msg_text

test.t1	assign_to_keycache	Error	Key 'unknown_key' doesn't exist in table 't1'

test.t1	assign_to_keycache	status	Operation failed

select @@keycache2.key_buffer_size;

@@keycache2.key_buffer_size

4194304

select @@keycache2.key_cache_block_size;

@@keycache2.key_cache_block_size

1024

set global keycache2.key_buffer_size=0;

select @@keycache2.key_buffer_size;

@@keycache2.key_buffer_size

0

select @@keycache2.key_cache_block_size;

@@keycache2.key_cache_block_size

1024

set global keycache2.key_buffer_size=1024*1024;

select @@keycache2.key_buffer_size;

@@keycache2.key_buffer_size

1048576

update t2 set p=4000 where a='zzzz';

update t1 set p=p+1;

set global keycache1.key_buffer_size=0;

select * from t2;

p	i	a

1001	2	qqqq

2	2	pppp

3	2	yyyy

4000	3	zzzz

2000	3	yyyy

select p from t2;

p

2

3

1001

2000

4000

explain select i from t2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	index	NULL	k1	5	NULL	5	Using index

select i from t2;

i

2

2

2

3

3

explain select a from t2;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

1	SIMPLE	t2	index	NULL	k2	43	NULL	5	Using index

select a from t2;

a

pppp

qqqq

yyyy

yyyy

zzzz

select * from t1;

p	a

3	qqqq

12	yyyy

select p from t1;

p

3

12

create table t3 (like t1);

cache index t3 in small;

Table	Op	Msg_type	Msg_text

test.t3	assign_to_keycache	status	OK

insert into t3 select * from t1;

cache index t3 in keycache2;

Table	Op	Msg_type	Msg_text

test.t3	assign_to_keycache	status	OK

cache index t1,t2 in default;

Table	Op	Msg_type	Msg_text

test.t1	assign_to_keycache	status	OK

test.t2	assign_to_keycache	status	OK

drop table t1,t2,t3;

show status like 'key_blocks_used';

Variable_name	Value

Key_blocks_used	4

show status like 'key_blocks_unused';

Variable_name	Value

Key_blocks_unused	837

set global keycache2.key_buffer_size=0;

set global keycache3.key_buffer_size=100;

Warnings:

Warning	1292	Truncated incorrect key_buffer_size value: '100'

set global keycache3.key_buffer_size=0;

set global key_cache_block_size= @my_key_cache_block_size;

ERROR 42000: Incorrect argument type to variable 'key_cache_block_size'

CREATE 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 TABLE t1(a int NOT NULL AUTO_INCREMENT PRIMARY KEY, b int) ENGINE=MYISAM;

CREATE 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

1048576

SET @bug28478_key_cache_block_size= @@global.key_cache_block_size;

SET GLOBAL key_cache_block_size= 1536;

CREATE 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');

INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1;

INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1;

INSERT INTO t1 (c1, c2, c3) SELECT c1, c2, c3 from t1;

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;