set @save_default_storage_engine=@@global.default_storage_engine; set global default_storage_engine=myisam; set session default_storage_engine=myisam; create table t1 (a int not null primary key auto_increment, message char(20)); create table t2 (a int not null primary key auto_increment, message char(20)); INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1"); INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2"); create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(t1,t2); select * from t3; a b 1 Testing 2 table 3 t1 1 Testing 2 table 3 t2 select * from t3 order by a desc; a b 3 t1 3 t2 2 table 2 table 1 Testing 1 Testing drop table t3; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,test.t2); explain select * from t3 where a < 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range a a 4 NULL 17 Using where explain select * from t3 where a > 10 and a < 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range a a 4 NULL 17 Using where select * from t3 where a = 10; a b 10 Testing 10 Testing select * from t3 where a < 10; a b 1 Testing 1 Testing 2 table 2 table 3 t1 3 t2 4 Testing 4 Testing 5 table 5 table 6 t2 6 t1 7 Testing 7 Testing 8 table 8 table 9 t2 9 t2 select * from t3 where a > 10 and a < 20; a b 11 table 11 table 12 t1 12 t1 13 Testing 13 Testing 14 table 14 table 15 t2 15 t2 16 Testing 16 Testing 17 table 17 table 18 t2 18 t2 19 Testing 19 Testing explain select a from t3 order by a desc limit 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 index NULL a 4 NULL 10 Using index select a from t3 order by a desc limit 10; a 699 698 697 696 695 694 693 692 691 690 select a from t3 order by a desc limit 300,10; a 416 415 415 414 414 413 413 412 412 411 delete from t3 where a=3; select * from t3 where a < 10; a b 1 Testing 1 Testing 2 table 2 table 4 Testing 4 Testing 5 table 5 table 6 t2 6 t1 7 Testing 7 Testing 8 table 8 table 9 t2 9 t2 delete from t3 where a >= 6 and a <= 8; select * from t3 where a < 10; a b 1 Testing 1 Testing 2 table 2 table 4 Testing 4 Testing 5 table 5 table 9 t2 9 t2 update t3 set a=3 where a=9; select * from t3 where a < 10; a b 1 Testing 1 Testing 2 table 2 table 3 t2 3 t2 4 Testing 4 Testing 5 table 5 table update t3 set a=6 where a=7; select * from t3 where a < 10; a b 1 Testing 1 Testing 2 table 2 table 3 t2 3 t2 4 Testing 4 Testing 5 table 5 table show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL, `b` char(20) DEFAULT NULL, KEY `a` (`a`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`t2`) create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2); select * from t4; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist alter table t4 add column c int; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist flush tables; select * from t4; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist create database mysqltest; create table mysqltest.t6 (a int not null primary key auto_increment, message char(20)); create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,mysqltest.t6); show create table t5; Table Create Table t5 CREATE TABLE `t5` ( `a` int(11) NOT NULL, `b` char(20) DEFAULT NULL, KEY `a` (`a`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`mysqltest`.`t6`) alter table t5 engine=myisam; drop table t5, mysqltest.t6; drop database mysqltest; drop table t4,t3,t1,t2; create table t1 (c char(10)) engine=myisam; create table t2 (c char(10)) engine=myisam; create table t3 (c char(10)) union=(t1,t2) engine=merge; insert into t1 (c) values ('test1'); insert into t1 (c) values ('test1'); insert into t1 (c) values ('test1'); insert into t2 (c) values ('test2'); insert into t2 (c) values ('test2'); insert into t2 (c) values ('test2'); select * from t3; c test1 test1 test1 test2 test2 test2 select * from t3; c test1 test1 test1 test2 test2 test2 delete from t3 where 1=1; select * from t3; c select * from t1; c drop table t3,t2,t1; CREATE TABLE t1 (incr int not null, othr int not null, primary key(incr)); CREATE TABLE t2 (incr int not null, othr int not null, primary key(incr)); CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr)) ENGINE=MERGE UNION=(t1,t2); SELECT * from t3; incr othr INSERT INTO t1 VALUES ( 1,10),( 3,53),( 5,21),( 7,12),( 9,17); INSERT INTO t2 VALUES ( 2,24),( 4,33),( 6,41),( 8,26),( 0,32); INSERT INTO t1 VALUES (11,20),(13,43),(15,11),(17,22),(19,37); INSERT INTO t2 VALUES (12,25),(14,31),(16,42),(18,27),(10,30); SELECT * from t3 where incr in (1,2,3,4) order by othr; incr othr 1 10 2 24 4 33 3 53 alter table t3 UNION=(t1); select count(*) from t3; count(*) 10 alter table t3 UNION=(t1,t2); select count(*) from t3; count(*) 20 alter table t3 ENGINE=MYISAM; select count(*) from t3; count(*) 20 drop table t3; CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr)) ENGINE=MERGE UNION=(t1,t2); show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL, `othr` int(11) NOT NULL, PRIMARY KEY (`incr`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`t2`) alter table t3 drop primary key; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL, `othr` int(11) NOT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`t2`) drop table t3,t2,t1; create table t1 (a int not null, key(a)) engine=merge; select * from t1; a drop table t1; create table t1 (a int not null, b int not null, key(a,b)); create table t2 (a int not null, b int not null, key(a,b)); create table t3 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2); insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6); insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6); flush tables; select * from t3 where a=1 order by b limit 2; a b 1 1 1 2 drop table t3,t1,t2; create table t1 (a int not null, b int not null auto_increment, primary key(a,b)); create table t2 (a int not null, b int not null auto_increment, primary key(a,b)); create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO; create table t4 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`t2`) show create table t5; Table Create Table t5 CREATE TABLE `t5` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=FIRST UNION=(`t1`,`t2`) show create table t6; Table Create Table t6 CREATE TABLE `t6` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`) insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL); insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL); select * from t3 order by b,a limit 3; a b select * from t4 order by b,a limit 3; a b 1 1 2 1 1 2 select * from t5 order by b,a limit 3,3; a b 2 2 1 3 2 3 select * from t6 order by b,a limit 6,3; a b 1 4 2 4 insert into t5 values (5,1),(5,2); insert into t6 values (6,1),(6,2); select * from t1 order by a,b; a b 1 1 1 2 1 3 1 4 5 1 5 2 select * from t2 order by a,b; a b 2 1 2 2 2 3 2 4 6 1 6 2 select * from t4 order by a,b; a b 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 5 1 5 2 6 1 6 2 insert into t3 values (3,1),(3,2),(3,3),(3,4); select * from t3 order by a,b; a b 3 1 3 2 3 3 3 4 alter table t4 UNION=(t1,t2,t3); show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t1`,`t2`,`t3`) select * from t4 order by a,b; a b 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 3 1 3 2 3 3 3 4 5 1 5 2 6 1 6 2 alter table t4 INSERT_METHOD=FIRST; show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, KEY `a` (`a`,`b`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`) insert into t4 values (4,1),(4,2); select * from t1 order by a,b; a b 1 1 1 2 1 3 1 4 4 1 4 2 5 1 5 2 select * from t2 order by a,b; a b 2 1 2 2 2 3 2 4 6 1 6 2 select * from t3 order by a,b; a b 3 1 3 2 3 3 3 4 select * from t4 order by a,b; a b 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 3 1 3 2 3 3 3 4 4 1 4 2 5 1 5 2 6 1 6 2 select * from t5 order by a,b; a b 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 4 1 4 2 5 1 5 2 6 1 6 2 select 1; 1 1 insert into t5 values (1,NULL),(5,NULL); insert into t6 values (2,NULL),(6,NULL); select * from t1 order by a,b; a b 1 1 1 2 1 3 1 4 1 5 4 1 4 2 5 1 5 2 5 3 select * from t2 order by a,b; a b 2 1 2 2 2 3 2 4 2 5 6 1 6 2 6 3 select * from t5 order by a,b; a b 1 1 1 2 1 3 1 4 1 5 2 1 2 2 2 3 2 4 2 5 4 1 4 2 5 1 5 2 5 3 6 1 6 2 6 3 select * from t6 order by a,b; a b 1 1 1 2 1 3 1 4 1 5 2 1 2 2 2 3 2 4 2 5 4 1 4 2 5 1 5 2 5 3 6 1 6 2 6 3 insert into t1 values (99,NULL); select * from t4 where a+0 > 90; a b 99 1 insert t5 values (1,1); ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY' insert t6 values (2,1); ERROR 23000: Duplicate entry '2-1' for key 'PRIMARY' insert t5 values (1,1) on duplicate key update b=b+10; insert t6 values (2,1) on duplicate key update b=b+20; select * from t5 where a < 3; a b 1 2 1 3 1 4 1 5 1 11 2 2 2 3 2 4 2 5 2 21 drop table t6, t5, t4, t3, t2, t1; CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,1), (2,1); CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,2), (2,2); CREATE TABLE t3 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) ENGINE=MRG_MyISAM UNION=(t1,t2); select max(b) from t3 where a = 2; max(b) 2 select max(b) from t1 where a = 2; max(b) 1 drop table t3,t1,t2; create table t1 (a int not null); 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); select * from t3; a 1 2 create temporary table t4 (a int not null); create temporary table t5 (a int not null); insert into t4 values (1); insert into t5 values (2); create temporary table t6 (a int not null) ENGINE=MERGE UNION=(t4,t5); select * from t6; 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; a 1 2 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 of type '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 '', fileset_root_id tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (fileset_id,file_code), KEY files (fileset_id,fileset_root_id) ) ENGINE=MyISAM; INSERT INTO t1 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1), (2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1), (2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1); CREATE TABLE t2 ( fileset_id tinyint(3) unsigned NOT NULL default '0', file_code varchar(32) NOT NULL default '', fileset_root_id tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (fileset_id,file_code), KEY files (fileset_id,fileset_root_id) ) ENGINE=MRG_MyISAM UNION=(t1); EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range PRIMARY PRIMARY 35 NULL 6 Using where EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range PRIMARY,files PRIMARY 35 NULL 6 Using where EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,files PRIMARY 35 NULL 6 Using index condition EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 AND file_code = '0000000115' LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY,files PRIMARY 35 const,const 1 DROP TABLE t2, t1; create table t1 (x int, y int, index xy(x, y)); create table t2 (x int, y int, index xy(x, y)); create table t3 (x int, y int, index xy(x, y)) engine=merge union=(t1,t2); insert into t1 values(1, 2); insert into t2 values(1, 3); select * from t3 where x = 1 and y < 5 order by y; x y 1 2 1 3 select * from t3 where x = 1 and y < 5 order by y desc; x y 1 3 1 2 drop table t1,t2,t3; create table t1 (a int); 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: 'test.t3' is not of type 'BASE TABLE' create table t3 engine=merge union=(t1, t2) select * from t2; ERROR HY000: 'test.t3' is not of type 'BASE TABLE' create table t3 engine=merge union=(t1, t2) select (select max(a) from t2); ERROR HY000: 'test.t3' is not of type 'BASE TABLE' drop table t1, t2; create table t1 ( a double(14,4), b varchar(10), index (a,b) ) engine=merge union=(t2,t3); create table t2 ( a double(14,4), b varchar(10), index (a,b) ) engine=myisam; create table t3 ( a double(14,4), b varchar(10), index (a,b) ) engine=myisam; insert into t2 values ( null, ''); insert into t2 values ( 9999999999.999, ''); insert into t3 select * from t2; select min(a), max(a) from t1; min(a) max(a) 9999999999.9990 9999999999.9990 flush tables; select min(a), max(a) from t1; min(a) max(a) 9999999999.9990 9999999999.9990 drop table t1, t2, t3; create table t1 (a int,b int,c int, index (a,b,c)); create table t2 (a int,b int,c int, index (a,b,c)); create table t3 (a int,b int,c int, index (a,b,c)) engine=merge union=(t1 ,t2); insert into t1 (a,b,c) values (1,1,0),(1,2,0); insert into t2 (a,b,c) values (1,1,1),(1,2,1); explain select a,b,c from t3 force index (a) where a=1 order by a,b,c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref a a 5 const 4 Using where; Using index select a,b,c from t3 force index (a) where a=1 order by a,b,c; a b c 1 1 0 1 1 1 1 2 0 1 2 1 explain select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref a a 5 const 4 Using where; Using index select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc; a b c 1 2 1 1 2 0 1 1 1 1 1 0 show index from t3; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t3 1 a 1 a A NULL NULL NULL YES BTREE NO t3 1 a 2 b A NULL NULL NULL YES BTREE NO t3 1 a 3 c A NULL NULL NULL YES BTREE NO drop table t1, t2, t3; CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), UNIQUE (b) ) ENGINE=MyISAM; CREATE TABLE t2 ( a INT AUTO_INCREMENT, b VARCHAR(10), INDEX (a), INDEX (b) ) ENGINE=MERGE UNION (t1) INSERT_METHOD=FIRST; INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=2; INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=3; SELECT b FROM t2; b 3 DROP TABLE t1, t2; create table t1(a int); create table t2(a int); insert into t1 values (1); insert into t2 values (2); create table t3 (a int) engine=merge union=(t1, t2) insert_method=first; select * from t3; a 1 2 insert t2 select * from t2; select * from t2; a 2 2 insert t3 select * from t1; select * from t3; a 1 1 2 2 insert t1 select * from t3; select * from t1; a 1 1 1 1 2 2 select * from t2; a 2 2 select * from t3; a 1 1 1 1 2 2 2 2 check table t1, t2; Table Op Msg_type Msg_text test.t1 check status OK test.t2 check status OK drop table t1, t2, t3; CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(2),(1); CREATE TABLE t2(a INT, KEY(a)) ENGINE=MERGE UNION=(t1); SELECT * FROM t2 WHERE a=2; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE t1, t2; CREATE TABLE t1(a INT) ENGINE=MEMORY; CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t1); SELECT * FROM t2; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist 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 DROP TABLE t2; CREATE TABLE t1(a INT, b TEXT); CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1); SELECT * FROM tm1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE t1, tm1; CREATE TABLE t1(a SMALLINT, b SMALLINT); CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1); SELECT * FROM tm1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE t1, tm1; CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(a, b)); CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1); SELECT * FROM tm1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE t1, tm1; CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(b)); CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1); SELECT * FROM tm1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE t1, tm1; CREATE TABLE t1(c1 VARCHAR(1)); CREATE TABLE m1 LIKE t1; ALTER TABLE m1 ENGINE=MERGE UNION=(t1); SELECT * FROM m1; c1 DROP TABLE t1, m1; CREATE TABLE t1(c1 VARCHAR(4), c2 TINYINT, c3 TINYINT, c4 TINYINT, c5 TINYINT, c6 TINYINT, c7 TINYINT, c8 TINYINT, c9 TINYINT); CREATE TABLE m1 LIKE t1; ALTER TABLE m1 ENGINE=MERGE UNION=(t1); SELECT * FROM m1; c1 c2 c3 c4 c5 c6 c7 c8 c9 DROP TABLE t1, m1; CREATE TABLE t1 (a VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_german2_ci, b INT, INDEX(a,b)); CREATE TABLE t2 LIKE t1; CREATE TABLE t3 LIKE t1; ALTER TABLE t3 ENGINE=MERGE UNION=(t1,t2); INSERT INTO t1 VALUES ('ss',1); INSERT INTO t2 VALUES ('ss',2),(0xDF,2); SELECT COUNT(*) FROM t3 WHERE a=0xDF AND b=2; COUNT(*) 2 DROP TABLE t1,t2,t3; create table t1 (b bit(1)); create table t2 (b bit(1)); create table tm (b bit(1)) engine = merge union = (t1,t2); select * from tm; b drop table tm, t1, t2; create table t1 (a int) insert_method = last engine = merge; insert into t1 values (1); ERROR HY000: Table 't1' is read only create table t2 (a int) engine = myisam; alter table t1 union (t2); insert into t1 values (1); alter table t1 insert_method = no; insert into t1 values (1); ERROR HY000: Table 't1' is read only 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 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 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 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 Corrupt CREATE TABLE t2(a BLOB); SELECT * FROM tm1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or 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 Corrupt ALTER TABLE t2 MODIFY a INT; SELECT * FROM tm1; a CHECK TABLE tm1; Table Op Msg_type Msg_text test.tm1 check status OK DROP TABLE tm1, t1, t2; CREATE TABLE t1(c1 INT); CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; Warnings: Note 1050 Table 't1' already exists DROP TABLE t1, t2; CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM; CREATE TABLE t2 LIKE t1; INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); INSERT INTO t1 SELECT * FROM t2; INSERT INTO t1 SELECT * FROM t2; CREATE TABLE t3 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MERGE UNION(t1); SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; id ref ref 4 4 5 4 4 5 4 4 5 4 4 5 SELECT * FROM t3; id ref 1 3 2 1 3 2 4 5 4 4 1 3 2 1 3 2 4 5 4 4 DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; SELECT * FROM t3; id ref 1 3 2 1 3 2 4 5 1 3 2 1 3 2 4 5 DROP TABLE t1, t2, t3; CREATE TABLE t1(a INT); CREATE TABLE m1(a INT) ENGINE=MERGE; SHOW CREATE TABLE m1; Table Create Table m1 CREATE TABLE `m1` ( `a` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE m1; CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(); SHOW CREATE TABLE m1; Table Create Table m1 CREATE TABLE `m1` ( `a` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ALTER TABLE m1 UNION=(t1); ALTER TABLE m1 UNION=(); SHOW CREATE TABLE m1; Table Create Table m1 CREATE TABLE `m1` ( `a` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1, m1; CREATE TABLE t1(a INT); CREATE TABLE t2(a VARCHAR(10)); CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(t1, t2); CREATE TABLE m2(a INT) ENGINE=MERGE UNION=(t1); SELECT * FROM t1; a SELECT * FROM m1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist SELECT * FROM m2; a DROP TABLE t1, t2, m1, m2; 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; SELECT * FROM t3; c1 UNLOCK TABLES; SELECT * FROM t1; c1 SELECT * FROM t2; c1 # # Truncate child table under locked tables. LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE; INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (2); TRUNCATE TABLE t1; SELECT * FROM t3; c1 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; SELECT * FROM t3; c1 SELECT * FROM t1; c1 SELECT * FROM t2; c1 # # Truncate temporary child table under locked tables. INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (2); TRUNCATE TABLE t1; SELECT * FROM t3; c1 2 SELECT * FROM t1; c1 SELECT * FROM t2; c1 2 UNLOCK TABLES; DROP TABLE t1, t2, t3, t4; connect con1,localhost,root,,; connect con2,localhost,root,,; connection default; CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST; REPAIR TABLE t1; connection con1; INSERT INTO t2 VALUES (1); connection default; 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; connection con1; INSERT INTO t2 VALUES (1); connection default; REPAIR TABLE t1; Table Op Msg_type Msg_text test.t1 repair status OK UNLOCK TABLES; connection con1; connection default; DROP TABLE t1, t2; CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; LOCK TABLE t1 WRITE; connection con1; INSERT INTO t1 VALUES (1); connection default; FLUSH TABLES; FLUSH TABLES; SELECT * FROM t1; c1 UNLOCK TABLES; connection con1; connection default; DROP TABLE t1; disconnect con1; disconnect con2; # # 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 of type 'BASE TABLE' SHOW CREATE TABLE t3; ERROR 42S02: Table 'test.t3' doesn't exist DROP TABLE t1, t2; # # Bug#37371 "CREATE TABLE LIKE merge loses UNION parameter" # Demonstrate that this is no longer the case. # # 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 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`) INSERT INTO t4 VALUES (4); DROP TABLE t4; # # 1. Create like with locked tables. LOCK TABLES t3 WRITE, t2 WRITE, t1 WRITE; CREATE TABLE t4 LIKE t3; ERROR HY000: Table 't4' was not locked with LOCK TABLES 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 # Temporary tables can be created in spite of LOCK TABLES. # If the temporary MERGE table uses the locked children only, # it can even be used. CREATE TEMPORARY TABLE t4 LIKE t3; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TEMPORARY TABLE `t4` ( `c1` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`) INSERT INTO t4 VALUES (4); UNLOCK TABLES; INSERT INTO t4 VALUES (4); 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 4 4 4 RENAME TABLE t2 TO t5; SELECT * FROM t3 ORDER BY c1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist RENAME TABLE t5 TO t2; SELECT * FROM t3 ORDER BY c1; c1 1 2 3 4 4 4 # # 3. Normal rename with locked tables. LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE; SELECT * FROM t3 ORDER BY c1; c1 1 2 3 4 4 4 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 4 4 4 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 4 4 4 UNLOCK TABLES; # # 4. Alter table rename. ALTER TABLE t2 RENAME TO t5; SELECT * FROM t3 ORDER BY c1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist ALTER TABLE t5 RENAME TO t2; SELECT * FROM t3 ORDER BY c1; c1 1 2 3 4 4 4 # # 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 't2' 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 4 4 4 # # 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 4 4 4 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 4 4 4 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 4 4 4 # # 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 4 4 4 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 HY000: Table 't1' was not locked with LOCK TABLES SELECT * FROM t1; ERROR HY000: Table 't1' was not locked with LOCK TABLES 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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_ai1 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_ai1; CHECK TABLE t3; Table Op Msg_type Msg_text test.t3 check status OK # Trigger on parent under LOCK TABLES LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; CREATE TRIGGER t4_ai2 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_ai2; UNLOCK TABLES; CHECK TABLE t3; Table Op Msg_type Msg_text test.t3 check status OK # # Trigger on child DELETE FROM t4 WHERE c1 = 4; CREATE TRIGGER t3_ai3 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_ai3; CHECK TABLE t3; Table Op Msg_type Msg_text test.t3 check status OK # Trigger on child under LOCK TABLES LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; CREATE TRIGGER t3_ai4 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_ai4; UNLOCK TABLES; CHECK TABLE t3; Table Op Msg_type Msg_text test.t3 check status OK # # Trigger with table use on child DELETE FROM t4 WHERE c1 = 4; CREATE TRIGGER t3_ai5 AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22); SELECT COUNT(*) FROM t2; COUNT(*) 1 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_ai5; UNLOCK TABLES; CHECK TABLE t2,t3; Table Op Msg_type Msg_text test.t2 check status OK test.t3 check status OK # Trigger with table use on child under LOCK TABLES LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; CREATE TRIGGER t3_ai6 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_ai6; UNLOCK TABLES; check table t2,t3,t4; Table Op Msg_type Msg_text test.t2 check status OK test.t3 check status OK test.t4 check status OK DELETE FROM t4 WHERE c1 = 22; DELETE FROM t4 WHERE c1 = 33; # Trigger with table use on child under different LOCK TABLES DELETE FROM t4 WHERE c1 = 4; LOCK TABLES t4 WRITE,t3 WRITE, t2 WRITE, t1 WRITE; CREATE TRIGGER t3_ai7 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 DROP TRIGGER t3_ai7; UNLOCK TABLES; check table t2,t3,t4; Table Op Msg_type Msg_text test.t2 check status OK test.t3 check status OK test.t4 check status OK DELETE FROM t4 WHERE c1 = 22; DELETE FROM t4 WHERE c1 = 33; # # 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 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 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 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 UNLOCK TABLES; # # Checksum # CHECKSUM TABLE t4; Table Checksum test.t4 149057747 CHECKSUM TABLE t2; Table Checksum test.t2 3700403066 SELECT * FROM t4 ORDER BY c1; c1 1 2 3 4 LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; CHECKSUM TABLE t4; Table Checksum test.t4 149057747 CHECKSUM TABLE t2; Table Checksum test.t2 3700403066 SELECT * FROM t4 ORDER BY c1; c1 1 2 3 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 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 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 @@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 connect con1,localhost,root,,; SELECT COUNT(DISTINCT a1.id) FROM t3 AS a1, t3 AS a2 WHERE a1.id = a2.id GROUP BY a2.grp; connection default; TRUNCATE TABLE t1; connection con1; disconnect con1; connection default; 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; 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; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST; SELECT table_schema, table_name, table_type, engine, version, row_format, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='tm1'; table_schema table_name table_type engine version row_format table_comment test tm1 BASE TABLE NULL NULL NULL Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist Warnings: Warning 1168 Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE tm1; CREATE TABLE t1(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM; CREATE TABLE t2(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM; CREATE TABLE t3(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM; CREATE TABLE t4(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MRG_MYISAM UNION=(t1, t2, t3); INSERT INTO t1 VALUES (1,1), (1,2),(1,3), (1,4); INSERT INTO t2 VALUES (2,1), (2,2),(2,3), (2,4); INSERT INTO t3 VALUES (3,1), (3,2),(3,3), (3,4); EXPLAIN SELECT COUNT(*) FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away EXPLAIN SELECT COUNT(*) FROM t4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away DROP TABLE t1, t2, t3, t4; CREATE TABLE t1(a INT, KEY(a)); INSERT INTO t1 VALUES(0),(1),(2),(3),(4); ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK CREATE TABLE m1(a INT, KEY(a)) ENGINE=MERGE UNION=(t1); SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; CARDINALITY 5 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; CARDINALITY 5 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; CARDINALITY 5 SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; CARDINALITY 5 DROP TABLE t1, m1; # # Bug #40675 MySQL 5.1 crash with index merge algorithm and Merge tables # # create MYISAM table t1 and insert values into it CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(1); # create MYISAM table t2 and insert values into it CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b)); INSERT INTO t2(a,b) VALUES (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), (1,2); # Create the merge table t3 CREATE TABLE t3(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b)) ENGINE=MERGE UNION=(t2) INSERT_METHOD=FIRST; # Lock tables t1 and t3 for write LOCK TABLES t1 WRITE, t3 WRITE; # Insert values into the merge table t3 INSERT INTO t3(a,b) VALUES(1,2); # select from the join of t2 and t3 (The merge table) SELECT t3.a FROM t1,t3 WHERE t3.b=2 AND t3.a=1; a 1 1 # Unlock the tables UNLOCK TABLES; # drop the created tables DROP TABLE t1, t2, t3; # insert duplicate value in child table while merge table doesn't have key create table t1 ( col1 int(10), primary key (col1) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE m1 ( col1 int(10) NOT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1); insert into m1 (col1) values (1); insert into m1 (col1) values (1); ERROR 23000: Can't write; duplicate key in table 'm1' drop table m1, t1; # # Bug#45800 crash when replacing into a merge table and there is a duplicate # # Replace duplicate value in child table when merge table doesn't have key CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM; CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); INSERT INTO m1 VALUES (666); SELECT * FROM m1; c1 666 # insert the duplicate value into the merge table REPLACE INTO m1 VALUES (666); SELECT * FROM m1; c1 666 DROP TABLE m1, t1; # Insert... on duplicate key update (with duplicate values in the table) CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM; CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); INSERT INTO m1 VALUES (666); SELECT * FROM m1; c1 666 # insert the duplicate value into the merge table INSERT INTO m1 VALUES (666) ON DUPLICATE KEY UPDATE c1=c1+1; SELECT * FROM m1; c1 667 DROP TABLE m1, t1; # Insert duplicate value on MERGE table, where, MERGE has a key but MyISAM has more keys CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1), UNIQUE (c2)); CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c1)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); INSERT INTO m1 VALUES (1,2); # insert the duplicate value into the merge table INSERT INTO m1 VALUES (3,2); ERROR 23000: Can't write; duplicate key in table 'm1' DROP TABLE m1,t1; # Try to define MERGE and MyISAM with keys on different columns CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1)); CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c2)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); # Try accessing the merge table for inserts (error occurs) INSERT INTO m1 VALUES (1,2); ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist INSERT INTO m1 VALUES (1,4); ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE m1,t1; CREATE TABLE t1 ( col1 INT(10) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE VIEW v1 as SELECT * FROM t1; CREATE TABLE m1 ( col1 INT(10) )ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(v1); #Select should detect that the child table is a view and fail. SELECT * FROM m1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP VIEW v1; DROP TABLE m1, t1; # # Bug #45796: invalid memory reads and writes when altering merge and # base tables # CREATE TABLE t1(c1 INT) ENGINE=MyISAM; CREATE TABLE m1(c1 INT) ENGINE=MERGE UNION=(t1); ALTER TABLE m1 ADD INDEX idx_c1(c1); 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 INDEX idx_c1(c1); SELECT * FROM m1; c1 DROP TABLE m1; DROP TABLE t1; # # Bug45781 infinite hang/crash in "opening tables" after handler tries to # open merge table # CREATE TABLE t1(a int)engine=myisam; CREATE TABLE t2(a int)engine=myisam; CREATE TABLE t3(a int)engine=myisam; CREATE TABLE t4(a int)engine=myisam; CREATE TABLE t5(a int)engine=myisam; CREATE TABLE t6(a int)engine=myisam; CREATE TABLE t7(a int)engine=myisam; CREATE TABLE m1(a int)engine=merge union=(t1,t2,t3,t4,t5,t6,t7); SELECT 1 FROM m1; 1 HANDLER m1 OPEN; ERROR HY000: Storage engine MRG_MyISAM of the table `test`.`m1` doesn't have this option DROP TABLE m1,t1,t2,t3,t4,t5,t6,t7; SELECT 1 FROM m1; ERROR 42S02: Table 'test.m1' doesn't exist # # Bug #46614: Assertion in show_create_trigger() # CREATE TABLE t1(a int); CREATE TABLE t2(a int); CREATE TABLE t3(a int) ENGINE = MERGE UNION(t1, t2); CREATE TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo(); SHOW CREATE TRIGGER tr1; Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created tr1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo() latin1 latin1_swedish_ci latin1_swedish_ci # DROP TRIGGER tr1; DROP TABLE t1, t2, t3; # # BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't work in 5.1.40) # CREATE DATABASE `test/1`; CREATE TABLE `test/1`.`t/1`(a INT); CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); SELECT * FROM m1; a SHOW CREATE TABLE m1; Table Create Table m1 CREATE TABLE `m1` ( `a` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`test/1`.`t/1`) DROP TABLE m1; CREATE TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); SELECT * FROM `test/1`.m1; a SHOW CREATE TABLE `test/1`.m1; Table Create Table m1 CREATE TABLE `m1` ( `a` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t/1`) DROP TABLE `test/1`.m1; DROP TABLE `test/1`.`t/1`; CREATE TEMPORARY TABLE `test/1`.`t/1`(a INT); CREATE TEMPORARY TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); SELECT * FROM m1; a SHOW CREATE TABLE m1; Table Create Table m1 CREATE TEMPORARY TABLE `m1` ( `a` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`test/1`.`t/1`) DROP TABLE m1; CREATE TEMPORARY TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); SELECT * FROM `test/1`.m1; a SHOW CREATE TABLE `test/1`.m1; Table Create Table m1 CREATE TEMPORARY TABLE `m1` ( `a` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t/1`) DROP TABLE `test/1`.m1; DROP TABLE `test/1`.`t/1`; DROP DATABASE `test/1`; CREATE TABLE `t@1`(a INT); SELECT * FROM m1; a SHOW CREATE TABLE m1; Table Create Table m1 CREATE TABLE `m1` ( `a` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`t@1`) DROP TABLE `t@1`; CREATE DATABASE `test@1`; CREATE TABLE `test@1`.`t@1`(a INT); FLUSH TABLE m1; SELECT * FROM m1; a SHOW CREATE TABLE m1; Table Create Table m1 CREATE TABLE `m1` ( `a` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci UNION=(`test@1`.`t@1`) DROP TABLE m1; DROP TABLE `test@1`.`t@1`; DROP DATABASE `test@1`; # # Bug#51494c rash with join, explain and 'sounds like' operator # CREATE TABLE t1 (a INT) ENGINE=MYISAM; INSERT INTO t1 VALUES(1); CREATE TABLE t2 (b INT NOT NULL,c INT,d INT,e BLOB NOT NULL, KEY idx0 (d, c)) ENGINE=MERGE; EXPLAIN SELECT * FROM t1 NATURAL RIGHT JOIN t2 WHERE b SOUNDS LIKE e AND d = 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t2, t1; # # Bug#46339 - crash on REPAIR TABLE merge table USE_FRM # CREATE TABLE t1 (c1 INT) ENGINE=MYISAM; CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1) INSERT_METHOD=LAST; LOCK TABLE m1 READ; REPAIR TABLE m1 USE_FRM; Table Op Msg_type Msg_text test.m1 repair Error Table 'm1' was locked with a READ lock and can't be updated test.m1 repair status Operation failed UNLOCK TABLES; REPAIR TABLE m1 USE_FRM; Table Op Msg_type Msg_text test.m1 repair note The storage engine for the table doesn't support repair DROP TABLE m1,t1; CREATE TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1); REPAIR TABLE m1 USE_FRM; Table Op Msg_type Msg_text test.m1 repair Warning Can't open table test.m1 repair error Corrupt CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM; REPAIR TABLE m1 USE_FRM; Table Op Msg_type Msg_text test.m1 repair note The storage engine for the table doesn't support repair REPAIR TABLE m1; Table Op Msg_type Msg_text test.m1 repair note The storage engine for the table doesn't support repair DROP TABLE m1, t1; CREATE TEMPORARY TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1); REPAIR TABLE m1 USE_FRM; Table Op Msg_type Msg_text test.m1 repair Error Table 'test.m1' doesn't exist test.m1 repair error Corrupt CREATE TEMPORARY TABLE t1 (f1 BIGINT) ENGINE=MyISAM; REPAIR TABLE m1 USE_FRM; Table Op Msg_type Msg_text m1 repair error Cannot repair temporary table from .frm file REPAIR TABLE m1; Table Op Msg_type Msg_text test.m1 repair note The storage engine for the table doesn't support repair DROP TABLE m1, t1; create temporary table t1_temp(i int); create temporary table tm_temp_temp (i int) engine=merge union=(t1_temp) insert_method=last; alter table tm_temp_temp insert_method=first; check table tm_temp_temp; Table Op Msg_type Msg_text test.tm_temp_temp check status OK drop temporary table t1_temp, tm_temp_temp; End of 5.1 tests # # MDEV-4277: Crash inside mi_killed_in_mariadb() with myisammrg # create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 ( i int ) engine=myisam; insert into t2 values (1),(2); create table t3 (a int, b int, filler char(100), key(a), key(b)) engine=myisam; create table t4 like t3; insert into t3 select A.a+10*B.a+100*C.a, A.a+10*B.a+100*C.a, 'filler-data-FILLER-DATA-qqq' from t1 A, t1 B, t1 C where C.a < 5; insert into t4 select A.a+10*B.a+100*C.a, A.a+10*B.a+100*C.a, 'filler-data-FILLER-DATA-qqq' from t1 A, t1 B, t1 C where C.a >= 5; create table t5 like t3; alter table t5 engine=merge; alter table t5 union(t3, t4); update t5 set b=999, a=999 where b>950; explain select * from t2, t5 where t5.a=999 and t5.b=999; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 1 SIMPLE t5 index_merge a,b a,b 5,5 NULL 6 Using intersect(a,b); Using where; Using join buffer (flat, BNL join) select * from t2, t5 where t5.a=999 and t5.b=999; i a b filler 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq 1 999 999 filler-data-FILLER-DATA-qqq 2 999 999 filler-data-FILLER-DATA-qqq drop table t5; drop table t1,t2,t3,t4; End of 5.3 tests CREATE TABLE t1(a INT, KEY(a)) ENGINE=merge; SELECT MAX(a) FROM t1; MAX(a) NULL DROP TABLE t1; # # An additional test case for Bug#27430 Crash in subquery code # when in PS and table DDL changed after PREPARE # # Test merge table with too many merge children. # set @save_table_open_cache=@@global.table_open_cache; # # Set @@global.table_open_cache to minimum # set @@global.table_open_cache=400; select @@table_open_cache; @@table_open_cache 400 set @a=null; # # Create 400 merge children # set @a=concat("create table t_parent (a int) union(", @a, ") insert_method=first engine=mrg_myisam"); prepare stmt from @a; execute stmt; prepare stmt from "select * from t_parent"; execute stmt; execute stmt; execute stmt; deallocate prepare stmt; # # Create merge parent # # # Cleanup # drop table t_parent; set @@global.table_open_cache=@save_table_open_cache; CREATE DATABASE mysql_test1; CREATE TABLE t1 ... DATA DIRECTORY=... INDEX DIRECTORY=... CREATE TABLE mysql_test1.t2 ... DATA DIRECTORY=... INDEX DIRECTORY=... CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,mysql_test1.t2) INSERT_METHOD=LAST; INSERT INTO t1 VALUES (1); INSERT INTO mysql_test1.t2 VALUES (2); SELECT * FROM m1; c1 1 2 DROP TABLE t1, mysql_test1.t2, m1; DROP DATABASE mysql_test1; CREATE TABLE t1 (c1 INT); CREATE TABLE t2 (c1 INT); INSERT INTO t1 (c1) VALUES (1); CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) INSERT_METHOD=FIRST; CREATE TABLE t3 (c1 INT); INSERT INTO t3 (c1) VALUES (1); CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM t3); CREATE VIEW v1 AS SELECT foo.c1 c1, f1() c2, bar.c1 c3, f1() c4 FROM tm1 foo, tm1 bar, t3; SELECT * FROM v1; c1 c2 c3 c4 1 1 1 1 DROP FUNCTION f1; DROP VIEW v1; DROP TABLE tm1, t1, t2, t3; CREATE TEMPORARY TABLE t1 (c1 INT); CREATE TEMPORARY TABLE t2 (c1 INT); CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) INSERT_METHOD=FIRST; CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM tm1); INSERT INTO tm1 (c1) VALUES (1); SELECT f1() FROM (SELECT 1) AS c1; f1() 1 DROP FUNCTION f1; DROP TABLE tm1, t1, t2; CREATE FUNCTION f1() RETURNS INT BEGIN CREATE TEMPORARY TABLE t1 (c1 INT); CREATE TEMPORARY TABLE t2 (c1 INT); CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2); INSERT INTO t1 (c1) VALUES (1); RETURN (SELECT MAX(c1) FROM tm1); END| SELECT f1() FROM (SELECT 1 UNION SELECT 1) c1; f1() 1 DROP FUNCTION f1; DROP TABLE tm1, t1, t2; CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; INSERT INTO tm1 VALUES (1); SELECT * FROM tm1; c1 1 DROP TABLE tm1, t1; CREATE FUNCTION f1() RETURNS INT BEGIN INSERT INTO tm1 VALUES (1); RETURN (SELECT MAX(c1) FROM tm1); END| CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; SELECT f1(); f1() 1 DROP FUNCTION f1; DROP TABLE tm1, t1; CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; LOCK TABLE tm1 WRITE; INSERT INTO tm1 VALUES (1); SELECT * FROM tm1; c1 1 UNLOCK TABLES; DROP TABLE tm1, t1; CREATE FUNCTION f1() RETURNS INT BEGIN INSERT INTO tm1 VALUES (1); RETURN (SELECT MAX(c1) FROM tm1); END| CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; LOCK TABLE tm1 WRITE; SELECT f1(); f1() 1 UNLOCK TABLES; DROP FUNCTION f1; DROP TABLE tm1, t1; CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TABLE t2 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW INSERT INTO tm1 VALUES(11); LOCK TABLE t2 WRITE; INSERT INTO t2 VALUES (2); SELECT * FROM tm1; c1 11 SELECT * FROM t2; c1 2 UNLOCK TABLES; DROP TRIGGER t2_ai; DROP TABLE tm1, t1, t2; CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; INSERT INTO tm1 VALUES (1); SELECT * FROM tm1; c1 1 DROP TABLE tm1, t1; CREATE FUNCTION f1() RETURNS INT BEGIN INSERT INTO tm1 VALUES (1); RETURN (SELECT MAX(c1) FROM tm1); END| CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; SELECT f1(); f1() 1 DROP FUNCTION f1; DROP TABLE tm1, t1; CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; CREATE TABLE t9 (c1 INT) ENGINE=MyISAM; LOCK TABLE t9 WRITE; INSERT INTO tm1 VALUES (1); SELECT * FROM tm1; c1 1 UNLOCK TABLES; DROP TABLE tm1, t1, t9; CREATE FUNCTION f1() RETURNS INT BEGIN INSERT INTO tm1 VALUES (1); RETURN (SELECT MAX(c1) FROM tm1); END| CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; CREATE TABLE t9 (c1 INT) ENGINE=MyISAM; LOCK TABLE t9 WRITE; SELECT f1(); f1() 1 UNLOCK TABLES; DROP FUNCTION f1; DROP TABLE tm1, t1, t9; CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; CREATE TABLE t2 (c1 INT) ENGINE=MyISAM; CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW INSERT INTO tm1 VALUES(11); LOCK TABLE t2 WRITE; INSERT INTO t2 VALUES (2); SELECT * FROM tm1; c1 11 SELECT * FROM t2; c1 2 UNLOCK TABLES; DROP TRIGGER t2_ai; DROP TABLE tm1, t1, t2; # # Don't allow an update of a MERGE child in a trigger # if the table's already being modified by the main # statement. # CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; CREATE TRIGGER tm1_ai AFTER INSERT ON tm1 FOR EACH ROW INSERT INTO t1 VALUES(11); LOCK TABLE tm1 WRITE, t1 WRITE; INSERT INTO tm1 VALUES (1); ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger SELECT * FROM tm1; c1 1 UNLOCK TABLES; LOCK TABLE t1 WRITE, tm1 WRITE; INSERT INTO tm1 VALUES (1); ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger SELECT * FROM tm1; c1 1 1 UNLOCK TABLES; DROP TRIGGER tm1_ai; DROP TABLE tm1, t1; # # Don't select MERGE child when trying to get a prelocked table. # # Due to a limitation demonstrated by the previous test # we can no longer use a write-locked prelocked table. # The test is kept for historical purposes. # CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; CREATE TRIGGER tm1_ai AFTER INSERT ON tm1 FOR EACH ROW SELECT max(c1) FROM t1 INTO @var; Warnings: Warning 1287 ' INTO FROM...' instead LOCK TABLE tm1 WRITE, t1 WRITE; INSERT INTO tm1 VALUES (1); SELECT * FROM tm1; c1 1 UNLOCK TABLES; LOCK TABLE t1 WRITE, tm1 WRITE; INSERT INTO tm1 VALUES (1); SELECT * FROM tm1; c1 1 1 UNLOCK TABLES; DROP TRIGGER tm1_ai; DROP TABLE tm1, t1; CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TABLE t2 (c1 INT) ENGINE=MyISAM; CREATE TABLE t3 (c1 INT) ENGINE=MyISAM; CREATE TABLE t4 (c1 INT) ENGINE=MyISAM; CREATE TABLE t5 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3,t4,t5) INSERT_METHOD=LAST; CREATE TRIGGER t2_au AFTER UPDATE ON t2 FOR EACH ROW SELECT MAX(c1) FROM t1 INTO @var; Warnings: Warning 1287 ' INTO FROM...' instead CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM t4); LOCK TABLE tm1 WRITE, t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE, t5 WRITE; INSERT INTO t1 VALUES(1); INSERT INTO t2 VALUES(2); INSERT INTO t3 VALUES(3); INSERT INTO t4 VALUES(4); INSERT INTO t5 VALUES(5); connect con1,localhost,root,,; UPDATE t2, tm1 SET t2.c1=f1(); connection default; FLUSH TABLES; FLUSH TABLES; UNLOCK TABLES; connection con1; disconnect con1; connection default; SELECT * FROM tm1; c1 1 4 3 4 5 DROP TRIGGER t2_au; DROP FUNCTION f1; DROP TABLE tm1, t1, t2, t3, t4, t5; # # Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE # CREATE TEMPORARY TABLE t1 (c1 INT); ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists,t1); OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize Error Table 'test.t_not_exists' doesn't exist test.t1 optimize Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist test.t1 optimize error Corrupt DROP TABLE t1; # # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine # More tests with TEMPORARY MERGE table and permanent children. # First without locked tables. # # 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 of type '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 of type '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 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`) SELECT * FROM m2; c1 c2 111 121 211 221 212 222 213 223 INSERT INTO m2 SELECT * FROM m1; SELECT * FROM m2; c1 c2 111 121 211 221 212 222 213 223 111 121 211 221 212 222 213 223 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 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`) SELECT * FROM m2; c1 c2 111 121 211 221 212 222 213 223 111 121 211 221 212 222 213 223 INSERT INTO m2 SELECT * FROM m1; SELECT * FROM m2; c1 c2 111 121 211 221 212 222 213 223 111 121 211 221 212 222 213 223 111 121 211 221 212 222 213 223 111 121 211 221 212 222 213 223 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 111 121 211 221 212 222 213 223 111 121 211 221 212 222 213 223 111 121 211 221 212 222 213 223 # # LOCK TABLE m1 WRITE, m2 WRITE; SELECT * FROM m1,m2 WHERE m1.c1=m2.c1; c1 c2 c1 c2 111 121 111 121 111 121 111 121 111 121 111 121 111 121 111 121 211 221 211 221 211 221 211 221 211 221 211 221 211 221 211 221 212 222 212 222 212 222 212 222 212 222 212 222 212 222 212 222 213 223 213 223 213 223 213 223 213 223 213 223 213 223 213 223 111 121 111 121 111 121 111 121 111 121 111 121 111 121 111 121 211 221 211 221 211 221 211 221 211 221 211 221 211 221 211 221 212 222 212 222 212 222 212 222 212 222 212 222 212 222 212 222 213 223 213 223 213 223 213 223 213 223 213 223 213 223 213 223 111 121 111 121 111 121 111 121 111 121 111 121 111 121 111 121 211 221 211 221 211 221 211 221 211 221 211 221 211 221 211 221 212 222 212 222 212 222 212 222 212 222 212 222 212 222 212 222 213 223 213 223 213 223 213 223 213 223 213 223 213 223 213 223 111 121 111 121 111 121 111 121 111 121 111 121 111 121 111 121 211 221 211 221 211 221 211 221 211 221 211 221 211 221 211 221 212 222 212 222 212 222 212 222 212 222 212 222 212 222 212 222 213 223 213 223 213 223 213 223 213 223 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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, t1 WRITE, t2 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 # UNLOCK TABLES; CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM; ALTER TABLE m1 UNION=(t1,t2,t3); 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci 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 COLLATE=latin1_swedish_ci SELECT * FROM m2; c1 c2 111 121 211 221 212 222 213 223 DROP TABLE m2; # CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) INSERT_METHOD=LAST; SELECT * FROM m2; c1 c2 311 321 411 421 LOCK TABLE m1 WRITE, m2 WRITE; UNLOCK TABLES; DROP TABLE m2; LOCK TABLE m1 WRITE; # # ER_TABLE_NOT_LOCKED is returned in ps-protocol 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 of type 'BASE TABLE' # 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 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`) LOCK TABLE m1 WRITE, m2 WRITE; 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 COLLATE=latin1_swedish_ci INSERT_METHOD=LAST UNION=(`t1`,`t2`) SELECT * FROM m2; c1 c2 111 121 211 221 212 222 213 223 INSERT INTO m2 SELECT * FROM m1; SELECT * FROM m2; c1 c2 111 121 211 221 212 222 213 223 111 121 211 221 212 222 213 223 DROP TABLE m2; # CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) INSERT_METHOD=LAST; 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 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 # OPEN # # Test that merge tables are closed correctly when opened using # HANDLER ... OPEN. # The general case. connect con1,localhost,root,,; CREATE TABLE t1 (c1 int); CREATE TABLE t2 (c1 int); CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2); START TRANSACTION; HANDLER t3 OPEN; ERROR HY000: Storage engine MRG_MyISAM of the table `test`.`t3` doesn't have this option DROP TABLE t1, t2, t3; connection default; # all mdl_tickets must have been released. disconnect con1; # The bug-specific case. connect con1,localhost,root,,; CREATE TABLE t1 (c1 int); CREATE TABLE t2 (c1 int); CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2); DROP TABLE t2; START TRANSACTION; HANDLER t3 OPEN; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TABLE t1, t3; connection default; # all mdl_tickets must have been released. disconnect con1; # # A test case for Bug#47648 main.merge fails sporadically # # Make sure we correctly maintain lex->query_tables_last_own. # create table t1 (c1 int not null); create table t2 (c1 int not null); create table t3 (c1 int not null); create function f1 () returns int return (select max(c1) from t3); create table t4 (c1 int not null) engine=merge union=(t1,t2) insert_method=last ; select * from t4 where c1 < f1(); c1 prepare stmt from "select * from t4 where c1 < f1()"; execute stmt; c1 execute stmt; c1 execute stmt; c1 drop function f1; execute stmt; ERROR 42000: FUNCTION test.f1 does not exist execute stmt; ERROR 42000: FUNCTION test.f1 does not exist drop table t4, t3, t2, t1; # # Bug#51240 ALTER TABLE of a locked MERGE table fails # CREATE TABLE t1 (c1 INT); CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1); LOCK TABLE m1 WRITE; ALTER TABLE m1 ADD INDEX (c1); UNLOCK TABLES; DROP TABLE m1, t1; # # Locking the merge table won't implicitly lock children. # CREATE TABLE t1 (c1 INT); CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1); LOCK TABLE m1 WRITE; ALTER TABLE t1 ADD INDEX (c1); ERROR HY000: Table 't1' was locked with a READ lock and can't be updated LOCK TABLE m1 WRITE, t1 WRITE; ALTER TABLE t1 ADD INDEX (c1); UNLOCK TABLES; DROP TABLE m1, t1; # # Test for bug #37371 "CREATE TABLE LIKE merge loses UNION parameter" # create table t1 (i int) engine=myisam; create table m1 (i int) engine=mrg_myisam union=(t1) insert_method=first; create table m2 like m1; # Table definitions should match show create table m1; Table Create Table m1 CREATE TABLE `m1` ( `i` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=FIRST UNION=(`t1`) show create table m2; Table Create Table m2 CREATE TABLE `m2` ( `i` int(11) DEFAULT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT_METHOD=FIRST UNION=(`t1`) drop tables m1, m2, t1; # # Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()" # Check that unique_table() works correctly for merge tables. # create table t1 (a int); create table t2 (a int); create table t3 (b int); create view v1 as select * from t3,t1; create table m1 (a int) engine=merge union (t1, t2) insert_method=last; create table m2 (a int) engine=merge union (t1, t2) insert_method=first; create temporary table tmp (b int); insert into tmp (b) values (1); insert into t1 (a) values (1); insert into t3 (b) values (1); insert into m1 (a) values ((select max(a) from m1)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from m2)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t1)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t2)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, m1)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, m2)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, t1)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, t2)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, m1)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, m2)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, t1)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, t2)); ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1' insert into m1 (a) values ((select max(a) from tmp, v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1' drop view v1; drop temporary table tmp; drop table t1, t2, t3, m1, m2; # # Bug#56494 Segfault in upgrade_shared_lock_to_exclusive() for # REPAIR of merge table # CREATE TABLE t1(a INT); ALTER TABLE t1 engine= MERGE UNION (t_not_exists); ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze Error Table 'test.t_not_exists' doesn't exist test.t1 analyze Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist test.t1 analyze error Corrupt CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check Error Table 'test.t_not_exists' is differently defined or of non-MyISAM type or doesn't exist test.t1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist test.t1 check error Corrupt CHECKSUM TABLE t1; Table Checksum test.t1 NULL Warnings: Error 1146 Table 'test.t_not_exists' doesn't exist Error 1168 Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize Error Table 'test.t_not_exists' doesn't exist test.t1 optimize Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist test.t1 optimize error Corrupt REPAIR TABLE t1; Table Op Msg_type Msg_text test.t1 repair Error Table 'test.t_not_exists' is differently defined or of non-MyISAM type or doesn't exist test.t1 repair Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist test.t1 repair error Corrupt REPAIR TABLE t1 USE_FRM; Table Op Msg_type Msg_text test.t1 repair Warning Can't open table test.t1 repair error Corrupt DROP TABLE t1; CREATE TABLE t1(a INT); CREATE TABLE t2(a INT) engine= MERGE UNION (t1); REPAIR TABLE t2 USE_FRM; Table Op Msg_type Msg_text test.t2 repair note The storage engine for the table doesn't support repair DROP TABLE t1, t2; # # Bug#57002 Assert in upgrade_shared_lock_to_exclusive() # for ALTER TABLE + MERGE tables # CREATE TABLE t1(a INT) engine=myisam; CREATE TABLE m1(a INT) engine=merge UNION(t1); LOCK TABLES t1 READ, m1 WRITE; ALTER TABLE t1 engine=myisam; ERROR HY000: Table 't1' was locked with a READ lock and can't be updated UNLOCK TABLES; DROP TABLE m1, t1; # # Test for bug #11754210 - "45777: CHECK TABLE DOESN'T SHOW ALL # PROBLEMS FOR MERGE TABLE COMPLIANCE IN 5.1" # create table t1(id int) engine=myisam; create view t3 as select 1 as id; create table t4(id int) engine=memory; create table m1(id int) engine=merge union=(t1,t2,t3,t4); select * from m1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist # The below CHECK and REPAIR TABLE statements should # report all problems with underlying tables: # - absence of 't2', # - missing base table for 't3', # - wrong engine of 't4'. check table m1; Table Op Msg_type Msg_text test.m1 check Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist test.m1 check Error Table 'test.t3' is differently defined or of non-MyISAM type or doesn't exist test.m1 check Error Table 'test.t4' is differently defined or of non-MyISAM type or doesn't exist test.m1 check Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist test.m1 check error Corrupt repair table m1; Table Op Msg_type Msg_text test.m1 repair Error Table 'test.t2' is differently defined or of non-MyISAM type or doesn't exist test.m1 repair Error Table 'test.t3' is differently defined or of non-MyISAM type or doesn't exist test.m1 repair Error Table 'test.t4' is differently defined or of non-MyISAM type or doesn't exist test.m1 repair Error Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist test.m1 repair error Corrupt # Clean-up. drop tables m1, t1, t4; drop view t3; # # MDEV-10424 Assertion `ticket == __null' failed in MDL_request::set_type # CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; CREATE TABLE tmerge (f1 INT) ENGINE=MERGE UNION=(t1); PREPARE stmt FROM "ANALYZE TABLE tmerge, t1"; EXECUTE stmt; Table Op Msg_type Msg_text test.tmerge analyze status Engine-independent statistics collected test.tmerge analyze note The storage engine for the table doesn't support analyze test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date EXECUTE stmt; Table Op Msg_type Msg_text test.tmerge analyze status Engine-independent statistics collected test.tmerge analyze note The storage engine for the table doesn't support analyze test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date DEALLOCATE PREPARE stmt; DROP TABLE t1, tmerge; # # End of 5.5 tests # # # Additional coverage for refactoring which is made as part # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege # to allow temp table operations". # # Check that prelocking works correctly for various variants of # merge tables. # create table t1 (j int); insert into t1 values (1); create function f1() returns int return (select count(*) from m1); create temporary table t2 (a int) engine=myisam; insert into t2 values (1); create temporary table m1 (a int) engine=merge union=(t2); select f1() from t1; f1() 1 drop tables t2, m1; create table t2 (a int) engine=myisam; insert into t2 values (1); create table m1 (a int) engine=merge union=(t2); select f1() from t1; f1() 1 drop table m1; create temporary table m1 (a int) engine=merge union=(t2); select f1() from t1; f1() 1 drop tables t1, t2, m1; drop function f1; # # Check that REPAIR/CHECK and CHECKSUM statements work correctly # for various variants of merge tables. create table t1 (a int) engine=myisam; insert into t1 values (1); create table m1 (a int) engine=merge union=(t1); check table m1; Table Op Msg_type Msg_text test.m1 check status OK repair table m1; Table Op Msg_type Msg_text test.m1 repair note The storage engine for the table doesn't support repair checksum table m1; Table Checksum test.m1 3459908756 drop tables t1, m1; create temporary table t1 (a int) engine=myisam; insert into t1 values (1); create temporary table m1 (a int) engine=merge union=(t1); check table m1; Table Op Msg_type Msg_text test.m1 check status OK repair table m1; Table Op Msg_type Msg_text test.m1 repair note The storage engine for the table doesn't support repair checksum table m1; Table Checksum test.m1 3459908756 drop tables t1, m1; create table t1 (a int) engine=myisam; insert into t1 values (1); create temporary table m1 (a int) engine=merge union=(t1); check table m1; Table Op Msg_type Msg_text test.m1 check status OK repair table m1; Table Op Msg_type Msg_text test.m1 repair note The storage engine for the table doesn't support repair checksum table m1; Table Checksum test.m1 3459908756 drop tables t1, m1; CREATE TABLE t1(a INT); CREATE TABLE m1(a INT) ENGINE = MERGE UNION (q1, q2); CREATE TRIGGER trg1 BEFORE DELETE ON t1 FOR EACH ROW INSERT INTO m1 VALUES (1); DELETE FROM t1; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist DROP TRIGGER trg1; DROP TABLE t1; DROP TABLE m1; set global default_storage_engine=@save_default_storage_engine; # # MDEV-31083 ASAN use-after-poison in myrg_attach_children # CREATE TABLE t1 (f TEXT, FULLTEXT (f)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('foo'),('bar'); CREATE TABLE mrg (f TEXT) ENGINE=MERGE, UNION(t1); SELECT * FROM mrg; f foo bar DROP TABLE mrg, t1; End of 10.5 tests # # End of 10.0 tests # # # MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results # create table t (a int, key(a desc)) engine=myisam; create table tm (a int, key(a)) engine=merge union(t); select * from tm; ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist drop table tm, t; # # MDEV-27586 Auto-increment does not work with DESC on MERGE table # create table t (a int not null, primary key(a desc)) engine=myisam; create table tm (a int not null auto_increment, primary key(a desc)) engine=merge union=(t) insert_method=first; insert into tm () values (); insert into tm () values (); insert into tm () values (); select * from tm; a 3 2 1 drop table tm, t; # # End of 10.8 tests #