From 446ec646511adf4327f9f5d9414fc4cb75c8161c Mon Sep 17 00:00:00 2001 From: Thirunarayanan Balathandayuthapani Date: Tue, 1 Mar 2022 13:01:48 +0530 Subject: MDEV-27962 Instant DDL downgrades the MDL when table is empty - Server incorrectly downgrading the MDL after prepare phase when table is empty. mdl_exclusive_after_prepare is being set in prepare phase only. But mdl_exclusive_after_prepare condition was misplaced and checked before prepare phase by commit d270525dfde86bcb92a2327234a0954083e14a94 and it is now changed to check after prepare phase. - main.innodb_mysql_sync test case was changed to avoid locking optimization when table is empty. --- mysql-test/main/innodb_mysql_sync.result | 12 ++++++++---- mysql-test/main/innodb_mysql_sync.test | 13 +++++++------ mysql-test/suite/innodb/r/instant_alter_debug.result | 17 ++++++++++++++++- mysql-test/suite/innodb/t/instant_alter_debug.test | 19 ++++++++++++++++++- 4 files changed, 49 insertions(+), 12 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/innodb_mysql_sync.result b/mysql-test/main/innodb_mysql_sync.result index 3f284edde86..5e1f60d3e25 100644 --- a/mysql-test/main/innodb_mysql_sync.result +++ b/mysql-test/main/innodb_mysql_sync.result @@ -131,6 +131,7 @@ connection default; DROP DATABASE db1; # Test 2: Primary index (implicit), should block writes. CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb; +INSERT INTO t1 VALUES(1, 2); SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; # Sending: ALTER TABLE t1 ADD UNIQUE INDEX(a), LOCK=SHARED; @@ -139,15 +140,16 @@ SET DEBUG_SYNC= "now WAIT_FOR manage"; USE test; SELECT * FROM t1; a b +1 2 # Sending: -UPDATE t1 SET a=NULL; +UPDATE t1 SET a=3; connection con2; # Waiting for SELECT to be blocked by the metadata lock on t1 SET DEBUG_SYNC= "now SIGNAL query"; connection default; # Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a) connection con1; -# Reaping: UPDATE t1 SET a=NULL +# Reaping: UPDATE t1 SET a=3 # Test 3: Primary index (explicit), should block writes. connection default; ALTER TABLE t1 DROP INDEX a; @@ -158,15 +160,16 @@ connection con1; SET DEBUG_SYNC= "now WAIT_FOR manage"; SELECT * FROM t1; a b +3 2 # Sending: -UPDATE t1 SET a=NULL; +UPDATE t1 SET a=4; connection con2; # Waiting for SELECT to be blocked by the metadata lock on t1 SET DEBUG_SYNC= "now SIGNAL query"; connection default; # Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a) connection con1; -# Reaping: UPDATE t1 SET a=NULL +# Reaping: UPDATE t1 SET a=4 # Test 4: Secondary unique index, should not block reads. connection default; SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; @@ -176,6 +179,7 @@ connection con1; SET DEBUG_SYNC= "now WAIT_FOR manage"; SELECT * FROM t1; a b +4 2 SET DEBUG_SYNC= "now SIGNAL query"; connection default; # Reaping: ALTER TABLE t1 ADD UNIQUE (b) diff --git a/mysql-test/main/innodb_mysql_sync.test b/mysql-test/main/innodb_mysql_sync.test index 4026080c4b4..466bcb360c5 100644 --- a/mysql-test/main/innodb_mysql_sync.test +++ b/mysql-test/main/innodb_mysql_sync.test @@ -176,6 +176,7 @@ DROP DATABASE db1; --echo # Test 2: Primary index (implicit), should block writes. CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb; +INSERT INTO t1 VALUES(1, 2); SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; --echo # Sending: --send ALTER TABLE t1 ADD UNIQUE INDEX(a), LOCK=SHARED @@ -185,13 +186,13 @@ SET DEBUG_SYNC= "now WAIT_FOR manage"; USE test; SELECT * FROM t1; --echo # Sending: ---send UPDATE t1 SET a=NULL +--send UPDATE t1 SET a=3 connection con2; --echo # Waiting for SELECT to be blocked by the metadata lock on t1 let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist WHERE state= 'Waiting for table metadata lock' - AND info='UPDATE t1 SET a=NULL'; + AND info='UPDATE t1 SET a=3'; --source include/wait_condition.inc SET DEBUG_SYNC= "now SIGNAL query"; @@ -200,7 +201,7 @@ connection default; --reap connection con1; ---echo # Reaping: UPDATE t1 SET a=NULL +--echo # Reaping: UPDATE t1 SET a=3 --reap --echo # Test 3: Primary index (explicit), should block writes. @@ -215,13 +216,13 @@ connection con1; SET DEBUG_SYNC= "now WAIT_FOR manage"; SELECT * FROM t1; --echo # Sending: ---send UPDATE t1 SET a=NULL +--send UPDATE t1 SET a=4 connection con2; --echo # Waiting for SELECT to be blocked by the metadata lock on t1 let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist WHERE state= 'Waiting for table metadata lock' - AND info='UPDATE t1 SET a=NULL'; + AND info='UPDATE t1 SET a=4'; --source include/wait_condition.inc SET DEBUG_SYNC= "now SIGNAL query"; @@ -230,7 +231,7 @@ connection default; --reap connection con1; ---echo # Reaping: UPDATE t1 SET a=NULL +--echo # Reaping: UPDATE t1 SET a=4 --reap --echo # Test 4: Secondary unique index, should not block reads. diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result index 11acb2734e7..5b9cee57389 100644 --- a/mysql-test/suite/innodb/r/instant_alter_debug.result +++ b/mysql-test/suite/innodb/r/instant_alter_debug.result @@ -462,12 +462,27 @@ INSERT INTO t1 SET a=0, i=REPEAT('1', 10000); ROLLBACK; set DEBUG_SYNC='now SIGNAL go'; connection default; -disconnect con1; SELECT * FROM t1; a b c d e f g h i 1 2 3 4 5 6 7 8 test DROP TABLE t1; SET DEBUG_SYNC=RESET; +# +# MDEV-27962 Instant DDL downgrades the MDL when table is empty +# +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL)ENGINE=InnoDB; +SET DEBUG_SYNC="alter_table_inplace_after_lock_downgrade SIGNAL try_insert WAIT_FOR alter_progress"; +ALTER TABLE t1 ADD INDEX(f1), ADD INDEX(f2); +connection con1; +SET SESSION lock_wait_timeout=1; +SET DEBUG_SYNC="now WAIT_FOR try_insert"; +INSERT INTO t1 VALUES(1, 2); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SET DEBUG_SYNC="now SIGNAL alter_progress"; +disconnect con1; +connection default; +DROP TABLE t1; +SET DEBUG_SYNC=reset; # End of 10.4 tests SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; SELECT variable_value-@old_instant instants diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test index f960affc372..e31b378ff10 100644 --- a/mysql-test/suite/innodb/t/instant_alter_debug.test +++ b/mysql-test/suite/innodb/t/instant_alter_debug.test @@ -533,11 +533,28 @@ set DEBUG_SYNC='now SIGNAL go'; connection default; reap; -disconnect con1; SELECT * FROM t1; DROP TABLE t1; SET DEBUG_SYNC=RESET; +--echo # +--echo # MDEV-27962 Instant DDL downgrades the MDL when table is empty +--echo # +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL)ENGINE=InnoDB; +SET DEBUG_SYNC="alter_table_inplace_after_lock_downgrade SIGNAL try_insert WAIT_FOR alter_progress"; +send ALTER TABLE t1 ADD INDEX(f1), ADD INDEX(f2); +connection con1; +SET SESSION lock_wait_timeout=1; +SET DEBUG_SYNC="now WAIT_FOR try_insert"; +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES(1, 2); +SET DEBUG_SYNC="now SIGNAL alter_progress"; +disconnect con1; +connection default; +reap; +DROP TABLE t1; +SET DEBUG_SYNC=reset; + --echo # End of 10.4 tests SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; -- cgit v1.2.1