summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/extra/binlog_tests/blackhole.test9
-rw-r--r--mysql-test/r/create.result2
-rw-r--r--mysql-test/r/delayed.result5
-rw-r--r--mysql-test/r/merge-big.result77
-rw-r--r--mysql-test/r/merge.result1094
-rw-r--r--mysql-test/r/myisam.result18
-rw-r--r--mysql-test/suite/binlog/r/binlog_stm_blackhole.result1
-rw-r--r--mysql-test/t/create.test2
-rw-r--r--mysql-test/t/delayed.test8
-rw-r--r--mysql-test/t/merge-big.test150
-rw-r--r--mysql-test/t/merge.test847
-rw-r--r--mysql-test/t/myisam.test26
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 '',
View Lorry Controller Status