1SET DEBUG_SYNC= 'RESET'; 2connect con1,localhost,root,,test,,; 3connect con2,localhost,root,,test,,; 4connect con3,localhost,root,,test,,; 5connection default; 6drop table if exists t1,t2,t3; 7create table t1 (i int); 8create table t2 (i int); 9connection: default 10lock tables t2 read; 11connection con1; 12connection: con1 13set debug_sync='mdl_upgrade_lock SIGNAL parked WAIT_FOR go'; 14alter table t1 rename t3; 15connection default; 16connection: default 17set debug_sync= 'now WAIT_FOR parked'; 18connection con2; 19connection: con2 20set debug_sync='mdl_acquire_lock_wait SIGNAL go'; 21drop table t1,t2; 22connection con1; 23connection: con1 24connection default; 25connection: default 26unlock tables; 27connection con2; 28connection: con2 29ERROR 42S02: Unknown table 'test.t1' 30connection default; 31drop table t3; 32disconnect con1; 33disconnect con2; 34disconnect con3; 35SET DEBUG_SYNC= 'RESET'; 36# 37# Basic test coverage for type-of-operation aware metadata locks. 38# 39drop table if exists t1, t2, t3; 40connect mdl_con1,localhost,root,,; 41connect mdl_con2,localhost,root,,; 42connect mdl_con3,localhost,root,,; 43connection default; 44set debug_sync= 'RESET'; 45create table t1 (c1 int); 46# 47# A) First let us check compatibility rules between differend kinds of 48# type-of-operation aware metadata locks. 49# Of course, these rules are already covered by the tests scattered 50# across the test suite. But it still makes sense to have one place 51# which covers all of them. 52# 53# 1) Acquire S (simple shared) lock on the table (by using HANDLER): 54# 55handler t1 open; 56# 57connection mdl_con1; 58# Check that S, SH, SR and SW locks are compatible with it. 59handler t1 open t; 60handler t close; 61select column_name from information_schema.columns where 62table_schema='test' and table_name='t1'; 63column_name 64c1 65select count(*) from t1; 66count(*) 670 68insert into t1 values (1), (1); 69# Check that SU lock is compatible with it. To do this use ALTER TABLE 70# which will fail when constructing .frm and thus obtaining SU metadata 71# lock. 72alter table t1 add index (not_exist); 73ERROR 42000: Key column 'not_exist' doesn't exist in table 74# Check that SNW lock is compatible with it. To do this use ALTER TABLE 75# which will fail during copying the table and thus obtaining SNW metadata 76# lock. 77alter table t1 add primary key (c1); 78ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 79# Check that X lock is incompatible with S lock. 80# Sending: 81rename table t1 to t2;; 82# 83connection mdl_con2; 84# Check that the above RENAME is blocked because of S lock. 85# 86connection default; 87# Unblock RENAME TABLE. 88handler t1 close; 89# 90connection mdl_con1; 91# Reaping RENAME TABLE. 92# Restore the original state of the things. 93rename table t2 to t1; 94# 95connection default; 96handler t1 open; 97# 98connection mdl_con1; 99# Check that upgrade from SNW to X is blocked by presence of S lock. 100# Sending: 101alter table t1 add column c2 int;; 102# 103connection mdl_con2; 104# Check that the above ALTER TABLE is blocked because of S lock. 105# 106connection default; 107# Unblock ALTER TABLE. 108handler t1 close; 109# 110connection mdl_con1; 111# Reaping ALTER TABLE. 112# Restore the original state of the things. 113alter table t1 drop column c2; 114# 115connection default; 116# 117# 2) Acquire SH (shared high-priority) lock on the table. 118# We have to involve DEBUG_SYNC facility for this as usually 119# such kind of locks are short-lived. 120# 121set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 122# Sending: 123select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';; 124# 125connection mdl_con1; 126set debug_sync= 'now WAIT_FOR locked'; 127# Check that S, SH, SR and SW locks are compatible with it. 128handler t1 open; 129handler t1 close; 130select column_name from information_schema.columns where 131table_schema='test' and table_name='t1'; 132column_name 133c1 134select count(*) from t1; 135count(*) 1362 137insert into t1 values (1); 138# Check that SU lock is compatible with it. To do this use ALTER TABLE 139# which will fail when constructing .frm and thus obtaining SU metadata 140# lock. 141alter table t1 add index (not_exist); 142ERROR 42000: Key column 'not_exist' doesn't exist in table 143# Check that SNW lock is compatible with it. To do this use ALTER TABLE 144# which will fail during copying the table and thus obtaining SNW metadata 145# lock. 146alter table t1 add primary key (c1); 147ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 148# Check that SNRW lock is compatible with SH lock. 149lock table t1 write; 150delete from t1 limit 1; 151unlock tables; 152# Check that X lock is incompatible with SH lock. 153# Sending: 154rename table t1 to t2;; 155# 156connection mdl_con2; 157# Check that the above RENAME is blocked because of SH lock. 158# Unblock RENAME TABLE. 159set debug_sync= 'now SIGNAL finish'; 160# 161connection default; 162# Reaping SELECT ... FROM I_S. 163table_name table_type auto_increment table_comment 164t1 BASE TABLE NULL 165# 166connection mdl_con1; 167# Reaping RENAME TABLE. 168# Restore the original state of the things. 169rename table t2 to t1; 170# 171connection default; 172set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 173# Sending: 174select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';; 175# 176connection mdl_con1; 177set debug_sync= 'now WAIT_FOR locked'; 178# Check that upgrade from SNW to X is blocked by presence of SH lock. 179# Sending: 180alter table t1 add column c2 int;; 181# 182connection mdl_con2; 183# Check that the above ALTER TABLE is blocked because of SH lock. 184# Unblock RENAME TABLE. 185set debug_sync= 'now SIGNAL finish'; 186# 187connection default; 188# Reaping SELECT ... FROM I_S. 189table_name table_type auto_increment table_comment 190t1 BASE TABLE NULL 191# 192connection mdl_con1; 193# Reaping ALTER TABLE. 194# Restore the original state of the things. 195alter table t1 drop column c2; 196# 197connection default; 198set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 199select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';; 200# 201connection mdl_con1; 202set debug_sync= 'now WAIT_FOR locked'; 203# Check that upgrade from SNRW to X is blocked by presence of S lock. 204lock table t1 write; 205# Sending: 206alter table t1 add column c2 int;; 207# 208connection mdl_con2; 209# Check that the above upgrade of SNRW to X in ALTER TABLE is blocked 210# because of S lock. 211# Unblock RENAME TABLE. 212set debug_sync= 'now SIGNAL finish'; 213# 214connection default; 215# Reaping SELECT ... FROM I_S. 216table_name table_type auto_increment table_comment 217t1 BASE TABLE NULL 218# 219connection mdl_con1; 220# Reaping ALTER TABLE. 221# Restore the original state of the things. 222alter table t1 drop column c2; 223unlock tables; 224# 225connection default; 226# 227# 228# 3) Acquire SR lock on the table. 229# 230# 231begin; 232select count(*) from t1; 233count(*) 2342 235# 236connection mdl_con1; 237# Check that S, SH, SR and SW locks are compatible with it. 238handler t1 open; 239handler t1 close; 240select column_name from information_schema.columns where 241table_schema='test' and table_name='t1'; 242column_name 243c1 244select count(*) from t1; 245count(*) 2462 247insert into t1 values (1); 248# Check that SU lock is compatible with it. To do this use ALTER TABLE 249# which will fail when constructing .frm and thus obtaining SU metadata 250# lock. 251alter table t1 add index (not_exist); 252ERROR 42000: Key column 'not_exist' doesn't exist in table 253# Check that SNW lock is compatible with it. To do this use ALTER TABLE 254# which will fail during copying the table and thus obtaining SNW metadata 255# lock. 256alter table t1 add primary key (c1); 257ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 258# Check that SNRW lock is not compatible with SR lock. 259# Sending: 260lock table t1 write;; 261# 262connection default; 263# Check that the above LOCK TABLES is blocked because of SR lock. 264# Unblock LOCK TABLES. 265commit; 266# 267connection mdl_con1; 268# Reaping LOCK TABLES. 269delete from t1 limit 1; 270unlock tables; 271# 272connection default; 273begin; 274select count(*) from t1; 275count(*) 2762 277# 278connection mdl_con1; 279# Check that X lock is incompatible with SR lock. 280# Sending: 281rename table t1 to t2;; 282# 283connection mdl_con2; 284# Check that the above RENAME is blocked because of SR lock. 285# 286connection default; 287# Unblock RENAME TABLE. 288commit; 289# 290connection mdl_con1; 291# Reaping RENAME TABLE. 292# Restore the original state of the things. 293rename table t2 to t1; 294# 295connection default; 296begin; 297select count(*) from t1; 298count(*) 2992 300# 301connection mdl_con1; 302# Check that upgrade from SNW to X is blocked by presence of SR lock. 303# Sending: 304alter table t1 add column c2 int;; 305# 306connection mdl_con2; 307# Check that the above ALTER TABLE is blocked because of SR lock. 308# 309connection default; 310# Unblock ALTER TABLE. 311commit; 312# 313connection mdl_con1; 314# Reaping ALTER TABLE. 315# Restore the original state of the things. 316alter table t1 drop column c2; 317# 318# There is no need to check that upgrade from SNRW to X is blocked 319# by presence of SR lock because SNRW is incompatible with SR anyway. 320# 321# 322connection default; 323# 324# 325# 4) Acquire SW lock on the table. 326# 327# 328begin; 329insert into t1 values (1); 330# 331connection mdl_con1; 332# Check that S, SH, SR and SW locks are compatible with it. 333handler t1 open; 334handler t1 close; 335select column_name from information_schema.columns where 336table_schema='test' and table_name='t1'; 337column_name 338c1 339# Disable result log to make test robust against 340# effects of concurrent insert. 341select * from t1; 342insert into t1 values (1); 343# Check that SU lock is compatible with it. To do this use ALTER TABLE 344# which will fail when constructing .frm and thus obtaining SU metadata 345# lock. 346alter table t1 add index (not_exist); 347ERROR 42000: Key column 'not_exist' doesn't exist in table 348# Check that SNW lock is not compatible with SW lock. 349# Again we use ALTER TABLE which fails during copying 350# the table to avoid upgrade of SNW -> X. 351# Sending: 352alter table t1 add primary key (c1);; 353# 354connection default; 355# Check that the above ALTER TABLE is blocked because of SW lock. 356# Unblock ALTER TABLE. 357commit; 358# 359connection mdl_con1; 360# Reaping ALTER TABLE. 361ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 362# 363connection default; 364begin; 365insert into t1 values (1); 366# 367connection mdl_con1; 368# Check that SNRW lock is not compatible with SW lock. 369# Sending: 370lock table t1 write;; 371# 372connection default; 373# Check that the above LOCK TABLES is blocked because of SW lock. 374# Unblock LOCK TABLES. 375commit; 376# 377connection mdl_con1; 378# Reaping LOCK TABLES. 379delete from t1 limit 2; 380unlock tables; 381# 382connection default; 383begin; 384insert into t1 values (1); 385# 386connection mdl_con1; 387# Check that X lock is incompatible with SW lock. 388# Sending: 389rename table t1 to t2;; 390# 391connection mdl_con2; 392# Check that the above RENAME is blocked because of SW lock. 393# 394connection default; 395# Unblock RENAME TABLE. 396commit; 397# 398connection mdl_con1; 399# Reaping RENAME TABLE. 400# Restore the original state of the things. 401rename table t2 to t1; 402# 403# There is no need to check that upgrade from SNW/SNRW to X is 404# blocked by presence of SW lock because SNW/SNRW is incompatible 405# with SW anyway. 406# 407# 408connection default; 409# 410# 411# 5) Acquire SU lock on the table. We have to use DEBUG_SYNC for 412# this, to prevent SU from being immediately upgraded to X. 413# 414set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; 415# Sending: 416alter table t1 add primary key (c1);; 417# 418connection mdl_con1; 419set debug_sync= 'now WAIT_FOR locked'; 420# Check that S, SH, SR and SW locks are compatible with it. 421handler t1 open; 422handler t1 close; 423select column_name from information_schema.columns where 424table_schema='test' and table_name='t1'; 425column_name 426c1 427select count(*) from t1; 428count(*) 4294 430delete from t1 limit 1; 431# Check that SU lock is incompatible with SU lock. 432# Sending: 433alter table t1 add primary key (c1);; 434# 435connection mdl_con2; 436# Check that the above ALTER is blocked because of SU lock. 437# Unblock ALTERs. 438set debug_sync= 'now SIGNAL finish'; 439# 440connection default; 441# Reaping first ALTER TABLE. 442ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 443# 444connection mdl_con1; 445# Reaping another ALTER TABLE. 446ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 447# 448connection default; 449set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; 450# Sending: 451alter table t1 add primary key (c1);; 452# 453connection mdl_con1; 454set debug_sync= 'now WAIT_FOR locked'; 455# Check that SNRW lock is incompatible with SU lock. 456# Sending: 457lock table t1 write;; 458# 459connection mdl_con2; 460# Check that the above LOCK TABLES is blocked because of SU lock. 461# Unblock ALTER and thus LOCK TABLES. 462set debug_sync= 'now SIGNAL finish'; 463# 464connection default; 465# Reaping ALTER TABLE. 466ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 467# 468connection mdl_con1; 469# Reaping LOCK TABLES 470insert into t1 values (1); 471unlock tables; 472# 473connection default; 474set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; 475# Sending: 476alter table t1 add primary key (c1);; 477# 478connection mdl_con1; 479set debug_sync= 'now WAIT_FOR locked'; 480# Check that X lock is incompatible with SU lock. 481# Sending: 482rename table t1 to t2;; 483# 484connection mdl_con2; 485# Check that the above RENAME is blocked because of SU lock. 486# Unblock ALTER and thus RENAME TABLE. 487set debug_sync= 'now SIGNAL finish'; 488# 489connection default; 490# Now we have ALTER TABLE with SU->SNW and RENAME TABLE with pending 491# X-lock. In this case ALTER TABLE should be chosen as victim. 492# Reaping ALTER TABLE. 493ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 494# 495connection mdl_con1; 496# Reaping RENAME TABLE 497# Revert back to original state of things. 498rename table t2 to t1; 499# 500# There is no need to check that upgrade from SNW/SNRW to X is 501# blocked by presence of another SU lock because SNW/SNRW is 502# incompatible with SU anyway. 503# 504connection default; 505# 506# 507# 6) Acquire SNW lock on the table. We have to use DEBUG_SYNC for 508# this, to prevent SNW from being immediately upgraded to X. 509# 510set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 511# Sending: 512alter table t1 add primary key (c1), lock=shared, algorithm=copy;; 513# 514connection mdl_con1; 515set debug_sync= 'now WAIT_FOR locked'; 516# Check that S, SH and SR locks are compatible with it. 517handler t1 open; 518handler t1 close; 519select column_name from information_schema.columns where 520table_schema='test' and table_name='t1'; 521column_name 522c1 523select count(*) from t1; 524count(*) 5254 526# Check that SW lock is incompatible with SNW lock. 527# Sending: 528delete from t1 limit 2;; 529# 530connection mdl_con2; 531# Check that the above DELETE is blocked because of SNW lock. 532# Unblock ALTER and thus DELETE. 533set debug_sync= 'now SIGNAL finish'; 534# 535connection default; 536# Reaping ALTER TABLE. 537ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 538# 539connection mdl_con1; 540# Reaping DELETE. 541# 542connection default; 543set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 544# Sending: 545alter table t1 add primary key (c1), lock=shared, algorithm=copy;; 546# 547connection mdl_con1; 548set debug_sync= 'now WAIT_FOR locked'; 549# Check that SU lock is incompatible with SNW lock. 550# Sending: 551alter table t1 add primary key (c1);; 552# 553connection mdl_con2; 554# Check that the above ALTER is blocked because of SNW lock. 555# Unblock ALTERs. 556set debug_sync= 'now SIGNAL finish'; 557# 558connection default; 559# Reaping first ALTER TABLE. 560ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 561# 562connection mdl_con1; 563# Reaping another ALTER TABLE. 564ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 565# 566# Note that we can't easily check SNW vs SNW locks since 567# SNW is only used by ALTER TABLE after upgrading from SU 568# and SU is also incompatible with SNW. 569# 570connection default; 571set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 572# Sending: 573alter table t1 add primary key (c1), lock=shared, algorithm=copy;; 574# 575connection mdl_con1; 576set debug_sync= 'now WAIT_FOR locked'; 577# Check that SNRW lock is incompatible with SNW lock. 578# Sending: 579lock table t1 write;; 580# 581connection mdl_con2; 582# Check that the above LOCK TABLES is blocked because of SNW lock. 583# Unblock ALTER and thus LOCK TABLES. 584set debug_sync= 'now SIGNAL finish'; 585# 586connection default; 587# Reaping ALTER TABLE. 588ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 589# 590connection mdl_con1; 591# Reaping LOCK TABLES 592insert into t1 values (1); 593unlock tables; 594# 595connection default; 596set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 597# Sending: 598alter table t1 add primary key (c1), algorithm=copy, lock=shared;; 599# 600connection mdl_con1; 601set debug_sync= 'now WAIT_FOR locked'; 602# Check that X lock is incompatible with SNW lock. 603# Sending: 604rename table t1 to t2;; 605# 606connection mdl_con2; 607# Check that the above RENAME is blocked because of SNW lock. 608# Unblock ALTER and thus RENAME TABLE. 609set debug_sync= 'now SIGNAL finish'; 610# 611connection default; 612# Reaping ALTER TABLE. 613ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 614# 615connection mdl_con1; 616# Reaping RENAME TABLE 617# Revert back to original state of things. 618rename table t2 to t1; 619# 620# There is no need to check that upgrade from SNW/SNRW to X is 621# blocked by presence of another SNW lock because SNW/SNRW is 622# incompatible with SNW anyway. 623# 624connection default; 625# 626# 627# 7) Acquire SNRW lock on the table. 628# 629# 630lock table t1 write; 631# 632connection mdl_con1; 633# Check that S and SH locks are compatible with it. 634select column_name from information_schema.columns where 635table_schema='test' and table_name='t1'; 636column_name 637c1 638# Check that SR lock is incompatible with SNRW lock. 639# Sending: 640select count(*) from t1;; 641# 642connection default; 643# Check that the above SELECT is blocked because of SNRW lock. 644# Unblock SELECT. 645unlock tables; 646# 647connection mdl_con1; 648# Reaping SELECT. 649count(*) 6503 651# 652connection default; 653lock table t1 write; 654# 655connection mdl_con1; 656# Check that SW lock is incompatible with SNRW lock. 657# Sending: 658delete from t1 limit 1;; 659# 660connection default; 661# Check that the above DELETE is blocked because of SNRW lock. 662# Unblock DELETE. 663unlock tables; 664# 665connection mdl_con1; 666# Reaping DELETE. 667# 668connection default; 669lock table t1 write; 670# 671connection mdl_con1; 672# Check that SU lock is incompatible with SNRW lock. 673# Sending: 674alter table t1 add primary key (c1);; 675# 676connection default; 677# Check that the above ALTER is blocked because of SNRW lock. 678# Unblock ALTER. 679unlock tables; 680# 681connection mdl_con1; 682# Reaping ALTER TABLE. 683ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 684# 685# Note that we can't easily check SNW vs SNRW locks since 686# SNW is only used by ALTER TABLE after upgrading from SU 687# and SU is also incompatible with SNRW. 688# 689connection default; 690lock table t1 write; 691# 692connection mdl_con1; 693# Check that SNRW lock is incompatible with SNRW lock. 694# Sending: 695lock table t1 write;; 696# 697connection default; 698# Check that the above LOCK TABLES is blocked because of SNRW lock. 699# Unblock waiting LOCK TABLES. 700unlock tables; 701# 702connection mdl_con1; 703# Reaping LOCK TABLES 704insert into t1 values (1); 705unlock tables; 706# 707connection default; 708lock table t1 write; 709# 710connection mdl_con1; 711# Check that X lock is incompatible with SNRW lock. 712# Sending: 713rename table t1 to t2;; 714# 715connection default; 716# Check that the above RENAME is blocked because of SNRW lock. 717# Unblock RENAME TABLE 718unlock tables; 719# 720connection mdl_con1; 721# Reaping RENAME TABLE 722# Revert back to original state of things. 723rename table t2 to t1; 724# 725# There is no need to check that upgrade from SNW/SNRW to X is 726# blocked by presence of another SNRW lock because SNW/SNRW is 727# incompatible with SNRW anyway. 728# 729connection default; 730# 731# 732# 8) Now do the same round of tests for X lock. We use additional 733# table to get long-lived lock of this type. 734# 735create table t2 (c1 int); 736# 737connection mdl_con2; 738# Take a lock on t2, so RENAME TABLE t1 TO t2 will get blocked 739# after acquiring X lock on t1. 740lock tables t2 read; 741# 742connection default; 743# Sending: 744rename table t1 to t2;; 745# 746connection mdl_con1; 747# Check that RENAME has acquired X lock on t1 and is waiting for t2. 748# Check that S lock in incompatible with X lock. 749# Sending: 750handler t1 open;; 751# 752connection mdl_con2; 753# Check that the above HANDLER statement is blocked because of X lock. 754# Unblock RENAME TABLE 755unlock tables; 756# 757connection default; 758# Reaping RENAME TABLE. 759ERROR 42S01: Table 't2' already exists 760# 761connection mdl_con1; 762# Reaping HANDLER. 763handler t1 close; 764# 765connection mdl_con2; 766# Prepare for blocking RENAME TABLE. 767lock tables t2 read; 768# 769connection default; 770# Sending: 771rename table t1 to t2;; 772# 773connection mdl_con1; 774# Check that RENAME has acquired X lock on t1 and is waiting for t2. 775# Check that SH lock in incompatible with X lock. 776# Sending: 777select column_name from information_schema.columns where table_schema='test' and table_name='t1';; 778# 779connection mdl_con2; 780# Check that the above SELECT ... FROM I_S ... statement is blocked 781# because of X lock. 782# Unblock RENAME TABLE 783unlock tables; 784# 785connection default; 786# Reaping RENAME TABLE. 787ERROR 42S01: Table 't2' already exists 788# 789connection mdl_con1; 790# Reaping SELECT ... FROM I_S. 791column_name 792c1 793# 794connection mdl_con2; 795# Prepare for blocking RENAME TABLE. 796lock tables t2 read; 797# 798connection default; 799# Sending: 800rename table t1 to t2;; 801# 802connection mdl_con1; 803# Check that RENAME has acquired X lock on t1 and is waiting for t2. 804# Check that SR lock in incompatible with X lock. 805# Sending: 806select count(*) from t1;; 807# 808connection mdl_con2; 809# Check that the above SELECT statement is blocked 810# because of X lock. 811# Unblock RENAME TABLE 812unlock tables; 813# 814connection default; 815# Reaping RENAME TABLE. 816ERROR 42S01: Table 't2' already exists 817# 818connection mdl_con1; 819# Reaping SELECT. 820count(*) 8213 822# 823connection mdl_con2; 824# Prepare for blocking RENAME TABLE. 825lock tables t2 read; 826# 827connection default; 828# Sending: 829rename table t1 to t2;; 830# 831connection mdl_con1; 832# Check that RENAME has acquired X lock on t1 and is waiting for t2. 833# Check that SW lock in incompatible with X lock. 834# Sending: 835delete from t1 limit 1;; 836# 837connection mdl_con2; 838# Check that the above DELETE statement is blocked 839# because of X lock. 840# Unblock RENAME TABLE 841unlock tables; 842# 843connection default; 844# Reaping RENAME TABLE. 845ERROR 42S01: Table 't2' already exists 846# 847connection mdl_con1; 848# Reaping DELETE. 849# 850connection mdl_con2; 851# Prepare for blocking RENAME TABLE. 852lock tables t2 read; 853# 854connection default; 855# Sending: 856rename table t1 to t2;; 857# 858connection mdl_con1; 859# Check that RENAME has acquired X lock on t1 and is waiting for t2. 860# Check that SU lock is incompatible with X lock. 861# Sending: 862alter table t1 add primary key (c1);; 863# 864connection mdl_con2; 865# Check that the above ALTER statement is blocked 866# because of X lock. 867# Unblock RENAME TABLE 868unlock tables; 869# 870connection default; 871# Reaping RENAME TABLE 872ERROR 42S01: Table 't2' already exists 873# 874connection mdl_con1; 875# Reaping ALTER. 876ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 877# 878# Note that we can't easily check SNW vs X locks since 879# SNW is only used by ALTER TABLE after upgrading from SU 880# and SU is also incompatible with X. 881# 882connection mdl_con2; 883# Prepare for blocking RENAME TABLE. 884lock tables t2 read; 885# 886connection default; 887# Sending: 888rename table t1 to t2;; 889# 890connection mdl_con1; 891# Check that RENAME has acquired X lock on t1 and is waiting for t2. 892# Check that SNRW lock is incompatible with X lock. 893# Sending: 894lock table t1 write;; 895# 896connection mdl_con2; 897# Check that the above LOCK TABLE statement is blocked 898# because of X lock. 899# Unblock RENAME TABLE 900unlock tables; 901# 902connection default; 903# Reaping RENAME TABLE 904ERROR 42S01: Table 't2' already exists 905# 906connection mdl_con1; 907# Reaping LOCK TABLE. 908unlock tables; 909# 910connection mdl_con2; 911# Prepare for blocking RENAME TABLE. 912lock tables t2 read; 913# 914connection default; 915# Sending: 916rename table t1 to t2;; 917# 918connection mdl_con1; 919# Check that RENAME has acquired X lock on t1 and is waiting for t2. 920# Check that X lock is incompatible with X lock. 921# Sending: 922rename table t1 to t3;; 923# 924connection mdl_con2; 925# Check that the above RENAME statement is blocked 926# because of X lock. 927# Unblock RENAME TABLE 928unlock tables; 929# 930connection default; 931# Reaping RENAME TABLE 932ERROR 42S01: Table 't2' already exists 933# 934connection mdl_con1; 935# Reaping RENAME. 936rename table t3 to t1; 937# 938# B) Now let us test compatibility in cases when both locks 939# are pending. I.e. let us test rules for priorities between 940# different types of metadata locks. 941# 942# Note: No tests for pending SU lock as this lock requires 943# even stronger active or pending lock. 944# 945# 946connection mdl_con2; 947# 948# 1) Check compatibility for pending SNW lock. 949# 950# Acquire SW lock in order to create pending SNW lock later. 951begin; 952insert into t1 values (1); 953# 954connection default; 955# Add pending SNW lock. 956# Sending: 957alter table t1 add primary key (c1);; 958# 959connection mdl_con1; 960# Check that ALTER TABLE is waiting with pending SNW lock. 961# Check that S, SH and SR locks are compatible with pending SNW 962handler t1 open t; 963handler t close; 964select column_name from information_schema.columns where 965table_schema='test' and table_name='t1'; 966column_name 967c1 968select count(*) from t1; 969count(*) 9703 971# Check that SW is incompatible with pending SNW 972# Sending: 973delete from t1 limit 1;; 974# 975connection mdl_con2; 976# Check that the above DELETE is blocked because of pending SNW lock. 977# Unblock ALTER TABLE. 978commit; 979# 980connection default; 981# Reaping ALTER. 982ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 983# 984connection mdl_con1; 985# Reaping DELETE. 986# 987# We can't do similar check for SNW, SNRW and X locks because 988# they will also be blocked by active SW lock. 989# 990# 991connection mdl_con2; 992# 993# 2) Check compatibility for pending SNRW lock. 994# 995# Acquire SR lock in order to create pending SNRW lock. 996begin; 997select count(*) from t1; 998count(*) 9992 1000# 1001connection default; 1002# Add pending SNRW lock. 1003# Sending: 1004lock table t1 write;; 1005# 1006connection mdl_con1; 1007# Check that LOCK TABLE is waiting with pending SNRW lock. 1008# Check that S and SH locks are compatible with pending SNRW 1009select column_name from information_schema.columns where 1010table_schema='test' and table_name='t1'; 1011column_name 1012c1 1013# Check that SR is incompatible with pending SNRW 1014# Sending: 1015select count(*) from t1;; 1016# 1017connection mdl_con2; 1018# Check that the above SELECT is blocked because of pending SNRW lock. 1019# Unblock LOCK TABLE. 1020commit; 1021# 1022connection default; 1023# Reaping LOCK TABLE. 1024unlock tables; 1025# 1026connection mdl_con1; 1027# Reaping SELECT. 1028count(*) 10292 1030# Restore pending SNRW lock. 1031# 1032connection mdl_con2; 1033begin; 1034select count(*) from t1; 1035count(*) 10362 1037# 1038connection default; 1039# Sending: 1040lock table t1 write;; 1041# 1042connection mdl_con1; 1043# Check that LOCK TABLE is waiting with pending SNRW lock. 1044# Check that SW is incompatible with pending SNRW 1045# Sending: 1046insert into t1 values (1);; 1047# 1048connection mdl_con2; 1049# Check that the above INSERT is blocked because of pending SNRW lock. 1050# Unblock LOCK TABLE. 1051commit; 1052# 1053connection default; 1054# Reaping LOCK TABLE. 1055unlock tables; 1056# 1057connection mdl_con1; 1058# Reaping INSERT. 1059# Restore pending SNRW lock. 1060# 1061connection mdl_con2; 1062begin; 1063select count(*) from t1; 1064count(*) 10653 1066# 1067connection default; 1068# Sending: 1069lock table t1 write;; 1070# 1071connection mdl_con1; 1072# Check that LOCK TABLE is waiting with pending SNRW lock. 1073# Check that SNW is compatible with pending SNRW 1074# So ALTER TABLE statements are not starved by LOCK TABLEs. 1075alter table t1 add primary key (c1); 1076ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1077# 1078connection mdl_con2; 1079# Unblock LOCK TABLE. 1080commit; 1081# 1082connection default; 1083# Reaping LOCK TABLE. 1084unlock tables; 1085# 1086# We can't do similar check for SNRW and X locks because 1087# they will also be blocked by active SR lock. 1088# 1089# 1090connection mdl_con2; 1091# 1092# 3) Check compatibility for pending X lock. 1093# 1094# Acquire SR lock in order to create pending X lock. 1095begin; 1096select count(*) from t1; 1097count(*) 10983 1099# 1100connection default; 1101# Add pending X lock. 1102# Sending: 1103rename table t1 to t2;; 1104# 1105connection mdl_con1; 1106# Check that RENAME TABLE is waiting with pending X lock. 1107# Check that SH locks are compatible with pending X 1108select column_name from information_schema.columns where 1109table_schema='test' and table_name='t1'; 1110column_name 1111c1 1112# Check that S is incompatible with pending X 1113# Sending: 1114handler t1 open;; 1115# 1116connection mdl_con2; 1117# Check that the above HANDLER OPEN is blocked because of pending X lock. 1118# Unblock RENAME TABLE. 1119commit; 1120# 1121connection default; 1122# Reaping RENAME TABLE. 1123ERROR 42S01: Table 't2' already exists 1124# 1125connection mdl_con1; 1126# Reaping HANDLER t1 OPEN. 1127handler t1 close; 1128# Restore pending X lock. 1129# 1130connection mdl_con2; 1131begin; 1132select count(*) from t1; 1133count(*) 11343 1135# 1136connection default; 1137# Add pending X lock. 1138# Sending: 1139rename table t1 to t2;; 1140# 1141connection mdl_con1; 1142# Check that RENAME TABLE is waiting with pending X lock. 1143# Check that SR is incompatible with pending X 1144# Sending: 1145select count(*) from t1;; 1146# 1147connection mdl_con2; 1148# Check that the above SELECT is blocked because of pending X lock. 1149# Unblock RENAME TABLE. 1150commit; 1151# 1152connection default; 1153# Reaping RENAME TABLE. 1154ERROR 42S01: Table 't2' already exists 1155# 1156connection mdl_con1; 1157# Reaping SELECT. 1158count(*) 11593 1160# Restore pending X lock. 1161# 1162connection mdl_con2; 1163begin; 1164select count(*) from t1; 1165count(*) 11663 1167# 1168connection default; 1169# Add pending X lock. 1170# Sending: 1171rename table t1 to t2;; 1172# 1173connection mdl_con1; 1174# Check that RENAME TABLE is waiting with pending X lock. 1175# Check that SW is incompatible with pending X 1176# Sending: 1177delete from t1 limit 1;; 1178# 1179connection mdl_con2; 1180# Check that the above DELETE is blocked because of pending X lock. 1181# Unblock RENAME TABLE. 1182commit; 1183# 1184connection default; 1185# Reaping RENAME TABLE. 1186ERROR 42S01: Table 't2' already exists 1187# 1188connection mdl_con1; 1189# Reaping DELETE. 1190# Restore pending X lock. 1191# 1192connection mdl_con2; 1193begin; 1194select count(*) from t1; 1195count(*) 11962 1197# 1198connection default; 1199# Add pending X lock. 1200# Sending: 1201rename table t1 to t2;; 1202# 1203connection mdl_con1; 1204# Check that RENAME TABLE is waiting with pending X lock. 1205# Check that SNW is incompatible with pending X 1206# Sending: 1207alter table t1 add primary key (c1);; 1208# 1209connection mdl_con2; 1210# Check that the above ALTER TABLE is blocked because of pending X lock. 1211# Unblock RENAME TABLE. 1212commit; 1213# 1214connection default; 1215# Reaping RENAME TABLE. 1216ERROR 42S01: Table 't2' already exists 1217# 1218connection mdl_con1; 1219# Reaping ALTER TABLE. 1220ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1221# Restore pending X lock. 1222# 1223connection mdl_con2; 1224handler t1 open; 1225# 1226connection default; 1227# Add pending X lock. 1228# Sending: 1229rename table t1 to t2;; 1230# 1231connection mdl_con1; 1232# Check that RENAME TABLE is waiting with pending X lock. 1233# Check that SNRW is incompatible with pending X 1234# Sending: 1235lock table t1 write;; 1236# 1237connection mdl_con3; 1238# Check that the above LOCK TABLES is blocked because of pending X lock. 1239# 1240connection mdl_con2; 1241# Unblock RENAME TABLE. 1242handler t1 close; 1243# 1244connection default; 1245# Reaping RENAME TABLE. 1246ERROR 42S01: Table 't2' already exists 1247# 1248connection mdl_con1; 1249# Reaping LOCK TABLES. 1250unlock tables; 1251# 1252connection default; 1253# 1254# 1255# C) Now let us test how type-of-operation locks are handled in 1256# transactional context. Obviously we are mostly interested 1257# in conflicting types of locks. 1258# 1259# Note: No tests for active/pending SU lock since 1260# ALTER TABLE is in its own transaction. 1261# 1262# 1263# 1) Let us check how various locks used within transactional 1264# context interact with active/pending SNW lock. 1265# 1266# We start with case when we are acquiring lock on the table 1267# which was not used in the transaction before. 1268begin; 1269select count(*) from t1; 1270count(*) 12712 1272# 1273connection mdl_con1; 1274# Create an active SNW lock on t2. 1275# We have to use DEBUG_SYNC facility as otherwise SNW lock 1276# will be immediately released (or upgraded to X lock). 1277insert into t2 values (1), (1); 1278set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 1279# Sending: 1280alter table t2 add primary key (c1), algorithm=copy, lock=shared;; 1281# 1282connection default; 1283set debug_sync= 'now WAIT_FOR locked'; 1284# SR lock should be acquired without any waiting. 1285select count(*) from t2; 1286count(*) 12872 1288commit; 1289# Now let us check that we will wait in case of SW lock. 1290begin; 1291select count(*) from t1; 1292count(*) 12932 1294# Sending: 1295insert into t2 values (1);; 1296# 1297connection mdl_con2; 1298# Check that the above INSERT is blocked. 1299# Unblock ALTER TABLE and thus INSERT. 1300set debug_sync= 'now SIGNAL finish'; 1301# 1302connection mdl_con1; 1303# Reap ALTER TABLE. 1304ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1305# 1306connection default; 1307# Reap INSERT. 1308commit; 1309# 1310# Now let us see what happens when we are acquiring lock on the table 1311# which is already used in transaction. 1312# 1313# *) First, case when transaction which has SR lock on the table also 1314# locked in SNW mode acquires yet another SR lock and then tries 1315# to acquire SW lock. 1316begin; 1317select count(*) from t1; 1318count(*) 13192 1320# 1321connection mdl_con1; 1322# Create an active SNW lock on t1. 1323set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; 1324# Sending: 1325alter table t1 add primary key (c1), algorithm=copy, lock=shared;; 1326# 1327connection default; 1328set debug_sync= 'now WAIT_FOR locked'; 1329# We should still be able to get SR lock without waiting. 1330select count(*) from t1; 1331count(*) 13322 1333# Since the above ALTER TABLE is not upgrading SNW lock to X by waiting 1334# for SW lock we won't create deadlock. 1335# So the below INSERT should not end-up with ER_LOCK_DEADLOCK error. 1336# Sending: 1337insert into t1 values (1);; 1338# 1339connection mdl_con2; 1340# Check that the above INSERT is blocked. 1341# Unblock ALTER TABLE and thus INSERT. 1342set debug_sync= 'now SIGNAL finish'; 1343# 1344connection mdl_con1; 1345# Reap ALTER TABLE. 1346ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1347# 1348connection default; 1349# Reap INSERT. 1350commit; 1351# 1352# **) Now test in which transaction that has SW lock on the table 1353# against which there is pending SNW lock acquires SR and SW 1354# locks on this table. 1355# 1356begin; 1357insert into t1 values (1); 1358# 1359connection mdl_con1; 1360# Create pending SNW lock on t1. 1361# Sending: 1362alter table t1 add primary key (c1);; 1363# 1364connection default; 1365# Wait until ALTER TABLE starts waiting for SNW lock. 1366# We should still be able to get both SW and SR locks without waiting. 1367select count(*) from t1; 1368count(*) 13694 1370delete from t1 limit 1; 1371# Unblock ALTER TABLE. 1372commit; 1373# 1374connection mdl_con1; 1375# Reap ALTER TABLE. 1376ERROR 23000: Duplicate entry '1' for key 'PRIMARY' 1377# 1378connection default; 1379# 1380# 2) Now similar tests for active SNW lock which is being upgraded 1381# to X lock. 1382# 1383# Again we start with case when we are acquiring lock on the 1384# table which was not used in the transaction before. 1385begin; 1386select count(*) from t1; 1387count(*) 13883 1389# 1390connection mdl_con2; 1391# Start transaction which will prevent SNW -> X upgrade from 1392# completing immediately. 1393begin; 1394select count(*) from t2; 1395count(*) 13963 1397# 1398connection mdl_con1; 1399# Create SNW lock pending upgrade to X on t2. 1400# Sending: 1401alter table t2 add column c2 int;; 1402# 1403connection default; 1404# Wait until ALTER TABLE starts waiting X lock. 1405# Check that attempt to acquire SR lock on t2 causes waiting. 1406# Sending: 1407select count(*) from t2;; 1408# 1409connection mdl_con2; 1410# Check that the above SELECT is blocked. 1411# Unblock ALTER TABLE. 1412commit; 1413# 1414connection mdl_con1; 1415# Reap ALTER TABLE. 1416# 1417connection default; 1418# Reap SELECT. 1419count(*) 14203 1421commit; 1422# Do similar check for SW lock. 1423begin; 1424select count(*) from t1; 1425count(*) 14263 1427# 1428connection mdl_con2; 1429# Start transaction which will prevent SNW -> X upgrade from 1430# completing immediately. 1431begin; 1432select count(*) from t2; 1433count(*) 14343 1435# 1436connection mdl_con1; 1437# Create SNW lock pending upgrade to X on t2. 1438# Sending: 1439alter table t2 drop column c2;; 1440# 1441connection default; 1442# Wait until ALTER TABLE starts waiting X lock. 1443# Check that attempt to acquire SW lock on t2 causes waiting. 1444# Sending: 1445insert into t2 values (1);; 1446# 1447connection mdl_con2; 1448# Check that the above INSERT is blocked. 1449# Unblock ALTER TABLE. 1450commit; 1451# 1452connection mdl_con1; 1453# Reap ALTER TABLE. 1454# 1455connection default; 1456# Reap INSERT. 1457commit; 1458# 1459# Test for the case in which we are acquiring lock on the table 1460# which is already used in transaction. 1461# 1462begin; 1463select count(*) from t1; 1464count(*) 14653 1466# 1467connection mdl_con1; 1468# Create SNW lock pending upgrade to X. 1469# Sending: 1470alter table t1 add column c2 int;; 1471# 1472connection default; 1473# Wait until ALTER TABLE starts waiting X lock. 1474# Check that transaction is still able to acquire SR lock. 1475select count(*) from t1; 1476count(*) 14773 1478# Waiting trying to acquire SW lock will cause deadlock and 1479# therefore should cause an error. 1480delete from t1 limit 1; 1481ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 1482# Unblock ALTER TABLE. 1483commit; 1484# 1485connection mdl_con1; 1486# Reap ALTER TABLE. 1487# 1488connection default; 1489# 1490# 3) Check how various locks used within transactional context 1491# interact with active/pending SNRW lock. 1492# 1493# Once again we start with case when we are acquiring lock on 1494# the table which was not used in the transaction before. 1495begin; 1496select count(*) from t1; 1497count(*) 14983 1499# 1500connection mdl_con1; 1501lock table t2 write; 1502# 1503connection default; 1504# Attempt to acquire SR should be blocked. It should 1505# not cause errors as it does not creates deadlock. 1506# Sending: 1507select count(*) from t2;; 1508# 1509connection mdl_con1; 1510# Check that the above SELECT is blocked 1511# Unblock SELECT. 1512unlock tables; 1513# 1514connection default; 1515# Reap SELECT. 1516count(*) 15174 1518commit; 1519# Repeat the same test for SW lock. 1520begin; 1521select count(*) from t1; 1522count(*) 15233 1524# 1525connection mdl_con1; 1526lock table t2 write; 1527# 1528connection default; 1529# Again attempt to acquire SW should be blocked and should 1530# not cause any errors. 1531# Sending: 1532delete from t2 limit 1;; 1533# 1534connection mdl_con1; 1535# Check that the above DELETE is blocked 1536# Unblock DELETE. 1537unlock tables; 1538# 1539connection default; 1540# Reap DELETE. 1541commit; 1542# 1543# Now coverage for the case in which we are acquiring lock on 1544# the table which is already used in transaction and against 1545# which there is a pending SNRW lock request. 1546# 1547# *) Let us start with case when transaction has only a SR lock. 1548# 1549begin; 1550select count(*) from t1; 1551count(*) 15523 1553# 1554connection mdl_con1; 1555# Sending: 1556lock table t1 write;; 1557# 1558connection default; 1559# Wait until LOCK TABLE is blocked creating pending request for X lock. 1560# Check that another instance of SR lock is granted without waiting. 1561select count(*) from t1; 1562count(*) 15633 1564# Attempt to wait for SW lock will lead to deadlock, thus 1565# the below statement should end with ER_LOCK_DEADLOCK error. 1566delete from t1 limit 1; 1567ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 1568# Unblock LOCK TABLES. 1569commit; 1570# 1571connection mdl_con1; 1572# Reap LOCK TABLES. 1573unlock tables; 1574# 1575connection default; 1576# 1577# **) Now case when transaction has a SW lock. 1578# 1579begin; 1580delete from t1 limit 1; 1581# 1582connection mdl_con1; 1583# Sending: 1584lock table t1 write;; 1585# 1586connection default; 1587# Wait until LOCK TABLE is blocked creating pending request for X lock. 1588# Check that both SR and SW locks are granted without waiting 1589# and errors. 1590select count(*) from t1; 1591count(*) 15922 1593insert into t1 values (1, 1); 1594# Unblock LOCK TABLES. 1595commit; 1596# 1597connection mdl_con1; 1598# Reap LOCK TABLES. 1599unlock tables; 1600# 1601connection default; 1602# 1603# 4) Check how various locks used within transactional context 1604# interact with active/pending X lock. 1605# 1606# As usual we start with case when we are acquiring lock on 1607# the table which was not used in the transaction before. 1608begin; 1609select count(*) from t1; 1610count(*) 16113 1612# 1613connection mdl_con2; 1614# Start transaction which will prevent X lock from going away 1615# immediately. 1616begin; 1617select count(*) from t2; 1618count(*) 16193 1620# 1621connection mdl_con1; 1622# Create pending X lock on t2. 1623# Sending: 1624rename table t2 to t3;; 1625# 1626connection default; 1627# Wait until RENAME TABLE starts waiting with pending X lock. 1628# Check that attempt to acquire SR lock on t2 causes waiting. 1629# Sending: 1630select count(*) from t2;; 1631# 1632connection mdl_con2; 1633# Check that the above SELECT is blocked. 1634# Unblock RENAME TABLE. 1635commit; 1636# 1637connection mdl_con1; 1638# Reap RENAME TABLE. 1639# 1640connection default; 1641# Reap SELECT. 1642ERROR 42S02: Table 'test.t2' doesn't exist 1643commit; 1644rename table t3 to t2; 1645# The same test for SW lock. 1646begin; 1647select count(*) from t1; 1648count(*) 16493 1650# 1651connection mdl_con2; 1652# Start transaction which will prevent X lock from going away 1653# immediately. 1654begin; 1655select count(*) from t2; 1656count(*) 16573 1658# 1659connection mdl_con1; 1660# Create pending X lock on t2. 1661# Sending: 1662rename table t2 to t3;; 1663# 1664connection default; 1665# Wait until RENAME TABLE starts waiting with pending X lock. 1666# Check that attempt to acquire SW lock on t2 causes waiting. 1667# Sending: 1668delete from t2 limit 1;; 1669# 1670connection mdl_con2; 1671# Check that the above DELETE is blocked. 1672# Unblock RENAME TABLE. 1673commit; 1674# 1675connection mdl_con1; 1676# Reap RENAME TABLE. 1677# 1678connection default; 1679# Reap DELETE. 1680ERROR 42S02: Table 'test.t2' doesn't exist 1681commit; 1682rename table t3 to t2; 1683# 1684# Coverage for the case in which we are acquiring lock on 1685# the table which is already used in transaction and against 1686# which there is a pending X lock request. 1687# 1688# *) The first case is when transaction has only a SR lock. 1689# 1690begin; 1691select count(*) from t1; 1692count(*) 16933 1694# 1695connection mdl_con1; 1696# Sending: 1697rename table t1 to t2;; 1698# 1699connection default; 1700# Wait until RENAME TABLE is blocked creating pending request for X lock. 1701# Check that another instance of SR lock is granted without waiting. 1702select count(*) from t1; 1703count(*) 17043 1705# Attempt to wait for SW lock will lead to deadlock, thus 1706# the below statement should end with ER_LOCK_DEADLOCK error. 1707delete from t1 limit 1; 1708ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 1709# Unblock RENAME TABLE. 1710commit; 1711# 1712connection mdl_con1; 1713# Reap RENAME TABLE. 1714ERROR 42S01: Table 't2' already exists 1715# 1716connection default; 1717# 1718# **) The second case is when transaction has a SW lock. 1719# 1720begin; 1721delete from t1 limit 1; 1722# 1723connection mdl_con1; 1724# Sending: 1725rename table t1 to t2;; 1726# 1727connection default; 1728# Wait until RENAME TABLE is blocked creating pending request for X lock. 1729# Check that both SR and SW locks are granted without waiting 1730# and errors. 1731select count(*) from t1; 1732count(*) 17332 1734insert into t1 values (1, 1); 1735# Unblock RENAME TABLE. 1736commit; 1737# 1738connection mdl_con1; 1739# Reap RENAME TABLE. 1740ERROR 42S01: Table 't2' already exists 1741# 1742connection default; 1743# Clean-up. 1744disconnect mdl_con1; 1745disconnect mdl_con2; 1746disconnect mdl_con3; 1747set debug_sync= 'RESET'; 1748drop table t1, t2; 1749# 1750# Test coverage for basic deadlock detection in metadata 1751# locking subsystem. 1752# 1753drop tables if exists t0, t1, t2, t3, t4, t5; 1754set debug_sync= 'RESET'; 1755connect deadlock_con1,localhost,root,,; 1756connect deadlock_con2,localhost,root,,; 1757connect deadlock_con3,localhost,root,,; 1758connection default; 1759create table t1 (i int); 1760create table t2 (j int); 1761create table t3 (k int); 1762create table t4 (k int); 1763# 1764# Test for the case in which no deadlock occurs. 1765# 1766# 1767connection deadlock_con1; 1768begin; 1769insert into t1 values (1); 1770# 1771connection deadlock_con2; 1772begin; 1773insert into t2 values (1); 1774# 1775connection default; 1776# Send: 1777rename table t2 to t0, t3 to t2, t0 to t3;; 1778# 1779connection deadlock_con1; 1780# Wait until the above RENAME TABLE is blocked because it has to wait 1781# for 'deadlock_con2' which holds shared metadata lock on 't2'. 1782# The below statement should wait for exclusive metadata lock 1783# on 't2' to go away and should not produce ER_LOCK_DEADLOCK 1784# as no deadlock is possible in this situation. 1785# Send: 1786select * from t2;; 1787# 1788connection deadlock_con2; 1789# Wait until the above SELECT * FROM t2 is starts waiting 1790# for an exclusive metadata lock to go away. 1791# 1792# Unblock RENAME TABLE by releasing shared metadata lock on t2. 1793commit; 1794# 1795connection default; 1796# Reap RENAME TABLE. 1797# 1798connection deadlock_con1; 1799# Reap SELECT. 1800k 1801# 1802connection default; 1803# 1804# Let us check that in the process of waiting for conflicting lock 1805# on table 't2' to go away transaction in connection 'deadlock_con1' 1806# has not released metadata lock on table 't1'. 1807# Send: 1808rename table t1 to t0, t3 to t1, t0 to t3;; 1809# 1810connection deadlock_con1; 1811# Wait until the above RENAME TABLE is blocked because it has to wait 1812# for 'deadlock_con1' which should still hold shared metadata lock on 1813# table 't1'. 1814# Commit transaction to unblock RENAME TABLE. 1815commit; 1816# 1817connection default; 1818# Reap RENAME TABLE. 1819# 1820# Test for case when deadlock occurs and should be detected immediately. 1821# 1822# 1823connection deadlock_con1; 1824begin; 1825insert into t2 values (2); 1826# 1827connection default; 1828# Send: 1829rename table t2 to t0, t1 to t2, t0 to t1;; 1830# 1831connection deadlock_con1; 1832# Wait until the above RENAME TABLE is blocked because it has to wait 1833# for 'deadlock_con1' which holds shared metadata lock on 't2'. 1834# 1835# The below statement should not wait as doing so will cause deadlock. 1836# Instead it should fail and emit ER_LOCK_DEADLOCK statement and 1837# transaction should be rolled back. 1838select * from t1; 1839ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 1840# 1841connection default; 1842# Reap RENAME TABLE. 1843# 1844# Test for the case in which deadlock also occurs but not immediately. 1845# 1846# 1847connection deadlock_con1; 1848begin; 1849insert into t2 values (1); 1850# 1851connection default; 1852lock table t1 write; 1853# 1854connection deadlock_con1; 1855# The below SELECT statement should wait for metadata lock 1856# on table 't1' and should not produce ER_LOCK_DEADLOCK 1857# immediately as no deadlock is possible at the moment. 1858select * from t1;; 1859# 1860connection deadlock_con2; 1861# Wait until the above SELECT * FROM t1 is starts waiting 1862# for an UNRW metadata lock to go away. 1863# Send RENAME TABLE statement that will deadlock with the 1864# SELECT statement and thus should abort the latter. 1865rename table t1 to t0, t2 to t1, t0 to t2;; 1866# 1867connection default; 1868# Wait till above RENAME TABLE is blocked while holding 1869# pending X lock on t1. 1870# Allow the above RENAME TABLE to acquire lock on t1 and 1871# create pending lock on t2 thus creating deadlock. 1872unlock tables; 1873# 1874connection deadlock_con1; 1875# Since the latest RENAME TABLE entered in deadlock with SELECT 1876# statement the latter should be aborted and emit ER_LOCK_DEADLOCK 1877# error and transaction should be rolled back. 1878# Reap SELECT * FROM t1. 1879ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 1880# 1881connection deadlock_con2; 1882# Reap RENAME TABLE ... . 1883# 1884connection default; 1885drop tables t1, t2, t3, t4; 1886# 1887# Now, test case which shows that deadlock detection empiric 1888# also takes into account requests for metadata lock upgrade. 1889# 1890create table t1 (i int); 1891insert into t1 values (1); 1892# Avoid race which occurs when SELECT in 'deadlock_con1' connection 1893# accesses table before the above INSERT unlocks the table and thus 1894# its result becomes visible to other connections. 1895select * from t1; 1896i 18971 1898# 1899connection deadlock_con1; 1900begin; 1901select * from t1; 1902i 19031 1904# 1905connection default; 1906# Send: 1907alter table t1 add column j int, rename to t2;; 1908# 1909connection deadlock_con1; 1910# Wait until the above ALTER TABLE ... RENAME acquires exclusive 1911# metadata lock on 't2' and starts waiting for connection 1912# 'deadlock_con1' which holds shared lock on 't1'. 1913# The below statement should not wait as it will cause deadlock. 1914# An appropriate error should be reported instead and transaction 1915# should be rolled back. 1916select * from t2; 1917ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 1918# 1919connection default; 1920# Reap ALTER TABLE ... RENAME. 1921drop table t2; 1922# 1923# Test that in situation when MDL subsystem detects a deadlock 1924# but it turns out that it can be resolved by backing-off locks 1925# acquired by one of participating transactions (which is 1926# possible when one of transactions consists only of currently 1927# executed statement, e.g. in autocommit mode) no error is 1928# reported. 1929# 1930create table t1 (i int); 1931create table t2 (j int); 1932# Ensure that the below SELECT stops once it has acquired metadata 1933# lock on table 't2'. 1934set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 1935# Sending: 1936select * from t2, t1; 1937# 1938connection deadlock_con1; 1939# Wait till SELECT acquires MDL on 't2' and starts waiting for signal. 1940set debug_sync= 'now WAIT_FOR locked'; 1941# Sending: 1942lock tables t1 write, t2 write; 1943# 1944connection deadlock_con2; 1945# Wait until LOCK TABLES acquires SNRW lock on 't1' and is blocked 1946# while trying to acquire SNRW lock on 't1'. 1947# Resume SELECT execution, this should eventually unblock LOCK TABLES. 1948set debug_sync= 'now SIGNAL finish'; 1949# 1950connection deadlock_con1; 1951# Reaping LOCK TABLES. 1952unlock tables; 1953# 1954connection default; 1955# Reaping SELECT. It succeed and not report ER_LOCK_DEADLOCK error. 1956j i 1957drop tables t1, t2; 1958# 1959# Test coverage for situation in which a race has happened 1960# during deadlock detection process which led to unwarranted 1961# ER_LOCK_DEADLOCK error. 1962# 1963create table t1 (i int); 1964# Ensure that ALTER waits once it has acquired SNW lock. 1965set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL parked1 WAIT_FOR go1'; 1966# Sending: 1967alter table t1 add column j int; 1968# 1969connection deadlock_con1; 1970# Wait till ALTER acquires SNW lock and stops. 1971set debug_sync='now WAIT_FOR parked1'; 1972# Ensure that INSERT is paused once it detects that there is 1973# a conflicting metadata lock so it has to wait, but before 1974# deadlock detection is run. 1975set debug_sync='mdl_acquire_lock_wait SIGNAL parked2 WAIT_FOR go2'; 1976# Sending: 1977insert into t1 values (); 1978# 1979connection deadlock_con2; 1980# Wait till INSERT is paused. 1981set debug_sync='now WAIT_FOR parked2'; 1982# Resume ALTER execution. Eventually it will release its 1983# metadata lock and INSERT's request for SW lock will be 1984# satisified. 1985set debug_sync='now SIGNAL go1'; 1986# 1987connection default; 1988# Reaping ALTER TABLE. 1989# Add a new request for SNW lock to waiting graph. 1990# Sending: 1991alter table t1 drop column j; 1992# 1993connection deadlock_con2; 1994# Wait until ALTER is blocked. 1995# Resume INSERT so it can start deadlock detection. 1996# 1997# At this point there is a discrepancy between the fact that INSERT's 1998# SW lock is already satisfied, but INSERT's connection is still 1999# marked as waiting for it. Looking for a loop in waiters graph 2000# without additional checks has detected a deadlock (INSERT waits 2001# for SW lock; which is not granted because of pending SNW lock from 2002# ALTER; which waits for active SW lock from INSERT). Since requests 2003# for SW and SNW locks have same weight ALTER was selected as a victim 2004# and ended with ER_LOCK_DEADLOCK error. 2005set debug_sync='now SIGNAL go2'; 2006# 2007connection deadlock_con1; 2008# Reaping INSERT. 2009# 2010connection default; 2011# Reaping ALTER. It should succeed and not produce ER_LOCK_DEADLOCK. 2012drop table t1; 2013# 2014# Now, test for a situation in which deadlock involves waiting not 2015# only in MDL subsystem but also for TDC. Such deadlocks should be 2016# successfully detected. If possible, they should be resolved without 2017# resorting to ER_LOCK_DEADLOCK error. 2018# 2019create table t1(i int); 2020create table t2(j int); 2021# 2022# First, let us check how we handle a simple scenario involving 2023# waits in MDL and TDC. 2024# 2025set debug_sync= 'RESET'; 2026connection deadlock_con1; 2027# Start a statement, which will acquire SR metadata lock on t1, open it 2028# and then stop, before trying to acquire SW lock on t2 and opening it. 2029set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; 2030# Sending: 2031select * from t1 where i in (select j from t2 for update); 2032connection deadlock_con2; 2033# Wait till the above SELECT stops. 2034set debug_sync='now WAIT_FOR parked'; 2035# The below FLUSH TABLES WITH READ LOCK should acquire 2036# SNW locks on t1 and t2 and wait till SELECT closes t1. 2037# Sending: 2038flush tables t1, t2 with read lock; 2039connection deadlock_con3; 2040# Wait until FLUSH TABLES WITH t1, t2 READ LOCK starts waiting 2041# for SELECT to close t1. 2042# Resume SELECT, so it tries to acquire SW lock on t1 and blocks, 2043# creating a deadlock. This deadlock should be detected and resolved 2044# by backing-off SELECT. As a result FTWRL should be able to finish. 2045set debug_sync='now SIGNAL go'; 2046connection deadlock_con2; 2047# Reap FLUSH TABLES WITH READ LOCK. 2048unlock tables; 2049connection deadlock_con1; 2050# Reap SELECT. 2051i 2052# 2053# The same scenario with a slightly different order of events 2054# which emphasizes that setting correct deadlock detector weights 2055# for flush waits is important. 2056# 2057set debug_sync= 'RESET'; 2058connection deadlock_con2; 2059set debug_sync='flush_tables_with_read_lock_after_acquire_locks SIGNAL parked WAIT_FOR go'; 2060# The below FLUSH TABLES WITH READ LOCK should acquire 2061# SNW locks on t1 and t2 and wait on debug sync point. 2062# Sending: 2063flush tables t1, t2 with read lock; 2064connection deadlock_con1; 2065# Wait till FLUSH TABLE WITH READ LOCK stops. 2066set debug_sync='now WAIT_FOR parked'; 2067# Start statement which will acquire SR metadata lock on t1, open 2068# it and then will block while trying to acquire SW lock on t2. 2069# Sending: 2070select * from t1 where i in (select j from t2 for update); 2071connection deadlock_con3; 2072# Wait till the above SELECT blocks. 2073# Resume FLUSH TABLES, so it tries to flush t1, thus creating 2074# a deadlock. This deadlock should be detected and resolved by 2075# backing-off SELECT. As a result FTWRL should be able to finish. 2076set debug_sync='now SIGNAL go'; 2077connection deadlock_con2; 2078# Reap FLUSH TABLES WITH READ LOCK. 2079unlock tables; 2080connection deadlock_con1; 2081# Reap SELECT. 2082i 2083# 2084# Now a more complex scenario involving two connections 2085# waiting for MDL and one for TDC. 2086# 2087set debug_sync= 'RESET'; 2088connection deadlock_con1; 2089# Start a statement which will acquire SR metadata lock on t2, open it 2090# and then stop, before trying to acquire SR on t1 and opening it. 2091set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; 2092# Sending: 2093select * from t2, t1; 2094connection deadlock_con2; 2095# Wait till the above SELECT stops. 2096set debug_sync='now WAIT_FOR parked'; 2097# The below FLUSH TABLES WITH READ LOCK should acquire 2098# SNW locks on t2 and wait till SELECT closes t2. 2099# Sending: 2100flush tables t2 with read lock; 2101connection deadlock_con3; 2102# Wait until FLUSH TABLES WITH READ LOCK starts waiting 2103# for SELECT to close t2. 2104# The below DROP TABLES should acquire X lock on t1 and start 2105# waiting for X lock on t2. 2106# Sending: 2107drop tables t1, t2; 2108connection default; 2109# Wait until DROP TABLES starts waiting for X lock on t2. 2110# Resume SELECT, so it tries to acquire SR lock on t1 and blocks, 2111# creating a deadlock. This deadlock should be detected and resolved 2112# by backing-off SELECT. As a result, FTWRL should be able to finish. 2113set debug_sync='now SIGNAL go'; 2114connection deadlock_con2; 2115# Reap FLUSH TABLES WITH READ LOCK. 2116# Unblock DROP TABLES. 2117unlock tables; 2118connection deadlock_con3; 2119# Reap DROP TABLES. 2120connection deadlock_con1; 2121# Reap SELECT. It should emit error about missing table. 2122ERROR 42S02: Table 'test.t2' doesn't exist 2123connection default; 2124set debug_sync= 'RESET'; 2125disconnect deadlock_con1; 2126disconnect deadlock_con2; 2127disconnect deadlock_con3; 2128# 2129# Test for a scenario in which FLUSH TABLES <list> WITH READ LOCK 2130# used to erroneously release metadata locks. 2131# 2132connect con1,localhost,root,,; 2133connect con2,localhost,root,,; 2134connection default; 2135drop tables if exists t1, t2; 2136set debug_sync= 'RESET'; 2137create table t1(i int); 2138create table t2(j int); 2139connection con2; 2140set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go'; 2141# The below FLUSH TABLES <list> WITH READ LOCK should acquire 2142# SNW locks on t1 and t2, open table t1 and block on the debug 2143# sync point. 2144# Sending: 2145flush tables t1, t2 with read lock; 2146connection con1; 2147# Wait till FLUSH TABLES <list> WITH READ LOCK stops. 2148set debug_sync='now WAIT_FOR parked'; 2149flush tables; 2150# Start a statement which will flush all tables and thus 2151# invalidate table t1 open by FLUSH TABLES <list> WITH READ LOCK. 2152# Sending: 2153flush tables t1; 2154connection default; 2155# Wait till the above FLUSH TABLES blocks. 2156# Resume FLUSH TABLES <list> WITH READ LOCK, so it tries to open t2 2157# discovers that its t1 is obsolete and tries to reopen all tables. 2158# Such reopen should not cause releasing of SNW metadata locks 2159# which would result in assertion failures. 2160set debug_sync='now SIGNAL go'; 2161connection con2; 2162# Reap FLUSH TABLES <list> WITH READ LOCK. 2163unlock tables; 2164connection con1; 2165# Reap FLUSH TABLES. 2166# Clean-up. 2167connection default; 2168drop tables t1, t2; 2169set debug_sync= 'RESET'; 2170disconnect con1; 2171disconnect con2; 2172# 2173# Test for bug #46748 "Assertion in MDL_context::wait_for_locks() 2174# on INSERT + CREATE TRIGGER". 2175# 2176drop tables if exists t1, t2, t3, t4, t5; 2177# Let us simulate scenario in which we open some tables from extended 2178# part of prelocking set but then encounter conflicting metadata lock, 2179# so have to back-off and wait for it to go away. 2180connect con1root,localhost,root,,test,,; 2181connect con2root,localhost,root,,test,,; 2182connection default; 2183create table t1 (i int); 2184create table t2 (j int); 2185create table t3 (k int); 2186create table t4 (l int); 2187create trigger t1_bi before insert on t1 for each row 2188insert into t2 values (new.i); 2189create trigger t2_bi before insert on t2 for each row 2190insert into t3 values (new.j); 2191# 2192connection con1root; 2193lock tables t4 read; 2194# 2195connection con2root; 2196# Send : 2197rename table t3 to t5, t4 to t3;; 2198# 2199connection default; 2200# Wait until the above RENAME TABLE adds pending requests for exclusive 2201# metadata lock on its tables and blocks due to 't4' being used by LOCK 2202# TABLES. 2203# Send : 2204insert into t1 values (1);; 2205# 2206connection con1root; 2207# Wait until INSERT statement waits due to encountering pending 2208# exclusive metadata lock on 't3'. 2209unlock tables; 2210# 2211connection con2root; 2212# Reap RENAME TABLE. 2213# 2214connection default; 2215# Reap INSERT. 2216# Clean-up. 2217disconnect con1root; 2218disconnect con2root; 2219drop tables t1, t2, t3, t5; 2220# 2221# Bug#42546 - Backup: RESTORE fails, thinking it finds an existing table 2222# 2223DROP TABLE IF EXISTS t1; 2224set @save_log_output=@@global.log_output; 2225set global log_output=file; 2226connect con2, localhost, root,,; 2227# 2228# Test 1: CREATE TABLE 2229# 2230connection con2; 2231# Start insert on the not-yet existing table 2232# Wait after taking the MDL lock 2233SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 2234INSERT INTO t1 VALUES(1,"def"); 2235connection default; 2236SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2237# Now INSERT has a MDL on the non-existent table t1. 2238# 2239# Continue the INSERT once CREATE waits for exclusive lock 2240SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish'; 2241# Try to create that table. 2242CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1)); 2243# Insert fails 2244connection con2; 2245ERROR 42S02: Table 'test.t1' doesn't exist 2246connection default; 2247SET DEBUG_SYNC= 'RESET'; 2248SHOW TABLES; 2249Tables_in_test 2250t1 2251DROP TABLE IF EXISTS t1; 2252# 2253# Test 2: CREATE TABLE LIKE 2254# 2255CREATE TABLE t2 (c1 INT, c2 VARCHAR(100), KEY(c1)); 2256connection con2; 2257# Start insert on the not-yet existing table 2258# Wait after taking the MDL 2259SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; 2260INSERT INTO t1 VALUES(1,"def"); 2261connection default; 2262SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2263# Now INSERT has a MDL on the non-existent table t1. 2264# 2265# Continue the INSERT once CREATE waits for exclusive lock 2266SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish'; 2267# Try to create that table. 2268CREATE TABLE t1 LIKE t2; 2269# Insert fails 2270connection con2; 2271ERROR 42S02: Table 'test.t1' doesn't exist 2272connection default; 2273SET DEBUG_SYNC= 'RESET'; 2274SHOW TABLES; 2275Tables_in_test 2276t1 2277t2 2278DROP TABLE t2; 2279disconnect con2; 2280DROP TABLE IF EXISTS t1; 2281set global log_output=@save_log_output; 2282# 2283# Bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY 2284# FOR UPDATE" 2285# 2286drop tables if exists t1, t2; 2287connect con46044, localhost, root,,; 2288connect con46044_2, localhost, root,,; 2289connect con46044_3, localhost, root,,; 2290connection default; 2291create table t1 (i int); 2292insert into t1 values(1); 2293# Let us check that we won't deadlock if during filling 2294# of I_S table we encounter conflicting metadata lock 2295# which owner is in its turn waiting for our connection. 2296lock tables t1 read; 2297connection con46044_2; 2298# Sending: 2299update t1 set i = 2; 2300connection con46044; 2301# Waiting until UPDATE t1 SET ... is blocked. 2302# Sending: 2303create table t2 select * from t1;; 2304connection default; 2305# Waiting until CREATE TABLE ... SELECT ... is blocked. 2306# First let us check that SHOW FIELDS/DESCRIBE doesn't 2307# gets blocked and emits and error. 2308show fields from t2; 2309ERROR HY000: Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement 2310# Now test for I_S query which reads only .FRMs. 2311# 2312# Query below should only emit a warning. 2313select column_name from information_schema.columns 2314where table_schema='test' and table_name='t2'; 2315column_name 2316Warnings: 2317Warning 1684 Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement 2318# Finally, test for I_S query which does full-blown table open. 2319# 2320# Query below should not be blocked. Warning message should be 2321# stored in the 'table_comment' column. 2322select table_name, table_type, auto_increment, table_comment 2323from information_schema.tables where table_schema='test' and table_name='t2'; 2324table_name table_type auto_increment table_comment 2325t2 BASE TABLE NULL Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement 2326Warnings: 2327Warning 1684 Table 'test'.'t2' was skipped since its definition is being modified by concurrent DDL statement 2328connection default; 2329unlock tables; 2330connection con46044; 2331# Reaping CREATE TABLE ... SELECT ... . 2332drop table t2; 2333connection con46044_2; 2334# Reaping UPDATE t1 statement 2335# 2336# Let us also check that queries to I_S wait for conflicting metadata 2337# locks to go away instead of skipping table with a warning in cases 2338# when deadlock is not possible. This is a nice thing from compatibility 2339# and ease of use points of view. 2340# 2341# We check same three queries to I_S in this new situation. 2342connection con46044_2; 2343lock tables t1 read; 2344connection con46044_3; 2345# Sending: 2346update t1 set i = 3; 2347connection con46044; 2348# Waiting until UPDATE t1 SET ... is blocked. 2349# Sending: 2350create table t2 select * from t1;; 2351connection default; 2352# Waiting until CREATE TABLE ... SELECT ... is blocked. 2353# Let us check that SHOW FIELDS/DESCRIBE gets blocked. 2354# Sending: 2355show fields from t2;; 2356connection con46044_2; 2357# Wait until SHOW FIELDS gets blocked. 2358unlock tables; 2359connection con46044; 2360# Reaping CREATE TABLE ... SELECT ... . 2361connection default; 2362# Reaping SHOW FIELDS ... 2363Field Type Null Key Default Extra 2364i int(11) YES NULL 2365drop table t2; 2366connection con46044_3; 2367# Reaping UPDATE t1 statement 2368connection con46044_2; 2369lock tables t1 read; 2370connection con46044_3; 2371# Sending: 2372update t1 set i = 4; 2373connection con46044; 2374# Waiting until UPDATE t1 SET ... is blocked. 2375# Sending: 2376create table t2 select * from t1;; 2377connection default; 2378# Waiting until CREATE TABLE ... SELECT ... is blocked. 2379# Check that I_S query which reads only .FRMs gets blocked. 2380# Sending: 2381select column_name from information_schema.columns where table_schema='test' and table_name='t2';; 2382connection con46044_2; 2383# Wait until SELECT COLUMN_NAME FROM I_S.COLUMNS gets blocked. 2384unlock tables; 2385connection con46044; 2386# Reaping CREATE TABLE ... SELECT ... . 2387connection default; 2388# Reaping SELECT COLUMN_NAME FROM I_S.COLUMNS 2389column_name 2390i 2391drop table t2; 2392connection con46044_3; 2393# Reaping UPDATE t1 statement 2394connection con46044_2; 2395lock tables t1 read; 2396connection con46044_3; 2397# Sending: 2398update t1 set i = 5; 2399connection con46044; 2400# Waiting until UPDATE t1 SET ... is blocked. 2401# Sending: 2402create table t2 select * from t1;; 2403connection default; 2404# Waiting until CREATE TABLE ... SELECT ... is blocked. 2405# Finally, check that I_S query which does full-blown table open 2406# also gets blocked. 2407# Sending: 2408select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t2';; 2409connection con46044_2; 2410# Wait until SELECT ... FROM I_S.TABLES gets blocked. 2411unlock tables; 2412connection con46044; 2413# Reaping CREATE TABLE ... SELECT ... . 2414connection default; 2415# Reaping SELECT ... FROM I_S.TABLES 2416table_name table_type auto_increment table_comment 2417t2 BASE TABLE NULL 2418drop table t2; 2419connection con46044_3; 2420# Reaping UPDATE t1 statement 2421connection default; 2422# Clean-up. 2423disconnect con46044; 2424disconnect con46044_2; 2425disconnect con46044_3; 2426drop table t1; 2427# 2428# Test for bug #46273 "MySQL 5.4.4 new MDL: Bug#989 is not fully fixed 2429# in case of ALTER". 2430# 2431drop table if exists t1; 2432set debug_sync= 'RESET'; 2433connect con46273,localhost,root,,test,,; 2434connection default; 2435create table t1 (c1 int primary key, c2 int, c3 int); 2436insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0); 2437begin; 2438select * from t1 where c2 = 3; 2439c1 c2 c3 24403 3 0 2441# 2442connection con46273; 2443set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL alter_table_locked WAIT_FOR alter_go'; 2444alter table t1 add column e int, rename to t2;; 2445# 2446connection default; 2447set debug_sync='now WAIT_FOR alter_table_locked'; 2448set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go'; 2449# The below statement should get ER_LOCK_DEADLOCK error 2450# (i.e. it should not allow ALTER to proceed, and then 2451# fail due to 't1' changing its name to 't2'). 2452update t1 set c3=c3+1 where c2 = 3; 2453ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2454# 2455connection con46273; 2456# Reap ALTER TABLE. 2457# 2458connection default; 2459disconnect con46273; 2460# Clean-up. 2461set debug_sync= 'RESET'; 2462drop table t2; 2463# 2464# Test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK 2465# and DML". 2466# 2467drop tables if exists t1; 2468connect con46673, localhost, root,,; 2469connection default; 2470create table t1 (i int); 2471connection con46673; 2472begin; 2473insert into t1 values (1); 2474connection default; 2475# Statement below should not get blocked. And if after some 2476# changes to code it is there should not be a deadlock between 2477# it and transaction from connection 'con46673'. 2478flush tables with read lock; 2479unlock tables; 2480connection con46673; 2481delete from t1 where i = 1; 2482commit; 2483connection default; 2484# Clean-up 2485disconnect con46673; 2486drop table t1; 2487# 2488# Bug#48210 FLUSH TABLES WITH READ LOCK deadlocks 2489# against concurrent CREATE PROCEDURE 2490# 2491connect con2, localhost, root; 2492# Test 1: CREATE PROCEDURE 2493connection default; 2494# Start CREATE PROCEDURE and open mysql.proc 2495SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait'; 2496CREATE PROCEDURE p1() SELECT 1; 2497connection con2; 2498SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 2499# Check that FLUSH must wait to get the GRL 2500# and let CREATE PROCEDURE continue 2501SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; 2502FLUSH TABLES WITH READ LOCK; 2503connection default; 2504connection con2; 2505UNLOCK TABLES; 2506connection default; 2507SET DEBUG_SYNC= 'RESET'; 2508# Test 2: DROP PROCEDURE 2509connection default; 2510# Start DROP PROCEDURE and open tables 2511SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait'; 2512DROP PROCEDURE p1; 2513connection con2; 2514SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 2515# Check that FLUSH must wait to get the GRL 2516# and let DROP PROCEDURE continue 2517SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 2518LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 2519MDL_BACKUP_DDL Backup lock 2520MDL_EXCLUSIVE Stored procedure metadata lock test p1 2521MDL_INTENTION_EXCLUSIVE Schema metadata lock test 2522MDL_SHARED_WRITE Table metadata lock mysql proc 2523SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; 2524FLUSH TABLES WITH READ LOCK; 2525connection default; 2526# Once FLUSH TABLES WITH READ LOCK starts waiting 2527# DROP PROCEDURE will be waked up and will drop 2528# procedure. Global read lock will be granted after 2529# this statement ends. 2530# 2531# Reaping DROP PROCEDURE. 2532connection con2; 2533# Reaping FTWRL. 2534UNLOCK TABLES; 2535connection default; 2536SET DEBUG_SYNC= 'RESET'; 2537# 2538# UPDATE should wait for FTWRL with non transactional table second 2539# 2540create table t1 (a int) engine=myisam; 2541create table t2 (a int) engine=innodb; 2542insert into t1 values (1); 2543insert into t2 values (1); 2544SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait execute 2'; 2545update t1,t2 set t1.a=2,t2.a=3; 2546connection con2; 2547SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 2548SET DEBUG_SYNC= 'now SIGNAL grlwait'; 2549SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 2550SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; 2551FLUSH TABLES WITH READ LOCK; 2552connection default; 2553# Reaping UPDATE 2554connection con2; 2555UNLOCK TABLES; 2556connection default; 2557SET DEBUG_SYNC= 'RESET'; 2558SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait execute 2'; 2559update t2,t1 set t1.a=2,t2.a=3; 2560connection con2; 2561SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 2562SET DEBUG_SYNC= 'now SIGNAL grlwait'; 2563SET DEBUG_SYNC= 'now WAIT_FOR table_opened'; 2564SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait'; 2565FLUSH TABLES WITH READ LOCK; 2566SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 2567LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 2568MDL_BACKUP_FTWRL2 Backup lock 2569unlock tables; 2570connection default; 2571# Reaping UPDATE 2572SET DEBUG_SYNC= 'RESET'; 2573drop table t1,t2; 2574disconnect con2; 2575# 2576# Bug#50786 Assertion `thd->mdl_context.trans_sentinel() == __null' 2577# failed in open_ltable() 2578# 2579# Supress warnings written to the log file 2580call mtr.add_suppression("Wait on a lock was aborted due to a pending exclusive lock"); 2581connect con1,localhost,root; 2582connect con2,localhost,root; 2583connect con3,localhost,root; 2584connection default; 2585CREATE TABLE t1 (i INT); 2586CREATE TABLE t2 (i INT); 2587SET @old_general_log= @@global.general_log; 2588SET @@global.general_log= 1; 2589SET @old_log_output= @@global.log_output; 2590SET @@global.log_output= 'TABLE'; 2591SET @old_sql_log_off= @@session.sql_log_off; 2592SET @@session.sql_log_off= 1; 2593# connection: con1 2594connection con1; 2595HANDLER t1 OPEN; 2596# connection: con3 2597connection con3; 2598SET @@session.sql_log_off= 1; 2599# connection: con2 2600connection con2; 2601SET DEBUG_SYNC= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; 2602# Sending: 2603SELECT 1; 2604# connection: con3 2605connection con3; 2606SET DEBUG_SYNC= 'now WAIT_FOR parked'; 2607# connection: con1 2608connection con1; 2609# Sending: 2610SELECT 1; 2611# connection: con3 2612connection con3; 2613ALTER TABLE t1 ADD COLUMN j INT; 2614# connection: default 2615connection default; 2616SET DEBUG_SYNC= 'now SIGNAL go'; 2617# connection: con1 2618connection con1; 2619# Reaping SELECT 1 26201 26211 2622HANDLER t1 CLOSE; 2623# connection: con2 2624connection con2; 2625# Reaping SELECT 1 26261 26271 2628# connection: default 2629connection default; 2630DROP TABLE t1, t2; 2631SET DEBUG_SYNC= 'RESET'; 2632disconnect con1; 2633disconnect con2; 2634disconnect con3; 2635SET @@global.general_log= @old_general_log; 2636SET @@global.log_output= @old_log_output; 2637SET @@session.sql_log_off= @old_sql_log_off; 2638# 2639# Additional coverage for bug #50913 "Deadlock between 2640# open_and_lock_tables_derived and MDL". The main test 2641# case is in lock_multi.test 2642# 2643drop table if exists t1; 2644set debug_sync= 'RESET'; 2645connect con50913_1,localhost,root; 2646connect con50913_2,localhost,root; 2647connection default; 2648create table t1 (i int) engine=InnoDB; 2649connection con50913_1; 2650set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; 2651# Sending: 2652alter table t1 add column j int, ALGORITHM=COPY; 2653connection default; 2654# Wait until ALTER TABLE gets blocked on a sync point after 2655# acquiring thr_lock.c lock. 2656set debug_sync= 'now WAIT_FOR parked'; 2657# The below statement should wait on MDL lock and not deadlock on 2658# thr_lock.c lock. 2659# Sending: 2660truncate table t1; 2661connection con50913_2; 2662# Wait until TRUNCATE TABLE is blocked on MDL lock. 2663# Unblock ALTER TABLE. 2664set debug_sync= 'now SIGNAL go'; 2665connection con50913_1; 2666# Reaping ALTER TABLE. 2667connection default; 2668# Reaping TRUNCATE TABLE. 2669disconnect con50913_1; 2670disconnect con50913_2; 2671set debug_sync= 'RESET'; 2672drop table t1; 2673# 2674# Test for bug #50998 "Deadlock in MDL code during test 2675# rqg_mdl_stability". 2676# Also provides coverage for the case when addition of 2677# waiting statement adds several loops in the waiters 2678# graph and therefore several searches for deadlock 2679# should be performed. 2680drop table if exists t1; 2681set debug_sync= 'RESET'; 2682connect con1,localhost,root; 2683connect con2,localhost,root; 2684connect con3,localhost,root; 2685connection default; 2686create table t1 (i int); 2687connection con1; 2688begin; 2689select * from t1; 2690i 2691connection con2; 2692begin; 2693select * from t1; 2694i 2695connection default; 2696# Start ALTER TABLE which will acquire SNW lock and 2697# table lock and get blocked on sync point. 2698set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; 2699# Sending: 2700alter table t1 add column j int; 2701connection con1; 2702# Wait until ALTER TABLE gets blocked on a sync point. 2703set debug_sync= 'now WAIT_FOR parked'; 2704# Sending: 2705insert into t1 values (1); 2706connection con2; 2707# Sending: 2708insert into t1 values (1); 2709connection con3; 2710# Wait until both 'con1' and 'con2' are blocked trying to acquire 2711# SW lock on the table. 2712# Unblock ALTER TABLE. Since it will try to upgrade SNW to X lock 2713# deadlock with two loops in waiting graph will occur. Both loops 2714# should be found and DML statements in both 'con1' and 'con2' 2715# should be aborted with ER_LOCK_DEADLOCK errors. 2716set debug_sync= 'now SIGNAL go'; 2717connection con1; 2718# Reaping INSERT. It should end with ER_LOCK_DEADLOCK error and 2719# not wait indefinitely (as it happened before the bugfix). 2720ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2721commit; 2722connection con2; 2723# Reaping INSERT. 2724ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 2725commit; 2726connection default; 2727# Reap ALTER TABLE. 2728disconnect con1; 2729disconnect con2; 2730disconnect con3; 2731connection default; 2732set debug_sync= 'RESET'; 2733drop table t1; 2734# 2735# Bug#42643: InnoDB does not support replication of TRUNCATE TABLE 2736# 2737# Ensure that a acquired lock is not given up due to a conflict. 2738# 2739connect con1,localhost,root,,test,,; 2740connect con2,localhost,root,,test,,; 2741connect con3,localhost,root,,test,,; 2742connection default; 2743DROP TABLE IF EXISTS t1; 2744CREATE TABLE t1 (a INT) ENGINE=InnoDB; 2745INSERT INTO t1 VALUES (1),(2),(3); 2746connection con1; 2747LOCK TABLES t1 WRITE; 2748SET debug_sync='upgrade_lock_for_truncate SIGNAL parked_truncate WAIT_FOR go_truncate'; 2749TRUNCATE TABLE t1; 2750connection default; 2751SET debug_sync='now WAIT_FOR parked_truncate'; 2752connection con2; 2753SET debug_sync='after_open_table_ignore_flush SIGNAL parked_show WAIT_FOR go_show'; 2754SHOW FIELDS FROM t1; 2755connection default; 2756SET debug_sync='now WAIT_FOR parked_show'; 2757connection con3; 2758SET debug_sync='after_flush_unlock SIGNAL parked_flush WAIT_FOR go_flush'; 2759FLUSH TABLES t1; 2760connection default; 2761SET debug_sync='now WAIT_FOR parked_flush'; 2762SET debug_sync='now SIGNAL go_truncate'; 2763# Ensure that truncate waits for a exclusive lock 2764SET debug_sync= 'now SIGNAL go_show'; 2765connection con1; 2766# Reaping... 2767UNLOCK TABLES; 2768connection con2; 2769# Reaping... 2770Field Type Null Key Default Extra 2771a int(11) YES NULL 2772connection default; 2773SET debug_sync= 'now SIGNAL go_flush'; 2774connection con3; 2775# Reaping... 2776disconnect con1; 2777disconnect con2; 2778disconnect con3; 2779connection default; 2780SET debug_sync= 'RESET'; 2781DROP TABLE t1; 2782# 2783# Bug#52856 concurrent show columns or show full columns causes a crash!!! 2784# 2785CREATE TABLE t1(a CHAR(255)); 2786connect con1, localhost, root; 2787SET DEBUG_SYNC= "get_schema_column SIGNAL waiting WAIT_FOR completed"; 2788SHOW FULL COLUMNS FROM t1; 2789connection default; 2790SET DEBUG_SYNC= "now WAIT_FOR waiting"; 2791SHOW FULL COLUMNS FROM t1; 2792Field Type Collation Null Key Default Extra Privileges Comment 2793a char(255) latin1_swedish_ci YES NULL # 2794SET DEBUG_SYNC= "now SIGNAL completed"; 2795connection con1; 2796Field Type Collation Null Key Default Extra Privileges Comment 2797a char(255) latin1_swedish_ci YES NULL # 2798connection default; 2799DROP TABLE t1; 2800disconnect con1; 2801# 2802# Tests for schema-scope locks 2803# 2804DROP DATABASE IF EXISTS db1; 2805DROP DATABASE IF EXISTS db2; 2806connect con2, localhost, root; 2807connect con3, localhost, root; 2808# Test 1: 2809# CREATE DATABASE blocks database DDL on the same database, but 2810# not database DDL on different databases. Tests X vs X lock. 2811# 2812connection default; 2813SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 2814# Sending: 2815CREATE DATABASE db1; 2816connection con2; 2817SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2818# Sending: 2819CREATE DATABASE db1; 2820connection con3; 2821CREATE DATABASE db2; 2822ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; 2823DROP DATABASE db2; 2824SET DEBUG_SYNC= 'now SIGNAL blocked'; 2825connection default; 2826# Reaping: CREATE DATABASE db1 2827connection con2; 2828# Reaping: CREATE DATABASE db1 2829ERROR HY000: Can't create database 'db1'; database exists 2830# Test 2: 2831# ALTER DATABASE blocks database DDL on the same database, but 2832# not database DDL on different databases. Tests X vs X lock. 2833# 2834connection default; 2835SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 2836# Sending: 2837ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; 2838connection con2; 2839SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2840# Sending: 2841ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; 2842connection con3; 2843CREATE DATABASE db2; 2844ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; 2845DROP DATABASE db2; 2846SET DEBUG_SYNC= 'now SIGNAL blocked'; 2847connection default; 2848# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 2849connection con2; 2850# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 2851connection default; 2852SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 2853# Sending: 2854ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; 2855connection con2; 2856SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2857# Sending: 2858DROP DATABASE db1; 2859connection con3; 2860SET DEBUG_SYNC= 'now SIGNAL blocked'; 2861connection default; 2862# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 2863connection con2; 2864# Reaping: DROP DATABASE db1 2865CREATE DATABASE db1; 2866# Test 3: 2867# Two ALTER..UPGRADE of the same database are mutually exclusive, but 2868# two ALTER..UPGRADE of different databases are not. Tests X vs X lock. 2869# 2870connection default; 2871SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 2872# Sending: 2873ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME; 2874connection con2; 2875SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2876# Sending: 2877ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME; 2878connection con3; 2879ALTER DATABASE `#mysql50#a-b-c-d` UPGRADE DATA DIRECTORY NAME; 2880SET DEBUG_SYNC= 'now SIGNAL blocked'; 2881connection default; 2882# Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME 2883connection con2; 2884# Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME 2885ERROR 42000: Unknown database '#mysql50#a-b-c' 2886DROP DATABASE `a-b-c`; 2887DROP DATABASE `a-b-c-d`; 2888# Test 4: 2889# DROP DATABASE blocks database DDL on the same database, but 2890# not database DDL on different databases. Tests X vs X lock. 2891# 2892connection default; 2893SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 2894# Sending: 2895DROP DATABASE db1; 2896connection con2; 2897SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2898# Sending: 2899DROP DATABASE db1; 2900connection con3; 2901CREATE DATABASE db2; 2902ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; 2903DROP DATABASE db2; 2904SET DEBUG_SYNC= 'now SIGNAL blocked'; 2905connection default; 2906# Reaping: DROP DATABASE db1 2907connection con2; 2908# Reaping: DROP DATABASE db1 2909ERROR HY000: Can't drop database 'db1'; database doesn't exist 2910connection default; 2911CREATE DATABASE db1; 2912SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 2913# Sending: 2914DROP DATABASE db1; 2915connection con2; 2916SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2917# Sending: 2918ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; 2919connection con3; 2920SET DEBUG_SYNC= 'now SIGNAL blocked'; 2921connection default; 2922# Reaping: DROP DATABASE db1 2923connection con2; 2924# Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 2925Got one of the listed errors 2926# Test 5: 2927# Locked database name prevents CREATE of tables in that database. 2928# Tests X vs IX lock. 2929# 2930connection default; 2931CREATE DATABASE db1; 2932SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 2933# Sending: 2934DROP DATABASE db1; 2935connection con2; 2936SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2937# Sending: 2938CREATE TABLE db1.t1 (a INT); 2939connection con3; 2940SET DEBUG_SYNC= 'now SIGNAL blocked'; 2941connection default; 2942# Reaping: DROP DATABASE db1 2943connection con2; 2944# Reaping: CREATE TABLE db1.t1 (a INT) 2945ERROR 42000: Unknown database 'db1' 2946# Test 6: 2947# Locked database name prevents RENAME of tables to/from that database. 2948# Tests X vs IX lock. 2949# 2950connection default; 2951CREATE DATABASE db1; 2952CREATE TABLE db1.t1 (a INT); 2953SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 2954# Sending: 2955DROP DATABASE db1; 2956connection con2; 2957SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2958# Sending: 2959RENAME TABLE db1.t1 TO test.t1; 2960connection con3; 2961SET DEBUG_SYNC= 'now SIGNAL blocked'; 2962connection default; 2963# Reaping: DROP DATABASE db1 2964connection con2; 2965# Reaping: RENAME TABLE db1.t1 TO test.t1 2966ERROR 42S02: Table 'db1.t1' doesn't exist 2967connection default; 2968CREATE DATABASE db1; 2969CREATE TABLE test.t2 (a INT); 2970SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 2971# Sending: 2972DROP DATABASE db1; 2973connection con2; 2974SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2975# Sending: 2976RENAME TABLE test.t2 TO db1.t2; 2977connection con3; 2978SET DEBUG_SYNC= 'now SIGNAL blocked'; 2979connection default; 2980# Reaping: DROP DATABASE db1 2981connection con2; 2982# Reaping: RENAME TABLE test.t2 TO db1.t2 2983Got one of the listed errors 2984DROP TABLE test.t2; 2985# Test 7: 2986# Locked database name prevents DROP of tables in that database. 2987# Tests X vs IX lock. 2988# 2989connection default; 2990CREATE DATABASE db1; 2991CREATE TABLE db1.t1 (a INT); 2992SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked'; 2993# Sending: 2994DROP DATABASE db1; 2995connection con2; 2996SET DEBUG_SYNC= 'now WAIT_FOR locked'; 2997# Sending: 2998DROP TABLE db1.t1; 2999connection con3; 3000SET DEBUG_SYNC= 'now SIGNAL blocked'; 3001connection default; 3002# Reaping: DROP DATABASE db1 3003connection con2; 3004# Reaping: DROP TABLE db1.t1 3005ERROR 42S02: Unknown table 'db1.t1' 3006connection default; 3007disconnect con2; 3008disconnect con3; 3009SET DEBUG_SYNC= 'RESET'; 3010# 3011# End of tests for schema-scope locks 3012# 3013# 3014# Tests of granted global S lock (FLUSH TABLE WITH READ LOCK) 3015# 3016CREATE DATABASE db1; 3017CREATE TABLE db1.t1(a INT); 3018connect con2, localhost, root; 3019connect con3, localhost, root; 3020connection default; 3021FLUSH TABLE WITH READ LOCK; 3022connection con2; 3023CREATE TABLE db1.t2(a INT); 3024connection default; 3025UNLOCK TABLES; 3026connection con2; 3027# Reaping CREATE TABLE db1.t2(a INT) 3028connection default; 3029FLUSH TABLE WITH READ LOCK; 3030connection con2; 3031ALTER DATABASE db1 DEFAULT CHARACTER SET utf8; 3032connection default; 3033UNLOCK TABLES; 3034connection con2; 3035# Reaping ALTER DATABASE db1 DEFAULT CHARACTER SET utf8 3036connection default; 3037FLUSH TABLE WITH READ LOCK; 3038connection con2; 3039FLUSH TABLE WITH READ LOCK; 3040UNLOCK TABLES; 3041connection default; 3042UNLOCK TABLES; 3043DROP DATABASE db1; 3044disconnect con2; 3045disconnect con3; 3046# 3047# Bug#56292 Deadlock with ALTER TABLE and MERGE tables 3048# 3049DROP TABLE IF EXISTS t1, t2, m1; 3050CREATE TABLE t1(a INT) engine=MyISAM; 3051CREATE TABLE t2(a INT) engine=MyISAM; 3052CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2); 3053INSERT INTO t1 VALUES (1), (2); 3054INSERT INTO t2 VALUES (3), (4); 3055connect con1, localhost, root; 3056connect con2, localhost, root; 3057connect con3, localhost, root; 3058connection con1; 3059# We need EXECUTE 2 since ALTER TABLE does SU => SNW => X and we want 3060# to stop at the second upgrade. 3061SET DEBUG_SYNC= 'mdl_upgrade_lock SIGNAL upgrade WAIT_FOR continue EXECUTE 2'; 3062# Sending: 3063ALTER TABLE m1 engine=MERGE UNION=(t2, t1); 3064connection con2; 3065# Waiting for ALTER TABLE to try lock upgrade 3066SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; 3067SET DEBUG_SYNC= 'now SIGNAL continue'; 3068SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; 3069# Sending: 3070DELETE FROM t2 WHERE a = 3; 3071connection con3; 3072# Check that DELETE is waiting on a metadata lock and not a table lock. 3073# Now that DELETE blocks on a metadata lock, we should be able to do 3074# SELECT * FROM m1 here. SELECT used to be blocked by a DELETE table 3075# lock request. 3076SELECT * FROM m1 WHERE a < 3; 3077connection default; 3078# Resuming ALTER TABLE 3079SET DEBUG_SYNC= 'now SIGNAL continue'; 3080connection con1; 3081# Reaping: ALTER TABLE m1 engine=MERGE UNION=(t2, t1) 3082connection con2; 3083# Reaping: DELETE FROM t2 WHERE a = 3 3084connection con3; 3085# Reaping: SELECT * FROM m1 WHERE a < 3 3086a 30871 30882 3089connection default; 3090DROP TABLE m1, t1, t2; 3091SET DEBUG_SYNC= 'RESET'; 3092disconnect con1; 3093disconnect con2; 3094disconnect con3; 3095# 3096# MDEV-12620 - set lock_wait_timeout = 1;flush tables with read lock; 3097# lock not released after timeout 3098# 3099CREATE TABLE t1(a INT) ENGINE=InnoDB; 3100SET debug_sync='open_tables_after_open_and_process_table SIGNAL ready WAIT_FOR go'; 3101INSERT INTO t1 values (1); 3102connect con1,localhost,root,,; 3103SET debug_sync='now WAIT_FOR ready'; 3104SET lock_wait_timeout=1; 3105FLUSH TABLES WITH READ LOCK; 3106ERROR HY000: Lock wait timeout exceeded; try restarting transaction 3107SET debug_sync='now SIGNAL go'; 3108connection default; 3109# After MDEV-5536, SELECT will not block FLUSH TABLES 3110SET debug_sync='RESET'; 3111SET debug_sync='open_tables_after_open_and_process_table SIGNAL ready WAIT_FOR go'; 3112SELECT * FROM t1; 3113connection con1; 3114SET debug_sync='now WAIT_FOR ready'; 3115SET lock_wait_timeout=1; 3116FLUSH TABLES WITH READ LOCK; 3117SET debug_sync='now SIGNAL go'; 3118connection default; 3119a 31201 3121connection con1; 3122unlock tables; 3123connection default; 3124SET debug_sync='RESET'; 3125DROP TABLE t1; 3126disconnect con1; 3127