| 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 |