1# 2# Test file for InnoDB tests that require the debug sync facility 3# 4--source include/have_innodb.inc 5--source include/have_partition.inc 6--source include/have_debug.inc 7--source include/have_debug_sync.inc 8# Save the initial number of concurrent sessions. 9--source include/count_sessions.inc 10 11 12--echo # 13--echo # Bug 42074 concurrent optimize table and 14--echo # alter table = Assertion failed: thd->is_error() 15--echo # 16 17--disable_warnings 18DROP TABLE IF EXISTS t1; 19--enable_warnings 20 21--echo # Create InnoDB table 22CREATE TABLE t1 (id INT) engine=innodb; 23connect (con2, localhost, root); 24 25--echo # Start optimizing table 26connection default; 27SET DEBUG_SYNC='ha_admin_try_alter SIGNAL optimize_started WAIT_FOR table_altered'; 28--send OPTIMIZE TABLE t1 29 30--echo # Change table to engine=memory 31connection con2; 32SET DEBUG_SYNC='now WAIT_FOR optimize_started'; 33ALTER TABLE t1 engine=memory; 34SET DEBUG_SYNC='now SIGNAL table_altered'; 35 36--echo # Complete optimization 37connection default; 38--reap 39 40disconnect con2; 41DROP TABLE t1; 42SET DEBUG_SYNC='RESET'; 43 44 45--echo # 46--echo # Bug#47459 Assertion in Diagnostics_area::set_eof_status on 47--echo # OPTIMIZE TABLE 48--echo # 49 50--disable_warnings 51DROP TABLE IF EXISTS t1; 52--enable_warnings 53 54connect (con1, localhost, root); 55connection default; 56 57CREATE TABLE t1(a INT) ENGINE= InnoDB; 58 59connection con1; 60SET DEBUG_SYNC= "ha_admin_open_ltable SIGNAL opening WAIT_FOR dropped"; 61--echo # Sending: 62--send OPTIMIZE TABLE t1 63 64connection default; 65SET DEBUG_SYNC= "now WAIT_FOR opening"; 66DROP TABLE t1; 67SET DEBUG_SYNC= "now SIGNAL dropped"; 68 69connection con1; 70--echo # Reaping: OPTIMIZE TABLE t1 71--reap 72 73connection default; 74disconnect con1; 75SET DEBUG_SYNC= "RESET"; 76 77 78--echo # 79--echo # Bug#53757 assert in mysql_truncate_by_delete 80--echo # 81 82--disable_warnings 83DROP TABLE IF EXISTS t1, t2; 84--enable_warnings 85 86CREATE TABLE t1(a INT) Engine=InnoDB; 87CREATE TABLE t2(id INT); 88INSERT INTO t1 VALUES (1), (2); 89 90connect (con1, localhost, root); 91INSERT INTO t2 VALUES(connection_id()); 92SET DEBUG_SYNC= "open_and_process_table SIGNAL opening WAIT_FOR killed"; 93--echo # Sending: (not reaped since connection is killed later) 94--send TRUNCATE t1 95 96connection default; 97SET DEBUG_SYNC= "now WAIT_FOR opening"; 98SELECT ((@id := id) - id) FROM t2; 99KILL @id; 100SET DEBUG_SYNC= "now SIGNAL killed"; 101DROP TABLE t1, t2; 102disconnect con1; 103--source include/wait_until_count_sessions.inc 104SET DEBUG_SYNC= "RESET"; 105 106 107--echo # 108--echo # Bug#58933 Assertion `thd- >is_error()' fails on shutdown with ongoing 109--echo # OPTIMIZE TABLE 110--echo # 111 112--disable_warnings 113DROP TABLE IF EXISTS t1; 114--enable_warnings 115 116CREATE TABLE t1 (a INT) ENGINE=InnoDB; 117INSERT INTO t1 VALUES (1), (2); 118 119connect (con1,localhost,root); 120let $ID= `SELECT connection_id()`; 121SET DEBUG_SYNC= 'ha_admin_open_ltable SIGNAL waiting WAIT_FOR killed'; 122--echo # Sending: 123--send OPTIMIZE TABLE t1 124 125connection default; 126SET DEBUG_SYNC= 'now WAIT_FOR waiting'; 127--replace_result $ID ID 128eval KILL QUERY $ID; 129SET DEBUG_SYNC= 'now SIGNAL killed'; 130 131connection con1; 132--echo # Reaping: OPTIMIZE TABLE t1 133--reap 134 135connection default; 136DROP TABLE t1; 137SET DEBUG_SYNC= 'RESET'; 138disconnect con1; 139 140 141--echo # 142--echo # Bug#42230 during add index, cannot do queries on storage engines 143--echo # that implement add_index 144--echo # 145 146--disable_warnings 147DROP DATABASE IF EXISTS db1; 148DROP TABLE IF EXISTS t1; 149--enable_warnings 150 151connect(con1,localhost,root); 152connect(con2,localhost,root); 153 154--echo # Test 1: Secondary index, should not block reads (original test case). 155 156connection default; 157CREATE DATABASE db1; 158CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb; 159INSERT INTO db1.t1(value) VALUES (1), (2); 160SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; 161--echo # Sending: 162--send ALTER TABLE db1.t1 ADD INDEX(value) 163 164connection con1; 165SET DEBUG_SYNC= "now WAIT_FOR manage"; 166# Neither of these two statements should be blocked 167USE db1; 168SELECT * FROM t1; 169SET DEBUG_SYNC= "now SIGNAL query"; 170 171connection default; 172--echo # Reaping: ALTER TABLE db1.t1 ADD INDEX(value) 173--reap 174DROP DATABASE db1; 175 176--echo # Test 2: Primary index (implicit), should block writes. 177 178CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb; 179SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; 180--echo # Sending: 181--send ALTER TABLE t1 ADD UNIQUE INDEX(a), LOCK=SHARED 182 183connection con1; 184SET DEBUG_SYNC= "now WAIT_FOR manage"; 185USE test; 186SELECT * FROM t1; 187--echo # Sending: 188--send UPDATE t1 SET a=NULL 189 190connection con2; 191--echo # Waiting for SELECT to be blocked by the metadata lock on t1 192let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist 193 WHERE state= 'Waiting for table metadata lock' 194 AND info='UPDATE t1 SET a=NULL'; 195--source include/wait_condition.inc 196SET DEBUG_SYNC= "now SIGNAL query"; 197 198connection default; 199--echo # Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a) 200--reap 201 202connection con1; 203--echo # Reaping: UPDATE t1 SET a=NULL 204--reap 205 206--echo # Test 3: Primary index (explicit), should block writes. 207 208connection default; 209ALTER TABLE t1 DROP INDEX a; 210SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; 211--echo # Sending: 212--send ALTER TABLE t1 ADD PRIMARY KEY (a), LOCK=SHARED 213 214connection con1; 215SET DEBUG_SYNC= "now WAIT_FOR manage"; 216SELECT * FROM t1; 217--echo # Sending: 218--send UPDATE t1 SET a=NULL 219 220connection con2; 221--echo # Waiting for SELECT to be blocked by the metadata lock on t1 222let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist 223 WHERE state= 'Waiting for table metadata lock' 224 AND info='UPDATE t1 SET a=NULL'; 225--source include/wait_condition.inc 226SET DEBUG_SYNC= "now SIGNAL query"; 227 228connection default; 229--echo # Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a) 230--reap 231 232connection con1; 233--echo # Reaping: UPDATE t1 SET a=NULL 234--reap 235 236--echo # Test 4: Secondary unique index, should not block reads. 237 238connection default; 239SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; 240--echo # Sending: 241--send ALTER TABLE t1 ADD UNIQUE (b) 242 243connection con1; 244SET DEBUG_SYNC= "now WAIT_FOR manage"; 245SELECT * FROM t1; 246SET DEBUG_SYNC= "now SIGNAL query"; 247 248connection default; 249--echo # Reaping: ALTER TABLE t1 ADD UNIQUE (b) 250--reap 251 252disconnect con1; 253disconnect con2; 254SET DEBUG_SYNC= "RESET"; 255DROP TABLE t1; 256 257 258--echo # 259--echo # Bug#11853126 RE-ENABLE CONCURRENT READS WHILE CREATING SECONDARY INDEX 260--echo # IN INNODB 261--echo # 262 263--disable_warnings 264DROP TABLE IF EXISTS t1; 265--enable_warnings 266 267CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb; 268INSERT INTO t1 VALUES (1, 12345), (2, 23456); 269 270--connect (con1,localhost,root) 271SET SESSION debug_dbug= "+d,alter_table_rollback_new_index"; 272--error ER_UNKNOWN_ERROR 273ALTER TABLE t1 ADD PRIMARY KEY(a); 274SELECT * FROM t1; 275 276--connection default 277SELECT * FROM t1; 278DROP TABLE t1; 279disconnect con1; 280 281 282--echo # 283--echo # Bug#13417754 ASSERT IN ROW_DROP_DATABASE_FOR_MYSQL DURING DROP SCHEMA 284--echo # 285 286--disable_warnings 287DROP TABLE IF EXISTS t1; 288DROP DATABASE IF EXISTS db1; 289--enable_warnings 290 291CREATE TABLE t1(a int) engine=InnoDB; 292CREATE DATABASE db1; 293 294connect(con1, localhost, root); 295connect(con2, localhost, root); 296 297connection con1; 298SET DEBUG_SYNC= 'after_innobase_rename_table SIGNAL locked WAIT_FOR continue'; 299--echo # Sending: 300--send ALTER TABLE t1 RENAME db1.t1 301 302connection con2; 303SET DEBUG_SYNC= 'now WAIT_FOR locked'; 304--echo # DROP DATABASE db1 should now be blocked by ALTER TABLE 305--echo # Sending: 306--send DROP DATABASE db1 307 308connection default; 309--echo # Check that DROP DATABASE is blocked by IX lock on db1 310let $wait_condition= 311 SELECT COUNT(*) = 1 FROM information_schema.processlist 312 WHERE state = "Waiting for schema metadata lock" and 313 info = "DROP DATABASE db1"; 314--source include/wait_condition.inc 315--echo # Resume ALTER TABLE 316SET DEBUG_SYNC= 'now SIGNAL continue'; 317 318connection con1; 319--echo # Reaping: ALTER TABLE t1 RENAME db1.t1; 320--reap 321 322connection con2; 323--echo # Reaping: DROP DATABASE db1 324--reap 325 326connection default; 327SET DEBUG_SYNC= 'RESET'; 328disconnect con1; 329disconnect con2; 330 331 332--echo # 333--echo # WL#5534 Online ALTER, Phase 1 334--echo # 335 336--echo # Multi thread tests. 337--echo # See alter_table.test for single thread tests. 338 339--disable_warnings 340DROP TABLE IF EXISTS t1; 341--enable_warnings 342 343CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB; 344INSERT INTO t1 VALUES (1,1), (2,2); 345SET DEBUG_SYNC= 'RESET'; 346connect (con1, localhost, root); 347SET SESSION lock_wait_timeout= 1; 348 349--echo # 350--echo # 1: In-place + writes blocked. 351--echo # 352 353--connection default 354SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; 355SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; 356SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3'; 357SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4'; 358--echo # Sending: 359--send ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= SHARED 360 361--connection con1 362SET DEBUG_SYNC= 'now WAIT_FOR opened'; 363--echo # At this point, neither reads nor writes should be blocked. 364SELECT * FROM t1; 365INSERT INTO t1 VALUES (3,3); 366 367SET DEBUG_SYNC= 'now SIGNAL continue1'; 368SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 369--echo # Now both reads and writes should be blocked 370--error ER_LOCK_WAIT_TIMEOUT 371SELECT * FROM t1; 372--error ER_LOCK_WAIT_TIMEOUT 373INSERT INTO t1 VALUES (4,4); 374 375SET DEBUG_SYNC= 'now SIGNAL continue2'; 376SET DEBUG_SYNC= 'now WAIT_FOR beforecommit'; 377--echo # Still both reads and writes should be blocked. 378--error ER_LOCK_WAIT_TIMEOUT 379SELECT * FROM t1; 380--error ER_LOCK_WAIT_TIMEOUT 381INSERT INTO t1 VALUES (5,5); 382 383SET DEBUG_SYNC= 'now SIGNAL continue3'; 384SET DEBUG_SYNC= 'now WAIT_FOR binlog'; 385--echo # Same here. 386--error ER_LOCK_WAIT_TIMEOUT 387SELECT * FROM t1; 388--error ER_LOCK_WAIT_TIMEOUT 389INSERT INTO t1 VALUES (6,6); 390 391SET DEBUG_SYNC= 'now SIGNAL continue4'; 392--connection default 393--echo # Reaping ALTER TABLE ... 394--reap 395SET DEBUG_SYNC= 'RESET'; 396DELETE FROM t1 WHERE a= 3; 397 398--echo # 399--echo # 2: Copy + writes blocked. 400--echo # 401 402SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; 403SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; 404SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue3'; 405--echo # Sending: 406--send ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= COPY, LOCK= SHARED 407 408--connection con1 409SET DEBUG_SYNC= 'now WAIT_FOR opened'; 410--echo # At this point, neither reads nor writes should be blocked. 411SELECT * FROM t1; 412INSERT INTO t1 VALUES (3,3); 413 414SET DEBUG_SYNC= 'now SIGNAL continue1'; 415SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 416--echo # Now writes should be blocked, reads still allowed. 417SELECT * FROM t1; 418--error ER_LOCK_WAIT_TIMEOUT 419INSERT INTO t1 VALUES (4,4); 420 421SET DEBUG_SYNC= 'now SIGNAL continue2'; 422SET DEBUG_SYNC= 'now WAIT_FOR binlog'; 423--echo # Now both reads and writes should be blocked. 424--error ER_LOCK_WAIT_TIMEOUT 425SELECT * FROM t1 limit 1; 426--error ER_LOCK_WAIT_TIMEOUT 427INSERT INTO t1 VALUES (5,5); 428 429SET DEBUG_SYNC= 'now SIGNAL continue3'; 430--connection default 431--echo # Reaping ALTER TABLE ... 432--reap 433SET DEBUG_SYNC= 'RESET'; 434DELETE FROM t1 WHERE a= 3; 435 436--echo # 437--echo # 3: In-place + writes allowed. 438--echo # 439 440--echo # TODO: Enable this test once WL#5526 is pushed 441--disable_parsing 442 443--connection default 444SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; 445SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; 446SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue3'; 447SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue4'; 448SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue5'; 449--echo # Sending: 450--send ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= NONE 451 452--connection con1 453SET DEBUG_SYNC= 'now WAIT_FOR opened'; 454--echo # At this point, neither reads nor writes should be blocked. 455SELECT * FROM t1; 456INSERT INTO t1 VALUES (3,3); 457 458SET DEBUG_SYNC= 'now SIGNAL continue1'; 459SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 460--echo # Now writes should be blocked, reads still allowed. 461SELECT * FROM t1; 462--error ER_LOCK_WAIT_TIMEOUT 463INSERT INTO t1 VALUES (4,4); 464 465SET DEBUG_SYNC= 'now SIGNAL continue2'; 466SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; 467--echo # Now writes should be allowed again. 468SELECT * FROM t1; 469INSERT INTO t1 VALUES (5,5); 470 471SET DEBUG_SYNC= 'now SIGNAL continue3'; 472SET DEBUG_SYNC= 'now WAIT_FOR beforecommit'; 473--echo # Now both reads and writes should be blocked. 474--error ER_LOCK_WAIT_TIMEOUT 475SELECT * FROM t1; 476--error ER_LOCK_WAIT_TIMEOUT 477INSERT INTO t1 VALUES (6,6); 478 479SET DEBUG_SYNC= 'now SIGNAL continue4'; 480SET DEBUG_SYNC= 'now WAIT_FOR binlog'; 481--echo # Same here. 482--error ER_LOCK_WAIT_TIMEOUT 483SELECT * FROM t1; 484--error ER_LOCK_WAIT_TIMEOUT 485INSERT INTO t1 VALUES (7,7); 486 487SET DEBUG_SYNC= 'now SIGNAL continue5'; 488--connection default 489--echo # Reaping ALTER TABLE ... 490--reap 491SET DEBUG_SYNC= 'RESET'; 492DELETE FROM t1 WHERE a= 3 OR a= 4; 493 494--echo # TODO: Enable this test once WL#5526 is pushed 495--enable_parsing 496 497--echo # 498--echo # 4: In-place + reads and writes blocked. 499--echo # 500 501--connection default 502SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; 503SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; 504SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3'; 505SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4'; 506--echo # Sending: 507--send ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE 508 509--connection con1 510SET DEBUG_SYNC= 'now WAIT_FOR opened'; 511--echo # At this point, neither reads nor writes should be blocked. 512SELECT * FROM t1; 513INSERT INTO t1 VALUES (3,3); 514 515SET DEBUG_SYNC= 'now SIGNAL continue1'; 516SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 517--echo # Now both reads and writes should be blocked. 518--error ER_LOCK_WAIT_TIMEOUT 519SELECT * FROM t1; 520--error ER_LOCK_WAIT_TIMEOUT 521INSERT INTO t1 VALUES (4,4); 522 523SET DEBUG_SYNC= 'now SIGNAL continue2'; 524SET DEBUG_SYNC= 'now WAIT_FOR beforecommit'; 525--echo # Same here. 526--error ER_LOCK_WAIT_TIMEOUT 527SELECT * FROM t1; 528--error ER_LOCK_WAIT_TIMEOUT 529INSERT INTO t1 VALUES (5,5); 530 531SET DEBUG_SYNC= 'now SIGNAL continue3'; 532SET DEBUG_SYNC= 'now WAIT_FOR binlog'; 533--echo # Same here. 534--error ER_LOCK_WAIT_TIMEOUT 535SELECT * FROM t1; 536--error ER_LOCK_WAIT_TIMEOUT 537INSERT INTO t1 VALUES (6,6); 538 539SET DEBUG_SYNC= 'now SIGNAL continue4'; 540--connection default 541--echo # Reaping ALTER TABLE ... 542--reap 543SET DEBUG_SYNC= 'RESET'; 544 545--connection default 546--disconnect con1 547DROP TABLE t1; 548SET DEBUG_SYNC= 'RESET'; 549 550 551--echo # 552--echo #BUG#13975225:ONLINE OPTIMIZE TABLE FOR INNODB TABLES 553--echo # 554 555SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; 556connect(con1,localhost,root,,); 557 558--echo #Setting up INNODB table. 559CREATE TABLE t1(fld1 INT, fld2 INT, fld3 INT) ENGINE= INNODB; 560INSERT INTO t1 VALUES (155, 45, 55); 561 562--echo #Concurrent INSERT, UPDATE, SELECT and DELETE is supported 563--echo #during OPTIMIZE TABLE operation for INNODB tables. 564--connection default 565--echo #OPTIMIZE TABLE operation. 566--send OPTIMIZE TABLE t1 567 568--connection con1 569SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; 570--echo # With the patch, concurrent DML operation succeeds. 571INSERT INTO t1 VALUES (10, 11, 12); 572UPDATE t1 SET fld1= 20 WHERE fld1= 155; 573DELETE FROM t1 WHERE fld1= 20; 574SELECT * from t1; 575SET DEBUG_SYNC= 'now SIGNAL continue'; 576 577--connection default 578--reap 579DROP TABLE t1; 580SET DEBUG_SYNC= 'RESET'; 581 582--echo #Concurrent INSERT, UPDATE, SELECT and DELETE is supported 583--echo #during OPTIMIZE TABLE operation for Partitioned table. 584 585SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; 586--echo #Setup PARTITIONED table. 587CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4; 588INSERT INTO t1 VALUES(10); 589 590--echo #OPTIMIZE TABLE operation. 591--send OPTIMIZE TABLE t1 592 593--connection con1 594SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; 595--echo # With the patch, concurrent DML operation succeeds. 596INSERT INTO t1 VALUES (30); 597UPDATE t1 SET fld1= 20 WHERE fld1= 10; 598DELETE FROM t1 WHERE fld1= 20; 599SELECT * from t1; 600SET DEBUG_SYNC= 'now SIGNAL continue'; 601 602--connection default 603--reap 604DROP TABLE t1; 605SET DEBUG_SYNC= 'RESET'; 606 607--echo #ALTER TABLE FORCE and ALTER TABLE ENGINE uses online rebuild 608--echo #of the table. 609 610CREATE TABLE t1(fld1 INT, fld2 INT) ENGINE=INNODB; 611INSERT INTO t1 VALUES(10, 20); 612 613--enable_info 614ALTER TABLE t1 FORCE; 615ALTER TABLE t1 ENGINE=INNODB; 616 617--echo #ALTER TABLE FORCE, ALTER TABLE ENGINE and OPTIMIZE TABLE uses 618--echo #table copy when the old_alter_table enabled. 619SET SESSION old_alter_table= TRUE; 620ALTER TABLE t1 FORCE; 621ALTER TABLE t1 ENGINE= INNODB; 622 623SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded'; 624--echo #OPTIMIZE TABLE operation using table copy. 625--send OPTIMIZE TABLE t1 626 627--connection con1 628SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 629INSERT INTO t1 VALUES(10, 20); 630 631--connection default 632--reap 633SET DEBUG_SYNC= 'RESET'; 634SET SESSION old_alter_table= FALSE; 635 636--echo #ALTER TABLE FORCE and ALTER TABLE ENGINE uses table copy 637--echo #when ALGORITHM COPY is used. 638ALTER TABLE t1 FORCE, ALGORITHM= COPY; 639ALTER TABLE t1 ENGINE= INNODB, ALGORITHM= COPY; 640--disable_info 641 642#cleanup 643DROP TABLE t1; 644 645--echo #OPTIMIZE TABLE on a table with FULLTEXT index uses 646--echo #ALTER TABLE FORCE using COPY algorithm here. This 647--echo #test case ensures the COPY table debug sync point is hit. 648 649SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded'; 650 651--echo #Setup a table with FULLTEXT index. 652--connection default 653CREATE TABLE t1(fld1 CHAR(10), FULLTEXT(fld1), FULLTEXT(fld1)) ENGINE= INNODB; 654INSERT INTO t1 VALUES("String1"); 655 656--echo #OPTIMIZE TABLE operation. 657--send OPTIMIZE TABLE t1 658 659--connection con1 660SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 661INSERT INTO t1 VALUES("String2"); 662 663--connection default 664--reap 665SET DEBUG_SYNC= 'RESET'; 666DROP TABLE t1; 667 668--echo #Test which demonstrates that ALTER TABLE, OPTIMIZE PARTITION 669--echo #takes OPTIMIZE TABLE code path, hence does an online rebuild 670--echo #of the table with the patch. 671 672--connection default 673SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; 674--echo #Setup PARTITIONED table. 675CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4; 676INSERT INTO t1 VALUES(10); 677 678--echo #OPTIMIZE ALL PARTITIONS operation. 679--send ALTER TABLE t1 OPTIMIZE PARTITION ALL 680 681--connection con1 682SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; 683--echo # With the patch, concurrent DML operation succeeds. 684INSERT INTO t1 VALUES (30); 685UPDATE t1 SET fld1= 20 WHERE fld1= 10; 686DELETE FROM t1 WHERE fld1= 20; 687SELECT * from t1; 688SET DEBUG_SYNC= 'now SIGNAL continue'; 689 690--connection default 691--reap 692SET DEBUG_SYNC= 'RESET'; 693 694--echo #OPTIMIZE PER PARTITION operation. 695SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; 696--send ALTER TABLE t1 OPTIMIZE PARTITION p0 697 698--connection con1 699SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; 700--echo # With the patch, concurrent DML operation succeeds. 701INSERT INTO t1 VALUES (30); 702UPDATE t1 SET fld1= 20 WHERE fld1= 10; 703DELETE FROM t1 WHERE fld1= 20; 704SELECT * from t1; 705SET DEBUG_SYNC= 'now SIGNAL continue'; 706 707--connection default 708--reap 709SET DEBUG_SYNC= 'RESET'; 710 711--echo # Test case for Bug#11938817 (ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED). 712--enable_info 713--echo # This should not do anything 714ALTER TABLE t1; 715--disable_info 716 717#Note that sync point is activated in the online rebuild code path. 718SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild'; 719 720--echo # Check that we rebuild the table 721--send ALTER TABLE t1 engine=innodb 722 723--connection con1 724SET DEBUG_SYNC= 'now WAIT_FOR rebuild'; 725 726--connection default 727--reap 728 729SET DEBUG_SYNC= 'RESET'; 730 731SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild'; 732 733--echo # Check that we rebuild the table 734--send ALTER TABLE t1 FORCE 735 736--connection con1 737SET DEBUG_SYNC= 'now WAIT_FOR rebuild'; 738 739--connection default 740--reap 741 742--disconnect con1 743 744SET DEBUG_SYNC= 'RESET'; 745DROP TABLE t1; 746 747 748# Check that all connections opened by test cases in this file are really 749# gone so execution of other tests won't be affected by their presence. 750--source include/wait_until_count_sessions.inc 751