Debian Codegraph @

mysql-test/suite/auth_sec/t/acl_tables_row_locking.test

raw
1 --echo #
2 --echo # WL#14087 Avoid locking reads from ACL tables
3 --echo #
4 --echo # Test scenarios that involve reading ACL tables and updating
5 --echo # ACL tables concurrently. This tests are related to WL#14087.
6 --echo #
7 --echo # Following ACL tables are considered for testing:
8 --echo # mysql.user
9 --echo # mysql.db
10 --echo # mysql.tables_priv
11 --echo # mysql.columns_priv
12 --echo # mysql.procs_priv
13 --echo # mysql.proxies_priv
14 --echo # mysql.role_edges
15 --echo # mysql.default_roles
16 --echo # mysql.global_grants
17 --echo # mysql.password_history
18
19 --source include/have_debug_sync.inc
20 --source include/big_test.inc
21
22 --echo #
23 --echo # Setup schema for the test
24 --echo # We create new users so that we can populate rows in all acl tables.
25 --echo # We avoid using 'root' user here, because that might affect execution
26 --echo # of other tests. Also, we would need more than one users for below
27 --echo # test cases.
28 --echo #
29 CREATE TABLE t1 (f1 INT);
30 DELIMITER //;
31 CREATE PROCEDURE proc1() BEGIN SELECT * FROM t1; END //
32 DELIMITER ;//
33 --echo # User accounts used for testing.
34 CREATE USER u1;
35 GRANT SELECT ON mysql.* TO u1;
36 GRANT SELECT ON performance_schema.* TO u1;
37 GRANT LOCK TABLES ON *.* TO u1;
38 GRANT RELOAD ON *.* TO u1;
39
40 CREATE USER writer1;
41 GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO writer1;
42 GRANT SELECT ON performance_schema.* TO writer1;
43 GRANT CREATE, DROP ON *.* TO writer1;
44 GRANT CREATE USER ON *.* TO writer1;
45
46 CREATE USER writer2;
47 GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO writer2;
48 GRANT SELECT ON performance_schema.* TO writer2;
49 GRANT CREATE, DROP ON *.* TO writer2;
50 GRANT CREATE USER ON *.* TO writer2;
51 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO writer2;
52 GRANT REFERENCES ON mysql.user TO writer2;
53
54 --echo # Populate all ACL tables with some rows for u2.
55 --echo # This user is used for checking purpose.
56 CREATE USER u2 PASSWORD HISTORY 3;
57 GRANT SELECT ON test.* TO u2;
58 GRANT SELECT ON test.t1 TO u2;
59 GRANT SELECT (f1) ON test.t1 TO u2;
60 GRANT EXECUTE ON PROCEDURE test.proc1 TO u2;
61 GRANT PROXY ON writer1 TO u2;
62 GRANT BACKUP_ADMIN ON *.* to u2;
63 GRANT writer1 TO u2;
64 SET DEFAULT ROLE writer1 TO u2;
65 ALTER USER u2 IDENTIFIED BY 'newpass1';
66
67 --echo # Verify that all ACL tables has some rows for u2.
68 SELECT User FROM mysql.user WHERE User='u2';
69 SELECT User FROM mysql.db WHERE User='u2';
70 SELECT User FROM mysql.tables_priv WHERE User='u2';
71 SELECT User FROM mysql.columns_priv WHERE User='u2';
72 SELECT User FROM mysql.procs_priv WHERE User='u2';
73 SELECT User FROM mysql.proxies_priv WHERE User='u2';
74 SELECT User FROM mysql.global_grants WHERE User='u2';
75 SELECT User FROM mysql.password_history WHERE User='u2';
76 SELECT User FROM mysql.default_roles WHERE User='u2';
77 SELECT To_user FROM mysql.role_edges WHERE To_user='u2';
78
79 --echo # Backup user 'u2' and rename it as 'u2x'. This row
80 --echo # is used to test DML on mysql.user.
81 CREATE TABLE user_bk AS SELECT * FROM mysql.user WHERE User='u2';
82 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
83 CREATE TABLE user_copy AS SELECT * FROM mysql.user LIMIT 0;
84
85 let query_row_locks= SELECT CONCAT(OBJECT_SCHEMA,'.',OBJECT_NAME)
86 tab,INDEX_NAME,LOCK_MODE, 'Acquired row lock on u2/u2x' AS Has_lock
87 FROM performance_schema.data_locks
88 WHERE LOCK_TYPE='RECORD' AND
89 (LOCATE('u2',LOCK_DATA)>0 OR LOCATE('u2x',LOCK_DATA)>0);
90
91 # Variables to loop through isolation levels.
92 let $c=1;
93 let $isolation_levels=
94 "SERIALIZABLE,REPEATABLE READ,READ COMMITTED,READ UNCOMMITTED";
95 let get_next_isolation=
96 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX($isolation_levels,',',\$c),',',-1);
97
98 delimiter |;
99 CREATE PROCEDURE proc2 ()
100 BEGIN
101 UPDATE mysql.user SET user='u2x' WHERE User='u2';
102 END|
103 CREATE FUNCTION func1 () RETURNS INTEGER
104 BEGIN
105 UPDATE user_copy SET User='u2x'
106 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
107 RETURN 0;
108 END|
109 delimiter ;|
110 GRANT EXECUTE ON PROCEDURE test.proc2 TO writer2;
111 GRANT EXECUTE ON FUNCTION test.func1 TO writer2;
112
113 # The optimizer flag skip_scan=on affects the way InnoDB
114 # acquires row locks. Optimize may or may not use skip_scan
115 # based on its statistics study. This would cause in-stability
116 # when checking kind of locks acquired using
117 # 'performance_schema.data_locks.lock_data'. Hence we disable it.
118 let $original_optimizer_switch=`SELECT @@global.optimizer_switch`;
119 SET GLOBAL optimizer_switch = "skip_scan=off";
120
121 --echo # Connections used for testing.
122 connect(read_con,localhost,u1,,);
123 connect(update_con1,localhost,writer1,,);
124 connect(update_con2,localhost,writer2,,);
125
126 --echo # Case 1:
127 --echo # Execute SELECT FOR UPDATE on ACL table after a concurrent
128 --echo # connection has started reading ACL table.
129 --echo #
130 --echo # SQL commands using ACL tables for read operation would skip
131 --echo # acquiring row lock. This will enable execution of
132 --echo # 'SELECT ... FOR UPDATE' statement without blocking.
133 --echo # connection which is reading a ACL table. We test all the ACL tables.
134 --echo #
135 --let column_name=User
136 --let acl_table=mysql.user
137 --source ../include/acl_tables_row_locking.inc
138 --let acl_table=mysql.db
139 --source ../include/acl_tables_row_locking.inc
140 --let acl_table=mysql.tables_priv
141 --source ../include/acl_tables_row_locking.inc
142 --let acl_table=mysql.columns_priv
143 --source ../include/acl_tables_row_locking.inc
144 --let acl_table=mysql.procs_priv
145 --source ../include/acl_tables_row_locking.inc
146 --let acl_table=mysql.proxies_priv
147 --source ../include/acl_tables_row_locking.inc
148 --let acl_table=mysql.global_grants
149 --source ../include/acl_tables_row_locking.inc
150 --let acl_table=mysql.password_history
151 --source ../include/acl_tables_row_locking.inc
152 --let acl_table=mysql.default_roles
153 --source ../include/acl_tables_row_locking.inc
154
155 --let acl_table=mysql.role_edges
156 --let column_name=To_user
157 --source ../include/acl_tables_row_locking.inc
158
159
160 let $c=1;
161 while($c <= 4)
162 {
163 let isolation_level = `$get_next_isolation`;
164
165 --echo # Case 2 (with $isolation_level isolation level):
166 --echo # Using a separate transaction (With START TRANSACTION)
167 --echo # Read ACL table in SELECT in all isolation mode
168 --echo # and run DDL/DML on ACL tables in concurrent.
169 connection read_con;
170 SELECT User FROM mysql.user WHERE User='u2';
171 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
172 START TRANSACTION;
173 eval SELECT User FROM mysql.user WHERE User='u2';
174
175 connection update_con1;
176 --source ../include/acl_table_read_test_dml_ddl.inc
177
178 connection read_con;
179 COMMIT;
180 connection default;
181
182 --echo # Case 2.1 (with $isolation_level isolation level):
183 --echo # Using a separate transaction (With START TRANSACTION)
184 --echo # Read ACL table in SELECT .. HIGH PRIORITY in all isolation mode
185 --echo # and run DDL/DML on ACL tables in concurrent.
186 connection read_con;
187 SELECT User FROM mysql.user WHERE User='u2';
188 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
189 START TRANSACTION;
190 eval SELECT HIGH_PRIORITY User FROM mysql.user WHERE User='u2';
191
192 connection update_con1;
193 --source ../include/acl_table_read_test_dml_ddl.inc
194
195 connection read_con;
196 COMMIT;
197
198 connection default;
199
200 --echo # Case 3 (with $isolation_level isolation level):
201 --echo # Using a separate transaction (With START TRANSACTION)
202 --echo # Read ACL table in SELECT in all isolation mode
203 --echo # and run DDL/DML on ACL tables in concurrent.
204 connection read_con;
205 SELECT User FROM mysql.user WHERE User='u2';
206 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
207 set autocommit=0;
208 eval SELECT User FROM mysql.user WHERE User='u2';
209
210 connection update_con1;
211 --source ../include/acl_table_read_test_dml_ddl.inc
212
213 connection read_con;
214 COMMIT;
215 set autocommit=default;
216
217 --echo # Case 4 (with $isolation_level isolation level):
218 --echo # Read ACL table in DO command in different isolation mode
219 --echo # and run DDL/DML on ACL tables in concurrent.
220 connection read_con;
221 SELECT User FROM mysql.user WHERE User='u2';
222 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
223 START TRANSACTION;
224 eval DO (SELECT User FROM mysql.user WHERE User='u2');
225
226 connection update_con1;
227 --source ../include/acl_table_read_test_dml_ddl.inc
228
229 connection read_con;
230 COMMIT;
231
232 --echo # Case 5 (with $isolation_level isolation level):
233 --echo # Read ACL table in SET command in different isolation mode
234 --echo # and run DDL/DML on ACL tables in concurrent.
235 connection read_con;
236 SELECT User FROM mysql.user WHERE User='u2';
237 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
238 START TRANSACTION;
239 SET @u2_host = (SELECT Host FROM mysql.user WHERE User='u2');
240
241 connection update_con1;
242 --source ../include/acl_table_read_test_dml_ddl.inc
243
244 connection read_con;
245 COMMIT;
246
247 --echo # Case 6:
248 --echo # Read ACL table in INSERT ... SELECT command in different
249 --echo # isolation mode and run DDL/DML on ACL tables in concurrent.
250 connection update_con2;
251 SELECT User FROM mysql.user WHERE User='u2';
252 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
253 START TRANSACTION;
254 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
255
256 connection update_con1;
257 --source ../include/acl_table_read_test_dml_ddl.inc
258
259 connection update_con2;
260 COMMIT;
261
262
263 --echo # Case 7:
264 --echo # Read ACL table in UPDATE ... WHERE SELECT in different
265 --echo # isolation mode and run DDL/DML on ACL tables in concurrent.
266 connection read_con;
267 SELECT User FROM mysql.user WHERE User='u2';
268 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
269 START TRANSACTION;
270 UPDATE user_copy SET User='u2x'
271 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
272
273 connection update_con1;
274 --source ../include/acl_table_read_test_dml_ddl.inc
275
276 connection read_con;
277 COMMIT;
278
279
280 --echo # Case 8:
281 --echo # Read ACL table in Multi-table UPDATE ... SELECT in different
282 --echo # isolation mode and run DDL/DML on ACL tables in concurrent.
283 connection read_con;
284 SELECT User FROM mysql.user WHERE User='u2';
285 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
286 START TRANSACTION;
287 UPDATE t1, user_copy SET user_copy.Host='instance1'
288 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
289
290 connection update_con1;
291 --source ../include/acl_table_read_test_dml_ddl.inc
292
293 connection read_con;
294 COMMIT;
295
296
297 --echo # Case 9:
298 --echo # Read ACL table in DELETE ... WHERE SELECT in different
299 --echo # isolation mode and run DDL/DML on ACL tables in concurrent.
300 connection read_con;
301 SELECT User FROM mysql.user WHERE User='u2';
302 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
303 START TRANSACTION;
304 DELETE FROM user_copy
305 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
306
307 connection update_con1;
308 --source ../include/acl_table_read_test_dml_ddl.inc
309
310 connection read_con;
311 COMMIT;
312
313
314 --echo # Case 10:
315 --echo # Read ACL table in Multi-table DELETE ... WHERE SELECT in different
316 --echo # isolation mode and run DDL/DML on ACL tables in concurrent.
317 connection update_con1;
318 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
319
320 connection read_con;
321 SELECT User FROM mysql.user WHERE User='u2';
322 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
323 START TRANSACTION;
324 DELETE user_copy FROM user_copy JOIN (SELECT * FROM user_copy) tuser_copy
325 WHERE EXISTS (SELECT User FROM mysql.user WHERE User='u2');
326
327 connection update_con1;
328 --source ../include/acl_table_read_test_dml_ddl.inc
329
330 connection read_con;
331 COMMIT;
332
333
334 --echo # Case 11:
335 --echo # Read ACL table in CREATE TABLE ... SELECT in different
336 --echo # isolation mode and run DDL/DML on ACL tables in concurrent.
337 connection update_con2;
338 SELECT User FROM mysql.user WHERE User='u2';
339 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
340 # Stop execution after inserting the row and do not commit the transaction.
341 SET SESSION DEBUG_SYNC='create_select_after_write_rows_event SIGNAL parked WAIT_FOR go';
342 --send CREATE TABLE tmp AS SELECT * FROM mysql.user WHERE User='u2'
343 connection default;
344 SET DEBUG_SYNC= 'now WAIT_FOR parked';
345
346 connection update_con1;
347 --source ../include/acl_table_read_test_dml_ddl.inc
348
349 connection default;
350 SET DEBUG_SYNC= 'now SIGNAL go';
351 connection update_con2;
352 --reap
353 SET SESSION DEBUG_SYNC=RESET;
354 COMMIT;
355
356 connection default;
357 SET SESSION DEBUG_SYNC=RESET;
358 DROP TABLE tmp;
359
360
361 --echo # Case 12:
362 --echo # Read ACL table in LOCK TABLE for READ mode and
363 --echo # Make sure a concurrent ACL DML on same table is blocked.
364 connection default;
365 connection read_con;
366 SELECT User FROM mysql.user WHERE User='u2';
367 LOCK TABLE mysql.user READ;
368
369 connection update_con1;
370 SET SESSION lock_wait_timeout=1;
371 SET SESSION innodb_lock_wait_timeout=1;
372 --echo # Make sure that we do not allow updating ACL table.
373 --error ER_LOCK_WAIT_TIMEOUT
374 INSERT INTO mysql.user SELECT * FROM user_bk;
375 --error ER_LOCK_WAIT_TIMEOUT
376 REPLACE INTO mysql.user SELECT * FROM user_bk;
377 --error ER_LOCK_WAIT_TIMEOUT
378 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
379 --error ER_LOCK_WAIT_TIMEOUT
380 DELETE FROM mysql.user WHERE User='u2';
381
382 connection read_con;
383 UNLOCK TABLES;
384
385
386 --echo # Case 13:
387 --echo # Run FTWRL READ mode and
388 --echo # make sure a concurrent ACL DML on same table is blocked.
389 connection read_con;
390 SELECT User FROM mysql.user WHERE User='u2';
391 FLUSH TABLES WITH READ LOCK;
392
393 connection update_con1;
394 --error ER_LOCK_WAIT_TIMEOUT
395 INSERT INTO mysql.user SELECT * FROM user_bk;
396 --error ER_LOCK_WAIT_TIMEOUT
397 REPLACE INTO mysql.user SELECT * FROM user_bk;
398 --error ER_LOCK_WAIT_TIMEOUT
399 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
400 --error ER_LOCK_WAIT_TIMEOUT
401 DELETE FROM mysql.user WHERE User='u2';
402
403 connection read_con;
404 UNLOCK TABLES;
405
406 # Explicilty purge the delete records. Not doing so affects the way
407 # InnoDB acquires the row locks. E.g.,
408 # 'performance_schema.data_locks.LOCK_DATA may show (sporadically) that
409 # there is S lock held on a row marked as deleted.
410 connection default;
411 --source include/wait_innodb_all_purged.inc
412 connection update_con2;
413
414 --echo # Case 14:
415 --echo # Update ACL table in a DML inside a SP in different isolation mode.
416 --echo # Make sure a concurrent ACL DML on same table is blocked.
417 connection default;
418 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
419
420 connection update_con2;
421 # Case 14.1: Procedure updating ACL table.
422 SELECT User FROM mysql.user WHERE User='u2';
423 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
424 START TRANSACTION;
425 CALL proc2();
426
427 --echo # Make sure X record locks are held on ACL table.
428 eval $query_row_locks;
429
430 connection update_con1;
431 --echo # Make sure that we do not allow updating ACL table.
432 --error ER_LOCK_WAIT_TIMEOUT
433 INSERT INTO mysql.user SELECT * FROM user_bk;
434 --error ER_LOCK_WAIT_TIMEOUT
435 REPLACE INTO mysql.user SELECT * FROM user_bk;
436 --error ER_LOCK_WAIT_TIMEOUT
437 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
438 --error ER_LOCK_WAIT_TIMEOUT
439 DELETE FROM mysql.user WHERE User='u2';
440
441 connection update_con2;
442 ROLLBACK;
443
444 connection default;
445 --source include/wait_innodb_all_purged.inc
446 connection update_con2;
447
448 # Case 14.2: Function reading ACL table.
449 connection update_con2;
450 SELECT User FROM mysql.user WHERE User='u2';
451 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
452 START TRANSACTION;
453 SELECT func1();
454
455 connection update_con1;
456 --source ../include/acl_table_read_test_dml_ddl.inc
457
458 connection update_con2;
459 ROLLBACK;
460
461
462 --echo # Case 15:
463 --echo # Reference ACL table in a foreign key. Reading ACL table while
464 --echo # updating the table in different isolation mode.
465 --echo # Make sure a concurrent ACL DML on ACL table is blocked.
466 connection update_con2;
467
468 CREATE TABLE fk_to_acl_table
469 (Host char(255) CHARACTER SET ASCII DEFAULT '' NOT NULL,
470 User char(32) COLLATE utf8_bin DEFAULT '' NOT NULL,
471 address CHAR(255),
472 FOREIGN KEY (Host, User) REFERENCES mysql.user(Host,User));
473 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
474
475 ALTER TABLE fk_to_acl_table MODIFY COLUMN address char(200),
476 ALGORITHM=COPY;
477
478 SELECT User FROM mysql.user WHERE User='u2';
479 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
480 START TRANSACTION;
481 INSERT INTO fk_to_acl_table VALUES ('%', 'u2', '2nd cross, 1st main, downtown');
482 --echo # Make sure S record locks are held on ACL table.
483 eval $query_row_locks;
484
485 connection update_con1;
486 --echo # Make sure that we do not allow updating ACL tables.
487 --error ER_LOCK_WAIT_TIMEOUT
488 REPLACE INTO mysql.user SELECT * FROM user_bk;
489 --error ER_LOCK_WAIT_TIMEOUT
490 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
491 --error ER_LOCK_WAIT_TIMEOUT
492 DELETE FROM mysql.user WHERE User='u2';
493
494 connection update_con2;
495 ROLLBACK;
496 --echo # Cleanup
497 connection default;
498 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
499 DROP TABLE fk_to_acl_table;
500
501 connection default;
502 --source include/wait_innodb_all_purged.inc
503 connection update_con2;
504
505 --echo # Case 16:
506 --echo # Case 16.1 Update ACL table in a multi-table UPDATE command in
507 --echo # different isolation level. Make sure a concurrent DML on ACL table
508 --echo # is blocked.
509 connection update_con2;
510 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
511 SELECT User FROM mysql.user WHERE User='u2';
512 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
513 START TRANSACTION;
514 UPDATE mysql.user u, user_copy SET u.User='u2x' WHERE u.User LIKE 'u2';
515
516 --echo # Make sure X record locks are held on ACL table.
517 eval $query_row_locks;
518
519 connection update_con1;
520 --echo # Make sure that we do not allow updating ACL tables.
521 --error ER_LOCK_WAIT_TIMEOUT
522 REPLACE INTO mysql.user SELECT * FROM user_bk;
523 --error ER_LOCK_WAIT_TIMEOUT
524 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
525 --error ER_LOCK_WAIT_TIMEOUT
526 DELETE FROM mysql.user WHERE User='u2';
527
528 connection update_con2;
529 ROLLBACK;
530
531 connection default;
532 --source include/wait_innodb_all_purged.inc
533 connection update_con2;
534
535 --echo # Case 16.2 Read ACL table in a multi-table UPDATE command in
536 --echo # different isolation level. Ideally concurrent DML on ACL table
537 --echo # should have been blocked. Due to Bug#31731752 we acquire
538 --echo # stronger lock on the table and hence acquire X clock on the
539 --echo # ACL table being read.
540 connection update_con2;
541 SELECT User FROM mysql.user WHERE User='u2';
542 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
543 START TRANSACTION;
544 UPDATE mysql.user u1, user_copy u2 SET u2.User='u2x' WHERE u2.User LIKE 'u2';
545
546 --echo # Check that X record locks is held on ACL table.
547 eval $query_row_locks;
548
549 connection update_con1;
550 --echo # Make sure that we do not allow updating ACL tables.
551 --error ER_LOCK_WAIT_TIMEOUT
552 REPLACE INTO mysql.user SELECT * FROM user_bk;
553 --error ER_LOCK_WAIT_TIMEOUT
554 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
555 --error ER_LOCK_WAIT_TIMEOUT
556 DELETE FROM mysql.user WHERE User='u2';
557
558 connection update_con2;
559 ROLLBACK;
560
561
562 --echo # Case 17:
563 --echo # Case 17.1: Delete row in ACL table in a multi-table DELETE in
564 --echo # different isolation level. Make sure a concurrent DML on ACL
565 --echo # table is blocked.
566 connection update_con2;
567 UPDATE user_bk SET user='u2' WHERE user LIKE 'u2x';
568 SELECT User FROM mysql.user WHERE User='u2';
569 SELECT User FROM user_copy;
570 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
571 START TRANSACTION;
572 DELETE u, cu FROM mysql.user u JOIN user_copy cu WHERE u.User LIKE 'u2';
573
574 --echo # Make sure X record locks are held on ACL table.
575 eval $query_row_locks;
576
577 connection update_con1;
578 --echo # Make sure that we do not allow updating ACL table.
579 --error ER_LOCK_WAIT_TIMEOUT
580 REPLACE INTO mysql.user SELECT * FROM user_bk;
581 --error ER_LOCK_WAIT_TIMEOUT
582 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
583 --error ER_LOCK_WAIT_TIMEOUT
584 DELETE FROM mysql.user WHERE User='u2';
585
586 connection update_con2;
587 ROLLBACK;
588 UPDATE user_bk SET user='u2x' WHERE user LIKE 'u2';
589
590 --echo # Case 17.2 Read ACL table in a multi-table DELETE command in
591 --echo # different isolation level. Make sure a concurrent DML on ACL table
592 --echo # is not blocked.
593 connection update_con2;
594 SELECT User FROM mysql.user WHERE User='u2';
595 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
596 START TRANSACTION;
597 DELETE u1 FROM user_copy u1 JOIN mysql.user u2 ON u2.User = u1.User;
598
599 eval $query_row_locks;
600
601 --echo # Make sure no record locks are held on ACL table.
602 connection update_con1;
603 --source ../include/acl_table_read_test_dml_ddl.inc
604
605 connection update_con2;
606 ROLLBACK;
607
608 --echo # Case 18:
609 --echo # Read ACL table in a DML inside a trigger in different isolation
610 --echo # mode. Make sure a concurrent ACL DML on same table is blocked.
611 connection update_con2;
612 INSERT INTO user_copy SELECT * FROM mysql.user WHERE User='u2';
613
614 CREATE TABLE tmp1 (f1 INT);
615 DELIMITER |;
616 CREATE TRIGGER read_trig1 BEFORE INSERT ON tmp1 FOR EACH ROW
617 BEGIN
618 UPDATE user_copy SET User='u2x'
619 WHERE User IN (SELECT User FROM mysql.user WHERE User='u2');
620 END|
621 CREATE TRIGGER update_trig1 BEFORE UPDATE ON tmp1 FOR EACH ROW
622 BEGIN
623 UPDATE mysql.user SET user='u2x' WHERE User='u2';
624 END|
625 DELIMITER ;|
626
627 connection default;
628 --source include/wait_innodb_all_purged.inc
629 connection update_con2;
630
631 # Case 18.1: Read ACL table in a trigger.
632 connection update_con2;
633 SELECT User FROM mysql.user WHERE User='u2';
634 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
635 START TRANSACTION;
636 INSERT INTO tmp1 VALUES (20);
637
638 --echo # Make sure no record locks are held on ACL table.
639 connection update_con1;
640 --source ../include/acl_table_read_test_dml_ddl.inc
641
642 connection update_con2;
643 COMMIT;
644
645 connection default;
646 --source include/wait_innodb_all_purged.inc
647 connection update_con2;
648
649 # Case 18.2: Update ACL table in a trigger.
650 eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
651 START TRANSACTION;
652 UPDATE tmp1 SET f1=30;
653
654 --echo # Make sure X record locks are held on ACL table.
655 eval $query_row_locks;
656
657 connection update_con1;
658 --echo # Make sure that we do not allow updating ACL table.
659 --error ER_LOCK_WAIT_TIMEOUT
660 INSERT INTO mysql.user SELECT * FROM user_bk;
661 --error ER_LOCK_WAIT_TIMEOUT
662 REPLACE INTO mysql.user SELECT * FROM user_bk;
663 --error ER_LOCK_WAIT_TIMEOUT
664 UPDATE mysql.user SET user='u2x' WHERE user LIKE 'u2';
665 --error ER_LOCK_WAIT_TIMEOUT
666 DELETE FROM mysql.user WHERE User='u2';
667
668 SET SESSION lock_wait_timeout=default;
669 SET SESSION innodb_lock_wait_timeout=default;
670
671 connection update_con2;
672 ROLLBACK;
673 DROP TABLE tmp1;
674 DELETE FROM user_copy;
675
676 inc $c;
677 }
678
679
680 --echo Cleanup
681 connection default;
682 disconnect read_con;
683 disconnect update_con1;
684 disconnect update_con2;
685 DROP PROCEDURE proc1;
686 DROP PROCEDURE proc2;
687 DROP FUNCTION func1;
688 DROP USER u1;
689 DROP USER u2;
690 DROP USER writer1;
691 DROP USER writer2;
692 DROP TABLE t1;
693 DROP TABLE user_copy;
694
695 DROP TABLE user_bk;
696
697 --disable_query_log
698 eval SET GLOBAL optimizer_switch = "$original_optimizer_switch";
699 --enable_query_log