1# Test that must have symlink. eg. using DATA/INDEX DIR 2# (DATA/INDEX DIR requires symlinks) 3# This test is only useful for MyISAM, since no other engine supports DATA DIR 4-- source include/have_partition.inc 5-- source include/have_symlink.inc 6# remove the not_windows line after fixing bug#33687 7# symlinks must also work for files, not only directories 8# as in --skip-symbolic-links 9-- source include/not_windows.inc 10-- disable_warnings 11DROP TABLE IF EXISTS t1; 12DROP DATABASE IF EXISTS mysqltest2; 13-- enable_warnings 14 15--echo # 16--echo # Test for WL#4445: EXCHANGE PARTITION 17--echo # 18--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 19eval CREATE TABLE t1 (a INT) 20ENGINE = MyISAM 21PARTITION BY LIST (a) 22(PARTITION p0 VALUES IN (0) 23 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 24 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 25 PARTITION p1 VALUES IN (1) 26 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 27 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 28 PARTITION p2 VALUES IN (2)); 29 30--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 31eval CREATE TABLE t2 (a INT) 32ENGINE = MyISAM 33 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 34 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp'; 35 36--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 37SHOW CREATE TABLE t1; 38--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 39SHOW CREATE TABLE t2; 40INSERT INTO t1 VALUES (0), (1), (2); 41ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; 42--error ER_TABLES_DIFFERENT_METADATA 43ALTER TABLE t1 EXCHANGE PARTITION p2 WITH TABLE t2; 44SELECT * FROM t1; 45SELECT * FROM t2; 46DROP TABLE t1, t2; 47# skipped because of bug#52354 48#CREATE TABLE t1 LIKE t2; 49#ALTER TABLE t1 PARTITION BY LIST (a) 50#(PARTITION p0 VALUES in (0)); 51#--error ER_TABLES_DIFFERENT_METADATA 52#ALTER TABLE t1 EXCHANGE PARTITION p2 WITH TABLE t2; 53#SELECT * FROM t2; 54#DROP TABLE t1, t2; 55 56# 57# Bug 32091: Security breach via directory changes 58# 59# The below test shows that a pre-existing table mysqltest2.t1 cannot be 60# replaced by a user with no rights in 'mysqltest2'. The altered table 61# test.t1 will be altered (remove partitioning) into the test directory 62# and having its partitions removed from the mysqltest2 directory. 63# (the partitions data files are named <tablename>#P#<partname>.MYD 64# and will not collide with a non partitioned table's data files.) 65# NOTE: the privileges on files and directories are the same for all 66# database users in mysqld, though mysqld enforces privileges on 67# the database and table levels which in turn maps to directories and 68# files, but not the other way around (any db-user can use any 69# directory or file that the mysqld-process can use, via DATA/INDEX DIR) 70# this is the security flaw that was used in bug#32091 and bug#32111 71 72-- echo # Creating two non colliding tables mysqltest2.t1 and test.t1 73-- echo # test.t1 have partitions in mysqltest2-directory! 74-- echo # user root: 75 CREATE USER mysqltest_1@localhost; 76 CREATE DATABASE mysqltest2; 77 USE mysqltest2; 78 CREATE TABLE t1 (a INT) ENGINE = MyISAM; 79 INSERT INTO t1 VALUES (0); 80connect(con1,localhost,mysqltest_1,,); 81-- echo # user mysqltest_1: 82 USE test; 83 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 84 eval CREATE TABLE t1 (a INT) 85 ENGINE = MyISAM 86 PARTITION BY LIST (a) ( 87 PARTITION p0 VALUES IN (0) 88 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 89 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 90 PARTITION p1 VALUES IN (1) 91 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 92 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 93 PARTITION p2 VALUES IN (2) 94 ); 95 -- echo # without the patch for bug#32091 this would create 96 -- echo # files mysqltest2/t1.MYD + .MYI and possible overwrite 97 -- echo # the mysqltest2.t1 table (depending on bug#32111) 98 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 99 ALTER TABLE t1 REMOVE PARTITIONING; 100 INSERT INTO t1 VALUES (1); 101 SELECT * FROM t1; 102connection default; 103-- echo # user root: 104 USE mysqltest2; 105 FLUSH TABLES; 106 -- echo # if the patch works, this should be different 107 -- echo # and before the patch they were the same! 108 SELECT * FROM t1; 109 USE test; 110 SELECT * FROM t1; 111 DROP TABLE t1; 112 DROP DATABASE mysqltest2; 113# The below test shows that a pre-existing partition can not be 114# destroyed by a new partition from another table. 115# (Remember that a table or partition that uses the DATA/INDEX DIR 116# is symlinked and thus has 117# 1. the real file in the DATA/INDEX DIR and 118# 2. a symlink in its default database directory pointing to 119# the real file. 120# So it is using/blocking 2 files in (in 2 different directories 121-- echo # test that symlinks can not overwrite files when CREATE TABLE 122-- echo # user root: 123 124 CREATE DATABASE mysqltest2; 125 USE mysqltest2; 126 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 127 eval CREATE TABLE t1 (a INT) 128 ENGINE = MyISAM 129 PARTITION BY LIST (a) ( 130 PARTITION p0 VALUES IN (0) 131 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 132 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 133 PARTITION p1 VALUES IN (1) 134 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 135 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' 136 ); 137connection con1; 138-- echo # user mysqltest_1: 139 USE test; 140 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 141 -- error 1,1 142 eval CREATE TABLE t1 (a INT) 143 ENGINE = MyISAM 144 PARTITION BY LIST (a) ( 145 PARTITION p0 VALUES IN (0) 146 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 147 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 148 PARTITION p1 VALUES IN (1) 149 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 150 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' 151 ); 152 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 153 -- error 1,1 154 eval CREATE TABLE t1 (a INT) 155 ENGINE = MyISAM 156 PARTITION BY LIST (a) ( 157 PARTITION p0 VALUES IN (0) 158 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 159 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 160 PARTITION p1 VALUES IN (1) 161 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 162 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' 163 ); 164connection default; 165-- echo # user root (cleanup): 166 DROP DATABASE mysqltest2; 167 USE test; 168 DROP USER mysqltest_1@localhost; 169 disconnect con1; 170 171# 172# Bug #24633 SQL MODE "NO_DIR_IN_CREATE" does not work with partitioned tables 173# 174 175--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 176eval create table t2 (i int ) 177ENGINE = MyISAM 178partition by range (i) 179( 180 partition p01 values less than (1000) 181 data directory="$MYSQLTEST_VARDIR/tmp" 182 index directory="$MYSQLTEST_VARDIR/tmp" 183); 184 185set @org_mode=@@sql_mode; 186set @@sql_mode='NO_DIR_IN_CREATE'; 187select @@sql_mode; 188create table t1 (i int ) 189ENGINE = MyISAM 190partition by range (i) 191( 192 partition p01 values less than (1000) 193 data directory='/not/existing' 194 index directory='/not/existing' 195); 196 197--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 198show create table t2; 199DROP TABLE t1, t2; 200set @@sql_mode=@org_mode; 201 202# 203# Bug 21350: Data Directory problems 204# 205# Added ER_WRONG_TABLE_NAME and reported bug#39045 206-- error ER_WRONG_ARGUMENTS, ER_WRONG_TABLE_NAME 207create table t1 (a int) 208ENGINE = MyISAM 209partition by key (a) 210(partition p0 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); 211 212# 213# Insert a test that manages to create the first partition and fails with 214# the second, ensure that we clean up afterwards in a proper manner. 215# 216# Added ER_WRONG_TABLE_NAME and reported bug#39045 217--error ER_WRONG_ARGUMENTS, ER_WRONG_TABLE_NAME 218create table t1 (a int) 219ENGINE = MyISAM 220partition by key (a) 221(partition p0, 222 partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); 223 224--echo # 225--echo # MDEV-25917 create table like fails if source table is partitioned and engine is myisam or aria with data directory. 226--echo # 227--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 228eval CREATE TABLE t1 (a INT) 229ENGINE = MyISAM 230PARTITION BY LIST (a) 231(PARTITION p0 VALUES IN (0) 232 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 233 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 234 PARTITION p1 VALUES IN (1) 235 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 236 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 237 PARTITION p2 VALUES IN (2)); 238 239CREATE TABLE t2 LIKE t1; 240--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 241SHOW CREATE TABLE t1; 242SHOW CREATE TABLE t2; 243DROP TABLE t1, t2; 244 245--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 246eval CREATE TABLE t1 ( 247 ID int(11) NOT NULL, 248 type int(11)) Engine=MyISAM 249PARTITION BY RANGE(ID) 250SUBPARTITION BY HASH(type) 251( 252 PARTITION p01 VALUES LESS THAN(100) 253 (SUBPARTITION s11 254 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 255 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 256 SUBPARTITION s12 257 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 258 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' 259 ), 260 PARTITION p11 VALUES LESS THAN(200) 261 (SUBPARTITION s21, SUBPARTITION s22), 262 PARTITION p21 VALUES LESS THAN MAXVALUE 263 (SUBPARTITION s31 264 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 265 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', 266 SUBPARTITION s32 267 DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' 268 INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' 269 ) 270); 271 272CREATE TABLE t2 LIKE t1; 273--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 274SHOW CREATE TABLE t1; 275SHOW CREATE TABLE t2; 276 277DROP TABLE t1, t2; 278