1# 2# Bug 42074 concurrent optimize table and 3# alter table = Assertion failed: thd->is_error() 4# 5DROP TABLE IF EXISTS t1; 6# Create InnoDB table 7CREATE TABLE t1 (id INT) engine=innodb; 8# Connection 1 9# Start optimizing table 10SET DEBUG_SYNC='ha_admin_try_alter SIGNAL optimize_started WAIT_FOR table_altered'; 11OPTIMIZE TABLE t1; 12# Connection 2 13# Change table to engine=memory 14SET DEBUG_SYNC='now WAIT_FOR optimize_started'; 15ALTER TABLE t1 engine=memory; 16SET DEBUG_SYNC='now SIGNAL table_altered'; 17# Connection 1 18# Complete optimization 19Table Op Msg_type Msg_text 20test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 21test.t1 optimize error Got error -1 from storage engine 22test.t1 optimize status Operation failed 23Warnings: 24Error 1030 Got error -1 from storage engine 25DROP TABLE t1; 26SET DEBUG_SYNC='RESET'; 27# 28# Bug#47459 Assertion in Diagnostics_area::set_eof_status on 29# OPTIMIZE TABLE 30# 31DROP TABLE IF EXISTS t1; 32CREATE TABLE t1(a INT) ENGINE= InnoDB; 33# Connection con1 34SET DEBUG_SYNC= "ha_admin_open_ltable SIGNAL opening WAIT_FOR dropped"; 35# Sending: 36OPTIMIZE TABLE t1; 37# Connection default 38SET DEBUG_SYNC= "now WAIT_FOR opening"; 39DROP TABLE t1; 40SET DEBUG_SYNC= "now SIGNAL dropped"; 41# Connection con1 42# Reaping: OPTIMIZE TABLE t1 43Table Op Msg_type Msg_text 44test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 45test.t1 optimize error Table 'test.t1' doesn't exist 46test.t1 optimize status Operation failed 47Warnings: 48Error 1146 Table 'test.t1' doesn't exist 49# Connection default 50SET DEBUG_SYNC= "RESET"; 51# 52# Bug#53757 assert in mysql_truncate_by_delete 53# 54DROP TABLE IF EXISTS t1, t2; 55CREATE TABLE t1(a INT) Engine=InnoDB; 56CREATE TABLE t2(id INT); 57INSERT INTO t1 VALUES (1), (2); 58INSERT INTO t2 VALUES(connection_id()); 59SET DEBUG_SYNC= "open_and_process_table SIGNAL opening WAIT_FOR killed"; 60# Sending: (not reaped since connection is killed later) 61TRUNCATE t1; 62SET DEBUG_SYNC= "now WAIT_FOR opening"; 63SELECT ((@id := id) - id) FROM t2; 64((@id := id) - id) 650 66KILL @id; 67SET DEBUG_SYNC= "now SIGNAL killed"; 68DROP TABLE t1, t2; 69SET DEBUG_SYNC= "RESET"; 70# 71# Bug#58933 Assertion `thd- >is_error()' fails on shutdown with ongoing 72# OPTIMIZE TABLE 73# 74DROP TABLE IF EXISTS t1; 75CREATE TABLE t1 (a INT) ENGINE=InnoDB; 76INSERT INTO t1 VALUES (1), (2); 77# Connection con1 78SET DEBUG_SYNC= 'ha_admin_open_ltable SIGNAL waiting WAIT_FOR killed'; 79# Sending: 80OPTIMIZE TABLE t1; 81# Connection default 82SET DEBUG_SYNC= 'now WAIT_FOR waiting'; 83KILL QUERY ID; 84SET DEBUG_SYNC= 'now SIGNAL killed'; 85# Connection con1 86# Reaping: OPTIMIZE TABLE t1 87Table Op Msg_type Msg_text 88test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 89test.t1 optimize status Operation failed 90# Connection default 91DROP TABLE t1; 92SET DEBUG_SYNC= 'RESET'; 93# 94# Bug#42230 during add index, cannot do queries on storage engines 95# that implement add_index 96# 97DROP DATABASE IF EXISTS db1; 98DROP TABLE IF EXISTS t1; 99# Test 1: Secondary index, should not block reads (original test case). 100# Connection default 101CREATE DATABASE db1; 102CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb; 103INSERT INTO db1.t1(value) VALUES (1), (2); 104SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; 105# Sending: 106ALTER TABLE db1.t1 ADD INDEX(value); 107# Connection con1 108SET DEBUG_SYNC= "now WAIT_FOR manage"; 109USE db1; 110SELECT * FROM t1; 111id value 1121 1 1132 2 114SET DEBUG_SYNC= "now SIGNAL query"; 115# Connection default 116# Reaping: ALTER TABLE db1.t1 ADD INDEX(value) 117DROP DATABASE db1; 118# Test 2: Primary index (implicit), should block writes. 119CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb; 120SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; 121# Sending: 122ALTER TABLE t1 ADD UNIQUE INDEX(a), LOCK=SHARED; 123# Connection con1 124SET DEBUG_SYNC= "now WAIT_FOR manage"; 125USE test; 126SELECT * FROM t1; 127a b 128# Sending: 129UPDATE t1 SET a=NULL; 130# Connection con2 131# Waiting for SELECT to be blocked by the metadata lock on t1 132SET DEBUG_SYNC= "now SIGNAL query"; 133# Connection default 134# Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a) 135# Connection con1 136# Reaping: UPDATE t1 SET a=NULL 137# Test 3: Primary index (explicit), should block writes. 138# Connection default 139ALTER TABLE t1 DROP INDEX a; 140SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; 141# Sending: 142ALTER TABLE t1 ADD PRIMARY KEY (a), LOCK=SHARED; 143# Connection con1 144SET DEBUG_SYNC= "now WAIT_FOR manage"; 145SELECT * FROM t1; 146a b 147# Sending: 148UPDATE t1 SET a=NULL; 149# Connection con2 150# Waiting for SELECT to be blocked by the metadata lock on t1 151SET DEBUG_SYNC= "now SIGNAL query"; 152# Connection default 153# Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a) 154# Connection con1 155# Reaping: UPDATE t1 SET a=NULL 156# Test 4: Secondary unique index, should not block reads. 157# Connection default 158SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; 159# Sending: 160ALTER TABLE t1 ADD UNIQUE (b); 161# Connection con1 162SET DEBUG_SYNC= "now WAIT_FOR manage"; 163SELECT * FROM t1; 164a b 165SET DEBUG_SYNC= "now SIGNAL query"; 166# Connection default 167# Reaping: ALTER TABLE t1 ADD UNIQUE (b) 168SET DEBUG_SYNC= "RESET"; 169DROP TABLE t1; 170# 171# Bug#11853126 RE-ENABLE CONCURRENT READS WHILE CREATING SECONDARY INDEX 172# IN INNODB 173# 174DROP TABLE IF EXISTS t1; 175CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb; 176INSERT INTO t1 VALUES (1, 12345), (2, 23456); 177# Connection con1 178SET SESSION debug= "+d,alter_table_rollback_new_index"; 179ALTER TABLE t1 ADD PRIMARY KEY(a); 180ERROR HY000: Unknown error 181SELECT * FROM t1; 182a b 1831 12345 1842 23456 185# Connection default 186SELECT * FROM t1; 187a b 1881 12345 1892 23456 190DROP TABLE t1; 191# 192# Bug#13417754 ASSERT IN ROW_DROP_DATABASE_FOR_MYSQL DURING DROP SCHEMA 193# 194DROP TABLE IF EXISTS t1; 195DROP DATABASE IF EXISTS db1; 196CREATE TABLE t1(a int) engine=InnoDB; 197CREATE DATABASE db1; 198# Connection con1 199SET DEBUG_SYNC= 'after_innobase_rename_table SIGNAL locked WAIT_FOR continue'; 200# Sending: 201ALTER TABLE t1 RENAME db1.t1; 202# Connection con2 203SET DEBUG_SYNC= 'now WAIT_FOR locked'; 204# DROP DATABASE db1 should now be blocked by ALTER TABLE 205# Sending: 206DROP DATABASE db1; 207# Connection default 208# Check that DROP DATABASE is blocked by IX lock on db1 209# Resume ALTER TABLE 210SET DEBUG_SYNC= 'now SIGNAL continue'; 211# Connection con1 212# Reaping: ALTER TABLE t1 RENAME db1.t1; 213# Connection con2 214# Reaping: DROP DATABASE db1 215# Connection default; 216SET DEBUG_SYNC= 'RESET'; 217# 218# WL#5534 Online ALTER, Phase 1 219# 220# Multi thread tests. 221# See alter_table.test for single thread tests. 222DROP TABLE IF EXISTS t1; 223CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB; 224INSERT INTO t1 VALUES (1,1), (2,2); 225SET DEBUG_SYNC= 'RESET'; 226SET SESSION lock_wait_timeout= 1; 227# 228# 1: In-place + writes blocked. 229# 230# Connection default 231SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; 232SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; 233SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3'; 234SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4'; 235# Sending: 236ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= SHARED; 237# Connection con1; 238SET DEBUG_SYNC= 'now WAIT_FOR opened'; 239# At this point, neither reads nor writes should be blocked. 240SELECT * FROM t1; 241a b 2421 1 2432 2 244INSERT INTO t1 VALUES (3,3); 245SET DEBUG_SYNC= 'now SIGNAL continue1'; 246SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 247# Now both reads and writes should be blocked 248SELECT * FROM t1; 249ERROR HY000: Lock wait timeout exceeded; try restarting transaction 250INSERT INTO t1 VALUES (4,4); 251ERROR HY000: Lock wait timeout exceeded; try restarting transaction 252SET DEBUG_SYNC= 'now SIGNAL continue2'; 253SET DEBUG_SYNC= 'now WAIT_FOR beforecommit'; 254# Still both reads and writes should be blocked. 255SELECT * FROM t1; 256ERROR HY000: Lock wait timeout exceeded; try restarting transaction 257INSERT INTO t1 VALUES (5,5); 258ERROR HY000: Lock wait timeout exceeded; try restarting transaction 259SET DEBUG_SYNC= 'now SIGNAL continue3'; 260SET DEBUG_SYNC= 'now WAIT_FOR binlog'; 261# Same here. 262SELECT * FROM t1; 263ERROR HY000: Lock wait timeout exceeded; try restarting transaction 264INSERT INTO t1 VALUES (6,6); 265ERROR HY000: Lock wait timeout exceeded; try restarting transaction 266SET DEBUG_SYNC= 'now SIGNAL continue4'; 267# Connection default 268# Reaping ALTER TABLE ... 269SET DEBUG_SYNC= 'RESET'; 270DELETE FROM t1 WHERE a= 3; 271# 272# 2: Copy + writes blocked. 273# 274SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; 275SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; 276SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue3'; 277# Sending: 278ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= COPY, LOCK= SHARED; 279# Connection con1; 280SET DEBUG_SYNC= 'now WAIT_FOR opened'; 281# At this point, neither reads nor writes should be blocked. 282SELECT * FROM t1; 283a b 2841 1 2852 2 286INSERT INTO t1 VALUES (3,3); 287SET DEBUG_SYNC= 'now SIGNAL continue1'; 288SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 289# Now writes should be blocked, reads still allowed. 290SELECT * FROM t1; 291a b 2921 1 2932 2 2943 3 295INSERT INTO t1 VALUES (4,4); 296ERROR HY000: Lock wait timeout exceeded; try restarting transaction 297SET DEBUG_SYNC= 'now SIGNAL continue2'; 298SET DEBUG_SYNC= 'now WAIT_FOR binlog'; 299# Now both reads and writes should be blocked. 300SELECT * FROM t1 limit 1; 301ERROR HY000: Lock wait timeout exceeded; try restarting transaction 302INSERT INTO t1 VALUES (5,5); 303ERROR HY000: Lock wait timeout exceeded; try restarting transaction 304SET DEBUG_SYNC= 'now SIGNAL continue3'; 305# Connection default 306# Reaping ALTER TABLE ... 307Warnings: 308Warning 1831 Duplicate index 'i2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 309SET DEBUG_SYNC= 'RESET'; 310DELETE FROM t1 WHERE a= 3; 311# 312# 3: In-place + writes allowed. 313# 314# TODO: Enable this test once WL#5526 is pushed 315# 316# 4: In-place + reads and writes blocked. 317# 318# Connection default 319SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; 320SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; 321SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3'; 322SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4'; 323# Sending: 324ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE; 325# Connection con1; 326SET DEBUG_SYNC= 'now WAIT_FOR opened'; 327# At this point, neither reads nor writes should be blocked. 328SELECT * FROM t1; 329a b 3301 1 3312 2 332INSERT INTO t1 VALUES (3,3); 333SET DEBUG_SYNC= 'now SIGNAL continue1'; 334SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 335# Now both reads and writes should be blocked. 336SELECT * FROM t1; 337ERROR HY000: Lock wait timeout exceeded; try restarting transaction 338INSERT INTO t1 VALUES (4,4); 339ERROR HY000: Lock wait timeout exceeded; try restarting transaction 340SET DEBUG_SYNC= 'now SIGNAL continue2'; 341SET DEBUG_SYNC= 'now WAIT_FOR beforecommit'; 342# Same here. 343SELECT * FROM t1; 344ERROR HY000: Lock wait timeout exceeded; try restarting transaction 345INSERT INTO t1 VALUES (5,5); 346ERROR HY000: Lock wait timeout exceeded; try restarting transaction 347SET DEBUG_SYNC= 'now SIGNAL continue3'; 348SET DEBUG_SYNC= 'now WAIT_FOR binlog'; 349# Same here. 350SELECT * FROM t1; 351ERROR HY000: Lock wait timeout exceeded; try restarting transaction 352INSERT INTO t1 VALUES (6,6); 353ERROR HY000: Lock wait timeout exceeded; try restarting transaction 354SET DEBUG_SYNC= 'now SIGNAL continue4'; 355# Connection default 356# Reaping ALTER TABLE ... 357Warnings: 358Warning 1831 Duplicate index 'i4' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. 359SET DEBUG_SYNC= 'RESET'; 360DROP TABLE t1; 361SET DEBUG_SYNC= 'RESET'; 362# 363#BUG#13975225:ONLINE OPTIMIZE TABLE FOR INNODB TABLES 364# 365SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; 366#Setting up INNODB table. 367CREATE TABLE t1(fld1 INT, fld2 INT, fld3 INT) ENGINE= INNODB; 368INSERT INTO t1 VALUES (155, 45, 55); 369#Concurrent INSERT, UPDATE, SELECT and DELETE is supported 370#during OPTIMIZE TABLE operation for INNODB tables. 371connection default; 372#OPTIMIZE TABLE operation. 373OPTIMIZE TABLE t1; 374connection con1; 375SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; 376# With the patch, concurrent DML operation succeeds. 377INSERT INTO t1 VALUES (10, 11, 12); 378UPDATE t1 SET fld1= 20 WHERE fld1= 155; 379DELETE FROM t1 WHERE fld1= 20; 380SELECT * from t1; 381fld1 fld2 fld3 38210 11 12 383SET DEBUG_SYNC= 'now SIGNAL continue'; 384connection default; 385Table Op Msg_type Msg_text 386test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 387test.t1 optimize status OK 388DROP TABLE t1; 389SET DEBUG_SYNC= 'RESET'; 390#Concurrent INSERT, UPDATE, SELECT and DELETE is supported 391#during OPTIMIZE TABLE operation for Partitioned table. 392SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; 393#Setup PARTITIONED table. 394CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4; 395INSERT INTO t1 VALUES(10); 396#OPTIMIZE TABLE operation. 397OPTIMIZE TABLE t1; 398connection con1; 399SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; 400# With the patch, concurrent DML operation succeeds. 401INSERT INTO t1 VALUES (30); 402UPDATE t1 SET fld1= 20 WHERE fld1= 10; 403DELETE FROM t1 WHERE fld1= 20; 404SELECT * from t1; 405fld1 40630 407SET DEBUG_SYNC= 'now SIGNAL continue'; 408connection default; 409Table Op Msg_type Msg_text 410test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 411test.t1 optimize status OK 412DROP TABLE t1; 413SET DEBUG_SYNC= 'RESET'; 414#ALTER TABLE FORCE and ALTER TABLE ENGINE uses online rebuild 415#of the table. 416CREATE TABLE t1(fld1 INT, fld2 INT); 417INSERT INTO t1 VALUES(10, 20); 418ALTER TABLE t1 FORCE; 419affected rows: 0 420info: Records: 0 Duplicates: 0 Warnings: 0 421ALTER TABLE t1 ENGINE=INNODB; 422affected rows: 0 423info: Records: 0 Duplicates: 0 Warnings: 0 424#ALTER TABLE FORCE, ALTER TABLE ENGINE and OPTIMIZE TABLE uses 425#table copy when the old_alter_table enabled. 426SET SESSION old_alter_table= TRUE; 427affected rows: 0 428ALTER TABLE t1 FORCE; 429affected rows: 1 430info: Records: 1 Duplicates: 0 Warnings: 0 431ALTER TABLE t1 ENGINE= INNODB; 432affected rows: 1 433info: Records: 1 Duplicates: 0 Warnings: 0 434SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded'; 435affected rows: 0 436#OPTIMIZE TABLE operation using table copy. 437OPTIMIZE TABLE t1; 438connection con1; 439SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 440affected rows: 0 441INSERT INTO t1 VALUES(10, 20); 442affected rows: 1 443connection default; 444Table Op Msg_type Msg_text 445test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 446test.t1 optimize status OK 447affected rows: 2 448SET DEBUG_SYNC= 'RESET'; 449affected rows: 0 450SET SESSION old_alter_table= FALSE; 451affected rows: 0 452#ALTER TABLE FORCE and ALTER TABLE ENGINE uses table copy 453#when ALGORITHM COPY is used. 454ALTER TABLE t1 FORCE, ALGORITHM= COPY; 455affected rows: 2 456info: Records: 2 Duplicates: 0 Warnings: 0 457ALTER TABLE t1 ENGINE= INNODB, ALGORITHM= COPY; 458affected rows: 2 459info: Records: 2 Duplicates: 0 Warnings: 0 460DROP TABLE t1; 461#OPTIMIZE TABLE on a table with FULLTEXT index uses 462#ALTER TABLE FORCE using COPY algorithm here. This 463#test case ensures the COPY table debug sync point is hit. 464SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded'; 465#Setup a table with FULLTEXT index. 466connection default; 467CREATE TABLE t1(fld1 CHAR(10), FULLTEXT(fld1)) ENGINE= INNODB; 468INSERT INTO t1 VALUES("String1"); 469#OPTIMIZE TABLE operation. 470OPTIMIZE TABLE t1; 471connection con1; 472SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; 473INSERT INTO t1 VALUES("String2"); 474connection default; 475Table Op Msg_type Msg_text 476test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 477test.t1 optimize status OK 478SET DEBUG_SYNC= 'RESET'; 479DROP TABLE t1; 480#Test which demonstrates that ALTER TABLE, OPTIMIZE PARTITION 481#takes OPTIMIZE TABLE code path, hence does an online rebuild 482#of the table with the patch. 483connection default; 484SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; 485#Setup PARTITIONED table. 486CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4; 487INSERT INTO t1 VALUES(10); 488#OPTIMIZE ALL PARTITIONS operation. 489ALTER TABLE t1 OPTIMIZE PARTITION ALL; 490connection con1; 491SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; 492# With the patch, concurrent DML operation succeeds. 493INSERT INTO t1 VALUES (30); 494UPDATE t1 SET fld1= 20 WHERE fld1= 10; 495DELETE FROM t1 WHERE fld1= 20; 496SELECT * from t1; 497fld1 49830 499SET DEBUG_SYNC= 'now SIGNAL continue'; 500connection default; 501Table Op Msg_type Msg_text 502test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. 503test.t1 optimize status OK 504SET DEBUG_SYNC= 'RESET'; 505#OPTIMIZE PER PARTITION operation. 506SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; 507ALTER TABLE t1 OPTIMIZE PARTITION p0; 508connection con1; 509SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; 510# With the patch, concurrent DML operation succeeds. 511INSERT INTO t1 VALUES (30); 512UPDATE t1 SET fld1= 20 WHERE fld1= 10; 513DELETE FROM t1 WHERE fld1= 20; 514SELECT * from t1; 515fld1 51630 51730 518SET DEBUG_SYNC= 'now SIGNAL continue'; 519connection default; 520Table Op Msg_type Msg_text 521test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. 522test.t1 optimize status OK 523SET DEBUG_SYNC= 'RESET'; 524# Test case for Bug#11938817 (ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED). 525# This should not do anything 526ALTER TABLE t1; 527affected rows: 0 528SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild'; 529# Check that we rebuild the table 530ALTER TABLE t1 engine=innodb; 531connection con1; 532SET DEBUG_SYNC= 'now WAIT_FOR rebuild'; 533connection default; 534SET DEBUG_SYNC= 'RESET'; 535SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild'; 536# Check that we rebuild the table 537ALTER TABLE t1 FORCE; 538connection con1; 539SET DEBUG_SYNC= 'now WAIT_FOR rebuild'; 540connection default; 541SET DEBUG_SYNC= 'RESET'; 542DROP TABLE t1; 543# 544# BUG#20367116: ALTER TABLE BREAKS ON DELETE CASCADE FOREIGN KEY 545# CONSTRAINT 546# Test case to ensure there are no orphaned rows. 547# (ALTER TABLE, COPY) Algorithm. 548CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1)) ENGINE=INNODB; 549CREATE TABLE t2(f2 INT NOT NULL, foreign key(f2) REFERENCES t1(f1) 550ON DELETE CASCADE)ENGINE=INNODB; 551INSERT INTO t1 VALUES(1); 552INSERT INTO t2 VALUES(1); 553SET DEBUG_SYNC= 'commit_alter_copy_table SIGNAL delete_parent WAIT_FOR delete_child'; 554ALTER TABLE t2 ADD f3 INT NOT NULL, ALGORITHM=COPY; 555connect con1, localhost, root,,; 556SET DEBUG_SYNC= 'now WAIT_FOR delete_parent'; 557DELETE FROM t1 WHERE f1 = 1; 558# Without the patch, there is no table MDL wait, so the below 559# times out. 560connect con2, localhost, root,,; 561SET DEBUG_SYNC= 'now SIGNAL delete_child'; 562connection con1; 563connection default; 564# Without the patch, there will be an orphaned row in table 't2'. 565SELECT * FROM t2; 566f2 f3 567SELECT * FROM t1; 568f1 569DROP TABLE t2, t1; 570# Cleanup 571SET DEBUG_SYNC= 'RESET'; 572disconnect con1; 573disconnect con2; 574# Test case to ensure there is no deadlock. 575# (ALTER TABLE, INPLACE) algorithm. 576CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB; 577CREATE TABLE t2(f2 INT NOT NULL, FOREIGN KEY(f2) REFERENCES t1(f1) ON DELETE CASCADE)ENGINE=INNODB; 578INSERT INTO t1 VALUES(1); 579INSERT INTO t2 VALUES(1); 580SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL delete_parent WAIT_FOR alter_child'; 581ALTER TABLE t2 ADD f3 INT NOT NULL, ALGORITHM=INPLACE; 582connect con1, localhost, root,,; 583SET DEBUG_SYNC='now WAIT_FOR delete_parent'; 584DELETE FROM t1 WHERE f1 = 1; 585# Without the patch, there is no table MDL wait, so the below times out. 586connect con2, localhost, root,,; 587SET DEBUG_SYNC='now signal alter_child'; 588connection con1; 589# Cleanup 590connection default; 591DROP TABLE t2, t1; 592SET DEBUG_SYNC= 'RESET'; 593disconnect con1; 594disconnect con2; 595# Test case to ensure that the parent's parent is also locked. 596# (ALTER TABLE, COPY) Algorithm. 597CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1)) ENGINE=INNODB; 598CREATE TABLE t2(f2 INT NOT NULL, f3 INT NOT NULL, FOREIGN KEY(f2) 599REFERENCES t1(f1) ON DELETE CASCADE, 600PRIMARY KEY(f3))ENGINE=INNODB; 601CREATE TABLE t3(f4 INT NOT NULL, FOREIGN KEY(f4) REFERENCES t2(f3) 602ON DELETE CASCADE) ENGINE=INNODB; 603INSERT INTO t1 VALUES(1); 604INSERT INTO t2 VALUES(1, 2); 605INSERT INTO t3 VALUES(2); 606SET DEBUG_SYNC= 'commit_alter_copy_table SIGNAL delete_parent_parent WAIT_FOR delete_child'; 607ALTER TABLE t3 ADD f5 INT NOT NULL, ALGORITHM=COPY; 608connect con1, localhost, root,,; 609SET DEBUG_SYNC= 'now WAIT_FOR delete_parent_parent'; 610DELETE FROM t1 WHERE f1 = 1; 611# Without the patch, there is no table MDL wait, so the below 612# times out. 613connect con2, localhost, root,,; 614SET DEBUG_SYNC= 'now SIGNAL delete_child'; 615connection con1; 616connection default; 617# Without the patch, there will be an orphaned row in table 't3'. 618SELECT * FROM t1; 619f1 620SELECT * FROM t2; 621f2 f3 622SELECT * FROM t3; 623f4 f5 624DROP TABLE t3, t2, t1; 625# Cleanup 626SET DEBUG_SYNC= 'RESET'; 627disconnect con1; 628disconnect con2; 629# Test case to ensure there is no deadlock by locking parent's parent. 630# (ALTER TABLE, INPLACE) algorithm. 631CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB; 632CREATE TABLE t2(f2 INT NOT NULL, f3 INT NOT NULL, FOREIGN KEY(f2) 633REFERENCES t1(f1) ON DELETE CASCADE, 634PRIMARY KEY(f3))ENGINE=INNODB; 635CREATE TABLE t3(f4 INT NOT NULL, FOREIGN KEY(f4) REFERENCES t2(f3) 636ON DELETE CASCADE) ENGINE=INNODB; 637INSERT INTO t1 VALUES(1); 638INSERT INTO t2 VALUES(1, 2); 639INSERT INTO t3 VALUES(2); 640SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL delete_parent_parent WAIT_FOR alter_child'; 641ALTER TABLE t3 ADD f3 INT NOT NULL, ALGORITHM=INPLACE; 642connect con1, localhost, root,,; 643SET DEBUG_SYNC='now WAIT_FOR delete_parent_parent'; 644DELETE FROM t1 WHERE f1 = 1; 645# Without the patch, there is no table MDL wait, so the below times out. 646connect con2, localhost, root,,; 647SET DEBUG_SYNC='now signal alter_child'; 648connection con1; 649# Cleanup 650connection default; 651DROP TABLE t3, t2, t1; 652SET DEBUG_SYNC= 'RESET'; 653disconnect con1; 654disconnect con2; 655# Test case where ALTER is performed under LOCK TABLES. 656# (ALTER TABLE, COPY) Algorithm. 657CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1)) ENGINE=INNODB; 658CREATE TABLE t2(f2 INT NOT NULL, FOREIGN KEY(f2) REFERENCES t1(f1) 659ON DELETE CASCADE)ENGINE=INNODB; 660INSERT INTO t1 VALUES(1); 661INSERT INTO t2 VALUES(1); 662SET DEBUG_SYNC= 'commit_alter_copy_table SIGNAL delete_parent_parent WAIT_FOR delete_child'; 663LOCK TABLES t2 WRITE; 664ALTER TABLE t2 ADD f5 INT NOT NULL, ALGORITHM=COPY; 665connect con1, localhost, root,,; 666SET DEBUG_SYNC= 'now WAIT_FOR delete_parent_parent'; 667DELETE FROM t1 WHERE f1 = 1; 668# Without the patch, there is no table MDL wait, so the below 669# times out. 670connect con2, localhost, root,,; 671SET DEBUG_SYNC= 'now SIGNAL delete_child'; 672connection con1; 673connection default; 674# Without the patch, there will be an orphaned row in table 't2'. 675UNLOCK TABLES; 676SELECT * FROM t1; 677f1 678SELECT * FROM t2; 679f2 f5 680DROP TABLE t2, t1; 681# Cleanup 682SET DEBUG_SYNC= 'RESET'; 683disconnect con1; 684disconnect con2; 685# Test case where ALTER is performed under LOCK TABLES. 686# (ALTER TABLE, INPLACE) algorithm. 687CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB; 688CREATE TABLE t2(f2 INT NOT NULL, FOREIGN KEY(f2) REFERENCES t1(f1) 689ON DELETE CASCADE)ENGINE=INNODB; 690INSERT INTO t1 VALUES(1); 691INSERT INTO t2 VALUES(1); 692SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL delete_parent_parent WAIT_FOR alter_child'; 693LOCK TABLES t2 WRITE; 694ALTER TABLE t2 ADD f3 INT NOT NULL, ALGORITHM=INPLACE; 695connect con1, localhost, root,,; 696SET DEBUG_SYNC='now WAIT_FOR delete_parent_parent'; 697DELETE FROM t1 WHERE f1 = 1; 698# Without the patch, there is no table MDL wait, so the below times out. 699connect con2, localhost, root,,; 700SET DEBUG_SYNC='now signal alter_child'; 701connection con1; 702# Cleanup 703connection default; 704UNLOCK TABLES; 705DROP TABLE t2, t1; 706SET DEBUG_SYNC= 'RESET'; 707disconnect con1; 708disconnect con2; 709# 710# BUG#21631284: DROP VIRTUAL COLUMN RESULT IN DROP WRONG INDEX. 711# 712# Index is not rebuilt, since there is no change in the definition. 713CREATE TABLE t1 (fld1 VARCHAR(300), fld2 INT, KEY idx1(fld2, fld1(200))) 714ENGINE=InnoDB; 715SET debug="+d,innodb_index_drop_count_zero"; 716#Without the patch, an error is reported. 717ALTER TABLE t1 FORCE; 718#cleanup 719DROP TABLE t1; 720SET debug="-d,innodb_index_drop_count_zero"; 721# Index is rebuilt since the index is changed from prefixed 722# to non-prefixed index. 723CREATE TABLE t1 (fld1 CHAR(10), KEY idx1(fld1(5))) ENGINE=InnoDB; 724SET debug="+d,innodb_index_drop_count_one"; 725#Without the patch, an error is reported. 726ALTER TABLE t1 MODIFY fld1 CHAR(5); 727#cleanup 728DROP TABLE t1; 729SET debug="-d,innodb_index_drop_count_one"; 730# Coverage test cases. 731# Index is rebuilt since the index is changed from non-prefixed 732# to prefixed index. 733CREATE TABLE t1 (fld1 CHAR(10), KEY idx1(fld1)) ENGINE=InnoDB; 734SET debug="+d,innodb_index_drop_count_one"; 735#In case of incorrect behavior, an error is reported. 736ALTER TABLE t1 MODIFY fld1 CHAR(5); 737#cleanup 738DROP TABLE t1; 739SET debug="-d,innodb_index_drop_count_one"; 740# Index is not rebuilt since the index prefix length is 741# the same. 742CREATE TABLE t1 (fld1 CHAR(10), KEY idx1(fld1(5))) ENGINE=InnoDB; 743SET debug="+d,innodb_index_drop_count_zero"; 744#In case of incorrect behavior, an error is reported. 745ALTER TABLE t1 MODIFY fld1 CHAR(20); 746#cleanup 747DROP TABLE t1; 748SET debug="-d,innodb_index_drop_count_zero"; 749# 750# BUG#26848813: INDEXED COLUMN CAN'T BE CHANGED FROM VARCHAR(15) 751# TO VARCHAR(40) INSTANTANEOUSLY 752CREATE TABLE t1(fld1 VARCHAR(5), KEY(fld1)) ENGINE= InnoDB; 753SET DEBUG="+d,innodb_index_drop_count_zero"; 754# Without patch, an error is reported. 755ALTER TABLE t1 MODIFY fld1 VARCHAR(7), ALGORITHM= INPLACE; 756# Scenario where non-packed keys is converted to packed keys 757# before the patch, an error is reported. 758ALTER TABLE t1 MODIFY fld1 VARCHAR(9), ALGORITHM= INPLACE; 759SET DEBUG="-d,innodb_index_drop_count_zero"; 760# Tests added for covering cases where rebuild is required. 761# Reducing the size of the field. 762ALTER TABLE t1 MODIFY fld1 VARCHAR(3), ALGORITHM= INPLACE; 763ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. 764# Increasing the size of the field to boundary condition. 765ALTER TABLE t1 MODIFY fld1 VARCHAR(256), ALGORITHM= INPLACE; 766ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. 767DROP TABLE t1; 768