diff options
Diffstat (limited to 'mysql-test/main/merge.test')
| -rw-r--r-- | mysql-test/main/merge.test | 2902 |
1 files changed, 2902 insertions, 0 deletions
diff --git a/mysql-test/main/merge.test b/mysql-test/main/merge.test new file mode 100644 index 00000000..aacfdd68 --- /dev/null +++ b/mysql-test/main/merge.test @@ -0,0 +1,2902 @@ +# +# Test of MERGE TABLES +# + +# Save the initial number of concurrent sessions. +--source include/count_sessions.inc + +# MERGE tables require MyISAM tables +set @save_default_storage_engine=@@global.default_storage_engine; +set global default_storage_engine=myisam; +set session default_storage_engine=myisam; + +# This test assumes a stable character set +--source include/default_charset.inc + +let $MYSQLD_DATADIR= `select @@datadir`; +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; +select * from t3 order by a desc; +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; +explain select * from t3 where a > 10 and a < 20; +select * from t3 where a = 10; +select * from t3 where a < 10; +select * from t3 where a > 10 and a < 20; +explain select a from t3 order by a desc limit 10; +select a from t3 order by a desc limit 10; +select a from t3 order by a desc limit 300,10; +delete from t3 where a=3; +select * from t3 where a < 10; +delete from t3 where a >= 6 and a <= 8; +select * from t3 where a < 10; +update t3 set a=3 where a=9; +select * from t3 where a < 10; +update t3 set a=6 where a=7; +select * from t3 where a < 10; +show create table t3; + +# The following should give errors +create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2); +--error 1168 +select * from t4; +--error 1168 +alter table t4 add column c int; +flush tables; +--error 1168 +select * from t4; + +# +# Test tables in different databases +# +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; +alter table t5 engine=myisam; +drop table t5, mysqltest.t6; +drop database mysqltest; + +# Because of windows, it's important that we drop the merge tables first! +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; +select * from t3; +delete from t3 where 1=1; +select * from t3; +select * from t1; +drop table t3,t2,t1; + +# +# Test 2 +# + +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; + +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; +alter table t3 UNION=(t1); +select count(*) from t3; +alter table t3 UNION=(t1,t2); +select count(*) from t3; +alter table t3 ENGINE=MYISAM; +select count(*) from t3; + +# Test that ALTER TABLE rembers the old UNION + +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; +alter table t3 drop primary key; +show create table t3; + +drop table t3,t2,t1; + +# +# Test table without unions +# +create table t1 (a int not null, key(a)) engine=merge; +select * from t1; +drop table t1; + +# +# Bug in flush tables combined with MERGE tables +# + +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; +drop table t3,t1,t2; + +# +# [phi] testing INSERT_METHOD stuff +# + +# first testing of common stuff with new parameters +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; +show create table t4; +show create table t5; +show create table t6; +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; +select * from t4 order by b,a limit 3; +select * from t5 order by b,a limit 3,3; +select * from t6 order by b,a limit 6,3; +# now testing inserts and where the data gets written +insert into t5 values (5,1),(5,2); +insert into t6 values (6,1),(6,2); +select * from t1 order by a,b; +select * from t2 order by a,b; +select * from t4 order by a,b; +# preperation for next test +insert into t3 values (3,1),(3,2),(3,3),(3,4); +select * from t3 order by a,b; +# now testing whether options are kept by alter table +alter table t4 UNION=(t1,t2,t3); +show create table t4; +select * from t4 order by a,b; +# testing switching off insert method and inserts again +alter table t4 INSERT_METHOD=FIRST; +show create table t4; +insert into t4 values (4,1),(4,2); +select * from t1 order by a,b; +select * from t2 order by a,b; +select * from t3 order by a,b; +select * from t4 order by a,b; +select * from t5 order by a,b; +# auto_increment +select 1; +insert into t5 values (1,NULL),(5,NULL); +insert into t6 values (2,NULL),(6,NULL); +select * from t1 order by a,b; +select * from t2 order by a,b; +select * from t5 order by a,b; +select * from t6 order by a,b; +insert into t1 values (99,NULL); +select * from t4 where a+0 > 90; +# bug#4008 - cannot determine a unique key that caused "dupl. key error" +--error ER_DUP_ENTRY +insert t5 values (1,1); +--error ER_DUP_ENTRY +insert t6 values (2,1); +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; +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; +select max(b) from t1 where a = 2; +drop table t3,t1,t2; + +# +# temporary merge tables +# +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; +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; +drop table t6, t3, t1, t2, t4, t5; +# +# Bug#19627 - temporary merge table locking +# MERGE table and its children must match in temporary type. +# Forbid temporary merge on non-temporary children: shown above. +# Forbid non-temporary merge on temporary children: +create temporary table t1 (a int not null); +create temporary table t2 (a int not null); +insert into t1 values (1); +insert into t2 values (2); +create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2); +--error ER_WRONG_MRG_TABLE +select * from t3; +drop table t3, t2, t1; +# Forbid children mismatch in temporary: +create table t1 (a int not null); +create temporary table t2 (a int not null); +insert into t1 values (1); +insert into t2 values (2); +create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2); +--error ER_WRONG_MRG_TABLE +select * from t3; +drop table t3; +create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2); +select * from t3; +drop table t3, t2, t1; +--echo # CREATE...SELECT is not implemented for MERGE tables. +CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL); +CREATE TEMPORARY TABLE t2 (c1 INT NOT NULL); +CREATE TABLE t3 (c1 INT NOT NULL); +INSERT INTO t3 VALUES (3), (33); +LOCK TABLES t3 READ; +--error ER_WRONG_OBJECT +CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2) + INSERT_METHOD=LAST SELECT * FROM t3; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM t4; +UNLOCK TABLES; +CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2) + INSERT_METHOD=LAST; +INSERT INTO t4 SELECT * FROM t3; +--echo # Alter temporary MERGE table. +ALTER TABLE t4 UNION=(t1); +LOCK TABLES t4 WRITE; +--echo # Alter temporary MERGE table under LOCk tables. +ALTER TABLE t4 UNION=(t1,t2); +UNLOCK TABLES; +--echo # MERGE table and function. +CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3); +SELECT * FROM t4 WHERE c1 < f1(); +DROP FUNCTION f1; +DROP TABLE t4, t3, t2, t1; + +# +# testing merge::records_in_range and optimizer +# + +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; +EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 +AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; +EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2 +AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; +EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2 +AND file_code = '0000000115' LIMIT 1; +DROP TABLE t2, t1; + +# +# Test of ORDER BY DESC on key (Bug #515) +# + +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; +# Bug is that followng query returns empty set while it must be same as above +select * from t3 where x = 1 and y < 5 order by y desc; +drop table t1,t2,t3; + +# +# Bug#5232: CREATE TABLE ... SELECT +# + +create table t1 (a int); +create table t2 (a int); +insert into t1 values (0); +insert into t2 values (1); +--error ER_WRONG_OBJECT +create table t3 engine=merge union=(t1, t2) select * from t1; +--error ER_WRONG_OBJECT +create table t3 engine=merge union=(t1, t2) select * from t2; +--error ER_WRONG_OBJECT +create table t3 engine=merge union=(t1, t2) select (select max(a) from t2); +drop table t1, t2; + +# +# Bug#9112 - Merge table with composite index producing invalid results with some queries +# This test case will fail only without the bugfix and some +# non-deterministic circumstances. It depends on properly initialized +# "un-initialized" memory. At the time it happens with a standard +# non-debug build. But there is no guarantee that this will be always so. +# +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; +flush tables; +select min(a), max(a) from t1; +drop table t1, t2, t3; +# BUG#6699 : no sorting on 'ref' retrieval +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; +select a,b,c from t3 force index (a) where a=1 order by a,b,c; + +# this actually wasn't affected: +explain select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc; +select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc; + +# BUG#7377 SHOW index on MERGE table crashes debug server +show index from t3; + +drop table t1, t2, t3; + +# +# Bug#10400 - Improperly-defined MERGE table crashes with INSERT ... ON DUPLICATE KEY UPDATE +# +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; +DROP TABLE t1, t2; + + +# +# BUG#5390 - problems with merge tables +# Problem #1: INSERT...SELECT +# +#drop table if exists t1, t2, t3; +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; +# +insert t2 select * from t2; +select * from t2; +# +insert t3 select * from t1; +select * from t3; +# +insert t1 select * from t3; +select * from t1; +select * from t2; +select * from t3; +check table t1, t2; +drop table t1, t2, t3; + +# +# BUG#21617 - crash when selecting from merge table with inconsistent +# indexes +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(2),(1); +CREATE TABLE t2(a INT, KEY(a)) ENGINE=MERGE UNION=(t1); +--error 1168 +SELECT * FROM t2 WHERE a=2; +DROP TABLE t1, t2; + +# +# BUG#10974 - No error message if merge table based on union of innodb, +# memory +# +CREATE TABLE t1(a INT) ENGINE=MEMORY; +CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t1); +--error 1168 +SELECT * FROM t2; +DROP TABLE t1, t2; +CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3); +--error ER_WRONG_MRG_TABLE +SELECT * FROM t2; +DROP TABLE t2; + +# +# Underlying table definition conformance tests. +# +CREATE TABLE t1(a INT, b TEXT); +CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1); +--error 1168 +SELECT * FROM tm1; +DROP TABLE t1, tm1; + +CREATE TABLE t1(a SMALLINT, b SMALLINT); +CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1); +--error 1168 +SELECT * FROM tm1; +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); +--error 1168 +SELECT * FROM tm1; +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); +--error 1168 +SELECT * FROM tm1; +DROP TABLE t1, tm1; + + +# BUG#26881 - Large MERGE tables report incorrect specification when no +# differences in tables +# +CREATE TABLE t1(c1 VARCHAR(1)); +CREATE TABLE m1 LIKE t1; +ALTER TABLE m1 ENGINE=MERGE UNION=(t1); +SELECT * FROM m1; +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; +DROP TABLE t1, m1; + +# +# BUG#24342 - Incorrect results with query over MERGE table +# +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; +DROP TABLE t1,t2,t3; + +# End of 4.1 tests + +# +# BUG#19648 - Merge table does not work with bit types +# +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; +drop table tm, t1, t2; + +# +# Bug #17766: The server accepts to create MERGE tables which cannot work +# +create table t1 (a int) insert_method = last engine = merge; +--error ER_OPEN_AS_READONLY +insert into t1 values (1); +create table t2 (a int) engine = myisam; +alter table t1 union (t2); +insert into t1 values (1); +alter table t1 insert_method = no; +--error ER_OPEN_AS_READONLY +insert into t1 values (1); +drop table t2; +drop table t1; + +# +# BUG#26976 - Missing table in merge not noted in related error msg + SHOW +# CREATE TABLE fails +# +CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2); +--error ER_WRONG_MRG_TABLE +SELECT * FROM tm1; +CHECK TABLE tm1; +CREATE TABLE t1(a INT); +--error ER_WRONG_MRG_TABLE +SELECT * FROM tm1; +CHECK TABLE tm1; +CREATE TABLE t2(a BLOB); +--error ER_WRONG_MRG_TABLE +SELECT * FROM tm1; +CHECK TABLE tm1; +ALTER TABLE t2 MODIFY a INT; +SELECT * FROM tm1; +CHECK TABLE tm1; +DROP TABLE tm1, t1, t2; + +# +# Bug#15522 - create ... select and with merge tables +# +# This was fixed together with Bug#20662 (Infinite loop in CREATE TABLE +# IF NOT EXISTS ... SELECT with locked tables). +# The new behavior for MERGE tables is consistent with the +# CREATE TABLE SELECT behavior for ordinary tables. +# +CREATE TABLE t1(c1 INT); +CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; +# After WL#5370, it just generates a warning that the table already exists +CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; +DROP TABLE t1, t2; + +# +# Bug #28837: MyISAM storage engine error (134) doing delete with self-join +# + +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; +SELECT * FROM t3; +DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; +SELECT * FROM t3; + +DROP TABLE t1, t2, t3; + +# +# BUG#28248 - mysqldump results with MERGE ... UNION=() cannot be executed +# +CREATE TABLE t1(a INT); +CREATE TABLE m1(a INT) ENGINE=MERGE; +SHOW CREATE TABLE m1; +DROP TABLE m1; +CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(); +SHOW CREATE TABLE m1; +ALTER TABLE m1 UNION=(t1); +ALTER TABLE m1 UNION=(); +SHOW CREATE TABLE m1; +DROP TABLE t1, m1; + +# +# BUG#32047 - 'Spurious' errors while opening MERGE tables +# +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; +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE t1, t2, m1, m2; + +--echo End of 5.0 tests + + +# +# Bug #8306: TRUNCATE leads to index corruption +# +create table t1 (c1 int, index(c1)); +create table t2 (c1 int, index(c1)) engine=merge union=(t1); +insert into t1 values (1); +# Close all tables. +flush tables; +# Open t2 and (implicitly) t1. +select * from t2; +# Truncate after flush works (unless another threads reopens t2 in between). +flush tables; +truncate table t1; +insert into t1 values (1); +# Close all tables. +flush tables; +# Open t2 and (implicitly) t1. +select * from t2; +# Truncate t1, which was not recognized as open without the bugfix. +# After fix for Bug#8306 and before fix for Bug#26379, +# it should fail with a table-in-use error message, otherwise succeed. +truncate table t1; +# The insert used to fail on the crashed table. +insert into t1 values (1); +drop table t1,t2; +--echo # +--echo # Extra tests for TRUNCATE. +--echo # +--echo # Truncate MERGE table. +CREATE TABLE t1 (c1 INT, INDEX(c1)); +CREATE TABLE t2 (c1 INT, INDEX(c1)); +CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +SELECT * FROM t3; +TRUNCATE TABLE t3; +SELECT * FROM t3; +--echo # +--echo # Truncate child table. +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +TRUNCATE TABLE t1; +SELECT * FROM t3; +--echo # +--echo # Truncate MERGE table under locked tables. +LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE; +INSERT INTO t1 VALUES (1); +TRUNCATE TABLE t3; +SELECT * FROM t3; +UNLOCK TABLES; +SELECT * FROM t1; +SELECT * FROM t2; +--echo # +--echo # 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; +UNLOCK TABLES; +DROP TABLE t1, t2, t3; +--echo # +--echo # Truncate temporary MERGE table. +CREATE TEMPORARY TABLE t1 (c1 INT, INDEX(c1)); +CREATE TEMPORARY TABLE t2 (c1 INT, INDEX(c1)); +CREATE TEMPORARY TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +SELECT * FROM t3; +TRUNCATE TABLE t3; +SELECT * FROM t3; +--echo # +--echo # Truncate temporary child table. +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +TRUNCATE TABLE t1; +SELECT * FROM t3; +--echo # +--echo # Truncate temporary MERGE table under locked tables. +INSERT INTO t1 VALUES (1); +CREATE TABLE t4 (c1 INT, INDEX(c1)); +LOCK TABLE t4 WRITE; +TRUNCATE TABLE t3; +SELECT * FROM t3; +SELECT * FROM t1; +SELECT * FROM t2; +--echo # +--echo # Truncate temporary child table under locked tables. +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +TRUNCATE TABLE t1; +SELECT * FROM t3; +SELECT * FROM t1; +SELECT * FROM t2; +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4; + +# +# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table +# Preparation +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +connection default; +# +# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table +# Problem #1 +# A thread trying to lock a MERGE table performed busy waiting while +# REPAIR TABLE or a similar table administration task was ongoing on one or +# more of its MyISAM tables. +# To allow for observability it was necessary to enter a multi-second sleep +# in mysql_admin_table() after remove_table_from_cache(), which comes after +# mysql_abort_lock(). The sleep faked a long running operation. One could +# watch a high CPU load during the sleep time. +# The problem was that mysql_abort_lock() upgrades the write lock to +# TL_WRITE_ONLY. This lock type persisted until the final unlock at the end +# of the administration task. The effect of TL_WRITE_ONLY is to reject any +# attempt to lock the table. The trying thread must close the table and wait +# until it is no longer used. Unfortunately there is no way to detect that +# one of the MyISAM tables of a MERGE table is in use. When trying to lock +# the MERGE table, all MyISAM tables are locked. If one fails on +# TL_WRITE_ONLY, all locks are aborted and wait_for_tables() is entered. +# But this doesn't see the MERGE table as used, so it seems appropriate to +# retry a lock... +# +CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; +CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST; +send REPAIR TABLE t1; + connection con1; + sleep 1; # let repair run into its sleep + INSERT INTO t2 VALUES (1); +connection default; +reap; +DROP TABLE t1, t2; +# +# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table +# Problem #2 +# A thread trying to lock a MERGE table performed busy waiting until all +# threads that did REPAIR TABLE or similar table administration tasks on +# one or more of its MyISAM tables in LOCK TABLES segments did +# UNLOCK TABLES. +# The difference against problem #1 is that the busy waiting took place +# *after* the administration task. It was terminated by UNLOCK TABLES only. +# +# This is the same test case as for +# Bug#26867 - LOCK TABLES REPAIR + merge table result in memory/cpu hogging +# +# +CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; +CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST; +LOCK TABLE t1 WRITE; + connection con1; + send INSERT INTO t2 VALUES (1); +connection default; +sleep 1; # Let INSERT go into thr_multi_lock(). +REPAIR TABLE t1; +sleep 2; # con1 performs busy waiting during this sleep. +UNLOCK TABLES; + connection con1; + reap; +connection default; +DROP TABLE t1, t2; +# +# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table +# Problem #3 +# Two FLUSH TABLES within a LOCK TABLES segment could invalidate the lock. +# This did *not* require a MERGE table. +# To increase reproducibility it was necessary to enter a sleep of 2 seconds +# at the end of wait_for_tables() after unlock of LOCK_open. In 5.0 and 5.1 +# the sleep must be inserted in open_and_lock_tables() after open_tables() +# instead. wait_for_tables() is not used in this case. +# The problem was that FLUSH TABLES releases LOCK_open while having unlocked +# and closed all tables. When this happened while a thread was in the loop in +# mysql_lock_tables() right after wait_for_tables() and before retrying to +# lock, the thread got the lock. (Translate to similar code places in 5.0 +# and 5.1). And it did not notice that the table needed a refresh. So it +# executed its statement on the table. +# The first FLUSH TABLES kicked the INSERT out of thr_multi_lock() and let +# it wait in wait_for_tables(). (open_table() in 5.0 and 5.1). The second +# FLUSH TABLES must happen while the INSERT was on its way from +# wait_for_tables() to the next call of thr_multi_lock(). This needed to be +# supported by a sleep to make it repeatable. +# +CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; +LOCK TABLE t1 WRITE; + connection con1; + send INSERT INTO t1 VALUES (1); +connection default; +sleep 1; # Let INSERT go into thr_multi_lock(). +FLUSH TABLES; +sleep 1; # Let INSERT go through wait_for_tables() where it sleeps. +FLUSH TABLES; +# This should give no result. But it will with sleep(2) at the right place. +SELECT * FROM t1; +UNLOCK TABLES; + connection con1; + reap; +connection default; +DROP TABLE t1; +# +# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table +# Cleanup +disconnect con1; +disconnect con2; +# +--echo # +--echo # Extra tests for Bug#26379 - Combination of FLUSH TABLE and +--echo # REPAIR TABLE corrupts a MERGE table +# +--echo # +--echo # CREATE ... SELECT is disabled for MERGE tables. +--echo # +CREATE TABLE t1(c1 INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; +--error ER_OPEN_AS_READONLY +CREATE TABLE t3 ENGINE=MRG_MYISAM INSERT_METHOD=LAST SELECT * FROM t2; +--error ER_NO_SUCH_TABLE +SHOW CREATE TABLE t3; +--error ER_WRONG_OBJECT +CREATE TABLE t3 ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST + SELECT * FROM t2; +--error ER_NO_SUCH_TABLE +SHOW CREATE TABLE t3; +DROP TABLE t1, t2; +# +--echo # +--echo # Bug#37371 "CREATE TABLE LIKE merge loses UNION parameter" +--echo # Demonstrate that this is no longer the case. +--echo # +--echo # 1. Create like. +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TABLE t3 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +INSERT INTO t3 VALUES (3); +CREATE TABLE t4 LIKE t3; +SHOW CREATE TABLE t4; +INSERT INTO t4 VALUES (4); +DROP TABLE t4; +--echo # +--echo # 1. Create like with locked tables. +LOCK TABLES t3 WRITE, t2 WRITE, t1 WRITE; +--error ER_TABLE_NOT_LOCKED +CREATE TABLE t4 LIKE t3; +--error ER_TABLE_NOT_LOCKED +SHOW CREATE TABLE t4; +--error ER_TABLE_NOT_LOCKED +INSERT INTO t4 VALUES (4); +--echo # Temporary tables can be created in spite of LOCK TABLES. +--echo # If the temporary MERGE table uses the locked children only, +--echo # it can even be used. +CREATE TEMPORARY TABLE t4 LIKE t3; +SHOW CREATE TABLE t4; +INSERT INTO t4 VALUES (4); +UNLOCK TABLES; +INSERT INTO t4 VALUES (4); +DROP TABLE t4; +# +--echo # +--echo # Rename child. +--echo # +--echo # 1. Normal rename of non-MERGE table. +CREATE TABLE t4 (c1 INT); +INSERT INTO t4 VALUES (4); +SELECT * FROM t4 ORDER BY c1; +RENAME TABLE t4 TO t5; +SELECT * FROM t5 ORDER BY c1; +RENAME TABLE t5 TO t4; +SELECT * FROM t4 ORDER BY c1; +DROP TABLE t4; +--echo # +--echo # 2. Normal rename. +SELECT * FROM t3 ORDER BY c1; +RENAME TABLE t2 TO t5; +--error ER_WRONG_MRG_TABLE +SELECT * FROM t3 ORDER BY c1; +RENAME TABLE t5 TO t2; +SELECT * FROM t3 ORDER BY c1; +--echo # +--echo # 3. Normal rename with locked tables. +LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE; +SELECT * FROM t3 ORDER BY c1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +RENAME TABLE t2 TO t5; +SELECT * FROM t3 ORDER BY c1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +RENAME TABLE t5 TO t2; +SELECT * FROM t3 ORDER BY c1; +UNLOCK TABLES; +--echo # +--echo # 4. Alter table rename. +ALTER TABLE t2 RENAME TO t5; +--error ER_WRONG_MRG_TABLE +SELECT * FROM t3 ORDER BY c1; +ALTER TABLE t5 RENAME TO t2; +SELECT * FROM t3 ORDER BY c1; +--echo # +--echo # 5. Alter table rename with locked tables. +LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE; +ALTER TABLE t2 RENAME TO t5; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM t3 ORDER BY c1; +--error ER_TABLE_NOT_LOCKED +ALTER TABLE t5 RENAME TO t2; +UNLOCK TABLES; +ALTER TABLE t5 RENAME TO t2; +SELECT * FROM t3 ORDER BY c1; +# +--echo # +--echo # Rename parent. +--echo # +--echo # 1. Normal rename with locked tables. +LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE; +SELECT * FROM t3 ORDER BY c1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +RENAME TABLE t3 TO t5; +SELECT * FROM t3 ORDER BY c1; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +RENAME TABLE t5 TO t3; +SELECT * FROM t3 ORDER BY c1; +--echo # +--echo # 5. Alter table rename with locked tables. +ALTER TABLE t3 RENAME TO t5; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM t5 ORDER BY c1; +--error ER_TABLE_NOT_LOCKED +ALTER TABLE t5 RENAME TO t3; +UNLOCK TABLES; +ALTER TABLE t5 RENAME TO t3; +SELECT * FROM t3 ORDER BY c1; +DROP TABLE t1, t2, t3; +# +--echo # +--echo # Drop locked tables. +--echo # +--echo # 1. Drop parent. +CREATE TABLE t1 (c1 INT, INDEX(c1)); +CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1) + INSERT_METHOD=LAST; +LOCK TABLES t1 WRITE, t2 WRITE; +INSERT INTO t1 VALUES (1); +DROP TABLE t2; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM t2; +SELECT * FROM t1; +UNLOCK TABLES; +--echo # 2. Drop child. +CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1) + INSERT_METHOD=LAST; +LOCK TABLES t1 WRITE, t2 WRITE; +INSERT INTO t1 VALUES (1); +DROP TABLE t1; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM t2; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM t1; +UNLOCK TABLES; +DROP TABLE t2; +# +--echo # +--echo # ALTER TABLE. Change child list. +--echo # +CREATE TABLE t1 (c1 INT, INDEX(c1)); +CREATE TABLE t2 (c1 INT, INDEX(c1)); +CREATE TABLE t3 (c1 INT, INDEX(c1)); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +INSERT INTO t3 VALUES (3); +CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t3,t2) + INSERT_METHOD=LAST; +--echo # Shrink child list. +ALTER TABLE t4 UNION=(t3); +SHOW CREATE TABLE t4; +SELECT * FROM t4 ORDER BY c1; +--echo # Extend child list. +ALTER TABLE t4 UNION=(t3,t2); +SHOW CREATE TABLE t4; +SELECT * FROM t4 ORDER BY c1; +# +--echo # +--echo # ALTER TABLE under LOCK TABLES. Change child list. +--echo # +LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE; +--echo # Shrink child list. +--error ER_LOCK_OR_ACTIVE_TRANSACTION +ALTER TABLE t4 UNION=(t3); +--echo # Extend child list within locked tables. +--error ER_LOCK_OR_ACTIVE_TRANSACTION +ALTER TABLE t4 UNION=(t3,t2); +--echo # Extend child list beyond locked tables. +--error ER_LOCK_OR_ACTIVE_TRANSACTION +ALTER TABLE t4 UNION=(t3,t2,t1); +SHOW CREATE TABLE t4; +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +DROP TABLE t4; +# +--echo # +--echo # ALTER TABLE under LOCK TABLES. Grave change, table re-creation. +--echo # +CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2,t3) + INSERT_METHOD=LAST; +--echo # Lock parent first and then children. +LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE; +ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1); +SELECT * FROM t4 ORDER BY c1; +ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1); +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +--echo # Lock children first and then parent. +LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE; +ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1); +SELECT * FROM t4 ORDER BY c1; +ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1); +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +--echo # Lock parent between children. +LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; +ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1); +SELECT * FROM t4 ORDER BY c1; +ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1); +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4; +# +--echo # +--echo # ALTER TABLE under LOCK TABLES. Simple change, no re-creation. +--echo # +CREATE TABLE t1 (c1 INT); +CREATE TABLE t2 (c1 INT); +CREATE TABLE t3 (c1 INT); +CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3) + INSERT_METHOD=LAST; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +INSERT INTO t3 VALUES (3); +--echo # Lock parent first and then children. +LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE; +ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44; +SELECT * FROM t4 ORDER BY c1; +ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22; +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +--echo # Lock children first and then parent. +LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE; +ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44; +SELECT * FROM t4 ORDER BY c1; +ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22; +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +--echo # Lock parent between children. +LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; +ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44; +SELECT * FROM t4 ORDER BY c1; +ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22; +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +# +--echo # +--echo # FLUSH TABLE under LOCK TABLES. +--echo # +--echo # Lock parent first and then children. +LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE; +FLUSH TABLE t4; +SELECT * FROM t4 ORDER BY c1; +FLUSH TABLE t2; +SELECT * FROM t4 ORDER BY c1; +FLUSH TABLES; +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +--echo # Lock children first and then parent. +LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE; +FLUSH TABLE t4; +SELECT * FROM t4 ORDER BY c1; +FLUSH TABLE t2; +SELECT * FROM t4 ORDER BY c1; +FLUSH TABLES; +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +--echo # Lock parent between children. +LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; +FLUSH TABLE t4; +SELECT * FROM t4 ORDER BY c1; +FLUSH TABLE t2; +SELECT * FROM t4 ORDER BY c1; +FLUSH TABLES; +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +# +--echo # +--echo # Triggers +--echo # +--echo # Trigger on parent +DELETE FROM t4 WHERE c1 = 4; +CREATE TRIGGER t4_ai1 AFTER INSERT ON t4 FOR EACH ROW SET @a=1; +SET @a=0; +INSERT INTO t4 VALUES (4); +SELECT @a; +SELECT * FROM t4 ORDER BY c1; +DROP TRIGGER t4_ai1; +CHECK TABLE t3; +--echo # 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; +SELECT * FROM t4 ORDER BY c1; +DROP TRIGGER t4_ai2; +UNLOCK TABLES; +CHECK TABLE t3; +--echo # +--echo # 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; +INSERT INTO t3 VALUES (33); +SELECT @a; +SELECT * FROM t4 ORDER BY c1; +DROP TRIGGER t3_ai3; +CHECK TABLE t3; +--echo # 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; +INSERT INTO t3 VALUES (33); +SELECT @a; +SELECT * FROM t4 ORDER BY c1; +DELETE FROM t4 WHERE c1 = 33; +DROP TRIGGER t3_ai4; +UNLOCK TABLES; +CHECK TABLE t3; +--echo # +--echo # 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; +INSERT INTO t4 VALUES (4); +SELECT * FROM t4 ORDER BY c1; +INSERT INTO t3 VALUES (33); +SELECT * FROM t4 ORDER BY c1; +DELETE FROM t4 WHERE c1 = 22; +DELETE FROM t4 WHERE c1 = 33; +DROP TRIGGER t3_ai5; +UNLOCK TABLES; +CHECK TABLE t2,t3; +--echo # 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; +INSERT INTO t3 VALUES (33); +SELECT * FROM t4 ORDER BY c1; +DROP TRIGGER t3_ai6; +UNLOCK TABLES; +check table t2,t3,t4; +DELETE FROM t4 WHERE c1 = 22; +DELETE FROM t4 WHERE c1 = 33; +--echo # 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; +INSERT INTO t3 VALUES (33); +SELECT * FROM t4 ORDER BY c1; +DROP TRIGGER t3_ai7; +UNLOCK TABLES; +check table t2,t3,t4; +DELETE FROM t4 WHERE c1 = 22; +DELETE FROM t4 WHERE c1 = 33; +# +--echo # +--echo # Repair +--echo # +REPAIR TABLE t4; +REPAIR TABLE t2; +SELECT * FROM t4 ORDER BY c1; +LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; +REPAIR TABLE t4; +REPAIR TABLE t2; +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +# +--echo # +--echo # Optimize +--echo # +OPTIMIZE TABLE t4; +OPTIMIZE TABLE t2; +SELECT * FROM t4 ORDER BY c1; +LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; +OPTIMIZE TABLE t4; +OPTIMIZE TABLE t2; +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +# +--echo # +--echo # Checksum +--echo # +CHECKSUM TABLE t4; +CHECKSUM TABLE t2; +SELECT * FROM t4 ORDER BY c1; +LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; +CHECKSUM TABLE t4; +CHECKSUM TABLE t2; +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +# +--echo # +--echo # Insert delayed +--echo # +# See also Bug#26464 - insert delayed + update + merge = corruption +# Succeeds in embedded server - is converted to normal insert +# Fails in normal server, ps-protocol - not supported by engine +# Fails in normal server, normal protocol - not a base table +--error 0, ER_DELAYED_NOT_SUPPORTED, ER_WRONG_OBJECT +INSERT DELAYED INTO t4 VALUES(44); +# Get rid of row in embedded server +DELETE FROM t4 WHERE c1 = 44; +INSERT DELAYED INTO t3 VALUES(33); + let $wait_cmd= SHOW STATUS LIKE 'Not_flushed_delayed_rows'; + let $run= query_get_value($wait_cmd, Value, 1); + while ($run) + { + let $run= query_get_value($wait_cmd, Value, 1); + } +SELECT * FROM t4 ORDER BY c1; +LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE; +--error ER_DELAYED_INSERT_TABLE_LOCKED, ER_DELAYED_NOT_SUPPORTED +INSERT DELAYED INTO t4 VALUES(444); +--error ER_DELAYED_INSERT_TABLE_LOCKED, ER_DELAYED_NOT_SUPPORTED +INSERT DELAYED INTO t3 VALUES(333); +SELECT * FROM t4 ORDER BY c1; +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4; +# +--echo # +--echo # Recursive inclusion of merge tables in their union clauses. +--echo # +CREATE TABLE t1 (c1 INT, INDEX(c1)); +CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1) + INSERT_METHOD=LAST; +CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t2,t1) + INSERT_METHOD=LAST; +ALTER TABLE t2 UNION=(t3,t1); +--error ER_ADMIN_WRONG_MRG_TABLE +SELECT * FROM t2; +DROP TABLE t1, t2, t3; + + +# +# Bug#25038 - Waiting TRUNCATE +# Truncate failed with error message when table was in use by MERGE. +# +# Show that truncate of child table after use of parent table works. +CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; +CREATE TABLE t2 (c1 INT) ENGINE= MyISAM; +CREATE TABLE t3 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1, t2); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (2); +SELECT * FROM t3; +TRUNCATE TABLE t1; +SELECT * FROM t3; +DROP TABLE t1, t2, t3; +# +# Show that truncate of child table waits while parent table is used. +# (test partly borrowed from count_distinct3.) +CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER); +SET @rnd_max= 2147483647; +let $1 = 10; +--disable_query_log +begin; +while ($1) +{ + SET @rnd= RAND(); + SET @id = CAST(@rnd * @rnd_max AS UNSIGNED); + SET @id_rev= @rnd_max - @id; + SET @grp= CAST(127.0 * @rnd AS UNSIGNED); + INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); + dec $1; +} +commit; +--enable_query_log +set @@read_buffer_size=2*1024*1024; +CREATE TABLE t2 SELECT * FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; +INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; +CREATE TABLE t3 (id INTEGER, grp TINYINT, id_rev INTEGER) + ENGINE= MRG_MYISAM UNION= (t1, t2); +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t3; +connect (con1,localhost,root,,); + # As t3 contains random numbers, results are different from test to test. + # That's okay, because we test only that select doesn't yield an + # error. Note, that --disable_result_log doesn't suppress error output. + --disable_result_log + send SELECT COUNT(DISTINCT a1.id) FROM t3 AS a1, t3 AS a2 + WHERE a1.id = a2.id GROUP BY a2.grp; +connection default; +sleep 1; +TRUNCATE TABLE t1; + connection con1; + reap; + --enable_result_log + disconnect con1; +connection default; +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t2; +SELECT COUNT(*) FROM t3; +DROP TABLE t1, t2, t3; + +# +# Bug#25700 - merge base tables get corrupted by optimize/analyze/repair table +# +# Using FLUSH TABLES before REPAIR. +CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; +CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; +INSERT INTO t2 VALUES (1); +SELECT * FROM t2; +LOCK TABLES t2 WRITE, t1 WRITE; +FLUSH TABLES; +REPAIR TABLE t1; +CHECK TABLE t1; +REPAIR TABLE t1; +UNLOCK TABLES; +CHECK TABLE t1 EXTENDED; +# +# Not using FLUSH TABLES before REPAIR. +LOCK TABLES t2 WRITE, t1 WRITE; +REPAIR TABLE t1; +CHECK TABLE t1; +REPAIR TABLE t1; +UNLOCK TABLES; +CHECK TABLE t1 EXTENDED; +DROP TABLE t1, t2; + +# +# Bug#26377 - Deadlock with MERGE and FLUSH TABLE +# +CREATE TABLE t1 ( a INT ) ENGINE=MyISAM; +CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1); +# Lock t1 first. This did always work. +LOCK TABLES t1 WRITE, m1 WRITE; +FLUSH TABLE t1; +UNLOCK TABLES; +DROP TABLE m1, t1; +# +CREATE TABLE t1 ( a INT ) ENGINE=MyISAM; +CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1); +# Lock m1 first. This did deadlock. +LOCK TABLES m1 WRITE, t1 WRITE; +FLUSH TABLE t1; +UNLOCK TABLES; +DROP TABLE m1, t1; + +# +# Bug#27660 - Falcon: merge table possible +# +# Normal MyISAM MERGE operation. +CREATE TABLE t1 (c1 INT, c2 INT) ENGINE= MyISAM; +CREATE TABLE t2 (c1 INT, c2 INT) ENGINE= MyISAM; +CREATE TABLE t3 (c1 INT, c2 INT) ENGINE= MRG_MYISAM UNION(t1, t2); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t2 VALUES (2, 2); +SELECT * FROM t3; +# Try an unsupported engine. +ALTER TABLE t1 ENGINE= MEMORY; +INSERT INTO t1 VALUES (0, 0); +# Before fixing, this succeeded, but (0, 0) was missing. +--error 1168 +SELECT * FROM t3; +DROP TABLE t1, t2, t3; + +# +# Bug#30275 - Merge tables: flush tables or unlock tables causes server to crash +# +CREATE TABLE t1 (c1 INT, KEY(c1)); +CREATE TABLE t2 (c1 INT, KEY(c1)) ENGINE=MRG_MYISAM UNION=(t1) + INSERT_METHOD=FIRST; +LOCK TABLE t1 WRITE, t2 WRITE; +FLUSH TABLES t2, t1; +OPTIMIZE TABLE t1; +FLUSH TABLES t1; +UNLOCK TABLES; +# +FLUSH TABLES; +INSERT INTO t1 VALUES (1); +LOCK TABLE t1 WRITE, t2 WRITE; +FLUSH TABLES t2, t1; +OPTIMIZE TABLE t1; +FLUSH TABLES t1; +UNLOCK TABLES; +DROP TABLE t1, t2; + +# +# Test derived from test program for +# Bug#30273 - merge tables: Can't lock file (errno: 155) +# +CREATE TABLE t1 (ID INT) ENGINE=MYISAM; +CREATE TABLE m1 (ID INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST; +INSERT INTO t1 VALUES (); +INSERT INTO m1 VALUES (); +LOCK TABLE t1 WRITE, m1 WRITE; +FLUSH TABLES m1, t1; +OPTIMIZE TABLE t1; +FLUSH TABLES m1, t1; +UNLOCK TABLES; +DROP TABLE t1, m1; + +# +# Bug#35068 - Assertion fails when reading from i_s.tables +# and there is incorrect merge table +# +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'; + +DROP TABLE tm1; + +# +# Bug#36006 - Optimizer does table scan for select count(*) +# +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; +EXPLAIN SELECT COUNT(*) FROM t4; +DROP TABLE t1, t2, t3, t4; + +# +# BUG#39185 - Cardinality for merge tables calculated incorrectly. +# +CREATE TABLE t1(a INT, KEY(a)); +INSERT INTO t1 VALUES(0),(1),(2),(3),(4); +ANALYZE TABLE t1; +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'; +SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; +SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; +SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; +DROP TABLE t1, m1; + +--echo # +--echo # Bug #40675 MySQL 5.1 crash with index merge algorithm and Merge tables +--echo # + +--echo # create MYISAM table t1 and insert values into it +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1); + +--echo # 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); + +--echo # 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; + +--echo # Lock tables t1 and t3 for write +LOCK TABLES t1 WRITE, t3 WRITE; + +--echo # Insert values into the merge table t3 +INSERT INTO t3(a,b) VALUES(1,2); + +--echo # 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; + +--echo # Unlock the tables +UNLOCK TABLES; + +--echo # drop the created tables +DROP TABLE t1, t2, t3; + +# +# Bug #41305 server crashes when inserting duplicate row into a merge table +# +--echo # 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); +--error ER_DUP_KEY +insert into m1 (col1) values (1); + +drop table m1, t1; + +--echo # +--echo # Bug#45800 crash when replacing into a merge table and there is a duplicate +--echo # + +--echo # 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; +--echo # insert the duplicate value into the merge table +REPLACE INTO m1 VALUES (666); +SELECT * FROM m1; +DROP TABLE m1, t1; + +--echo # 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; +--echo # insert the duplicate value into the merge table +INSERT INTO m1 VALUES (666) ON DUPLICATE KEY UPDATE c1=c1+1; +SELECT * FROM m1; +DROP TABLE m1, t1; + +--echo # 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); +--echo # insert the duplicate value into the merge table +--error ER_DUP_KEY +INSERT INTO m1 VALUES (3,2); +DROP TABLE m1,t1; + +--echo # 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); +--echo # Try accessing the merge table for inserts (error occurs) +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (1,2); +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (1,4); +DROP TABLE m1,t1; + +# +#Bug #44040 MySQL allows creating a MERGE table upon VIEWs but crashes +#when using it +# + +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); + +--echo #Select should detect that the child table is a view and fail. +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; + +DROP VIEW v1; +DROP TABLE m1, t1; + +--echo # +--echo # Bug #45796: invalid memory reads and writes when altering merge and +--echo # base tables +--echo # + +CREATE TABLE t1(c1 INT) ENGINE=MyISAM; +CREATE TABLE m1(c1 INT) ENGINE=MERGE UNION=(t1); +ALTER TABLE m1 ADD INDEX idx_c1(c1); +# Open the MERGE table and allocate buffers based on children's definition. +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +# Change the child table definition. +ALTER TABLE t1 ADD INDEX idx_c1(c1); +# Check that old buffers are not reused +SELECT * FROM m1; + +DROP TABLE m1; +DROP TABLE t1; + +--echo # +--echo # Bug45781 infinite hang/crash in "opening tables" after handler tries to +--echo # open merge table +--echo # + +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; +--error ER_ILLEGAL_HA +HANDLER m1 OPEN; +DROP TABLE m1,t1,t2,t3,t4,t5,t6,t7; +--error ER_NO_SUCH_TABLE +SELECT 1 FROM m1; # Should not hang! + +--echo # +--echo # Bug #46614: Assertion in show_create_trigger() +--echo # +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(); +--replace_column 7 # +SHOW CREATE TRIGGER tr1; +DROP TRIGGER tr1; +DROP TABLE t1, t2, t3; + +--echo # +--echo # BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't work in 5.1.40) +--echo # +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; +SHOW CREATE TABLE m1; +DROP TABLE m1; + +CREATE TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); +SELECT * FROM `test/1`.m1; +SHOW CREATE TABLE `test/1`.m1; +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; +SHOW CREATE TABLE m1; +DROP TABLE m1; + +CREATE TEMPORARY TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); +SELECT * FROM `test/1`.m1; +SHOW CREATE TABLE `test/1`.m1; +DROP TABLE `test/1`.m1; +DROP TABLE `test/1`.`t/1`; + +DROP DATABASE `test/1`; + +# Test compatibility. Use '@' instead of '/' (was not allowed in 5.0) + +CREATE TABLE `t@1`(a INT); +copy_file std_data/bug48265.frm $MYSQLD_DATADIR/test/m1.frm; +write_file $MYSQLD_DATADIR/test/m1.MRG; +t@1 +EOF +SELECT * FROM m1; +SHOW CREATE TABLE m1; +DROP TABLE `t@1`; + +CREATE DATABASE `test@1`; +CREATE TABLE `test@1`.`t@1`(a INT); +FLUSH TABLE m1; +remove_file $MYSQLD_DATADIR/test/m1.MRG; +write_file $MYSQLD_DATADIR/test/m1.MRG; +./test@1/t@1 +EOF +SELECT * FROM m1; +SHOW CREATE TABLE m1; +DROP TABLE m1; +DROP TABLE `test@1`.`t@1`; +DROP DATABASE `test@1`; + +--echo # +--echo # Bug#51494c rash with join, explain and 'sounds like' operator +--echo # + +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; + +DROP TABLE t2, t1; + +--echo # +--echo # Bug#46339 - crash on REPAIR TABLE merge table USE_FRM +--echo # +# +# Test derived from a proposal of Shane Bester. +# +CREATE TABLE t1 (c1 INT) ENGINE=MYISAM; +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1) INSERT_METHOD=LAST; +# +# REPAIR ... USE_FRM with LOCK TABLES. +# +LOCK TABLE m1 READ; +REPAIR TABLE m1 USE_FRM; +UNLOCK TABLES; +# +# REPAIR ... USE_FRM without LOCK TABLES. +# +# This statement crashed the server (Bug#46339). +# +REPAIR TABLE m1 USE_FRM; +# +DROP TABLE m1,t1; +# +# Test derived from a proposal of Matthias Leich. +# +# Base table is missing. +# +CREATE TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1); +# +# This statement crashed the server (Bug#46339). +# +REPAIR TABLE m1 USE_FRM; +# +# Create base table. +# +CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM; +# +# This statement crashed the server (Bug#46339). +# +REPAIR TABLE m1 USE_FRM; +# +# Normal repair as reference. +# +REPAIR TABLE m1; +# +# Cleanup. +# +DROP TABLE m1, t1; +# +# Same with temporary tables. +# +# Base table is missing. +# +CREATE TEMPORARY TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1); +# +# This statement crashed the server (Bug#46339). +# +REPAIR TABLE m1 USE_FRM; +# +# Create base table. +# +CREATE TEMPORARY TABLE t1 (f1 BIGINT) ENGINE=MyISAM; +# +# This statement crashed the server (Bug#46339). +# +REPAIR TABLE m1 USE_FRM; +# +# Normal repair as reference. +# +REPAIR TABLE m1; +# +# Cleanup. +# +DROP TABLE m1, t1; + +# +# MDEV-5266 MySQL:57657 - Temporary MERGE table with temporary underlying is broken by ALTER +# +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; +drop temporary table t1_temp, tm_temp_temp; + +--echo End of 5.1 tests + +--echo # +--echo # MDEV-4277: Crash inside mi_killed_in_mariadb() with myisammrg +--echo # +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; +select * from t2, t5 where t5.a=999 and t5.b=999; + +drop table t5; +drop table t1,t2,t3,t4; + +--echo End of 5.3 tests + +# +# BUG#35274 - merge table doesn't need any base tables, gives error 124 when +# key accessed +# +CREATE TABLE t1(a INT, KEY(a)) ENGINE=merge; +SELECT MAX(a) FROM t1; +DROP TABLE t1; + +--echo # +--echo # An additional test case for Bug#27430 Crash in subquery code +--echo # when in PS and table DDL changed after PREPARE +--echo # +--echo # Test merge table with too many merge children. +--echo # +set @save_table_open_cache=@@global.table_open_cache; +--echo # +--echo # Set @@global.table_open_cache to minimum +--echo # +set @@global.table_open_cache=400; +select @@table_open_cache; +set @a=null; +let $1 = `select @@table_open_cache;`; +--echo # +--echo # Create 400 merge children +--echo # +--disable_query_log +while ($1) +{ + eval create table t$1 (a int) engine=myisam; + eval set @a=ifnull(concat(@a, ", ", "t$1"), "t$1"); + dec $1; +} +--enable_query_log +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"; +--disable_result_log +--error 0,ER_NEED_REPREPARE +execute stmt; +--error 0,ER_NEED_REPREPARE +execute stmt; +--error 0,ER_NEED_REPREPARE +execute stmt; +deallocate prepare stmt; +--enable_result_log + +--echo # +--echo # Create merge parent +--echo # + +--echo # +--echo # Cleanup +--echo # +let $1 = `select @@table_open_cache;`; +--disable_query_log +while ($1) +{ + eval drop table t$1; + dec $1; +} +--enable_query_log +drop table t_parent; +set @@global.table_open_cache=@save_table_open_cache; + +# +# WL#4144 - Lock MERGE engine children +# +# Test DATA/INDEX DIRECTORY +# +CREATE DATABASE mysql_test1; +--disable_query_log +# data/index directory don't work in HAVE_purify builds. Disable +# build-dependent warnings. +--disable_warnings +--echo CREATE TABLE t1 ... DATA DIRECTORY=... INDEX DIRECTORY=... +eval CREATE TABLE t1 (c1 INT) + DATA DIRECTORY='$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY='$MYSQLTEST_VARDIR/tmp'; +--echo CREATE TABLE mysql_test1.t2 ... DATA DIRECTORY=... INDEX DIRECTORY=... +eval CREATE TABLE mysql_test1.t2 (c1 INT) + DATA DIRECTORY='$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY='$MYSQLTEST_VARDIR/tmp'; +--enable_query_log +--enable_warnings +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; +#--copy_file $MYSQLTEST_DATADIR/test/m1.MRG /tmp/mysql-test-m1.MRG +DROP TABLE t1, mysql_test1.t2, m1; +DROP DATABASE mysql_test1; +# +# Review detected Crash #1. Detaching main tables while in sub statement. +# +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; +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE tm1, t1, t2, t3; +# +# Review detected Crash #2. Trying to attach temporary table twice. +# +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; +DROP FUNCTION f1; +DROP TABLE tm1, t1, t2; +# +# Review suggested test. DDL in a stored function. +# +DELIMITER |; +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| +DELIMITER ;| +--disable_ps2_protocol +SELECT f1() FROM (SELECT 1 UNION SELECT 1) c1; +--enable_ps2_protocol +DROP FUNCTION f1; +DROP TABLE tm1, t1, t2; +# +# Base table. No LOCK TABLES, no functions/triggers. +# +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; +DROP TABLE tm1, t1; +# +# Base table. No LOCK TABLES, sub-statement that is run inside a function. +# +DELIMITER |; +CREATE FUNCTION f1() RETURNS INT +BEGIN + INSERT INTO tm1 VALUES (1); + RETURN (SELECT MAX(c1) FROM tm1); +END| +DELIMITER ;| +CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; +CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST; +SELECT f1(); +DROP FUNCTION f1; +DROP TABLE tm1, t1; +# +# Base table. LOCK TABLES, no functions/triggers. +# +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; +UNLOCK TABLES; +DROP TABLE tm1, t1; +# +# Base table. LOCK TABLES, sub-statement that is run inside a function. +# +DELIMITER |; +CREATE FUNCTION f1() RETURNS INT +BEGIN + INSERT INTO tm1 VALUES (1); + RETURN (SELECT MAX(c1) FROM tm1); +END| +DELIMITER ;| +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(); +UNLOCK TABLES; +DROP FUNCTION f1; +DROP TABLE tm1, t1; +# +# Base table. LOCK TABLES statement that locks a table that has a trigger +# that inserts into a merge table, so an attempt is made to lock tables +# of a sub-statement. +# +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; +SELECT * FROM t2; +UNLOCK TABLES; +DROP TRIGGER t2_ai; +DROP TABLE tm1, t1, t2; +# +# Temporary. No LOCK TABLES, no functions/triggers. +# +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; +DROP TABLE tm1, t1; +# +# Temporary. No LOCK TABLES, sub-statement that is run inside a function. +# +DELIMITER |; +CREATE FUNCTION f1() RETURNS INT +BEGIN + INSERT INTO tm1 VALUES (1); + RETURN (SELECT MAX(c1) FROM tm1); +END| +DELIMITER ;| +CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) + INSERT_METHOD=LAST; +SELECT f1(); +DROP FUNCTION f1; +DROP TABLE tm1, t1; +# +# Temporary. LOCK TABLES, no functions/triggers. +# +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; +UNLOCK TABLES; +DROP TABLE tm1, t1, t9; +# +# Temporary. LOCK TABLES, sub-statement that is run inside a function. +# +DELIMITER |; +CREATE FUNCTION f1() RETURNS INT +BEGIN + INSERT INTO tm1 VALUES (1); + RETURN (SELECT MAX(c1) FROM tm1); +END| +DELIMITER ;| +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(); +UNLOCK TABLES; +DROP FUNCTION f1; +DROP TABLE tm1, t1, t9; +# +# Temporary. LOCK TABLES statement that locks a table that has a trigger +# that inserts into a merge table, so an attempt is made to lock tables +# of a sub-statement. +# +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; +SELECT * FROM t2; +UNLOCK TABLES; +DROP TRIGGER t2_ai; +DROP TABLE tm1, t1, t2; +--echo # +--echo # Don't allow an update of a MERGE child in a trigger +--echo # if the table's already being modified by the main +--echo # statement. +--echo # +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; +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +INSERT INTO tm1 VALUES (1); +SELECT * FROM tm1; +UNLOCK TABLES; +LOCK TABLE t1 WRITE, tm1 WRITE; +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +INSERT INTO tm1 VALUES (1); +SELECT * FROM tm1; +UNLOCK TABLES; +DROP TRIGGER tm1_ai; +DROP TABLE tm1, t1; + +--echo # +--echo # Don't select MERGE child when trying to get a prelocked table. +--echo # +--echo # Due to a limitation demonstrated by the previous test +--echo # we can no longer use a write-locked prelocked table. +--echo # The test is kept for historical purposes. +--echo # +CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; +CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) + INSERT_METHOD=LAST; +--enable_prepare_warnings +CREATE TRIGGER tm1_ai AFTER INSERT ON tm1 + FOR EACH ROW SELECT max(c1) FROM t1 INTO @var; +--disable_prepare_warnings +LOCK TABLE tm1 WRITE, t1 WRITE; +INSERT INTO tm1 VALUES (1); +SELECT * FROM tm1; +UNLOCK TABLES; +LOCK TABLE t1 WRITE, tm1 WRITE; +INSERT INTO tm1 VALUES (1); +SELECT * FROM tm1; +UNLOCK TABLES; +DROP TRIGGER tm1_ai; +DROP TABLE tm1, t1; + +# Don't resurrect chopped off prelocked tables. +# The problem is not visible by test results; only by debugging. +# +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; +--enable_prepare_warnings +CREATE TRIGGER t2_au AFTER UPDATE ON t2 + FOR EACH ROW SELECT MAX(c1) FROM t1 INTO @var; +--disable_prepare_warnings +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,,); + send UPDATE t2, tm1 SET t2.c1=f1(); +connection default; +# Force reopen in other thread. +#sleep 1; +FLUSH TABLES; +#sleep 1; +FLUSH TABLES; +#sleep 1; +UNLOCK TABLES; + connection con1; + reap; + disconnect con1; +connection default; +SELECT * FROM tm1; +DROP TRIGGER t2_au; +DROP FUNCTION f1; +DROP TABLE tm1, t1, t2, t3, t4, t5; + +--echo # +--echo # Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE +--echo # +CREATE TEMPORARY TABLE t1 (c1 INT); +ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists,t1); +OPTIMIZE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine +--echo # More tests with TEMPORARY MERGE table and permanent children. +--echo # First without locked tables. +--echo # +# +--echo # +CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE m1; +SELECT * FROM m1; +INSERT INTO t1 VALUES (111, 121); +INSERT INTO m1 VALUES (211, 221); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +# +--echo # +ALTER TABLE m1 RENAME m2; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +# +--echo # +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +--error ER_TABLE_EXISTS_ERROR +ALTER TABLE m2 RENAME m1; +DROP TABLE m1; +ALTER TABLE m2 RENAME m1; +SHOW CREATE TABLE m1; +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 ADD COLUMN c3 INT; +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (212, 222, 232); +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +ALTER TABLE t1 ADD COLUMN c3 INT; +ALTER TABLE t2 ADD COLUMN c3 INT; +INSERT INTO m1 VALUES (212, 222, 232); +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 DROP COLUMN c3; +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (213, 223); +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +ALTER TABLE t1 DROP COLUMN c3; +ALTER TABLE t2 DROP COLUMN c3; +INSERT INTO m1 VALUES (213, 223); +SELECT * FROM m1; +# +--echo # +CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3); +INSERT INTO m1 VALUES (311, 321); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; +# +--echo # +CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3,t4); +INSERT INTO m1 VALUES (411, 421); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; +SELECT * FROM t4; +# +--echo # +ALTER TABLE m1 ENGINE=MyISAM; +SHOW CREATE TABLE m1; +INSERT INTO m1 VALUES (511, 521); +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +# +--echo # +CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (611, 621); +SELECT * FROM m1; +DROP TABLE t1; +SELECT * FROM m1; +# +# +--echo # +--echo # +SHOW CREATE TABLE m1; +# +--echo # +CREATE TABLE m2 SELECT * FROM m1; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TEMPORARY TABLE m2 SELECT * FROM m1; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST; +--error ER_WRONG_MRG_TABLE +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +--error ER_WRONG_OBJECT +CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST SELECT * FROM m1; +# +--echo # +--error ER_WRONG_OBJECT +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST SELECT * FROM m1; +# +--echo # +CREATE TABLE m2 LIKE m1; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TEMPORARY TABLE m2 LIKE m1; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +# +# +--echo # +--echo # +LOCK TABLE m1 WRITE, m2 WRITE; +SELECT * FROM m1,m2 WHERE m1.c1=m2.c1; +UNLOCK TABLES; +# +DROP TABLE t1, t2, t3, t4, m1, m2; +# +# +# +--echo # +--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine +--echo # More tests with TEMPORARY MERGE table and permanent children. +--echo # (continued) Now the same with locked table. +--echo # +CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM; +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE m1; +SELECT * FROM m1; +INSERT INTO t1 VALUES (111, 121); +INSERT INTO m1 VALUES (211, 221); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +# +--echo # +LOCK TABLE m1 WRITE, t1 WRITE, t2 WRITE; +# +--echo # +ALTER TABLE m1 RENAME m2; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +# +--echo # +CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +--error ER_TABLE_EXISTS_ERROR +ALTER TABLE m2 RENAME m1; +DROP TABLE m1; +ALTER TABLE m2 RENAME m1; +SHOW CREATE TABLE m1; +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 ADD COLUMN c3 INT; +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (212, 222, 232); +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +ALTER TABLE t1 ADD COLUMN c3 INT; +ALTER TABLE t2 ADD COLUMN c3 INT; +INSERT INTO m1 VALUES (212, 222, 232); +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 DROP COLUMN c3; +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (213, 223); +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +ALTER TABLE t1 DROP COLUMN c3; +ALTER TABLE t2 DROP COLUMN c3; +INSERT INTO m1 VALUES (213, 223); +SELECT * FROM m1; +# +--echo # +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; +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; +# +--echo # +CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM; +ALTER TABLE m1 UNION=(t1,t2,t3,t4); +INSERT INTO m1 VALUES (411, 421); +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; +SELECT * FROM t4; +# +--echo # +ALTER TABLE m1 ENGINE=MyISAM; +SHOW CREATE TABLE m1; +INSERT INTO m1 VALUES (511, 521); +SELECT * FROM m1; +# +--echo # +ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2) + INSERT_METHOD=LAST; +SELECT * FROM m1; +SELECT * FROM t1; +SELECT * FROM t2; +# +--echo # +CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (611, 621); +SELECT * FROM m1; +DROP TABLE t1; +SELECT * FROM m1; +# +# +--echo # +--echo # +SHOW CREATE TABLE m1; +--error ER_TABLE_NOT_LOCKED +CREATE TABLE m2 SELECT * FROM m1; +# +--echo # +CREATE TEMPORARY TABLE m2 SELECT * FROM m1; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST; +SELECT * FROM m2; +LOCK TABLE m1 WRITE, m2 WRITE; +UNLOCK TABLES; +DROP TABLE m2; +LOCK TABLE m1 WRITE; +# +--echo # +--echo # ER_TABLE_NOT_LOCKED is returned in ps-protocol +--error ER_WRONG_OBJECT, ER_TABLE_NOT_LOCKED +CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST SELECT * FROM m1; +# +--echo # +--error ER_WRONG_OBJECT +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST SELECT * FROM m1; +# +--echo # +CREATE TEMPORARY TABLE m2 LIKE m1; +SHOW CREATE TABLE m2; +LOCK TABLE m1 WRITE, m2 WRITE; +SHOW CREATE TABLE m2; +SELECT * FROM m2; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +DROP TABLE m2; +# +--echo # +CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4) + INSERT_METHOD=LAST; +LOCK TABLE m1 WRITE, m2 WRITE; +INSERT INTO m2 SELECT * FROM m1; +SELECT * FROM m2; +# +--echo # +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4, m1, m2; + +--echo # +--echo # Bug47098 assert in MDL_context::destroy on HANDLER +--echo # <damaged merge table> OPEN +--echo # +--echo # Test that merge tables are closed correctly when opened using +--echo # HANDLER ... OPEN. +--echo # 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; +--error ER_ILLEGAL_HA +HANDLER t3 OPEN; +DROP TABLE t1, t2, t3; +connection default; +--echo # all mdl_tickets must have been released. +disconnect con1; +--echo # 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; +--error ER_WRONG_MRG_TABLE +HANDLER t3 OPEN; +DROP TABLE t1, t3; +connection default; +--echo # all mdl_tickets must have been released. +disconnect con1; + +--echo # +--echo # A test case for Bug#47648 main.merge fails sporadically +--echo # +--echo # Make sure we correctly maintain lex->query_tables_last_own. +--echo # +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(); +prepare stmt from "select * from t4 where c1 < f1()"; +execute stmt; +execute stmt; +execute stmt; +drop function f1; +--error ER_SP_DOES_NOT_EXIST +execute stmt; +--error ER_SP_DOES_NOT_EXIST +execute stmt; +drop table t4, t3, t2, t1; + + +--echo # +--echo # Bug#51240 ALTER TABLE of a locked MERGE table fails +--echo # + +CREATE TABLE t1 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1); +LOCK TABLE m1 WRITE; +# This used to cause an error. +ALTER TABLE m1 ADD INDEX (c1); + +UNLOCK TABLES; +DROP TABLE m1, t1; + +--echo # +--echo # Locking the merge table won't implicitly lock children. +--echo # +CREATE TABLE t1 (c1 INT); +CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1); +LOCK TABLE m1 WRITE; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +ALTER TABLE t1 ADD INDEX (c1); +LOCK TABLE m1 WRITE, t1 WRITE; +ALTER TABLE t1 ADD INDEX (c1); +UNLOCK TABLES; +DROP TABLE m1, t1; + +--echo # +--echo # Test for bug #37371 "CREATE TABLE LIKE merge loses UNION parameter" +--echo # +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; +--echo # Table definitions should match +show create table m1; +show create table m2; +drop tables m1, m2, t1; + +--echo # +--echo # Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()" +--echo # Check that unique_table() works correctly for merge tables. +--echo # +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); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from m1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from m2)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t2)); + +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, m1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, m2)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, t1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from t3, t2)); + +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, m1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, m2)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, t1)); +--error ER_UPDATE_TABLE_USED +insert into m1 (a) values ((select max(a) from tmp, t2)); + +--error ER_VIEW_PREVENT_UPDATE +insert into m1 (a) values ((select max(a) from v1)); +--error ER_VIEW_PREVENT_UPDATE +insert into m1 (a) values ((select max(a) from tmp, v1)); + + +drop view v1; +drop temporary table tmp; +drop table t1, t2, t3, m1, m2; + + +--echo # +--echo # Bug#56494 Segfault in upgrade_shared_lock_to_exclusive() for +--echo # REPAIR of merge table +--echo # + +CREATE TABLE t1(a INT); +ALTER TABLE t1 engine= MERGE UNION (t_not_exists); +# This caused the segfault +ANALYZE TABLE t1; +CHECK TABLE t1; +CHECKSUM TABLE t1; +OPTIMIZE TABLE t1; +REPAIR TABLE t1; + +# This caused an assert +REPAIR TABLE t1 USE_FRM; + +DROP TABLE t1; +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT) engine= MERGE UNION (t1); +# This caused an assert +REPAIR TABLE t2 USE_FRM; + +DROP TABLE t1, t2; + + +--echo # +--echo # Bug#57002 Assert in upgrade_shared_lock_to_exclusive() +--echo # for ALTER TABLE + MERGE tables +--echo # + +CREATE TABLE t1(a INT) engine=myisam; +CREATE TABLE m1(a INT) engine=merge UNION(t1); +LOCK TABLES t1 READ, m1 WRITE; + +# This caused an assert +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +ALTER TABLE t1 engine=myisam; + +UNLOCK TABLES; +DROP TABLE m1, t1; + + +--echo # +--echo # Test for bug #11754210 - "45777: CHECK TABLE DOESN'T SHOW ALL +--echo # PROBLEMS FOR MERGE TABLE COMPLIANCE IN 5.1" +--echo # +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); +--error ER_WRONG_MRG_TABLE +select * from m1; +--echo # The below CHECK and REPAIR TABLE statements should +--echo # report all problems with underlying tables: +--echo # - absence of 't2', +--echo # - missing base table for 't3', +--echo # - wrong engine of 't4'. +check table m1; +repair table m1; +--echo # Clean-up. +drop tables m1, t1, t4; +drop view t3; + + +--echo # +--echo # MDEV-10424 Assertion `ticket == __null' failed in MDL_request::set_type +--echo # +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; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +DROP TABLE t1, tmerge; + +--echo # +--echo # End of 5.5 tests +--echo # + +--echo # +--echo # Additional coverage for refactoring which is made as part +--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege +--echo # to allow temp table operations". +--echo # +--echo # Check that prelocking works correctly for various variants of +--echo # merge tables. +--echo # +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; +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; +drop table m1; +create temporary table m1 (a int) engine=merge union=(t2); +select f1() from t1; +drop tables t1, t2, m1; +drop function f1; +--echo # +--echo # Check that REPAIR/CHECK and CHECKSUM statements work correctly +--echo # 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; +repair table m1; +checksum table m1; +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; +repair table m1; +checksum table m1; +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; +repair table m1; +checksum table m1; +drop tables t1, m1; + +# Check effect of Bug#27480-preliminary patch: +# a merge-table with non-existing children, opened from a prelocked list. + +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); + +--error ER_WRONG_MRG_TABLE +DELETE FROM t1; + +DROP TRIGGER trg1; +DROP TABLE t1; +DROP TABLE m1; + +set global default_storage_engine=@save_default_storage_engine; + +# Check that all connections opened by test cases in this file are really +# gone so execution of other tests won't be affected by their presence. +--source include/wait_until_count_sessions.inc + +--echo # +--echo # MDEV-31083 ASAN use-after-poison in myrg_attach_children +--echo # + +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; +DROP TABLE mrg, t1; + +--echo End of 10.5 tests + +--echo # +--echo # End of 10.0 tests +--echo # + +--echo # +--echo # MDEV-27407 Different ASC/DESC index attributes on MERGE and underlying table can cause wrong results +--echo # +create table t (a int, key(a desc)) engine=myisam; +create table tm (a int, key(a)) engine=merge union(t); +--error ER_WRONG_MRG_TABLE +select * from tm; +drop table tm, t; + +--echo # +--echo # MDEV-27586 Auto-increment does not work with DESC on MERGE table +--echo # +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; +drop table tm, t; + +--echo # +--echo # End of 10.8 tests +--echo # |