1DROP TABLE IF EXISTS t1, t2; 2DROP DATABASE IF EXISTS mysqltest2; 3# 4# Test for WL#4445: EXCHANGE PARTITION 5# 6CREATE TABLE t1 (a INT) 7ENGINE = MyISAM 8PARTITION BY LIST (a) 9(PARTITION p0 VALUES IN (0) 10DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 11 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', 12PARTITION p1 VALUES IN (1) 13DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 14 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', 15PARTITION p2 VALUES IN (2)); 16Warnings: 17Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 18CREATE TABLE t2 (a INT) 19ENGINE = MyISAM 20DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 21 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp'; 22SHOW CREATE TABLE t1; 23Table Create Table 24t1 CREATE TABLE `t1` ( 25 `a` int(11) DEFAULT NULL 26) ENGINE=MyISAM DEFAULT CHARSET=latin1 27/*!50100 PARTITION BY LIST (a) 28(PARTITION p0 VALUES IN (0) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM, 29 PARTITION p1 VALUES IN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM, 30 PARTITION p2 VALUES IN (2) ENGINE = MyISAM) */ 31Warnings: 32Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 33SHOW CREATE TABLE t2; 34Table Create Table 35t2 CREATE TABLE `t2` ( 36 `a` int(11) DEFAULT NULL 37) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/' INDEX DIRECTORY='MYSQLTEST_VARDIR/tmp/' 38INSERT INTO t1 VALUES (0), (1), (2); 39ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; 40ERROR HY000: Tables have different definitions 41ALTER TABLE t1 EXCHANGE PARTITION p2 WITH TABLE t2; 42ERROR HY000: Tables have different definitions 43SELECT * FROM t2; 44a 45DROP TABLE t1, t2; 46# Creating two non colliding tables mysqltest2.t1 and test.t1 47# test.t1 have partitions in mysqltest2-directory! 48# user root: 49CREATE USER mysqltest_1@localhost; 50# Need FILE permission to use external datadir or indexdir. 51GRANT FILE ON *.* TO mysqltest_1@localhost; 52CREATE DATABASE mysqltest2; 53USE mysqltest2; 54CREATE TABLE t1 (a INT) ENGINE = MyISAM; 55INSERT INTO t1 VALUES (0); 56# user mysqltest_1: 57USE test; 58CREATE TABLE t1 (a INT) 59ENGINE = MyISAM 60PARTITION BY LIST (a) ( 61PARTITION p0 VALUES IN (0) 62DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 63 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', 64PARTITION p1 VALUES IN (1) 65DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 66 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', 67PARTITION p2 VALUES IN (2) 68); 69Warnings: 70Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 71# without the patch for bug#32091 this would create 72# files mysqltest2/t1.MYD + .MYI and possible overwrite 73# the mysqltest2.t1 table (depending on bug#32111) 74ALTER TABLE t1 REMOVE PARTITIONING; 75INSERT INTO t1 VALUES (1); 76SELECT * FROM t1; 77a 781 79# user root: 80USE mysqltest2; 81FLUSH TABLES; 82# if the patch works, this should be different 83# and before the patch they were the same! 84SELECT * FROM t1; 85a 860 87USE test; 88SELECT * FROM t1; 89a 901 91DROP TABLE t1; 92DROP DATABASE mysqltest2; 93# test that symlinks can not overwrite files when CREATE TABLE 94# user root: 95CREATE DATABASE mysqltest2; 96USE mysqltest2; 97CREATE TABLE t1 (a INT) 98ENGINE = MyISAM 99PARTITION BY LIST (a) ( 100PARTITION p0 VALUES IN (0) 101DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 102 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', 103PARTITION p1 VALUES IN (1) 104DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 105 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp' 106 ); 107Warnings: 108Warning 1287 The partition engine, used by table 'mysqltest2.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 109# user mysqltest_1: 110USE test; 111CREATE TABLE t1 (a INT) 112ENGINE = MyISAM 113PARTITION BY LIST (a) ( 114PARTITION p0 VALUES IN (0) 115DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 116 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', 117PARTITION p1 VALUES IN (1) 118DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 119 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp' 120 ); 121Got one of the listed errors 122CREATE TABLE t1 (a INT) 123ENGINE = MyISAM 124PARTITION BY LIST (a) ( 125PARTITION p0 VALUES IN (0) 126DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 127 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', 128PARTITION p1 VALUES IN (1) 129DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' 130 INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp' 131 ); 132Got one of the listed errors 133# user root (cleanup): 134DROP DATABASE mysqltest2; 135USE test; 136DROP USER mysqltest_1@localhost; 137create table t2 (i int ) 138ENGINE = MyISAM 139partition by range (i) 140( 141partition p01 values less than (1000) 142data directory="MYSQLTEST_VARDIR/tmp" 143 index directory="MYSQLTEST_VARDIR/tmp" 144); 145Warnings: 146Warning 1287 The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead. 147set @org_mode=@@sql_mode; 148set @@sql_mode='NO_DIR_IN_CREATE'; 149Warnings: 150Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 151select @@sql_mode; 152@@sql_mode 153NO_DIR_IN_CREATE 154create table t1 (i int ) 155ENGINE = MyISAM 156partition by range (i) 157( 158partition p01 values less than (1000) 159data directory='/not/existing' 160 index directory='/not/existing' 161); 162Warnings: 163Warning 1618 <DATA DIRECTORY> option ignored 164Warning 1618 <INDEX DIRECTORY> option ignored 165Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 166show create table t2; 167Table Create Table 168t2 CREATE TABLE `t2` ( 169 `i` int(11) DEFAULT NULL 170) ENGINE=MyISAM DEFAULT CHARSET=latin1 171/*!50100 PARTITION BY RANGE (i) 172(PARTITION p01 VALUES LESS THAN (1000) ENGINE = MyISAM) */ 173Warnings: 174Warning 1287 The partition engine, used by table 'test.t2', is deprecated and will be removed in a future release. Please use native partitioning instead. 175DROP TABLE t1, t2; 176set @@sql_mode=@org_mode; 177Warnings: 178Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 179create table t1 (a int) 180ENGINE = MyISAM 181partition by key (a) 182(partition p0 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); 183Got one of the listed errors 184create table t1 (a int) 185ENGINE = MyISAM 186partition by key (a) 187(partition p0, 188partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); 189Got one of the listed errors 190