1# 2# We need the Debug Sync Facility. 3# 4--source include/have_debug_sync.inc 5--source include/have_metadata_lock_info.inc 6 7# We need InnoDB tables for some of the tests. 8--source include/have_innodb.inc 9 10# Save the initial number of concurrent sessions. 11--source include/count_sessions.inc 12 13 14# Clean up resources used in this test case. 15--disable_warnings 16SET DEBUG_SYNC= 'RESET'; 17--enable_warnings 18 19# 20# Test the case of when a exclusive lock request waits for a 21# shared lock being upgraded to a exclusive lock. 22# 23 24connect (con1,localhost,root,,test,,); 25connect (con2,localhost,root,,test,,); 26connect (con3,localhost,root,,test,,); 27 28connection default; 29 30--disable_warnings 31drop table if exists t1,t2,t3; 32--enable_warnings 33 34create table t1 (i int); 35create table t2 (i int); 36 37--echo connection: default 38lock tables t2 read; 39 40connection con1; 41--echo connection: con1 42set debug_sync='mdl_upgrade_lock SIGNAL parked WAIT_FOR go'; 43--send alter table t1 rename t3 44 45connection default; 46--echo connection: default 47set debug_sync= 'now WAIT_FOR parked'; 48 49connection con2; 50--echo connection: con2 51set debug_sync='mdl_acquire_lock_wait SIGNAL go'; 52--send drop table t1,t2 53 54connection con1; 55--echo connection: con1 56--reap 57 58connection default; 59--echo connection: default 60unlock tables; 61 62connection con2; 63--echo connection: con2 64--error ER_BAD_TABLE_ERROR 65--reap 66 67connection default; 68drop table t3; 69 70disconnect con1; 71disconnect con2; 72disconnect con3; 73 74# Clean up resources used in this test case. 75--disable_warnings 76SET DEBUG_SYNC= 'RESET'; 77--enable_warnings 78 79 80--echo # 81--echo # Basic test coverage for type-of-operation aware metadata locks. 82--echo # 83--disable_warnings 84drop table if exists t1, t2, t3; 85--enable_warnings 86connect(mdl_con1,localhost,root,,); 87connect(mdl_con2,localhost,root,,); 88connect(mdl_con3,localhost,root,,); 89connection default; 90set debug_sync= 'RESET'; 91create table t1 (c1 int); 92 93--echo # 94--echo # A) First let us check compatibility rules between differend kinds of 95--echo # type-of-operation aware metadata locks. 96--echo # Of course, these rules are already covered by the tests scattered 97--echo # across the test suite. But it still makes sense to have one place 98--echo # which covers all of them. 99--echo # 100 101--echo # 1) Acquire S (simple shared) lock on the table (by using HANDLER): 102--echo # 103handler t1 open; 104--echo # 105connection mdl_con1; 106--echo # Check that S, SH, SR and SW locks are compatible with it. 107handler t1 open t; 108handler t close; 109select column_name from information_schema.columns where 110 table_schema='test' and table_name='t1'; 111select count(*) from t1; 112insert into t1 values (1), (1); 113--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE 114--echo # which will fail when constructing .frm and thus obtaining SU metadata 115--echo # lock. 116--error ER_KEY_COLUMN_DOES_NOT_EXITS 117alter table t1 add index (not_exist); 118--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE 119--echo # which will fail during copying the table and thus obtaining SNW metadata 120--echo # lock. 121--error ER_DUP_ENTRY 122alter table t1 add primary key (c1); 123--echo # Check that X lock is incompatible with S lock. 124--echo # Sending: 125--send rename table t1 to t2; 126--echo # 127connection mdl_con2; 128--echo # Check that the above RENAME is blocked because of S lock. 129let $wait_condition= 130 select count(*) = 1 from information_schema.processlist 131 where state = "Waiting for table metadata lock" and 132 info = "rename table t1 to t2"; 133--source include/wait_condition.inc 134--echo # 135connection default; 136--echo # Unblock RENAME TABLE. 137handler t1 close; 138--echo # 139connection mdl_con1; 140--echo # Reaping RENAME TABLE. 141--reap 142--echo # Restore the original state of the things. 143rename table t2 to t1; 144--echo # 145connection default; 146handler t1 open; 147--echo # 148connection mdl_con1; 149--echo # Check that upgrade from SNW to X is blocked by presence of S lock. 150--echo # Sending: 151--send alter table t1 add column c2 int; 152--echo # 153connection mdl_con2; 154--echo # Check that the above ALTER TABLE is blocked because of S lock. 155let $wait_condition= 156 select count(*) = 1 from information_schema.processlist 157 where state = "Waiting for table metadata lock" and 158 info = "alter table t1 add column c2 int"; 159--source include/wait_condition.inc 160--echo # 161connection default; 162--echo # Unblock ALTER TABLE. 163handler t1 close; 164--echo # 165connection mdl_con1; 166--echo # Reaping ALTER TABLE. 167--reap 168--echo # Restore the original state of the things. 169alter table t1 drop column c2; 170--echo # 171connection default; 172--echo # 173--echo # 2) Acquire SH (shared high-priority) lock on the table. 174--echo # We have to involve DEBUG_SYNC facility for this as usually 175--echo # such kind of locks are short-lived. 176--echo # 177set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 178--echo # Sending: 179--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; 180--echo # 181connection mdl_con1; 182set debug_sync= 'now WAIT_FOR locked'; 183--echo # Check that S, SH, SR and SW locks are compatible with it. 184handler t1 open; 185handler t1 close; 186select column_name from information_schema.columns where 187 table_schema='test' and table_name='t1'; 188select count(*) from t1; 189insert into t1 values (1); 190--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE 191--echo # which will fail when constructing .frm and thus obtaining SU metadata 192--echo # lock. 193--error ER_KEY_COLUMN_DOES_NOT_EXITS 194alter table t1 add index (not_exist); 195--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE 196--echo # which will fail during copying the table and thus obtaining SNW metadata 197--echo # lock. 198--error ER_DUP_ENTRY 199alter table t1 add primary key (c1); 200--echo # Check that SNRW lock is compatible with SH lock. 201lock table t1 write; 202delete from t1 limit 1; 203unlock tables; 204--echo # Check that X lock is incompatible with SH lock. 205--echo # Sending: 206--send rename table t1 to t2; 207--echo # 208connection mdl_con2; 209--echo # Check that the above RENAME is blocked because of SH lock. 210let $wait_condition= 211 select count(*) = 1 from information_schema.processlist 212 where state = "Waiting for table metadata lock" and 213 info = "rename table t1 to t2"; 214--source include/wait_condition.inc 215--echo # Unblock RENAME TABLE. 216set debug_sync= 'now SIGNAL finish'; 217--echo # 218connection default; 219--echo # Reaping SELECT ... FROM I_S. 220--reap 221--echo # 222connection mdl_con1; 223--echo # Reaping RENAME TABLE. 224--reap 225--echo # Restore the original state of the things. 226rename table t2 to t1; 227--echo # 228connection default; 229set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 230--echo # Sending: 231--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; 232--echo # 233connection mdl_con1; 234set debug_sync= 'now WAIT_FOR locked'; 235--echo # Check that upgrade from SNW to X is blocked by presence of SH lock. 236--echo # Sending: 237--send alter table t1 add column c2 int; 238--echo # 239connection mdl_con2; 240--echo # Check that the above ALTER TABLE is blocked because of SH lock. 241let $wait_condition= 242 select count(*) = 1 from information_schema.processlist 243 where state = "Waiting for table metadata lock" and 244 info = "alter table t1 add column c2 int"; 245--source include/wait_condition.inc 246--echo # Unblock RENAME TABLE. 247set debug_sync= 'now SIGNAL finish'; 248--echo # 249connection default; 250--echo # Reaping SELECT ... FROM I_S. 251--reap 252--echo # 253connection mdl_con1; 254--echo # Reaping ALTER TABLE. 255--reap 256--echo # Restore the original state of the things. 257alter table t1 drop column c2; 258--echo # 259connection default; 260set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 261--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1'; 262--echo # 263connection mdl_con1; 264set debug_sync= 'now WAIT_FOR locked'; 265--echo # Check that upgrade from SNRW to X is blocked by presence of S lock. 266lock table t1 write; 267--echo # Sending: 268--send alter table t1 add column c2 int; 269--echo # 270connection mdl_con2; 271--echo # Check that the above upgrade of SNRW to X in ALTER TABLE is blocked 272--echo # because of S lock. 273let $wait_condition= 274 select count(*) = 1 from information_schema.processlist 275 where state = "Waiting for table metadata lock" and 276 info = "alter table t1 add column c2 int"; 277--source include/wait_condition.inc 278--echo # Unblock RENAME TABLE. 279set debug_sync= 'now SIGNAL finish'; 280--echo # 281connection default; 282--echo # Reaping SELECT ... FROM I_S. 283--reap 284--echo # 285connection mdl_con1; 286--echo # Reaping ALTER TABLE. 287--reap 288--echo # Restore the original state of the things. 289alter table t1 drop column c2; 290unlock tables; 291--echo # 292connection default; 293--echo # 294--echo # 295--echo # 3) Acquire SR lock on the table. 296--echo # 297--echo # 298begin; 299select count(*) from t1; 300--echo # 301connection mdl_con1; 302--echo # Check that S, SH, SR and SW locks are compatible with it. 303handler t1 open; 304handler t1 close; 305select column_name from information_schema.columns where 306 table_schema='test' and table_name='t1'; 307select count(*) from t1; 308insert into t1 values (1); 309--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE 310--echo # which will fail when constructing .frm and thus obtaining SU metadata 311--echo # lock. 312--error ER_KEY_COLUMN_DOES_NOT_EXITS 313alter table t1 add index (not_exist); 314--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE 315--echo # which will fail during copying the table and thus obtaining SNW metadata 316--echo # lock. 317--error ER_DUP_ENTRY 318alter table t1 add primary key (c1); 319--echo # Check that SNRW lock is not compatible with SR lock. 320--echo # Sending: 321--send lock table t1 write; 322--echo # 323connection default; 324--echo # Check that the above LOCK TABLES is blocked because of SR lock. 325let $wait_condition= 326 select count(*) = 1 from information_schema.processlist 327 where state = "Waiting for table metadata lock" and 328 info = "lock table t1 write"; 329--source include/wait_condition.inc 330--echo # Unblock LOCK TABLES. 331commit; 332--echo # 333connection mdl_con1; 334--echo # Reaping LOCK TABLES. 335--reap 336delete from t1 limit 1; 337unlock tables; 338--echo # 339connection default; 340begin; 341select count(*) from t1; 342--echo # 343connection mdl_con1; 344--echo # Check that X lock is incompatible with SR lock. 345--echo # Sending: 346--send rename table t1 to t2; 347--echo # 348connection mdl_con2; 349--echo # Check that the above RENAME is blocked because of SR lock. 350let $wait_condition= 351 select count(*) = 1 from information_schema.processlist 352 where state = "Waiting for table metadata lock" and 353 info = "rename table t1 to t2"; 354--source include/wait_condition.inc 355--echo # 356connection default; 357--echo # Unblock RENAME TABLE. 358commit; 359--echo # 360connection mdl_con1; 361--echo # Reaping RENAME TABLE. 362--reap 363--echo # Restore the original state of the things. 364rename table t2 to t1; 365--echo # 366connection default; 367begin; 368select count(*) from t1; 369--echo # 370connection mdl_con1; 371--echo # Check that upgrade from SNW to X is blocked by presence of SR lock. 372--echo # Sending: 373--send alter table t1 add column c2 int; 374--echo # 375connection mdl_con2; 376--echo # Check that the above ALTER TABLE is blocked because of SR lock. 377let $wait_condition= 378 select count(*) = 1 from information_schema.processlist 379 where state = "Waiting for table metadata lock" and 380 info = "alter table t1 add column c2 int"; 381--source include/wait_condition.inc 382--echo # 383connection default; 384--echo # Unblock ALTER TABLE. 385commit; 386--echo # 387connection mdl_con1; 388--echo # Reaping ALTER TABLE. 389--reap 390--echo # Restore the original state of the things. 391alter table t1 drop column c2; 392--echo # 393--echo # There is no need to check that upgrade from SNRW to X is blocked 394--echo # by presence of SR lock because SNRW is incompatible with SR anyway. 395--echo # 396--echo # 397connection default; 398--echo # 399--echo # 400--echo # 4) Acquire SW lock on the table. 401--echo # 402--echo # 403begin; 404insert into t1 values (1); 405--echo # 406connection mdl_con1; 407--echo # Check that S, SH, SR and SW locks are compatible with it. 408handler t1 open; 409handler t1 close; 410select column_name from information_schema.columns where 411 table_schema='test' and table_name='t1'; 412--echo # Disable result log to make test robust against 413--echo # effects of concurrent insert. 414--disable_result_log 415select * from t1; 416--enable_result_log 417insert into t1 values (1); 418--echo # Check that SU lock is compatible with it. To do this use ALTER TABLE 419--echo # which will fail when constructing .frm and thus obtaining SU metadata 420--echo # lock. 421--error ER_KEY_COLUMN_DOES_NOT_EXITS 422alter table t1 add index (not_exist); 423--echo # Check that SNW lock is not compatible with SW lock. 424--echo # Again we use ALTER TABLE which fails during copying 425--echo # the table to avoid upgrade of SNW -> X. 426--echo # Sending: 427--send alter table t1 add primary key (c1); 428--echo # 429connection default; 430--echo # Check that the above ALTER TABLE is blocked because of SW lock. 431let $wait_condition= 432 select count(*) = 1 from information_schema.processlist 433 where state = "Waiting for table metadata lock" and 434 info = "alter table t1 add primary key (c1)"; 435--source include/wait_condition.inc 436--echo # Unblock ALTER TABLE. 437commit; 438--echo # 439connection mdl_con1; 440--echo # Reaping ALTER TABLE. 441--error ER_DUP_ENTRY 442--reap 443--echo # 444connection default; 445begin; 446insert into t1 values (1); 447--echo # 448connection mdl_con1; 449--echo # Check that SNRW lock is not compatible with SW lock. 450--echo # Sending: 451--send lock table t1 write; 452--echo # 453connection default; 454--echo # Check that the above LOCK TABLES is blocked because of SW lock. 455let $wait_condition= 456 select count(*) = 1 from information_schema.processlist 457 where state = "Waiting for table metadata lock" and 458 info = "lock table t1 write"; 459--source include/wait_condition.inc 460--echo # Unblock LOCK TABLES. 461commit; 462--echo # 463connection mdl_con1; 464--echo # Reaping LOCK TABLES. 465--reap 466delete from t1 limit 2; 467unlock tables; 468--echo # 469connection default; 470begin; 471insert into t1 values (1); 472--echo # 473connection mdl_con1; 474--echo # Check that X lock is incompatible with SW lock. 475--echo # Sending: 476--send rename table t1 to t2; 477--echo # 478connection mdl_con2; 479--echo # Check that the above RENAME is blocked because of SW lock. 480let $wait_condition= 481 select count(*) = 1 from information_schema.processlist 482 where state = "Waiting for table metadata lock" and 483 info = "rename table t1 to t2"; 484--source include/wait_condition.inc 485--echo # 486connection default; 487--echo # Unblock RENAME TABLE. 488commit; 489--echo # 490connection mdl_con1; 491--echo # Reaping RENAME TABLE. 492--reap 493--echo # Restore the original state of the things. 494rename table t2 to t1; 495--echo # 496--echo # There is no need to check that upgrade from SNW/SNRW to X is 497--echo # blocked by presence of SW lock because SNW/SNRW is incompatible 498--echo # with SW anyway. 499--echo # 500--echo # 501connection default; 502--echo # 503--echo # 504--echo # 5) Acquire SU lock on the table. We have to use DEBUG_SYNC for 505--echo # this, to prevent SU from being immediately upgraded to X. 506--echo # 507set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; 508--echo # Sending: 509--send alter table t1 add primary key (c1); 510--echo # 511connection mdl_con1; 512set debug_sync= 'now WAIT_FOR locked'; 513--echo # Check that S, SH, SR and SW locks are compatible with it. 514handler t1 open; 515handler t1 close; 516select column_name from information_schema.columns where 517 table_schema='test' and table_name='t1'; 518select count(*) from t1; 519delete from t1 limit 1; 520--echo # Check that SU lock is incompatible with SU lock. 521--echo # Sending: 522--send alter table t1 add primary key (c1); 523--echo # 524connection mdl_con2; 525--echo # Check that the above ALTER is blocked because of SU lock. 526let $wait_condition= 527 select count(*) = 1 from information_schema.processlist 528 where state = "Waiting for table metadata lock" and 529 info = "alter table t1 add primary key (c1)"; 530--source include/wait_condition.inc 531--echo # Unblock ALTERs. 532set debug_sync= 'now SIGNAL finish'; 533--echo # 534connection default; 535--echo # Reaping first ALTER TABLE. 536--error ER_DUP_ENTRY 537--reap 538--echo # 539connection mdl_con1; 540--echo # Reaping another ALTER TABLE. 541--error ER_DUP_ENTRY 542--reap 543--echo # 544connection default; 545set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; 546--echo # Sending: 547--send alter table t1 add primary key (c1); 548--echo # 549connection mdl_con1; 550set debug_sync= 'now WAIT_FOR locked'; 551--echo # Check that SNRW lock is incompatible with SU lock. 552--echo # Sending: 553--send lock table t1 write; 554--echo # 555connection mdl_con2; 556--echo # Check that the above LOCK TABLES is blocked because of SU lock. 557let $wait_condition= 558 select count(*) = 1 from information_schema.processlist 559 where state = "Waiting for table metadata lock" and 560 info = "lock table t1 write"; 561--source include/wait_condition.inc 562--echo # Unblock ALTER and thus LOCK TABLES. 563set debug_sync= 'now SIGNAL finish'; 564--echo # 565connection default; 566--echo # Reaping ALTER TABLE. 567--error ER_DUP_ENTRY 568--reap 569--echo # 570connection mdl_con1; 571--echo # Reaping LOCK TABLES 572--reap 573insert into t1 values (1); 574unlock tables; 575--echo # 576connection default; 577set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; 578--echo # Sending: 579--send alter table t1 add primary key (c1); 580--echo # 581connection mdl_con1; 582set debug_sync= 'now WAIT_FOR locked'; 583--echo # Check that X lock is incompatible with SU lock. 584--echo # Sending: 585--send rename table t1 to t2; 586--echo # 587connection mdl_con2; 588--echo # Check that the above RENAME is blocked because of SU lock. 589let $wait_condition= 590 select count(*) = 1 from information_schema.processlist 591 where state = "Waiting for table metadata lock" and 592 info = "rename table t1 to t2"; 593--source include/wait_condition.inc 594--echo # Unblock ALTER and thus RENAME TABLE. 595set debug_sync= 'now SIGNAL finish'; 596--echo # 597connection default; 598--echo # Now we have ALTER TABLE with SU->SNW and RENAME TABLE with pending 599--echo # X-lock. In this case ALTER TABLE should be chosen as victim. 600--echo # Reaping ALTER TABLE. 601--error ER_LOCK_DEADLOCK 602--reap 603--echo # 604connection mdl_con1; 605--echo # Reaping RENAME TABLE 606--reap 607--echo # Revert back to original state of things. 608rename table t2 to t1; 609--echo # 610--echo # There is no need to check that upgrade from SNW/SNRW to X is 611--echo # blocked by presence of another SU lock because SNW/SNRW is 612--echo # incompatible with SU anyway. 613--echo # 614connection default; 615--echo # 616--echo # 617--echo # 6) Acquire SNW lock on the table. We have to use DEBUG_SYNC for 618--echo # this, to prevent SNW from being immediately upgraded to X. 619--echo # 620set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 621--echo # Sending: 622--send alter table t1 add primary key (c1), lock=shared, algorithm=copy; 623--echo # 624connection mdl_con1; 625set debug_sync= 'now WAIT_FOR locked'; 626--echo # Check that S, SH and SR locks are compatible with it. 627handler t1 open; 628handler t1 close; 629select column_name from information_schema.columns where 630 table_schema='test' and table_name='t1'; 631select count(*) from t1; 632--echo # Check that SW lock is incompatible with SNW lock. 633--echo # Sending: 634--send delete from t1 limit 2; 635--echo # 636connection mdl_con2; 637--echo # Check that the above DELETE is blocked because of SNW lock. 638let $wait_condition= 639 select count(*) = 1 from information_schema.processlist 640 where state = "Waiting for table metadata lock" and 641 info = "delete from t1 limit 2"; 642--source include/wait_condition.inc 643--echo # Unblock ALTER and thus DELETE. 644set debug_sync= 'now SIGNAL finish'; 645--echo # 646connection default; 647--echo # Reaping ALTER TABLE. 648--error ER_DUP_ENTRY 649--reap 650--echo # 651connection mdl_con1; 652--echo # Reaping DELETE. 653--reap 654--echo # 655connection default; 656set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 657--echo # Sending: 658--send alter table t1 add primary key (c1), lock=shared, algorithm=copy; 659--echo # 660connection mdl_con1; 661set debug_sync= 'now WAIT_FOR locked'; 662--echo # Check that SU lock is incompatible with SNW lock. 663--echo # Sending: 664--send alter table t1 add primary key (c1); 665--echo # 666connection mdl_con2; 667--echo # Check that the above ALTER is blocked because of SNW lock. 668let $wait_condition= 669 select count(*) = 1 from information_schema.processlist 670 where state = "Waiting for table metadata lock" and 671 info = "alter table t1 add primary key (c1)"; 672--source include/wait_condition.inc 673--echo # Unblock ALTERs. 674set debug_sync= 'now SIGNAL finish'; 675--echo # 676connection default; 677--echo # Reaping first ALTER TABLE. 678--error ER_DUP_ENTRY 679--reap 680--echo # 681connection mdl_con1; 682--echo # Reaping another ALTER TABLE. 683--error ER_DUP_ENTRY 684--reap 685--echo # 686--echo # Note that we can't easily check SNW vs SNW locks since 687--echo # SNW is only used by ALTER TABLE after upgrading from SU 688--echo # and SU is also incompatible with SNW. 689--echo # 690connection default; 691set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 692--echo # Sending: 693--send alter table t1 add primary key (c1), lock=shared, algorithm=copy; 694--echo # 695connection mdl_con1; 696set debug_sync= 'now WAIT_FOR locked'; 697--echo # Check that SNRW lock is incompatible with SNW lock. 698--echo # Sending: 699--send lock table t1 write; 700--echo # 701connection mdl_con2; 702--echo # Check that the above LOCK TABLES is blocked because of SNW lock. 703let $wait_condition= 704 select count(*) = 1 from information_schema.processlist 705 where state = "Waiting for table metadata lock" and 706 info = "lock table t1 write"; 707--source include/wait_condition.inc 708--echo # Unblock ALTER and thus LOCK TABLES. 709set debug_sync= 'now SIGNAL finish'; 710--echo # 711connection default; 712--echo # Reaping ALTER TABLE. 713--error ER_DUP_ENTRY 714--reap 715--echo # 716connection mdl_con1; 717--echo # Reaping LOCK TABLES 718--reap 719insert into t1 values (1); 720unlock tables; 721--echo # 722connection default; 723set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 724--echo # Sending: 725--send alter table t1 add primary key (c1), algorithm=copy, lock=shared; 726--echo # 727connection mdl_con1; 728set debug_sync= 'now WAIT_FOR locked'; 729--echo # Check that X lock is incompatible with SNW lock. 730--echo # Sending: 731--send rename table t1 to t2; 732--echo # 733connection mdl_con2; 734--echo # Check that the above RENAME is blocked because of SNW lock. 735let $wait_condition= 736 select count(*) = 1 from information_schema.processlist 737 where state = "Waiting for table metadata lock" and 738 info = "rename table t1 to t2"; 739--source include/wait_condition.inc 740--echo # Unblock ALTER and thus RENAME TABLE. 741set debug_sync= 'now SIGNAL finish'; 742--echo # 743connection default; 744--echo # Reaping ALTER TABLE. 745--error ER_DUP_ENTRY 746--reap 747--echo # 748connection mdl_con1; 749--echo # Reaping RENAME TABLE 750--reap 751--echo # Revert back to original state of things. 752rename table t2 to t1; 753--echo # 754--echo # There is no need to check that upgrade from SNW/SNRW to X is 755--echo # blocked by presence of another SNW lock because SNW/SNRW is 756--echo # incompatible with SNW anyway. 757--echo # 758connection default; 759--echo # 760--echo # 761--echo # 7) Acquire SNRW lock on the table. 762--echo # 763--echo # 764lock table t1 write; 765--echo # 766connection mdl_con1; 767--echo # Check that S and SH locks are compatible with it. 768select column_name from information_schema.columns where 769 table_schema='test' and table_name='t1'; 770--echo # Check that SR lock is incompatible with SNRW lock. 771--echo # Sending: 772--send select count(*) from t1; 773--echo # 774connection default; 775--echo # Check that the above SELECT is blocked because of SNRW lock. 776let $wait_condition= 777 select count(*) = 1 from information_schema.processlist 778 where state = "Waiting for table metadata lock" and 779 info = "select count(*) from t1"; 780--source include/wait_condition.inc 781--echo # Unblock SELECT. 782unlock tables; 783--echo # 784connection mdl_con1; 785--echo # Reaping SELECT. 786--reap 787--echo # 788connection default; 789lock table t1 write; 790--echo # 791connection mdl_con1; 792--echo # Check that SW lock is incompatible with SNRW lock. 793--echo # Sending: 794--send delete from t1 limit 1; 795--echo # 796connection default; 797--echo # Check that the above DELETE is blocked because of SNRW lock. 798let $wait_condition= 799 select count(*) = 1 from information_schema.processlist 800 where state = "Waiting for table metadata lock" and 801 info = "delete from t1 limit 1"; 802--source include/wait_condition.inc 803--echo # Unblock DELETE. 804unlock tables; 805--echo # 806connection mdl_con1; 807--echo # Reaping DELETE. 808--reap 809--echo # 810connection default; 811lock table t1 write; 812--echo # 813connection mdl_con1; 814--echo # Check that SU lock is incompatible with SNRW lock. 815--echo # Sending: 816--send alter table t1 add primary key (c1); 817--echo # 818connection default; 819--echo # Check that the above ALTER is blocked because of SNRW lock. 820let $wait_condition= 821 select count(*) = 1 from information_schema.processlist 822 where state = "Waiting for table metadata lock" and 823 info = "alter table t1 add primary key (c1)"; 824--source include/wait_condition.inc 825--echo # Unblock ALTER. 826unlock tables; 827--echo # 828connection mdl_con1; 829--echo # Reaping ALTER TABLE. 830--error ER_DUP_ENTRY 831--reap 832--echo # 833--echo # Note that we can't easily check SNW vs SNRW locks since 834--echo # SNW is only used by ALTER TABLE after upgrading from SU 835--echo # and SU is also incompatible with SNRW. 836--echo # 837connection default; 838lock table t1 write; 839--echo # 840connection mdl_con1; 841--echo # Check that SNRW lock is incompatible with SNRW lock. 842--echo # Sending: 843--send lock table t1 write; 844--echo # 845connection default; 846--echo # Check that the above LOCK TABLES is blocked because of SNRW lock. 847let $wait_condition= 848 select count(*) = 1 from information_schema.processlist 849 where state = "Waiting for table metadata lock" and 850 info = "lock table t1 write"; 851--source include/wait_condition.inc 852--echo # Unblock waiting LOCK TABLES. 853unlock tables; 854--echo # 855connection mdl_con1; 856--echo # Reaping LOCK TABLES 857--reap 858insert into t1 values (1); 859unlock tables; 860--echo # 861connection default; 862lock table t1 write; 863--echo # 864connection mdl_con1; 865--echo # Check that X lock is incompatible with SNRW lock. 866--echo # Sending: 867--send rename table t1 to t2; 868--echo # 869connection default; 870--echo # Check that the above RENAME is blocked because of SNRW lock. 871let $wait_condition= 872 select count(*) = 1 from information_schema.processlist 873 where state = "Waiting for table metadata lock" and 874 info = "rename table t1 to t2"; 875--source include/wait_condition.inc 876--echo # Unblock RENAME TABLE 877unlock tables; 878--echo # 879connection mdl_con1; 880--echo # Reaping RENAME TABLE 881--reap 882--echo # Revert back to original state of things. 883rename table t2 to t1; 884--echo # 885--echo # There is no need to check that upgrade from SNW/SNRW to X is 886--echo # blocked by presence of another SNRW lock because SNW/SNRW is 887--echo # incompatible with SNRW anyway. 888--echo # 889connection default; 890--echo # 891--echo # 892--echo # 8) Now do the same round of tests for X lock. We use additional 893--echo # table to get long-lived lock of this type. 894--echo # 895create table t2 (c1 int); 896--echo # 897connection mdl_con2; 898--echo # Take a lock on t2, so RENAME TABLE t1 TO t2 will get blocked 899--echo # after acquiring X lock on t1. 900lock tables t2 read; 901--echo # 902connection default; 903--echo # Sending: 904--send rename table t1 to t2; 905--echo # 906connection mdl_con1; 907--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. 908let $wait_condition= 909select count(*) = 1 from information_schema.processlist 910where state = "Waiting for table metadata lock" and 911 info = "rename table t1 to t2"; 912--source include/wait_condition.inc 913--echo # Check that S lock in incompatible with X lock. 914--echo # Sending: 915--send handler t1 open; 916--echo # 917connection mdl_con2; 918--echo # Check that the above HANDLER statement is blocked because of X lock. 919let $wait_condition= 920select count(*) = 1 from information_schema.processlist 921where state = "Waiting for table metadata lock" and 922 info = "handler t1 open"; 923--source include/wait_condition.inc 924--echo # Unblock RENAME TABLE 925unlock tables; 926--echo # 927connection default; 928--echo # Reaping RENAME TABLE. 929--error ER_TABLE_EXISTS_ERROR 930--reap 931--echo # 932connection mdl_con1; 933--echo # Reaping HANDLER. 934--reap 935handler t1 close; 936--echo # 937connection mdl_con2; 938--echo # Prepare for blocking RENAME TABLE. 939lock tables t2 read; 940--echo # 941connection default; 942--echo # Sending: 943--send rename table t1 to t2; 944--echo # 945connection mdl_con1; 946--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. 947let $wait_condition= 948select count(*) = 1 from information_schema.processlist 949where state = "Waiting for table metadata lock" and 950 info = "rename table t1 to t2"; 951--source include/wait_condition.inc 952--echo # Check that SH lock in incompatible with X lock. 953--echo # Sending: 954--send select column_name from information_schema.columns where table_schema='test' and table_name='t1'; 955--echo # 956connection mdl_con2; 957--echo # Check that the above SELECT ... FROM I_S ... statement is blocked 958--echo # because of X lock. 959let $wait_condition= 960select count(*) = 1 from information_schema.processlist 961where state = "Waiting for table metadata lock" and 962 info like "select column_name from information_schema.columns%"; 963--source include/wait_condition.inc 964--echo # Unblock RENAME TABLE 965unlock tables; 966--echo # 967connection default; 968--echo # Reaping RENAME TABLE. 969--error ER_TABLE_EXISTS_ERROR 970--reap 971--echo # 972connection mdl_con1; 973--echo # Reaping SELECT ... FROM I_S. 974--reap 975--echo # 976connection mdl_con2; 977--echo # Prepare for blocking RENAME TABLE. 978lock tables t2 read; 979--echo # 980connection default; 981--echo # Sending: 982--send rename table t1 to t2; 983--echo # 984connection mdl_con1; 985--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. 986let $wait_condition= 987select count(*) = 1 from information_schema.processlist 988where state = "Waiting for table metadata lock" and 989 info = "rename table t1 to t2"; 990--source include/wait_condition.inc 991--echo # Check that SR lock in incompatible with X lock. 992--echo # Sending: 993--send select count(*) from t1; 994--echo # 995connection mdl_con2; 996--echo # Check that the above SELECT statement is blocked 997--echo # because of X lock. 998let $wait_condition= 999select count(*) = 1 from information_schema.processlist 1000where state = "Waiting for table metadata lock" and 1001 info = "select count(*) from t1"; 1002--source include/wait_condition.inc 1003--echo # Unblock RENAME TABLE 1004unlock tables; 1005--echo # 1006connection default; 1007--echo # Reaping RENAME TABLE. 1008--error ER_TABLE_EXISTS_ERROR 1009--reap 1010--echo # 1011connection mdl_con1; 1012--echo # Reaping SELECT. 1013--reap 1014--echo # 1015connection mdl_con2; 1016--echo # Prepare for blocking RENAME TABLE. 1017lock tables t2 read; 1018--echo # 1019connection default; 1020--echo # Sending: 1021--send rename table t1 to t2; 1022--echo # 1023connection mdl_con1; 1024--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. 1025let $wait_condition= 1026select count(*) = 1 from information_schema.processlist 1027where state = "Waiting for table metadata lock" and 1028 info = "rename table t1 to t2"; 1029--source include/wait_condition.inc 1030--echo # Check that SW lock in incompatible with X lock. 1031--echo # Sending: 1032--send delete from t1 limit 1; 1033--echo # 1034connection mdl_con2; 1035--echo # Check that the above DELETE statement is blocked 1036--echo # because of X lock. 1037let $wait_condition= 1038select count(*) = 1 from information_schema.processlist 1039where state = "Waiting for table metadata lock" and 1040 info = "delete from t1 limit 1"; 1041--source include/wait_condition.inc 1042--echo # Unblock RENAME TABLE 1043unlock tables; 1044--echo # 1045connection default; 1046--echo # Reaping RENAME TABLE. 1047--error ER_TABLE_EXISTS_ERROR 1048--reap 1049--echo # 1050connection mdl_con1; 1051--echo # Reaping DELETE. 1052--reap 1053--echo # 1054connection mdl_con2; 1055--echo # Prepare for blocking RENAME TABLE. 1056lock tables t2 read; 1057--echo # 1058connection default; 1059--echo # Sending: 1060--send rename table t1 to t2; 1061--echo # 1062connection mdl_con1; 1063--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. 1064let $wait_condition= 1065select count(*) = 1 from information_schema.processlist 1066where state = "Waiting for table metadata lock" and 1067 info = "rename table t1 to t2"; 1068--source include/wait_condition.inc 1069--echo # Check that SU lock is incompatible with X lock. 1070--echo # Sending: 1071--send alter table t1 add primary key (c1); 1072--echo # 1073connection mdl_con2; 1074--echo # Check that the above ALTER statement is blocked 1075--echo # because of X lock. 1076let $wait_condition= 1077select count(*) = 1 from information_schema.processlist 1078where state = "Waiting for table metadata lock" and 1079 info = "alter table t1 add primary key (c1)"; 1080--source include/wait_condition.inc 1081--echo # Unblock RENAME TABLE 1082unlock tables; 1083--echo # 1084connection default; 1085--echo # Reaping RENAME TABLE 1086--error ER_TABLE_EXISTS_ERROR 1087--reap 1088--echo # 1089connection mdl_con1; 1090--echo # Reaping ALTER. 1091--error ER_DUP_ENTRY 1092--reap 1093--echo # 1094--echo # Note that we can't easily check SNW vs X locks since 1095--echo # SNW is only used by ALTER TABLE after upgrading from SU 1096--echo # and SU is also incompatible with X. 1097--echo # 1098connection mdl_con2; 1099--echo # Prepare for blocking RENAME TABLE. 1100lock tables t2 read; 1101--echo # 1102connection default; 1103--echo # Sending: 1104--send rename table t1 to t2; 1105--echo # 1106connection mdl_con1; 1107--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. 1108let $wait_condition= 1109select count(*) = 1 from information_schema.processlist 1110where state = "Waiting for table metadata lock" and 1111 info = "rename table t1 to t2"; 1112--source include/wait_condition.inc 1113--echo # Check that SNRW lock is incompatible with X lock. 1114--echo # Sending: 1115--send lock table t1 write; 1116--echo # 1117connection mdl_con2; 1118--echo # Check that the above LOCK TABLE statement is blocked 1119--echo # because of X lock. 1120let $wait_condition= 1121select count(*) = 1 from information_schema.processlist 1122where state = "Waiting for table metadata lock" and 1123 info = "lock table t1 write"; 1124--source include/wait_condition.inc 1125--echo # Unblock RENAME TABLE 1126unlock tables; 1127--echo # 1128connection default; 1129--echo # Reaping RENAME TABLE 1130--error ER_TABLE_EXISTS_ERROR 1131--reap 1132--echo # 1133connection mdl_con1; 1134--echo # Reaping LOCK TABLE. 1135--reap 1136unlock tables; 1137--echo # 1138connection mdl_con2; 1139--echo # Prepare for blocking RENAME TABLE. 1140lock tables t2 read; 1141--echo # 1142connection default; 1143--echo # Sending: 1144--send rename table t1 to t2; 1145--echo # 1146connection mdl_con1; 1147--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2. 1148let $wait_condition= 1149select count(*) = 1 from information_schema.processlist 1150where state = "Waiting for table metadata lock" and 1151 info = "rename table t1 to t2"; 1152--source include/wait_condition.inc 1153--echo # Check that X lock is incompatible with X lock. 1154--echo # Sending: 1155--send rename table t1 to t3; 1156--echo # 1157connection mdl_con2; 1158--echo # Check that the above RENAME statement is blocked 1159--echo # because of X lock. 1160let $wait_condition= 1161select count(*) = 1 from information_schema.processlist 1162where state = "Waiting for table metadata lock" and 1163 info = "rename table t1 to t3"; 1164--source include/wait_condition.inc 1165--echo # Unblock RENAME TABLE 1166unlock tables; 1167--echo # 1168connection default; 1169--echo # Reaping RENAME TABLE 1170--error ER_TABLE_EXISTS_ERROR 1171--reap 1172--echo # 1173connection mdl_con1; 1174--echo # Reaping RENAME. 1175--reap 1176rename table t3 to t1; 1177 1178--echo # 1179--echo # B) Now let us test compatibility in cases when both locks 1180--echo # are pending. I.e. let us test rules for priorities between 1181--echo # different types of metadata locks. 1182--echo # 1183--echo # Note: No tests for pending SU lock as this lock requires 1184--echo # even stronger active or pending lock. 1185--echo # 1186 1187--echo # 1188connection mdl_con2; 1189--echo # 1190--echo # 1) Check compatibility for pending SNW lock. 1191--echo # 1192--echo # Acquire SW lock in order to create pending SNW lock later. 1193begin; 1194insert into t1 values (1); 1195--echo # 1196connection default; 1197--echo # Add pending SNW lock. 1198--echo # Sending: 1199--send alter table t1 add primary key (c1); 1200--echo # 1201connection mdl_con1; 1202--echo # Check that ALTER TABLE is waiting with pending SNW lock. 1203let $wait_condition= 1204select count(*) = 1 from information_schema.processlist 1205where state = "Waiting for table metadata lock" and 1206 info = "alter table t1 add primary key (c1)"; 1207--source include/wait_condition.inc 1208--echo # Check that S, SH and SR locks are compatible with pending SNW 1209handler t1 open t; 1210handler t close; 1211select column_name from information_schema.columns where 1212 table_schema='test' and table_name='t1'; 1213select count(*) from t1; 1214--echo # Check that SW is incompatible with pending SNW 1215--echo # Sending: 1216--send delete from t1 limit 1; 1217--echo # 1218connection mdl_con2; 1219--echo # Check that the above DELETE is blocked because of pending SNW lock. 1220let $wait_condition= 1221select count(*) = 1 from information_schema.processlist 1222where state = "Waiting for table metadata lock" and 1223 info = "delete from t1 limit 1"; 1224--source include/wait_condition.inc 1225--echo # Unblock ALTER TABLE. 1226commit; 1227--echo # 1228connection default; 1229--echo # Reaping ALTER. 1230--error ER_DUP_ENTRY 1231--reap 1232--echo # 1233connection mdl_con1; 1234--echo # Reaping DELETE. 1235--reap 1236--echo # 1237--echo # We can't do similar check for SNW, SNRW and X locks because 1238--echo # they will also be blocked by active SW lock. 1239--echo # 1240--echo # 1241connection mdl_con2; 1242--echo # 1243--echo # 2) Check compatibility for pending SNRW lock. 1244--echo # 1245--echo # Acquire SR lock in order to create pending SNRW lock. 1246begin; 1247select count(*) from t1; 1248--echo # 1249connection default; 1250--echo # Add pending SNRW lock. 1251--echo # Sending: 1252--send lock table t1 write; 1253--echo # 1254connection mdl_con1; 1255--echo # Check that LOCK TABLE is waiting with pending SNRW lock. 1256let $wait_condition= 1257select count(*) = 1 from information_schema.processlist 1258where state = "Waiting for table metadata lock" and 1259 info = "lock table t1 write"; 1260--source include/wait_condition.inc 1261--echo # Check that S and SH locks are compatible with pending SNRW 1262select column_name from information_schema.columns where 1263 table_schema='test' and table_name='t1'; 1264--echo # Check that SR is incompatible with pending SNRW 1265--echo # Sending: 1266--send select count(*) from t1; 1267--echo # 1268connection mdl_con2; 1269--echo # Check that the above SELECT is blocked because of pending SNRW lock. 1270let $wait_condition= 1271select count(*) = 1 from information_schema.processlist 1272where state = "Waiting for table metadata lock" and 1273 info = "select count(*) from t1"; 1274--source include/wait_condition.inc 1275--echo # Unblock LOCK TABLE. 1276commit; 1277--echo # 1278connection default; 1279--echo # Reaping LOCK TABLE. 1280--reap 1281unlock tables; 1282--echo # 1283connection mdl_con1; 1284--echo # Reaping SELECT. 1285--reap 1286--echo # Restore pending SNRW lock. 1287--echo # 1288connection mdl_con2; 1289begin; 1290select count(*) from t1; 1291--echo # 1292connection default; 1293--echo # Sending: 1294--send lock table t1 write; 1295--echo # 1296connection mdl_con1; 1297--echo # Check that LOCK TABLE is waiting with pending SNRW lock. 1298let $wait_condition= 1299select count(*) = 1 from information_schema.processlist 1300where state = "Waiting for table metadata lock" and 1301 info = "lock table t1 write"; 1302--source include/wait_condition.inc 1303--echo # Check that SW is incompatible with pending SNRW 1304--echo # Sending: 1305--send insert into t1 values (1); 1306--echo # 1307connection mdl_con2; 1308--echo # Check that the above INSERT is blocked because of pending SNRW lock. 1309let $wait_condition= 1310select count(*) = 1 from information_schema.processlist 1311where state = "Waiting for table metadata lock" and 1312 info = "insert into t1 values (1)"; 1313--source include/wait_condition.inc 1314--echo # Unblock LOCK TABLE. 1315commit; 1316--echo # 1317connection default; 1318--echo # Reaping LOCK TABLE. 1319--reap 1320unlock tables; 1321--echo # 1322connection mdl_con1; 1323--echo # Reaping INSERT. 1324--reap 1325--echo # Restore pending SNRW lock. 1326--echo # 1327connection mdl_con2; 1328begin; 1329select count(*) from t1; 1330--echo # 1331connection default; 1332--echo # Sending: 1333--send lock table t1 write; 1334--echo # 1335connection mdl_con1; 1336--echo # Check that LOCK TABLE is waiting with pending SNRW lock. 1337let $wait_condition= 1338select count(*) = 1 from information_schema.processlist 1339where state = "Waiting for table metadata lock" and 1340 info = "lock table t1 write"; 1341--source include/wait_condition.inc 1342--echo # Check that SNW is compatible with pending SNRW 1343--echo # So ALTER TABLE statements are not starved by LOCK TABLEs. 1344--error ER_DUP_ENTRY 1345alter table t1 add primary key (c1); 1346--echo # 1347connection mdl_con2; 1348--echo # Unblock LOCK TABLE. 1349commit; 1350--echo # 1351connection default; 1352--echo # Reaping LOCK TABLE. 1353--reap 1354unlock tables; 1355--echo # 1356--echo # We can't do similar check for SNRW and X locks because 1357--echo # they will also be blocked by active SR lock. 1358--echo # 1359--echo # 1360connection mdl_con2; 1361--echo # 1362--echo # 3) Check compatibility for pending X lock. 1363--echo # 1364--echo # Acquire SR lock in order to create pending X lock. 1365begin; 1366select count(*) from t1; 1367--echo # 1368connection default; 1369--echo # Add pending X lock. 1370--echo # Sending: 1371--send rename table t1 to t2; 1372--echo # 1373connection mdl_con1; 1374--echo # Check that RENAME TABLE is waiting with pending X lock. 1375let $wait_condition= 1376select count(*) = 1 from information_schema.processlist 1377where state = "Waiting for table metadata lock" and 1378 info = "rename table t1 to t2"; 1379--source include/wait_condition.inc 1380--echo # Check that SH locks are compatible with pending X 1381select column_name from information_schema.columns where 1382 table_schema='test' and table_name='t1'; 1383--echo # Check that S is incompatible with pending X 1384--echo # Sending: 1385--send handler t1 open; 1386--echo # 1387connection mdl_con2; 1388--echo # Check that the above HANDLER OPEN is blocked because of pending X lock. 1389let $wait_condition= 1390select count(*) = 1 from information_schema.processlist 1391where state = "Waiting for table metadata lock" and 1392 info = "handler t1 open"; 1393--source include/wait_condition.inc 1394--echo # Unblock RENAME TABLE. 1395commit; 1396--echo # 1397connection default; 1398--echo # Reaping RENAME TABLE. 1399--error ER_TABLE_EXISTS_ERROR 1400--reap 1401--echo # 1402connection mdl_con1; 1403--echo # Reaping HANDLER t1 OPEN. 1404--reap 1405handler t1 close; 1406--echo # Restore pending X lock. 1407--echo # 1408connection mdl_con2; 1409begin; 1410select count(*) from t1; 1411--echo # 1412connection default; 1413--echo # Add pending X lock. 1414--echo # Sending: 1415--send rename table t1 to t2; 1416--echo # 1417connection mdl_con1; 1418--echo # Check that RENAME TABLE is waiting with pending X lock. 1419let $wait_condition= 1420select count(*) = 1 from information_schema.processlist 1421where state = "Waiting for table metadata lock" and 1422 info = "rename table t1 to t2"; 1423--source include/wait_condition.inc 1424--echo # Check that SR is incompatible with pending X 1425--echo # Sending: 1426--send select count(*) from t1; 1427--echo # 1428connection mdl_con2; 1429--echo # Check that the above SELECT is blocked because of pending X lock. 1430let $wait_condition= 1431select count(*) = 1 from information_schema.processlist 1432where state = "Waiting for table metadata lock" and 1433 info = "select count(*) from t1"; 1434--source include/wait_condition.inc 1435--echo # Unblock RENAME TABLE. 1436commit; 1437--echo # 1438connection default; 1439--echo # Reaping RENAME TABLE. 1440--error ER_TABLE_EXISTS_ERROR 1441--reap 1442--echo # 1443connection mdl_con1; 1444--echo # Reaping SELECT. 1445--reap 1446--echo # Restore pending X lock. 1447--echo # 1448connection mdl_con2; 1449begin; 1450select count(*) from t1; 1451--echo # 1452connection default; 1453--echo # Add pending X lock. 1454--echo # Sending: 1455--send rename table t1 to t2; 1456--echo # 1457connection mdl_con1; 1458--echo # Check that RENAME TABLE is waiting with pending X lock. 1459let $wait_condition= 1460select count(*) = 1 from information_schema.processlist 1461where state = "Waiting for table metadata lock" and 1462 info = "rename table t1 to t2"; 1463--source include/wait_condition.inc 1464--echo # Check that SW is incompatible with pending X 1465--echo # Sending: 1466--send delete from t1 limit 1; 1467--echo # 1468connection mdl_con2; 1469--echo # Check that the above DELETE is blocked because of pending X lock. 1470let $wait_condition= 1471select count(*) = 1 from information_schema.processlist 1472where state = "Waiting for table metadata lock" and 1473 info = "delete from t1 limit 1"; 1474--source include/wait_condition.inc 1475--echo # Unblock RENAME TABLE. 1476commit; 1477--echo # 1478connection default; 1479--echo # Reaping RENAME TABLE. 1480--error ER_TABLE_EXISTS_ERROR 1481--reap 1482--echo # 1483connection mdl_con1; 1484--echo # Reaping DELETE. 1485--reap 1486--echo # Restore pending X lock. 1487--echo # 1488connection mdl_con2; 1489begin; 1490select count(*) from t1; 1491--echo # 1492connection default; 1493--echo # Add pending X lock. 1494--echo # Sending: 1495--send rename table t1 to t2; 1496--echo # 1497connection mdl_con1; 1498--echo # Check that RENAME TABLE is waiting with pending X lock. 1499let $wait_condition= 1500select count(*) = 1 from information_schema.processlist 1501where state = "Waiting for table metadata lock" and 1502 info = "rename table t1 to t2"; 1503--source include/wait_condition.inc 1504--echo # Check that SNW is incompatible with pending X 1505--echo # Sending: 1506--send alter table t1 add primary key (c1); 1507--echo # 1508connection mdl_con2; 1509--echo # Check that the above ALTER TABLE is blocked because of pending X lock. 1510let $wait_condition= 1511select count(*) = 1 from information_schema.processlist 1512where state = "Waiting for table metadata lock" and 1513 info = "alter table t1 add primary key (c1)"; 1514--source include/wait_condition.inc 1515--echo # Unblock RENAME TABLE. 1516commit; 1517--echo # 1518connection default; 1519--echo # Reaping RENAME TABLE. 1520--error ER_TABLE_EXISTS_ERROR 1521--reap 1522--echo # 1523connection mdl_con1; 1524--echo # Reaping ALTER TABLE. 1525--error ER_DUP_ENTRY 1526--reap 1527--echo # Restore pending X lock. 1528--echo # 1529connection mdl_con2; 1530handler t1 open; 1531--echo # 1532connection default; 1533--echo # Add pending X lock. 1534--echo # Sending: 1535--send rename table t1 to t2; 1536--echo # 1537connection mdl_con1; 1538--echo # Check that RENAME TABLE is waiting with pending X lock. 1539let $wait_condition= 1540select count(*) = 1 from information_schema.processlist 1541where state = "Waiting for table metadata lock" and 1542 info = "rename table t1 to t2"; 1543--source include/wait_condition.inc 1544--echo # Check that SNRW is incompatible with pending X 1545--echo # Sending: 1546--send lock table t1 write; 1547--echo # 1548connection mdl_con3; 1549--echo # Check that the above LOCK TABLES is blocked because of pending X lock. 1550let $wait_condition= 1551select count(*) = 1 from information_schema.processlist 1552where state = "Waiting for table metadata lock" and 1553 info = "lock table t1 write"; 1554--source include/wait_condition.inc 1555--echo # 1556connection mdl_con2; 1557--echo # Unblock RENAME TABLE. 1558handler t1 close; 1559--echo # 1560connection default; 1561--echo # Reaping RENAME TABLE. 1562--error ER_TABLE_EXISTS_ERROR 1563--reap 1564--echo # 1565connection mdl_con1; 1566--echo # Reaping LOCK TABLES. 1567--reap 1568unlock tables; 1569--echo # 1570connection default; 1571 1572--echo # 1573--echo # 1574--echo # C) Now let us test how type-of-operation locks are handled in 1575--echo # transactional context. Obviously we are mostly interested 1576--echo # in conflicting types of locks. 1577--echo # 1578--echo # Note: No tests for active/pending SU lock since 1579--echo # ALTER TABLE is in its own transaction. 1580--echo # 1581 1582--echo # 1583--echo # 1) Let us check how various locks used within transactional 1584--echo # context interact with active/pending SNW lock. 1585--echo # 1586--echo # We start with case when we are acquiring lock on the table 1587--echo # which was not used in the transaction before. 1588begin; 1589select count(*) from t1; 1590--echo # 1591connection mdl_con1; 1592--echo # Create an active SNW lock on t2. 1593--echo # We have to use DEBUG_SYNC facility as otherwise SNW lock 1594--echo # will be immediately released (or upgraded to X lock). 1595insert into t2 values (1), (1); 1596set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 1597--echo # Sending: 1598--send alter table t2 add primary key (c1), algorithm=copy, lock=shared; 1599--echo # 1600connection default; 1601set debug_sync= 'now WAIT_FOR locked'; 1602--echo # SR lock should be acquired without any waiting. 1603select count(*) from t2; 1604commit; 1605--echo # Now let us check that we will wait in case of SW lock. 1606begin; 1607select count(*) from t1; 1608--echo # Sending: 1609--send insert into t2 values (1); 1610--echo # 1611connection mdl_con2; 1612--echo # Check that the above INSERT is blocked. 1613let $wait_condition= 1614select count(*) = 1 from information_schema.processlist 1615where state = "Waiting for table metadata lock" and 1616 info = "insert into t2 values (1)"; 1617--source include/wait_condition.inc 1618--echo # Unblock ALTER TABLE and thus INSERT. 1619set debug_sync= 'now SIGNAL finish'; 1620--echo # 1621connection mdl_con1; 1622--echo # Reap ALTER TABLE. 1623--error ER_DUP_ENTRY 1624--reap 1625--echo # 1626connection default; 1627--echo # Reap INSERT. 1628--reap 1629commit; 1630--echo # 1631--echo # Now let us see what happens when we are acquiring lock on the table 1632--echo # which is already used in transaction. 1633--echo # 1634--echo # *) First, case when transaction which has SR lock on the table also 1635--echo # locked in SNW mode acquires yet another SR lock and then tries 1636--echo # to acquire SW lock. 1637begin; 1638select count(*) from t1; 1639--echo # 1640connection mdl_con1; 1641--echo # Create an active SNW lock on t1. 1642set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 1643--echo # Sending: 1644--send alter table t1 add primary key (c1), algorithm=copy, lock=shared; 1645--echo # 1646connection default; 1647set debug_sync= 'now WAIT_FOR locked'; 1648--echo # We should still be able to get SR lock without waiting. 1649select count(*) from t1; 1650--echo # Since the above ALTER TABLE is not upgrading SNW lock to X by waiting 1651--echo # for SW lock we won't create deadlock. 1652--echo # So the below INSERT should not end-up with ER_LOCK_DEADLOCK error. 1653--echo # Sending: 1654--send insert into t1 values (1); 1655--echo # 1656connection mdl_con2; 1657--echo # Check that the above INSERT is blocked. 1658let $wait_condition= 1659select count(*) = 1 from information_schema.processlist 1660where state = "Waiting for table metadata lock" and 1661 info = "insert into t1 values (1)"; 1662--source include/wait_condition.inc 1663--echo # Unblock ALTER TABLE and thus INSERT. 1664set debug_sync= 'now SIGNAL finish'; 1665--echo # 1666connection mdl_con1; 1667--echo # Reap ALTER TABLE. 1668--error ER_DUP_ENTRY 1669--reap 1670--echo # 1671connection default; 1672--echo # Reap INSERT. 1673--reap 1674commit; 1675--echo # 1676--echo # **) Now test in which transaction that has SW lock on the table 1677--echo # against which there is pending SNW lock acquires SR and SW 1678--echo # locks on this table. 1679--echo # 1680begin; 1681insert into t1 values (1); 1682--echo # 1683connection mdl_con1; 1684--echo # Create pending SNW lock on t1. 1685--echo # Sending: 1686--send alter table t1 add primary key (c1); 1687--echo # 1688connection default; 1689--echo # Wait until ALTER TABLE starts waiting for SNW lock. 1690let $wait_condition= 1691select count(*) = 1 from information_schema.processlist 1692where state = "Waiting for table metadata lock" and 1693 info = "alter table t1 add primary key (c1)"; 1694--source include/wait_condition.inc 1695--echo # We should still be able to get both SW and SR locks without waiting. 1696select count(*) from t1; 1697delete from t1 limit 1; 1698--echo # Unblock ALTER TABLE. 1699commit; 1700--echo # 1701connection mdl_con1; 1702--echo # Reap ALTER TABLE. 1703--error ER_DUP_ENTRY 1704--reap 1705--echo # 1706connection default; 1707--echo # 1708--echo # 2) Now similar tests for active SNW lock which is being upgraded 1709--echo # to X lock. 1710--echo # 1711--echo # Again we start with case when we are acquiring lock on the 1712--echo # table which was not used in the transaction before. 1713begin; 1714select count(*) from t1; 1715--echo # 1716connection mdl_con2; 1717--echo # Start transaction which will prevent SNW -> X upgrade from 1718--echo # completing immediately. 1719begin; 1720select count(*) from t2; 1721--echo # 1722connection mdl_con1; 1723--echo # Create SNW lock pending upgrade to X on t2. 1724--echo # Sending: 1725--send alter table t2 add column c2 int; 1726--echo # 1727connection default; 1728--echo # Wait until ALTER TABLE starts waiting X lock. 1729let $wait_condition= 1730select count(*) = 1 from information_schema.processlist 1731where state = "Waiting for table metadata lock" and 1732 info = "alter table t2 add column c2 int"; 1733--source include/wait_condition.inc 1734--echo # Check that attempt to acquire SR lock on t2 causes waiting. 1735--echo # Sending: 1736--send select count(*) from t2; 1737--echo # 1738connection mdl_con2; 1739--echo # Check that the above SELECT is blocked. 1740let $wait_condition= 1741select count(*) = 1 from information_schema.processlist 1742where state = "Waiting for table metadata lock" and 1743 info = "select count(*) from t2"; 1744--source include/wait_condition.inc 1745--echo # Unblock ALTER TABLE. 1746commit; 1747--echo # 1748connection mdl_con1; 1749--echo # Reap ALTER TABLE. 1750--reap 1751--echo # 1752connection default; 1753--echo # Reap SELECT. 1754--reap 1755commit; 1756--echo # Do similar check for SW lock. 1757begin; 1758select count(*) from t1; 1759--echo # 1760connection mdl_con2; 1761--echo # Start transaction which will prevent SNW -> X upgrade from 1762--echo # completing immediately. 1763begin; 1764select count(*) from t2; 1765--echo # 1766connection mdl_con1; 1767--echo # Create SNW lock pending upgrade to X on t2. 1768--echo # Sending: 1769--send alter table t2 drop column c2; 1770--echo # 1771connection default; 1772--echo # Wait until ALTER TABLE starts waiting X lock. 1773let $wait_condition= 1774select count(*) = 1 from information_schema.processlist 1775where state = "Waiting for table metadata lock" and 1776 info = "alter table t2 drop column c2"; 1777--source include/wait_condition.inc 1778--echo # Check that attempt to acquire SW lock on t2 causes waiting. 1779--echo # Sending: 1780--send insert into t2 values (1); 1781--echo # 1782connection mdl_con2; 1783--echo # Check that the above INSERT is blocked. 1784let $wait_condition= 1785select count(*) = 1 from information_schema.processlist 1786where state = "Waiting for table metadata lock" and 1787 info = "insert into t2 values (1)"; 1788--source include/wait_condition.inc 1789--echo # Unblock ALTER TABLE. 1790commit; 1791--echo # 1792connection mdl_con1; 1793--echo # Reap ALTER TABLE. 1794--reap 1795--echo # 1796connection default; 1797--echo # Reap INSERT. 1798--reap 1799commit; 1800--echo # 1801--echo # Test for the case in which we are acquiring lock on the table 1802--echo # which is already used in transaction. 1803--echo # 1804begin; 1805select count(*) from t1; 1806--echo # 1807connection mdl_con1; 1808--echo # Create SNW lock pending upgrade to X. 1809--echo # Sending: 1810--send alter table t1 add column c2 int; 1811--echo # 1812connection default; 1813--echo # Wait until ALTER TABLE starts waiting X lock. 1814let $wait_condition= 1815select count(*) = 1 from information_schema.processlist 1816where state = "Waiting for table metadata lock" and 1817 info = "alter table t1 add column c2 int"; 1818--source include/wait_condition.inc 1819--echo # Check that transaction is still able to acquire SR lock. 1820select count(*) from t1; 1821--echo # Waiting trying to acquire SW lock will cause deadlock and 1822--echo # therefore should cause an error. 1823--error ER_LOCK_DEADLOCK 1824delete from t1 limit 1; 1825--echo # Unblock ALTER TABLE. 1826commit; 1827--echo # 1828connection mdl_con1; 1829--echo # Reap ALTER TABLE. 1830--reap 1831--echo # 1832connection default; 1833--echo # 1834--echo # 3) Check how various locks used within transactional context 1835--echo # interact with active/pending SNRW lock. 1836--echo # 1837--echo # Once again we start with case when we are acquiring lock on 1838--echo # the table which was not used in the transaction before. 1839begin; 1840select count(*) from t1; 1841--echo # 1842connection mdl_con1; 1843lock table t2 write; 1844--echo # 1845connection default; 1846--echo # Attempt to acquire SR should be blocked. It should 1847--echo # not cause errors as it does not creates deadlock. 1848--echo # Sending: 1849--send select count(*) from t2; 1850--echo # 1851connection mdl_con1; 1852--echo # Check that the above SELECT is blocked 1853let $wait_condition= 1854select count(*) = 1 from information_schema.processlist 1855where state = "Waiting for table metadata lock" and 1856 info = "select count(*) from t2"; 1857--source include/wait_condition.inc 1858--echo # Unblock SELECT. 1859unlock tables; 1860--echo # 1861connection default; 1862--echo # Reap SELECT. 1863--reap 1864commit; 1865--echo # Repeat the same test for SW lock. 1866begin; 1867select count(*) from t1; 1868--echo # 1869connection mdl_con1; 1870lock table t2 write; 1871--echo # 1872connection default; 1873--echo # Again attempt to acquire SW should be blocked and should 1874--echo # not cause any errors. 1875--echo # Sending: 1876--send delete from t2 limit 1; 1877--echo # 1878connection mdl_con1; 1879--echo # Check that the above DELETE is blocked 1880let $wait_condition= 1881select count(*) = 1 from information_schema.processlist 1882where state = "Waiting for table metadata lock" and 1883 info = "delete from t2 limit 1"; 1884--source include/wait_condition.inc 1885--echo # Unblock DELETE. 1886unlock tables; 1887--echo # 1888connection default; 1889--echo # Reap DELETE. 1890--reap 1891commit; 1892--echo # 1893--echo # Now coverage for the case in which we are acquiring lock on 1894--echo # the table which is already used in transaction and against 1895--echo # which there is a pending SNRW lock request. 1896--echo # 1897--echo # *) Let us start with case when transaction has only a SR lock. 1898--echo # 1899begin; 1900select count(*) from t1; 1901--echo # 1902connection mdl_con1; 1903--echo # Sending: 1904--send lock table t1 write; 1905--echo # 1906connection default; 1907--echo # Wait until LOCK TABLE is blocked creating pending request for X lock. 1908let $wait_condition= 1909select count(*) = 1 from information_schema.processlist 1910where state = "Waiting for table metadata lock" and 1911 info = "lock table t1 write"; 1912--source include/wait_condition.inc 1913--echo # Check that another instance of SR lock is granted without waiting. 1914select count(*) from t1; 1915--echo # Attempt to wait for SW lock will lead to deadlock, thus 1916--echo # the below statement should end with ER_LOCK_DEADLOCK error. 1917--error ER_LOCK_DEADLOCK 1918delete from t1 limit 1; 1919--echo # Unblock LOCK TABLES. 1920commit; 1921--echo # 1922connection mdl_con1; 1923--echo # Reap LOCK TABLES. 1924--reap 1925unlock tables; 1926--echo # 1927connection default; 1928--echo # 1929--echo # **) Now case when transaction has a SW lock. 1930--echo # 1931begin; 1932delete from t1 limit 1; 1933--echo # 1934connection mdl_con1; 1935--echo # Sending: 1936--send lock table t1 write; 1937--echo # 1938connection default; 1939--echo # Wait until LOCK TABLE is blocked creating pending request for X lock. 1940let $wait_condition= 1941select count(*) = 1 from information_schema.processlist 1942where state = "Waiting for table metadata lock" and 1943 info = "lock table t1 write"; 1944--source include/wait_condition.inc 1945--echo # Check that both SR and SW locks are granted without waiting 1946--echo # and errors. 1947select count(*) from t1; 1948insert into t1 values (1, 1); 1949--echo # Unblock LOCK TABLES. 1950commit; 1951--echo # 1952connection mdl_con1; 1953--echo # Reap LOCK TABLES. 1954--reap 1955unlock tables; 1956--echo # 1957connection default; 1958--echo # 1959--echo # 4) Check how various locks used within transactional context 1960--echo # interact with active/pending X lock. 1961--echo # 1962--echo # As usual we start with case when we are acquiring lock on 1963--echo # the table which was not used in the transaction before. 1964begin; 1965select count(*) from t1; 1966--echo # 1967connection mdl_con2; 1968--echo # Start transaction which will prevent X lock from going away 1969--echo # immediately. 1970begin; 1971select count(*) from t2; 1972--echo # 1973connection mdl_con1; 1974--echo # Create pending X lock on t2. 1975--echo # Sending: 1976--send rename table t2 to t3; 1977--echo # 1978connection default; 1979--echo # Wait until RENAME TABLE starts waiting with pending X lock. 1980let $wait_condition= 1981select count(*) = 1 from information_schema.processlist 1982where state = "Waiting for table metadata lock" and 1983 info = "rename table t2 to t3"; 1984--source include/wait_condition.inc 1985--echo # Check that attempt to acquire SR lock on t2 causes waiting. 1986--echo # Sending: 1987--send select count(*) from t2; 1988--echo # 1989connection mdl_con2; 1990--echo # Check that the above SELECT is blocked. 1991let $wait_condition= 1992select count(*) = 1 from information_schema.processlist 1993where state = "Waiting for table metadata lock" and 1994 info = "select count(*) from t2"; 1995--source include/wait_condition.inc 1996--echo # Unblock RENAME TABLE. 1997commit; 1998--echo # 1999connection mdl_con1; 2000--echo # Reap RENAME TABLE. 2001--reap 2002--echo # 2003connection default; 2004--echo # Reap SELECT. 2005--error ER_NO_SUCH_TABLE 2006--reap 2007commit; 2008rename table t3 to t2; 2009--echo # The same test for SW lock. 2010begin; 2011select count(*) from t1; 2012--echo # 2013connection mdl_con2; 2014--echo # Start transaction which will prevent X lock from going away 2015--echo # immediately. 2016begin; 2017select count(*) from t2; 2018--echo # 2019connection mdl_con1; 2020--echo # Create pending X lock on t2. 2021--echo # Sending: 2022--send rename table t2 to t3; 2023--echo # 2024connection default; 2025--echo # Wait until RENAME TABLE starts waiting with pending X lock. 2026let $wait_condition= 2027select count(*) = 1 from information_schema.processlist 2028where state = "Waiting for table metadata lock" and 2029 info = "rename table t2 to t3"; 2030--source include/wait_condition.inc 2031--echo # Check that attempt to acquire SW lock on t2 causes waiting. 2032--echo # Sending: 2033--send delete from t2 limit 1; 2034--echo # 2035connection mdl_con2; 2036--echo # Check that the above DELETE is blocked. 2037let $wait_condition= 2038select count(*) = 1 from information_schema.processlist 2039where state = "Waiting for table metadata lock" and 2040 info = "delete from t2 limit 1"; 2041--source include/wait_condition.inc 2042--echo # Unblock RENAME TABLE. 2043commit; 2044--echo # 2045connection mdl_con1; 2046--echo # Reap RENAME TABLE. 2047--reap 2048--echo # 2049connection default; 2050--echo # Reap DELETE. 2051--error ER_NO_SUCH_TABLE 2052--reap 2053commit; 2054rename table t3 to t2; 2055--echo # 2056--echo # Coverage for the case in which we are acquiring lock on 2057--echo # the table which is already used in transaction and against 2058--echo # which there is a pending X lock request. 2059--echo # 2060--echo # *) The first case is when transaction has only a SR lock. 2061--echo # 2062begin; 2063select count(*) from t1; 2064--echo # 2065connection mdl_con1; 2066--echo # Sending: 2067--send rename table t1 to t2; 2068--echo # 2069connection default; 2070--echo # Wait until RENAME TABLE is blocked creating pending request for X lock. 2071let $wait_condition= 2072select count(*) = 1 from information_schema.processlist 2073where state = "Waiting for table metadata lock" and 2074 info = "rename table t1 to t2"; 2075--source include/wait_condition.inc 2076--echo # Check that another instance of SR lock is granted without waiting. 2077select count(*) from t1; 2078--echo # Attempt to wait for SW lock will lead to deadlock, thus 2079--echo # the below statement should end with ER_LOCK_DEADLOCK error. 2080--error ER_LOCK_DEADLOCK 2081delete from t1 limit 1; 2082--echo # Unblock RENAME TABLE. 2083commit; 2084--echo # 2085connection mdl_con1; 2086--echo # Reap RENAME TABLE. 2087--error ER_TABLE_EXISTS_ERROR 2088--reap 2089--echo # 2090connection default; 2091--echo # 2092--echo # **) The second case is when transaction has a SW lock. 2093--echo # 2094begin; 2095delete from t1 limit 1; 2096--echo # 2097connection mdl_con1; 2098--echo # Sending: 2099--send rename table t1 to t2; 2100--echo # 2101connection default; 2102--echo # Wait until RENAME TABLE is blocked creating pending request for X lock. 2103let $wait_condition= 2104select count(*) = 1 from information_schema.processlist 2105where state = "Waiting for table metadata lock" and 2106 info = "rename table t1 to t2"; 2107--source include/wait_condition.inc 2108--echo # Check that both SR and SW locks are granted without waiting 2109--echo # and errors. 2110select count(*) from t1; 2111insert into t1 values (1, 1); 2112--echo # Unblock RENAME TABLE. 2113commit; 2114--echo # 2115connection mdl_con1; 2116--echo # Reap RENAME TABLE. 2117--error ER_TABLE_EXISTS_ERROR 2118--reap 2119--echo # 2120connection default; 2121 2122--echo # Clean-up. 2123disconnect mdl_con1; 2124disconnect mdl_con2; 2125disconnect mdl_con3; 2126set debug_sync= 'RESET'; 2127drop table t1, t2; 2128 2129--echo # 2130--echo # Test coverage for basic deadlock detection in metadata 2131--echo # locking subsystem. 2132--echo # 2133--disable_warnings 2134drop tables if exists t0, t1, t2, t3, t4, t5; 2135--enable_warnings 2136set debug_sync= 'RESET'; 2137 2138connect(deadlock_con1,localhost,root,,); 2139connect(deadlock_con2,localhost,root,,); 2140connect(deadlock_con3,localhost,root,,); 2141connection default; 2142create table t1 (i int); 2143create table t2 (j int); 2144create table t3 (k int); 2145create table t4 (k int); 2146 2147--echo # 2148--echo # Test for the case in which no deadlock occurs. 2149--echo # 2150 2151--echo # 2152connection deadlock_con1; 2153begin; 2154insert into t1 values (1); 2155 2156--echo # 2157connection deadlock_con2; 2158begin; 2159insert into t2 values (1); 2160 2161--echo # 2162connection default; 2163--echo # Send: 2164--send rename table t2 to t0, t3 to t2, t0 to t3; 2165 2166--echo # 2167connection deadlock_con1; 2168--echo # Wait until the above RENAME TABLE is blocked because it has to wait 2169--echo # for 'deadlock_con2' which holds shared metadata lock on 't2'. 2170let $wait_condition= 2171 select count(*) = 1 from information_schema.processlist 2172 where state = "Waiting for table metadata lock" and 2173 info = "rename table t2 to t0, t3 to t2, t0 to t3"; 2174--source include/wait_condition.inc 2175--echo # The below statement should wait for exclusive metadata lock 2176--echo # on 't2' to go away and should not produce ER_LOCK_DEADLOCK 2177--echo # as no deadlock is possible in this situation. 2178--echo # Send: 2179--send select * from t2; 2180 2181--echo # 2182connection deadlock_con2; 2183--echo # Wait until the above SELECT * FROM t2 is starts waiting 2184--echo # for an exclusive metadata lock to go away. 2185let $wait_condition= 2186 select count(*) = 1 from information_schema.processlist 2187 where state = "Waiting for table metadata lock" and 2188 info = "select * from t2"; 2189--source include/wait_condition.inc 2190--echo # 2191--echo # Unblock RENAME TABLE by releasing shared metadata lock on t2. 2192commit; 2193 2194--echo # 2195connection default; 2196--echo # Reap RENAME TABLE. 2197--reap 2198 2199--echo # 2200connection deadlock_con1; 2201--echo # Reap SELECT. 2202--reap 2203 2204--echo # 2205connection default; 2206--echo # 2207--echo # Let us check that in the process of waiting for conflicting lock 2208--echo # on table 't2' to go away transaction in connection 'deadlock_con1' 2209--echo # has not released metadata lock on table 't1'. 2210--echo # Send: 2211--send rename table t1 to t0, t3 to t1, t0 to t3; 2212 2213--echo # 2214connection deadlock_con1; 2215--echo # Wait until the above RENAME TABLE is blocked because it has to wait 2216--echo # for 'deadlock_con1' which should still hold shared metadata lock on 2217--echo # table 't1'. 2218let $wait_condition= 2219 select count(*) = 1 from information_schema.processlist 2220 where state = "Waiting for table metadata lock" and 2221 info = "rename table t1 to t0, t3 to t1, t0 to t3"; 2222--source include/wait_condition.inc 2223--echo # Commit transaction to unblock RENAME TABLE. 2224commit; 2225 2226--echo # 2227connection default; 2228--echo # Reap RENAME TABLE. 2229--reap 2230 2231--echo # 2232--echo # Test for case when deadlock occurs and should be detected immediately. 2233--echo # 2234 2235--echo # 2236connection deadlock_con1; 2237begin; 2238insert into t2 values (2); 2239 2240--echo # 2241connection default; 2242--echo # Send: 2243--send rename table t2 to t0, t1 to t2, t0 to t1; 2244 2245--echo # 2246connection deadlock_con1; 2247--echo # Wait until the above RENAME TABLE is blocked because it has to wait 2248--echo # for 'deadlock_con1' which holds shared metadata lock on 't2'. 2249let $wait_condition= 2250 select count(*) = 1 from information_schema.processlist 2251 where state = "Waiting for table metadata lock" and 2252 info = "rename table t2 to t0, t1 to t2, t0 to t1"; 2253--source include/wait_condition.inc 2254--echo # 2255--echo # The below statement should not wait as doing so will cause deadlock. 2256--echo # Instead it should fail and emit ER_LOCK_DEADLOCK statement and 2257--echo # transaction should be rolled back. 2258--error ER_LOCK_DEADLOCK 2259select * from t1; 2260 2261--echo # 2262connection default; 2263--echo # Reap RENAME TABLE. 2264--reap 2265 2266--echo # 2267--echo # Test for the case in which deadlock also occurs but not immediately. 2268--echo # 2269 2270--echo # 2271connection deadlock_con1; 2272begin; 2273insert into t2 values (1); 2274 2275--echo # 2276connection default; 2277lock table t1 write; 2278 2279--echo # 2280connection deadlock_con1; 2281--echo # The below SELECT statement should wait for metadata lock 2282--echo # on table 't1' and should not produce ER_LOCK_DEADLOCK 2283--echo # immediately as no deadlock is possible at the moment. 2284--send select * from t1; 2285 2286--echo # 2287connection deadlock_con2; 2288--echo # Wait until the above SELECT * FROM t1 is starts waiting 2289--echo # for an UNRW metadata lock to go away. 2290let $wait_condition= 2291 select count(*) = 1 from information_schema.processlist 2292 where state = "Waiting for table metadata lock" and info = "select * from t1"; 2293--source include/wait_condition.inc 2294 2295--echo # Send RENAME TABLE statement that will deadlock with the 2296--echo # SELECT statement and thus should abort the latter. 2297--send rename table t1 to t0, t2 to t1, t0 to t2; 2298 2299--echo # 2300connection default; 2301--echo # Wait till above RENAME TABLE is blocked while holding 2302--echo # pending X lock on t1. 2303let $wait_condition= 2304 select count(*) = 1 from information_schema.processlist 2305 where state = "Waiting for table metadata lock" and 2306 info = "rename table t1 to t0, t2 to t1, t0 to t2"; 2307--source include/wait_condition.inc 2308--echo # Allow the above RENAME TABLE to acquire lock on t1 and 2309--echo # create pending lock on t2 thus creating deadlock. 2310unlock tables; 2311 2312--echo # 2313connection deadlock_con1; 2314--echo # Since the latest RENAME TABLE entered in deadlock with SELECT 2315--echo # statement the latter should be aborted and emit ER_LOCK_DEADLOCK 2316--echo # error and transaction should be rolled back. 2317--echo # Reap SELECT * FROM t1. 2318--error ER_LOCK_DEADLOCK 2319--reap 2320 2321--echo # 2322connection deadlock_con2; 2323--echo # Reap RENAME TABLE ... . 2324--reap; 2325 2326--echo # 2327connection default; 2328 2329drop tables t1, t2, t3, t4; 2330 2331--echo # 2332--echo # Now, test case which shows that deadlock detection empiric 2333--echo # also takes into account requests for metadata lock upgrade. 2334--echo # 2335create table t1 (i int); 2336insert into t1 values (1); 2337--echo # Avoid race which occurs when SELECT in 'deadlock_con1' connection 2338--echo # accesses table before the above INSERT unlocks the table and thus 2339--echo # its result becomes visible to other connections. 2340select * from t1; 2341 2342--echo # 2343connection deadlock_con1; 2344begin; 2345select * from t1; 2346 2347--echo # 2348connection default; 2349--echo # Send: 2350--send alter table t1 add column j int, rename to t2; 2351 2352--echo # 2353connection deadlock_con1; 2354--echo # Wait until the above ALTER TABLE ... RENAME acquires exclusive 2355--echo # metadata lock on 't2' and starts waiting for connection 2356--echo # 'deadlock_con1' which holds shared lock on 't1'. 2357let $wait_condition= 2358 select count(*) = 1 from information_schema.processlist 2359 where state = "Waiting for table metadata lock" and 2360 info = "alter table t1 add column j int, rename to t2"; 2361--source include/wait_condition.inc 2362 2363--echo # The below statement should not wait as it will cause deadlock. 2364--echo # An appropriate error should be reported instead and transaction 2365--echo # should be rolled back. 2366--error ER_LOCK_DEADLOCK 2367select * from t2; 2368 2369--echo # 2370connection default; 2371--echo # Reap ALTER TABLE ... RENAME. 2372--reap 2373 2374drop table t2; 2375 2376--echo # 2377--echo # Test that in situation when MDL subsystem detects a deadlock 2378--echo # but it turns out that it can be resolved by backing-off locks 2379--echo # acquired by one of participating transactions (which is 2380--echo # possible when one of transactions consists only of currently 2381--echo # executed statement, e.g. in autocommit mode) no error is 2382--echo # reported. 2383--echo # 2384create table t1 (i int); 2385create table t2 (j int); 2386--echo # Ensure that the below SELECT stops once it has acquired metadata 2387--echo # lock on table 't2'. 2388set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 2389--echo # Sending: 2390--send select * from t2, t1 2391 2392--echo # 2393connection deadlock_con1; 2394--echo # Wait till SELECT acquires MDL on 't2' and starts waiting for signal. 2395set debug_sync= 'now WAIT_FOR locked'; 2396--echo # Sending: 2397--send lock tables t1 write, t2 write 2398 2399--echo # 2400connection deadlock_con2; 2401--echo # Wait until LOCK TABLES acquires SNRW lock on 't1' and is blocked 2402--echo # while trying to acquire SNRW lock on 't1'. 2403let $wait_condition= 2404 select count(*) = 1 from information_schema.processlist 2405 where state = "Waiting for table metadata lock" and 2406 info = "lock tables t1 write, t2 write"; 2407--source include/wait_condition.inc 2408--echo # Resume SELECT execution, this should eventually unblock LOCK TABLES. 2409set debug_sync= 'now SIGNAL finish'; 2410 2411--echo # 2412connection deadlock_con1; 2413--echo # Reaping LOCK TABLES. 2414--reap 2415unlock tables; 2416 2417--echo # 2418connection default; 2419--echo # Reaping SELECT. It succeed and not report ER_LOCK_DEADLOCK error. 2420--reap 2421 2422drop tables t1, t2; 2423 2424--echo # 2425--echo # Test coverage for situation in which a race has happened 2426--echo # during deadlock detection process which led to unwarranted 2427--echo # ER_LOCK_DEADLOCK error. 2428--echo # 2429create table t1 (i int); 2430 2431--echo # Ensure that ALTER waits once it has acquired SNW lock. 2432set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL parked1 WAIT_FOR go1'; 2433--echo # Sending: 2434--send alter table t1 add column j int 2435 2436--echo # 2437connection deadlock_con1; 2438--echo # Wait till ALTER acquires SNW lock and stops. 2439set debug_sync='now WAIT_FOR parked1'; 2440--echo # Ensure that INSERT is paused once it detects that there is 2441--echo # a conflicting metadata lock so it has to wait, but before 2442--echo # deadlock detection is run. 2443set debug_sync='mdl_acquire_lock_wait SIGNAL parked2 WAIT_FOR go2'; 2444--echo # Sending: 2445--send insert into t1 values () 2446 2447--echo # 2448connection deadlock_con2; 2449--echo # Wait till INSERT is paused. 2450set debug_sync='now WAIT_FOR parked2'; 2451--echo # Resume ALTER execution. Eventually it will release its 2452--echo # metadata lock and INSERT's request for SW lock will be 2453--echo # satisified. 2454set debug_sync='now SIGNAL go1'; 2455 2456--echo # 2457connection default; 2458--echo # Reaping ALTER TABLE. 2459--reap 2460--echo # Add a new request for SNW lock to waiting graph. 2461--echo # Sending: 2462--send alter table t1 drop column j 2463 2464--echo # 2465connection deadlock_con2; 2466--echo # Wait until ALTER is blocked. 2467let $wait_condition= 2468 select count(*) = 1 from information_schema.processlist 2469 where state = "Waiting for table metadata lock" and 2470 info = "alter table t1 drop column j"; 2471--source include/wait_condition.inc 2472--echo # Resume INSERT so it can start deadlock detection. 2473--echo # 2474--echo # At this point there is a discrepancy between the fact that INSERT's 2475--echo # SW lock is already satisfied, but INSERT's connection is still 2476--echo # marked as waiting for it. Looking for a loop in waiters graph 2477--echo # without additional checks has detected a deadlock (INSERT waits 2478--echo # for SW lock; which is not granted because of pending SNW lock from 2479--echo # ALTER; which waits for active SW lock from INSERT). Since requests 2480--echo # for SW and SNW locks have same weight ALTER was selected as a victim 2481--echo # and ended with ER_LOCK_DEADLOCK error. 2482set debug_sync='now SIGNAL go2'; 2483 2484--echo # 2485connection deadlock_con1; 2486--echo # Reaping INSERT. 2487--reap 2488 2489--echo # 2490connection default; 2491--echo # Reaping ALTER. It should succeed and not produce ER_LOCK_DEADLOCK. 2492--reap 2493 2494drop table t1; 2495 2496set debug_sync= 'RESET'; 2497 2498disconnect deadlock_con1; 2499disconnect deadlock_con2; 2500disconnect deadlock_con3; 2501 2502 2503--echo # 2504--echo # Test for a scenario in which FLUSH TABLES <list> WITH READ LOCK 2505--echo # used to erroneously release metadata locks. 2506--echo # 2507connect(con1,localhost,root,,); 2508connect(con2,localhost,root,,); 2509connection default; 2510--disable_warnings 2511drop tables if exists t1, t2; 2512--enable_warnings 2513set debug_sync= 'RESET'; 2514create table t1(i int); 2515create table t2(j int); 2516 2517connection con2; 2518set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; 2519 2520--echo # The below FLUSH TABLES <list> WITH READ LOCK should acquire 2521--echo # SNW locks on t1 and t2, open table t1 and block on the debug 2522--echo # sync point. 2523--echo # Sending: 2524send flush tables t1, t2 with read lock; 2525 2526connection con1; 2527--echo # Wait till FLUSH TABLES <list> WITH READ LOCK stops. 2528set debug_sync='now WAIT_FOR parked'; 2529 2530# Simple flush tables should not block 2531flush tables; 2532 2533--echo # Start a statement which will flush all tables and thus 2534--echo # invalidate table t1 open by FLUSH TABLES <list> WITH READ LOCK. 2535--echo # Sending: 2536send flush tables t1; 2537 2538connection default; 2539--echo # Wait till the above FLUSH TABLES blocks. 2540let $wait_condition= 2541 select count(*) = 1 from information_schema.processlist 2542 where state = "Waiting for table metadata lock" and 2543 info = "flush tables t1"; 2544--source include/wait_condition.inc 2545 2546--echo # Resume FLUSH TABLES <list> WITH READ LOCK, so it tries to open t2 2547--echo # discovers that its t1 is obsolete and tries to reopen all tables. 2548--echo # Such reopen should not cause releasing of SNW metadata locks 2549--echo # which would result in assertion failures. 2550set debug_sync='now SIGNAL go'; 2551 2552connection con2; 2553--echo # Reap FLUSH TABLES <list> WITH READ LOCK. 2554reap; 2555unlock tables; 2556 2557connection con1; 2558--echo # Reap FLUSH TABLES. 2559reap; 2560 2561--echo # Clean-up. 2562connection default; 2563drop tables t1, t2; 2564set debug_sync= 'RESET'; 2565disconnect con1; 2566disconnect con2; 2567 2568 2569--echo # 2570--echo # Test for bug #46748 "Assertion in MDL_context::wait_for_locks() 2571--echo # on INSERT + CREATE TRIGGER". 2572--echo # 2573--disable_warnings 2574drop tables if exists t1, t2, t3, t4, t5; 2575--enable_warnings 2576--echo # Let us simulate scenario in which we open some tables from extended 2577--echo # part of prelocking set but then encounter conflicting metadata lock, 2578--echo # so have to back-off and wait for it to go away. 2579connect (con1root,localhost,root,,test,,); 2580connect (con2root,localhost,root,,test,,); 2581connection default; 2582create table t1 (i int); 2583create table t2 (j int); 2584create table t3 (k int); 2585create table t4 (l int); 2586create trigger t1_bi before insert on t1 for each row 2587 insert into t2 values (new.i); 2588create trigger t2_bi before insert on t2 for each row 2589 insert into t3 values (new.j); 2590--echo # 2591connection con1root; 2592lock tables t4 read; 2593--echo # 2594connection con2root; 2595--echo # Send : 2596--send rename table t3 to t5, t4 to t3; 2597--echo # 2598connection default; 2599--echo # Wait until the above RENAME TABLE adds pending requests for exclusive 2600--echo # metadata lock on its tables and blocks due to 't4' being used by LOCK 2601--echo # TABLES. 2602let $wait_condition= select count(*)= 1 from information_schema.processlist 2603 where state= 'Waiting for table metadata lock' and 2604 info='rename table t3 to t5, t4 to t3'; 2605--source include/wait_condition.inc 2606--echo # Send : 2607--send insert into t1 values (1); 2608--echo # 2609connection con1root; 2610--echo # Wait until INSERT statement waits due to encountering pending 2611--echo # exclusive metadata lock on 't3'. 2612let $wait_condition= select count(*)= 1 from information_schema.processlist 2613 where state= 'Waiting for table metadata lock' and 2614 info='insert into t1 values (1)'; 2615--source include/wait_condition.inc 2616unlock tables; 2617--echo # 2618connection con2root; 2619--echo # Reap RENAME TABLE. 2620--reap 2621--echo # 2622connection default; 2623--echo # Reap INSERT. 2624--reap 2625--echo # Clean-up. 2626disconnect con1root; 2627disconnect con2root; 2628drop tables t1, t2, t3, t5; 2629 2630 2631--echo # 2632--echo # Bug#42546 - Backup: RESTORE fails, thinking it finds an existing table 2633--echo # 2634 2635--disable_warnings 2636DROP TABLE IF EXISTS t1; 2637--enable_warnings 2638set @save_log_output=@@global.log_output; 2639set global log_output=file; 2640 2641connect(con2, localhost, root,,); 2642 2643--echo # 2644--echo # Test 1: CREATE TABLE 2645--echo # 2646 2647connection con2; 2648--echo # Start insert on the not-yet existing table 2649--echo # Wait after taking the MDL lock 2650SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 2651--send INSERT INTO t1 VALUES(1,"def") 2652 2653connection default; 2654SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2655--echo # Now INSERT has a MDL on the non-existent table t1. 2656 2657--echo # 2658--echo # Continue the INSERT once CREATE waits for exclusive lock 2659SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish'; 2660--echo # Try to create that table. 2661--send CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1)) 2662 2663--echo # Insert fails 2664connection con2; 2665--error ER_NO_SUCH_TABLE 2666--reap 2667 2668connection default; 2669--reap; 2670SET DEBUG_SYNC= 'RESET'; 2671SHOW TABLES; 2672 2673--disable_warnings 2674DROP TABLE IF EXISTS t1; 2675--enable_warnings 2676 2677--echo # 2678--echo # Test 2: CREATE TABLE LIKE 2679--echo # 2680 2681CREATE TABLE t2 (c1 INT, c2 VARCHAR(100), KEY(c1)); 2682 2683connection con2; 2684--echo # Start insert on the not-yet existing table 2685--echo # Wait after taking the MDL 2686SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 2687--send INSERT INTO t1 VALUES(1,"def") 2688 2689connection default; 2690SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2691--echo # Now INSERT has a MDL on the non-existent table t1. 2692 2693--echo # 2694--echo # Continue the INSERT once CREATE waits for exclusive lock 2695SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish'; 2696--echo # Try to create that table. 2697--send CREATE TABLE t1 LIKE t2 2698 2699--echo # Insert fails 2700connection con2; 2701--error ER_NO_SUCH_TABLE 2702--reap 2703 2704connection default; 2705--reap 2706SET DEBUG_SYNC= 'RESET'; 2707SHOW TABLES; 2708 2709DROP TABLE t2; 2710disconnect con2; 2711--disable_warnings 2712DROP TABLE IF EXISTS t1; 2713--enable_warnings 2714 2715set global log_output=@save_log_output; 2716 2717 2718--echo # 2719--echo # Bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY 2720--echo # FOR UPDATE" 2721--echo # 2722--disable_warnings 2723drop tables if exists t1, t2; 2724--enable_warnings 2725connect (con46044, localhost, root,,); 2726connect (con46044_2, localhost, root,,); 2727connect (con46044_3, localhost, root,,); 2728connection default; 2729create table t1 (i int); 2730insert into t1 values(1); 2731 2732--echo # Let us check that we won't deadlock if during filling 2733--echo # of I_S table we encounter conflicting metadata lock 2734--echo # which owner is in its turn waiting for our connection. 2735lock tables t1 read; 2736 2737connection con46044_2; 2738--echo # Sending: 2739--send update t1 set i = 2 2740 2741connection con46044; 2742 2743--echo # Waiting until UPDATE t1 SET ... is blocked. 2744let $wait_condition= 2745 select count(*) = 1 from information_schema.processlist 2746 where state = "Waiting for table level lock" and 2747 info = "update t1 set i = 2"; 2748--source include/wait_condition.inc 2749 2750--echo # Sending: 2751--send create table t2 select * from t1; 2752 2753connection default; 2754--echo # Waiting until CREATE TABLE ... SELECT ... is blocked. 2755let $wait_condition= 2756 select count(*) = 1 from information_schema.processlist 2757 where state = "Waiting for table level lock" and 2758 info = "create table t2 select * from t1"; 2759--source include/wait_condition.inc 2760 2761--echo # First let us check that SHOW FIELDS/DESCRIBE doesn't 2762--echo # gets blocked and emits and error. 2763--error ER_WARN_I_S_SKIPPED_TABLE 2764show fields from t2; 2765 2766--echo # Now test for I_S query which reads only .FRMs. 2767--echo # 2768--echo # Query below should only emit a warning. 2769select column_name from information_schema.columns 2770 where table_schema='test' and table_name='t2'; 2771 2772--echo # Finally, test for I_S query which does full-blown table open. 2773--echo # 2774--echo # Query below should not be blocked. Warning message should be 2775--echo # stored in the 'table_comment' column. 2776select table_name, table_type, auto_increment, table_comment 2777 from information_schema.tables where table_schema='test' and table_name='t2'; 2778 2779connection default; 2780unlock tables; 2781 2782connection con46044; 2783--echo # Reaping CREATE TABLE ... SELECT ... . 2784--reap 2785drop table t2; 2786 2787connection con46044_2; 2788--echo # Reaping UPDATE t1 statement 2789--reap 2790 2791--echo # 2792--echo # Let us also check that queries to I_S wait for conflicting metadata 2793--echo # locks to go away instead of skipping table with a warning in cases 2794--echo # when deadlock is not possible. This is a nice thing from compatibility 2795--echo # and ease of use points of view. 2796--echo # 2797--echo # We check same three queries to I_S in this new situation. 2798 2799connection con46044_2; 2800lock tables t1 read; 2801 2802connection con46044_3; 2803--echo # Sending: 2804send update t1 set i = 3; 2805 2806connection con46044; 2807 2808--echo # Waiting until UPDATE t1 SET ... is blocked. 2809let $wait_condition= 2810 select count(*) = 1 from information_schema.processlist 2811 where state = "Waiting for table level lock" and 2812 info = "update t1 set i = 3"; 2813--source include/wait_condition.inc 2814 2815--echo # Sending: 2816--send create table t2 select * from t1; 2817 2818connection default; 2819--echo # Waiting until CREATE TABLE ... SELECT ... is blocked. 2820let $wait_condition= 2821 select count(*) = 1 from information_schema.processlist 2822 where state = "Waiting for table level lock" and 2823 info = "create table t2 select * from t1"; 2824--source include/wait_condition.inc 2825 2826--echo # Let us check that SHOW FIELDS/DESCRIBE gets blocked. 2827--echo # Sending: 2828--send show fields from t2; 2829 2830connection con46044_2; 2831--echo # Wait until SHOW FIELDS gets blocked. 2832let $wait_condition= 2833 select count(*) = 1 from information_schema.processlist 2834 where state = "Waiting for table metadata lock" and 2835 info = "show fields from t2"; 2836--source include/wait_condition.inc 2837 2838unlock tables; 2839 2840connection con46044; 2841--echo # Reaping CREATE TABLE ... SELECT ... . 2842--reap 2843 2844connection default; 2845--echo # Reaping SHOW FIELDS ... 2846--reap 2847drop table t2; 2848 2849connection con46044_3; 2850--echo # Reaping UPDATE t1 statement 2851--reap 2852 2853connection con46044_2; 2854lock tables t1 read; 2855 2856connection con46044_3; 2857--echo # Sending: 2858--send update t1 set i = 4 2859 2860connection con46044; 2861 2862--echo # Waiting until UPDATE t1 SET ... is blocked. 2863let $wait_condition= 2864 select count(*) = 1 from information_schema.processlist 2865 where state = "Waiting for table level lock" and 2866 info = "update t1 set i = 4"; 2867--source include/wait_condition.inc 2868 2869--echo # Sending: 2870--send create table t2 select * from t1; 2871 2872connection default; 2873--echo # Waiting until CREATE TABLE ... SELECT ... is blocked. 2874let $wait_condition= 2875 select count(*) = 1 from information_schema.processlist 2876 where state = "Waiting for table level lock" and 2877 info = "create table t2 select * from t1"; 2878--source include/wait_condition.inc 2879 2880--echo # Check that I_S query which reads only .FRMs gets blocked. 2881--echo # Sending: 2882--send select column_name from information_schema.columns where table_schema='test' and table_name='t2'; 2883 2884connection con46044_2; 2885--echo # Wait until SELECT COLUMN_NAME FROM I_S.COLUMNS gets blocked. 2886let $wait_condition= 2887 select count(*) = 1 from information_schema.processlist 2888 where state = "Waiting for table metadata lock" and 2889 info like "select column_name from information_schema.columns%"; 2890--source include/wait_condition.inc 2891 2892unlock tables; 2893 2894connection con46044; 2895--echo # Reaping CREATE TABLE ... SELECT ... . 2896--reap 2897 2898connection default; 2899--echo # Reaping SELECT COLUMN_NAME FROM I_S.COLUMNS 2900--reap 2901drop table t2; 2902 2903connection con46044_3; 2904--echo # Reaping UPDATE t1 statement 2905--reap 2906 2907connection con46044_2; 2908lock tables t1 read; 2909 2910connection con46044_3; 2911--echo # Sending: 2912--send update t1 set i = 5 2913 2914connection con46044; 2915 2916--echo # Waiting until UPDATE t1 SET ... is blocked. 2917let $wait_condition= 2918 select count(*) = 1 from information_schema.processlist 2919 where state = "Waiting for table level lock" and 2920 info = "update t1 set i = 5"; 2921--source include/wait_condition.inc 2922 2923--echo # Sending: 2924--send create table t2 select * from t1; 2925 2926connection default; 2927--echo # Waiting until CREATE TABLE ... SELECT ... is blocked. 2928let $wait_condition= 2929 select count(*) = 1 from information_schema.processlist 2930 where state = "Waiting for table level lock" and 2931 info = "create table t2 select * from t1"; 2932--source include/wait_condition.inc 2933 2934--echo # Finally, check that I_S query which does full-blown table open 2935--echo # also gets blocked. 2936--echo # Sending: 2937--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t2'; 2938 2939connection con46044_2; 2940--echo # Wait until SELECT ... FROM I_S.TABLES gets blocked. 2941let $wait_condition= 2942 select count(*) = 1 from information_schema.processlist 2943 where state = "Waiting for table metadata lock" and 2944 info like "select table_name, table_type, auto_increment, table_comment from information_schema.tables%"; 2945--source include/wait_condition.inc 2946 2947unlock tables; 2948 2949connection con46044; 2950--echo # Reaping CREATE TABLE ... SELECT ... . 2951--reap 2952 2953connection default; 2954--echo # Reaping SELECT ... FROM I_S.TABLES 2955--reap 2956drop table t2; 2957 2958connection con46044_3; 2959--echo # Reaping UPDATE t1 statement 2960--reap 2961 2962connection default; 2963--echo # Clean-up. 2964disconnect con46044; 2965disconnect con46044_2; 2966disconnect con46044_3; 2967drop table t1; 2968 2969 2970--echo # 2971--echo # Test for bug #46273 "MySQL 5.4.4 new MDL: Bug#989 is not fully fixed 2972--echo # in case of ALTER". 2973--echo # 2974--disable_warnings 2975drop table if exists t1; 2976--enable_warnings 2977set debug_sync= 'RESET'; 2978connect (con46273,localhost,root,,test,,); 2979connection default; 2980create table t1 (c1 int primary key, c2 int, c3 int); 2981insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0); 2982 2983begin; 2984select * from t1 where c2 = 3; 2985 2986--echo # 2987connection con46273; 2988set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL alter_table_locked WAIT_FOR alter_go'; 2989--send alter table t1 add column e int, rename to t2; 2990 2991--echo # 2992connection default; 2993set debug_sync='now WAIT_FOR alter_table_locked'; 2994set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go'; 2995--echo # The below statement should get ER_LOCK_DEADLOCK error 2996--echo # (i.e. it should not allow ALTER to proceed, and then 2997--echo # fail due to 't1' changing its name to 't2'). 2998--error ER_LOCK_DEADLOCK 2999update t1 set c3=c3+1 where c2 = 3; 3000 3001--echo # 3002connection con46273; 3003--echo # Reap ALTER TABLE. 3004--reap 3005 3006--echo # 3007connection default; 3008disconnect con46273; 3009--echo # Clean-up. 3010set debug_sync= 'RESET'; 3011drop table t2; 3012 3013 3014--echo # 3015--echo # Test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK 3016--echo # and DML". 3017--echo # 3018--disable_warnings 3019drop tables if exists t1; 3020--enable_warnings 3021connect (con46673, localhost, root,,); 3022connection default; 3023create table t1 (i int); 3024 3025connection con46673; 3026begin; 3027insert into t1 values (1); 3028 3029connection default; 3030--echo # Statement below should not get blocked. And if after some 3031--echo # changes to code it is there should not be a deadlock between 3032--echo # it and transaction from connection 'con46673'. 3033flush tables with read lock; 3034unlock tables; 3035 3036connection con46673; 3037delete from t1 where i = 1; 3038commit; 3039 3040connection default; 3041--echo # Clean-up 3042disconnect con46673; 3043drop table t1; 3044 3045 3046--echo # 3047--echo # Bug#48210 FLUSH TABLES WITH READ LOCK deadlocks 3048--echo # against concurrent CREATE PROCEDURE 3049--echo # 3050 3051connect (con2, localhost, root); 3052 3053--echo # Test 1: CREATE PROCEDURE 3054 3055connection default; 3056--echo # Start CREATE PROCEDURE and open mysql.proc 3057SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait'; 3058--send CREATE PROCEDURE p1() SELECT 1 3059 3060connection con2; 3061SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 3062--echo # Check that FLUSH must wait to get the GRL 3063--echo # and let CREATE PROCEDURE continue 3064SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; 3065--send FLUSH TABLES WITH READ LOCK 3066 3067connection default; 3068--reap 3069 3070connection con2; 3071--reap 3072UNLOCK TABLES; 3073 3074connection default; 3075SET DEBUG_SYNC= 'RESET'; 3076 3077--echo # Test 2: DROP PROCEDURE 3078 3079connection default; 3080--echo # Start DROP PROCEDURE and open tables 3081SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait'; 3082--send DROP PROCEDURE p1 3083 3084connection con2; 3085SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 3086--echo # Check that FLUSH must wait to get the GRL 3087--echo # and let DROP PROCEDURE continue 3088SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 3089SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; 3090--send FLUSH TABLES WITH READ LOCK 3091 3092connection default; 3093--echo # Once FLUSH TABLES WITH READ LOCK starts waiting 3094--echo # DROP PROCEDURE will be waked up and will drop 3095--echo # procedure. Global read lock will be granted after 3096--echo # this statement ends. 3097--echo # 3098--echo # Reaping DROP PROCEDURE. 3099--reap 3100 3101connection con2; 3102--echo # Reaping FTWRL. 3103--reap 3104UNLOCK TABLES; 3105connection default; 3106SET DEBUG_SYNC= 'RESET'; 3107 3108--echo # 3109--echo # UPDATE should wait for FTWRL with non transactional table second 3110--echo # 3111 3112create table t1 (a int) engine=myisam; 3113create table t2 (a int) engine=innodb; 3114insert into t1 values (1); 3115insert into t2 values (1); 3116 3117SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait execute 2'; 3118--send update t1,t2 set t1.a=2,t2.a=3 3119 3120connection con2; 3121SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 3122SET DEBUG_SYNC= 'now SIGNAL grlwait'; 3123SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 3124SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; 3125FLUSH TABLES WITH READ LOCK; 3126 3127connection default; 3128--echo # Reaping UPDATE 3129--reap 3130 3131connection con2; 3132UNLOCK TABLES; 3133 3134connection default; 3135SET DEBUG_SYNC= 'RESET'; 3136 3137# This will cause a wait as we first get lock for innodb table t2 but FTWRL 3138# will cause lock for t1 to wait 3139 3140SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait execute 2'; 3141--send update t2,t1 set t1.a=2,t2.a=3 3142 3143connection con2; 3144SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 3145SET DEBUG_SYNC= 'now SIGNAL grlwait'; 3146SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 3147SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; 3148FLUSH TABLES WITH READ LOCK; 3149 3150let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.metadata_lock_info; 3151--source include/wait_condition.inc 3152SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 3153 3154unlock tables; 3155 3156connection default; 3157--echo # Reaping UPDATE 3158--reap 3159SET DEBUG_SYNC= 'RESET'; 3160drop table t1,t2; 3161disconnect con2; 3162 3163--echo # 3164--echo # Bug#50786 Assertion `thd->mdl_context.trans_sentinel() == __null' 3165--echo # failed in open_ltable() 3166--echo # 3167 3168--echo # Supress warnings written to the log file 3169call mtr.add_suppression("Wait on a lock was aborted due to a pending exclusive lock"); 3170 3171connect (con1,localhost,root); 3172connect (con2,localhost,root); 3173connect (con3,localhost,root); 3174connection default; 3175 3176CREATE TABLE t1 (i INT); 3177CREATE TABLE t2 (i INT); 3178 3179SET @old_general_log= @@global.general_log; 3180SET @@global.general_log= 1; 3181 3182SET @old_log_output= @@global.log_output; 3183SET @@global.log_output= 'TABLE'; 3184 3185SET @old_sql_log_off= @@session.sql_log_off; 3186SET @@session.sql_log_off= 1; 3187 3188--echo # connection: con1 3189connection con1; 3190HANDLER t1 OPEN; 3191 3192--echo # connection: con3 3193connection con3; 3194SET @@session.sql_log_off= 1; 3195 3196--echo # connection: con2 3197connection con2; 3198SET DEBUG_SYNC= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; 3199 3200# The below statement will block on the debug sync point 3201# after it gets write lock on mysql.general_log table. 3202--echo # Sending: 3203--send SELECT 1 3204 3205--echo # connection: con3 3206connection con3; 3207SET DEBUG_SYNC= 'now WAIT_FOR parked'; 3208 3209--echo # connection: con1 3210connection con1; 3211# This statement will block in open_ltable() when 3212# trying to write into mysql.general_log. 3213--echo # Sending: 3214--send SELECT 1 3215 3216--echo # connection: con3 3217connection con3; 3218let $wait_condition= 3219 SELECT COUNT(*) = 1 FROM information_schema.processlist 3220 WHERE state = "Waiting for table level lock" and info = "SELECT 1"; 3221--source include/wait_condition.inc 3222# The ALTER below will try to abort the statement in connection con1, 3223# since the latter waits on a table-level lock while having a HANDLER 3224# open. This will cause mysql_lock_tables() in con1 fail which before 3225# triggered the assert. 3226ALTER TABLE t1 ADD COLUMN j INT; 3227 3228--echo # connection: default 3229connection default; 3230SET DEBUG_SYNC= 'now SIGNAL go'; 3231 3232--echo # connection: con1 3233connection con1; 3234--echo # Reaping SELECT 1 3235--reap 3236HANDLER t1 CLOSE; 3237 3238--echo # connection: con2 3239connection con2; 3240--echo # Reaping SELECT 1 3241--reap 3242 3243--echo # connection: default 3244connection default; 3245DROP TABLE t1, t2; 3246SET DEBUG_SYNC= 'RESET'; 3247disconnect con1; 3248disconnect con2; 3249disconnect con3; 3250SET @@global.general_log= @old_general_log; 3251SET @@global.log_output= @old_log_output; 3252SET @@session.sql_log_off= @old_sql_log_off; 3253 3254 3255--echo # 3256--echo # Additional coverage for bug #50913 "Deadlock between 3257--echo # open_and_lock_tables_derived and MDL". The main test 3258--echo # case is in lock_multi.test 3259--echo # 3260--disable_warnings 3261drop table if exists t1; 3262--enable_warnings 3263set debug_sync= 'RESET'; 3264connect (con50913_1,localhost,root); 3265connect (con50913_2,localhost,root); 3266connection default; 3267create table t1 (i int) engine=InnoDB; 3268 3269connection con50913_1; 3270set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; 3271--echo # Sending: 3272--send alter table t1 add column j int, ALGORITHM=COPY 3273 3274connection default; 3275--echo # Wait until ALTER TABLE gets blocked on a sync point after 3276--echo # acquiring thr_lock.c lock. 3277set debug_sync= 'now WAIT_FOR parked'; 3278--echo # The below statement should wait on MDL lock and not deadlock on 3279--echo # thr_lock.c lock. 3280--echo # Sending: 3281--send truncate table t1 3282 3283connection con50913_2; 3284--echo # Wait until TRUNCATE TABLE is blocked on MDL lock. 3285let $wait_condition= 3286 select count(*) = 1 from information_schema.processlist 3287 where state = "Waiting for table metadata lock" and 3288 info = "truncate table t1"; 3289--source include/wait_condition.inc 3290--echo # Unblock ALTER TABLE. 3291set debug_sync= 'now SIGNAL go'; 3292 3293connection con50913_1; 3294--echo # Reaping ALTER TABLE. 3295--reap 3296 3297connection default; 3298--echo # Reaping TRUNCATE TABLE. 3299--reap 3300disconnect con50913_1; 3301disconnect con50913_2; 3302set debug_sync= 'RESET'; 3303drop table t1; 3304 3305 3306--echo # 3307--echo # Test for bug #50998 "Deadlock in MDL code during test 3308--echo # rqg_mdl_stability". 3309--echo # Also provides coverage for the case when addition of 3310--echo # waiting statement adds several loops in the waiters 3311--echo # graph and therefore several searches for deadlock 3312--echo # should be performed. 3313--disable_warnings 3314drop table if exists t1; 3315--enable_warnings 3316set debug_sync= 'RESET'; 3317connect (con1,localhost,root); 3318connect (con2,localhost,root); 3319connect (con3,localhost,root); 3320connection default; 3321create table t1 (i int); 3322 3323connection con1; 3324begin; 3325select * from t1; 3326 3327connection con2; 3328begin; 3329select * from t1; 3330 3331connection default; 3332--echo # Start ALTER TABLE which will acquire SNW lock and 3333--echo # table lock and get blocked on sync point. 3334set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; 3335--echo # Sending: 3336--send alter table t1 add column j int 3337 3338connection con1; 3339--echo # Wait until ALTER TABLE gets blocked on a sync point. 3340set debug_sync= 'now WAIT_FOR parked'; 3341--echo # Sending: 3342--send insert into t1 values (1) 3343 3344connection con2; 3345--echo # Sending: 3346--send insert into t1 values (1) 3347 3348connection con3; 3349--echo # Wait until both 'con1' and 'con2' are blocked trying to acquire 3350--echo # SW lock on the table. 3351let $wait_condition= 3352 select count(*) = 2 from information_schema.processlist 3353 where state = "Waiting for table metadata lock" and 3354 info = "insert into t1 values (1)"; 3355--source include/wait_condition.inc 3356--echo # Unblock ALTER TABLE. Since it will try to upgrade SNW to X lock 3357--echo # deadlock with two loops in waiting graph will occur. Both loops 3358--echo # should be found and DML statements in both 'con1' and 'con2' 3359--echo # should be aborted with ER_LOCK_DEADLOCK errors. 3360set debug_sync= 'now SIGNAL go'; 3361 3362connection con1; 3363--echo # Reaping INSERT. It should end with ER_LOCK_DEADLOCK error and 3364--echo # not wait indefinitely (as it happened before the bugfix). 3365--error ER_LOCK_DEADLOCK 3366--reap 3367commit; 3368 3369connection con2; 3370--echo # Reaping INSERT. 3371--error ER_LOCK_DEADLOCK 3372--reap 3373commit; 3374 3375connection default; 3376--echo # Reap ALTER TABLE. 3377--reap 3378 3379disconnect con1; 3380disconnect con2; 3381disconnect con3; 3382connection default; 3383set debug_sync= 'RESET'; 3384drop table t1; 3385 3386--echo # 3387--echo # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE 3388--echo # 3389--echo # Ensure that a acquired lock is not given up due to a conflict. 3390--echo # 3391 3392connect (con1,localhost,root,,test,,); 3393connect (con2,localhost,root,,test,,); 3394connect (con3,localhost,root,,test,,); 3395 3396connection default; 3397 3398--disable_warnings 3399DROP TABLE IF EXISTS t1; 3400--enable_warnings 3401 3402CREATE TABLE t1 (a INT) ENGINE=InnoDB; 3403INSERT INTO t1 VALUES (1),(2),(3); 3404 3405connection con1; 3406LOCK TABLES t1 WRITE; 3407SET debug_sync='upgrade_lock_for_truncate SIGNAL parked_truncate WAIT_FOR go_truncate'; 3408send TRUNCATE TABLE t1; 3409 3410connection default; 3411SET debug_sync='now WAIT_FOR parked_truncate'; 3412 3413connection con2; 3414SET debug_sync='after_open_table_ignore_flush SIGNAL parked_show WAIT_FOR go_show'; 3415send SHOW FIELDS FROM t1; 3416 3417connection default; 3418SET debug_sync='now WAIT_FOR parked_show'; 3419 3420connection con3; 3421SET debug_sync='after_flush_unlock SIGNAL parked_flush WAIT_FOR go_flush'; 3422send FLUSH TABLES t1; 3423 3424connection default; 3425SET debug_sync='now WAIT_FOR parked_flush'; 3426SET debug_sync='now SIGNAL go_truncate'; 3427--echo # Ensure that truncate waits for a exclusive lock 3428let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3429 WHERE state='Waiting for table metadata lock' AND info='TRUNCATE TABLE t1'; 3430--source include/wait_condition.inc 3431SET debug_sync= 'now SIGNAL go_show'; 3432 3433connection con1; 3434--echo # Reaping... 3435reap; 3436UNLOCK TABLES; 3437 3438connection con2; 3439--echo # Reaping... 3440reap; 3441 3442connection default; 3443SET debug_sync= 'now SIGNAL go_flush'; 3444 3445connection con3; 3446--echo # Reaping... 3447reap; 3448 3449disconnect con1; 3450disconnect con2; 3451disconnect con3; 3452 3453connection default; 3454SET debug_sync= 'RESET'; 3455DROP TABLE t1; 3456 3457 3458--echo # 3459--echo # Bug#52856 concurrent show columns or show full columns causes a crash!!! 3460--echo # 3461CREATE TABLE t1(a CHAR(255)); 3462 3463connect(con1, localhost, root); 3464SET DEBUG_SYNC= "get_schema_column SIGNAL waiting WAIT_FOR completed"; 3465--send SHOW FULL COLUMNS FROM t1 3466 3467connection default; 3468SET DEBUG_SYNC= "now WAIT_FOR waiting"; 3469--replace_column 8 # 3470SHOW FULL COLUMNS FROM t1; 3471SET DEBUG_SYNC= "now SIGNAL completed"; 3472--replace_column 8 # 3473connection con1; 3474--reap 3475connection default; 3476DROP TABLE t1; 3477disconnect con1; 3478 3479 3480--echo # 3481--echo # Tests for schema-scope locks 3482--echo # 3483 3484--disable_warnings 3485DROP DATABASE IF EXISTS db1; 3486DROP DATABASE IF EXISTS db2; 3487--enable_warnings 3488 3489connect (con2, localhost, root); 3490connect (con3, localhost, root); 3491 3492--echo # Test 1: 3493--echo # CREATE DATABASE blocks database DDL on the same database, but 3494--echo # not database DDL on different databases. Tests X vs X lock. 3495--echo # 3496 3497connection default; 3498SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3499--echo # Sending: 3500--send CREATE DATABASE db1 3501 3502connection con2; 3503SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3504--echo # Sending: 3505# This should block. 3506--send CREATE DATABASE db1 3507 3508connection con3; 3509let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3510 WHERE state='Waiting for schema metadata lock' AND info='CREATE DATABASE db1'; 3511--source include/wait_condition.inc 3512# This should not block. 3513CREATE DATABASE db2; 3514ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; 3515DROP DATABASE db2; 3516SET DEBUG_SYNC= 'now SIGNAL blocked'; 3517 3518connection default; 3519--echo # Reaping: CREATE DATABASE db1 3520--reap 3521 3522connection con2; 3523--echo # Reaping: CREATE DATABASE db1 3524--error ER_DB_CREATE_EXISTS 3525--reap 3526 3527--echo # Test 2: 3528--echo # ALTER DATABASE blocks database DDL on the same database, but 3529--echo # not database DDL on different databases. Tests X vs X lock. 3530--echo # 3531 3532connection default; 3533SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3534--echo # Sending: 3535--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3536 3537connection con2; 3538SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3539--echo # Sending: 3540# This should block. 3541--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3542 3543connection con3; 3544let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3545 WHERE state='Waiting for schema metadata lock' 3546 AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8'; 3547--source include/wait_condition.inc 3548# This should not block. 3549CREATE DATABASE db2; 3550ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; 3551DROP DATABASE db2; 3552SET DEBUG_SYNC= 'now SIGNAL blocked'; 3553 3554connection default; 3555--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3556--reap 3557 3558connection con2; 3559--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3560--reap 3561 3562connection default; 3563SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3564--echo # Sending: 3565--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3566 3567connection con2; 3568SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3569--echo # Sending: 3570# This should also block. 3571--send DROP DATABASE db1 3572 3573connection con3; 3574let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3575 WHERE state='Waiting for schema metadata lock' AND info='DROP DATABASE db1'; 3576--source include/wait_condition.inc 3577SET DEBUG_SYNC= 'now SIGNAL blocked'; 3578 3579connection default; 3580--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3581--reap 3582 3583connection con2; 3584--echo # Reaping: DROP DATABASE db1 3585--reap 3586# Recreate the database 3587CREATE DATABASE db1; 3588 3589--echo # Test 3: 3590--echo # Two ALTER..UPGRADE of the same database are mutually exclusive, but 3591--echo # two ALTER..UPGRADE of different databases are not. Tests X vs X lock. 3592--echo # 3593 3594let $MYSQLD_DATADIR= `select @@datadir`; 3595# Manually make a 5.0 database from the template 3596--mkdir $MYSQLD_DATADIR/a-b-c 3597--copy_file $MYSQLD_DATADIR/db1/db.opt $MYSQLD_DATADIR/a-b-c/db.opt 3598--mkdir $MYSQLD_DATADIR/a-b-c-d 3599--copy_file $MYSQLD_DATADIR/db1/db.opt $MYSQLD_DATADIR/a-b-c-d/db.opt 3600 3601connection default; 3602SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3603--echo # Sending: 3604--send ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME 3605 3606connection con2; 3607SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3608--echo # Sending: 3609# This should block. 3610--send ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME 3611 3612connection con3; 3613let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3614 WHERE state='Waiting for schema metadata lock' 3615 AND info='ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME'; 3616--source include/wait_condition.inc 3617# This should not block. 3618ALTER DATABASE `#mysql50#a-b-c-d` UPGRADE DATA DIRECTORY NAME; 3619SET DEBUG_SYNC= 'now SIGNAL blocked'; 3620 3621connection default; 3622--echo # Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME 3623--reap 3624 3625connection con2; 3626--echo # Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME 3627--error ER_BAD_DB_ERROR 3628--reap 3629DROP DATABASE `a-b-c`; 3630DROP DATABASE `a-b-c-d`; 3631 3632--echo # Test 4: 3633--echo # DROP DATABASE blocks database DDL on the same database, but 3634--echo # not database DDL on different databases. Tests X vs X lock. 3635--echo # 3636 3637connection default; 3638SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3639--echo # Sending: 3640--send DROP DATABASE db1 3641 3642connection con2; 3643SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3644--echo # Sending: 3645# This should block. 3646--send DROP DATABASE db1 3647 3648connection con3; 3649let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3650 WHERE state='Waiting for schema metadata lock' AND info='DROP DATABASE db1'; 3651--source include/wait_condition.inc 3652# This should not block. 3653CREATE DATABASE db2; 3654ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; 3655DROP DATABASE db2; 3656SET DEBUG_SYNC= 'now SIGNAL blocked'; 3657 3658connection default; 3659--echo # Reaping: DROP DATABASE db1 3660--reap 3661 3662connection con2; 3663--echo # Reaping: DROP DATABASE db1 3664--error ER_DB_DROP_EXISTS 3665--reap 3666 3667connection default; 3668CREATE DATABASE db1; 3669SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3670--echo # Sending: 3671--send DROP DATABASE db1 3672 3673connection con2; 3674SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3675--echo # Sending: 3676# This should also block. 3677--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3678 3679connection con3; 3680let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3681 WHERE state='Waiting for schema metadata lock' 3682 AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8'; 3683--source include/wait_condition.inc 3684SET DEBUG_SYNC= 'now SIGNAL blocked'; 3685 3686connection default; 3687--echo # Reaping: DROP DATABASE db1 3688--reap 3689 3690connection con2; 3691--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3692# Error 1 is from ALTER DATABASE when the database does not exist. 3693# Listing the error twice to prevent result diffences based on filename. 3694--error 1,1 3695--reap 3696 3697 3698--echo # Test 5: 3699--echo # Locked database name prevents CREATE of tables in that database. 3700--echo # Tests X vs IX lock. 3701--echo # 3702 3703connection default; 3704CREATE DATABASE db1; 3705SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3706--echo # Sending: 3707--send DROP DATABASE db1 3708 3709connection con2; 3710SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3711--echo # Sending: 3712# This should block. 3713--send CREATE TABLE db1.t1 (a INT) 3714 3715connection con3; 3716let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3717 WHERE state='Waiting for schema metadata lock' AND 3718 info='CREATE TABLE db1.t1 (a INT)'; 3719--source include/wait_condition.inc 3720SET DEBUG_SYNC= 'now SIGNAL blocked'; 3721 3722connection default; 3723--echo # Reaping: DROP DATABASE db1 3724--reap 3725 3726connection con2; 3727--echo # Reaping: CREATE TABLE db1.t1 (a INT) 3728--error ER_BAD_DB_ERROR 3729--reap 3730 3731--echo # Test 6: 3732--echo # Locked database name prevents RENAME of tables to/from that database. 3733--echo # Tests X vs IX lock. 3734--echo # 3735 3736connection default; 3737CREATE DATABASE db1; 3738CREATE TABLE db1.t1 (a INT); 3739SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3740--echo # Sending: 3741--send DROP DATABASE db1 3742 3743connection con2; 3744SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3745--echo # Sending: 3746# This should block. 3747--send RENAME TABLE db1.t1 TO test.t1 3748 3749connection con3; 3750let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3751 WHERE state='Waiting for schema metadata lock' AND 3752 info='RENAME TABLE db1.t1 TO test.t1'; 3753--source include/wait_condition.inc 3754SET DEBUG_SYNC= 'now SIGNAL blocked'; 3755 3756connection default; 3757--echo # Reaping: DROP DATABASE db1 3758--reap 3759 3760connection con2; 3761--echo # Reaping: RENAME TABLE db1.t1 TO test.t1 3762--error ER_NO_SUCH_TABLE 3763--reap 3764 3765connection default; 3766CREATE DATABASE db1; 3767CREATE TABLE test.t2 (a INT); 3768SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3769--echo # Sending: 3770--send DROP DATABASE db1 3771 3772connection con2; 3773SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3774--echo # Sending: 3775# This should block. 3776--send RENAME TABLE test.t2 TO db1.t2 3777 3778connection con3; 3779let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3780 WHERE state='Waiting for schema metadata lock' AND 3781 info='RENAME TABLE test.t2 TO db1.t2'; 3782--source include/wait_condition.inc 3783SET DEBUG_SYNC= 'now SIGNAL blocked'; 3784 3785connection default; 3786--echo # Reaping: DROP DATABASE db1 3787--reap 3788 3789connection con2; 3790--echo # Reaping: RENAME TABLE test.t2 TO db1.t2 3791# Error 7 is from RENAME TABLE where the target database does not exist. 3792# Listing the error twice to prevent result diffences based on filename. 3793--error 7, 7 3794--reap 3795DROP TABLE test.t2; 3796 3797 3798--echo # Test 7: 3799--echo # Locked database name prevents DROP of tables in that database. 3800--echo # Tests X vs IX lock. 3801--echo # 3802 3803connection default; 3804CREATE DATABASE db1; 3805CREATE TABLE db1.t1 (a INT); 3806SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 3807--echo # Sending: 3808--send DROP DATABASE db1 3809 3810connection con2; 3811SET DEBUG_SYNC= 'now WAIT_FOR locked'; 3812--echo # Sending: 3813# This should block. 3814--send DROP TABLE db1.t1 3815 3816connection con3; 3817let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3818 WHERE state='Waiting for schema metadata lock' AND info='DROP TABLE db1.t1'; 3819--source include/wait_condition.inc 3820SET DEBUG_SYNC= 'now SIGNAL blocked'; 3821 3822connection default; 3823--echo # Reaping: DROP DATABASE db1 3824--reap 3825 3826connection con2; 3827--echo # Reaping: DROP TABLE db1.t1 3828--error ER_BAD_TABLE_ERROR 3829--reap 3830 3831connection default; 3832disconnect con2; 3833disconnect con3; 3834SET DEBUG_SYNC= 'RESET'; 3835 3836--echo # 3837--echo # End of tests for schema-scope locks 3838--echo # 3839 3840--echo # 3841--echo # Tests of granted global S lock (FLUSH TABLE WITH READ LOCK) 3842--echo # 3843 3844CREATE DATABASE db1; 3845CREATE TABLE db1.t1(a INT); 3846connect(con2, localhost, root); 3847connect(con3, localhost, root); 3848 3849connection default; 3850FLUSH TABLE WITH READ LOCK; 3851 3852connection con2; 3853# IX global lock should block 3854--send CREATE TABLE db1.t2(a INT) 3855 3856connection default; 3857let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3858 WHERE state='Waiting for backup lock' 3859 AND info='CREATE TABLE db1.t2(a INT)'; 3860--source include/wait_condition.inc 3861UNLOCK TABLES; 3862 3863connection con2; 3864--echo # Reaping CREATE TABLE db1.t2(a INT) 3865--reap 3866 3867connection default; 3868FLUSH TABLE WITH READ LOCK; 3869 3870connection con2; 3871# X global lock should block 3872--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3873 3874connection default; 3875let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 3876 WHERE state='Waiting for backup lock' 3877 AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8'; 3878--source include/wait_condition.inc 3879UNLOCK TABLES; 3880 3881connection con2; 3882--echo # Reaping ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3883--reap 3884 3885connection default; 3886FLUSH TABLE WITH READ LOCK; 3887 3888connection con2; 3889# S global lock should not block 3890FLUSH TABLE WITH READ LOCK; 3891UNLOCK TABLES; 3892 3893connection default; 3894UNLOCK TABLES; 3895DROP DATABASE db1; 3896disconnect con2; 3897disconnect con3; 3898 3899 3900--echo # 3901--echo # Bug#56292 Deadlock with ALTER TABLE and MERGE tables 3902--echo # 3903 3904--disable_warnings 3905DROP TABLE IF EXISTS t1, t2, m1; 3906--enable_warnings 3907 3908CREATE TABLE t1(a INT) engine=MyISAM; 3909CREATE TABLE t2(a INT) engine=MyISAM; 3910CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2); 3911 3912INSERT INTO t1 VALUES (1), (2); 3913INSERT INTO t2 VALUES (3), (4); 3914 3915connect(con1, localhost, root); 3916connect(con2, localhost, root); 3917connect(con3, localhost, root); 3918 3919connection con1; 3920--echo # We need EXECUTE 2 since ALTER TABLE does SU => SNW => X and we want 3921--echo # to stop at the second upgrade. 3922SET DEBUG_SYNC= 'mdl_upgrade_lock SIGNAL upgrade WAIT_FOR continue EXECUTE 2'; 3923--echo # Sending: 3924--send ALTER TABLE m1 engine=MERGE UNION=(t2, t1) 3925 3926connection con2; 3927--echo # Waiting for ALTER TABLE to try lock upgrade 3928SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; 3929SET DEBUG_SYNC= 'now SIGNAL continue'; 3930SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; 3931--echo # Sending: 3932--send DELETE FROM t2 WHERE a = 3 3933 3934connection con3; 3935--echo # Check that DELETE is waiting on a metadata lock and not a table lock. 3936let $wait_condition= 3937 SELECT COUNT(*) = 1 FROM information_schema.processlist 3938 WHERE state = "Waiting for table metadata lock" AND 3939 info = "DELETE FROM t2 WHERE a = 3"; 3940--source include/wait_condition.inc 3941--echo # Now that DELETE blocks on a metadata lock, we should be able to do 3942--echo # SELECT * FROM m1 here. SELECT used to be blocked by a DELETE table 3943--echo # lock request. 3944--send SELECT * FROM m1 WHERE a < 3 3945 3946connection default; 3947--echo # Resuming ALTER TABLE 3948SET DEBUG_SYNC= 'now SIGNAL continue'; 3949 3950connection con1; 3951--echo # Reaping: ALTER TABLE m1 engine=MERGE UNION=(t2, t1) 3952--reap 3953connection con2; 3954--echo # Reaping: DELETE FROM t2 WHERE a = 3 3955--reap 3956connection con3; 3957--echo # Reaping: SELECT * FROM m1 WHERE a < 3 3958--reap 3959connection default; 3960DROP TABLE m1, t1, t2; 3961SET DEBUG_SYNC= 'RESET'; 3962disconnect con1; 3963disconnect con2; 3964disconnect con3; 3965 3966 3967--echo # 3968--echo # MDEV-12620 - set lock_wait_timeout = 1;flush tables with read lock; 3969--echo # lock not released after timeout 3970--echo # 3971 3972CREATE TABLE t1(a INT) ENGINE=InnoDB; 3973SET debug_sync='open_tables_after_open_and_process_table SIGNAL ready WAIT_FOR go'; 3974send INSERT INTO t1 values (1); 3975 3976connect (con1,localhost,root,,); 3977SET debug_sync='now WAIT_FOR ready'; 3978# lock_wait_timeout should be 0 in 10.3, so that we don't have to wait at all 3979SET lock_wait_timeout=1; 3980--error ER_LOCK_WAIT_TIMEOUT 3981FLUSH TABLES WITH READ LOCK; 3982SET debug_sync='now SIGNAL go'; 3983 3984connection default; 3985reap; 3986 3987--echo # After MDEV-5536, SELECT will not block FLUSH TABLES 3988 3989SET debug_sync='RESET'; 3990SET debug_sync='open_tables_after_open_and_process_table SIGNAL ready WAIT_FOR go'; 3991send SELECT * FROM t1; 3992 3993connection con1; 3994SET debug_sync='now WAIT_FOR ready'; 3995# lock_wait_timeout should be 0 in 10.3, so that we don't have to wait at all 3996SET lock_wait_timeout=1; 3997FLUSH TABLES WITH READ LOCK; 3998SET debug_sync='now SIGNAL go'; 3999 4000connection default; 4001reap; 4002connection con1; 4003unlock tables; 4004connection default; 4005 4006SET debug_sync='RESET'; 4007DROP TABLE t1; 4008 4009disconnect con1; 4010 4011# Check that all connections opened by test cases in this file are really 4012# gone so execution of other tests won't be affected by their presence. 4013--source include/wait_until_count_sessions.inc 4014