#At file:///home2/mydev/bzrroot/mysql-6.0-bug36171-1/ based on revid:rafal.somla@stripped
2851 Ingo Struewing 2009-10-28
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.
@ mysql-test/r/merge.result
Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
Updated test result.
@ mysql-test/r/merge_sync.result
Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
Renamed test ('-' -> '_').
Added test result.
@ mysql-test/t/merge.test
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
Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
Renamed test ('-' -> '_').
Added test case.
@ storage/myisammrg/ha_myisammrg.cc
Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
Changed constraint for temporary state of tables.
renamed:
mysql-test/r/merge-sync.result => mysql-test/r/merge_sync.result
mysql-test/t/merge-sync.test => mysql-test/t/merge_sync.test
modified:
mysql-test/r/merge.result
mysql-test/t/merge.test
storage/myisammrg/ha_myisammrg.cc
mysql-test/r/merge_sync.result
mysql-test/t/merge_sync.test
=== modified file 'mysql-test/r/merge.result'
--- a/mysql-test/r/merge.result 2009-07-21 16:53:40 +0000
+++ b/mysql-test/r/merge.result 2009-10-28 18:36:59 +0000
@@ -586,7 +586,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);
@@ -615,7 +617,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);
@@ -2319,4 +2323,620 @@ c1
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;
End of 6.0 tests
=== renamed file 'mysql-test/r/merge-sync.result' => 'mysql-test/r/merge_sync.result'
--- a/mysql-test/r/merge-sync.result 2008-07-09 14:27:18 +0000
+++ b/mysql-test/r/merge_sync.result 2009-10-28 18:36:59 +0000
@@ -67,4 +67,45 @@ 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 2009-07-21 16:53:40 +0000
+++ b/mysql-test/t/merge.test 2009-10-28 18:36:59 +0000
@@ -226,7 +226,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);
@@ -258,7 +257,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.
@@ -1756,6 +1754,348 @@ DROP TRIGGER t2_au;
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 End of 6.0 tests
--disable_result_log
=== renamed file 'mysql-test/t/merge-sync.test' => 'mysql-test/t/merge_sync.test'
--- a/mysql-test/t/merge-sync.test 2008-07-09 14:27:18 +0000
+++ b/mysql-test/t/merge_sync.test 2009-10-28 18:36:59 +0000
@@ -410,6 +410,61 @@ connection default;
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.
#
=== modified file 'storage/myisammrg/ha_myisammrg.cc'
--- a/storage/myisammrg/ha_myisammrg.cc 2009-05-27 10:46:16 +0000
+++ b/storage/myisammrg/ha_myisammrg.cc 2009-10-28 18:36:59 +0000
@@ -518,15 +518,17 @@ static MI_INFO *myisammrg_attach_childre
ha_myrg->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-20091028183659-6kmv1k3gdq6cpg4d.bundle
| Thread |
|---|
| • bzr commit into mysql-5.4 branch (ingo.struewing:2851) Bug#36171 | Ingo Struewing | 28 Oct |
