List:Commits« Previous MessageNext Message »
From:Ingo Struewing Date:March 5 2010 3:26pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (ingo.struewing:3123)
Bug#36171
View as plain text  
#At file:///home2/mydev/bzrroot/mysql-5.6-bug36171-2/ based on revid:ingo.struewing@stripped

 3123 Ingo Struewing	2010-03-05
      Backport of revid:ingo.struewing@stripped
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
        
        In former MySQL versions, up to 5.1.23/6.0.4 it was possible to create
        temporary MERGE tables with non-temporary MyISAM tables.
        
        This has been changed in the mentioned version due to Bug 19627
        (temporary merge table locking). MERGE children were locked through
        the parent table. If the parent was temporary, it was not locked and
        so the children were not locked either. Parallel use of the MyISAM
        tables corrupted them.
        
        Since 6.0.6 (WL 4144 - Lock MERGE engine children), the children are
        locked independently from the parent. Now it is possible to allow
        non-temporary children with a temporary parent. Even though the
        temporary MERGE table itself is not locked, each non-temporary
        MyISAM table is locked anyway.
        
        NOTE: Behavior change: In 5.1.23/6.0.4 we prohibited non-temporary
        children with a temporary MERGE table. Now we re-allow it.
        An important side-effect is that temporary tables, which overlay
        non-temporary MERGE children, overlay the children in the MERGE table.
      
      This leaves merge.test failing. To be fixed by later backports.
     @ mysql-test/r/merge.result
        Backport of revid:ingo.struewing@stripped
            Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
            Updated test result.
     @ mysql-test/r/merge_sync.result
        Backport of revid:ingo.struewing@stripped
            Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
            Renamed test ('-' -> '_').
            Added test result.
     @ mysql-test/t/merge.test
        Backport of revid:ingo.struewing@stripped
            Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
            Updated test cases. Temporary MERGE with non-temporary MyISAM is allowed now.
            Added more tests.
     @ mysql-test/t/merge_sync.test
        Backport of revid:ingo.struewing@stripped
            Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
            Renamed test ('-' -> '_').
            Added test case.
     @ storage/myisammrg/ha_myisammrg.cc
        Backport of revid:ingo.struewing@stripped
            Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
            Changed constraint for temporary state of tables.

    added:
      mysql-test/r/merge_sync.result
      mysql-test/t/merge_sync.test
    modified:
      mysql-test/r/merge.result
      mysql-test/t/merge.test
      storage/myisammrg/ha_myisammrg.cc
=== modified file 'mysql-test/r/merge.result'
--- a/mysql-test/r/merge.result	2010-03-03 14:23:32 +0000
+++ b/mysql-test/r/merge.result	2010-03-05 15:26:04 +0000
@@ -584,7 +584,9 @@ INSERT INTO t1 VALUES (1);
 INSERT INTO t2 VALUES (2);
 CREATE TEMPORARY TABLE t3 (c1 INT NOT NULL) ENGINE=MRG_MYISAM 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
+c1
+1
+2
 CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL);
 CREATE TEMPORARY TABLE t5 (c1 INT NOT NULL);
 INSERT INTO t4 VALUES (4);
@@ -613,7 +615,9 @@ ERROR HY000: Unable to open underlying t
 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
+a
+1
+2
 drop table t3, t2, t1;
 # CREATE...SELECT is not implemented for MERGE tables.
 CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL);
@@ -2556,6 +2560,622 @@ DROP TRIGGER t2_au;
 DROP FUNCTION f1;
 DROP TABLE tm1, t1, t2, t3, t4, t5;
 #
+# Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
+# More tests with TEMPORARY MERGE table and permanent children.
+# First without locked tables.
+#
+DROP TABLE IF EXISTS t1, t2, t3, t4, m1, m2;
+#
+CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE TABLE m1;
+Table	Create Table
+m1	CREATE TEMPORARY TABLE `m1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m1;
+c1	c2
+INSERT INTO t1 VALUES (111, 121);
+INSERT INTO m1 VALUES (211, 221);
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+SELECT * FROM t1;
+c1	c2
+111	121
+SELECT * FROM t2;
+c1	c2
+211	221
+#
+ALTER TABLE m1 RENAME m2;
+SHOW CREATE TABLE m2;
+Table	Create Table
+m2	CREATE TEMPORARY TABLE `m2` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m2;
+c1	c2
+111	121
+211	221
+#
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+ALTER TABLE m2 RENAME m1;
+ERROR 42S01: Table 'm1' already exists
+DROP TABLE m1;
+ALTER TABLE m2 RENAME m1;
+SHOW CREATE TABLE m1;
+Table	Create Table
+m1	CREATE TEMPORARY TABLE `m1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+#
+ALTER TABLE m1 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+SELECT * FROM m1;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ALTER TABLE t1 ADD COLUMN c3 INT;
+ALTER TABLE t2 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+SELECT * FROM m1;
+c1	c2	c3
+111	121	NULL
+211	221	NULL
+212	222	232
+#
+ALTER TABLE m1 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+SELECT * FROM m1;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ALTER TABLE t1 DROP COLUMN c3;
+ALTER TABLE t2 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+#
+CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3);
+INSERT INTO m1 VALUES (311, 321);
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+311	321
+SELECT * FROM t1;
+c1	c2
+111	121
+SELECT * FROM t2;
+c1	c2
+211	221
+212	222
+213	223
+SELECT * FROM t3;
+c1	c2
+311	321
+#
+CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3,t4);
+INSERT INTO m1 VALUES (411, 421);
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+311	321
+411	421
+SELECT * FROM t1;
+c1	c2
+111	121
+SELECT * FROM t2;
+c1	c2
+211	221
+212	222
+213	223
+SELECT * FROM t3;
+c1	c2
+311	321
+SELECT * FROM t4;
+c1	c2
+411	421
+#
+ALTER TABLE m1 ENGINE=MyISAM;
+SHOW CREATE TABLE m1;
+Table	Create Table
+m1	CREATE TEMPORARY TABLE `m1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO m1 VALUES (511, 521);
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+311	321
+411	421
+511	521
+#
+ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+SELECT * FROM t1;
+c1	c2
+111	121
+SELECT * FROM t2;
+c1	c2
+211	221
+212	222
+213	223
+#
+CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (611, 621);
+SELECT * FROM m1;
+c1	c2
+611	621
+211	221
+212	222
+213	223
+DROP TABLE t1;
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+#
+#
+SHOW CREATE TABLE m1;
+Table	Create Table
+m1	CREATE TEMPORARY TABLE `m1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+#
+CREATE TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+Table	Create Table
+m2	CREATE TABLE `m2` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM m2;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+DROP TABLE m2;
+#
+CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+Table	Create Table
+m2	CREATE TEMPORARY TABLE `m2` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM m2;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+DROP TABLE m2;
+#
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST;
+SELECT * FROM m2;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+DROP TABLE m2;
+#
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST SELECT * FROM m1;
+ERROR HY000: 'test.m2' is not BASE TABLE
+#
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST SELECT * FROM m1;
+ERROR HY000: 'test.m2' is not BASE TABLE
+#
+CREATE TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+Table	Create Table
+m2	CREATE TABLE `m2` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM m2;
+c1	c2
+# Table was created without a UNION clause. No INSERT possible.
+INSERT INTO m2 SELECT * FROM m1;
+ERROR HY000: Table 'm2' is read only
+SELECT * FROM m2;
+c1	c2
+DROP TABLE m2;
+#
+CREATE TEMPORARY TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+Table	Create Table
+m2	CREATE TEMPORARY TABLE `m2` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM m2;
+c1	c2
+# Table was created without a UNION clause. No INSERT possible.
+INSERT INTO m2 SELECT * FROM m1;
+ERROR HY000: Table 'm2' is read only
+SELECT * FROM m2;
+c1	c2
+DROP TABLE m2;
+#
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST;
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1	c2
+311	321
+411	421
+111	121
+211	221
+212	222
+213	223
+#
+#
+LOCK TABLE m1 WRITE, m2 WRITE;
+SELECT * FROM m1,m2;
+c1	c2	c1	c2
+111	121	311	321
+211	221	311	321
+212	222	311	321
+213	223	311	321
+111	121	411	421
+211	221	411	421
+212	222	411	421
+213	223	411	421
+111	121	111	121
+211	221	111	121
+212	222	111	121
+213	223	111	121
+111	121	211	221
+211	221	211	221
+212	222	211	221
+213	223	211	221
+111	121	212	222
+211	221	212	222
+212	222	212	222
+213	223	212	222
+111	121	213	223
+211	221	213	223
+212	222	213	223
+213	223	213	223
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4, m1, m2;
+#
+# Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
+# More tests with TEMPORARY MERGE table and permanent children.
+# (continued) Now the same with locked table.
+#
+CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE TABLE m1;
+Table	Create Table
+m1	CREATE TEMPORARY TABLE `m1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m1;
+c1	c2
+INSERT INTO t1 VALUES (111, 121);
+INSERT INTO m1 VALUES (211, 221);
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+SELECT * FROM t1;
+c1	c2
+111	121
+SELECT * FROM t2;
+c1	c2
+211	221
+#
+LOCK TABLE m1 WRITE;
+#
+ALTER TABLE m1 RENAME m2;
+SHOW CREATE TABLE m2;
+Table	Create Table
+m2	CREATE TEMPORARY TABLE `m2` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m2;
+c1	c2
+111	121
+211	221
+#
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+ALTER TABLE m2 RENAME m1;
+ERROR 42S01: Table 'm1' already exists
+DROP TABLE m1;
+ALTER TABLE m2 RENAME m1;
+SHOW CREATE TABLE m1;
+Table	Create Table
+m1	CREATE TEMPORARY TABLE `m1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+#
+ALTER TABLE m1 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+SELECT * FROM m1;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ALTER TABLE t1 ADD COLUMN c3 INT;
+ALTER TABLE t2 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+SELECT * FROM m1;
+c1	c2	c3
+111	121	NULL
+211	221	NULL
+212	222	232
+#
+ALTER TABLE m1 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+SELECT * FROM m1;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+ALTER TABLE t1 DROP COLUMN c3;
+ALTER TABLE t2 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+#
+CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3);
+INSERT INTO m1 VALUES (311, 321);
+ERROR HY000: Table 't3' was not locked with LOCK TABLES
+LOCK TABLE m1 WRITE;
+INSERT INTO m1 VALUES (311, 321);
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+311	321
+SELECT * FROM t1;
+c1	c2
+111	121
+SELECT * FROM t2;
+c1	c2
+211	221
+212	222
+213	223
+SELECT * FROM t3;
+c1	c2
+311	321
+#
+CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3,t4);
+INSERT INTO m1 VALUES (411, 421);
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+311	321
+411	421
+SELECT * FROM t1;
+c1	c2
+111	121
+SELECT * FROM t2;
+c1	c2
+211	221
+212	222
+213	223
+SELECT * FROM t3;
+c1	c2
+311	321
+SELECT * FROM t4;
+c1	c2
+411	421
+#
+ALTER TABLE m1 ENGINE=MyISAM;
+SHOW CREATE TABLE m1;
+Table	Create Table
+m1	CREATE TEMPORARY TABLE `m1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO m1 VALUES (511, 521);
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+311	321
+411	421
+511	521
+#
+ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
+INSERT_METHOD=LAST;
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+SELECT * FROM t1;
+c1	c2
+111	121
+SELECT * FROM t2;
+c1	c2
+211	221
+212	222
+213	223
+#
+CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (611, 621);
+SELECT * FROM m1;
+c1	c2
+611	621
+211	221
+212	222
+213	223
+DROP TABLE t1;
+SELECT * FROM m1;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+#
+#
+SHOW CREATE TABLE m1;
+Table	Create Table
+m1	CREATE TEMPORARY TABLE `m1` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`)
+CREATE TABLE m2 SELECT * FROM m1;
+ERROR HY000: Table 'm2' was not locked with LOCK TABLES
+#
+CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+Table	Create Table
+m2	CREATE TEMPORARY TABLE `m2` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM m2;
+c1	c2
+111	121
+211	221
+212	222
+213	223
+DROP TABLE m2;
+#
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST;
+SELECT * FROM m2;
+ERROR HY000: Table 'm2' was not locked with LOCK TABLES
+LOCK TABLE m1 WRITE, m2 WRITE;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+UNLOCK TABLES;
+DROP TABLE m2;
+LOCK TABLE m1 WRITE;
+#
+# In normal mode we get ER_WRONG_OBJECT,
+# in ps-protocol mode we get ER_TABLE_NOT_LOCKED
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST SELECT * FROM m1;
+Got one of the listed errors
+#
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST SELECT * FROM m1;
+ERROR HY000: 'test.m2' is not BASE TABLE
+#
+CREATE TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+ERROR HY000: Table 'm2' was not locked with LOCK TABLES
+LOCK TABLE m1 WRITE, m2 WRITE;
+SHOW CREATE TABLE m2;
+Table	Create Table
+m2	CREATE TABLE `m2` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM m2;
+c1	c2
+# Table was created without a UNION clause. No INSERT possible.
+INSERT INTO m2 SELECT * FROM m1;
+ERROR HY000: Table 'm2' is read only
+SELECT * FROM m2;
+c1	c2
+DROP TABLE m2;
+#
+CREATE TEMPORARY TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+Table	Create Table
+m2	CREATE TEMPORARY TABLE `m2` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM m2;
+c1	c2
+# Table was created without a UNION clause. No INSERT possible.
+INSERT INTO m2 SELECT * FROM m1;
+ERROR HY000: Table 'm2' is read only
+SELECT * FROM m2;
+c1	c2
+DROP TABLE m2;
+#
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+INSERT_METHOD=LAST;
+INSERT INTO m2 SELECT * FROM m1;
+ERROR HY000: Table 't3' was not locked with LOCK TABLES
+LOCK TABLE m1 WRITE, m2 WRITE;
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+c1	c2
+311	321
+411	421
+111	121
+211	221
+212	222
+213	223
+#
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4, m1, m2;
+#
 # Bug47098 assert in MDL_context::destroy on HANDLER
 #          <damaged merge table> OPEN
 #

=== added file 'mysql-test/r/merge_sync.result'
--- a/mysql-test/r/merge_sync.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/merge_sync.result	2010-03-05 15:26:04 +0000
@@ -0,0 +1,111 @@
+SET DEBUG_SYNC= 'RESET';
+drop table if exists t1,t2,t3,t4,t5,t6;
+drop database if exists mysqltest;
+SET GLOBAL concurrent_insert= 0;
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
+connection con1
+SET DEBUG_SYNC= 'after_admin_flush
+                     SIGNAL admin_flush WAIT_FOR end_repair';
+REPAIR TABLE t1;
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR admin_flush';
+SET DEBUG_SYNC= 'mysql_lock_retry HIT_LIMIT 3';
+SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL end_repair';
+INSERT INTO t2 VALUES (1);
+SET DEBUG_SYNC= 'now SIGNAL end_repair';
+connection con1
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+connection default;
+SET DEBUG_SYNC= 'RESET';
+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;
+REPAIR TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+connection con1
+SET DEBUG_SYNC= 'mysql_lock_retry HIT_LIMIT 3';
+SET DEBUG_SYNC= 'after_insert SIGNAL end_repair';
+SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL end_repair';
+INSERT INTO t2 VALUES (1);
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR end_repair';
+UNLOCK TABLES;
+connection con1
+connection default;
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE t1, t2;
+CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
+CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
+connection con1
+SET DEBUG_SYNC= 'before_myisammrg_attach
+                     SIGNAL attach WAIT_FOR flushed';
+INSERT INTO m1 VALUES (2);
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR attach';
+SET DEBUG_SYNC= 'after_flush_unlock SIGNAL flushed';
+FLUSH TABLE m1;
+connection con1
+connection default;
+SELECT * FROM m1;
+c1
+2
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE m1, t1;
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+LOCK TABLE t1 WRITE;
+connection con1
+SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked';
+INSERT INTO t1 VALUES (1);
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+UNLOCK TABLES;
+connection con1
+connection default;
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE t1;
+#
+# Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
+#
+# Check that a non-temporary child is properly locked under a
+# temporary parent.
+#
+CREATE TABLE t1 (c1 REAL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE m1 (c1 REAL) ENGINE=MRG_MYISAM UNION=(t1);
+INSERT INTO t1 VALUES(0.1);
+# connection default, lock the tables.
+SET DEBUG_SYNC= 'before_acos_function SIGNAL selecting WAIT_FOR finish';
+SELECT ACOS(c1) FROM m1;
+#
+# connection con1, try to insert.
+SET DEBUG_SYNC= 'now WAIT_FOR selecting';
+SET DEBUG_SYNC= 'wait_for_lock SIGNAL lockwait';
+INSERT INTO t1 VALUES(2.1);
+#
+# connection con2, show state.
+SET DEBUG_SYNC= 'now WAIT_FOR lockwait';
+SELECT STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;
+STATE	INFO
+executing	SELECT STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST
+Table lock	INSERT INTO t1 VALUES(2.1)
+debug sync point: before_acos_function	SELECT ACOS(c1) FROM m1
+SET DEBUG_SYNC= 'now SIGNAL finish';
+#
+# connection default, retrieve SELECT result.
+ACOS(c1)
+1.4706289056333368
+#
+# connection con1, retrieve INSERT result.
+#
+#connection default, show table contents.
+SELECT * FROM m1;
+c1
+0.1
+2.1
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE m1;
+DROP TABLE t1;
+SET GLOBAL concurrent_insert= DEFAULT;

=== modified file 'mysql-test/t/merge.test'
--- a/mysql-test/t/merge.test	2010-03-03 14:23:32 +0000
+++ b/mysql-test/t/merge.test	2010-03-05 15:26:04 +0000
@@ -220,7 +220,6 @@ CREATE TABLE t2 (c1 INT NOT NULL);
 INSERT INTO t1 VALUES (1);
 INSERT INTO t2 VALUES (2);
 CREATE TEMPORARY TABLE t3 (c1 INT NOT NULL) ENGINE=MRG_MYISAM UNION=(t1,t2);
---error ER_WRONG_MRG_TABLE
 SELECT * FROM t3;
 CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL);
 CREATE TEMPORARY TABLE t5 (c1 INT NOT NULL);
@@ -252,7 +251,6 @@ create table t3 (a int not null) ENGINE=
 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.
@@ -886,12 +884,9 @@ SHOW CREATE TABLE t4;
 --error ER_TABLE_NOT_LOCKED
 INSERT INTO t4 VALUES (4);
 CREATE TEMPORARY TABLE t4 LIKE t3;
---error ER_WRONG_MRG_TABLE
 SHOW CREATE TABLE t4;
---error ER_WRONG_MRG_TABLE
 INSERT INTO t4 VALUES (4);
 UNLOCK TABLES;
---error ER_WRONG_MRG_TABLE
 INSERT INTO t4 VALUES (4);
 DROP TABLE t4;
 #
@@ -2033,6 +2028,348 @@ DROP FUNCTION f1;
 DROP TABLE tm1, t1, t2, t3, t4, t5;
 
 --echo #
+--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
+--echo # More tests with TEMPORARY MERGE table and permanent children.
+--echo # First without locked tables.
+--echo #
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, t3, t4, m1, m2;
+--enable_warnings
+#
+--echo #
+CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE m1;
+SELECT * FROM m1;
+INSERT INTO t1 VALUES (111, 121);
+INSERT INTO m1 VALUES (211, 221);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+#
+--echo #
+ALTER TABLE m1 RENAME m2;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+--error ER_TABLE_EXISTS_ERROR
+ALTER TABLE m2 RENAME m1;
+DROP TABLE m1;
+ALTER TABLE m2 RENAME m1;
+SHOW CREATE TABLE m1;
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 ADD COLUMN c3 INT;
+--error ER_WRONG_MRG_TABLE
+INSERT INTO m1 VALUES (212, 222, 232);
+--error ER_WRONG_MRG_TABLE
+SELECT * FROM m1;
+ALTER TABLE t1 ADD COLUMN c3 INT;
+ALTER TABLE t2 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 DROP COLUMN c3;
+--error ER_WRONG_MRG_TABLE
+INSERT INTO m1 VALUES (213, 223);
+--error ER_WRONG_MRG_TABLE
+SELECT * FROM m1;
+ALTER TABLE t1 DROP COLUMN c3;
+ALTER TABLE t2 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+SELECT * FROM m1;
+#
+--echo #
+CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3);
+INSERT INTO m1 VALUES (311, 321);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+#
+--echo #
+CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3,t4);
+INSERT INTO m1 VALUES (411, 421);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+SELECT * FROM t4;
+#
+--echo #
+ALTER TABLE m1 ENGINE=MyISAM;
+SHOW CREATE TABLE m1;
+INSERT INTO m1 VALUES (511, 521);
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+#
+--echo #
+CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (611, 621);
+SELECT * FROM m1;
+DROP TABLE t1;
+SELECT * FROM m1;
+#
+#
+--echo #
+--echo #
+SHOW CREATE TABLE m1;
+#
+--echo #
+CREATE TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST;
+--error ER_WRONG_MRG_TABLE
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+--error ER_WRONG_OBJECT
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST SELECT * FROM m1;
+#
+--echo #
+--error ER_WRONG_OBJECT
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST SELECT * FROM m1;
+#
+--echo #
+CREATE TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+--echo # Table was created without a UNION clause. No INSERT possible.
+--error ER_OPEN_AS_READONLY
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+--echo # Table was created without a UNION clause. No INSERT possible.
+--error ER_OPEN_AS_READONLY
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST;
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+#
+#
+--echo #
+--echo #
+LOCK TABLE m1 WRITE, m2 WRITE;
+SELECT * FROM m1,m2;
+UNLOCK TABLES;
+#
+DROP TABLE t1, t2, t3, t4, m1, m2;
+#
+#
+#
+--echo #
+--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
+--echo # More tests with TEMPORARY MERGE table and permanent children.
+--echo # (continued) Now the same with locked table.
+--echo #
+CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE m1;
+SELECT * FROM m1;
+INSERT INTO t1 VALUES (111, 121);
+INSERT INTO m1 VALUES (211, 221);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+#
+--echo #
+LOCK TABLE m1 WRITE;
+#
+--echo #
+ALTER TABLE m1 RENAME m2;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+--error ER_TABLE_EXISTS_ERROR
+ALTER TABLE m2 RENAME m1;
+DROP TABLE m1;
+ALTER TABLE m2 RENAME m1;
+SHOW CREATE TABLE m1;
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 ADD COLUMN c3 INT;
+--error ER_WRONG_MRG_TABLE
+INSERT INTO m1 VALUES (212, 222, 232);
+--error ER_WRONG_MRG_TABLE
+SELECT * FROM m1;
+ALTER TABLE t1 ADD COLUMN c3 INT;
+ALTER TABLE t2 ADD COLUMN c3 INT;
+INSERT INTO m1 VALUES (212, 222, 232);
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 DROP COLUMN c3;
+--error ER_WRONG_MRG_TABLE
+INSERT INTO m1 VALUES (213, 223);
+--error ER_WRONG_MRG_TABLE
+SELECT * FROM m1;
+ALTER TABLE t1 DROP COLUMN c3;
+ALTER TABLE t2 DROP COLUMN c3;
+INSERT INTO m1 VALUES (213, 223);
+SELECT * FROM m1;
+#
+--echo #
+CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3);
+--error ER_TABLE_NOT_LOCKED
+INSERT INTO m1 VALUES (311, 321);
+LOCK TABLE m1 WRITE;
+INSERT INTO m1 VALUES (311, 321);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+#
+--echo #
+CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
+ALTER TABLE m1 UNION=(t1,t2,t3,t4);
+INSERT INTO m1 VALUES (411, 421);
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+SELECT * FROM t4;
+#
+--echo #
+ALTER TABLE m1 ENGINE=MyISAM;
+SHOW CREATE TABLE m1;
+INSERT INTO m1 VALUES (511, 521);
+SELECT * FROM m1;
+#
+--echo #
+ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
+  INSERT_METHOD=LAST;
+SELECT * FROM m1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+#
+--echo #
+CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (611, 621);
+SELECT * FROM m1;
+DROP TABLE t1;
+SELECT * FROM m1;
+#
+#
+--echo #
+--echo #
+SHOW CREATE TABLE m1;
+--error ER_TABLE_NOT_LOCKED
+CREATE TABLE m2 SELECT * FROM m1;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST;
+--error ER_TABLE_NOT_LOCKED
+SELECT * FROM m2;
+--error ER_WRONG_MRG_TABLE
+LOCK TABLE m1 WRITE, m2 WRITE;
+UNLOCK TABLES;
+DROP TABLE m2;
+LOCK TABLE m1 WRITE;
+#
+--echo #
+--echo # In normal mode we get ER_WRONG_OBJECT,
+--echo # in ps-protocol mode we get ER_TABLE_NOT_LOCKED
+--error ER_WRONG_OBJECT, ER_TABLE_NOT_LOCKED
+CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST SELECT * FROM m1;
+#
+--echo #
+--error ER_WRONG_OBJECT
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST SELECT * FROM m1;
+#
+--echo #
+CREATE TABLE m2 LIKE m1;
+--error ER_TABLE_NOT_LOCKED
+SHOW CREATE TABLE m2;
+LOCK TABLE m1 WRITE, m2 WRITE;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+--echo # Table was created without a UNION clause. No INSERT possible.
+--error ER_OPEN_AS_READONLY
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 LIKE m1;
+SHOW CREATE TABLE m2;
+SELECT * FROM m2;
+--echo # Table was created without a UNION clause. No INSERT possible.
+--error ER_OPEN_AS_READONLY
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+DROP TABLE m2;
+#
+--echo #
+CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
+  INSERT_METHOD=LAST;
+--error ER_TABLE_NOT_LOCKED
+INSERT INTO m2 SELECT * FROM m1;
+LOCK TABLE m1 WRITE, m2 WRITE;
+INSERT INTO m2 SELECT * FROM m1;
+SELECT * FROM m2;
+#
+--echo #
+UNLOCK TABLES;
+DROP TABLE t1, t2, t3, t4, m1, m2;
+
+--echo #
 --echo # Bug47098 assert in MDL_context::destroy on HANDLER
 --echo #          <damaged merge table> OPEN
 --echo #

=== added file 'mysql-test/t/merge_sync.test'
--- a/mysql-test/t/merge_sync.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/merge_sync.test	2010-03-05 15:26:04 +0000
@@ -0,0 +1,472 @@
+#
+# Test of MERGE TABLES
+#
+
+#
+# We need the Debug Sync Facility.
+#
+--source include/have_debug_sync.inc
+
+# Clean up resources used in this test case.
+--disable_warnings
+SET DEBUG_SYNC= 'RESET';
+drop table if exists t1,t2,t3,t4,t5,t6;
+drop database if exists mysqltest;
+--enable_warnings
+
+#
+# With concurrent inserts enabled, SELECT does not always see rows inserted
+# by another session immediately before. Disable it to get stable results.
+#
+SET GLOBAL concurrent_insert= 0;
+
+#
+# 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;
+#
+    --echo connection con1
+    connect (con1,localhost,root,,);
+    # When reaching repair code, signal admin_flush and wait for end_repair.
+    SET DEBUG_SYNC= 'after_admin_flush
+                     SIGNAL admin_flush WAIT_FOR end_repair';
+    send REPAIR TABLE t1;
+#
+--echo connection default;
+connection default;
+# Wait that the other thread reaches repair.
+SET DEBUG_SYNC= 'now WAIT_FOR admin_flush';
+#
+# If the bug exists, INSERT will loop infinitely in getting its lock.
+# Bail out if lock retry is done 3 times.
+SET DEBUG_SYNC= 'mysql_lock_retry HIT_LIMIT 3';
+# When the bug is fixed, we wait for refresh of repaired table.
+# In this case resume repair thread so that we do not deadlock.
+SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL end_repair';
+# Succeeds with bug fixed.
+INSERT INTO t2 VALUES (1);
+#
+# Resume the other thread. (non-bug fixed case)
+SET DEBUG_SYNC= 'now SIGNAL end_repair';
+#
+    --echo connection con1
+    connection con1;
+    reap;
+    disconnect con1;
+#
+--echo connection default;
+connection default;
+# Clear debug_sync signal.
+SET DEBUG_SYNC= 'RESET';
+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;
+REPAIR TABLE t1;
+#
+    --echo connection con1
+    connect (con1,localhost,root,,);
+    # If the bug exists, the insert will loop infinitely in getting its lock.
+    # Bail out if lock retry is done 3 times.
+    SET DEBUG_SYNC= 'mysql_lock_retry HIT_LIMIT 3';
+    # If the bug exists, resume repair thread after reaching the retry limit.
+    SET DEBUG_SYNC= 'after_insert SIGNAL end_repair';
+    # If the bug is fixed, we wait for refresh of repaired table.
+    # In this case resume repair thread so that we do not deadlock.
+    SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL end_repair';
+    send INSERT INTO t2 VALUES (1);
+#
+--echo connection default;
+connection default;
+# Wait for signal from insert. Would be infinite with bug and no retry limit.
+SET DEBUG_SYNC= 'now WAIT_FOR end_repair';
+UNLOCK TABLES;
+#
+    --echo connection con1
+    connection con1;
+    # Succeeds with bug fixed.
+    reap;
+    disconnect con1;
+#
+--echo connection default;
+connection default;
+# Clear debug_sync signal.
+SET DEBUG_SYNC= 'RESET';
+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.
+# In >= 5.0, when waiting after open_tables() one FLUSH is sufficient
+# to allow the INSERT to step in.
+#
+# This test does not work in 6.0 because the table locking scheme
+# changed a lot. The above problem cannot happen any more.
+# Some of the places where the sync points were placed, don't exist
+# any more.
+if (0)
+{
+  CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+  LOCK TABLE t1 WRITE;
+  #
+      --echo connection con1
+      connect (con1,localhost,root,,);
+      # After open, wait for flush.
+      SET DEBUG_SYNC= 'before_lock_tables_takes_lock
+                       SIGNAL opened WAIT_FOR flushed';
+      # If bug is fixed, INSERT will go into wait_for_lock.
+      # Retain action after use. First used by general_log.
+      SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2';
+      # Alternatively INSERT can wait for refresh in open_table().
+      SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL locked';
+      # If bug is not fixed, INSERT will succeed. Pretend locked.
+      SET DEBUG_SYNC= 'after_insert SIGNAL locked';
+      send INSERT INTO t1 VALUES (1);
+  #
+  --echo connection default
+  connection default;
+  # Wait until INSERT opened the table.
+  SET DEBUG_SYNC= 'now WAIT_FOR opened';
+  #
+  # Let INSERT exploit the gap when flush waits wthout lock
+  # for other threads to close the tables.
+  SET DEBUG_SYNC= 'after_flush_unlock SIGNAL flushed';
+  FLUSH TABLES;
+  #
+  # Wait until INSERT is locked (bug fixed) or finished (bug not fixed).
+  SET DEBUG_SYNC= 'now WAIT_FOR locked';
+  #
+  # This should give no result. But it will if the bug exists.
+  SELECT * FROM t1;
+  UNLOCK TABLES;
+  #
+      --echo connection con1
+      connection con1;
+      reap;
+      disconnect con1;
+  #
+  --echo connection default
+  connection default;
+  # Clear debug_sync signal.
+  SET DEBUG_SYNC= 'RESET';
+  DROP TABLE t1;
+  #
+  # Show that truncate of child table waits while parent table is used.
+  CREATE TABLE t1 (c1 REAL) ENGINE=MyISAM;
+  CREATE TABLE t2 (c1 REAL) ENGINE=MyISAM;
+  INSERT INTO t1 VALUES(0.1);
+  INSERT INTO t2 VALUES(0.2);
+  CREATE TABLE t3 (c1 REAL) ENGINE=MRG_MYISAM UNION=(t1,t2);
+  #
+      --echo connection con1
+      connect (con1,localhost,root,,);
+      # When reaching acos(), send 'select' and wait for truncated.
+      SET DEBUG_SYNC= 'before_acos_function
+                       SIGNAL select WAIT_FOR truncated';
+      send SELECT ACOS(c1) FROM t3;
+  #
+  --echo connection default
+  connection default;
+  # Wait for con1 to reach acos().
+  SET DEBUG_SYNC= 'now WAIT_FOR select';
+  #
+  # With bug fix present, TRUNCATE runs into wait_for_locked_table_names().
+  SET DEBUG_SYNC= 'before_wait_locked_tname SIGNAL truncated';
+  TRUNCATE TABLE t1;
+  #
+  # With bug fix not present, we need to signal after TRUNCATE.
+  SET DEBUG_SYNC= 'now SIGNAL truncated';
+  #
+      --echo connection con1
+      connection con1;
+      # In non-debug server, the order of select and truncate is undetermined.
+      # So we may have one or two rows here.
+      --disable_result_log
+      reap;
+      --enable_result_log
+      disconnect con1;
+  #
+  --echo connection default
+  connection default;
+  SELECT ACOS(c1) FROM t3;
+  # Clear debug_sync signal.
+  SET DEBUG_SYNC= 'RESET';
+  DROP TABLE t1, t2, t3;
+}
+
+#
+# In-depth test.
+CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
+CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
+    --echo connection con1
+    connect (con1,localhost,root,,);
+    # Wait for flush before attaching children.
+    SET DEBUG_SYNC= 'before_myisammrg_attach
+                     SIGNAL attach WAIT_FOR flushed';
+    send INSERT INTO m1 VALUES (2);
+--echo connection default;
+connection default;
+#
+# Wait for con1 to reach attach_merge_children(), then flush and signal.
+SET DEBUG_SYNC= 'now WAIT_FOR attach';
+SET DEBUG_SYNC= 'after_flush_unlock SIGNAL flushed';
+FLUSH TABLE m1;
+#
+    --echo connection con1
+    connection con1;
+    reap;
+    disconnect con1;
+--echo connection default;
+connection default;
+SELECT * FROM m1;
+# Clear debug_sync signal.
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE m1, t1;
+#
+# Test derived from test program for
+# Bug#30273 - merge tables: Can't lock file (errno: 155)
+# Second test try to step in between lock_count() and store_lock().
+#
+#
+# This test does not work in 6.0 because FLUSH TABLE does not use
+# table locks to find old tables. hence it does not call lock_count()
+# nor store_lock().
+if (0)
+{
+  CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
+  CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
+      --echo connection con1
+      connect (con1,localhost,root,,);
+      # When reaching attach_merge_children(), signal attach and
+      # wait for store_lock1 before attaching children.
+      # Then run through attach of children until store_lock().
+      # Signal store_lock2 and wait for flushed.
+      SET DEBUG_SYNC= 'before_myisammrg_attach
+                       SIGNAL attach WAIT_FOR store_lock1';
+      SET DEBUG_SYNC= 'before_myisammrg_store_lock
+                       SIGNAL store_lock2 WAIT_FOR flushed';
+      send INSERT INTO m1 VALUES (2);
+  --echo connection default;
+  connection default;
+  #
+  # Wait for con1 to reach attach.
+  SET DEBUG_SYNC= 'now WAIT_FOR attach';
+  # Run until myisammrg store_lock(),
+  # then signal store_lock1 and
+  # wait for con1 to go through attach until store_lock() (store_lock2),
+  # then flush and signal flushed.
+  SET DEBUG_SYNC= 'before_myisammrg_store_lock
+                   SIGNAL store_lock1 WAIT_FOR store_lock2';
+  SET DEBUG_SYNC= 'after_flush_unlock SIGNAL flushed';
+  FLUSH TABLE m1;
+  #
+      --echo connection con1
+      connection con1;
+      reap;
+      disconnect con1;
+  --echo connection default;
+  connection default;
+  SELECT * FROM m1;
+  # Clear debug_sync signal.
+  SET DEBUG_SYNC= 'RESET';
+  DROP TABLE m1, t1;
+}
+
+#
+# Coverage test for mysql_lock_retry hit limit.
+# Similar test as for Bug#26379, Problem #3.
+# But mysql_lock_retry hit limit set.
+#
+#
+# This test does not work in 6.0 because the table locking scheme
+# changed a lot. The above problem cannot happen any more.
+# Some of the places where the sync points were placed, don't exist
+# any more.
+if (0)
+{
+  CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+  LOCK TABLE t1 WRITE;
+  #
+      --echo connection con1
+      connect (con1,localhost,root,,);
+      SET DEBUG_SYNC= 'mysql_lock_retry HIT_LIMIT 1';
+      # After open, wait for flush.
+      SET DEBUG_SYNC= 'before_lock_tables_takes_lock
+                       SIGNAL opened WAIT_FOR flushed';
+      # If bug is not fixed, INSERT will succeed. Pretend locked.
+      SET DEBUG_SYNC= 'after_insert SIGNAL locked';
+      # If bug is fixed, INSERT will go into wait_for_lock.
+      # Retain action after use. First used by general_log.
+      SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2';
+      # Alternatively INSERT can wait for refresh in open_table().
+      SET DEBUG_SYNC= 'before_open_table_wait_refresh SIGNAL locked';
+      send INSERT INTO t1 VALUES (1);
+  #
+  --echo connection default;
+  connection default;
+  #
+  # Wait until INSERT opened the table.
+  SET DEBUG_SYNC= 'now WAIT_FOR opened';
+  #
+  # Let INSERT exploit the gap when flush waits wthout lock
+  # for other threads to close the tables.
+  SET DEBUG_SYNC= 'after_flush_unlock SIGNAL flushed EXECUTE 2';
+  FLUSH TABLES;
+  #
+  # Wait until INSERT is locked (bug fixed) or finished (bug not fixed).
+  SET DEBUG_SYNC= 'now WAIT_FOR locked';
+  UNLOCK TABLES;
+  #
+      --echo connection con1
+      connection con1;
+      # Succeeds if DEBUG_SYNC is disabled
+      --error 0, ER_DEBUG_SYNC_HIT_LIMIT
+      reap;
+      disconnect con1;
+  #
+  --echo connection default;
+  connection default;
+  #
+  # Clear debug_sync signal.
+  SET DEBUG_SYNC= 'RESET';
+  DROP TABLE t1;
+}
+
+#
+# Coverage test for wait_for_lock.
+#
+CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
+LOCK TABLE t1 WRITE;
+#
+    --echo connection con1
+    connect (con1,localhost,root,,);
+    SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked';
+    send INSERT INTO t1 VALUES (1);
+#
+--echo connection default;
+connection default;
+#
+# Wait until INSERT is locked.
+SET DEBUG_SYNC= 'now WAIT_FOR locked';
+UNLOCK TABLES;
+#
+    --echo connection con1
+    connection con1;
+    reap;
+    disconnect con1;
+#
+--echo connection default;
+connection default;
+#
+# Clear debug_sync signal.
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
+--echo #
+--echo # Check that a non-temporary child is properly locked under a
+--echo # temporary parent.
+--echo #
+CREATE TABLE t1 (c1 REAL) ENGINE=MyISAM;
+CREATE TEMPORARY TABLE m1 (c1 REAL) ENGINE=MRG_MYISAM UNION=(t1);
+INSERT INTO t1 VALUES(0.1);
+#
+--echo # connection default, lock the tables.
+# When reaching acos(), send 'selecting' and wait for 'finish'.
+SET DEBUG_SYNC= 'before_acos_function SIGNAL selecting WAIT_FOR finish';
+send SELECT ACOS(c1) FROM m1;
+#
+    --echo #
+    --echo # connection con1, try to insert.
+    --connect (con1,localhost,root,,)
+    # Wait until select started (and thus locked tables)
+    SET DEBUG_SYNC= 'now WAIT_FOR selecting';
+    # When reaching wait_for_lock(), send 'lockwait'.
+    SET DEBUG_SYNC= 'wait_for_lock SIGNAL lockwait';
+    send INSERT INTO t1 VALUES(2.1);
+#
+        --echo #
+        --echo # connection con2, show state.
+        --connect (con2,localhost,root,,)
+        # Wait until INSERT reached wait_for_lock().
+        SET DEBUG_SYNC= 'now WAIT_FOR lockwait';
+        SELECT STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;
+        # Let SELECT continue. INSERT will follow after it.
+        SET DEBUG_SYNC= 'now SIGNAL finish';
+        --disconnect con2
+#
+--echo #
+--echo # connection default, retrieve SELECT result.
+--connection default
+reap;
+#
+    --echo #
+    --echo # connection con1, retrieve INSERT result.
+    --connection con1
+    reap;
+    --disconnect con1
+#
+--echo #
+--echo #connection default, show table contents.
+--connection default
+SELECT * FROM m1;
+#
+# Cleanup.
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE m1;
+DROP TABLE t1;
+
+#
+# Restore to default setting.
+#
+SET GLOBAL concurrent_insert= DEFAULT;
+

=== modified file 'storage/myisammrg/ha_myisammrg.cc'
--- a/storage/myisammrg/ha_myisammrg.cc	2010-02-15 16:35:53 +0000
+++ b/storage/myisammrg/ha_myisammrg.cc	2010-03-05 15:26:04 +0000
@@ -567,15 +567,17 @@ static MI_INFO *myisammrg_attach_childre
     param->need_compat_check= TRUE;
 
   /*
-    If parent is temporary, children must be temporary too and vice
-    versa. This check must be done for every child on every open because
-    the table def version can overlap between temporary and
-    non-temporary tables. We need to detect the case where a
-    non-temporary table has been replaced with a temporary table of the
-    same version. Or vice versa. A very unlikely case, but it could
-    happen.
+    If child is temporary, parent must be temporary as well. Other
+    parent/child combinations are allowed. This check must be done for
+    every child on every open because the table def version can overlap
+    between temporary and non-temporary tables. We need to detect the
+    case where a non-temporary table has been replaced with a temporary
+    table of the same version. Or vice versa. A very unlikely case, but
+    it could happen. (Note that the condition was different from
+    5.1.23/6.0.4(Bug#19627) to 5.4.4(Bug#36171): child->s->tmp_table !=
+    parent->s->tmp_table. Tables were required to have the same status.)
   */
-  if (child->s->tmp_table != parent->s->tmp_table)
+  if (child->s->tmp_table && !parent->s->tmp_table)
   {
     DBUG_PRINT("error", ("temporary table mismatch parent: %d  child: %d",
                          parent->s->tmp_table, child->s->tmp_table));


Attachment: [text/bzr-bundle] bzr/ingo.struewing@sun.com-20100305152604-058fogo9e36e74uk.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (ingo.struewing:3123)Bug#36171Ingo Struewing5 Mar