DROP TABLE IF EXISTS t1;
DROP DATABASE IF EXISTS mysqltest2;
#
# Test for WL#4445: EXCHANGE PARTITION
#
CREATE TABLE t1 (a INT)
ENGINE = MyISAM
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
  INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
PARTITION p1 VALUES IN (1)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
  INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
PARTITION p2 VALUES IN (2));
CREATE TABLE t2 (a INT)
ENGINE = MyISAM
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
  INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY LIST (`a`)
(PARTITION `p0` VALUES IN (0) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM,
 PARTITION `p1` VALUES IN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM,
 PARTITION `p2` VALUES IN (2) ENGINE = MyISAM)
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/' INDEX DIRECTORY='MYSQLTEST_VARDIR/tmp/'
INSERT INTO t1 VALUES (0), (1), (2);
ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
ALTER TABLE t1 EXCHANGE PARTITION p2 WITH TABLE t2;
ERROR HY000: Tables have different definitions
SELECT * FROM t1;
a
1
2
SELECT * FROM t2;
a
0
DROP TABLE t1, t2;
# Creating two non colliding tables mysqltest2.t1 and test.t1
# test.t1 have partitions in mysqltest2-directory!
# user root:
CREATE USER mysqltest_1@localhost;
GRANT ALL ON test.* to mysqltest_1@localhost;
CREATE DATABASE mysqltest2;
USE mysqltest2;
CREATE TABLE t1 (a INT) ENGINE = MyISAM;
INSERT INTO t1 VALUES (0);
connect con1,localhost,mysqltest_1,,;
# user mysqltest_1:
USE test;
CREATE TABLE t1 (a INT)
ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
     INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
PARTITION p1 VALUES IN (1)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
     INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
PARTITION p2 VALUES IN (2)
);
# without the patch for bug#32091 this would create
# files mysqltest2/t1.MYD + .MYI and possible overwrite
# the mysqltest2.t1 table (depending on bug#32111)
ALTER TABLE t1 REMOVE PARTITIONING;
INSERT INTO t1 VALUES (1);
SELECT * FROM t1;
a
1
connection default;
# user root:
USE mysqltest2;
FLUSH TABLES;
# if the patch works, this should be different
# and before the patch they were the same!
SELECT * FROM t1;
a
0
USE test;
SELECT * FROM t1;
a
1
DROP TABLE t1;
DROP DATABASE mysqltest2;
# test that symlinks can not overwrite files when CREATE TABLE
# user root:
CREATE DATABASE mysqltest2;
USE mysqltest2;
CREATE TABLE t1 (a INT)
ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
     INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
PARTITION p1 VALUES IN (1)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
     INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp'
   );
connection con1;
# user mysqltest_1:
USE test;
CREATE TABLE t1 (a INT)
ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
     INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
PARTITION p1 VALUES IN (1)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
     INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp'
   );
Got one of the listed errors
CREATE TABLE t1 (a INT)
ENGINE = MyISAM
PARTITION BY LIST (a) (
PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
     INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
PARTITION p1 VALUES IN (1)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
     INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp'
  );
Got one of the listed errors
connection default;
# user root (cleanup):
DROP DATABASE mysqltest2;
USE test;
DROP USER mysqltest_1@localhost;
disconnect con1;
create table t2 (i int )
ENGINE = MyISAM
partition by range (i)
(
partition p01 values less than (1000)
data directory="MYSQLTEST_VARDIR/tmp"
    index directory="MYSQLTEST_VARDIR/tmp"
);
set @org_mode=@@sql_mode;
set @@sql_mode='NO_DIR_IN_CREATE';
select @@sql_mode;
@@sql_mode
NO_DIR_IN_CREATE
create table t1 (i int )
ENGINE = MyISAM
partition by range (i)
(
partition p01 values less than (1000)
data directory='/not/existing'
    index directory='/not/existing'
);
Warnings:
Warning	1618	<DATA DIRECTORY> option ignored
Warning	1618	<INDEX DIRECTORY> option ignored
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY RANGE (`i`)
(PARTITION `p01` VALUES LESS THAN (1000) ENGINE = MyISAM)
DROP TABLE t1, t2;
set @@sql_mode=@org_mode;
create table t1 (a int)
ENGINE = MyISAM
partition by key (a)
(partition p0 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data');
Got one of the listed errors
create table t1 (a int)
ENGINE = MyISAM
partition by key (a)
(partition p0,
partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data');
Got one of the listed errors
#
# MDEV-25917 create table like fails if source table is partitioned and engine is myisam or aria with data directory.
#
CREATE TABLE t1 (a INT)
ENGINE = MyISAM
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (0)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
  INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
PARTITION p1 VALUES IN (1)
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
  INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
PARTITION p2 VALUES IN (2));
CREATE TABLE t2 LIKE t1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY LIST (`a`)
(PARTITION `p0` VALUES IN (0) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM,
 PARTITION `p1` VALUES IN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM,
 PARTITION `p2` VALUES IN (2) ENGINE = MyISAM)
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY LIST (`a`)
(PARTITION `p0` VALUES IN (0) ENGINE = MyISAM,
 PARTITION `p1` VALUES IN (1) ENGINE = MyISAM,
 PARTITION `p2` VALUES IN (2) ENGINE = MyISAM)
DROP TABLE t1, t2;
CREATE TABLE t1 (
ID int(11) NOT NULL,
type int(11)) Engine=MyISAM
PARTITION BY RANGE(ID)
SUBPARTITION BY HASH(type)
(
PARTITION p01 VALUES LESS THAN(100)
(SUBPARTITION s11
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
           INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
SUBPARTITION s12
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
           INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp'
      ),
PARTITION p11 VALUES LESS THAN(200)
(SUBPARTITION s21, SUBPARTITION s22),
PARTITION p21 VALUES LESS THAN MAXVALUE
(SUBPARTITION s31
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
           INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp',
SUBPARTITION s32
DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
           INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp'
      )
);
CREATE TABLE t2 LIKE t1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `ID` int(11) NOT NULL,
  `type` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY RANGE (`ID`)
SUBPARTITION BY HASH (`type`)
(PARTITION `p01` VALUES LESS THAN (100)
 (SUBPARTITION `s11` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM,
  SUBPARTITION `s12` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM),
 PARTITION `p11` VALUES LESS THAN (200)
 (SUBPARTITION `s21` ENGINE = MyISAM,
  SUBPARTITION `s22` ENGINE = MyISAM),
 PARTITION `p21` VALUES LESS THAN MAXVALUE
 (SUBPARTITION `s31` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM,
  SUBPARTITION `s32` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM))
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ID` int(11) NOT NULL,
  `type` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY RANGE (`ID`)
SUBPARTITION BY HASH (`type`)
(PARTITION `p01` VALUES LESS THAN (100)
 (SUBPARTITION `s11` ENGINE = MyISAM,
  SUBPARTITION `s12` ENGINE = MyISAM),
 PARTITION `p11` VALUES LESS THAN (200)
 (SUBPARTITION `s21` ENGINE = MyISAM,
  SUBPARTITION `s22` ENGINE = MyISAM),
 PARTITION `p21` VALUES LESS THAN MAXVALUE
 (SUBPARTITION `s31` ENGINE = MyISAM,
  SUBPARTITION `s32` ENGINE = MyISAM))
DROP TABLE t1, t2;