1
################### mysql-test\t\key_buffer_size_func.test ####################
3
# Variable Name: key_buffer_size #
5
# Access Type: Dynamic #
7
# Default Value: 131072 #
11
# Creation Date: 2008-03-07 #
12
# Author: Salman Rawala #
14
# Description: Test Cases of Dynamic System Variable key_buffer_size #
15
# that checks the functionality of this variable #
18
# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
20
###############################################################################
23
DROP TABLE IF EXISTS t1;
26
#########################
27
# Creating new table #
28
#########################
30
--echo ## Creating new table t1 ##
33
id INT NOT NULL AUTO_INCREMENT,
39
SET @start_value= @@global.key_buffer_size;
43
--echo '#--------------------FN_DYNVARS_055_01-------------------------#'
44
########################################################################
45
# Setting initial value of key_buffer_size, inserting some rows
46
# & creating 2 new connections
47
########################################################################
49
--echo ## Setting initial value of variable to 131072 ##
50
SET @@global.key_buffer_size = 131072;
53
--echo ## Creating two new connections ##
54
CONNECT (test_con1,localhost,root,,);
55
CONNECT (test_con2,localhost,root,,);
58
--echo '#--------------------FN_DYNVARS_055_02-------------------------#'
59
###############################################################################
60
# Verifying initial behavior of variable by concatinating values greater than 4
61
###############################################################################
63
--echo ## Connecting with connection test_con1 ##
65
SELECT @@global.key_buffer_size;
68
--echo ## Inserting some rows in table ##
69
INSERT INTO t1(rollno, name) VALUES(1, 'Record_1');
70
INSERT INTO t1(rollno, name) VALUES(2, 'Record_2');
71
INSERT INTO t1(rollno, name) VALUES(1, 'Record_3');
72
INSERT INTO t1(rollno, name) VALUES(3, 'Record_4');
73
INSERT INTO t1(rollno, name) VALUES(1, 'Record_5');
74
INSERT INTO t1(rollno, name) VALUES(3, 'Record_6');
75
INSERT INTO t1(rollno, name) VALUES(4, 'Record_7');
76
INSERT INTO t1(rollno, name) VALUES(4, 'Record_8');
77
INSERT INTO t1(rollno, name) VALUES(4, 'Record_9');
78
INSERT INTO t1(rollno, name) VALUES(4, 'Record_10');
80
--echo ## Key_reads must be zero (no disk access) ##
81
show status like 'Key_reads';
83
--echo ## Switching to connection test_con2 ##
86
--echo ## Key_reads must be zero (no disk access) ##
87
show status like 'Key_reads';
89
--echo ## Connecting with connection test_con1 ##
92
--echo ## Inserting some rows in table ##
93
INSERT INTO t1(rollno, name) VALUES(5, 'Record_11');
94
INSERT INTO t1(rollno, name) VALUES(6, 'Record_12');
95
INSERT INTO t1(rollno, name) VALUES(5, 'Record_13');
96
INSERT INTO t1(rollno, name) VALUES(7, 'Record_14');
97
INSERT INTO t1(rollno, name) VALUES(5, 'Record_15');
98
INSERT INTO t1(rollno, name) VALUES(7, 'Record_16');
99
INSERT INTO t1(rollno, name) VALUES(8, 'Record_17');
100
INSERT INTO t1(rollno, name) VALUES(8, 'Record_18');
101
INSERT INTO t1(rollno, name) VALUES(8, 'Record_19');
102
INSERT INTO t1(rollno, name) VALUES(8, 'Record_20');
104
--echo ## Key_reads must be zero (no disk access) ##
105
show status like 'Key_reads';
107
############################################################
108
# Disconnecting all connection & dropping table #
109
############################################################
111
--echo ## Dropping table ##
112
DROP TABLE IF EXISTS t1;
114
--echo ## Disconnecting both the connections ##
115
DISCONNECT test_con2;
116
DISCONNECT test_con1;
119
SET @@global.key_buffer_size= @start_value;