1
##################### mysql-test\t\delay_key_write_func.test #################
3
# Variable Name: delay_key_write #
5
# Access Type: Dynamic #
6
# Data Type: enumeration #
8
# Valid Values: ON, OFF & ALL #
11
# Creation Date: 2008-03-08 #
14
# Description: Test Cases of Dynamic System Variable delay_key_write #
15
# that checks the behavior of this variable #
17
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
18
# server-system-variables.html #
20
###############################################################################
22
--echo '#--------------------FN_DYNVARS_023_01-------------------------#'
23
#######################################################################
24
# Check if setting delay_key_write is changed in every new connection #
25
#######################################################################
28
SET @@global.delay_key_write = ON;
29
SELECT @@global.delay_key_write;
31
--echo 'connect (user1,localhost,root,,,,)'
32
connect (user1,localhost,root,,,,);
33
--echo 'connection user1'
35
SELECT @@global.delay_key_write AS res_is_ON;
36
SET @@global.delay_key_write = ALL;
39
--echo 'connect (user1,localhost,root,,,,)'
40
connect (user1,localhost,root,,,,);
41
--echo 'connection user1'
43
SELECT @@global.delay_key_write AS res_is_ALL;
45
--echo '#--------------------FN_DYNVARS_023_02-------------------------#'
46
######################################################
47
# Begin the functionality Testing of delay_key_write #
48
######################################################
50
# create procedure to add rows
53
CREATE PROCEDURE sp_addRecords (IN var1 INT,IN var2 INT)
55
WHILE (var1 < var2) DO
56
INSERT INTO t1 VALUES(var1,REPEAT('MYSQL',10),100000.0/var1);
63
#==============================================================================
64
--echo '---check when delay_key_write is OFF---'
65
#==============================================================================
68
SET @@global.delay_key_write = OFF;
73
DROP TABLE IF EXISTS t1;
75
# create a table with delay_key_write enabled
86
CALL sp_addRecords(1,10);
88
SHOW STATUS LIKE 'Key_reads';
89
SHOW STATUS LIKE 'Key_writes';
90
SHOW STATUS LIKE 'Key_write_requests';
91
select count(*) from t1;
93
#==============================================================================
94
--echo '----check when delay_key_write is ON---'
95
#==============================================================================
97
SET @@global.delay_key_write = ON;
101
DROP TABLE IF EXISTS t1;
103
# create a table with delay_key_write enabled
108
)delay_key_write = 1;
112
CALL sp_addRecords(1,10);
114
SHOW STATUS LIKE 'Key_reads';
115
SHOW STATUS LIKE 'Key_writes';
116
SHOW STATUS LIKE 'Key_write_requests';
117
select count(*) from t1;
119
#==============================================================================
120
--echo '----check when delay_key_write is ALL---'
121
#==============================================================================
122
SET @@global.delay_key_write = ALL;
126
DROP TABLE IF EXISTS t1;
128
# create a table with delay_key_write disabled
133
)delay_key_write = 0;
137
CALL sp_addRecords(1,10);
139
SHOW STATUS LIKE 'Key_reads';
140
SHOW STATUS LIKE 'Key_writes';
141
SHOW STATUS LIKE 'Key_write_requests';
142
select count(*) from t1;
144
DROP PROCEDURE sp_addRecords;
147
####################################################
148
# End of functionality testing for delay_key_write #
149
####################################################