507 lines
26 KiB
Plaintext
507 lines
26 KiB
Plaintext
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 6 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single
|
|
test/t1#p#p0 test/t1#p#p0 97 7 Dynamic 0 Single
|
|
test/t1#p#p1 test/t1#p#p1 97 7 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 6 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single
|
|
test/t1#p#p0 test/t1#p#p0 97 7 Dynamic 0 Single
|
|
test/t1#p#p1 test/t1#p#p1 97 7 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 6 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single
|
|
test/t11#p#p0 test/t11#p#p0 97 7 Dynamic 0 Single
|
|
test/t11#p#p1 test/t11#p#p1 97 7 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 6 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single
|
|
test/t1#p#p0 test/t1#p#p0 97 7 Dynamic 0 Single
|
|
test/t1#p#p1 test/t1#p#p1 97 7 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 6 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single
|
|
test/t1#p#p0#sp#s0 test/t1#p#p0#sp#s0 97 7 Dynamic 0 Single
|
|
test/t1#p#p0#sp#s1 test/t1#p#p0#sp#s1 97 7 Dynamic 0 Single
|
|
test/t1#p#p1#sp#s2 test/t1#p#p1#sp#s2 97 7 Dynamic 0 Single
|
|
test/t1#p#p1#sp#s3 test/t1#p#p1#sp#s3 97 7 Dynamic 0 Single
|
|
test/t1#p#p2#sp#s4 test/t1#p#p2#sp#s4 97 7 Dynamic 0 Single
|
|
test/t1#p#p2#sp#s5 test/t1#p#p2#sp#s5 97 7 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 6 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single
|
|
test/t1#p#p0#sp#s0 test/t1#p#p0#sp#s0 97 7 Dynamic 0 Single
|
|
test/t1#p#p0#sp#s1 test/t1#p#p0#sp#s1 97 7 Dynamic 0 Single
|
|
test/t1#p#p1#sp#s2 test/t1#p#p1#sp#s2 97 7 Dynamic 0 Single
|
|
test/t1#p#p1#sp#s3 test/t1#p#p1#sp#s3 97 7 Dynamic 0 Single
|
|
test/t1#p#p2#sp#s4 test/t1#p#p2#sp#s4 97 7 Dynamic 0 Single
|
|
test/t1#p#p2#sp#s5 test/t1#p#p2#sp#s5 97 7 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 6 Dynamic 0 Single
|
|
mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single
|
|
test/t1#p#p0 test/t1#p#p0 97 6 Dynamic 0 Single
|
|
test/t_file_per_table_off innodb_system 161 7 Dynamic 0 System
|
|
test/t_file_per_table_on test/t_file_per_table_on 33 7 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;
|