Debian Codegraph @

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

raw
1 ################################################################################
2 # #
3 # Variable Name: sql_low_priority_updates #
4 # Scope: GLOBAL & SESSION #
5 # Access Type: Dynamic #
6 # Data Type: BOOLEAN #
7 # Default Value: 1 TRUE #
8 # Values: 1 TRUE, 0 FALSE #
9 # #
10 # #
11 # Creation Date: 2008-02-25 #
12 # Author: Sharique Abdullah #
13 # #
14 # Description: Test Cases of Dynamic System Variable sql_low_priority_updates #
15 # that checks behavior of this variable in the following ways #
16 # * Functionality based on different values #
17 # #
18 # Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html #
19 # #
20 ################################################################################
21
22 --source include/force_myisam_default.inc
23
24 # Skip the test if MyISAM is not available
25 --source include/have_myisam.inc
26
27 ################################################################
28 # sql_low_priority_updates was renamed to low_priority_updates #
29 ################################################################
30
31 --echo ** Setup **
32 --echo
33 #
34 # Setup
35 #
36 --echo ** Connecting con0 using root **
37 connect (con0,localhost,root,,);
38 --echo ** Connecting con1 using root **
39 connect (con1,localhost,root,,);
40
41 --echo ** Connection default **
42 connection default;
43
44 SET @global_low_priority_updates = @@GLOBAL.low_priority_updates;
45 SET @session_low_priority_updates = @@SESSION.low_priority_updates;
46
47 #
48 # Create Table
49 #
50
51 CREATE TABLE t1 (a varchar(100));
52 create view v1 as select * from t1;
53
54 --echo '#--------------------FN_DYNVARS_160_01-------------------------#'
55 #
56 # Value ON
57 #
58
59 --echo ** Connection con0 **
60 connection con0;
61 SET SESSION low_priority_updates = ON;
62 --echo ** Connection con1 **
63 connection con1;
64 SET SESSION low_priority_updates = ON;
65 --echo ** Connection default **
66 connection default;
67 SET SESSION low_priority_updates = ON;
68
69 INSERT INTO t1 VALUES('1');
70 INSERT INTO t1 VALUES('2');
71 INSERT INTO t1 VALUES('3');
72 INSERT INTO t1 VALUES('4');
73 INSERT INTO t1 VALUES('5');
74 INSERT INTO t1 VALUES('6');
75
76 LOCK TABLE v1 WRITE;
77
78 --echo ** Connection con1 **
79 connection con1;
80
81 --echo ** Asynchronous Execution **
82 delimiter |;
83
84 send
85 UPDATE t1 SET a = CONCAT(a,"-updated");|
86
87 delimiter ;|
88
89 --echo ** Connection con0 **
90 connection con0;
91 let $wait_condition = SELECT COUNT(*) > 0 FROM information_schema.processlist
92 WHERE state='Waiting for table metadata lock' AND
93 info LIKE 'UPDATE t1 SET a = CONCAT(a,"-updated")';
94 --source include/wait_condition.inc
95
96 --echo ** Asynchronous Execution **
97 delimiter |;
98
99 send
100 LOCK TABLE t1 READ;
101 SELECT * FROM t1;
102 UNLOCK TABLES;|
103
104 delimiter ;|
105
106 --echo ** Connection default **
107 connection default;
108
109 let $wait_condition= SELECT count(*) = 2 FROM information_schema.processlist
110 WHERE state LIKE 'Waiting for table metadata lock';
111 --source include/wait_condition.inc
112 UNLOCK TABLES;
113
114 --echo ** Connection con0 **
115 connection con0;
116 --echo ** Asynchronous Result **
117 reap;
118
119 --echo Expected values of a without -updated;
120
121 --echo ** Connection default **
122 connection default;
123
124 DELETE FROM t1;
125
126 --echo '#--------------------FN_DYNVARS_160_02-------------------------#'
127 #
128 # Value ON
129 #
130
131 --echo ** Connection con0 **
132 connection con0;
133 SET SESSION low_priority_updates = OFF;
134 --echo ** Connection con1 **
135 connection con1;
136 reap;
137 SET SESSION low_priority_updates = OFF;
138 --echo ** Connection default**
139 connection default;
140 SET SESSION low_priority_updates = OFF;
141
142 INSERT INTO t1 VALUES('1');
143 INSERT INTO t1 VALUES('2');
144 INSERT INTO t1 VALUES('3');
145 INSERT INTO t1 VALUES('4');
146 INSERT INTO t1 VALUES('5');
147 INSERT INTO t1 VALUES('6');
148
149 LOCK TABLE v1 WRITE;
150
151 --echo ** Connection con1 **
152 connection con1;
153
154 --echo ** Asynchronous Execution **
155 delimiter |;
156
157 send
158 UPDATE t1 SET a = CONCAT(a,"-updated");|
159
160 delimiter ;|
161
162 --echo ** Connection con0 **
163 connection con0;
164
165 let $wait_condition = SELECT COUNT(*) > 0 FROM information_schema.processlist
166 WHERE state='Waiting for table metadata lock' AND
167 info LIKE 'UPDATE t1 SET a = CONCAT(a,"-updated")';
168 --source include/wait_condition.inc
169
170 --echo ** Asynchronous Execution **
171 delimiter |;
172
173 send
174 LOCK TABLE t1 READ;
175 SELECT * FROM t1 ORDER BY a ASC;
176 UNLOCK TABLES;|
177
178 delimiter ;|
179
180 --echo ** Connection default **
181 connection default;
182
183 let $wait_condition= SELECT count(*) = 2 FROM information_schema.processlist
184 WHERE state LIKE 'Waiting for table metadata lock';
185 --source include/wait_condition.inc
186 UNLOCK TABLES;
187
188 --echo ** Connection con0 **
189 connection con0;
190 --echo ** Asynchronous Result **
191 reap;
192 --echo Expected values of a with -updated;
193
194 --echo ** Connection default**
195 connection default;
196
197 DELETE FROM t1;
198
199 --echo '#--------------------FN_DYNVARS_160_03-------------------------#'
200 #
201 # Session data integrity check & GLOBAL Value check
202 #
203
204 --echo ** Connecting con_int1 using root **
205 connect (con_int1,localhost,root,,);
206
207 --echo ** Connection con_int1 **
208 connection con_int1;
209 SELECT @@SESSION.low_priority_updates;
210 --echo 1 / TRUE Expected
211
212 SET SESSION low_priority_updates = FALSE;
213
214 --echo ** Connecting con_int2 using root **
215 connect (con_int2,localhost,root,,);
216
217 --echo ** Connection con_int2 **
218 connection con_int2;
219 SELECT @@SESSION.low_priority_updates;
220 --echo 1 / TRUE Expected
221
222 SET SESSION low_priority_updates = TRUE;
223
224 --echo ** Connection con_int1 **
225 connection con_int1;
226 SELECT @@SESSION.low_priority_updates;
227 --echo 0 / FALSE Expected
228
229 --echo ** Connection con_int2 **
230 connection con_int2;
231 SELECT @@SESSION.low_priority_updates;
232 --echo 1 / TRUE Expected
233
234 --echo ** Connection default **
235 connection default;
236
237 --echo Disconnecting Connections con_int1, con_int2
238 disconnect con_int1;
239 disconnect con_int2;
240
241 #
242 # Cleanup
243 #
244
245 --echo ** Connection default **
246 connection default;
247 --echo Disconnecting Connections con0, con1
248 disconnect con0;
249 disconnect con1;
250
251 drop view v1;
252 DROP TABLE t1;
253
254 ######################################################################################
255 # Check if changing session variable throws error without sufficient privileges #
256 ######################################################################################
257 CREATE USER foo, bar;
258 GRANT SESSION_VARIABLES_ADMIN ON *.* TO bar;
259 --let $conn_name = foo_connection
260 --connect($conn_name, localhost, foo,,,)
261 SELECT @@session.low_priority_updates;
262 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
263 SET @@session.low_priority_updates = TRUE;
264 --connection default
265 disconnect $conn_name;
266 --let $conn_name = bar_connection
267 --connect($conn_name, localhost, bar,,,)
268 SELECT @@session.low_priority_updates;
269 SET @@session.low_priority_updates = TRUE;
270 SELECT @@session.low_priority_updates;
271 --connection default
272 disconnect $conn_name;
273 DROP USER foo, bar;
274
275
276 SET @@GLOBAL.low_priority_updates = @global_low_priority_updates;
277 SET @@SESSION.low_priority_updates = @session_low_priority_updates;