Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE

            corrupts a MERGE table
Bug 26867 - LOCK TABLES + REPAIR + merge table result in
            memory/cpu hogging
Bug 26377 - Deadlock with MERGE and FLUSH TABLE
Bug 25038 - Waiting TRUNCATE
Bug 25700 - merge base tables get corrupted by
            optimize/analyze/repair table
Bug 30275 - Merge tables: flush tables or unlock tables
            causes server to crash
Bug 19627 - temporary merge table locking
Bug 27660 - Falcon: merge table possible
Bug 30273 - merge tables: Can't lock file (errno: 155)

The problems were:

Bug 26379 - Combination of FLUSH TABLE and REPAIR TABLE
                corrupts a MERGE table

  1. A thread trying to lock a MERGE table performs busy waiting while
     REPAIR TABLE or a similar table administration task is ongoing on
     one or more of its MyISAM tables.
  
  2. A thread trying to lock a MERGE table performs busy waiting until all
     threads that did REPAIR TABLE or similar table administration tasks
     on one or more of its MyISAM tables in LOCK TABLES segments do UNLOCK
     TABLES. The difference against problem #1 is that the busy waiting
     takes place *after* the administration task. It is terminated by
     UNLOCK TABLES only.
  
  3. Two FLUSH TABLES within a LOCK TABLES segment can invalidate the
     lock. This does *not* require a MERGE table. The first FLUSH TABLES
     can be replaced by any statement that requires other threads to
     reopen the table. In 5.0 and 5.1 a single FLUSH TABLES can provoke
     the problem.

Bug 26867 - LOCK TABLES + REPAIR + merge table result in
            memory/cpu hogging

  Trying DML on a MERGE table, which has a child locked and
  repaired by another thread, made an infinite loop in the server.

Bug 26377 - Deadlock with MERGE and FLUSH TABLE

  Locking a MERGE table and its children in parent-child order
  and flushing the child deadlocked the server.

Bug 25038 - Waiting TRUNCATE

  Truncating a MERGE child, while the MERGE table was in use,
  let the truncate fail instead of waiting for the table to
  become free.

Bug 25700 - merge base tables get corrupted by
            optimize/analyze/repair table

  Repairing a child of an open MERGE table corrupted the child.
  It was necessary to FLUSH the child first.

Bug 30275 - Merge tables: flush tables or unlock tables
            causes server to crash

  Flushing and optimizing locked MERGE children crashed the server.

Bug 19627 - temporary merge table locking

  Use of a temporary MERGE table with non-temporary children
  could corrupt the children.

  Temporary tables are never locked. So we do now prohibit
  non-temporary chidlren of a temporary MERGE table.

Bug 27660 - Falcon: merge table possible

  It was possible to create a MERGE table with non-MyISAM children.

Bug 30273 - merge tables: Can't lock file (errno: 155)

  This was a Windows-only bug. Table administration statements
  sometimes failed with "Can't lock file (errno: 155)".

These bugs are fixed by a new implementation of MERGE table open.

When opening a MERGE table in open_tables() we do now add the
child tables to the list of tables to be opened by open_tables()
(the "query_list"). The children are not opened in the handler at
this stage.

After opening the parent, open_tables() opens each child from the
now extended query_list. When the last child is opened, we remove
the children from the query_list again and attach the children to
the parent. This behaves similar to the old open. However it does
not open the MyISAM tables directly, but grabs them from the already
open children.

When closing a MERGE table in close_thread_table() we detach the
children only. Closing of the children is done implicitly because
they are in thd->open_tables.

For more detail see the comment at the top of ha_myisammrg.cc.

Changed from open_ltable() to open_and_lock_tables() in all places
that can be relevant for MERGE tables. The latter can handle tables
added to the list on the fly. When open_ltable() was used in a loop
over a list of tables, the list must be temporarily terminated
after every table for open_and_lock_tables().
table_list->required_type is set to FRMTYPE_TABLE to avoid open of
special tables. Handling of derived tables is suppressed.
These details are handled by the new function
open_n_lock_single_table(), which has nearly the same signature as
open_ltable() and can replace it in most cases.

In reopen_tables() some of the tables open by a thread can be
closed and reopened. When a MERGE child is affected, the parent
must be closed and reopened too. Closing of the parent is forced
before the first child is closed. Reopen happens in the order of
thd->open_tables. MERGE parents do not attach their children
automatically at open. This is done after all tables are reopened.
So all children are open when attaching them.

Special lock handling like mysql_lock_abort() or mysql_lock_remove()
needs to be suppressed for MERGE children or forwarded to the parent.
This depends on the situation. In loops over all open tables one
suppresses child lock handling. When a single table is touched,
forwarding is done.

Behavioral changes:
===================

This patch changes the behavior of temporary MERGE tables.
Temporary MERGE must have temporary children.
The old behavior was wrong. A temporary table is not locked. Hence
even non-temporary children were not locked. See
Bug 19627 - temporary merge table locking.

You cannot change the union list of a non-temporary MERGE table
when LOCK TABLES is in effect. The following does *not* work:
CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...;
LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE;
ALTER TABLE m1 ... UNION=(t1,t2) ...;
However, you can do this with a temporary MERGE table.

You cannot create a MERGE table with CREATE ... SELECT, neither
as a temporary MERGE table, nor as a non-temporary MERGE table.
CREATE TABLE m1 ... ENGINE=MRG_MYISAM ... SELECT ...;
Gives error message: table is not BASE TABLE.

12 files changed, 2160 insertions, 79 deletions

diff --git a/mysql-test/extra/binlog_tests/blackhole.test b/mysql-test/extra/binlog_tests/blackhole.test
index 1ca59955d76..df2295af4ff 100644
--- a/mysql-test/extra/binlog_tests/blackhole.test
+++ b/mysql-test/extra/binlog_tests/blackhole.test
@@ -134,6 +134,15 @@ drop table t1,t2,t3;
 #             table
 #
 CREATE TABLE t1(a INT) ENGINE=BLACKHOLE;
+# NOTE: After exchanging open_ltable() by open_and_lock_tables() in
+# handle_delayed_insert() to fix problems with MERGE tables (Bug#26379),
+# problems with INSERT DELAYED and BLACKHOLE popped up. open_ltable()
+# does not check if the binlogging capabilities of the statement and the
+# table match. So the below used to succeed. But since INSERT DELAYED
+# switches to row-based logging in mixed-mode and BLACKHOLE cannot do
+# row-based logging, it could not really work. Until this problem is
+# correctly fixed, we have that error here.
+--error ER_BINLOG_LOGGING_IMPOSSIBLE
 INSERT DELAYED INTO t1 VALUES(1);
 DROP TABLE t1;
 
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index ca3b344af53..73a9dba4e69 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -594,7 +594,7 @@ create table t1 (a int);
 create table t1 select * from t1;
 ERROR HY000: You can't specify target table 't1' for update in FROM clause
 create table t2 union = (t1) select * from t1;
-ERROR HY000: You can't specify target table 't1' for update in FROM clause
+ERROR HY000: 'test.t2' is not BASE TABLE
 flush tables with read lock;
 unlock tables;
 drop table t1;
diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result
index c520ab52ab3..92a20630d77 100644
--- a/mysql-test/r/delayed.result
+++ b/mysql-test/r/delayed.result
@@ -250,11 +250,6 @@ SELECT HEX(a) FROM t1;
 HEX(a)
 1
 DROP TABLE t1;
-CREATE TABLE t1(c1 INT) ENGINE=MyISAM;
-CREATE TABLE t2(c1 INT) ENGINE=MERGE UNION=(t1);
-INSERT DELAYED INTO t2 VALUES(1);
-ERROR HY000: Table storage engine for 't2' doesn't have this option
-DROP TABLE t1, t2;
 DROP TABLE IF EXISTS t1,t2;
 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
 CREATE TABLE `t1` (
diff --git a/mysql-test/r/merge-big.result b/mysql-test/r/merge-big.result
new file mode 100644
index 00000000000..82fedc1df73
--- /dev/null
+++ b/mysql-test/r/merge-big.result
@@ -0,0 +1,77 @@
+drop table if exists t1,t2,t3,t4,t5,t6;
+#
+# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
+#             corrupts a MERGE table
+# Problem #3
+#
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+LOCK TABLE t1 WRITE;
+# connection con1
+SET SESSION debug="+d,sleep_open_and_lock_after_open";
+INSERT INTO t1 VALUES (1);
+# connection default
+# Let INSERT go into thr_multi_lock().
+# Kick INSERT out of thr_multi_lock().
+FLUSH TABLES;
+# Let INSERT go through open_tables() where it sleeps.
+# Unlock and close table and wait for con1 to close too.
+FLUSH TABLES;
+# This should give no result.
+SELECT * FROM t1;
+c1
+UNLOCK TABLES;
+# connection con1
+SET SESSION debug="-d,sleep_open_and_lock_after_open";
+# connection default
+DROP TABLE t1;
+#
+# Extra tests for Bug#26379 - Combination of FLUSH TABLE and
+#                             REPAIR TABLE corrupts a MERGE table
+#
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TABLE t3 (c1 INT);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (3);
+#
+# CREATE ... SELECT
+# try to access parent from another thread.
+#
+# connection con1
+SET SESSION debug="+d,sleep_create_select_before_lock";
+CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
+INSERT_METHOD=FIRST SELECT * FROM t3;
+# connection default
+# Now try to access the parent.
+# If 3 is in table, SELECT had to wait.
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+# connection con1
+SET SESSION debug="-d,sleep_create_select_before_lock";
+# connection default
+# Cleanup for next test.
+DROP TABLE t4;
+DELETE FROM t1 WHERE c1 != 1;
+#
+# CREATE ... SELECT
+# try to access child from another thread.
+#
+# connection con1
+SET SESSION debug="+d,sleep_create_select_before_lock";
+CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
+INSERT_METHOD=FIRST SELECT * FROM t3;
+# connection default
+# Now try to access a child.
+# If 3 is in table, SELECT had to wait.
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+3
+# connection con1
+SET SESSION debug="-d,sleep_create_select_before_lock";
+# connection default
+DROP TABLE t1, t2, t3, t4;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index d6e19107ec4..74441db2512 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -584,9 +584,7 @@ insert into t1 values (1);
 insert into t2 values (2);
 create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
 select * from t3;
-a
-1
-2
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
 create temporary table t4 (a int not null);
 create temporary table t5 (a int not null);
 insert into t4 values (1);
@@ -597,6 +595,54 @@ a
 1
 2
 drop table t6, t3, t1, t2, t4, t5;
+create temporary table t1 (a int not null);
+create temporary table t2 (a int not null);
+insert into t1 values (1);
+insert into t2 values (2);
+create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
+select * from t3;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+drop table t3, t2, t1;
+create table t1 (a int not null);
+create temporary table t2 (a int not null);
+insert into t1 values (1);
+insert into t2 values (2);
+create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
+select * from t3;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+drop table t3;
+create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
+select * from t3;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+drop table t3, t2, t1;
+# CREATE...SELECT is not implemented for MERGE tables.
+CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL);
+CREATE TEMPORARY TABLE t2 (c1 INT NOT NULL);
+CREATE TABLE t3 (c1 INT NOT NULL);
+INSERT INTO t3 VALUES (3), (33);
+LOCK TABLES t3 READ;
+CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
+INSERT_METHOD=LAST SELECT * FROM t3;
+ERROR HY000: 'test.t4' is not BASE TABLE
+SELECT * FROM t4;
+ERROR HY000: Table 't4' was not locked with LOCK TABLES
+UNLOCK TABLES;
+CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
+INSERT_METHOD=LAST;
+INSERT INTO t4 SELECT * FROM t3;
+# Alter temporary MERGE table.
+ALTER TABLE t4 UNION=(t1);
+LOCK TABLES t4 WRITE;
+# Alter temporary MERGE table under LOCk tables.
+ALTER TABLE t4 UNION=(t1,t2);
+UNLOCK TABLES;
+# MERGE table and function.
+CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3);
+SELECT * FROM t4 WHERE c1 < f1();
+c1
+3
+DROP FUNCTION f1;
+DROP TABLE t4, t3, t2, t1;
 CREATE TABLE t1 (
 fileset_id tinyint(3) unsigned NOT NULL default '0',
 file_code varchar(32) NOT NULL default '',
@@ -650,11 +696,11 @@ create table t2 (a int);
 insert into t1 values (0);
 insert into t2 values (1);
 create table t3 engine=merge union=(t1, t2) select * from t1;
-ERROR HY000: You can't specify target table 't1' for update in FROM clause
+ERROR HY000: 'test.t3' is not BASE TABLE
 create table t3 engine=merge union=(t1, t2) select * from t2;
-ERROR HY000: You can't specify target table 't2' for update in FROM clause
+ERROR HY000: 'test.t3' is not BASE TABLE
 create table t3 engine=merge union=(t1, t2) select (select max(a) from t2);
-ERROR HY000: You can't specify target table 't2' for update in FROM clause
+ERROR HY000: 'test.t3' is not BASE TABLE
 drop table t1, t2;
 create table t1 (
 a double(14,4),
@@ -784,7 +830,7 @@ ERROR HY000: Unable to open underlying table which is differently defined or of
 DROP TABLE t1, t2;
 CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3);
 SELECT * FROM t2;
-ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ERROR 42S02: Table 'test.t3' doesn't exist
 DROP TABLE t2;
 CREATE TABLE t1(a INT, b TEXT);
 CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1);
@@ -849,20 +895,17 @@ drop table t2;
 drop table t1;
 CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2);
 SELECT * FROM tm1;
-ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ERROR 42S02: Table 'test.t1' doesn't exist
 CHECK TABLE tm1;
 Table	Op	Msg_type	Msg_text
-test.tm1	check	Error	Table 'test.t1' is differently defined or of non-MyISAM type or doesn't exist
-test.tm1	check	Error	Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
-test.tm1	check	Error	Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+test.tm1	check	Error	Table 'test.t1' doesn't exist
 test.tm1	check	error	Corrupt
 CREATE TABLE t1(a INT);
 SELECT * FROM tm1;
-ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ERROR 42S02: Table 'test.t2' doesn't exist
 CHECK TABLE tm1;
 Table	Op	Msg_type	Msg_text
-test.tm1	check	Error	Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist
-test.tm1	check	Error	Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+test.tm1	check	Error	Table 'test.t2' doesn't exist
 test.tm1	check	error	Corrupt
 CREATE TABLE t2(a BLOB);
 SELECT * FROM tm1;
@@ -885,3 +928,1026 @@ CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
 ERROR HY000: You can't specify target table 't1' for update in FROM clause
 DROP TABLE t1, t2;
 End of 5.0 tests
+create table t1 (c1 int, index(c1));
+create table t2 (c1 int, index(c1)) engine=merge union=(t1);
+insert into t1 values (1);
+flush tables;
+select * from t2;
+c1
+1
+flush tables;
+truncate table t1;
+insert into t1 values (1);
+flush tables;
+select * from t2;
+c1
+1
+truncate table t1;
+insert into t1 values (1);
+drop table t1,t2;
+#
+# Extra tests for TRUNCATE.
+#
+# Truncate MERGE table.
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+CREATE TABLE t2 (c1 INT, INDEX(c1));
+CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t3;
+c1
+1
+2
+TRUNCATE TABLE t3;
+SELECT * FROM t3;
+c1
+#
+# Truncate child table.
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+TRUNCATE TABLE t1;
+SELECT * FROM t3;
+c1
+2
+#
+# Truncate MERGE table under locked tables.
+LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE;
+INSERT INTO t1 VALUES (1);
+TRUNCATE TABLE t3;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+SELECT * FROM t3;
+c1
+1
+2
+#
+# Truncate child table under locked tables.
+TRUNCATE TABLE t1;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+SELECT * FROM t3;
+c1
+1
+2
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3;
+#
+# Truncate temporary MERGE table.
+CREATE TEMPORARY TABLE t1 (c1 INT, INDEX(c1));
+CREATE TEMPORARY TABLE t2 (c1 INT, INDEX(c1));
+CREATE TEMPORARY TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t3;
+c1
+1
+2
+TRUNCATE TABLE t3;
+SELECT * FROM t3;
+c1
+#
+# Truncate temporary child table.
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+TRUNCATE TABLE t1;
+SELECT * FROM t3;
+c1
+2
+#
+# Truncate temporary MERGE table under locked tables.
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t4 (c1 INT, INDEX(c1));
+LOCK TABLE t4 WRITE;
+TRUNCATE TABLE t3;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+SELECT * FROM t3;
+c1
+1
+2
+#
+# Truncate temporary child table under locked tables.
+TRUNCATE TABLE t1;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+SELECT * FROM t3;
+c1
+1
+2
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4;
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
+REPAIR TABLE t1;
+INSERT INTO t2 VALUES (1);
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+DROP TABLE t1, t2;
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
+LOCK TABLE t1 WRITE;
+INSERT INTO t2 VALUES (1);
+REPAIR TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+UNLOCK TABLES;
+DROP TABLE t1, t2;
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+LOCK TABLE t1 WRITE;
+INSERT INTO t1 VALUES (1);
+FLUSH TABLES;
+FLUSH TABLES;
+SELECT * FROM t1;
+c1
+UNLOCK TABLES;
+DROP TABLE t1;
+#
+# Extra tests for Bug#26379 - Combination of FLUSH TABLE and
+#                             REPAIR TABLE corrupts a MERGE table
+#
+# CREATE ... SELECT is disabled for MERGE tables.
+#
+CREATE TABLE t1(c1 INT);
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
+CREATE TABLE t3 ENGINE=MRG_MYISAM INSERT_METHOD=LAST SELECT * FROM t2;
+ERROR HY000: Table 't3' is read only
+SHOW CREATE TABLE t3;
+ERROR 42S02: Table 'test.t3' doesn't exist
+CREATE TABLE t3 ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST
+SELECT * FROM t2;
+ERROR HY000: 'test.t3' is not BASE TABLE
+SHOW CREATE TABLE t3;
+ERROR 42S02: Table 'test.t3' doesn't exist
+DROP TABLE t1, t2;
+#
+# CREATE ... LIKE
+#
+# 1. Create like.
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TABLE t3 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (3);
+CREATE TABLE t4 LIKE t3;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `c1` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=()
+INSERT INTO t4 VALUES (4);
+ERROR HY000: Table 't4' is read only
+DROP TABLE t4;
+#
+# 1. Create like with locked tables.
+LOCK TABLES t3 WRITE, t2 WRITE, t1 WRITE;
+CREATE TABLE t4 LIKE t3;
+SHOW CREATE TABLE t4;
+ERROR HY000: Table 't4' was not locked with LOCK TABLES
+INSERT INTO t4 VALUES (4);
+ERROR HY000: Table 't4' was not locked with LOCK TABLES
+UNLOCK TABLES;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `c1` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=()
+INSERT INTO t4 VALUES (4);
+ERROR HY000: Table 't4' is read only
+DROP TABLE t4;
+#
+# Rename child.
+#
+# 1. Normal rename of non-MERGE table.
+CREATE TABLE t4 (c1 INT);
+INSERT INTO t4 VALUES (4);
+SELECT * FROM t4 ORDER BY c1;
+c1
+4
+RENAME TABLE t4 TO t5;
+SELECT * FROM t5 ORDER BY c1;
+c1
+4
+RENAME TABLE t5 TO t4;
+SELECT * FROM t4 ORDER BY c1;
+c1
+4
+DROP TABLE t4;
+#
+# 2. Normal rename.
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+RENAME TABLE t2 TO t5;
+SELECT * FROM t3 ORDER BY c1;
+ERROR 42S02: Table 'test.t2' doesn't exist
+RENAME TABLE t5 TO t2;
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+#
+# 3. Normal rename with locked tables.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+RENAME TABLE t2 TO t5;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+RENAME TABLE t5 TO t2;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+UNLOCK TABLES;
+#
+# 4. Alter table rename.
+ALTER TABLE t2 RENAME TO t5;
+SELECT * FROM t3 ORDER BY c1;
+ERROR 42S02: Table 'test.t2' doesn't exist
+ALTER TABLE t5 RENAME TO t2;
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+#
+# 5. Alter table rename with locked tables.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
+ALTER TABLE t2 RENAME TO t5;
+SELECT * FROM t3 ORDER BY c1;
+ERROR HY000: Table 't3' was not locked with LOCK TABLES
+ALTER TABLE t5 RENAME TO t2;
+ERROR HY000: Table 't5' was not locked with LOCK TABLES
+UNLOCK TABLES;
+ALTER TABLE t5 RENAME TO t2;
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+#
+# Rename parent.
+#
+# 1. Normal rename with locked tables.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+RENAME TABLE t3 TO t5;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+RENAME TABLE t5 TO t3;
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+#
+# 5. Alter table rename with locked tables.
+ALTER TABLE t3 RENAME TO t5;
+SELECT * FROM t5 ORDER BY c1;
+ERROR HY000: Table 't5' was not locked with LOCK TABLES
+ALTER TABLE t5 RENAME TO t3;
+ERROR HY000: Table 't5' was not locked with LOCK TABLES
+UNLOCK TABLES;
+ALTER TABLE t5 RENAME TO t3;
+SELECT * FROM t3 ORDER BY c1;
+c1
+1
+2
+3
+DROP TABLE t1, t2, t3;
+#
+# Drop locked tables.
+#
+# 1. Drop parent.
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
+INSERT_METHOD=LAST;
+LOCK TABLES t1 WRITE, t2 WRITE;
+INSERT INTO t1 VALUES (1);
+DROP TABLE t2;
+SELECT * FROM t2;
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+SELECT * FROM t1;
+c1
+1
+UNLOCK TABLES;
+# 2. Drop child.
+CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
+INSERT_METHOD=LAST;
+LOCK TABLES t1 WRITE, t2 WRITE;
+INSERT INTO t1 VALUES (1);
+DROP TABLE t1;
+SELECT * FROM t2;
+ERROR 42S02: Table 'test.t1' doesn't exist
+SELECT * FROM t1;
+ERROR 42S02: Table 'test.t1' doesn't exist
+UNLOCK TABLES;
+DROP TABLE t2;
+#
+# ALTER TABLE. Change child list.
+#
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+CREATE TABLE t2 (c1 INT, INDEX(c1));
+CREATE TABLE t3 (c1 INT, INDEX(c1));
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (3);
+CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t3,t2)
+INSERT_METHOD=LAST;
+# Shrink child list.
+ALTER TABLE t4 UNION=(t3);
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `c1` int(11) DEFAULT NULL,
+  KEY `c1` (`c1`)
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t3`)
+SELECT * FROM t4 ORDER BY c1;
+c1
+3
+# Extend child list.
+ALTER TABLE t4 UNION=(t3,t2);
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `c1` int(11) DEFAULT NULL,
+  KEY `c1` (`c1`)
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t3`,`t2`)
+SELECT * FROM t4 ORDER BY c1;
+c1
+2
+3
+#
+# ALTER TABLE under LOCK TABLES. Change child list.
+#
+LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE;
+# Shrink child list.
+ALTER TABLE t4 UNION=(t3);
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+# Extend child list within locked tables.
+ALTER TABLE t4 UNION=(t3,t2);
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+# Extend child list beyond locked tables.
+ALTER TABLE t4 UNION=(t3,t2,t1);
+ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `c1` int(11) DEFAULT NULL,
+  KEY `c1` (`c1`)
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t3`,`t2`)
+SELECT * FROM t4 ORDER BY c1;
+c1
+2
+3
+UNLOCK TABLES;
+DROP TABLE t4;
+#
+# ALTER TABLE under LOCK TABLES. Grave change, table re-creation.
+#
+CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
+INSERT_METHOD=LAST;
+# Lock parent first and then children.
+LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
+ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+UNLOCK TABLES;
+# Lock children first and then parent.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
+ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+UNLOCK TABLES;
+# Lock parent between children.
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4;
+#
+# ALTER TABLE under LOCK TABLES. Simple change, no re-creation.
+#
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TABLE t3 (c1 INT);
+CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
+INSERT_METHOD=LAST;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (3);
+# Lock parent first and then children.
+LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
+ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+UNLOCK TABLES;
+# Lock children first and then parent.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
+ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+UNLOCK TABLES;
+# Lock parent between children.
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+UNLOCK TABLES;
+#
+# FLUSH TABLE under LOCK TABLES.
+#
+# Lock parent first and then children.
+LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
+FLUSH TABLE t4;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+FLUSH TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+FLUSH TABLES;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+UNLOCK TABLES;
+# Lock children first and then parent.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
+FLUSH TABLE t4;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+FLUSH TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+FLUSH TABLES;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+UNLOCK TABLES;
+# Lock parent between children.
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+FLUSH TABLE t4;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+FLUSH TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+FLUSH TABLES;
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+UNLOCK TABLES;
+#
+# Triggers
+#
+# Trigger on parent
+DELETE FROM t4 WHERE c1 = 4;
+CREATE TRIGGER t4_ai AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
+SET @a=0;
+INSERT INTO t4 VALUES (4);
+SELECT @a;
+@a
+1
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+DROP TRIGGER t4_ai;
+# Trigger on parent under LOCK TABLES
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+CREATE TRIGGER t4_ai AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
+SET @a=0;
+INSERT INTO t4 VALUES (4);
+SELECT @a;
+@a
+1
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+DROP TRIGGER t4_ai;
+UNLOCK TABLES;
+#
+# Trigger on child
+DELETE FROM t4 WHERE c1 = 4;
+CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
+SET @a=0;
+INSERT INTO t4 VALUES (4);
+SELECT @a;
+@a
+0
+INSERT INTO t3 VALUES (33);
+SELECT @a;
+@a
+1
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+33
+DROP TRIGGER t3_ai;
+# Trigger on child under LOCK TABLES
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
+SET @a=0;
+INSERT INTO t4 VALUES (4);
+SELECT @a;
+@a
+0
+INSERT INTO t3 VALUES (33);
+SELECT @a;
+@a
+1
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+33
+33
+DELETE FROM t4 WHERE c1 = 33;
+DROP TRIGGER t3_ai;
+#
+# Trigger with table use on child
+DELETE FROM t4 WHERE c1 = 4;
+CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
+INSERT INTO t4 VALUES (4);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+INSERT INTO t3 VALUES (33);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+22
+33
+DELETE FROM t4 WHERE c1 = 22;
+DELETE FROM t4 WHERE c1 = 33;
+DROP TRIGGER t3_ai;
+# Trigger with table use on child under LOCK TABLES
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
+INSERT INTO t4 VALUES (4);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+INSERT INTO t3 VALUES (33);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+22
+33
+DROP TRIGGER t3_ai;
+DELETE FROM t4 WHERE c1 = 22;
+DELETE FROM t4 WHERE c1 = 33;
+UNLOCK TABLES;
+#
+# Repair
+#
+REPAIR TABLE t4;
+Table	Op	Msg_type	Msg_text
+test.t4	repair	note	The storage engine for the table doesn't support repair
+REPAIR TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	repair	status	OK
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+REPAIR TABLE t4;
+Table	Op	Msg_type	Msg_text
+test.t4	repair	note	The storage engine for the table doesn't support repair
+REPAIR TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	repair	status	OK
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+UNLOCK TABLES;
+#
+# Optimize
+#
+OPTIMIZE TABLE t4;
+Table	Op	Msg_type	Msg_text
+test.t4	optimize	note	The storage engine for the table doesn't support optimize
+OPTIMIZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	optimize	status	OK
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+OPTIMIZE TABLE t4;
+Table	Op	Msg_type	Msg_text
+test.t4	optimize	note	The storage engine for the table doesn't support optimize
+OPTIMIZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	optimize	status	Table is already up to date
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+UNLOCK TABLES;
+#
+# Checksum
+#
+CHECKSUM TABLE t4;
+Table	Checksum
+test.t4	46622073
+CHECKSUM TABLE t2;
+Table	Checksum
+test.t2	3700403066
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+CHECKSUM TABLE t4;
+Table	Checksum
+test.t4	46622073
+CHECKSUM TABLE t2;
+Table	Checksum
+test.t2	3700403066
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+UNLOCK TABLES;
+#
+# Insert delayed
+#
+INSERT DELAYED INTO t4 VALUES(44);
+DELETE FROM t4 WHERE c1 = 44;
+INSERT DELAYED INTO t3 VALUES(33);
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+33
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+INSERT DELAYED INTO t4 VALUES(444);
+Got one of the listed errors
+INSERT DELAYED INTO t3 VALUES(333);
+Got one of the listed errors
+SELECT * FROM t4 ORDER BY c1;
+c1
+1
+2
+3
+4
+4
+33
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4;
+#
+# Recursive inclusion of merge tables in their union clauses.
+#
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
+INSERT_METHOD=LAST;
+CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t2,t1)
+INSERT_METHOD=LAST;
+ALTER TABLE t2 UNION=(t3,t1);
+SELECT * FROM t2;
+ERROR HY000: Table 't3' is differently defined or of non-MyISAM type or doesn't exist
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+CREATE TABLE t2 (c1 INT) ENGINE= MyISAM;
+CREATE TABLE t3 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1, t2);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t3;
+c1
+1
+2
+TRUNCATE TABLE t1;
+SELECT * FROM t3;
+c1
+2
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);
+SET @rnd_max= 2147483647;
+SET @rnd= RAND();
+SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+SET @id_rev= @rnd_max - @id;
+SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
+INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
+SET @rnd= RAND();
+SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+SET @id_rev= @rnd_max - @id;
+SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
+INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
+SET @rnd= RAND();
+SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+SET @id_rev= @rnd_max - @id;
+SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
+INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
+SET @rnd= RAND();
+SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+SET @id_rev= @rnd_max - @id;
+SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
+INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
+SET @rnd= RAND();
+SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+SET @id_rev= @rnd_max - @id;
+SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
+INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
+SET @rnd= RAND();
+SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+SET @id_rev= @rnd_max - @id;
+SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
+INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
+SET @rnd= RAND();
+SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+SET @id_rev= @rnd_max - @id;
+SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
+INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
+SET @rnd= RAND();
+SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+SET @id_rev= @rnd_max - @id;
+SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
+INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
+SET @rnd= RAND();
+SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+SET @id_rev= @rnd_max - @id;
+SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
+INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
+SET @rnd= RAND();
+SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+SET @id_rev= @rnd_max - @id;
+SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
+INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
+set @@read_buffer_size=2*1024*1024;
+CREATE TABLE t2 SELECT * FROM t1;
+INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
+INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
+INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
+INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
+INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
+CREATE TABLE t3 (id INTEGER, grp TINYINT, id_rev INTEGER)
+ENGINE= MRG_MYISAM UNION= (t1, t2);
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+130
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+80
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+210
+SELECT COUNT(DISTINCT a1.id) FROM t3 AS a1, t3 AS a2
+WHERE a1.id = a2.id GROUP BY a2.grp;
+TRUNCATE TABLE t1;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+80
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+80
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
+CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
+INSERT INTO t2 VALUES (1);
+SELECT * FROM t2;
+c1
+1
+LOCK TABLES t2 WRITE, t1 WRITE;
+FLUSH TABLES;
+REPAIR TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+REPAIR TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+UNLOCK TABLES;
+CHECK TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+LOCK TABLES t2 WRITE, t1 WRITE;
+SELECT * FROM t2;
+c1
+1
+LOCK TABLES t2 WRITE, t1 WRITE;
+REPAIR TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+REPAIR TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+UNLOCK TABLES;
+CHECK TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+DROP TABLE t1, t2;
+CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
+CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
+LOCK TABLES t1 WRITE, m1 WRITE;
+FLUSH TABLE t1;
+UNLOCK TABLES;
+DROP TABLE m1, t1;
+CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
+CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
+LOCK TABLES m1 WRITE, t1 WRITE;
+FLUSH TABLE t1;
+UNLOCK TABLES;
+DROP TABLE m1, t1;
+CREATE TABLE t1 (c1 INT, c2 INT) ENGINE= MyISAM;
+CREATE TABLE t2 (c1 INT, c2 INT) ENGINE= MyISAM;
+CREATE TABLE t3 (c1 INT, c2 INT) ENGINE= MRG_MYISAM UNION(t1, t2);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t2 VALUES (2, 2);
+SELECT * FROM t3;
+c1	c2
+1	1
+2	2
+ALTER TABLE t1 ENGINE= MEMORY;
+INSERT INTO t1 VALUES (0, 0);
+SELECT * FROM t3;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (c1 INT, KEY(c1));
+CREATE TABLE t2 (c1 INT, KEY(c1)) ENGINE=MRG_MYISAM UNION=(t1)
+INSERT_METHOD=FIRST;
+LOCK TABLE t1 WRITE, t2 WRITE;
+FLUSH TABLES t2, t1;
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	Table is already up to date
+FLUSH TABLES t1;
+UNLOCK TABLES;
+FLUSH TABLES;
+INSERT INTO t1 VALUES (1);
+LOCK TABLE t1 WRITE, t2 WRITE;
+FLUSH TABLES t2, t1;
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+FLUSH TABLES t1;
+UNLOCK TABLES;
+DROP TABLE t1, t2;
+CREATE TABLE t1 (ID INT) ENGINE=MYISAM;
+CREATE TABLE m1 (ID INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST;
+INSERT INTO t1 VALUES ();
+INSERT INTO m1 VALUES ();
+LOCK TABLE t1 WRITE, m1 WRITE;
+FLUSH TABLES m1, t1;
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+FLUSH TABLES m1, t1;
+UNLOCK TABLES;
+DROP TABLE t1, m1;
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 50fbe89db52..353d66b1ad5 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -606,24 +606,6 @@ select count(*) from t1 where a is null;
 count(*)
 2
 drop table t1;
-create table t1 (c1 int, index(c1));
-create table t2 (c1 int, index(c1)) engine=merge union=(t1);
-insert into t1 values (1);
-flush tables;
-select * from t2;
-c1
-1
-flush tables;
-truncate table t1;
-insert into t1 values (1);
-flush tables;
-select * from t2;
-c1
-1
-truncate table t1;
-ERROR HY000: MyISAM table 't1' is in use (most likely by a MERGE table). Try FLUSH TABLES.
-insert into t1 values (1);
-drop table t1,t2;
 create table t1 (c1 int, c2 varchar(4) not null default '',
 key(c2(3))) default charset=utf8;
 insert into t1 values (1,'A'), (2, 'B'), (3, 'A');
diff --git a/mysql-test/suite/binlog/r/binlog_stm_blackhole.result b/mysql-test/suite/binlog/r/binlog_stm_blackhole.result
index a1c83ffc73d..a79642a9204 100644
--- a/mysql-test/suite/binlog/r/binlog_stm_blackhole.result
+++ b/mysql-test/suite/binlog/r/binlog_stm_blackhole.result
@@ -124,6 +124,7 @@ master-bin.000001	#	Query	#	#	use `test`; replace into t1 select * from t3
 drop table t1,t2,t3;
 CREATE TABLE t1(a INT) ENGINE=BLACKHOLE;
 INSERT DELAYED INTO t1 VALUES(1);
+ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
 DROP TABLE t1;
 CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE;
 DELETE FROM t1 WHERE a=10;
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 023e55ea418..45ee4c1c88d 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -483,7 +483,7 @@ drop table t1,t2;
 create table t1 (a int);
 --error 1093
 create table t1 select * from t1;
---error 1093
+--error ER_WRONG_OBJECT
 create table t2 union = (t1) select * from t1;
 flush tables with read lock;
 unlock tables;
diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test
index 03b4f8b3013..a0e25509ba2 100644
--- a/mysql-test/t/delayed.test
+++ b/mysql-test/t/delayed.test
@@ -244,14 +244,6 @@ SELECT HEX(a) FROM t1;
 DROP TABLE t1;
 
 #
-# Bug#26464 - insert delayed + update + merge = corruption
-#
-CREATE TABLE t1(c1 INT) ENGINE=MyISAM;
-CREATE TABLE t2(c1 INT) ENGINE=MERGE UNION=(t1);
---error 1031
-INSERT DELAYED INTO t2 VALUES(1);
-DROP TABLE t1, t2;
-#
 # Bug#27358 INSERT DELAYED does not honour SQL_MODE of the client
 #
 --disable_warnings
diff --git a/mysql-test/t/merge-big.test b/mysql-test/t/merge-big.test
new file mode 100644
index 00000000000..eddcbb59ed4
--- /dev/null
+++ b/mysql-test/t/merge-big.test
@@ -0,0 +1,150 @@
+#
+# Test of MERGE tables with multisession and many waits.
+#
+# This test takes rather long time so let us run it only in --big-test mode
+--source include/big_test.inc
+# We are using some debug-only features in this test
+--source include/have_debug.inc
+
+--disable_warnings
+drop table if exists t1,t2,t3,t4,t5,t6;
+--enable_warnings
+
+--echo #
+--echo # Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE
+--echo #             corrupts a MERGE table
+--echo # Problem #3
+--echo #
+# Two FLUSH TABLES within a LOCK TABLES segment could invalidate the lock.
+# This did *not* require a MERGE table.
+#
+# To increase reproducibility it was necessary to enter a sleep of 2
+# seconds at the end of wait_for_tables() after unlock of LOCK_open. In
+# 5.0 and 5.1 the sleep must be inserted in open_and_lock_tables() after
+# open_tables() instead. wait_for_tables() is not used in this case. The
+# problem was that FLUSH TABLES releases LOCK_open while having unlocked
+# and closed all tables. When this happened while a thread was in the
+# loop in mysql_lock_tables() right after wait_for_tables()
+# (open_tables()) and before retrying to lock, the thread got the lock.
+# And it did not notice that the table needed a refresh after the
+# [re-]open. So it executed its statement on the table.
+#
+# The first FLUSH TABLES kicked the INSERT out of thr_multi_lock() and
+# let it wait in wait_for_tables() (open_table()). The second FLUSH
+# TABLES must happen while the INSERT was on its way from
+# wait_for_tables() (open_table()) to the next call of thr_multi_lock().
+# This needed to be supported by a sleep to make it repeatable.
+#
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+LOCK TABLE t1 WRITE;
+#SELECT NOW();
+    --echo # connection con1
+    connect (con1,localhost,root,,);
+    let $con1_id= `SELECT CONNECTION_ID()`;
+    SET SESSION debug="+d,sleep_open_and_lock_after_open";
+    send INSERT INTO t1 VALUES (1);
+--echo # connection default
+connection default;
+--echo # Let INSERT go into thr_multi_lock().
+let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+    WHERE ID = $con1_id AND STATE = 'Locked';
+--source include/wait_condition.inc
+#SELECT NOW();
+--echo # Kick INSERT out of thr_multi_lock().
+FLUSH TABLES;
+#SELECT NOW();
+--echo # Let INSERT go through open_tables() where it sleeps.
+let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+    WHERE ID = $con1_id AND STATE = 'DBUG sleep';
+--source include/wait_condition.inc
+#SELECT NOW();
+--echo # Unlock and close table and wait for con1 to close too.
+FLUSH TABLES;
+#SELECT NOW();
+--echo # This should give no result.
+SELECT * FROM t1;
+#SELECT NOW();
+UNLOCK TABLES;
+    --echo # connection con1
+    connection con1;
+    reap;
+    SET SESSION debug="-d,sleep_open_and_lock_after_open";
+    disconnect con1;
+--echo # connection default
+connection default;
+DROP TABLE t1;
+
+--echo #
+--echo # Extra tests for Bug#26379 - Combination of FLUSH TABLE and
+--echo #                             REPAIR TABLE corrupts a MERGE table
+--echo #
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TABLE t3 (c1 INT);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (3);
+--echo #
+--echo # CREATE ... SELECT
+--echo # try to access parent from another thread.
+--echo #
+#SELECT NOW();
+    --echo # connection con1
+    connect (con1,localhost,root,,);
+    let $con1_id= `SELECT CONNECTION_ID()`;
+    SET SESSION debug="+d,sleep_create_select_before_lock";
+    send CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
+         INSERT_METHOD=FIRST SELECT * FROM t3;
+--echo # connection default
+connection default;
+# wait for the other query to start executing
+let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+    WHERE ID = $con1_id AND STATE = 'DBUG sleep';
+--source include/wait_condition.inc
+#SELECT NOW();
+--echo # Now try to access the parent.
+--echo # If 3 is in table, SELECT had to wait.
+SELECT * FROM t4 ORDER BY c1;
+#SELECT NOW();
+    --echo # connection con1
+    connection con1;
+    reap;
+    #SELECT NOW();
+    SET SESSION debug="-d,sleep_create_select_before_lock";
+    disconnect con1;
+--echo # connection default
+connection default;
+--echo # Cleanup for next test.
+DROP TABLE t4;
+DELETE FROM t1 WHERE c1 != 1;
+--echo #
+--echo # CREATE ... SELECT
+--echo # try to access child from another thread.
+--echo #
+#SELECT NOW();
+    --echo # connection con1
+    connect (con1,localhost,root,,);
+    let $con1_id= `SELECT CONNECTION_ID()`;
+    SET SESSION debug="+d,sleep_create_select_before_lock";
+    send CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
+         INSERT_METHOD=FIRST SELECT * FROM t3;
+--echo # connection default
+connection default;
+# wait for the other query to start executing
+let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+    WHERE ID = $con1_id AND STATE = 'DBUG sleep';
+--source include/wait_condition.inc
+#SELECT NOW();
+--echo # Now try to access a child.
+--echo # If 3 is in table, SELECT had to wait.
+SELECT * FROM t1 ORDER BY c1;
+#SELECT NOW();
+    --echo # connection con1
+    connection con1;
+    reap;
+    #SELECT NOW();
+    SET SESSION debug="-d,sleep_create_select_before_lock";
+    disconnect con1;
+--echo # connection default
+connection default;
+DROP TABLE t1, t2, t3, t4;
diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test
index a50588b1e78..cd4e732b954 100644
--- a/mysql-test/t/merge.test
+++ b/mysql-test/t/merge.test
@@ -221,6 +221,7 @@ create table t2 (a int not null);
 insert into t1 values (1);
 insert into t2 values (2);
 create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
+--error ER_WRONG_MRG_TABLE
 select * from t3;
 create temporary table t4 (a int not null);
 create temporary table t5 (a int not null);
@@ -229,6 +230,58 @@ insert into t5 values (2);
 create temporary table t6 (a int not null) ENGINE=MERGE UNION=(t4,t5);
 select * from t6;
 drop table t6, t3, t1, t2, t4, t5;
+#
+# Bug#19627 - temporary merge table locking
+# MERGE table and its children must match in temporary type.
+# Forbid temporary merge on non-temporary children: shown above.
+# Forbid non-temporary merge on temporary children:
+create temporary table t1 (a int not null);
+create temporary table t2 (a int not null);
+insert into t1 values (1);
+insert into t2 values (2);
+create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
+--error ER_WRONG_MRG_TABLE
+select * from t3;
+drop table t3, t2, t1;
+# Forbid children mismatch in temporary:
+create table t1 (a int not null);
+create temporary table t2 (a int not null);
+insert into t1 values (1);
+insert into t2 values (2);
+create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
+--error ER_WRONG_MRG_TABLE
+select * from t3;
+drop table t3;
+create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
+--error ER_WRONG_MRG_TABLE
+select * from t3;
+drop table t3, t2, t1;
+--echo # CREATE...SELECT is not implemented for MERGE tables.
+CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL);
+CREATE TEMPORARY TABLE t2 (c1 INT NOT NULL);
+CREATE TABLE t3 (c1 INT NOT NULL);
+INSERT INTO t3 VALUES (3), (33);
+LOCK TABLES t3 READ;
+--error ER_WRONG_OBJECT
+CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
+  INSERT_METHOD=LAST SELECT * FROM t3;
+--error ER_TABLE_NOT_LOCKED
+SELECT * FROM t4;
+UNLOCK TABLES;
+CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+INSERT INTO t4 SELECT * FROM t3;
+--echo # Alter temporary MERGE table.
+ALTER TABLE t4 UNION=(t1);
+LOCK TABLES t4 WRITE;
+--echo # Alter temporary MERGE table under LOCk tables.
+ALTER TABLE t4 UNION=(t1,t2);
+UNLOCK TABLES;
+--echo # MERGE table and function.
+CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3);
+SELECT * FROM t4 WHERE c1 < f1();
+DROP FUNCTION f1;
+DROP TABLE t4, t3, t2, t1;
 
 #
 # testing merge::records_in_range and optimizer
@@ -284,11 +337,11 @@ create table t1 (a int);
 create table t2 (a int);
 insert into t1 values (0);
 insert into t2 values (1);
---error 1093
+--error ER_WRONG_OBJECT
 create table t3 engine=merge union=(t1, t2) select * from t1;
---error 1093
+--error ER_WRONG_OBJECT
 create table t3 engine=merge union=(t1, t2) select * from t2;
---error 1093
+--error ER_WRONG_OBJECT
 create table t3 engine=merge union=(t1, t2) select (select max(a) from t2);
 drop table t1, t2;
 
@@ -403,7 +456,7 @@ CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t1);
 SELECT * FROM t2;
 DROP TABLE t1, t2;
 CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3);
---error 1168
+--error ER_NO_SUCH_TABLE
 SELECT * FROM t2;
 DROP TABLE t2;
 
@@ -495,11 +548,11 @@ drop table t1;
 #             CREATE TABLE fails
 #
 CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2);
---error 1168
+--error ER_NO_SUCH_TABLE
 SELECT * FROM tm1;
 CHECK TABLE tm1;
 CREATE TABLE t1(a INT);
---error 1168
+--error ER_NO_SUCH_TABLE
 SELECT * FROM tm1;
 CHECK TABLE tm1;
 CREATE TABLE t2(a BLOB);
@@ -526,3 +579,785 @@ CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
 DROP TABLE t1, t2;
 
 --echo End of 5.0 tests
+
+#
+# Bug #8306: TRUNCATE leads to index corruption 
+#
+create table t1 (c1 int, index(c1));
+create table t2 (c1 int, index(c1)) engine=merge union=(t1);
+insert into t1 values (1);
+# Close all tables.
+flush tables;
+# Open t2 and (implicitly) t1.
+select * from t2;
+# Truncate after flush works (unless another threads reopens t2 in between).
+flush tables;
+truncate table t1;
+insert into t1 values (1);
+# Close all tables.
+flush tables;
+# Open t2 and (implicitly) t1.
+select * from t2;
+# Truncate t1, wich was not recognized as open without the bugfix.
+# After fix for Bug#8306 and before fix for Bug#26379,
+# it should fail with a table-in-use error message, otherwise succeed.
+truncate table t1;
+# The insert used to fail on the crashed table.
+insert into t1 values (1);
+drop table t1,t2;
+--echo #
+--echo # Extra tests for TRUNCATE.
+--echo #
+--echo # Truncate MERGE table.
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+CREATE TABLE t2 (c1 INT, INDEX(c1));
+CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t3;
+TRUNCATE TABLE t3;
+SELECT * FROM t3;
+--echo #
+--echo # Truncate child table.
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+TRUNCATE TABLE t1;
+SELECT * FROM t3;
+--echo #
+--echo # Truncate MERGE table under locked tables.
+LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE;
+INSERT INTO t1 VALUES (1);
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+TRUNCATE TABLE t3;
+SELECT * FROM t3;
+--echo #
+--echo # Truncate child table under locked tables.
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+TRUNCATE TABLE t1;
+SELECT * FROM t3;
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3;
+--echo #
+--echo # Truncate temporary MERGE table.
+CREATE TEMPORARY TABLE t1 (c1 INT, INDEX(c1));
+CREATE TEMPORARY TABLE t2 (c1 INT, INDEX(c1));
+CREATE TEMPORARY TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t3;
+TRUNCATE TABLE t3;
+SELECT * FROM t3;
+--echo #
+--echo # Truncate temporary child table.
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+TRUNCATE TABLE t1;
+SELECT * FROM t3;
+--echo #
+--echo # Truncate temporary MERGE table under locked tables.
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t4 (c1 INT, INDEX(c1));
+LOCK TABLE t4 WRITE;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+TRUNCATE TABLE t3;
+SELECT * FROM t3;
+--echo #
+--echo # Truncate temporary child table under locked tables.
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+TRUNCATE TABLE t1;
+SELECT * FROM t3;
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4;
+
+#
+# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
+# Preparation
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+connection default;
+#
+# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
+# Problem #1
+# A thread trying to lock a MERGE table performed busy waiting while
+# REPAIR TABLE or a similar table administration task was ongoing on one or
+# more of its MyISAM tables.
+# To allow for observability it was necessary to enter a multi-second sleep
+# in mysql_admin_table() after remove_table_from_cache(), which comes after
+# mysql_abort_lock(). The sleep faked a long running operation. One could
+# watch a high CPU load during the sleep time.
+# The problem was that mysql_abort_lock() upgrades the write lock to
+# TL_WRITE_ONLY. This lock type persisted until the final unlock at the end
+# of the administration task. The effect of TL_WRITE_ONLY is to reject any
+# attempt to lock the table. The trying thread must close the table and wait
+# until it is no longer used. Unfortunately there is no way to detect that
+# one of the MyISAM tables of a MERGE table is in use. When trying to lock
+# the MERGE table, all MyISAM tables are locked. If one fails on
+# TL_WRITE_ONLY, all locks are aborted and wait_for_tables() is entered.
+# But this doesn't see the MERGE table as used, so it seems appropriate to
+# retry a lock...
+#
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
+send REPAIR TABLE t1;
+    connection con1;
+    sleep 1; # let repair run into its sleep
+    INSERT INTO t2 VALUES (1);
+connection default;
+reap;
+DROP TABLE t1, t2;
+#
+# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
+# Problem #2
+# A thread trying to lock a MERGE table performed busy waiting until all
+# threads that did REPAIR TABLE or similar table administration tasks on
+# one or more of its MyISAM tables in LOCK TABLES segments did
+# UNLOCK TABLES.
+# The difference against problem #1 is that the busy waiting took place
+# *after* the administration task. It was terminated by UNLOCK TABLES only.
+#
+# This is the same test case as for
+# Bug#26867 - LOCK TABLES + REPAIR + merge table result in memory/cpu hogging
+#
+#
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
+LOCK TABLE t1 WRITE;
+    connection con1;
+    send INSERT INTO t2 VALUES (1);
+connection default;
+sleep 1; # Let INSERT go into thr_multi_lock().
+REPAIR TABLE t1;
+sleep 2; # con1 performs busy waiting during this sleep.
+UNLOCK TABLES;
+    connection con1;
+    reap;
+connection default;
+DROP TABLE t1, t2;
+#
+# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
+# Problem #3
+# Two FLUSH TABLES within a LOCK TABLES segment could invalidate the lock.
+# This did *not* require a MERGE table.
+# To increase reproducibility it was necessary to enter a sleep of 2 seconds
+# at the end of wait_for_tables() after unlock of LOCK_open. In 5.0 and 5.1
+# the sleep must be inserted in open_and_lock_tables() after open_tables()
+# instead. wait_for_tables() is not used in this case.
+# The problem was that FLUSH TABLES releases LOCK_open while having unlocked
+# and closed all tables. When this happened while a thread was in the loop in
+# mysql_lock_tables() right after wait_for_tables() and before retrying to
+# lock, the thread got the lock.  (Translate to similar code places in 5.0
+# and 5.1). And it did not notice that the table needed a refresh. So it
+# executed its statement on the table.
+# The first FLUSH TABLES kicked the INSERT out of thr_multi_lock() and let
+# it wait in wait_for_tables(). (open_table() in 5.0 and 5.1). The second
+# FLUSH TABLES must happen while the INSERT was on its way from
+# wait_for_tables() to the next call of thr_multi_lock(). This needed to be
+# supported by a sleep to make it repeatable.
+#
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+LOCK TABLE t1 WRITE;
+    connection con1;
+    send INSERT INTO t1 VALUES (1);
+connection default;
+sleep 1; # Let INSERT go into thr_multi_lock().
+FLUSH TABLES;
+sleep 1; # Let INSERT go through wait_for_tables() where it sleeps.
+FLUSH TABLES;
+# This should give no result. But it will with sleep(2) at the right place.
+SELECT * FROM t1;
+UNLOCK TABLES;
+    connection con1;
+    reap;
+connection default;
+DROP TABLE t1;
+#
+# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
+# Cleanup
+disconnect con1;
+disconnect con2;
+#
+--echo #
+--echo # Extra tests for Bug#26379 - Combination of FLUSH TABLE and
+--echo #                             REPAIR TABLE corrupts a MERGE table
+#
+--echo #
+--echo # CREATE ... SELECT is disabled for MERGE tables.
+--echo #
+CREATE TABLE t1(c1 INT);
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
+--error ER_OPEN_AS_READONLY
+CREATE TABLE t3 ENGINE=MRG_MYISAM INSERT_METHOD=LAST SELECT * FROM t2;
+--error ER_NO_SUCH_TABLE
+SHOW CREATE TABLE t3;
+--error ER_WRONG_OBJECT
+CREATE TABLE t3 ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST
+  SELECT * FROM t2;
+--error ER_NO_SUCH_TABLE
+SHOW CREATE TABLE t3;
+DROP TABLE t1, t2;
+#
+--echo #
+--echo # CREATE ... LIKE
+--echo #
+--echo # 1. Create like.
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TABLE t3 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (3);
+CREATE TABLE t4 LIKE t3;
+SHOW CREATE TABLE t4;
+--error ER_OPEN_AS_READONLY
+INSERT INTO t4 VALUES (4);
+DROP TABLE t4;
+--echo #
+--echo # 1. Create like with locked tables.
+LOCK TABLES t3 WRITE, t2 WRITE, t1 WRITE;
+CREATE TABLE t4 LIKE t3;
+--error ER_TABLE_NOT_LOCKED
+SHOW CREATE TABLE t4;
+--error ER_TABLE_NOT_LOCKED
+INSERT INTO t4 VALUES (4);
+UNLOCK TABLES;
+SHOW CREATE TABLE t4;
+--error ER_OPEN_AS_READONLY
+INSERT INTO t4 VALUES (4);
+DROP TABLE t4;
+#
+--echo #
+--echo # Rename child.
+--echo #
+--echo # 1. Normal rename of non-MERGE table.
+CREATE TABLE t4 (c1 INT);
+INSERT INTO t4 VALUES (4);
+SELECT * FROM t4 ORDER BY c1;
+RENAME TABLE t4 TO t5;
+SELECT * FROM t5 ORDER BY c1;
+RENAME TABLE t5 TO t4;
+SELECT * FROM t4 ORDER BY c1;
+DROP TABLE t4;
+--echo #
+--echo # 2. Normal rename.
+SELECT * FROM t3 ORDER BY c1;
+RENAME TABLE t2 TO t5;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM t3 ORDER BY c1;
+RENAME TABLE t5 TO t2;
+SELECT * FROM t3 ORDER BY c1;
+--echo #
+--echo # 3. Normal rename with locked tables.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
+SELECT * FROM t3 ORDER BY c1;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+RENAME TABLE t2 TO t5;
+SELECT * FROM t3 ORDER BY c1;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+RENAME TABLE t5 TO t2;
+SELECT * FROM t3 ORDER BY c1;
+UNLOCK TABLES;
+--echo #
+--echo # 4. Alter table rename.
+ALTER TABLE t2 RENAME TO t5;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM t3 ORDER BY c1;
+ALTER TABLE t5 RENAME TO t2;
+SELECT * FROM t3 ORDER BY c1;
+--echo #
+--echo # 5. Alter table rename with locked tables.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
+ALTER TABLE t2 RENAME TO t5;
+--error ER_TABLE_NOT_LOCKED
+SELECT * FROM t3 ORDER BY c1;
+--error ER_TABLE_NOT_LOCKED
+ALTER TABLE t5 RENAME TO t2;
+UNLOCK TABLES;
+ALTER TABLE t5 RENAME TO t2;
+SELECT * FROM t3 ORDER BY c1;
+#
+--echo #
+--echo # Rename parent.
+--echo #
+--echo # 1. Normal rename with locked tables.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
+SELECT * FROM t3 ORDER BY c1;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+RENAME TABLE t3 TO t5;
+SELECT * FROM t3 ORDER BY c1;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+RENAME TABLE t5 TO t3;
+SELECT * FROM t3 ORDER BY c1;
+--echo #
+--echo # 5. Alter table rename with locked tables.
+ALTER TABLE t3 RENAME TO t5;
+--error ER_TABLE_NOT_LOCKED
+SELECT * FROM t5 ORDER BY c1;
+--error ER_TABLE_NOT_LOCKED
+ALTER TABLE t5 RENAME TO t3;
+UNLOCK TABLES;
+ALTER TABLE t5 RENAME TO t3;
+SELECT * FROM t3 ORDER BY c1;
+DROP TABLE t1, t2, t3;
+#
+--echo #
+--echo # Drop locked tables.
+--echo #
+--echo # 1. Drop parent.
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
+  INSERT_METHOD=LAST;
+LOCK TABLES t1 WRITE, t2 WRITE;
+INSERT INTO t1 VALUES (1);
+DROP TABLE t2;
+--error ER_TABLE_NOT_LOCKED
+SELECT * FROM t2;
+SELECT * FROM t1;
+UNLOCK TABLES;
+--echo # 2. Drop child.
+CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
+  INSERT_METHOD=LAST;
+LOCK TABLES t1 WRITE, t2 WRITE;
+INSERT INTO t1 VALUES (1);
+DROP TABLE t1;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM t2;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM t1;
+UNLOCK TABLES;
+DROP TABLE t2;
+#
+--echo #
+--echo # ALTER TABLE. Change child list.
+--echo #
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+CREATE TABLE t2 (c1 INT, INDEX(c1));
+CREATE TABLE t3 (c1 INT, INDEX(c1));
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (3);
+CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t3,t2)
+  INSERT_METHOD=LAST;
+--echo # Shrink child list.
+ALTER TABLE t4 UNION=(t3);
+SHOW CREATE TABLE t4;
+SELECT * FROM t4 ORDER BY c1;
+--echo # Extend child list.
+ALTER TABLE t4 UNION=(t3,t2);
+SHOW CREATE TABLE t4;
+SELECT * FROM t4 ORDER BY c1;
+#
+--echo #
+--echo # ALTER TABLE under LOCK TABLES. Change child list.
+--echo #
+LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE;
+--echo # Shrink child list.
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+ALTER TABLE t4 UNION=(t3);
+--echo # Extend child list within locked tables.
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+ALTER TABLE t4 UNION=(t3,t2);
+--echo # Extend child list beyond locked tables.
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+ALTER TABLE t4 UNION=(t3,t2,t1);
+SHOW CREATE TABLE t4;
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+DROP TABLE t4;
+#
+--echo #
+--echo # ALTER TABLE under LOCK TABLES. Grave change, table re-creation.
+--echo #
+CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
+  INSERT_METHOD=LAST;
+--echo # Lock parent first and then children.
+LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
+ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+--echo # Lock children first and then parent.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
+ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+--echo # Lock parent between children.
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4;
+#
+--echo #
+--echo # ALTER TABLE under LOCK TABLES. Simple change, no re-creation.
+--echo #
+CREATE TABLE t1 (c1 INT);
+CREATE TABLE t2 (c1 INT);
+CREATE TABLE t3 (c1 INT);
+CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
+  INSERT_METHOD=LAST;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+INSERT INTO t3 VALUES (3);
+--echo # Lock parent first and then children.
+LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
+ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
+SELECT * FROM t4 ORDER BY c1;
+ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+--echo # Lock children first and then parent.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
+ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
+SELECT * FROM t4 ORDER BY c1;
+ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+--echo # Lock parent between children.
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
+SELECT * FROM t4 ORDER BY c1;
+ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+#
+--echo #
+--echo # FLUSH TABLE under LOCK TABLES.
+--echo #
+--echo # Lock parent first and then children.
+LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
+FLUSH TABLE t4;
+SELECT * FROM t4 ORDER BY c1;
+FLUSH TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+FLUSH TABLES;
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+--echo # Lock children first and then parent.
+LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
+FLUSH TABLE t4;
+SELECT * FROM t4 ORDER BY c1;
+FLUSH TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+FLUSH TABLES;
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+--echo # Lock parent between children.
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+FLUSH TABLE t4;
+SELECT * FROM t4 ORDER BY c1;
+FLUSH TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+FLUSH TABLES;
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+#
+--echo #
+--echo # Triggers
+--echo #
+--echo # Trigger on parent
+DELETE FROM t4 WHERE c1 = 4;
+CREATE TRIGGER t4_ai AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
+SET @a=0;
+INSERT INTO t4 VALUES (4);
+SELECT @a;
+SELECT * FROM t4 ORDER BY c1;
+DROP TRIGGER t4_ai;
+--echo # Trigger on parent under LOCK TABLES
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+CREATE TRIGGER t4_ai AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
+SET @a=0;
+INSERT INTO t4 VALUES (4);
+SELECT @a;
+SELECT * FROM t4 ORDER BY c1;
+DROP TRIGGER t4_ai;
+UNLOCK TABLES;
+--echo #
+--echo # Trigger on child
+DELETE FROM t4 WHERE c1 = 4;
+CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
+SET @a=0;
+INSERT INTO t4 VALUES (4);
+SELECT @a;
+INSERT INTO t3 VALUES (33);
+SELECT @a;
+SELECT * FROM t4 ORDER BY c1;
+DROP TRIGGER t3_ai;
+--echo # Trigger on child under LOCK TABLES
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
+SET @a=0;
+INSERT INTO t4 VALUES (4);
+SELECT @a;
+INSERT INTO t3 VALUES (33);
+SELECT @a;
+SELECT * FROM t4 ORDER BY c1;
+DELETE FROM t4 WHERE c1 = 33;
+DROP TRIGGER t3_ai;
+--echo #
+--echo # Trigger with table use on child
+DELETE FROM t4 WHERE c1 = 4;
+CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
+INSERT INTO t4 VALUES (4);
+SELECT * FROM t4 ORDER BY c1;
+INSERT INTO t3 VALUES (33);
+SELECT * FROM t4 ORDER BY c1;
+DELETE FROM t4 WHERE c1 = 22;
+DELETE FROM t4 WHERE c1 = 33;
+DROP TRIGGER t3_ai;
+--echo # Trigger with table use on child under LOCK TABLES
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
+INSERT INTO t4 VALUES (4);
+SELECT * FROM t4 ORDER BY c1;
+INSERT INTO t3 VALUES (33);
+SELECT * FROM t4 ORDER BY c1;
+DROP TRIGGER t3_ai;
+DELETE FROM t4 WHERE c1 = 22;
+DELETE FROM t4 WHERE c1 = 33;
+UNLOCK TABLES;
+#
+--echo #
+--echo # Repair
+--echo #
+REPAIR TABLE t4;
+REPAIR TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+REPAIR TABLE t4;
+REPAIR TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+#
+--echo #
+--echo # Optimize
+--echo #
+OPTIMIZE TABLE t4;
+OPTIMIZE TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+OPTIMIZE TABLE t4;
+OPTIMIZE TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+#
+--echo #
+--echo # Checksum
+--echo #
+CHECKSUM TABLE t4;
+CHECKSUM TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+CHECKSUM TABLE t4;
+CHECKSUM TABLE t2;
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+#
+--echo #
+--echo # Insert delayed
+--echo #
+# See also Bug#26464 - insert delayed + update + merge = corruption
+# Succeeds in embedded server - is converted to normal insert
+# Fails in normal server, ps-protocol - not supported by engine
+# Fails in normal server, normal protocol - not a base table
+--error 0, ER_ILLEGAL_HA, ER_WRONG_OBJECT
+INSERT DELAYED INTO t4 VALUES(44);
+# Get rid of row in embedded server
+DELETE FROM t4 WHERE c1 = 44;
+INSERT DELAYED INTO t3 VALUES(33);
+  let $wait_cmd= SHOW STATUS LIKE 'Not_flushed_delayed_rows';
+  let $run= query_get_value($wait_cmd, Value, 1);
+  while ($run)
+  {
+    let $run= query_get_value($wait_cmd, Value, 1);
+  }
+SELECT * FROM t4 ORDER BY c1;
+LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
+--error ER_DELAYED_INSERT_TABLE_LOCKED, ER_ILLEGAL_HA
+INSERT DELAYED INTO t4 VALUES(444);
+--error ER_DELAYED_INSERT_TABLE_LOCKED, ER_ILLEGAL_HA
+INSERT DELAYED INTO t3 VALUES(333);
+SELECT * FROM t4 ORDER BY c1;
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4;
+#
+--echo #
+--echo # Recursive inclusion of merge tables in their union clauses.
+--echo #
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
+  INSERT_METHOD=LAST;
+CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t2,t1)
+  INSERT_METHOD=LAST;
+ALTER TABLE t2 UNION=(t3,t1);
+--error ER_ADMIN_WRONG_MRG_TABLE
+SELECT * FROM t2;
+DROP TABLE t1, t2, t3;
+
+
+#
+# Bug#25038 - Waiting TRUNCATE
+#
+# Show that truncate of child table after use of parent table works.
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; 
+CREATE TABLE t2 (c1 INT) ENGINE= MyISAM; 
+CREATE TABLE t3 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1, t2); 
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t3;
+TRUNCATE TABLE t1;
+SELECT * FROM t3;
+DROP TABLE t1, t2, t3;
+#
+# Show that truncate of child table waits while parent table is used.
+# (test partly borrowed from count_distinct3.)
+CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);
+SET @rnd_max= 2147483647;
+let $1 = 10;
+while ($1)
+{
+  SET @rnd= RAND();
+  SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
+  SET @id_rev= @rnd_max - @id;
+  SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 
+  INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 
+  dec $1;
+}
+set @@read_buffer_size=2*1024*1024;
+CREATE TABLE t2 SELECT * FROM t1;
+INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
+INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
+INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
+INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
+INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
+CREATE TABLE t3 (id INTEGER, grp TINYINT, id_rev INTEGER)
+  ENGINE= MRG_MYISAM UNION= (t1, t2);
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t2;
+SELECT COUNT(*) FROM t3;
+connect (con1,localhost,root,,);
+    # As t3 contains random numbers, results are different from test to test. 
+    # That's okay, because we test only that select doesn't yield an
+    # error. Note, that --disable_result_log doesn't suppress error output.
+    --disable_result_log
+    send SELECT COUNT(DISTINCT a1.id) FROM t3 AS a1, t3 AS a2
+      WHERE a1.id = a2.id GROUP BY a2.grp;
+connection default;
+sleep 1;
+TRUNCATE TABLE t1;
+    connection con1;
+    reap;
+    --enable_result_log
+    disconnect con1;
+connection default;
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t2;
+SELECT COUNT(*) FROM t3;
+DROP TABLE t1, t2, t3;
+
+#
+# Bug#25700 - merge base tables get corrupted by optimize/analyze/repair table
+#
+# Using FLUSH TABLES before REPAIR.
+CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
+CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
+INSERT INTO t2 VALUES (1);
+SELECT * FROM t2;
+LOCK TABLES t2 WRITE, t1 WRITE;
+FLUSH TABLES;
+REPAIR TABLE t1;
+CHECK TABLE t1;
+REPAIR TABLE t1;
+UNLOCK TABLES;
+CHECK TABLE t1 EXTENDED;
+#
+# Not using FLUSH TABLES before REPAIR.
+LOCK TABLES t2 WRITE, t1 WRITE;
+SELECT * FROM t2;
+LOCK TABLES t2 WRITE, t1 WRITE;
+REPAIR TABLE t1;
+CHECK TABLE t1;
+REPAIR TABLE t1;
+UNLOCK TABLES;
+CHECK TABLE t1 EXTENDED;
+DROP TABLE t1, t2;
+
+#
+# Bug#26377 - Deadlock with MERGE and FLUSH TABLE
+#
+CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
+CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
+# Lock t1 first. This did always work.
+LOCK TABLES t1 WRITE, m1 WRITE;
+FLUSH TABLE t1;
+UNLOCK TABLES;
+DROP TABLE m1, t1;
+#
+CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
+CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
+# Lock m1 first. This did deadlock.
+LOCK TABLES m1 WRITE, t1 WRITE;
+FLUSH TABLE t1;
+UNLOCK TABLES;
+DROP TABLE m1, t1;
+
+#
+# Bug#27660 - Falcon: merge table possible
+#
+# Normal MyISAM MERGE operation.
+CREATE TABLE t1 (c1 INT, c2 INT) ENGINE= MyISAM;
+CREATE TABLE t2 (c1 INT, c2 INT) ENGINE= MyISAM;
+CREATE TABLE t3 (c1 INT, c2 INT) ENGINE= MRG_MYISAM UNION(t1, t2);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t2 VALUES (2, 2);
+SELECT * FROM t3;
+# Try an unsupported engine.
+ALTER TABLE t1 ENGINE= MEMORY;
+INSERT INTO t1 VALUES (0, 0);
+# Before fixing, this succeeded, but (0, 0) was missing.
+--error 1168
+SELECT * FROM t3;
+DROP TABLE t1, t2, t3;
+
+#
+# Bug#30275 - Merge tables: flush tables or unlock tables causes server to crash
+#
+CREATE TABLE t1 (c1 INT, KEY(c1));
+CREATE TABLE t2 (c1 INT, KEY(c1)) ENGINE=MRG_MYISAM UNION=(t1)
+  INSERT_METHOD=FIRST;
+LOCK TABLE t1 WRITE, t2 WRITE;
+FLUSH TABLES t2, t1;
+OPTIMIZE TABLE t1;
+FLUSH TABLES t1;
+UNLOCK TABLES;
+#
+FLUSH TABLES;
+INSERT INTO t1 VALUES (1);
+LOCK TABLE t1 WRITE, t2 WRITE;
+FLUSH TABLES t2, t1;
+OPTIMIZE TABLE t1;
+FLUSH TABLES t1;
+UNLOCK TABLES;
+DROP TABLE t1, t2;
+
+#
+# Test derived from test program for
+# Bug#30273 - merge tables: Can't lock file (errno: 155)
+#
+CREATE TABLE t1 (ID INT) ENGINE=MYISAM;
+CREATE TABLE m1 (ID INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST;
+INSERT INTO t1 VALUES ();
+INSERT INTO m1 VALUES ();
+LOCK TABLE t1 WRITE, m1 WRITE;
+FLUSH TABLES m1, t1;
+OPTIMIZE TABLE t1;
+FLUSH TABLES m1, t1;
+UNLOCK TABLES;
+DROP TABLE t1, m1;
+
diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test
index 43c40ab8966..cadab8b3b70 100644
--- a/mysql-test/t/myisam.test
+++ b/mysql-test/t/myisam.test
@@ -576,32 +576,6 @@ select count(*) from t1 where a is null;
 drop table t1;
 
 #
-# Bug #8306: TRUNCATE leads to index corruption 
-#
-create table t1 (c1 int, index(c1));
-create table t2 (c1 int, index(c1)) engine=merge union=(t1);
-insert into t1 values (1);
-# Close all tables.
-flush tables;
-# Open t2 and (implicitly) t1.
-select * from t2;
-# Truncate after flush works (unless another threads reopens t2 in between).
-flush tables;
-truncate table t1;
-insert into t1 values (1);
-# Close all tables.
-flush tables;
-# Open t2 and (implicitly) t1.
-select * from t2;
-# Truncate t1, wich was not recognized as open without the bugfix.
-# Now, it should fail with a table-in-use error message.
---error 1105
-truncate table t1;
-# The insert used to fail on the crashed table.
-insert into t1 values (1);
-drop table t1,t2;
-
-#
 # bug9188 - Corruption Can't open file: 'table.MYI' (errno: 145)
 #
 create table t1 (c1 int, c2 varchar(4) not null default '',