Debian Codegraph @

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

raw
1 ################################################################################
2 # #
3 # Variable Name: delayed_insert_limit #
4 # Scope: GLOBAL #
5 # Access Type: Dynamic #
6 # Data Type: Numeric #
7 # Default Value: 100 #
8 # Range: 1 - 4294967295 #
9 # #
10 # #
11 # Creation Date: 2008-02-25 #
12 # Author: Sharique Abdullah #
13 # Modified: HHunger 2009-02-26 Replaced 2 sleeps by wait conditions #
14 # Modified: mleich 2009-03-18 Partially reimplemented #
15 # #
16 # Description: Test Cases of Dynamic System Variable "delayed_insert_limit" #
17 # that checks behavior of this variable in the following ways #
18 # * Functionality based on different values #
19 # #
20 # Reference: #
21 # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
22 # #
23 ################################################################################
24
25 --echo ** Setup **
26 --echo
27 #
28 # Setup
29 #
30
31 --source include/not_embedded.inc
32
33 connect (con0,localhost,root,,);
34 let $con0_id=`SELECT CONNECTION_ID()`;
35 connect (con1,localhost,root,,);
36 let $con1_id=`SELECT CONNECTION_ID()`;
37
38 connection default;
39
40 SET @global_delayed_insert_limit = @@GLOBAL.delayed_insert_limit;
41
42 #
43 # Create Table
44 #
45
46 CREATE TABLE t1 (a VARCHAR(100),b VARCHAR(100),c VARCHAR(100));
47 CREATE VIEW v1 as select * from t1;
48
49 --echo '#--------------------FN_DYNVARS_25_01-------------------------#'
50
51 # delayed_insert_limit is smaller than the number of inserted rows
52
53 SET GLOBAL delayed_insert_limit = 14;
54
55 INSERT INTO t1 VALUES('1','1','1');
56 INSERT INTO t1 VALUES('2','1','1');
57 INSERT INTO t1 VALUES('3','1','1');
58 INSERT INTO t1 VALUES('4','1','1');
59 INSERT INTO t1 VALUES('5','1','1');
60 INSERT INTO t1 VALUES('6','1','1');
61
62 LOCK TABLE v1 READ;
63
64 connection con1;
65
66 INSERT DELAYED INTO t1 VALUES('7','1','1');
67 INSERT DELAYED INTO t1 VALUES('8','1','1');
68 INSERT DELAYED INTO t1 VALUES('9','1','1');
69 INSERT DELAYED INTO t1 VALUES('10','1','1');
70 INSERT DELAYED INTO t1 VALUES('11','1','1');
71 INSERT DELAYED INTO t1 VALUES('12','1','1');
72 INSERT DELAYED INTO t1 VALUES('13','1','1');
73 INSERT DELAYED INTO t1 VALUES('14','1','1');
74 INSERT DELAYED INTO t1 VALUES('15','1','1');
75 INSERT DELAYED INTO t1 VALUES('16','1','1');
76 INSERT DELAYED INTO t1 VALUES('17','1','1');
77 INSERT DELAYED INTO t1 VALUES('18','1','1');
78 INSERT DELAYED INTO t1 VALUES('19','1','1');
79 INSERT DELAYED INTO t1 VALUES('20','1','1');
80 INSERT DELAYED INTO t1 VALUES('21','1','1');
81 INSERT DELAYED INTO t1 VALUES('22','1','1');
82 INSERT DELAYED INTO t1 VALUES('23','1','1');
83 INSERT DELAYED INTO t1 VALUES('24','1','1');
84 INSERT DELAYED INTO t1 VALUES('25','1','1');
85 INSERT DELAYED INTO t1 VALUES('26','1','1');
86 INSERT DELAYED INTO t1 VALUES('27','1','1');
87 INSERT DELAYED INTO t1 VALUES('28','1','1');
88 INSERT DELAYED INTO t1 VALUES('29','1','1');
89 INSERT DELAYED INTO t1 VALUES('30','1','1');
90 INSERT DELAYED INTO t1 VALUES('31','1','1');
91 INSERT DELAYED INTO t1 VALUES('32','1','1');
92 INSERT DELAYED INTO t1 VALUES('33','1','1');
93 INSERT DELAYED INTO t1 VALUES('34','1','1');
94 INSERT DELAYED INTO t1 VALUES('35','1','1');
95 INSERT DELAYED INTO t1 VALUES('36','1','1');
96 INSERT DELAYED INTO t1 VALUES('37','1','1');
97 INSERT DELAYED INTO t1 VALUES('38','1','1');
98 INSERT DELAYED INTO t1 VALUES('39','1','1');
99 INSERT DELAYED INTO t1 VALUES('40','1','1');
100 INSERT DELAYED INTO t1 VALUES('41','1','1');
101 INSERT DELAYED INTO t1 VALUES('42','1','1');
102 INSERT DELAYED INTO t1 VALUES('43','1','1');
103
104 connection con0;
105 let $wait_condition=
106 SELECT COUNT(*) = 1 FROM information_schema.processlist
107 WHERE state = 'Waiting for table level lock' AND user='delayed';
108 --source include/wait_condition.inc
109 let $my_select= SELECT COUNT(*) FROM t1;
110 send;
111 eval $my_select;
112
113 connection default;
114 --echo ** Wait till con0 is blocked **
115 let $wait_condition=
116 SELECT COUNT(*) = 1 FROM information_schema.processlist
117 WHERE state = 'Waiting for table level lock' AND info = '$my_select';
118 --source include/wait_condition.inc
119 UNLOCK TABLES;
120
121 connection con0;
122 --echo Asynchronous "reap" result
123 --echo The next result suffers from
124 --echo '# Bug#35386 insert delayed inserts 1 + limit rows instead of just limit rows'
125 reap;
126
127 connection default;
128 let $wait_condition= SELECT count(*) = 43 FROM t1;
129 --source include/wait_condition.inc
130 --echo Checking if the delayed insert continued afterwards
131 SELECT COUNT(*) FROM t1;
132
133 DROP TABLE t1;
134 DROP VIEW v1;
135
136
137 --echo '#--------------------FN_DYNVARS_25_02-------------------------#'
138
139 # delayed_insert_limit is bigger than the number of inserted rows
140
141 CREATE TABLE t1 (a VARCHAR(100));
142 CREATE VIEW v1 AS SELECT * FROM t1;
143
144 SET GLOBAL delayed_insert_limit = 20;
145
146 INSERT INTO t1 VALUES('1');
147 INSERT INTO t1 VALUES('2');
148 INSERT INTO t1 VALUES('3');
149 INSERT INTO t1 VALUES('4');
150 INSERT INTO t1 VALUES('5');
151 INSERT INTO t1 VALUES('6');
152
153 LOCK TABLE v1 READ;
154
155 connection con1;
156
157 INSERT DELAYED INTO t1 VALUES('7');
158 INSERT DELAYED INTO t1 VALUES('8');
159 INSERT DELAYED INTO t1 VALUES('9');
160 INSERT DELAYED INTO t1 VALUES('10');
161 INSERT DELAYED INTO t1 VALUES('11');
162 INSERT DELAYED INTO t1 VALUES('12');
163 INSERT DELAYED INTO t1 VALUES('13');
164 INSERT DELAYED INTO t1 VALUES('14');
165 INSERT DELAYED INTO t1 VALUES('15');
166 INSERT DELAYED INTO t1 VALUES('16');
167 INSERT DELAYED INTO t1 VALUES('17');
168 INSERT DELAYED INTO t1 VALUES('18');
169 INSERT DELAYED INTO t1 VALUES('19');
170 INSERT DELAYED INTO t1 VALUES('20');
171 INSERT DELAYED INTO t1 VALUES('21');
172 INSERT DELAYED INTO t1 VALUES('22');
173
174 connection con0;
175 let $wait_condition=
176 SELECT COUNT(*) = 1 FROM information_schema.processlist
177 WHERE state = 'Waiting for table level lock' AND user='delayed';
178 --source include/wait_condition.inc
179 --echo Asynchronous execute
180 # Due to performance and server behaveiour the test observes values between 6 and 22.
181 # In any case the value must not be outside of that range.
182 let $my_select= SELECT COUNT(*) FROM t1;
183 send;
184 eval $my_select;
185
186 connection default;
187 --echo ** Wait till con0 is blocked **
188 let $wait_condition=
189 SELECT COUNT(*) = 1 FROM information_schema.processlist
190 WHERE state = 'Waiting for table level lock' AND info = '$my_select';
191 --source include/wait_condition.inc
192 UNLOCK TABLES;
193
194 connection con0;
195 --echo Asynchronous "reap" result
196 reap;
197
198 connection default;
199 --echo Checking if the delayed insert gives the same result afterwards
200 eval $my_select;
201
202
203 #
204 # Cleanup
205 #
206
207 connection default;
208 DROP TABLE t1;
209 DROP VIEW v1;
210 SET @@GLOBAL.delayed_insert_limit = @global_delayed_insert_limit;
211
212 disconnect con0;
213 disconnect con1;
214 let $wait_condition=
215 SELECT COUNT(*) = 0 FROM information_schema.processlist
216 WHERE id IN ($con0_id,$con1_id);
217 --source include/wait_condition.inc