SET default_storage_engine=InnoDB;
#
# A series of tests to make sure tables are opened after restart.
# Bug#13357607 Compressed file-per-table tablespaces fail to open
#
set global innodb_file_per_table=on;
set global innodb_file_format='Barracuda';
#
# Create and insert records into a DYNAMIC row formatted table.
#
CREATE TABLE t1(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=REDUNDANT  ENGINE=InnoDB;
INSERT INTO t1 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t1 (SELECT 0, c2, c3, c4, c5 FROM t1);
INSERT INTO t1 (SELECT 0, c2, c3, c4, c5 FROM t1);
INSERT INTO t1 (SELECT 0, c2, c3, c4, c5 FROM t1);
INSERT INTO t1 (SELECT 0, c2, c3, c4, c5 FROM t1);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT
SELECT count(*) FROM t1;
count(*)
16
#
# Create and insert records into a COMPACT row formatted table.
#
CREATE TABLE t2(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=COMPACT  ENGINE=InnoDB;
INSERT INTO t2 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t2 (SELECT 0, c2, c3, c4, c5 FROM t2);
INSERT INTO t2 (SELECT 0, c2, c3, c4, c5 FROM t2);
INSERT INTO t2 (SELECT 0, c2, c3, c4, c5 FROM t2);
INSERT INTO t2 (SELECT 0, c2, c3, c4, c5 FROM t2);
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
SELECT count(*) FROM t2;
count(*)
16
#
# Create and insert records into a COMPRESSED row formatted table.
#
CREATE TABLE t3(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=COMPRESSED  KEY_BLOCK_SIZE=4  ENGINE=InnoDB;
INSERT INTO t3 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t3 (SELECT 0, c2, c3, c4, c5 FROM t3);
INSERT INTO t3 (SELECT 0, c2, c3, c4, c5 FROM t3);
INSERT INTO t3 (SELECT 0, c2, c3, c4, c5 FROM t3);
INSERT INTO t3 (SELECT 0, c2, c3, c4, c5 FROM t3);
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
SELECT count(*) FROM t3;
count(*)
16
#
# Create and insert records into a DYNAMIC row formatted table.
#
CREATE TABLE t4(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=DYNAMIC  ENGINE=InnoDB;
INSERT INTO t4 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
SELECT count(*) FROM t4;
count(*)
16
#
# Create and insert records into a table that uses a remote DATA DIRECTORY.
#
CREATE TABLE t5(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=DYNAMIC  ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
INSERT INTO t5 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
SHOW CREATE TABLE t5;
Table	Create Table
t5	CREATE TABLE `t5` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
SELECT count(*) FROM t5;
count(*)
16
#
# Create and insert records into a partitioned table that uses
# a remote DATA DIRECTORY for each partition.
#
CREATE TABLE t6(
c1 INT AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=COMPRESSED  ENGINE=InnoDB
PARTITION BY HASH(c1) (
PARTITION p0  DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir',
PARTITION p1  DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir',
PARTITION p2  DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir');
INSERT INTO t6 VALUES (0, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t6 (SELECT 0, c2, c3, c4, c5 FROM t6);
INSERT INTO t6 (SELECT 0, c2, c3, c4, c5 FROM t6);
INSERT INTO t6 (SELECT 0, c2, c3, c4, c5 FROM t6);
INSERT INTO t6 (SELECT 0, c2, c3, c4, c5 FROM t6);
SHOW CREATE TABLE t6;
Table	Create Table
t6	CREATE TABLE `t6` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (c1)
(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB) */
SELECT count(*) FROM t6;
count(*)
16
#
# Create and insert records into a subpartitioned table that uses
# a remote DATA DIRECTORY for each subpartition.
#
CREATE TABLE t7(
c1 INT AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=DYNAMIC  ENGINE=InnoDB
PARTITION BY RANGE(c1) SUBPARTITION BY HASH(c1) (
PARTITION p0 VALUES LESS THAN (10) (
SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir',
SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir'),
PARTITION p1 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir',
SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir'));
INSERT INTO t7 VALUES (0, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t7 (SELECT 0, c2, c3, c4, c5 FROM t7);
INSERT INTO t7 (SELECT 0, c2, c3, c4, c5 FROM t7);
INSERT INTO t7 (SELECT 0, c2, c3, c4, c5 FROM t7);
INSERT INTO t7 (SELECT 0, c2, c3, c4, c5 FROM t7);
SHOW CREATE TABLE t7;
Table	Create Table
t7	CREATE TABLE `t7` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1)
(PARTITION p0 VALUES LESS THAN (10)
 (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
 PARTITION p1 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)) */
SELECT count(*) FROM t7;
count(*)
16
#
# Show these tables in information_schema.
#
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	8	Antelope	Redundant
test/t2	8	Antelope	Compact
test/t3	8	Barracuda	Compressed
test/t4	8	Barracuda	Dynamic
test/t5	8	Barracuda	Dynamic
test/t6#p#p0	8	Barracuda	Compressed
test/t6#p#p1	8	Barracuda	Compressed
test/t6#p#p2	8	Barracuda	Compressed
test/t7#p#p0#sp#s0	8	Barracuda	Dynamic
test/t7#p#p0#sp#s1	8	Barracuda	Dynamic
test/t7#p#p1#sp#s2	8	Barracuda	Dynamic
test/t7#p#p1#sp#s3	8	Barracuda	Dynamic
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
WHERE name LIKE 'test%' ORDER BY name;
name	file_format	row_format
test/t1	Antelope	Compact or Redundant
test/t2	Antelope	Compact or Redundant
test/t3	Barracuda	Compressed
test/t4	Barracuda	Dynamic
test/t5	Barracuda	Dynamic
test/t6#p#p0	Barracuda	Compressed
test/t6#p#p1	Barracuda	Compressed
test/t6#p#p2	Barracuda	Compressed
test/t7#p#p0#sp#s0	Barracuda	Dynamic
test/t7#p#p0#sp#s1	Barracuda	Dynamic
test/t7#p#p1#sp#s2	Barracuda	Dynamic
test/t7#p#p1#sp#s3	Barracuda	Dynamic
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_DATA_DIR/test/t1.ibd
MYSQL_DATA_DIR/test/t2.ibd
MYSQL_DATA_DIR/test/t3.ibd
MYSQL_DATA_DIR/test/t4.ibd
MYSQL_TMP_DIR/alt_dir/test/t5.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p0.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p1.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p2.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p0#sp#s0.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p0#sp#s1.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p1#sp#s2.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p1#sp#s3.ibd
#
# Shutdown the server and list the tablespace OS files
#
---- MYSQL_DATA_DIR/test
t1.frm
t1.ibd
t2.frm
t2.ibd
t3.frm
t3.ibd
t4.frm
t4.ibd
t5.frm
t5.isl
t6#p#p0.isl
t6#p#p1.isl
t6#p#p2.isl
t6.frm
t6.par
t7#p#p0#sp#s0.isl
t7#p#p0#sp#s1.isl
t7#p#p1#sp#s2.isl
t7#p#p1#sp#s3.isl
t7.frm
t7.par
---- MYSQL_TMP_DIR/alt_dir
test
---- MYSQL_TMP_DIR/alt_dir/test
t5.ibd
t6#p#p0.ibd
t6#p#p1.ibd
t6#p#p2.ibd
t7#p#p0#sp#s0.ibd
t7#p#p0#sp#s1.ibd
t7#p#p1#sp#s2.ibd
t7#p#p1#sp#s3.ibd
#
# Start the server and show that tables are still visible and accessible.
#
SHOW VARIABLES LIKE 'innodb_file_per_table';
Variable_name	Value
innodb_file_per_table	ON
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
SHOW CREATE TABLE t5;
Table	Create Table
t5	CREATE TABLE `t5` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
SHOW CREATE TABLE t6;
Table	Create Table
t6	CREATE TABLE `t6` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (c1)
(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB) */
SHOW CREATE TABLE t7;
Table	Create Table
t7	CREATE TABLE `t7` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1)
(PARTITION p0 VALUES LESS THAN (10)
 (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
 PARTITION p1 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)) */
INSERT INTO t1 (SELECT 0, c2, c3, c4, c5 FROM t1);
INSERT INTO t2 (SELECT 0, c2, c3, c4, c5 FROM t2);
INSERT INTO t3 (SELECT 0, c2, c3, c4, c5 FROM t3);
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t6 (SELECT 0, c2, c3, c4, c5 FROM t6);
INSERT INTO t7 (SELECT 0, c2, c3, c4, c5 FROM t7);
SELECT count(*) FROM t1;
count(*)
32
SELECT count(*) FROM t2;
count(*)
32
SELECT count(*) FROM t3;
count(*)
32
SELECT count(*) FROM t4;
count(*)
32
SELECT count(*) FROM t5;
count(*)
32
SELECT count(*) FROM t6;
count(*)
32
SELECT count(*) FROM t7;
count(*)
32
#
# Show these tables in information_schema.
#
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	8	Antelope	Redundant
test/t2	8	Antelope	Compact
test/t3	8	Barracuda	Compressed
test/t4	8	Barracuda	Dynamic
test/t5	8	Barracuda	Dynamic
test/t6#p#p0	8	Barracuda	Compressed
test/t6#p#p1	8	Barracuda	Compressed
test/t6#p#p2	8	Barracuda	Compressed
test/t7#p#p0#sp#s0	8	Barracuda	Dynamic
test/t7#p#p0#sp#s1	8	Barracuda	Dynamic
test/t7#p#p1#sp#s2	8	Barracuda	Dynamic
test/t7#p#p1#sp#s3	8	Barracuda	Dynamic
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
WHERE name LIKE 'test%' ORDER BY name;
name	file_format	row_format
test/t1	Antelope	Compact or Redundant
test/t2	Antelope	Compact or Redundant
test/t3	Barracuda	Compressed
test/t4	Barracuda	Dynamic
test/t5	Barracuda	Dynamic
test/t6#p#p0	Barracuda	Compressed
test/t6#p#p1	Barracuda	Compressed
test/t6#p#p2	Barracuda	Compressed
test/t7#p#p0#sp#s0	Barracuda	Dynamic
test/t7#p#p0#sp#s1	Barracuda	Dynamic
test/t7#p#p1#sp#s2	Barracuda	Dynamic
test/t7#p#p1#sp#s3	Barracuda	Dynamic
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_DATA_DIR/test/t1.ibd
MYSQL_DATA_DIR/test/t2.ibd
MYSQL_DATA_DIR/test/t3.ibd
MYSQL_DATA_DIR/test/t4.ibd
MYSQL_TMP_DIR/alt_dir/test/t5.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p0.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p1.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p2.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p0#sp#s0.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p0#sp#s1.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p1#sp#s2.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p1#sp#s3.ibd
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
#
# Truncate the remote tablespaces.
#
TRUNCATE TABLE t5;
ALTER TABLE t6 TRUNCATE PARTITION p2;
ALTER TABLE t7 TRUNCATE PARTITION p1;
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_DATA_DIR/test/t4.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p0.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p1.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p0#sp#s0.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p0#sp#s1.ibd
MYSQL_TMP_DIR/alt_dir/test/t5.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p2.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p1#sp#s2.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p1#sp#s3.ibd
INSERT INTO t5 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
SELECT count(*) FROM t5;
count(*)
8
SHOW CREATE TABLE t5;
Table	Create Table
t5	CREATE TABLE `t5` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000012 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
SELECT count(*) FROM t6;
count(*)
21
SHOW CREATE TABLE t6;
Table	Create Table
t6	CREATE TABLE `t6` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (c1)
(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB) */
SELECT count(*) FROM t7;
count(*)
9
SHOW CREATE TABLE t7;
Table	Create Table
t7	CREATE TABLE `t7` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1)
(PARTITION p0 VALUES LESS THAN (10)
 (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
 PARTITION p1 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)) */
#
# Shutdown the server and make a backup of a tablespace
#
---- MYSQL_DATA_DIR/test
t4.frm
t4.ibd
t5.frm
t5.frm.bak
t5.isl
t5.isl.bak
t6#p#p0.isl
t6#p#p1.isl
t6#p#p2.isl
t6.frm
t6.par
t7#p#p0#sp#s0.isl
t7#p#p0#sp#s1.isl
t7#p#p1#sp#s2.isl
t7#p#p1#sp#s3.isl
t7.frm
t7.par
---- MYSQL_TMP_DIR/alt_dir/test
t5.ibd
t5.ibd.bak
t6#p#p0.ibd
t6#p#p1.ibd
t6#p#p2.ibd
t7#p#p0#sp#s0.ibd
t7#p#p0#sp#s1.ibd
t7#p#p1#sp#s2.ibd
t7#p#p1#sp#s3.ibd
#
# Start the server and show the tablespaces.
#
SHOW VARIABLES LIKE 'innodb_file_per_table';
Variable_name	Value
innodb_file_per_table	ON
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_DATA_DIR/test/t4.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p0.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p1.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p0#sp#s0.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p0#sp#s1.ibd
MYSQL_TMP_DIR/alt_dir/test/t5.ibd
MYSQL_TMP_DIR/alt_dir/test/t6#p#p2.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p1#sp#s2.ibd
MYSQL_TMP_DIR/alt_dir/test/t7#p#p1#sp#s3.ibd
SELECT count(*) FROM t5;
count(*)
8
SHOW CREATE TABLE t5;
Table	Create Table
t5	CREATE TABLE `t5` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000009 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
SELECT count(*) FROM t6;
count(*)
21
SHOW CREATE TABLE t6;
Table	Create Table
t6	CREATE TABLE `t6` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (c1)
(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB) */
SELECT count(*) FROM t7;
count(*)
9
SHOW CREATE TABLE t7;
Table	Create Table
t7	CREATE TABLE `t7` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1)
(PARTITION p0 VALUES LESS THAN (10)
 (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
 PARTITION p1 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)) */
#
# Try to rename a tablespace to a file that already exists
#
RENAME TABLE t5 TO t55;
ERROR 42S01: Table 't55' already exists
RENAME TABLE t5 TO t55;
ERROR HY000: Error on rename of './test/t5' to './test/t55' (errno: 184 - Tablespace already exists)
---- MYSQL_DATA_DIR/test
t4.frm
t4.ibd
t5.frm
t5.isl
t6#p#p0.isl
t6#p#p1.isl
t6#p#p2.isl
t6.frm
t6.par
t7#p#p0#sp#s0.isl
t7#p#p0#sp#s1.isl
t7#p#p1#sp#s2.isl
t7#p#p1#sp#s3.isl
t7.frm
t7.par
---- MYSQL_TMP_DIR/alt_dir/test
t5.ibd
t6#p#p0.ibd
t6#p#p1.ibd
t6#p#p2.ibd
t7#p#p0#sp#s0.ibd
t7#p#p0#sp#s1.ibd
t7#p#p1#sp#s2.ibd
t7#p#p1#sp#s3.ibd
#
# Rename file table and tablespace
#
RENAME TABLE t5 TO t55;
RENAME TABLE t6 TO t66;
RENAME TABLE t7 TO t77;
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_DATA_DIR/test/t4.ibd
MYSQL_TMP_DIR/alt_dir/test/t66#p#p0.ibd
MYSQL_TMP_DIR/alt_dir/test/t66#p#p1.ibd
MYSQL_TMP_DIR/alt_dir/test/t77#p#p0#sp#s0.ibd
MYSQL_TMP_DIR/alt_dir/test/t77#p#p0#sp#s1.ibd
MYSQL_TMP_DIR/alt_dir/test/t55.ibd
MYSQL_TMP_DIR/alt_dir/test/t66#p#p2.ibd
MYSQL_TMP_DIR/alt_dir/test/t77#p#p1#sp#s2.ibd
MYSQL_TMP_DIR/alt_dir/test/t77#p#p1#sp#s3.ibd
INSERT INTO t55 (SELECT 0, c2, c3, c4, c5 FROM t55);
SELECT count(*) FROM t55;
count(*)
16
SHOW CREATE TABLE t55;
Table	Create Table
t55	CREATE TABLE `t55` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000024 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
INSERT INTO t66 (SELECT 0, c2, c3, c4, c5 FROM t66);
SELECT count(*) FROM t66;
count(*)
42
SHOW CREATE TABLE t66;
Table	Create Table
t66	CREATE TABLE `t66` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (c1)
(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB) */
INSERT INTO t77 (SELECT 0, c2, c3, c4, c5 FROM t77);
SELECT count(*) FROM t77;
count(*)
18
SHOW CREATE TABLE t77;
Table	Create Table
t77	CREATE TABLE `t77` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1)
(PARTITION p0 VALUES LESS THAN (10)
 (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
 PARTITION p1 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)) */
---- MYSQL_DATA_DIR/test
t4.frm
t4.ibd
t55.frm
t55.isl
t66#p#p0.isl
t66#p#p1.isl
t66#p#p2.isl
t66.frm
t66.par
t77#p#p0#sp#s0.isl
t77#p#p0#sp#s1.isl
t77#p#p1#sp#s2.isl
t77#p#p1#sp#s3.isl
t77.frm
t77.par
---- MYSQL_TMP_DIR/alt_dir/test
t55.ibd
t66#p#p0.ibd
t66#p#p1.ibd
t66#p#p2.ibd
t77#p#p0#sp#s0.ibd
t77#p#p0#sp#s1.ibd
t77#p#p1#sp#s2.ibd
t77#p#p1#sp#s3.ibd
#
# Restart the server
#
SHOW VARIABLES LIKE 'innodb_file_per_table';
Variable_name	Value
innodb_file_per_table	ON
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_DATA_DIR/test/t4.ibd
MYSQL_TMP_DIR/alt_dir/test/t66#p#p0.ibd
MYSQL_TMP_DIR/alt_dir/test/t66#p#p1.ibd
MYSQL_TMP_DIR/alt_dir/test/t77#p#p0#sp#s0.ibd
MYSQL_TMP_DIR/alt_dir/test/t77#p#p0#sp#s1.ibd
MYSQL_TMP_DIR/alt_dir/test/t55.ibd
MYSQL_TMP_DIR/alt_dir/test/t66#p#p2.ibd
MYSQL_TMP_DIR/alt_dir/test/t77#p#p1#sp#s2.ibd
MYSQL_TMP_DIR/alt_dir/test/t77#p#p1#sp#s3.ibd
INSERT INTO t55 (SELECT 0, c2, c3, c4, c5 FROM t55);
SELECT count(*) FROM t55;
count(*)
32
SHOW CREATE TABLE t55;
Table	Create Table
t55	CREATE TABLE `t55` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000048 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
INSERT INTO t66 (SELECT 0, c2, c3, c4, c5 FROM t66);
SELECT count(*) FROM t66;
count(*)
84
SHOW CREATE TABLE t66;
Table	Create Table
t66	CREATE TABLE `t66` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (c1)
(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
 PARTITION p2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB) */
INSERT INTO t77 (SELECT 0, c2, c3, c4, c5 FROM t77);
SELECT count(*) FROM t77;
count(*)
36
SHOW CREATE TABLE t77;
Table	Create Table
t77	CREATE TABLE `t77` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1)
(PARTITION p0 VALUES LESS THAN (10)
 (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB),
 PARTITION p1 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB,
  SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir' ENGINE = InnoDB)) */
#
# Shutdown the server
#
#
# Move the remote tablespaces to a new location and change the ISL files
#
---- MYSQL_DATA_DIR/test
t4.frm
t4.ibd
t55.frm
t55.isl
t66#p#p0.isl
t66#p#p1.isl
t66#p#p2.isl
t66.frm
t66.par
t77#p#p0#sp#s0.isl
t77#p#p0#sp#s1.isl
t77#p#p1#sp#s2.isl
t77#p#p1#sp#s3.isl
t77.frm
t77.par
---- MYSQL_TMP_DIR/alt_dir/test
t55.ibd
t66#p#p0.ibd
t66#p#p1.ibd
t66#p#p2.ibd
t77#p#p0#sp#s0.ibd
t77#p#p0#sp#s1.ibd
t77#p#p1#sp#s2.ibd
t77#p#p1#sp#s3.ibd
---- MYSQL_TMP_DIR/new_dir/test
# Moving tablespace 't4' from MYSQL_DATA_DIR to MYSQL_TMP_DIR/new_dir
# Moving tablespace 't55' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
# Moving tablespace 't66' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
# Moving tablespace 't77' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
---- MYSQL_DATA_DIR/test
t4.frm
t4.isl
t55.frm
t55.isl
t66#p#p0.isl
t66#p#p1.isl
t66#p#p2.isl
t66.frm
t66.par
t77#p#p0#sp#s0.isl
t77#p#p0#sp#s1.isl
t77#p#p1#sp#s2.isl
t77#p#p1#sp#s3.isl
t77.frm
t77.par
---- MYSQL_TMP_DIR/alt_dir/test
---- MYSQL_TMP_DIR/new_dir/test
t4.ibd
t55.ibd
t66#p#p0.ibd
t66#p#p1.ibd
t66#p#p2.ibd
t77#p#p0#sp#s0.ibd
t77#p#p0#sp#s1.ibd
t77#p#p1#sp#s2.ibd
t77#p#p1#sp#s3.ibd
#
# Start the server and check tablespaces.
#
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_DATA_DIR/test/t4.ibd
MYSQL_TMP_DIR/new_dir/test/t66#p#p0.ibd
MYSQL_TMP_DIR/new_dir/test/t66#p#p1.ibd
MYSQL_TMP_DIR/new_dir/test/t77#p#p0#sp#s0.ibd
MYSQL_TMP_DIR/new_dir/test/t77#p#p0#sp#s1.ibd
MYSQL_TMP_DIR/new_dir/test/t55.ibd
MYSQL_TMP_DIR/new_dir/test/t66#p#p2.ibd
MYSQL_TMP_DIR/new_dir/test/t77#p#p1#sp#s2.ibd
MYSQL_TMP_DIR/new_dir/test/t77#p#p1#sp#s3.ibd
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
SELECT count(*) FROM t4;
count(*)
64
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000099 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
INSERT INTO t55 (SELECT 0, c2, c3, c4, c5 FROM t55);
SELECT count(*) FROM t55;
count(*)
64
SHOW CREATE TABLE t55;
Table	Create Table
t55	CREATE TABLE `t55` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000096 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/new_dir/'
INSERT INTO t66 (SELECT 0, c2, c3, c4, c5 FROM t66);
SELECT count(*) FROM t66;
count(*)
168
SHOW CREATE TABLE t66;
Table	Create Table
t66	CREATE TABLE `t66` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (c1)
(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB,
 PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB,
 PARTITION p2 DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB) */
INSERT INTO t77 (SELECT 0, c2, c3, c4, c5 FROM t77);
SELECT count(*) FROM t77;
count(*)
72
SHOW CREATE TABLE t77;
Table	Create Table
t77	CREATE TABLE `t77` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1)
(PARTITION p0 VALUES LESS THAN (10)
 (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB,
  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB),
 PARTITION p1 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB,
  SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/new_dir' ENGINE = InnoDB)) */
#
# Shutdown the server
#
#
# Move the remote tablespaces back to the default datadir and delete the ISL file.
#
---- MYSQL_DATA_DIR/test
t4.frm
t4.isl
t55.frm
t55.isl
t66#p#p0.isl
t66#p#p1.isl
t66#p#p2.isl
t66.frm
t66.par
t77#p#p0#sp#s0.isl
t77#p#p0#sp#s1.isl
t77#p#p1#sp#s2.isl
t77#p#p1#sp#s3.isl
t77.frm
t77.par
---- MYSQL_TMP_DIR/new_dir/test
t4.ibd
t55.ibd
t66#p#p0.ibd
t66#p#p1.ibd
t66#p#p2.ibd
t77#p#p0#sp#s0.ibd
t77#p#p0#sp#s1.ibd
t77#p#p1#sp#s2.ibd
t77#p#p1#sp#s3.ibd
# Moving 't4' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
# Moving 't55' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
# Moving 't66' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
# Moving 't77' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
---- MYSQL_DATA_DIR/test
t4.frm
t4.ibd
t55.frm
t55.ibd
t66#p#p0.ibd
t66#p#p1.ibd
t66#p#p2.ibd
t66.frm
t66.par
t77#p#p0#sp#s0.ibd
t77#p#p0#sp#s1.ibd
t77#p#p1#sp#s2.ibd
t77#p#p1#sp#s3.ibd
t77.frm
t77.par
---- MYSQL_TMP_DIR/new_dir/test
#
# Start the server and check tablespaces.
#
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
MYSQL_DATA_DIR/test/t4.ibd
MYSQL_DATA_DIR/test/t66#p#p0.ibd
MYSQL_DATA_DIR/test/t66#p#p1.ibd
MYSQL_DATA_DIR/test/t77#p#p0#sp#s0.ibd
MYSQL_DATA_DIR/test/t77#p#p0#sp#s1.ibd
MYSQL_DATA_DIR/test/t55.ibd
MYSQL_DATA_DIR/test/t66#p#p2.ibd
MYSQL_DATA_DIR/test/t77#p#p1#sp#s2.ibd
MYSQL_DATA_DIR/test/t77#p#p1#sp#s3.ibd
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
SELECT count(*) FROM t4;
count(*)
128
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000195 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
INSERT INTO t55 (SELECT 0, c2, c3, c4, c5 FROM t55);
SELECT count(*) FROM t55;
count(*)
128
SHOW CREATE TABLE t55;
Table	Create Table
t55	CREATE TABLE `t55` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000192 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
INSERT INTO t66 (SELECT 0, c2, c3, c4, c5 FROM t66);
SELECT count(*) FROM t66;
count(*)
336
SHOW CREATE TABLE t66;
Table	Create Table
t66	CREATE TABLE `t66` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (c1)
(PARTITION p0 ENGINE = InnoDB,
 PARTITION p1 ENGINE = InnoDB,
 PARTITION p2 ENGINE = InnoDB) */
INSERT INTO t77 (SELECT 0, c2, c3, c4, c5 FROM t77);
SELECT count(*) FROM t77;
count(*)
144
SHOW CREATE TABLE t77;
Table	Create Table
t77	CREATE TABLE `t77` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1)
(PARTITION p0 VALUES LESS THAN (10)
 (SUBPARTITION s0 ENGINE = InnoDB,
  SUBPARTITION s1 ENGINE = InnoDB),
 PARTITION p1 VALUES LESS THAN MAXVALUE
 (SUBPARTITION s2 ENGINE = InnoDB,
  SUBPARTITION s3 ENGINE = InnoDB)) */
#
# Cleanup
#
DROP TABLE t4;
DROP TABLE t55;
DROP TABLE t66;
DROP TABLE t77;