SET default_storage_engine=InnoDB; # # TABLESPACE related tests for the partition engine and InnoDB. # # The partition engine can send DATA DIRECTORY to InnoDB. # In strict mode, it is an error if innodb_file_per_table = OFF # or INDEX DIRECTORY is used. SET SESSION innodb_strict_mode = ON; SET GLOBAL innodb_file_per_table = OFF; CREATE TABLE t1 (a int KEY, b text) ENGINE = InnoDB PARTITION BY HASH (a) (PARTITION p0 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data' INDEX DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data', PARTITION p1 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data' INDEX DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data'); ERROR HY000: Table storage engine for 't1' doesn't have this option SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table. Warning 1478 InnoDB: INDEX DIRECTORY is not supported Error 1031 Table storage engine for 't1' doesn't have this option # Try again with innodb_file_per_table = ON and no INDEX DIRECTORY. SET GLOBAL innodb_file_per_table = ON; CREATE TABLE t1 (a int KEY, b text) ENGINE = InnoDB PARTITION BY HASH (a) (PARTITION p0 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data', PARTITION p1 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2'); SHOW WARNINGS; Level Code Message SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (`a`) (PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data/' ENGINE = InnoDB, PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2/' ENGINE = InnoDB) */ === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single test/t1#p#p0 test/t1#p#p0 97 8 Dynamic 0 Single test/t1#p#p1 test/t1#p#p1 97 8 Dynamic 0 Single === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0.ibd test/t1#p#p1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0 TABLESPACE InnoDB NORMAL test/t1#p#p0 MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0.ibd test/t1#p#p1 TABLESPACE InnoDB NORMAL test/t1#p#p1 MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1.ibd # Verifying .ibd files # Verifying that there are no MyISAM files # Test TRUNCATE TABLE with partitioned InnoDB tables INSERT INTO t1 VALUES (1, "red"); INSERT INTO t1 VALUES (2, "green"); INSERT INTO t1 VALUES (3, "blue"); SELECT * FROM t1; a b 2 green 1 red 3 blue TRUNCATE TABLE t1; SELECT * FROM t1; a b SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (`a`) (PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data/' ENGINE = InnoDB, PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2/' ENGINE = InnoDB) */ === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single test/t1#p#p0 test/t1#p#p0 97 8 Dynamic 0 Single test/t1#p#p1 test/t1#p#p1 97 8 Dynamic 0 Single === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0.ibd test/t1#p#p1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0 TABLESPACE InnoDB NORMAL test/t1#p#p0 MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0.ibd test/t1#p#p1 TABLESPACE InnoDB NORMAL test/t1#p#p1 MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1.ibd # Verifying .ibd files # Test RENAME TABLE with partitioned InnoDB tables RENAME TABLE t1 TO t11; SHOW CREATE TABLE t11; Table Create Table t11 CREATE TABLE `t11` ( `a` int(11) NOT NULL, `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (`a`) (PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data/' ENGINE = InnoDB, PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2/' ENGINE = InnoDB) */ === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single test/t11#p#p0 test/t11#p#p0 97 8 Dynamic 0 Single test/t11#p#p1 test/t11#p#p1 97 8 Dynamic 0 Single === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t11#p#p0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data/test/t11#p#p0.ibd test/t11#p#p1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data2/test/t11#p#p1.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t11#p#p0 TABLESPACE InnoDB NORMAL test/t11#p#p0 MYSQL_TMP_DIR/alternate_dir/data/test/t11#p#p0.ibd test/t11#p#p1 TABLESPACE InnoDB NORMAL test/t11#p#p1 MYSQL_TMP_DIR/alternate_dir/data2/test/t11#p#p1.ibd # Verifying .ibd files DROP TABLE t11; # Test the previous DDL as a prepared statement. SET GLOBAL innodb_file_per_table = ON; PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text) ENGINE = InnoDB PARTITION BY HASH (a) (PARTITION p0 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data', PARTITION p1 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2')"; EXECUTE stmt1; SHOW WARNINGS; Level Code Message DEALLOCATE PREPARE stmt1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (`a`) (PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data/' ENGINE = InnoDB, PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2/' ENGINE = InnoDB) */ === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single test/t1#p#p0 test/t1#p#p0 97 8 Dynamic 0 Single test/t1#p#p1 test/t1#p#p1 97 8 Dynamic 0 Single === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0.ibd test/t1#p#p1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0 TABLESPACE InnoDB NORMAL test/t1#p#p0 MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0.ibd test/t1#p#p1 TABLESPACE InnoDB NORMAL test/t1#p#p1 MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1.ibd DROP TABLE t1; # Test DATA DIRECTORY with Sub-partitions. SET GLOBAL innodb_file_per_table = ON; CREATE TABLE t1 (id INT, purchased DATE) engine=InnoDB PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data', SUBPARTITION s1 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2' ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data', SUBPARTITION s3 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2' ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data', SUBPARTITION s5 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2' ) ); SHOW WARNINGS; Level Code Message INSERT INTO t1 VALUES(1,'1980-05-31'); INSERT INTO t1 VALUES(2,'2090-05-31'); INSERT INTO t1 VALUES(3,'2012-05-31'); INSERT INTO t1 VALUES(4,'1970-05-31'); INSERT INTO t1 VALUES(5,'1985-05-31'); INSERT INTO t1 VALUES(6,'2006-05-31'); SELECT * FROM t1; id purchased 4 1970-05-31 1 1980-05-31 5 1985-05-31 2 2090-05-31 3 2012-05-31 6 2006-05-31 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `purchased` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (year(`purchased`)) SUBPARTITION BY HASH (to_days(`purchased`)) (PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data/' ENGINE = InnoDB, SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2/' ENGINE = InnoDB), PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data/' ENGINE = InnoDB, SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2/' ENGINE = InnoDB), PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION s4 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data/' ENGINE = InnoDB, SUBPARTITION s5 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2/' ENGINE = InnoDB)) */ === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single test/t1#p#p0#sp#s0 test/t1#p#p0#sp#s0 97 8 Dynamic 0 Single test/t1#p#p0#sp#s1 test/t1#p#p0#sp#s1 97 8 Dynamic 0 Single test/t1#p#p1#sp#s2 test/t1#p#p1#sp#s2 97 8 Dynamic 0 Single test/t1#p#p1#sp#s3 test/t1#p#p1#sp#s3 97 8 Dynamic 0 Single test/t1#p#p2#sp#s4 test/t1#p#p2#sp#s4 97 8 Dynamic 0 Single test/t1#p#p2#sp#s5 test/t1#p#p2#sp#s5 97 8 Dynamic 0 Single === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0#sp#s0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0#sp#s0.ibd test/t1#p#p0#sp#s1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p0#sp#s1.ibd test/t1#p#p1#sp#s2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p1#sp#s2.ibd test/t1#p#p1#sp#s3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1#sp#s3.ibd test/t1#p#p2#sp#s4 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p2#sp#s4.ibd test/t1#p#p2#sp#s5 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p2#sp#s5.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0#sp#s0 TABLESPACE InnoDB NORMAL test/t1#p#p0#sp#s0 MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0#sp#s0.ibd test/t1#p#p0#sp#s1 TABLESPACE InnoDB NORMAL test/t1#p#p0#sp#s1 MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p0#sp#s1.ibd test/t1#p#p1#sp#s2 TABLESPACE InnoDB NORMAL test/t1#p#p1#sp#s2 MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p1#sp#s2.ibd test/t1#p#p1#sp#s3 TABLESPACE InnoDB NORMAL test/t1#p#p1#sp#s3 MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1#sp#s3.ibd test/t1#p#p2#sp#s4 TABLESPACE InnoDB NORMAL test/t1#p#p2#sp#s4 MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p2#sp#s4.ibd test/t1#p#p2#sp#s5 TABLESPACE InnoDB NORMAL test/t1#p#p2#sp#s5 MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p2#sp#s5.ibd # Verifying .ibd files DROP TABLE t1; # Same as above except with ROW_FORMAT=Dyamic. SET GLOBAL innodb_file_per_table = ON; CREATE TABLE t1 (id INT, purchased DATE) engine = innodb row_format = dynamic PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data', SUBPARTITION s1 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2' ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data', SUBPARTITION s3 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2' ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data', SUBPARTITION s5 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2' ) ); SHOW WARNINGS; Level Code Message INSERT INTO t1 VALUES(1,'1980-05-31'); INSERT INTO t1 VALUES(2,'2090-05-31'); INSERT INTO t1 VALUES(3,'2012-05-31'); INSERT INTO t1 VALUES(4,'1970-05-31'); INSERT INTO t1 VALUES(5,'1985-05-31'); INSERT INTO t1 VALUES(6,'2006-05-31'); SELECT * FROM t1; id purchased 4 1970-05-31 1 1980-05-31 5 1985-05-31 2 2090-05-31 3 2012-05-31 6 2006-05-31 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `purchased` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC /*!50100 PARTITION BY RANGE (year(`purchased`)) SUBPARTITION BY HASH (to_days(`purchased`)) (PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data/' ENGINE = InnoDB, SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2/' ENGINE = InnoDB), PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data/' ENGINE = InnoDB, SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2/' ENGINE = InnoDB), PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION s4 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data/' ENGINE = InnoDB, SUBPARTITION s5 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2/' ENGINE = InnoDB)) */ === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single test/t1#p#p0#sp#s0 test/t1#p#p0#sp#s0 97 8 Dynamic 0 Single test/t1#p#p0#sp#s1 test/t1#p#p0#sp#s1 97 8 Dynamic 0 Single test/t1#p#p1#sp#s2 test/t1#p#p1#sp#s2 97 8 Dynamic 0 Single test/t1#p#p1#sp#s3 test/t1#p#p1#sp#s3 97 8 Dynamic 0 Single test/t1#p#p2#sp#s4 test/t1#p#p2#sp#s4 97 8 Dynamic 0 Single test/t1#p#p2#sp#s5 test/t1#p#p2#sp#s5 97 8 Dynamic 0 Single === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0#sp#s0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0#sp#s0.ibd test/t1#p#p0#sp#s1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p0#sp#s1.ibd test/t1#p#p1#sp#s2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p1#sp#s2.ibd test/t1#p#p1#sp#s3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1#sp#s3.ibd test/t1#p#p2#sp#s4 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p2#sp#s4.ibd test/t1#p#p2#sp#s5 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p2#sp#s5.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0#sp#s0 TABLESPACE InnoDB NORMAL test/t1#p#p0#sp#s0 MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0#sp#s0.ibd test/t1#p#p0#sp#s1 TABLESPACE InnoDB NORMAL test/t1#p#p0#sp#s1 MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p0#sp#s1.ibd test/t1#p#p1#sp#s2 TABLESPACE InnoDB NORMAL test/t1#p#p1#sp#s2 MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p1#sp#s2.ibd test/t1#p#p1#sp#s3 TABLESPACE InnoDB NORMAL test/t1#p#p1#sp#s3 MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1#sp#s3.ibd test/t1#p#p2#sp#s4 TABLESPACE InnoDB NORMAL test/t1#p#p2#sp#s4 MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p2#sp#s4.ibd test/t1#p#p2#sp#s5 TABLESPACE InnoDB NORMAL test/t1#p#p2#sp#s5 MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p2#sp#s5.ibd # Verifying .ibd files # # Cleanup # DROP TABLE t1; CREATE TABLE t1 (a INT) ENGINE = InnoDB DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_table' PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 3 (PARTITION p0 VALUES LESS THAN (10) (SUBPARTITION sp0, SUBPARTITION sp1 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data_subpart', SUBPARTITION sp2), PARTITION p1 VALUES LESS THAN (20) DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_part' (SUBPARTITION sp3, SUBPARTITION sp4 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data_subpart', SUBPARTITION sp5), PARTITION p2 VALUES LESS THAN (30) (SUBPARTITION sp6, SUBPARTITION sp7 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data_subpart', SUBPARTITION sp8)); # Verifying .ibd files SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (`a`) SUBPARTITION BY HASH (`a`) (PARTITION p0 VALUES LESS THAN (10) (SUBPARTITION sp0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_table/' ENGINE = InnoDB, SUBPARTITION sp1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_subpart/' ENGINE = InnoDB, SUBPARTITION sp2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_table/' ENGINE = InnoDB), PARTITION p1 VALUES LESS THAN (20) (SUBPARTITION sp3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_part/' ENGINE = InnoDB, SUBPARTITION sp4 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_subpart/' ENGINE = InnoDB, SUBPARTITION sp5 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_part/' ENGINE = InnoDB), PARTITION p2 VALUES LESS THAN (30) (SUBPARTITION sp6 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_table/' ENGINE = InnoDB, SUBPARTITION sp7 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_subpart/' ENGINE = InnoDB, SUBPARTITION sp8 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_table/' ENGINE = InnoDB)) */ DROP TABLE t1; # Test of table where the engine returns the DATA DIR. CREATE TABLE t1 (a int not null, primary key (a)) ENGINE=InnoDB PARTITION BY KEY (a) (PARTITION p0 DATA DIRECTORY 'MYSQL_TMP_DIR/alternate_dir/data_part', PARTITION p1); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY KEY (a) (PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_part/' ENGINE = InnoDB, PARTITION p1 ENGINE = InnoDB) */ SET @@global.innodb_file_per_table = ON; CREATE TABLE t_file_per_table_on (a int not null auto_increment primary key, b varchar(128)) ENGINE = InnoDB; SET @@global.innodb_file_per_table = OFF; CREATE TABLE t_file_per_table_off (a int not null auto_increment primary key, b varchar(128)) ENGINE = InnoDB; SHOW CREATE TABLE t_file_per_table_on; Table Create Table t_file_per_table_on CREATE TABLE `t_file_per_table_on` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(128) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SHOW CREATE TABLE t_file_per_table_off; Table Create Table t_file_per_table_off CREATE TABLE `t_file_per_table_off` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(128) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SET @@global.innodb_file_per_table = ON; SHOW CREATE TABLE t_file_per_table_on; Table Create Table t_file_per_table_on CREATE TABLE `t_file_per_table_on` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(128) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SHOW CREATE TABLE t_file_per_table_off; Table Create Table t_file_per_table_off CREATE TABLE `t_file_per_table_off` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(128) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # Restart to get DATA DIRECTORY read from the engine (InnoDB dictionary) # restart ALTER TABLE t1 COALESCE PARTITION 1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY KEY (a) (PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_part' ENGINE = InnoDB) */ SET @old_sql_quote_show_create=@@sql_quote_show_create; SET @@sql_quote_show_create=0; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE t1 ( a int(11) NOT NULL, PRIMARY KEY (a) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY KEY (a) (PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data_part' ENGINE = InnoDB) */ SET @@sql_quote_show_create=@old_sql_quote_show_create; === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 7 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 7 Dynamic 0 Single test/t1#p#p0 test/t1#p#p0 97 7 Dynamic 0 Single test/t_file_per_table_off innodb_system 161 8 Dynamic 0 System test/t_file_per_table_on test/t_file_per_table_on 33 8 Dynamic 0 Single === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alternate_dir/data_part/test/t1#p#p0.ibd test/t_file_per_table_on Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_file_per_table_on.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd test/t1#p#p0 TABLESPACE InnoDB NORMAL test/t1#p#p0 MYSQL_TMP_DIR/alternate_dir/data_part/test/t1#p#p0.ibd test/t_file_per_table_on TABLESPACE InnoDB NORMAL test/t_file_per_table_on MYSQLD_DATADIR/test/t_file_per_table_on.ibd DROP TABLE t1; SET @@global.innodb_file_per_table = ON; SHOW CREATE TABLE t_file_per_table_on; Table Create Table t_file_per_table_on CREATE TABLE `t_file_per_table_on` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(128) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SHOW CREATE TABLE t_file_per_table_off; Table Create Table t_file_per_table_off CREATE TABLE `t_file_per_table_off` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(128) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SET @@global.innodb_file_per_table = OFF; SHOW CREATE TABLE t_file_per_table_on; Table Create Table t_file_per_table_on CREATE TABLE `t_file_per_table_on` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(128) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SHOW CREATE TABLE t_file_per_table_off; Table Create Table t_file_per_table_off CREATE TABLE `t_file_per_table_off` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(128) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP TABLE t_file_per_table_on; DROP TABLE t_file_per_table_off;