Debian Codegraph @

mysql-test/suite/auth_sec/r/acl_tables_locking_big_debug_1.result

raw
1 # -----------------------------------------------------------------------
2 # Setup
3 # Saving initial values
4 SET @global_start_value = @@global.transaction_isolation;
5 SELECT @global_start_value;
6 @global_start_value
7 REPEATABLE-READ
8 SET @session_start_value = @@session.transaction_isolation;
9 SELECT @session_start_value;
10 @session_start_value
11 REPEATABLE-READ
12 # Setting transaction isolation to 'READ-UNCOMMITTED'
13 SET GLOBAL transaction_isolation = 'READ-UNCOMMITTED';
14 # -----------------------------------------------------------------------
15 # Case 1: Successful ACL DDLs + FLUSH PRIVILEGES
16 # -------------------------------
17 # Case 1.1: CREATE USER + FLUSH PRIVILEGES
18 connect conn_attempt, localhost, root,,,,;
19 SET @@lock_wait_timeout=1;
20 connect conn_execute, localhost, root,,,,;
21 connection conn_attempt;
22 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
23 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
24 FLUSH PRIVILEGES;
25 connection conn_execute;
26 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
27 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
28 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
29 CREATE USER u1;
30 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
31 connection conn_attempt;
32 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
33 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
34 connection default;
35 SET DEBUG_SYNC="RESET";
36 disconnect conn_attempt;
37 disconnect conn_execute;
38 DROP USER u1;
39 # -------------------------------
40 # Case 1.2: ALTER USER + FLUSH PRIVILEGES
41 CREATE USER u1;
42 connect conn_attempt, localhost, root,,,,;
43 SET @@lock_wait_timeout=1;
44 connect conn_execute, localhost, root,,,,;
45 connection conn_attempt;
46 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
47 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
48 FLUSH PRIVILEGES;
49 connection conn_execute;
50 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
51 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
52 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
53 ALTER USER u1 IDENTIFIED BY 'abcd';
54 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
55 connection conn_attempt;
56 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
57 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
58 connection default;
59 SET DEBUG_SYNC="RESET";
60 disconnect conn_attempt;
61 disconnect conn_execute;
62 DROP USER u1;
63 # -------------------------------
64 # Case 1.3: RENAME USER + FLUSH PRIVILEGES
65 CREATE USER u1;
66 connect conn_attempt, localhost, root,,,,;
67 SET @@lock_wait_timeout=1;
68 connect conn_execute, localhost, root,,,,;
69 connection conn_attempt;
70 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
71 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
72 FLUSH PRIVILEGES;
73 connection conn_execute;
74 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
75 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
76 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
77 RENAME USER u1 TO u2;
78 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
79 connection conn_attempt;
80 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
81 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
82 connection default;
83 SET DEBUG_SYNC="RESET";
84 disconnect conn_attempt;
85 disconnect conn_execute;
86 DROP USER u2;
87 # -------------------------------
88 # Case 1.4: CREATE ROLE + FLUSH PRIVILEGES
89 connect conn_attempt, localhost, root,,,,;
90 SET @@lock_wait_timeout=1;
91 connect conn_execute, localhost, root,,,,;
92 connection conn_attempt;
93 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
94 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
95 FLUSH PRIVILEGES;
96 connection conn_execute;
97 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
98 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
99 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
100 CREATE ROLE r1;
101 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
102 connection conn_attempt;
103 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
104 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
105 connection default;
106 SET DEBUG_SYNC="RESET";
107 disconnect conn_attempt;
108 disconnect conn_execute;
109 DROP ROLE r1;
110 # -------------------------------
111 # Case 1.5: GRANT privilege + FLUSH PRIVILEGES
112 CREATE USER u1;
113 connect conn_attempt, localhost, root,,,,;
114 SET @@lock_wait_timeout=1;
115 connect conn_execute, localhost, root,,,,;
116 connection conn_attempt;
117 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
118 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
119 FLUSH PRIVILEGES;
120 connection conn_execute;
121 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
122 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
123 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
124 GRANT SELECT ON *.* TO u1;
125 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
126 connection conn_attempt;
127 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
128 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
129 connection default;
130 SET DEBUG_SYNC="RESET";
131 disconnect conn_attempt;
132 disconnect conn_execute;
133 DROP USER u1;
134 # -------------------------------
135 # Case 1.6: GRANT role + FLUSH PRIVILEGES
136 CREATE USER u1;
137 CREATE ROLE r1;
138 connect conn_attempt, localhost, root,,,,;
139 SET @@lock_wait_timeout=1;
140 connect conn_execute, localhost, root,,,,;
141 connection conn_attempt;
142 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
143 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
144 FLUSH PRIVILEGES;
145 connection conn_execute;
146 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
147 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
148 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
149 GRANT r1 TO u1;
150 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
151 connection conn_attempt;
152 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
153 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
154 connection default;
155 SET DEBUG_SYNC="RESET";
156 disconnect conn_attempt;
157 disconnect conn_execute;
158 DROP USER r1;
159 DROP USER u1;
160 # -------------------------------
161 # Case 1.7: SET DEFAULT ROLE + FLUSH PRIVILEGES
162 CREATE USER u1;
163 CREATE ROLE r1;
164 GRANT r1 TO u1;
165 connect conn_attempt, localhost, root,,,,;
166 SET @@lock_wait_timeout=1;
167 connect conn_execute, localhost, root,,,,;
168 connection conn_attempt;
169 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
170 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
171 FLUSH PRIVILEGES;
172 connection conn_execute;
173 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
174 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
175 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
176 SET DEFAULT ROLE r1 TO u1;
177 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
178 connection conn_attempt;
179 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
180 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
181 connection default;
182 SET DEBUG_SYNC="RESET";
183 disconnect conn_attempt;
184 disconnect conn_execute;
185 DROP USER r1;
186 DROP USER u1;
187 # -------------------------------
188 # Case 1.8: SET PASSWORD + FLUSH PRIVILEGES
189 CREATE USER u1;
190 connect conn_attempt, localhost, root,,,,;
191 SET @@lock_wait_timeout=1;
192 connect conn_execute, localhost, root,,,,;
193 connection conn_attempt;
194 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
195 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
196 FLUSH PRIVILEGES;
197 connection conn_execute;
198 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
199 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
200 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
201 SET PASSWORD FOR u1 = 'efgh';
202 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
203 connection conn_attempt;
204 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
205 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
206 connection default;
207 SET DEBUG_SYNC="RESET";
208 disconnect conn_attempt;
209 disconnect conn_execute;
210 DROP USER u1;
211 # -------------------------------
212 # Case 1.9: REVOKE role + FLUSH PRIVILEGES
213 CREATE USER u1;
214 CREATE ROLE r1;
215 GRANT r1 TO u1;
216 connect conn_attempt, localhost, root,,,,;
217 SET @@lock_wait_timeout=1;
218 connect conn_execute, localhost, root,,,,;
219 connection conn_attempt;
220 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
221 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
222 FLUSH PRIVILEGES;
223 connection conn_execute;
224 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
225 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
226 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
227 REVOKE r1 FROM u1;
228 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
229 connection conn_attempt;
230 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
231 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
232 connection default;
233 SET DEBUG_SYNC="RESET";
234 disconnect conn_attempt;
235 disconnect conn_execute;
236 DROP ROLE r1;
237 DROP USER u1;
238 # -------------------------------
239 # Case 1.10: REVOKE privilege + FLUSH PRIVILEGES
240 CREATE USER u1;
241 connect conn_attempt, localhost, root,,,,;
242 SET @@lock_wait_timeout=1;
243 connect conn_execute, localhost, root,,,,;
244 connection conn_attempt;
245 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
246 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
247 FLUSH PRIVILEGES;
248 connection conn_execute;
249 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
250 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
251 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
252 REVOKE SELECT ON *.* FROM u1;
253 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
254 connection conn_attempt;
255 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
256 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
257 connection default;
258 SET DEBUG_SYNC="RESET";
259 disconnect conn_attempt;
260 disconnect conn_execute;
261 DROP USER u1;
262 # -------------------------------
263 # Case 1.11: DROP ROLE + FLUSH PRIVILEGES
264 CREATE ROLE r1;
265 connect conn_attempt, localhost, root,,,,;
266 SET @@lock_wait_timeout=1;
267 connect conn_execute, localhost, root,,,,;
268 connection conn_attempt;
269 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
270 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
271 FLUSH PRIVILEGES;
272 connection conn_execute;
273 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
274 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
275 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
276 DROP ROLE r1;
277 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
278 connection conn_attempt;
279 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
280 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
281 connection default;
282 SET DEBUG_SYNC="RESET";
283 disconnect conn_attempt;
284 disconnect conn_execute;
285 # -------------------------------
286 # Case 1.12: DROP USER + FLUSH PRIVILEGES
287 CREATE USER u1;
288 connect conn_attempt, localhost, root,,,,;
289 SET @@lock_wait_timeout=1;
290 connect conn_execute, localhost, root,,,,;
291 connection conn_attempt;
292 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
293 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
294 FLUSH PRIVILEGES;
295 connection conn_execute;
296 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
297 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
298 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
299 DROP USER u1;
300 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
301 connection conn_attempt;
302 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
303 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
304 connection default;
305 SET DEBUG_SYNC="RESET";
306 disconnect conn_attempt;
307 disconnect conn_execute;
308 # -----------------------------------------------------------------------
309 # Case 2: Unsuccessful ACL DDLs + FLUSH PRIVILEGES
310 SET @@lock_wait_timeout=1;
311 # -------------------------------
312 # Case 2.1: CREATE USER + FLUSH PRIVILEGES
313 CREATE USER u1;
314 connect conn_attempt, localhost, root,,,,;
315 SET @@lock_wait_timeout=1;
316 connect conn_execute, localhost, root,,,,;
317 connection conn_attempt;
318 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
319 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
320 FLUSH PRIVILEGES;
321 connection conn_execute;
322 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
323 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
324 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
325 CREATE USER u3, u2, u1;
326 ERROR HY000: Operation CREATE USER failed for 'u1'@'%'
327 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
328 connection conn_attempt;
329 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
330 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
331 connection default;
332 SET DEBUG_SYNC="RESET";
333 disconnect conn_attempt;
334 disconnect conn_execute;
335 DROP USER u1;
336 # -------------------------------
337 # Case 2.2: ALTER USER + FLUSH PRIVILEGES
338 connect conn_attempt, localhost, root,,,,;
339 SET @@lock_wait_timeout=1;
340 connect conn_execute, localhost, root,,,,;
341 connection conn_attempt;
342 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
343 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
344 FLUSH PRIVILEGES;
345 connection conn_execute;
346 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
347 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
348 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
349 ALTER USER u2 IDENTIFIED BY 'abcd';
350 ERROR HY000: Operation ALTER USER failed for 'u2'@'%'
351 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
352 connection conn_attempt;
353 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
354 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
355 connection default;
356 SET DEBUG_SYNC="RESET";
357 disconnect conn_attempt;
358 disconnect conn_execute;
359 # -------------------------------
360 # Case 2.3: RENAME USER + FLUSH PRIVILEGES
361 connect conn_attempt, localhost, root,,,,;
362 SET @@lock_wait_timeout=1;
363 connect conn_execute, localhost, root,,,,;
364 connection conn_attempt;
365 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
366 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
367 FLUSH PRIVILEGES;
368 connection conn_execute;
369 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
370 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
371 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
372 RENAME USER u2 TO u3;
373 ERROR HY000: Operation RENAME USER failed for 'u2'@'%'
374 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
375 connection conn_attempt;
376 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
377 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
378 connection default;
379 SET DEBUG_SYNC="RESET";
380 disconnect conn_attempt;
381 disconnect conn_execute;
382 # -------------------------------
383 # Case 2.4: DROP USER + FLUSH PRIVILEGES
384 connect conn_attempt, localhost, root,,,,;
385 SET @@lock_wait_timeout=1;
386 connect conn_execute, localhost, root,,,,;
387 connection conn_attempt;
388 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
389 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
390 FLUSH PRIVILEGES;
391 connection conn_execute;
392 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
393 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
394 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
395 DROP USER u2;
396 ERROR HY000: Operation DROP USER failed for 'u2'@'%'
397 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
398 connection conn_attempt;
399 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
400 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
401 connection default;
402 SET DEBUG_SYNC="RESET";
403 disconnect conn_attempt;
404 disconnect conn_execute;
405 # -------------------------------
406 # Case 2.5: SET PASSWORD + FLUSH PRIVILEGES
407 CREATE USER u1;
408 connect conn_attempt, localhost, root,,,,;
409 SET @@lock_wait_timeout=1;
410 connect conn_execute, localhost, root,,,,;
411 connection conn_attempt;
412 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
413 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
414 FLUSH PRIVILEGES;
415 connection conn_execute;
416 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout, wl14084_simulate_set_password_failure";
417 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
418 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
419 SET PASSWORD FOR u1 = 'abcd';
420 ERROR 42000: Can't find any matching row in the user table
421 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout, wl14084_simulate_set_password_failure";
422 connection conn_attempt;
423 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
424 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
425 connection default;
426 SET DEBUG_SYNC="RESET";
427 disconnect conn_attempt;
428 disconnect conn_execute;
429 DROP USER u1;
430 # -------------------------------
431 # Case 2.6: CREATE ROLE + FLUSH PRIVILEGES
432 CREATE ROLE r1;
433 connect conn_attempt, localhost, root,,,,;
434 SET @@lock_wait_timeout=1;
435 connect conn_execute, localhost, root,,,,;
436 connection conn_attempt;
437 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
438 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
439 FLUSH PRIVILEGES;
440 connection conn_execute;
441 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
442 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
443 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
444 CREATE ROLE r3, r2, r1;
445 ERROR HY000: Operation CREATE ROLE failed for 'r1'@'%'
446 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
447 connection conn_attempt;
448 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
449 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
450 connection default;
451 SET DEBUG_SYNC="RESET";
452 disconnect conn_attempt;
453 disconnect conn_execute;
454 DROP ROLE r1;
455 # -------------------------------
456 # Case 2.7: DROP ROLE + FLUSH PRIVILEGES
457 connect conn_attempt, localhost, root,,,,;
458 SET @@lock_wait_timeout=1;
459 connect conn_execute, localhost, root,,,,;
460 connection conn_attempt;
461 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
462 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
463 FLUSH PRIVILEGES;
464 connection conn_execute;
465 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
466 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
467 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
468 DROP ROLE r2;
469 ERROR HY000: Operation DROP ROLE failed for 'r2'@'%'
470 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
471 connection conn_attempt;
472 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
473 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
474 connection default;
475 SET DEBUG_SYNC="RESET";
476 disconnect conn_attempt;
477 disconnect conn_execute;
478 # -------------------------------
479 # Case 2.8: GRANT privilege + FLUSH PRIVILEGES
480 connect conn_attempt, localhost, root,,,,;
481 SET @@lock_wait_timeout=1;
482 connect conn_execute, localhost, root,,,,;
483 connection conn_attempt;
484 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
485 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
486 FLUSH PRIVILEGES;
487 connection conn_execute;
488 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
489 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
490 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
491 GRANT SELECT ON *.* TO u2;
492 ERROR 42000: You are not allowed to create a user with GRANT
493 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
494 connection conn_attempt;
495 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
496 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
497 connection default;
498 SET DEBUG_SYNC="RESET";
499 disconnect conn_attempt;
500 disconnect conn_execute;
501 # -------------------------------
502 # Case 2.9: GRANT role + FLUSH PRIVILEGES
503 CREATE ROLE r1;
504 connect conn_attempt, localhost, root,,,,;
505 SET @@lock_wait_timeout=1;
506 connect conn_execute, localhost, root,,,,;
507 connection conn_attempt;
508 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
509 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
510 FLUSH PRIVILEGES;
511 connection conn_execute;
512 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
513 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
514 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
515 GRANT r1 TO u2;
516 ERROR HY000: Unknown authorization ID `u2`@`%`
517 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
518 connection conn_attempt;
519 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
520 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
521 connection default;
522 SET DEBUG_SYNC="RESET";
523 disconnect conn_attempt;
524 disconnect conn_execute;
525 DROP ROLE r1;
526 # -------------------------------
527 # Case 2.10: REVOKE role + FLUSH PRIVILEGES
528 CREATE ROLE r1;
529 connect conn_attempt, localhost, root,,,,;
530 SET @@lock_wait_timeout=1;
531 connect conn_execute, localhost, root,,,,;
532 connection conn_attempt;
533 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
534 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
535 FLUSH PRIVILEGES;
536 connection conn_execute;
537 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
538 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
539 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
540 REVOKE r1 FROM u2;
541 ERROR HY000: Unknown authorization ID `u2`@`%`
542 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
543 connection conn_attempt;
544 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
545 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
546 connection default;
547 SET DEBUG_SYNC="RESET";
548 disconnect conn_attempt;
549 disconnect conn_execute;
550 DROP ROLE r1;
551 # -------------------------------
552 # Case 2.11: REVOKE privilege + FLUSH PRIVILEGES
553 connect conn_attempt, localhost, root,,,,;
554 SET @@lock_wait_timeout=1;
555 connect conn_execute, localhost, root,,,,;
556 connection conn_attempt;
557 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
558 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
559 FLUSH PRIVILEGES;
560 connection conn_execute;
561 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
562 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
563 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
564 REVOKE SELECT ON *.* FROM u2;
565 ERROR 42000: There is no such grant defined for user 'u2' on host '%'
566 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
567 connection conn_attempt;
568 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
569 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
570 connection default;
571 SET DEBUG_SYNC="RESET";
572 disconnect conn_attempt;
573 disconnect conn_execute;
574 # -------------------------------
575 # Case 2.12: SET DEFAULT ROLE + FLUSH PRIVILEGES
576 CREATE ROLE r1;
577 connect conn_attempt, localhost, root,,,,;
578 SET @@lock_wait_timeout=1;
579 connect conn_execute, localhost, root,,,,;
580 connection conn_attempt;
581 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
582 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
583 FLUSH PRIVILEGES;
584 connection conn_execute;
585 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
586 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
587 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
588 SET DEFAULT ROLE r1 TO u2;
589 ERROR HY000: `r1`@`%` is not granted to `u2`@`%`
590 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
591 connection conn_attempt;
592 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
593 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
594 connection default;
595 SET DEBUG_SYNC="RESET";
596 disconnect conn_attempt;
597 disconnect conn_execute;
598 DROP ROLE r1;
599 # -----------------------------------------------------------------------
600 # Cleanup
601 # Restore initial values
602 SET @@global.transaction_isolation = @global_start_value;
603 SELECT @@global.transaction_isolation;
604 @@global.transaction_isolation
605 REPEATABLE-READ
606 SET @@session.transaction_isolation = @session_start_value;
607 SELECT @@session.transaction_isolation;
608 @@session.transaction_isolation
609 REPEATABLE-READ
610 # -----------------------------------------------------------------------
611 # Setup
612 # Saving initial values
613 SET @global_start_value = @@global.transaction_isolation;
614 SELECT @global_start_value;
615 @global_start_value
616 REPEATABLE-READ
617 SET @session_start_value = @@session.transaction_isolation;
618 SELECT @session_start_value;
619 @session_start_value
620 REPEATABLE-READ
621 # Setting transaction isolation to 'READ-COMMITTED'
622 SET GLOBAL transaction_isolation = 'READ-COMMITTED';
623 # -----------------------------------------------------------------------
624 # Case 1: Successful ACL DDLs + FLUSH PRIVILEGES
625 # -------------------------------
626 # Case 1.1: CREATE USER + FLUSH PRIVILEGES
627 connect conn_attempt, localhost, root,,,,;
628 SET @@lock_wait_timeout=1;
629 connect conn_execute, localhost, root,,,,;
630 connection conn_attempt;
631 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
632 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
633 FLUSH PRIVILEGES;
634 connection conn_execute;
635 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
636 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
637 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
638 CREATE USER u1;
639 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
640 connection conn_attempt;
641 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
642 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
643 connection default;
644 SET DEBUG_SYNC="RESET";
645 disconnect conn_attempt;
646 disconnect conn_execute;
647 DROP USER u1;
648 # -------------------------------
649 # Case 1.2: ALTER USER + FLUSH PRIVILEGES
650 CREATE USER u1;
651 connect conn_attempt, localhost, root,,,,;
652 SET @@lock_wait_timeout=1;
653 connect conn_execute, localhost, root,,,,;
654 connection conn_attempt;
655 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
656 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
657 FLUSH PRIVILEGES;
658 connection conn_execute;
659 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
660 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
661 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
662 ALTER USER u1 IDENTIFIED BY 'abcd';
663 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
664 connection conn_attempt;
665 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
666 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
667 connection default;
668 SET DEBUG_SYNC="RESET";
669 disconnect conn_attempt;
670 disconnect conn_execute;
671 DROP USER u1;
672 # -------------------------------
673 # Case 1.3: RENAME USER + FLUSH PRIVILEGES
674 CREATE USER u1;
675 connect conn_attempt, localhost, root,,,,;
676 SET @@lock_wait_timeout=1;
677 connect conn_execute, localhost, root,,,,;
678 connection conn_attempt;
679 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
680 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
681 FLUSH PRIVILEGES;
682 connection conn_execute;
683 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
684 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
685 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
686 RENAME USER u1 TO u2;
687 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
688 connection conn_attempt;
689 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
690 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
691 connection default;
692 SET DEBUG_SYNC="RESET";
693 disconnect conn_attempt;
694 disconnect conn_execute;
695 DROP USER u2;
696 # -------------------------------
697 # Case 1.4: CREATE ROLE + FLUSH PRIVILEGES
698 connect conn_attempt, localhost, root,,,,;
699 SET @@lock_wait_timeout=1;
700 connect conn_execute, localhost, root,,,,;
701 connection conn_attempt;
702 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
703 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
704 FLUSH PRIVILEGES;
705 connection conn_execute;
706 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
707 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
708 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
709 CREATE ROLE r1;
710 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
711 connection conn_attempt;
712 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
713 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
714 connection default;
715 SET DEBUG_SYNC="RESET";
716 disconnect conn_attempt;
717 disconnect conn_execute;
718 DROP ROLE r1;
719 # -------------------------------
720 # Case 1.5: GRANT privilege + FLUSH PRIVILEGES
721 CREATE USER u1;
722 connect conn_attempt, localhost, root,,,,;
723 SET @@lock_wait_timeout=1;
724 connect conn_execute, localhost, root,,,,;
725 connection conn_attempt;
726 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
727 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
728 FLUSH PRIVILEGES;
729 connection conn_execute;
730 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
731 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
732 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
733 GRANT SELECT ON *.* TO u1;
734 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
735 connection conn_attempt;
736 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
737 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
738 connection default;
739 SET DEBUG_SYNC="RESET";
740 disconnect conn_attempt;
741 disconnect conn_execute;
742 DROP USER u1;
743 # -------------------------------
744 # Case 1.6: GRANT role + FLUSH PRIVILEGES
745 CREATE USER u1;
746 CREATE ROLE r1;
747 connect conn_attempt, localhost, root,,,,;
748 SET @@lock_wait_timeout=1;
749 connect conn_execute, localhost, root,,,,;
750 connection conn_attempt;
751 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
752 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
753 FLUSH PRIVILEGES;
754 connection conn_execute;
755 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
756 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
757 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
758 GRANT r1 TO u1;
759 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
760 connection conn_attempt;
761 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
762 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
763 connection default;
764 SET DEBUG_SYNC="RESET";
765 disconnect conn_attempt;
766 disconnect conn_execute;
767 DROP USER r1;
768 DROP USER u1;
769 # -------------------------------
770 # Case 1.7: SET DEFAULT ROLE + FLUSH PRIVILEGES
771 CREATE USER u1;
772 CREATE ROLE r1;
773 GRANT r1 TO u1;
774 connect conn_attempt, localhost, root,,,,;
775 SET @@lock_wait_timeout=1;
776 connect conn_execute, localhost, root,,,,;
777 connection conn_attempt;
778 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
779 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
780 FLUSH PRIVILEGES;
781 connection conn_execute;
782 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
783 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
784 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
785 SET DEFAULT ROLE r1 TO u1;
786 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
787 connection conn_attempt;
788 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
789 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
790 connection default;
791 SET DEBUG_SYNC="RESET";
792 disconnect conn_attempt;
793 disconnect conn_execute;
794 DROP USER r1;
795 DROP USER u1;
796 # -------------------------------
797 # Case 1.8: SET PASSWORD + FLUSH PRIVILEGES
798 CREATE USER u1;
799 connect conn_attempt, localhost, root,,,,;
800 SET @@lock_wait_timeout=1;
801 connect conn_execute, localhost, root,,,,;
802 connection conn_attempt;
803 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
804 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
805 FLUSH PRIVILEGES;
806 connection conn_execute;
807 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
808 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
809 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
810 SET PASSWORD FOR u1 = 'efgh';
811 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
812 connection conn_attempt;
813 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
814 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
815 connection default;
816 SET DEBUG_SYNC="RESET";
817 disconnect conn_attempt;
818 disconnect conn_execute;
819 DROP USER u1;
820 # -------------------------------
821 # Case 1.9: REVOKE role + FLUSH PRIVILEGES
822 CREATE USER u1;
823 CREATE ROLE r1;
824 GRANT r1 TO u1;
825 connect conn_attempt, localhost, root,,,,;
826 SET @@lock_wait_timeout=1;
827 connect conn_execute, localhost, root,,,,;
828 connection conn_attempt;
829 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
830 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
831 FLUSH PRIVILEGES;
832 connection conn_execute;
833 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
834 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
835 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
836 REVOKE r1 FROM u1;
837 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
838 connection conn_attempt;
839 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
840 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
841 connection default;
842 SET DEBUG_SYNC="RESET";
843 disconnect conn_attempt;
844 disconnect conn_execute;
845 DROP ROLE r1;
846 DROP USER u1;
847 # -------------------------------
848 # Case 1.10: REVOKE privilege + FLUSH PRIVILEGES
849 CREATE USER u1;
850 connect conn_attempt, localhost, root,,,,;
851 SET @@lock_wait_timeout=1;
852 connect conn_execute, localhost, root,,,,;
853 connection conn_attempt;
854 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
855 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
856 FLUSH PRIVILEGES;
857 connection conn_execute;
858 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
859 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
860 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
861 REVOKE SELECT ON *.* FROM u1;
862 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
863 connection conn_attempt;
864 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
865 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
866 connection default;
867 SET DEBUG_SYNC="RESET";
868 disconnect conn_attempt;
869 disconnect conn_execute;
870 DROP USER u1;
871 # -------------------------------
872 # Case 1.11: DROP ROLE + FLUSH PRIVILEGES
873 CREATE ROLE r1;
874 connect conn_attempt, localhost, root,,,,;
875 SET @@lock_wait_timeout=1;
876 connect conn_execute, localhost, root,,,,;
877 connection conn_attempt;
878 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
879 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
880 FLUSH PRIVILEGES;
881 connection conn_execute;
882 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
883 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
884 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
885 DROP ROLE r1;
886 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
887 connection conn_attempt;
888 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
889 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
890 connection default;
891 SET DEBUG_SYNC="RESET";
892 disconnect conn_attempt;
893 disconnect conn_execute;
894 # -------------------------------
895 # Case 1.12: DROP USER + FLUSH PRIVILEGES
896 CREATE USER u1;
897 connect conn_attempt, localhost, root,,,,;
898 SET @@lock_wait_timeout=1;
899 connect conn_execute, localhost, root,,,,;
900 connection conn_attempt;
901 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
902 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
903 FLUSH PRIVILEGES;
904 connection conn_execute;
905 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
906 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
907 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
908 DROP USER u1;
909 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
910 connection conn_attempt;
911 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
912 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
913 connection default;
914 SET DEBUG_SYNC="RESET";
915 disconnect conn_attempt;
916 disconnect conn_execute;
917 # -----------------------------------------------------------------------
918 # Case 2: Unsuccessful ACL DDLs + FLUSH PRIVILEGES
919 SET @@lock_wait_timeout=1;
920 # -------------------------------
921 # Case 2.1: CREATE USER + FLUSH PRIVILEGES
922 CREATE USER u1;
923 connect conn_attempt, localhost, root,,,,;
924 SET @@lock_wait_timeout=1;
925 connect conn_execute, localhost, root,,,,;
926 connection conn_attempt;
927 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
928 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
929 FLUSH PRIVILEGES;
930 connection conn_execute;
931 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
932 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
933 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
934 CREATE USER u3, u2, u1;
935 ERROR HY000: Operation CREATE USER failed for 'u1'@'%'
936 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
937 connection conn_attempt;
938 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
939 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
940 connection default;
941 SET DEBUG_SYNC="RESET";
942 disconnect conn_attempt;
943 disconnect conn_execute;
944 DROP USER u1;
945 # -------------------------------
946 # Case 2.2: ALTER USER + FLUSH PRIVILEGES
947 connect conn_attempt, localhost, root,,,,;
948 SET @@lock_wait_timeout=1;
949 connect conn_execute, localhost, root,,,,;
950 connection conn_attempt;
951 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
952 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
953 FLUSH PRIVILEGES;
954 connection conn_execute;
955 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
956 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
957 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
958 ALTER USER u2 IDENTIFIED BY 'abcd';
959 ERROR HY000: Operation ALTER USER failed for 'u2'@'%'
960 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
961 connection conn_attempt;
962 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
963 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
964 connection default;
965 SET DEBUG_SYNC="RESET";
966 disconnect conn_attempt;
967 disconnect conn_execute;
968 # -------------------------------
969 # Case 2.3: RENAME USER + FLUSH PRIVILEGES
970 connect conn_attempt, localhost, root,,,,;
971 SET @@lock_wait_timeout=1;
972 connect conn_execute, localhost, root,,,,;
973 connection conn_attempt;
974 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
975 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
976 FLUSH PRIVILEGES;
977 connection conn_execute;
978 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
979 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
980 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
981 RENAME USER u2 TO u3;
982 ERROR HY000: Operation RENAME USER failed for 'u2'@'%'
983 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
984 connection conn_attempt;
985 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
986 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
987 connection default;
988 SET DEBUG_SYNC="RESET";
989 disconnect conn_attempt;
990 disconnect conn_execute;
991 # -------------------------------
992 # Case 2.4: DROP USER + FLUSH PRIVILEGES
993 connect conn_attempt, localhost, root,,,,;
994 SET @@lock_wait_timeout=1;
995 connect conn_execute, localhost, root,,,,;
996 connection conn_attempt;
997 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
998 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
999 FLUSH PRIVILEGES;
1000 connection conn_execute;
1001 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1002 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1003 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1004 DROP USER u2;
1005 ERROR HY000: Operation DROP USER failed for 'u2'@'%'
1006 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1007 connection conn_attempt;
1008 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1009 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1010 connection default;
1011 SET DEBUG_SYNC="RESET";
1012 disconnect conn_attempt;
1013 disconnect conn_execute;
1014 # -------------------------------
1015 # Case 2.5: SET PASSWORD + FLUSH PRIVILEGES
1016 CREATE USER u1;
1017 connect conn_attempt, localhost, root,,,,;
1018 SET @@lock_wait_timeout=1;
1019 connect conn_execute, localhost, root,,,,;
1020 connection conn_attempt;
1021 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1022 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1023 FLUSH PRIVILEGES;
1024 connection conn_execute;
1025 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout, wl14084_simulate_set_password_failure";
1026 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1027 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1028 SET PASSWORD FOR u1 = 'abcd';
1029 ERROR 42000: Can't find any matching row in the user table
1030 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout, wl14084_simulate_set_password_failure";
1031 connection conn_attempt;
1032 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1033 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1034 connection default;
1035 SET DEBUG_SYNC="RESET";
1036 disconnect conn_attempt;
1037 disconnect conn_execute;
1038 DROP USER u1;
1039 # -------------------------------
1040 # Case 2.6: CREATE ROLE + FLUSH PRIVILEGES
1041 CREATE ROLE r1;
1042 connect conn_attempt, localhost, root,,,,;
1043 SET @@lock_wait_timeout=1;
1044 connect conn_execute, localhost, root,,,,;
1045 connection conn_attempt;
1046 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1047 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1048 FLUSH PRIVILEGES;
1049 connection conn_execute;
1050 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1051 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1052 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1053 CREATE ROLE r3, r2, r1;
1054 ERROR HY000: Operation CREATE ROLE failed for 'r1'@'%'
1055 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1056 connection conn_attempt;
1057 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1058 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1059 connection default;
1060 SET DEBUG_SYNC="RESET";
1061 disconnect conn_attempt;
1062 disconnect conn_execute;
1063 DROP ROLE r1;
1064 # -------------------------------
1065 # Case 2.7: DROP ROLE + FLUSH PRIVILEGES
1066 connect conn_attempt, localhost, root,,,,;
1067 SET @@lock_wait_timeout=1;
1068 connect conn_execute, localhost, root,,,,;
1069 connection conn_attempt;
1070 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1071 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1072 FLUSH PRIVILEGES;
1073 connection conn_execute;
1074 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1075 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1076 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1077 DROP ROLE r2;
1078 ERROR HY000: Operation DROP ROLE failed for 'r2'@'%'
1079 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1080 connection conn_attempt;
1081 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1082 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1083 connection default;
1084 SET DEBUG_SYNC="RESET";
1085 disconnect conn_attempt;
1086 disconnect conn_execute;
1087 # -------------------------------
1088 # Case 2.8: GRANT privilege + FLUSH PRIVILEGES
1089 connect conn_attempt, localhost, root,,,,;
1090 SET @@lock_wait_timeout=1;
1091 connect conn_execute, localhost, root,,,,;
1092 connection conn_attempt;
1093 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1094 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1095 FLUSH PRIVILEGES;
1096 connection conn_execute;
1097 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1098 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1099 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1100 GRANT SELECT ON *.* TO u2;
1101 ERROR 42000: You are not allowed to create a user with GRANT
1102 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1103 connection conn_attempt;
1104 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1105 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1106 connection default;
1107 SET DEBUG_SYNC="RESET";
1108 disconnect conn_attempt;
1109 disconnect conn_execute;
1110 # -------------------------------
1111 # Case 2.9: GRANT role + FLUSH PRIVILEGES
1112 CREATE ROLE r1;
1113 connect conn_attempt, localhost, root,,,,;
1114 SET @@lock_wait_timeout=1;
1115 connect conn_execute, localhost, root,,,,;
1116 connection conn_attempt;
1117 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1118 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1119 FLUSH PRIVILEGES;
1120 connection conn_execute;
1121 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1122 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1123 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1124 GRANT r1 TO u2;
1125 ERROR HY000: Unknown authorization ID `u2`@`%`
1126 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1127 connection conn_attempt;
1128 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1129 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1130 connection default;
1131 SET DEBUG_SYNC="RESET";
1132 disconnect conn_attempt;
1133 disconnect conn_execute;
1134 DROP ROLE r1;
1135 # -------------------------------
1136 # Case 2.10: REVOKE role + FLUSH PRIVILEGES
1137 CREATE ROLE r1;
1138 connect conn_attempt, localhost, root,,,,;
1139 SET @@lock_wait_timeout=1;
1140 connect conn_execute, localhost, root,,,,;
1141 connection conn_attempt;
1142 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1143 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1144 FLUSH PRIVILEGES;
1145 connection conn_execute;
1146 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1147 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1148 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1149 REVOKE r1 FROM u2;
1150 ERROR HY000: Unknown authorization ID `u2`@`%`
1151 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1152 connection conn_attempt;
1153 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1154 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1155 connection default;
1156 SET DEBUG_SYNC="RESET";
1157 disconnect conn_attempt;
1158 disconnect conn_execute;
1159 DROP ROLE r1;
1160 # -------------------------------
1161 # Case 2.11: REVOKE privilege + FLUSH PRIVILEGES
1162 connect conn_attempt, localhost, root,,,,;
1163 SET @@lock_wait_timeout=1;
1164 connect conn_execute, localhost, root,,,,;
1165 connection conn_attempt;
1166 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1167 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1168 FLUSH PRIVILEGES;
1169 connection conn_execute;
1170 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1171 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1172 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1173 REVOKE SELECT ON *.* FROM u2;
1174 ERROR 42000: There is no such grant defined for user 'u2' on host '%'
1175 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1176 connection conn_attempt;
1177 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1178 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1179 connection default;
1180 SET DEBUG_SYNC="RESET";
1181 disconnect conn_attempt;
1182 disconnect conn_execute;
1183 # -------------------------------
1184 # Case 2.12: SET DEFAULT ROLE + FLUSH PRIVILEGES
1185 CREATE ROLE r1;
1186 connect conn_attempt, localhost, root,,,,;
1187 SET @@lock_wait_timeout=1;
1188 connect conn_execute, localhost, root,,,,;
1189 connection conn_attempt;
1190 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1191 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1192 FLUSH PRIVILEGES;
1193 connection conn_execute;
1194 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1195 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1196 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1197 SET DEFAULT ROLE r1 TO u2;
1198 ERROR HY000: `r1`@`%` is not granted to `u2`@`%`
1199 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1200 connection conn_attempt;
1201 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1202 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1203 connection default;
1204 SET DEBUG_SYNC="RESET";
1205 disconnect conn_attempt;
1206 disconnect conn_execute;
1207 DROP ROLE r1;
1208 # -----------------------------------------------------------------------
1209 # Cleanup
1210 # Restore initial values
1211 SET @@global.transaction_isolation = @global_start_value;
1212 SELECT @@global.transaction_isolation;
1213 @@global.transaction_isolation
1214 REPEATABLE-READ
1215 SET @@session.transaction_isolation = @session_start_value;
1216 SELECT @@session.transaction_isolation;
1217 @@session.transaction_isolation
1218 REPEATABLE-READ
1219 # -----------------------------------------------------------------------
1220 # Setup
1221 # Saving initial values
1222 SET @global_start_value = @@global.transaction_isolation;
1223 SELECT @global_start_value;
1224 @global_start_value
1225 REPEATABLE-READ
1226 SET @session_start_value = @@session.transaction_isolation;
1227 SELECT @session_start_value;
1228 @session_start_value
1229 REPEATABLE-READ
1230 # Setting transaction isolation to 'REPEATABLE-READ'
1231 SET GLOBAL transaction_isolation = 'REPEATABLE-READ';
1232 # -----------------------------------------------------------------------
1233 # Case 1: Successful ACL DDLs + FLUSH PRIVILEGES
1234 # -------------------------------
1235 # Case 1.1: CREATE USER + FLUSH PRIVILEGES
1236 connect conn_attempt, localhost, root,,,,;
1237 SET @@lock_wait_timeout=1;
1238 connect conn_execute, localhost, root,,,,;
1239 connection conn_attempt;
1240 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1241 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1242 FLUSH PRIVILEGES;
1243 connection conn_execute;
1244 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1245 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1246 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1247 CREATE USER u1;
1248 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1249 connection conn_attempt;
1250 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1251 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1252 connection default;
1253 SET DEBUG_SYNC="RESET";
1254 disconnect conn_attempt;
1255 disconnect conn_execute;
1256 DROP USER u1;
1257 # -------------------------------
1258 # Case 1.2: ALTER USER + FLUSH PRIVILEGES
1259 CREATE USER u1;
1260 connect conn_attempt, localhost, root,,,,;
1261 SET @@lock_wait_timeout=1;
1262 connect conn_execute, localhost, root,,,,;
1263 connection conn_attempt;
1264 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1265 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1266 FLUSH PRIVILEGES;
1267 connection conn_execute;
1268 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1269 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1270 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1271 ALTER USER u1 IDENTIFIED BY 'abcd';
1272 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1273 connection conn_attempt;
1274 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1275 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1276 connection default;
1277 SET DEBUG_SYNC="RESET";
1278 disconnect conn_attempt;
1279 disconnect conn_execute;
1280 DROP USER u1;
1281 # -------------------------------
1282 # Case 1.3: RENAME USER + FLUSH PRIVILEGES
1283 CREATE USER u1;
1284 connect conn_attempt, localhost, root,,,,;
1285 SET @@lock_wait_timeout=1;
1286 connect conn_execute, localhost, root,,,,;
1287 connection conn_attempt;
1288 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1289 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1290 FLUSH PRIVILEGES;
1291 connection conn_execute;
1292 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1293 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1294 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1295 RENAME USER u1 TO u2;
1296 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1297 connection conn_attempt;
1298 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1299 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1300 connection default;
1301 SET DEBUG_SYNC="RESET";
1302 disconnect conn_attempt;
1303 disconnect conn_execute;
1304 DROP USER u2;
1305 # -------------------------------
1306 # Case 1.4: CREATE ROLE + FLUSH PRIVILEGES
1307 connect conn_attempt, localhost, root,,,,;
1308 SET @@lock_wait_timeout=1;
1309 connect conn_execute, localhost, root,,,,;
1310 connection conn_attempt;
1311 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1312 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1313 FLUSH PRIVILEGES;
1314 connection conn_execute;
1315 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1316 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1317 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1318 CREATE ROLE r1;
1319 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1320 connection conn_attempt;
1321 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1322 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1323 connection default;
1324 SET DEBUG_SYNC="RESET";
1325 disconnect conn_attempt;
1326 disconnect conn_execute;
1327 DROP ROLE r1;
1328 # -------------------------------
1329 # Case 1.5: GRANT privilege + FLUSH PRIVILEGES
1330 CREATE USER u1;
1331 connect conn_attempt, localhost, root,,,,;
1332 SET @@lock_wait_timeout=1;
1333 connect conn_execute, localhost, root,,,,;
1334 connection conn_attempt;
1335 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1336 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1337 FLUSH PRIVILEGES;
1338 connection conn_execute;
1339 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1340 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1341 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1342 GRANT SELECT ON *.* TO u1;
1343 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1344 connection conn_attempt;
1345 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1346 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1347 connection default;
1348 SET DEBUG_SYNC="RESET";
1349 disconnect conn_attempt;
1350 disconnect conn_execute;
1351 DROP USER u1;
1352 # -------------------------------
1353 # Case 1.6: GRANT role + FLUSH PRIVILEGES
1354 CREATE USER u1;
1355 CREATE ROLE r1;
1356 connect conn_attempt, localhost, root,,,,;
1357 SET @@lock_wait_timeout=1;
1358 connect conn_execute, localhost, root,,,,;
1359 connection conn_attempt;
1360 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1361 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1362 FLUSH PRIVILEGES;
1363 connection conn_execute;
1364 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1365 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1366 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1367 GRANT r1 TO u1;
1368 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1369 connection conn_attempt;
1370 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1371 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1372 connection default;
1373 SET DEBUG_SYNC="RESET";
1374 disconnect conn_attempt;
1375 disconnect conn_execute;
1376 DROP USER r1;
1377 DROP USER u1;
1378 # -------------------------------
1379 # Case 1.7: SET DEFAULT ROLE + FLUSH PRIVILEGES
1380 CREATE USER u1;
1381 CREATE ROLE r1;
1382 GRANT r1 TO u1;
1383 connect conn_attempt, localhost, root,,,,;
1384 SET @@lock_wait_timeout=1;
1385 connect conn_execute, localhost, root,,,,;
1386 connection conn_attempt;
1387 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1388 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1389 FLUSH PRIVILEGES;
1390 connection conn_execute;
1391 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1392 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1393 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1394 SET DEFAULT ROLE r1 TO u1;
1395 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1396 connection conn_attempt;
1397 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1398 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1399 connection default;
1400 SET DEBUG_SYNC="RESET";
1401 disconnect conn_attempt;
1402 disconnect conn_execute;
1403 DROP USER r1;
1404 DROP USER u1;
1405 # -------------------------------
1406 # Case 1.8: SET PASSWORD + FLUSH PRIVILEGES
1407 CREATE USER u1;
1408 connect conn_attempt, localhost, root,,,,;
1409 SET @@lock_wait_timeout=1;
1410 connect conn_execute, localhost, root,,,,;
1411 connection conn_attempt;
1412 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1413 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1414 FLUSH PRIVILEGES;
1415 connection conn_execute;
1416 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1417 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1418 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1419 SET PASSWORD FOR u1 = 'efgh';
1420 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1421 connection conn_attempt;
1422 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1423 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1424 connection default;
1425 SET DEBUG_SYNC="RESET";
1426 disconnect conn_attempt;
1427 disconnect conn_execute;
1428 DROP USER u1;
1429 # -------------------------------
1430 # Case 1.9: REVOKE role + FLUSH PRIVILEGES
1431 CREATE USER u1;
1432 CREATE ROLE r1;
1433 GRANT r1 TO u1;
1434 connect conn_attempt, localhost, root,,,,;
1435 SET @@lock_wait_timeout=1;
1436 connect conn_execute, localhost, root,,,,;
1437 connection conn_attempt;
1438 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1439 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1440 FLUSH PRIVILEGES;
1441 connection conn_execute;
1442 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1443 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1444 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1445 REVOKE r1 FROM u1;
1446 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1447 connection conn_attempt;
1448 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1449 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1450 connection default;
1451 SET DEBUG_SYNC="RESET";
1452 disconnect conn_attempt;
1453 disconnect conn_execute;
1454 DROP ROLE r1;
1455 DROP USER u1;
1456 # -------------------------------
1457 # Case 1.10: REVOKE privilege + FLUSH PRIVILEGES
1458 CREATE USER u1;
1459 connect conn_attempt, localhost, root,,,,;
1460 SET @@lock_wait_timeout=1;
1461 connect conn_execute, localhost, root,,,,;
1462 connection conn_attempt;
1463 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1464 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1465 FLUSH PRIVILEGES;
1466 connection conn_execute;
1467 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1468 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1469 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1470 REVOKE SELECT ON *.* FROM u1;
1471 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1472 connection conn_attempt;
1473 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1474 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1475 connection default;
1476 SET DEBUG_SYNC="RESET";
1477 disconnect conn_attempt;
1478 disconnect conn_execute;
1479 DROP USER u1;
1480 # -------------------------------
1481 # Case 1.11: DROP ROLE + FLUSH PRIVILEGES
1482 CREATE ROLE r1;
1483 connect conn_attempt, localhost, root,,,,;
1484 SET @@lock_wait_timeout=1;
1485 connect conn_execute, localhost, root,,,,;
1486 connection conn_attempt;
1487 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1488 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1489 FLUSH PRIVILEGES;
1490 connection conn_execute;
1491 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1492 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1493 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1494 DROP ROLE r1;
1495 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1496 connection conn_attempt;
1497 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1498 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1499 connection default;
1500 SET DEBUG_SYNC="RESET";
1501 disconnect conn_attempt;
1502 disconnect conn_execute;
1503 # -------------------------------
1504 # Case 1.12: DROP USER + FLUSH PRIVILEGES
1505 CREATE USER u1;
1506 connect conn_attempt, localhost, root,,,,;
1507 SET @@lock_wait_timeout=1;
1508 connect conn_execute, localhost, root,,,,;
1509 connection conn_attempt;
1510 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1511 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1512 FLUSH PRIVILEGES;
1513 connection conn_execute;
1514 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1515 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1516 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1517 DROP USER u1;
1518 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1519 connection conn_attempt;
1520 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1521 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1522 connection default;
1523 SET DEBUG_SYNC="RESET";
1524 disconnect conn_attempt;
1525 disconnect conn_execute;
1526 # -----------------------------------------------------------------------
1527 # Case 2: Unsuccessful ACL DDLs + FLUSH PRIVILEGES
1528 SET @@lock_wait_timeout=1;
1529 # -------------------------------
1530 # Case 2.1: CREATE USER + FLUSH PRIVILEGES
1531 CREATE USER u1;
1532 connect conn_attempt, localhost, root,,,,;
1533 SET @@lock_wait_timeout=1;
1534 connect conn_execute, localhost, root,,,,;
1535 connection conn_attempt;
1536 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1537 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1538 FLUSH PRIVILEGES;
1539 connection conn_execute;
1540 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1541 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1542 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1543 CREATE USER u3, u2, u1;
1544 ERROR HY000: Operation CREATE USER failed for 'u1'@'%'
1545 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1546 connection conn_attempt;
1547 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1548 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1549 connection default;
1550 SET DEBUG_SYNC="RESET";
1551 disconnect conn_attempt;
1552 disconnect conn_execute;
1553 DROP USER u1;
1554 # -------------------------------
1555 # Case 2.2: ALTER USER + FLUSH PRIVILEGES
1556 connect conn_attempt, localhost, root,,,,;
1557 SET @@lock_wait_timeout=1;
1558 connect conn_execute, localhost, root,,,,;
1559 connection conn_attempt;
1560 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1561 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1562 FLUSH PRIVILEGES;
1563 connection conn_execute;
1564 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1565 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1566 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1567 ALTER USER u2 IDENTIFIED BY 'abcd';
1568 ERROR HY000: Operation ALTER USER failed for 'u2'@'%'
1569 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1570 connection conn_attempt;
1571 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1572 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1573 connection default;
1574 SET DEBUG_SYNC="RESET";
1575 disconnect conn_attempt;
1576 disconnect conn_execute;
1577 # -------------------------------
1578 # Case 2.3: RENAME USER + FLUSH PRIVILEGES
1579 connect conn_attempt, localhost, root,,,,;
1580 SET @@lock_wait_timeout=1;
1581 connect conn_execute, localhost, root,,,,;
1582 connection conn_attempt;
1583 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1584 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1585 FLUSH PRIVILEGES;
1586 connection conn_execute;
1587 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1588 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1589 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1590 RENAME USER u2 TO u3;
1591 ERROR HY000: Operation RENAME USER failed for 'u2'@'%'
1592 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1593 connection conn_attempt;
1594 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1595 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1596 connection default;
1597 SET DEBUG_SYNC="RESET";
1598 disconnect conn_attempt;
1599 disconnect conn_execute;
1600 # -------------------------------
1601 # Case 2.4: DROP USER + FLUSH PRIVILEGES
1602 connect conn_attempt, localhost, root,,,,;
1603 SET @@lock_wait_timeout=1;
1604 connect conn_execute, localhost, root,,,,;
1605 connection conn_attempt;
1606 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1607 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1608 FLUSH PRIVILEGES;
1609 connection conn_execute;
1610 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1611 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1612 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1613 DROP USER u2;
1614 ERROR HY000: Operation DROP USER failed for 'u2'@'%'
1615 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1616 connection conn_attempt;
1617 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1618 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1619 connection default;
1620 SET DEBUG_SYNC="RESET";
1621 disconnect conn_attempt;
1622 disconnect conn_execute;
1623 # -------------------------------
1624 # Case 2.5: SET PASSWORD + FLUSH PRIVILEGES
1625 CREATE USER u1;
1626 connect conn_attempt, localhost, root,,,,;
1627 SET @@lock_wait_timeout=1;
1628 connect conn_execute, localhost, root,,,,;
1629 connection conn_attempt;
1630 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1631 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1632 FLUSH PRIVILEGES;
1633 connection conn_execute;
1634 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout, wl14084_simulate_set_password_failure";
1635 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1636 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1637 SET PASSWORD FOR u1 = 'abcd';
1638 ERROR 42000: Can't find any matching row in the user table
1639 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout, wl14084_simulate_set_password_failure";
1640 connection conn_attempt;
1641 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1642 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1643 connection default;
1644 SET DEBUG_SYNC="RESET";
1645 disconnect conn_attempt;
1646 disconnect conn_execute;
1647 DROP USER u1;
1648 # -------------------------------
1649 # Case 2.6: CREATE ROLE + FLUSH PRIVILEGES
1650 CREATE ROLE r1;
1651 connect conn_attempt, localhost, root,,,,;
1652 SET @@lock_wait_timeout=1;
1653 connect conn_execute, localhost, root,,,,;
1654 connection conn_attempt;
1655 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1656 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1657 FLUSH PRIVILEGES;
1658 connection conn_execute;
1659 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1660 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1661 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1662 CREATE ROLE r3, r2, r1;
1663 ERROR HY000: Operation CREATE ROLE failed for 'r1'@'%'
1664 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1665 connection conn_attempt;
1666 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1667 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1668 connection default;
1669 SET DEBUG_SYNC="RESET";
1670 disconnect conn_attempt;
1671 disconnect conn_execute;
1672 DROP ROLE r1;
1673 # -------------------------------
1674 # Case 2.7: DROP ROLE + FLUSH PRIVILEGES
1675 connect conn_attempt, localhost, root,,,,;
1676 SET @@lock_wait_timeout=1;
1677 connect conn_execute, localhost, root,,,,;
1678 connection conn_attempt;
1679 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1680 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1681 FLUSH PRIVILEGES;
1682 connection conn_execute;
1683 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1684 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1685 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1686 DROP ROLE r2;
1687 ERROR HY000: Operation DROP ROLE failed for 'r2'@'%'
1688 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1689 connection conn_attempt;
1690 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1691 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1692 connection default;
1693 SET DEBUG_SYNC="RESET";
1694 disconnect conn_attempt;
1695 disconnect conn_execute;
1696 # -------------------------------
1697 # Case 2.8: GRANT privilege + FLUSH PRIVILEGES
1698 connect conn_attempt, localhost, root,,,,;
1699 SET @@lock_wait_timeout=1;
1700 connect conn_execute, localhost, root,,,,;
1701 connection conn_attempt;
1702 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1703 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1704 FLUSH PRIVILEGES;
1705 connection conn_execute;
1706 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1707 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1708 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1709 GRANT SELECT ON *.* TO u2;
1710 ERROR 42000: You are not allowed to create a user with GRANT
1711 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1712 connection conn_attempt;
1713 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1714 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1715 connection default;
1716 SET DEBUG_SYNC="RESET";
1717 disconnect conn_attempt;
1718 disconnect conn_execute;
1719 # -------------------------------
1720 # Case 2.9: GRANT role + FLUSH PRIVILEGES
1721 CREATE ROLE r1;
1722 connect conn_attempt, localhost, root,,,,;
1723 SET @@lock_wait_timeout=1;
1724 connect conn_execute, localhost, root,,,,;
1725 connection conn_attempt;
1726 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1727 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1728 FLUSH PRIVILEGES;
1729 connection conn_execute;
1730 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1731 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1732 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1733 GRANT r1 TO u2;
1734 ERROR HY000: Unknown authorization ID `u2`@`%`
1735 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1736 connection conn_attempt;
1737 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1738 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1739 connection default;
1740 SET DEBUG_SYNC="RESET";
1741 disconnect conn_attempt;
1742 disconnect conn_execute;
1743 DROP ROLE r1;
1744 # -------------------------------
1745 # Case 2.10: REVOKE role + FLUSH PRIVILEGES
1746 CREATE ROLE r1;
1747 connect conn_attempt, localhost, root,,,,;
1748 SET @@lock_wait_timeout=1;
1749 connect conn_execute, localhost, root,,,,;
1750 connection conn_attempt;
1751 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1752 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1753 FLUSH PRIVILEGES;
1754 connection conn_execute;
1755 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1756 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1757 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1758 REVOKE r1 FROM u2;
1759 ERROR HY000: Unknown authorization ID `u2`@`%`
1760 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1761 connection conn_attempt;
1762 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1763 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1764 connection default;
1765 SET DEBUG_SYNC="RESET";
1766 disconnect conn_attempt;
1767 disconnect conn_execute;
1768 DROP ROLE r1;
1769 # -------------------------------
1770 # Case 2.11: REVOKE privilege + FLUSH PRIVILEGES
1771 connect conn_attempt, localhost, root,,,,;
1772 SET @@lock_wait_timeout=1;
1773 connect conn_execute, localhost, root,,,,;
1774 connection conn_attempt;
1775 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1776 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1777 FLUSH PRIVILEGES;
1778 connection conn_execute;
1779 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1780 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1781 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1782 REVOKE SELECT ON *.* FROM u2;
1783 ERROR 42000: There is no such grant defined for user 'u2' on host '%'
1784 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1785 connection conn_attempt;
1786 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1787 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1788 connection default;
1789 SET DEBUG_SYNC="RESET";
1790 disconnect conn_attempt;
1791 disconnect conn_execute;
1792 # -------------------------------
1793 # Case 2.12: SET DEFAULT ROLE + FLUSH PRIVILEGES
1794 CREATE ROLE r1;
1795 connect conn_attempt, localhost, root,,,,;
1796 SET @@lock_wait_timeout=1;
1797 connect conn_execute, localhost, root,,,,;
1798 connection conn_attempt;
1799 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1800 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1801 FLUSH PRIVILEGES;
1802 connection conn_execute;
1803 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1804 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1805 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1806 SET DEFAULT ROLE r1 TO u2;
1807 ERROR HY000: `r1`@`%` is not granted to `u2`@`%`
1808 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
1809 connection conn_attempt;
1810 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1811 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1812 connection default;
1813 SET DEBUG_SYNC="RESET";
1814 disconnect conn_attempt;
1815 disconnect conn_execute;
1816 DROP ROLE r1;
1817 # -----------------------------------------------------------------------
1818 # Cleanup
1819 # Restore initial values
1820 SET @@global.transaction_isolation = @global_start_value;
1821 SELECT @@global.transaction_isolation;
1822 @@global.transaction_isolation
1823 REPEATABLE-READ
1824 SET @@session.transaction_isolation = @session_start_value;
1825 SELECT @@session.transaction_isolation;
1826 @@session.transaction_isolation
1827 REPEATABLE-READ
1828 # -----------------------------------------------------------------------
1829 # Setup
1830 # Saving initial values
1831 SET @global_start_value = @@global.transaction_isolation;
1832 SELECT @global_start_value;
1833 @global_start_value
1834 REPEATABLE-READ
1835 SET @session_start_value = @@session.transaction_isolation;
1836 SELECT @session_start_value;
1837 @session_start_value
1838 REPEATABLE-READ
1839 # Setting transaction isolation to 'SERIALIZABLE'
1840 SET GLOBAL transaction_isolation = 'SERIALIZABLE';
1841 # -----------------------------------------------------------------------
1842 # Case 1: Successful ACL DDLs + FLUSH PRIVILEGES
1843 # -------------------------------
1844 # Case 1.1: CREATE USER + FLUSH PRIVILEGES
1845 connect conn_attempt, localhost, root,,,,;
1846 SET @@lock_wait_timeout=1;
1847 connect conn_execute, localhost, root,,,,;
1848 connection conn_attempt;
1849 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1850 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1851 FLUSH PRIVILEGES;
1852 connection conn_execute;
1853 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1854 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1855 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1856 CREATE USER u1;
1857 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1858 connection conn_attempt;
1859 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1860 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1861 connection default;
1862 SET DEBUG_SYNC="RESET";
1863 disconnect conn_attempt;
1864 disconnect conn_execute;
1865 DROP USER u1;
1866 # -------------------------------
1867 # Case 1.2: ALTER USER + FLUSH PRIVILEGES
1868 CREATE USER u1;
1869 connect conn_attempt, localhost, root,,,,;
1870 SET @@lock_wait_timeout=1;
1871 connect conn_execute, localhost, root,,,,;
1872 connection conn_attempt;
1873 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1874 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1875 FLUSH PRIVILEGES;
1876 connection conn_execute;
1877 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1878 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1879 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1880 ALTER USER u1 IDENTIFIED BY 'abcd';
1881 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1882 connection conn_attempt;
1883 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1884 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1885 connection default;
1886 SET DEBUG_SYNC="RESET";
1887 disconnect conn_attempt;
1888 disconnect conn_execute;
1889 DROP USER u1;
1890 # -------------------------------
1891 # Case 1.3: RENAME USER + FLUSH PRIVILEGES
1892 CREATE USER u1;
1893 connect conn_attempt, localhost, root,,,,;
1894 SET @@lock_wait_timeout=1;
1895 connect conn_execute, localhost, root,,,,;
1896 connection conn_attempt;
1897 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1898 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1899 FLUSH PRIVILEGES;
1900 connection conn_execute;
1901 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1902 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1903 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1904 RENAME USER u1 TO u2;
1905 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1906 connection conn_attempt;
1907 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1908 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1909 connection default;
1910 SET DEBUG_SYNC="RESET";
1911 disconnect conn_attempt;
1912 disconnect conn_execute;
1913 DROP USER u2;
1914 # -------------------------------
1915 # Case 1.4: CREATE ROLE + FLUSH PRIVILEGES
1916 connect conn_attempt, localhost, root,,,,;
1917 SET @@lock_wait_timeout=1;
1918 connect conn_execute, localhost, root,,,,;
1919 connection conn_attempt;
1920 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1921 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1922 FLUSH PRIVILEGES;
1923 connection conn_execute;
1924 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1925 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1926 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1927 CREATE ROLE r1;
1928 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1929 connection conn_attempt;
1930 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1931 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1932 connection default;
1933 SET DEBUG_SYNC="RESET";
1934 disconnect conn_attempt;
1935 disconnect conn_execute;
1936 DROP ROLE r1;
1937 # -------------------------------
1938 # Case 1.5: GRANT privilege + FLUSH PRIVILEGES
1939 CREATE USER u1;
1940 connect conn_attempt, localhost, root,,,,;
1941 SET @@lock_wait_timeout=1;
1942 connect conn_execute, localhost, root,,,,;
1943 connection conn_attempt;
1944 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1945 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1946 FLUSH PRIVILEGES;
1947 connection conn_execute;
1948 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1949 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1950 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1951 GRANT SELECT ON *.* TO u1;
1952 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1953 connection conn_attempt;
1954 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1955 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1956 connection default;
1957 SET DEBUG_SYNC="RESET";
1958 disconnect conn_attempt;
1959 disconnect conn_execute;
1960 DROP USER u1;
1961 # -------------------------------
1962 # Case 1.6: GRANT role + FLUSH PRIVILEGES
1963 CREATE USER u1;
1964 CREATE ROLE r1;
1965 connect conn_attempt, localhost, root,,,,;
1966 SET @@lock_wait_timeout=1;
1967 connect conn_execute, localhost, root,,,,;
1968 connection conn_attempt;
1969 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1970 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1971 FLUSH PRIVILEGES;
1972 connection conn_execute;
1973 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1974 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
1975 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
1976 GRANT r1 TO u1;
1977 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
1978 connection conn_attempt;
1979 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1980 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
1981 connection default;
1982 SET DEBUG_SYNC="RESET";
1983 disconnect conn_attempt;
1984 disconnect conn_execute;
1985 DROP USER r1;
1986 DROP USER u1;
1987 # -------------------------------
1988 # Case 1.7: SET DEFAULT ROLE + FLUSH PRIVILEGES
1989 CREATE USER u1;
1990 CREATE ROLE r1;
1991 GRANT r1 TO u1;
1992 connect conn_attempt, localhost, root,,,,;
1993 SET @@lock_wait_timeout=1;
1994 connect conn_execute, localhost, root,,,,;
1995 connection conn_attempt;
1996 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
1997 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
1998 FLUSH PRIVILEGES;
1999 connection conn_execute;
2000 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2001 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2002 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2003 SET DEFAULT ROLE r1 TO u1;
2004 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2005 connection conn_attempt;
2006 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2007 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2008 connection default;
2009 SET DEBUG_SYNC="RESET";
2010 disconnect conn_attempt;
2011 disconnect conn_execute;
2012 DROP USER r1;
2013 DROP USER u1;
2014 # -------------------------------
2015 # Case 1.8: SET PASSWORD + FLUSH PRIVILEGES
2016 CREATE USER u1;
2017 connect conn_attempt, localhost, root,,,,;
2018 SET @@lock_wait_timeout=1;
2019 connect conn_execute, localhost, root,,,,;
2020 connection conn_attempt;
2021 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2022 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2023 FLUSH PRIVILEGES;
2024 connection conn_execute;
2025 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2026 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2027 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2028 SET PASSWORD FOR u1 = 'efgh';
2029 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2030 connection conn_attempt;
2031 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2032 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2033 connection default;
2034 SET DEBUG_SYNC="RESET";
2035 disconnect conn_attempt;
2036 disconnect conn_execute;
2037 DROP USER u1;
2038 # -------------------------------
2039 # Case 1.9: REVOKE role + FLUSH PRIVILEGES
2040 CREATE USER u1;
2041 CREATE ROLE r1;
2042 GRANT r1 TO u1;
2043 connect conn_attempt, localhost, root,,,,;
2044 SET @@lock_wait_timeout=1;
2045 connect conn_execute, localhost, root,,,,;
2046 connection conn_attempt;
2047 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2048 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2049 FLUSH PRIVILEGES;
2050 connection conn_execute;
2051 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2052 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2053 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2054 REVOKE r1 FROM u1;
2055 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2056 connection conn_attempt;
2057 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2058 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2059 connection default;
2060 SET DEBUG_SYNC="RESET";
2061 disconnect conn_attempt;
2062 disconnect conn_execute;
2063 DROP ROLE r1;
2064 DROP USER u1;
2065 # -------------------------------
2066 # Case 1.10: REVOKE privilege + FLUSH PRIVILEGES
2067 CREATE USER u1;
2068 connect conn_attempt, localhost, root,,,,;
2069 SET @@lock_wait_timeout=1;
2070 connect conn_execute, localhost, root,,,,;
2071 connection conn_attempt;
2072 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2073 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2074 FLUSH PRIVILEGES;
2075 connection conn_execute;
2076 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2077 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2078 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2079 REVOKE SELECT ON *.* FROM u1;
2080 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2081 connection conn_attempt;
2082 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2083 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2084 connection default;
2085 SET DEBUG_SYNC="RESET";
2086 disconnect conn_attempt;
2087 disconnect conn_execute;
2088 DROP USER u1;
2089 # -------------------------------
2090 # Case 1.11: DROP ROLE + FLUSH PRIVILEGES
2091 CREATE ROLE r1;
2092 connect conn_attempt, localhost, root,,,,;
2093 SET @@lock_wait_timeout=1;
2094 connect conn_execute, localhost, root,,,,;
2095 connection conn_attempt;
2096 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2097 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2098 FLUSH PRIVILEGES;
2099 connection conn_execute;
2100 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2101 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2102 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2103 DROP ROLE r1;
2104 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2105 connection conn_attempt;
2106 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2107 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2108 connection default;
2109 SET DEBUG_SYNC="RESET";
2110 disconnect conn_attempt;
2111 disconnect conn_execute;
2112 # -------------------------------
2113 # Case 1.12: DROP USER + FLUSH PRIVILEGES
2114 CREATE USER u1;
2115 connect conn_attempt, localhost, root,,,,;
2116 SET @@lock_wait_timeout=1;
2117 connect conn_execute, localhost, root,,,,;
2118 connection conn_attempt;
2119 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2120 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2121 FLUSH PRIVILEGES;
2122 connection conn_execute;
2123 SET DEBUG="+d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2124 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2125 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2126 DROP USER u1;
2127 SET DEBUG="-d, wl14084_acl_ddl_after_table_lock_trigger_timeout";
2128 connection conn_attempt;
2129 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2130 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2131 connection default;
2132 SET DEBUG_SYNC="RESET";
2133 disconnect conn_attempt;
2134 disconnect conn_execute;
2135 # -----------------------------------------------------------------------
2136 # Case 2: Unsuccessful ACL DDLs + FLUSH PRIVILEGES
2137 SET @@lock_wait_timeout=1;
2138 # -------------------------------
2139 # Case 2.1: CREATE USER + FLUSH PRIVILEGES
2140 CREATE USER u1;
2141 connect conn_attempt, localhost, root,,,,;
2142 SET @@lock_wait_timeout=1;
2143 connect conn_execute, localhost, root,,,,;
2144 connection conn_attempt;
2145 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2146 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2147 FLUSH PRIVILEGES;
2148 connection conn_execute;
2149 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2150 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2151 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2152 CREATE USER u3, u2, u1;
2153 ERROR HY000: Operation CREATE USER failed for 'u1'@'%'
2154 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2155 connection conn_attempt;
2156 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2157 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2158 connection default;
2159 SET DEBUG_SYNC="RESET";
2160 disconnect conn_attempt;
2161 disconnect conn_execute;
2162 DROP USER u1;
2163 # -------------------------------
2164 # Case 2.2: ALTER USER + FLUSH PRIVILEGES
2165 connect conn_attempt, localhost, root,,,,;
2166 SET @@lock_wait_timeout=1;
2167 connect conn_execute, localhost, root,,,,;
2168 connection conn_attempt;
2169 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2170 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2171 FLUSH PRIVILEGES;
2172 connection conn_execute;
2173 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2174 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2175 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2176 ALTER USER u2 IDENTIFIED BY 'abcd';
2177 ERROR HY000: Operation ALTER USER failed for 'u2'@'%'
2178 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2179 connection conn_attempt;
2180 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2181 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2182 connection default;
2183 SET DEBUG_SYNC="RESET";
2184 disconnect conn_attempt;
2185 disconnect conn_execute;
2186 # -------------------------------
2187 # Case 2.3: RENAME USER + FLUSH PRIVILEGES
2188 connect conn_attempt, localhost, root,,,,;
2189 SET @@lock_wait_timeout=1;
2190 connect conn_execute, localhost, root,,,,;
2191 connection conn_attempt;
2192 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2193 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2194 FLUSH PRIVILEGES;
2195 connection conn_execute;
2196 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2197 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2198 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2199 RENAME USER u2 TO u3;
2200 ERROR HY000: Operation RENAME USER failed for 'u2'@'%'
2201 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2202 connection conn_attempt;
2203 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2204 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2205 connection default;
2206 SET DEBUG_SYNC="RESET";
2207 disconnect conn_attempt;
2208 disconnect conn_execute;
2209 # -------------------------------
2210 # Case 2.4: DROP USER + FLUSH PRIVILEGES
2211 connect conn_attempt, localhost, root,,,,;
2212 SET @@lock_wait_timeout=1;
2213 connect conn_execute, localhost, root,,,,;
2214 connection conn_attempt;
2215 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2216 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2217 FLUSH PRIVILEGES;
2218 connection conn_execute;
2219 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2220 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2221 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2222 DROP USER u2;
2223 ERROR HY000: Operation DROP USER failed for 'u2'@'%'
2224 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2225 connection conn_attempt;
2226 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2227 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2228 connection default;
2229 SET DEBUG_SYNC="RESET";
2230 disconnect conn_attempt;
2231 disconnect conn_execute;
2232 # -------------------------------
2233 # Case 2.5: SET PASSWORD + FLUSH PRIVILEGES
2234 CREATE USER u1;
2235 connect conn_attempt, localhost, root,,,,;
2236 SET @@lock_wait_timeout=1;
2237 connect conn_execute, localhost, root,,,,;
2238 connection conn_attempt;
2239 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2240 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2241 FLUSH PRIVILEGES;
2242 connection conn_execute;
2243 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout, wl14084_simulate_set_password_failure";
2244 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2245 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2246 SET PASSWORD FOR u1 = 'abcd';
2247 ERROR 42000: Can't find any matching row in the user table
2248 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout, wl14084_simulate_set_password_failure";
2249 connection conn_attempt;
2250 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2251 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2252 connection default;
2253 SET DEBUG_SYNC="RESET";
2254 disconnect conn_attempt;
2255 disconnect conn_execute;
2256 DROP USER u1;
2257 # -------------------------------
2258 # Case 2.6: CREATE ROLE + FLUSH PRIVILEGES
2259 CREATE ROLE r1;
2260 connect conn_attempt, localhost, root,,,,;
2261 SET @@lock_wait_timeout=1;
2262 connect conn_execute, localhost, root,,,,;
2263 connection conn_attempt;
2264 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2265 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2266 FLUSH PRIVILEGES;
2267 connection conn_execute;
2268 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2269 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2270 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2271 CREATE ROLE r3, r2, r1;
2272 ERROR HY000: Operation CREATE ROLE failed for 'r1'@'%'
2273 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2274 connection conn_attempt;
2275 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2276 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2277 connection default;
2278 SET DEBUG_SYNC="RESET";
2279 disconnect conn_attempt;
2280 disconnect conn_execute;
2281 DROP ROLE r1;
2282 # -------------------------------
2283 # Case 2.7: DROP ROLE + FLUSH PRIVILEGES
2284 connect conn_attempt, localhost, root,,,,;
2285 SET @@lock_wait_timeout=1;
2286 connect conn_execute, localhost, root,,,,;
2287 connection conn_attempt;
2288 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2289 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2290 FLUSH PRIVILEGES;
2291 connection conn_execute;
2292 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2293 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2294 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2295 DROP ROLE r2;
2296 ERROR HY000: Operation DROP ROLE failed for 'r2'@'%'
2297 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2298 connection conn_attempt;
2299 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2300 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2301 connection default;
2302 SET DEBUG_SYNC="RESET";
2303 disconnect conn_attempt;
2304 disconnect conn_execute;
2305 # -------------------------------
2306 # Case 2.8: GRANT privilege + FLUSH PRIVILEGES
2307 connect conn_attempt, localhost, root,,,,;
2308 SET @@lock_wait_timeout=1;
2309 connect conn_execute, localhost, root,,,,;
2310 connection conn_attempt;
2311 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2312 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2313 FLUSH PRIVILEGES;
2314 connection conn_execute;
2315 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2316 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2317 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2318 GRANT SELECT ON *.* TO u2;
2319 ERROR 42000: You are not allowed to create a user with GRANT
2320 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2321 connection conn_attempt;
2322 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2323 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2324 connection default;
2325 SET DEBUG_SYNC="RESET";
2326 disconnect conn_attempt;
2327 disconnect conn_execute;
2328 # -------------------------------
2329 # Case 2.9: GRANT role + FLUSH PRIVILEGES
2330 CREATE ROLE r1;
2331 connect conn_attempt, localhost, root,,,,;
2332 SET @@lock_wait_timeout=1;
2333 connect conn_execute, localhost, root,,,,;
2334 connection conn_attempt;
2335 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2336 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2337 FLUSH PRIVILEGES;
2338 connection conn_execute;
2339 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2340 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2341 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2342 GRANT r1 TO u2;
2343 ERROR HY000: Unknown authorization ID `u2`@`%`
2344 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2345 connection conn_attempt;
2346 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2347 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2348 connection default;
2349 SET DEBUG_SYNC="RESET";
2350 disconnect conn_attempt;
2351 disconnect conn_execute;
2352 DROP ROLE r1;
2353 # -------------------------------
2354 # Case 2.10: REVOKE role + FLUSH PRIVILEGES
2355 CREATE ROLE r1;
2356 connect conn_attempt, localhost, root,,,,;
2357 SET @@lock_wait_timeout=1;
2358 connect conn_execute, localhost, root,,,,;
2359 connection conn_attempt;
2360 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2361 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2362 FLUSH PRIVILEGES;
2363 connection conn_execute;
2364 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2365 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2366 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2367 REVOKE r1 FROM u2;
2368 ERROR HY000: Unknown authorization ID `u2`@`%`
2369 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2370 connection conn_attempt;
2371 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2372 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2373 connection default;
2374 SET DEBUG_SYNC="RESET";
2375 disconnect conn_attempt;
2376 disconnect conn_execute;
2377 DROP ROLE r1;
2378 # -------------------------------
2379 # Case 2.11: REVOKE privilege + FLUSH PRIVILEGES
2380 connect conn_attempt, localhost, root,,,,;
2381 SET @@lock_wait_timeout=1;
2382 connect conn_execute, localhost, root,,,,;
2383 connection conn_attempt;
2384 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2385 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2386 FLUSH PRIVILEGES;
2387 connection conn_execute;
2388 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2389 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2390 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2391 REVOKE SELECT ON *.* FROM u2;
2392 ERROR 42000: There is no such grant defined for user 'u2' on host '%'
2393 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2394 connection conn_attempt;
2395 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2396 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2397 connection default;
2398 SET DEBUG_SYNC="RESET";
2399 disconnect conn_attempt;
2400 disconnect conn_execute;
2401 # -------------------------------
2402 # Case 2.12: SET DEFAULT ROLE + FLUSH PRIVILEGES
2403 CREATE ROLE r1;
2404 connect conn_attempt, localhost, root,,,,;
2405 SET @@lock_wait_timeout=1;
2406 connect conn_execute, localhost, root,,,,;
2407 connection conn_attempt;
2408 SET DEBUG="+d, wl14084_simulate_flush_privileges_timeout";
2409 SET DEBUG_SYNC="wl14084_flush_privileges_before_table_locks SIGNAL proceed_with_acl_ddl WAIT_FOR flush_now";
2410 FLUSH PRIVILEGES;
2411 connection conn_execute;
2412 SET DEBUG="+d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2413 SET DEBUG_SYNC="now WAIT_FOR proceed_with_acl_ddl";
2414 SET DEBUG_SYNC="wl14084_after_table_locks SIGNAL flush_now";
2415 SET DEFAULT ROLE r1 TO u2;
2416 ERROR HY000: `r1`@`%` is not granted to `u2`@`%`
2417 SET DEBUG="-d, wl14084_acl_ddl_error_before_cache_reload_trigger_timeout";
2418 connection conn_attempt;
2419 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2420 SET DEBUG="-d, wl14084_simulate_flush_privileges_timeout";
2421 connection default;
2422 SET DEBUG_SYNC="RESET";
2423 disconnect conn_attempt;
2424 disconnect conn_execute;
2425 DROP ROLE r1;
2426 # -----------------------------------------------------------------------
2427 # Cleanup
2428 # Restore initial values
2429 SET @@global.transaction_isolation = @global_start_value;
2430 SELECT @@global.transaction_isolation;
2431 @@global.transaction_isolation
2432 REPEATABLE-READ
2433 SET @@session.transaction_isolation = @session_start_value;
2434 SELECT @@session.transaction_isolation;
2435 @@session.transaction_isolation
2436 REPEATABLE-READ