Manual merge after improving concurrency for key cache reassignment

3 files changed, 274 insertions, 3 deletions

diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 3af75b3c5bc..6c3960a91ee 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -514,7 +514,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 explain 
 select min(a1) from t1 where a1 != 'KKK';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	3	NULL	14	Using where; Using index
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	3	NULL	14	Using where; Using index
 explain
 select max(a3) from t1 where a2 < 2 and a3 < 'SEA';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -560,7 +560,7 @@ explain
 select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	3	NULL	14	Using index
-1	SIMPLE	t2	index	NULL	k2	4	NULL	6	Using where; Using index
+1	SIMPLE	t2	range	k2	k2	4	NULL	6	Using where; Using index
 drop table t1, t2;
 CREATE TABLE t1 (a int, b int);
 select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1;
diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result
index dd45cb51d33..79fe0599bc4 100644
--- a/mysql-test/r/key_cache.result
+++ b/mysql-test/r/key_cache.result
@@ -41,3 +41,194 @@ SELECT @@default.key_buffer_size;
 ERROR 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
 SELECT @@skr.table_type="test";
 ERROR HY000: Variable 'table_type' 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
+0
+select @@key_buffer_size;
+@@key_buffer_size
+2097152
+select @@key_cache_block_size;
+@@key_cache_block_size
+1024
+drop table if exists t1, t2;
+create table t1 (p int primary key, a char(10));
+create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a));
+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;
+cache index t1 keys 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 where; Using index
+select count(*) from t1, t2 where t1.p = t2.i;
+count(*)
+3
+cache index t2 keys 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 keys 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 keys 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	11	NULL	5	Using index
+select a from t2;
+a
+pppp
+qqqq
+yyyy
+yyyy
+zzzz
+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
+0
+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	11	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
diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test
index fb6b6b0027f..371d610eb5f 100644
--- a/mysql-test/t/key_cache.test
+++ b/mysql-test/t/key_cache.test
@@ -1,5 +1,5 @@
 #
-# Test of key cache
+# Test of multiple key caches
 #
 
 SET @save_key_buffer=@@key_buffer_size;
@@ -40,3 +40,83 @@ SET @@global.key_buffer_size=@save_key_buffer;
 SELECT @@default.key_buffer_size;
 --error 1271
 SELECT @@skr.table_type="test";
+
+select @@keycache1.key_cache_block_size;
+select @@keycache1.key_buffer_size;
+set global keycache1.key_cache_block_size=2048;
+select @@keycache1.key_buffer_size;
+select @@keycache1.key_cache_block_size;
+set global keycache1.key_buffer_size=1*1024*1024;
+select @@keycache1.key_buffer_size;
+select @@keycache1.key_cache_block_size;
+set global keycache2.key_buffer_size=4*1024*1024;
+select @@keycache2.key_buffer_size;
+select @@keycache2.key_cache_block_size;
+set global keycache1.key_buffer_size=0;
+select @@keycache1.key_buffer_size;
+select @@keycache1.key_cache_block_size;
+select @@key_buffer_size;
+select @@key_cache_block_size;
+
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+
+create table t1 (p int primary key, a char(10));
+create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a));
+
+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;
+select * from t2;
+
+update t1 set p=2 where p=1;
+update t2 set i=2 where i=1;
+
+cache index t1 keys in keycache1;
+
+explain select p from t1;
+select p from t1;
+explain select i from t2;
+select i from t2;
+explain select count(*) from t1, t2 where t1.p = t2.i;
+select count(*) from t1, t2 where t1.p = t2.i;
+
+cache index t2 keys in keycache1;
+update t2 set p=p+1000, i=2 where a='qqqq';
+cache index t2 keys in keycache2;
+insert into t2 values (2000, 3, 'yyyy');
+cache index t2 keys in keycache1;
+update t2 set p=3000 where a='zzzz';
+select * from t2;
+explain select p from t2;
+select p from t2;
+explain select i from t2;
+select i from t2;
+explain select a from t2;
+select a from t2;
+
+select @@keycache2.key_buffer_size;
+select @@keycache2.key_cache_block_size;
+set global keycache2.key_buffer_size=0;
+select @@keycache2.key_buffer_size;
+select @@keycache2.key_cache_block_size;
+
+
+update t2 set p=4000 where a='zzzz';
+update t1 set p=p+1;
+
+set global keycache1.key_buffer_size=0;
+select * from t2;
+select p from t2;
+explain select i from t2;
+select i from t2;
+explain select a from t2;
+select a from t2;
+
+select * from t1;
+select p from t1;
+
+