diff options
Diffstat (limited to 'mysql-test/main/merge.test')
| -rw-r--r-- | mysql-test/main/merge.test | 2934 |
1 files changed, 0 insertions, 2934 deletions
diff --git a/mysql-test/main/merge.test b/mysql-test/main/merge.test deleted file mode 100644 index f8f421994e9..00000000000 --- a/mysql-test/main/merge.test +++ /dev/null @@ -1,2934 +0,0 @@ -# -# 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 ;| -SELECT f1() FROM (SELECT 1 UNION SELECT 1) c1; -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-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 # - ---echo # ---echo # MDEV-30088 Assertion `cond_selectivity <= 1.0' failed in get_range_limit_read_cost ---echo # - -CREATE TABLE t1 (a TIMESTAMP, KEY(a)) ENGINE=MRG_MyISAM; -explain SELECT a, COUNT(*) FROM t1 WHERE a >= '2000-01-01 00:00:00' GROUP BY a; -SELECT a, COUNT(*) FROM t1 WHERE a >= '2000-01-01 00:00:00' GROUP BY a; -DROP TABLE t1; - ---echo # ---echo # MDEV-30525: Assertion `ranges > 0' fails in IO_AND_CPU_COST handler::keyread_time ---echo # -CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; -CREATE TABLE t2 (a INT, KEY(a)) ENGINE=MyISAM; -CREATE TABLE tm (a INT, KEY(a)) ENGINE=MRG_MyISAM UNION=(t1,t2); -SELECT DISTINCT a FROM tm WHERE a > 50; -DROP TABLE tm, t1, t2; - ---echo # Testcase 2: -CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; -CREATE TABLE t2 (a INT, KEY(a)) ENGINE=MyISAM; -CREATE TABLE tm (a INT, KEY(a)) ENGINE=MERGE UNION = (t1, t2) INSERT_METHOD=FIRST; -ANALYZE TABLE tm PERSISTENT FOR ALL; -SELECT DISTINCT a FROM (SELECT * FROM tm WHERE a iS NOT NULL) AS sq; -DROP TABLE tm, t1, t2; - ---echo # ---echo # MDEV-30568 Assertion `cond_selectivity <= 1.000000001' failed in get_range_limit_read_cost ---echo # -CREATE TABLE t1 (f INT, KEY(f)) ENGINE=MyISAM; -CREATE TABLE t2 (f INT, KEY(f)) ENGINE=MyISAM; -CREATE TABLE tm (f INT, KEY(f)) ENGINE=MERGE UNION = (t1, t2); -SELECT DISTINCT f FROM tm WHERE f IN (47, 126, 97, 48, 73, 0); -DROP TABLE tm, t1, t2; - ---echo # ---echo # End of 11.0 tests ---echo # ---echo # ---echo # MDEV-29174: UPDATE of view that uses MERGE table ---echo # - -CREATE TABLE t1 (a int) ENGINE=MERGE; -CREATE VIEW v1 AS SELECT a FROM t1; -UPDATE v1 SET a=0; -DROP VIEW v1; -DROP TABLE t1; - ---echo # End of 11.1 tests |