Debian Codegraph @

mysql-test/suite/sys_vars/t/delay_key_write_func.test

raw
1 ##################### mysql-test\t\delay_key_write_func.test #################
2 # #
3 # Variable Name: delay_key_write #
4 # Scope: GLOBAL #
5 # Access Type: Dynamic #
6 # Data Type: enumeration #
7 # Default Value: ON #
8 # Valid Values: ON, OFF & ALL #
9 # #
10 # #
11 # Creation Date: 2008-03-08 #
12 # Author: Rizwan #
13 # #
14 # Description: Test Cases of Dynamic System Variable delay_key_write #
15 # that checks the behavior of this variable #
16 # #
17 # Reference: #
18 # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
19 # #
20 ###############################################################################
21 --source include/force_myisam_default.inc
22
23 # Skip the test if MyISAM is not available
24 --source include/have_myisam.inc
25
26 --echo '#--------------------FN_DYNVARS_023_01-------------------------#'
27 #######################################################################
28 # Check if setting delay_key_write is changed in every new connection #
29 #######################################################################
30
31 SET @start_value= @@global.delay_key_write;
32
33 SET @@global.delay_key_write = ON;
34 SELECT @@global.delay_key_write;
35
36 --echo 'connect (user1,localhost,root,,,,)'
37 connect (user1,localhost,root,,,,);
38 --echo 'connection user1'
39 connection user1;
40 SELECT @@global.delay_key_write AS res_is_ON;
41 SET @@global.delay_key_write = ALL;
42 disconnect user1;
43
44 --echo 'connect (user1,localhost,root,,,,)'
45 connect (user1,localhost,root,,,,);
46 --echo 'connection user1'
47 connection user1;
48 SELECT @@global.delay_key_write AS res_is_ALL;
49
50 --echo '#--------------------FN_DYNVARS_023_02-------------------------#'
51 ######################################################
52 # Begin the functionality Testing of delay_key_write #
53 ######################################################
54
55 # create procedure to add rows
56 --disable_query_log
57 DELIMITER //;
58 CREATE PROCEDURE sp_addRecords (IN var1 INT,IN var2 INT)
59 BEGIN
60 WHILE (var1 < var2) DO
61 INSERT INTO t1 VALUES(var1,REPEAT('MYSQL',10),100000.0/var1);
62 SET var1=var1+1;
63 END WHILE;
64 END//
65 DELIMITER ;//
66 --enable_query_log
67
68 #==============================================================================
69 --echo '---check when delay_key_write is OFF---'
70 #==============================================================================
71
72
73 SET @@global.delay_key_write = OFF;
74
75
76 --disable_query_log
77 --disable_warnings
78 DROP TABLE IF EXISTS t1;
79 --enable_warnings
80 # create a table with delay_key_write enabled
81 CREATE TABLE t1(
82 a INT PRIMARY KEY,
83 b VARCHAR(512),
84 c DOUBLE
85 ) DELAY_KEY_WRITE = 1;
86 --enable_query_log
87
88
89 FLUSH STATUS;
90
91 CALL sp_addRecords(1,10);
92
93 SHOW STATUS LIKE 'Key_reads';
94 SHOW STATUS LIKE 'Key_writes';
95 SHOW STATUS LIKE 'Key_write_requests';
96 SELECT COUNT(*) FROM t1;
97
98 #==============================================================================
99 --echo '----check when delay_key_write is ON---'
100 #==============================================================================
101
102 SET @@global.delay_key_write = ON;
103
104 --disable_query_log
105 --disable_warnings
106 DROP TABLE IF EXISTS t1;
107 --enable_warnings
108 # create a table with delay_key_write enabled
109 CREATE TABLE t1(
110 a INT PRIMARY KEY,
111 b VARCHAR(512),
112 c DOUBLE
113 ) DELAY_KEY_WRITE = 1;
114 --enable_query_log
115
116 FLUSH STATUS;
117 CALL sp_addRecords(1,10);
118
119 SHOW STATUS LIKE 'Key_reads';
120 SHOW STATUS LIKE 'Key_writes';
121 SHOW STATUS LIKE 'Key_write_requests';
122 SELECT COUNT(*) FROM t1;
123
124 #==============================================================================
125 --echo '----check when delay_key_write is ALL---'
126 #==============================================================================
127 SET @@global.delay_key_write = ALL;
128
129 --disable_query_log
130 --disable_warnings
131 DROP TABLE IF EXISTS t1;
132 --enable_warnings
133 # create a table with delay_key_write disabled
134 CREATE TABLE t1(
135 a INT PRIMARY KEY,
136 b VARCHAR(512),
137 c DOUBLE
138 ) DELAY_KEY_WRITE = 0;
139 --enable_query_log
140
141 FLUSH STATUS;
142 CALL sp_addRecords(1,10);
143
144 SHOW STATUS LIKE 'Key_reads';
145 SHOW STATUS LIKE 'Key_writes';
146 SHOW STATUS LIKE 'Key_write_requests';
147 SELECT COUNT(*) FROM t1;
148
149 DROP PROCEDURE sp_addRecords;
150 DROP TABLE t1;
151 disconnect user1;
152
153 connection default;
154 SET @@global.delay_key_write= @start_value;
155
156 ####################################################
157 # End of functionality testing for delay_key_write #
158 ####################################################
159