innodb/r/innodb-tablespace.result

SET default_storage_engine=InnoDB;
#
# TABLESPACE related tests.
#
#
# CREATE TABLE ... DATA DIRECTORY
# Innodb does not support INDEX DIRECTORY.
#
SET SESSION innodb_strict_mode = ON;
CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir';
ERROR HY000: Table storage engine for 't1' doesn't have this option
SHOW WARNINGS;
Level	Code	Message
Warning	1478	InnoDB: INDEX DIRECTORY is not supported
Error	1031	Table storage engine for 't1' doesn't have this option
#
# Without strict mode, INDEX DIRECTORY is just ignored
#
SET SESSION innodb_strict_mode = OFF;
CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir';
Warnings:
Warning	1618	<INDEX DIRECTORY> option ignored
SHOW WARNINGS;
Level	Code	Message
Warning	1618	<INDEX DIRECTORY> option ignored
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=latin1
DROP TABLE t1;
#
# Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON.
#
SET SESSION innodb_strict_mode = ON;
SET GLOBAL innodb_file_per_table=OFF;
CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
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.
Error	1031	Table storage engine for 't1' doesn't have this option
#
# Without strict mode, DATA DIRECTORY without innodb_file_per_table=ON is just ignored.
#
SET SESSION innodb_strict_mode = OFF;
CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
Warnings:
Warning	1478	InnoDB: DATA DIRECTORY requires innodb_file_per_table.
Warning	1618	<DATA DIRECTORY> option ignored
SHOW WARNINGS;
Level	Code	Message
Warning	1478	InnoDB: DATA DIRECTORY requires innodb_file_per_table.
Warning	1618	<DATA DIRECTORY> option ignored
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=latin1
DROP TABLE t1;
# Now set innodb_file_per_table so that DATA DIRECTORY can be tested.
SET GLOBAL innodb_file_per_table=ON;
#
# Create the tablespace in MYSQL_TMP_DIR/alt_dir
# InnoDB will create the sub-directories if needed.
#
CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
SHOW WARNINGS;
Level	Code	Message
INSERT INTO t1 VALUES (1, "Create the tablespace");
SELECT * FROM t1;
a	b
1	Create the tablespace
#
# Check if link file exists  in MYSQLD_DATADIR
#
---- MYSQLD_DATADIR/test
t1.frm
t1.isl
# Check if tablespace file exists where we specified in DATA DIRECTORY
---- MYSQL_TMP_DIR/alt_dir/test
t1.ibd
#
# Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
#
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=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
# Show that the new system tables have this table in them correctly
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
name	n_cols	file_format	row_format
test/t1	5	Antelope	Compact
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
test/t1	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir/test/t1.ibd
#
# Show that the system tables are updated on drop table
#
DROP TABLE t1;
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%'
       ORDER BY name;
name	n_cols	file_format	row_format
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
#
# Create the same table a second time in the same place
#
CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
INSERT INTO t1 VALUES (2, "Create the same table a second time in the same place");
SELECT * FROM t1;
a	b
2	Create the same table a second time in the same place
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=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%'
       ORDER BY name;
name	n_cols	file_format	row_format
test/t1	5	Antelope	Compact
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
test/t1	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir/test/t1.ibd
---- MYSQLD_DATADIR/test
t1.frm
t1.isl
---- MYSQL_TMP_DIR/alt_dir/test
t1.ibd
#
# Truncate the table, then insert and verify
#
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (3, "Truncate the table, then insert");
SELECT * FROM t1;
a	b
3	Truncate the table, then insert
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%'
       ORDER BY name;
name	n_cols	file_format	row_format
test/t1	5	Antelope	Compact
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
test/t1	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir/test/t1.ibd
---- MYSQLD_DATADIR/test
t1.frm
t1.isl
---- MYSQL_TMP_DIR/alt_dir/test
t1.ibd
#
# Rename the table, then insert and verify
#
RENAME TABLE t1 TO t2;
INSERT INTO t2 VALUES (4, "Rename the table, then insert");
SELECT * FROM t2;
a	b
3	Truncate the table, then insert
4	Rename the table, then insert
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%'
       ORDER BY name;
name	n_cols	file_format	row_format
test/t2	5	Antelope	Compact
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
test/t2	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir/test/t2.ibd
---- MYSQLD_DATADIR/test
t2.frm
t2.isl
---- MYSQL_TMP_DIR/alt_dir/test
t2.ibd
#
# CREATE TABLE LIKE does not retain DATA DIRECTORY automatically.
#
CREATE TABLE t3 LIKE t2;
INSERT INTO t3 VALUES (5, "CREATE TABLE LIKE");
SELECT * FROM t3;
a	b
5	CREATE TABLE LIKE
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%'
       ORDER BY name;
name	n_cols	file_format	row_format
test/t2	5	Antelope	Compact
test/t3	5	Antelope	Compact
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
test/t2	Antelope	Compact or Redundant
test/t3	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir/test/t2.ibd
MYSQLD_DATADIR/test/t3.ibd
---- MYSQLD_DATADIR/test
t2.frm
t2.isl
t3.frm
t3.ibd
#
# Now make sure the tables can be fully dropped.
#
DROP TABLE t2, t3;
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%'
       ORDER BY name;
name	n_cols	file_format	row_format
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
---- MYSQLD_DATADIR/test
---- MYSQL_TMP_DIR/alt_dir/test
#
# Be sure SQL MODE "NO_DIR_IN_CREATE" prevents the use of DATA DIRECTORY
#
SET @org_mode=@@sql_mode;
SET @@sql_mode='NO_DIR_IN_CREATE';
SELECT @@sql_mode;
@@sql_mode
NO_DIR_IN_CREATE
CREATE TABLE t1 (a int, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
Warnings:
Warning	1618	<DATA DIRECTORY> option ignored
SHOW WARNINGS;
Level	Code	Message
Warning	1618	<DATA DIRECTORY> option ignored
INSERT INTO t1 VALUES (6, "SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY");
DROP TABLE t1;
set @@sql_mode=@org_mode;
#
# MySQL engine does not allow DATA DIRECTORY to be
# within --datadir for any engine, including InnoDB
#
CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY 'MYSQLD_DATADIR/test';
ERROR HY000: Incorrect arguments to DATA DIRECTORY
# TEMPORARY tables are incompatible with DATA DIRECTORY
SET SESSION innodb_strict_mode = ON;
CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
ERROR HY000: Table storage engine for 't1' doesn't have this option
SHOW WARNINGS;
Level	Code	Message
Warning	1478	InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
Error	1031	Table storage engine for 't1' doesn't have this option
SET SESSION innodb_strict_mode = OFF;
CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
Warnings:
Warning	1478	InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
Warning	1618	<DATA DIRECTORY> option ignored
SHOW WARNINGS;
Level	Code	Message
Warning	1478	InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
Warning	1618	<DATA DIRECTORY> option ignored
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` text,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t1;
---- MYSQLD_DATADIR/test
---- MYSQL_TMP_DIR/alt_dir/test
#
# Create the remote table via static DDL statements in a stored procedure
#
CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; END |
CALL static_proc;
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir/test/t1.ibd
INSERT INTO t1 VALUES (7, "Create the remote table via static DDL statements");
SELECT * FROM t1;
a	b
7	Create the remote table via static DDL statements
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=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
---- MYSQLD_DATADIR/test
t1.frm
t1.isl
---- MYSQL_TMP_DIR/alt_dir/test
t1.ibd
DROP PROCEDURE static_proc;
DROP TABLE t1;
#
# Create the remote table via dynamic DDL statements in a stored procedure
#
CREATE PROCEDURE dynamic_proc() BEGIN PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'"; EXECUTE stmt1; END |
CALL dynamic_proc;
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir/test/t1.ibd
INSERT INTO t1 VALUES (8, "Create the remote table via dynamic DDL statements");
SELECT * FROM t1;
a	b
8	Create the remote table via dynamic DDL statements
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=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
---- MYSQLD_DATADIR/test
t1.frm
t1.isl
---- MYSQL_TMP_DIR/alt_dir/test
t1.ibd
DROP PROCEDURE dynamic_proc;
DROP TABLE t1;
#
# CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY
#
CREATE TABLE emp (
id INT NOT NULL,
store_name VARCHAR(30),
parts VARCHAR(30),
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION east VALUES IN (10,20,30)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east',
PARTITION north VALUES IN (40,50,60)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north',
PARTITION west VALUES IN (70,80,100)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'
);
INSERT INTO emp values(1,'Oracle','NUTT',10);
INSERT INTO emp values(2,'HUAWEI','BOLT',40);
INSERT INTO emp values(3,'IBM','NAIL',70);
SHOW CREATE TABLE emp;
Table	Create Table
emp	CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `store_name` varchar(30) DEFAULT NULL,
  `parts` varchar(30) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (store_id)
(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables
WHERE name LIKE 'test%'
       ORDER BY name;
name	n_cols	file_format	row_format
test/emp#p#east	7	Antelope	Compact
test/emp#p#north	7	Antelope	Compact
test/emp#p#west	7	Antelope	Compact
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
test/emp#p#east	Antelope	Compact or Redundant
test/emp#p#north	Antelope	Compact or Redundant
test/emp#p#west	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
SELECT * FROM emp;
id	store_name	parts	store_id
1	Oracle	NUTT	10
2	HUAWEI	BOLT	40
3	IBM	NAIL	70
---- MYSQLD_DATADIR/test
emp#p#east.isl
emp#p#north.isl
emp#p#west.isl
emp.frm
emp.par
---- MYSQL_TMP_DIR/alt_dir_east/test
emp#p#east.ibd
---- MYSQL_TMP_DIR/alt_dir_north/test
emp#p#north.ibd
---- MYSQL_TMP_DIR/alt_dir_west/test
emp#p#west.ibd
#
# DROP one PARTITION.
#
ALTER TABLE emp DROP PARTITION west;
SHOW CREATE TABLE emp;
Table	Create Table
emp	CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `store_name` varchar(30) DEFAULT NULL,
  `parts` varchar(30) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (store_id)
(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB) */
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables
WHERE name LIKE 'test%'
       ORDER BY name;
name	n_cols	file_format	row_format
test/emp#p#east	7	Antelope	Compact
test/emp#p#north	7	Antelope	Compact
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
test/emp#p#east	Antelope	Compact or Redundant
test/emp#p#north	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
SELECT * FROM emp;
id	store_name	parts	store_id
1	Oracle	NUTT	10
2	HUAWEI	BOLT	40
---- MYSQLD_DATADIR/test
emp#p#east.isl
emp#p#north.isl
emp.frm
emp.par
---- MYSQL_TMP_DIR/alt_dir_east/test
emp#p#east.ibd
---- MYSQL_TMP_DIR/alt_dir_north/test
emp#p#north.ibd
---- MYSQL_TMP_DIR/alt_dir_west/test
#
# ADD the PARTITION back.
#
ALTER TABLE emp ADD PARTITION (
PARTITION west VALUES IN (70,80,100)
DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west');
SHOW CREATE TABLE emp;
Table	Create Table
emp	CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `store_name` varchar(30) DEFAULT NULL,
  `parts` varchar(30) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (store_id)
(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables
WHERE name LIKE 'test%'
       ORDER BY name;
name	n_cols	file_format	row_format
test/emp#p#east	7	Antelope	Compact
test/emp#p#north	7	Antelope	Compact
test/emp#p#west	7	Antelope	Compact
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
test/emp#p#east	Antelope	Compact or Redundant
test/emp#p#north	Antelope	Compact or Redundant
test/emp#p#west	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
INSERT INTO emp VALUES(3,'IBM','NAIL',70);
SELECT * FROM emp;
id	store_name	parts	store_id
1	Oracle	NUTT	10
2	HUAWEI	BOLT	40
3	IBM	NAIL	70
---- MYSQLD_DATADIR/test
emp#p#east.isl
emp#p#north.isl
emp#p#west.isl
emp.frm
emp.par
---- MYSQL_TMP_DIR/alt_dir_east/test
emp#p#east.ibd
---- MYSQL_TMP_DIR/alt_dir_north/test
emp#p#north.ibd
---- MYSQL_TMP_DIR/alt_dir_west/test
emp#p#west.ibd
#
# TRUNCATE one PARTITION.
#
ALTER TABLE emp TRUNCATE PARTITION west;
SHOW CREATE TABLE emp;
Table	Create Table
emp	CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `store_name` varchar(30) DEFAULT NULL,
  `parts` varchar(30) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (store_id)
(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables
WHERE name LIKE 'test%'
       ORDER BY name;
name	n_cols	file_format	row_format
test/emp#p#east	7	Antelope	Compact
test/emp#p#north	7	Antelope	Compact
test/emp#p#west	7	Antelope	Compact
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name	file_format	row_format
mysql/innodb_index_stats	Antelope	Compact or Redundant
mysql/innodb_table_stats	Antelope	Compact or Redundant
mysql/slave_master_info	Antelope	Compact or Redundant
mysql/slave_relay_log_info	Antelope	Compact or Redundant
mysql/slave_worker_info	Antelope	Compact or Redundant
test/emp#p#east	Antelope	Compact or Redundant
test/emp#p#north	Antelope	Compact or Redundant
test/emp#p#west	Antelope	Compact or Redundant
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
SELECT * FROM emp;
id	store_name	parts	store_id
1	Oracle	NUTT	10
2	HUAWEI	BOLT	40
INSERT INTO emp VALUES(3,'IBM','NAIL',70);
SELECT * FROM emp;
id	store_name	parts	store_id
1	Oracle	NUTT	10
2	HUAWEI	BOLT	40
3	IBM	NAIL	70
---- MYSQLD_DATADIR/test
emp#p#east.isl
emp#p#north.isl
emp#p#west.isl
emp.frm
emp.par
---- MYSQL_TMP_DIR/alt_dir_east/test
emp#p#east.ibd
---- MYSQL_TMP_DIR/alt_dir_north/test
emp#p#north.ibd
---- MYSQL_TMP_DIR/alt_dir_west/test
emp#p#west.ibd
DROP TABLE emp;
#
# Cleanup
#