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