4 files changed, 102 insertions, 4 deletions
diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result
index d484ab77701..de57e3859b7 100644
--- a/mysql-test/r/mdl_sync.result
+++ b/mysql-test/r/mdl_sync.result
@@ -2913,3 +2913,41 @@ UNLOCK TABLES;
 # Connection default
 UNLOCK TABLES;
 DROP DATABASE db1;
+#
+# Bug#56292 Deadlock with ALTER TABLE and MERGE tables
+#
+DROP TABLE IF EXISTS t1, t2, m1;
+CREATE TABLE t1(a INT) engine=MyISAM;
+CREATE TABLE t2(a INT) engine=MyISAM;
+CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (3), (4);
+# Connection con1
+SET DEBUG_SYNC= 'mdl_upgrade_shared_lock_to_exclusive SIGNAL upgrade WAIT_FOR continue';
+# Sending:
+ALTER TABLE m1 engine=MERGE UNION=(t2, t1);
+# Connection con2
+# Waiting for ALTER TABLE to try lock upgrade
+SET DEBUG_SYNC= 'now WAIT_FOR upgrade';
+# Sending:
+DELETE FROM t2 WHERE a = 3;
+# Connection default
+# Check that DELETE is waiting on a metadata lock and not a table lock.
+# Now that DELETE blocks on a metadata lock, we should be able to do
+# SELECT * FROM m1 here. SELECT used to be blocked by a DELETE table
+# lock request.
+SELECT * FROM m1;
+a
+1
+2
+3
+4
+# Resuming ALTER TABLE
+SET DEBUG_SYNC= 'now SIGNAL continue';
+# Connection con1
+# Reaping: ALTER TABLE m1 engine=MERGE UNION=(t2, t1)
+# Connection con2
+# Reaping: DELETE FROM t2 WHERE a = 3
+# Connection default
+DROP TABLE m1, t1, t2;
+SET DEBUG_SYNC= 'RESET';
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index c7e54288f8b..5d96970ce74 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -3444,13 +3444,12 @@ ALTER TABLE m1 ADD INDEX (c1);
 UNLOCK TABLES;
 DROP TABLE m1, t1;
 #
-# If children are to be altered, they need an explicit lock.
+# Locking the merge table will implicitly lock children.
 #
 CREATE TABLE t1 (c1 INT);
 CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1);
 LOCK TABLE m1 WRITE;
 ALTER TABLE t1 ADD INDEX (c1);
-ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
 LOCK TABLE m1 WRITE, t1 WRITE;
 ALTER TABLE t1 ADD INDEX (c1);
 UNLOCK TABLES;
diff --git a/mysql-test/t/mdl_sync.test b/mysql-test/t/mdl_sync.test
index 55c04d7870b..f7780d2003a 100644
--- a/mysql-test/t/mdl_sync.test
+++ b/mysql-test/t/mdl_sync.test
@@ -4532,6 +4532,68 @@ disconnect con2;
 disconnect con3;
 
 
+--echo #
+--echo # Bug#56292 Deadlock with ALTER TABLE and MERGE tables
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, m1;
+--enable_warnings
+
+CREATE TABLE t1(a INT) engine=MyISAM;
+CREATE TABLE t2(a INT) engine=MyISAM;
+CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2);
+
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (3), (4);
+
+connect(con1, localhost, root);
+connect(con2, localhost, root);
+
+--echo # Connection con1
+connection con1;
+SET DEBUG_SYNC= 'mdl_upgrade_shared_lock_to_exclusive SIGNAL upgrade WAIT_FOR continue';
+--echo # Sending:
+--send ALTER TABLE m1 engine=MERGE UNION=(t2, t1)
+
+--echo # Connection con2
+connection con2;
+--echo # Waiting for ALTER TABLE to try lock upgrade
+SET DEBUG_SYNC= 'now WAIT_FOR upgrade';
+--echo # Sending:
+--send DELETE FROM t2 WHERE a = 3
+
+--echo # Connection default
+connection default;
+--echo # Check that DELETE is waiting on a metadata lock and not a table lock.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock" AND
+        info = "DELETE FROM t2 WHERE a = 3";
+--source include/wait_condition.inc
+--echo # Now that DELETE blocks on a metadata lock, we should be able to do
+--echo # SELECT * FROM m1 here. SELECT used to be blocked by a DELETE table
+--echo # lock request.
+SELECT * FROM m1;
+--echo # Resuming ALTER TABLE
+SET DEBUG_SYNC= 'now SIGNAL continue';
+
+--echo # Connection con1
+connection con1;
+--echo # Reaping: ALTER TABLE m1 engine=MERGE UNION=(t2, t1)
+--reap
+--echo # Connection con2
+connection con2;
+--echo # Reaping: DELETE FROM t2 WHERE a = 3
+--reap
+--echo # Connection default
+connection default;
+DROP TABLE m1, t1, t2;
+SET DEBUG_SYNC= 'RESET';
+disconnect con1;
+disconnect con2;
+
+
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index b6ad3324d19..1553d7f29c5 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -2519,12 +2519,11 @@ UNLOCK TABLES;
 DROP TABLE m1, t1;
 
 --echo #
---echo # If children are to be altered, they need an explicit lock.
+--echo # Locking the merge table will implicitly lock children.
 --echo #
 CREATE TABLE t1 (c1 INT);
 CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1);
 LOCK TABLE m1 WRITE;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
 ALTER TABLE t1 ADD INDEX (c1);
 LOCK TABLE m1 WRITE, t1 WRITE;
 ALTER TABLE t1 ADD INDEX (c1);