ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
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
#
#
# Create and insert records into a REDUNDANT row formatted table.
#
CREATE TABLE t1_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=REDUNDANT  ENGINE=InnoDB;
INSERT INTO t1_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t1_restart (SELECT 0, c2, c3, c4, c5 FROM t1_restart);
INSERT INTO t1_restart (SELECT 0, c2, c3, c4, c5 FROM t1_restart);
INSERT INTO t1_restart (SELECT 0, c2, c3, c4, c5 FROM t1_restart);
INSERT INTO t1_restart (SELECT 0, c2, c3, c4, c5 FROM t1_restart);
SHOW CREATE TABLE t1_restart;
Table	Create Table
t1_restart	CREATE TABLE `t1_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=REDUNDANT
SELECT count(*) FROM t1_restart;
count(*)
16
#
# Create and insert records into a COMPACT row formatted table.
#
CREATE TABLE t2_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=COMPACT  ENGINE=InnoDB;
INSERT INTO t2_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t2_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart);
INSERT INTO t2_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart);
INSERT INTO t2_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart);
INSERT INTO t2_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart);
SHOW CREATE TABLE t2_restart;
Table	Create Table
t2_restart	CREATE TABLE `t2_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPACT
SELECT count(*) FROM t2_restart;
count(*)
16
#
# Create and insert records into a COMPRESSED row formatted table.
#
CREATE TABLE t3_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=COMPRESSED  KEY_BLOCK_SIZE=2  ENGINE=InnoDB;
INSERT INTO t3_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t3_restart (SELECT 0, c2, c3, c4, c5 FROM t3_restart);
INSERT INTO t3_restart (SELECT 0, c2, c3, c4, c5 FROM t3_restart);
INSERT INTO t3_restart (SELECT 0, c2, c3, c4, c5 FROM t3_restart);
INSERT INTO t3_restart (SELECT 0, c2, c3, c4, c5 FROM t3_restart);
SHOW CREATE TABLE t3_restart;
Table	Create Table
t3_restart	CREATE TABLE `t3_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
SELECT count(*) FROM t3_restart;
count(*)
16
#
# Create and insert records into a DYNAMIC row formatted table.
#
CREATE TABLE t4_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=DYNAMIC  ENGINE=InnoDB;
INSERT INTO t4_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
SHOW CREATE TABLE t4_restart;
Table	Create Table
t4_restart	CREATE TABLE `t4_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
SELECT count(*) FROM t4_restart;
count(*)
16
#
# Create and insert records into a table that uses a remote DATA DIRECTORY.
#
CREATE TABLE t5_restart(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_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
SHOW CREATE TABLE t5_restart;
Table	Create Table
t5_restart	CREATE TABLE `t5_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000027 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
SELECT count(*) FROM t5_restart;
count(*)
16
#
# Create and insert records into a partitioned table that uses
# a remote DATA DIRECTORY for each partition.
#
CREATE TABLE t6_restart(
c1 INT AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
ROW_FORMAT=COMPRESSED  KEY_BLOCK_SIZE=2  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_restart VALUES (0, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t6_restart (SELECT 0, c2, c3, c4, c5 FROM t6_restart);
INSERT INTO t6_restart (SELECT 0, c2, c3, c4, c5 FROM t6_restart);
INSERT INTO t6_restart (SELECT 0, c2, c3, c4, c5 FROM t6_restart);
INSERT INTO t6_restart (SELECT 0, c2, c3, c4, c5 FROM t6_restart);
SHOW CREATE TABLE t6_restart;
Table	Create Table
t6_restart	CREATE TABLE `t6_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
 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_restart;
count(*)
16
#
# Create and insert records into a subpartitioned table that uses
# a remote DATA DIRECTORY for each subpartition.
#
CREATE TABLE t7_restart(
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_restart VALUES (0, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t7_restart (SELECT 0, c2, c3, c4, c5 FROM t7_restart);
INSERT INTO t7_restart (SELECT 0, c2, c3, c4, c5 FROM t7_restart);
INSERT INTO t7_restart (SELECT 0, c2, c3, c4, c5 FROM t7_restart);
INSERT INTO t7_restart (SELECT 0, c2, c3, c4, c5 FROM t7_restart);
SHOW CREATE TABLE t7_restart;
Table	Create Table
t7_restart	CREATE TABLE `t7_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
 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_restart;
count(*)
16
#
# Show these tables in information_schema.
#
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size
test/t1_restart	test/t1_restart	0	8	Redundant	0
test/t2_restart	test/t2_restart	1	8	Compact	0
test/t3_restart	test/t3_restart	37	8	Compressed	2048
test/t4_restart	test/t4_restart	33	8	Dynamic	0
test/t5_restart	test/t5_restart	97	8	Dynamic	0
test/t6_restart#p#p0	test/t6_restart#p#p0	101	8	Compressed	2048
test/t6_restart#p#p1	test/t6_restart#p#p1	101	8	Compressed	2048
test/t6_restart#p#p2	test/t6_restart#p#p2	101	8	Compressed	2048
test/t7_restart#p#p0#sp#s0	test/t7_restart#p#p0#sp#s0	97	8	Dynamic	0
test/t7_restart#p#p0#sp#s1	test/t7_restart#p#p0#sp#s1	97	8	Dynamic	0
test/t7_restart#p#p1#sp#s2	test/t7_restart#p#p1#sp#s2	97	8	Dynamic	0
test/t7_restart#p#p1#sp#s3	test/t7_restart#p#p1#sp#s3	97	8	Dynamic	0
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
Space_Name	Page_Size	Zip_Size	Path
innodb_undo001	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo001
innodb_undo002	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo002
innodb_undo003	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo003
test/t1_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t1_restart.ibd
test/t2_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t2_restart.ibd
test/t3_restart	DEFAULT	2048	MYSQLD_DATADIR/test/t3_restart.ibd
test/t4_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t4_restart.ibd
test/t5_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd
test/t6_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
test/t6_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
test/t6_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
test/t7_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd
test/t7_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd
test/t7_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd
test/t7_restart#p#p1#sp#s3	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd
innodb_temporary	DEFAULT	DEFAULT	MYSQLD_DATADIR/ibtmp1
#
# Shutdown the server and list the tablespace OS files
#
---- MYSQL_DATA_DIR/test
db.opt
t1_restart.frm
t1_restart.ibd
t2_restart.frm
t2_restart.ibd
t3_restart.frm
t3_restart.ibd
t4_restart.frm
t4_restart.ibd
t5_restart.frm
t5_restart.isl
t6_restart#p#p0.isl
t6_restart#p#p1.isl
t6_restart#p#p2.isl
t6_restart.frm
t6_restart.par
t7_restart#p#p0#sp#s0.isl
t7_restart#p#p0#sp#s1.isl
t7_restart#p#p1#sp#s2.isl
t7_restart#p#p1#sp#s3.isl
t7_restart.frm
t7_restart.par
---- MYSQL_TMP_DIR/alt_dir
test
---- MYSQL_TMP_DIR/alt_dir/test
t5_restart.ibd
t6_restart#p#p0.ibd
t6_restart#p#p1.ibd
t6_restart#p#p2.ibd
t7_restart#p#p0#sp#s0.ibd
t7_restart#p#p0#sp#s1.ibd
t7_restart#p#p1#sp#s2.ibd
t7_restart#p#p1#sp#s3.ibd
#
# Start the server and show that tables are still visible and accessible.
#
# restart
SHOW CREATE TABLE t1_restart;
Table	Create Table
t1_restart	CREATE TABLE `t1_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=REDUNDANT
SHOW CREATE TABLE t2_restart;
Table	Create Table
t2_restart	CREATE TABLE `t2_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPACT
SHOW CREATE TABLE t3_restart;
Table	Create Table
t3_restart	CREATE TABLE `t3_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
SHOW CREATE TABLE t4_restart;
Table	Create Table
t4_restart	CREATE TABLE `t4_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
SHOW CREATE TABLE t5_restart;
Table	Create Table
t5_restart	CREATE TABLE `t5_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000020 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
SHOW CREATE TABLE t6_restart;
Table	Create Table
t6_restart	CREATE TABLE `t6_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
 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_restart;
Table	Create Table
t7_restart	CREATE TABLE `t7_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
 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_restart (SELECT 0, c2, c3, c4, c5 FROM t1_restart);
INSERT INTO t2_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart);
INSERT INTO t3_restart (SELECT 0, c2, c3, c4, c5 FROM t3_restart);
INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
INSERT INTO t6_restart (SELECT 0, c2, c3, c4, c5 FROM t6_restart);
INSERT INTO t7_restart (SELECT 0, c2, c3, c4, c5 FROM t7_restart);
SELECT count(*) FROM t1_restart;
count(*)
32
SELECT count(*) FROM t2_restart;
count(*)
32
SELECT count(*) FROM t3_restart;
count(*)
32
SELECT count(*) FROM t4_restart;
count(*)
32
SELECT count(*) FROM t5_restart;
count(*)
32
SELECT count(*) FROM t6_restart;
count(*)
32
SELECT count(*) FROM t7_restart;
count(*)
32
#
# Show these tables in information_schema.
#
=== information_schema.innodb_sys_tables  and innodb_sys_tablespaces ===
Table Name	Tablespace	Table Flags	Columns	Row Format	Zip Size
test/t1_restart	test/t1_restart	0	8	Redundant	0
test/t2_restart	test/t2_restart	1	8	Compact	0
test/t3_restart	test/t3_restart	37	8	Compressed	2048
test/t4_restart	test/t4_restart	33	8	Dynamic	0
test/t5_restart	test/t5_restart	97	8	Dynamic	0
test/t6_restart#p#p0	test/t6_restart#p#p0	101	8	Compressed	2048
test/t6_restart#p#p1	test/t6_restart#p#p1	101	8	Compressed	2048
test/t6_restart#p#p2	test/t6_restart#p#p2	101	8	Compressed	2048
test/t7_restart#p#p0#sp#s0	test/t7_restart#p#p0#sp#s0	97	8	Dynamic	0
test/t7_restart#p#p0#sp#s1	test/t7_restart#p#p0#sp#s1	97	8	Dynamic	0
test/t7_restart#p#p1#sp#s2	test/t7_restart#p#p1#sp#s2	97	8	Dynamic	0
test/t7_restart#p#p1#sp#s3	test/t7_restart#p#p1#sp#s3	97	8	Dynamic	0
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
Space_Name	Page_Size	Zip_Size	Path
innodb_undo001	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo001
innodb_undo002	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo002
innodb_undo003	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo003
test/t1_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t1_restart.ibd
test/t2_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t2_restart.ibd
test/t3_restart	DEFAULT	2048	MYSQLD_DATADIR/test/t3_restart.ibd
test/t4_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t4_restart.ibd
test/t5_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd
test/t6_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
test/t6_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
test/t6_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
test/t7_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd
test/t7_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd
test/t7_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd
test/t7_restart#p#p1#sp#s3	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd
innodb_temporary	DEFAULT	DEFAULT	MYSQLD_DATADIR/ibtmp1
DROP TABLE t1_restart;
DROP TABLE t2_restart;
DROP TABLE t3_restart;
#
# Truncate the remote tablespaces.
#
TRUNCATE TABLE t5_restart;
ALTER TABLE t6_restart TRUNCATE PARTITION p2;
ALTER TABLE t7_restart TRUNCATE PARTITION p1;
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
Space_Name	Page_Size	Zip_Size	Path
innodb_undo001	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo001
innodb_undo002	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo002
innodb_undo003	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo003
test/t4_restart	DEFAULT	DEFAULT	MYSQLD_DATADIR/test/t4_restart.ibd
test/t6_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd
test/t6_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd
test/t7_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd
test/t7_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd
test/t5_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd
test/t6_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd
test/t7_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd
test/t7_restart#p#p1#sp#s3	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd
innodb_temporary	DEFAULT	DEFAULT	MYSQLD_DATADIR/ibtmp1
INSERT INTO t5_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
INSERT INTO t5_restart (SELECT 0, c2, c3, c4, c5 FROM t5_restart);
SELECT count(*) FROM t5_restart;
count(*)
8
SHOW CREATE TABLE t5_restart;
Table	Create Table
t5_restart	CREATE TABLE `t5_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000012 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
SELECT count(*) FROM t6_restart;
count(*)
21
SHOW CREATE TABLE t6_restart;
Table	Create Table
t6_restart	CREATE TABLE `t6_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
 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_restart;
count(*)
9
SHOW CREATE TABLE t7_restart;
Table	Create Table
t7_restart	CREATE TABLE `t7_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
 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
db.opt
t4_restart.frm
t4_restart.ibd
t5_restart.frm
t5_restart.frm.bak
t5_restart.isl
t5_restart.isl.bak
t6_restart#p#p0.isl
t6_restart#p#p1.isl
t6_restart#p#p2.isl
t6_restart.frm
t6_restart.par
t7_restart#p#p0#sp#s0.isl
t7_restart#p#p0#sp#s1.isl
t7_restart#p#p1#sp#s2.isl
t7_restart#p#p1#sp#s3.isl
t7_restart.frm
t7_restart.par
---- MYSQL_TMP_DIR/alt_dir/test
t5_restart.ibd
t5_restart.ibd.bak
t6_restart#p#p0.ibd
t6_restart#p#p1.ibd
t6_restart#p#p2.ibd
t7_restart#p#p0#sp#s0.ibd
t7_restart#p#p0#sp#s1.ibd
t7_restart#p#p1#sp#s2.ibd
t7_restart#p#p1#sp#s3.ibd
#
# Start the server and show the tablespaces.
#
# restart
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
Space_Name	Page_Size	Zip_Size	Path
innodb_undo001	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo001
innodb_undo002	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo002
innodb_undo003	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo003
innodb_temporary	DEFAULT	DEFAULT	MYSQLD_DATADIR/ibtmp1
SELECT count(*) FROM t5_restart;
count(*)
8
SHOW CREATE TABLE t5_restart;
Table	Create Table
t5_restart	CREATE TABLE `t5_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000009 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
SELECT count(*) FROM t6_restart;
count(*)
21
SHOW CREATE TABLE t6_restart;
Table	Create Table
t6_restart	CREATE TABLE `t6_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
 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_restart;
count(*)
9
SHOW CREATE TABLE t7_restart;
Table	Create Table
t7_restart	CREATE TABLE `t7_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
 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_restart TO t55_restart;
ERROR 42S01: Table 't55_restart' already exists
RENAME TABLE t5_restart TO t55_restart;
ERROR HY000: Error on rename of './test/t5_restart' to './test/t55_restart' (errno: 184 "Tablespace already exists")
---- MYSQL_DATA_DIR/test
db.opt
t4_restart.frm
t4_restart.ibd
t5_restart.frm
t5_restart.isl
t6_restart#p#p0.isl
t6_restart#p#p1.isl
t6_restart#p#p2.isl
t6_restart.frm
t6_restart.par
t7_restart#p#p0#sp#s0.isl
t7_restart#p#p0#sp#s1.isl
t7_restart#p#p1#sp#s2.isl
t7_restart#p#p1#sp#s3.isl
t7_restart.frm
t7_restart.par
---- MYSQL_TMP_DIR/alt_dir/test
t5_restart.ibd
t6_restart#p#p0.ibd
t6_restart#p#p1.ibd
t6_restart#p#p2.ibd
t7_restart#p#p0#sp#s0.ibd
t7_restart#p#p0#sp#s1.ibd
t7_restart#p#p1#sp#s2.ibd
t7_restart#p#p1#sp#s3.ibd
#
# Rename file table and tablespace
#
RENAME TABLE t5_restart TO t55_restart;
RENAME TABLE t6_restart TO t66_restart;
RENAME TABLE t7_restart TO t77_restart;
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
Space_Name	Page_Size	Zip_Size	Path
innodb_undo001	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo001
innodb_undo002	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo002
innodb_undo003	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo003
test/t66_restart#p#p0	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd
test/t66_restart#p#p1	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd
test/t77_restart#p#p0#sp#s0	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s0.ibd
test/t77_restart#p#p0#sp#s1	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s1.ibd
test/t55_restart	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t55_restart.ibd
test/t66_restart#p#p2	DEFAULT	2048	MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p2.ibd
test/t77_restart#p#p1#sp#s2	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s2.ibd
test/t77_restart#p#p1#sp#s3	DEFAULT	DEFAULT	MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s3.ibd
innodb_temporary	DEFAULT	DEFAULT	MYSQLD_DATADIR/ibtmp1
INSERT INTO t55_restart (SELECT 0, c2, c3, c4, c5 FROM t55_restart);
SELECT count(*) FROM t55_restart;
count(*)
16
SHOW CREATE TABLE t55_restart;
Table	Create Table
t55_restart	CREATE TABLE `t55_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000024 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
INSERT INTO t66_restart (SELECT 0, c2, c3, c4, c5 FROM t66_restart);
SELECT count(*) FROM t66_restart;
count(*)
42
SHOW CREATE TABLE t66_restart;
Table	Create Table
t66_restart	CREATE TABLE `t66_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
 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_restart (SELECT 0, c2, c3, c4, c5 FROM t77_restart);
SELECT count(*) FROM t77_restart;
count(*)
18
SHOW CREATE TABLE t77_restart;
Table	Create Table
t77_restart	CREATE TABLE `t77_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
 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
db.opt
t4_restart.frm
t4_restart.ibd
t55_restart.frm
t55_restart.isl
t66_restart#p#p0.isl
t66_restart#p#p1.isl
t66_restart#p#p2.isl
t66_restart.frm
t66_restart.par
t77_restart#p#p0#sp#s0.isl
t77_restart#p#p0#sp#s1.isl
t77_restart#p#p1#sp#s2.isl
t77_restart#p#p1#sp#s3.isl
t77_restart.frm
t77_restart.par
---- MYSQL_TMP_DIR/alt_dir/test
t55_restart.ibd
t66_restart#p#p0.ibd
t66_restart#p#p1.ibd
t66_restart#p#p2.ibd
t77_restart#p#p0#sp#s0.ibd
t77_restart#p#p0#sp#s1.ibd
t77_restart#p#p1#sp#s2.ibd
t77_restart#p#p1#sp#s3.ibd
#
# Restart the server
#
# restart
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
Space_Name	Page_Size	Zip_Size	Path
innodb_undo001	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo001
innodb_undo002	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo002
innodb_undo003	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo003
innodb_temporary	DEFAULT	DEFAULT	MYSQLD_DATADIR/ibtmp1
INSERT INTO t55_restart (SELECT 0, c2, c3, c4, c5 FROM t55_restart);
SELECT count(*) FROM t55_restart;
count(*)
32
SHOW CREATE TABLE t55_restart;
Table	Create Table
t55_restart	CREATE TABLE `t55_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000048 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
INSERT INTO t66_restart (SELECT 0, c2, c3, c4, c5 FROM t66_restart);
SELECT count(*) FROM t66_restart;
count(*)
84
SHOW CREATE TABLE t66_restart;
Table	Create Table
t66_restart	CREATE TABLE `t66_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
 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_restart (SELECT 0, c2, c3, c4, c5 FROM t77_restart);
SELECT count(*) FROM t77_restart;
count(*)
36
SHOW CREATE TABLE t77_restart;
Table	Create Table
t77_restart	CREATE TABLE `t77_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
 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
db.opt
t4_restart.frm
t4_restart.ibd
t55_restart.frm
t55_restart.isl
t66_restart#p#p0.isl
t66_restart#p#p1.isl
t66_restart#p#p2.isl
t66_restart.frm
t66_restart.par
t77_restart#p#p0#sp#s0.isl
t77_restart#p#p0#sp#s1.isl
t77_restart#p#p1#sp#s2.isl
t77_restart#p#p1#sp#s3.isl
t77_restart.frm
t77_restart.par
---- MYSQL_TMP_DIR/alt_dir/test
t55_restart.ibd
t66_restart#p#p0.ibd
t66_restart#p#p1.ibd
t66_restart#p#p2.ibd
t77_restart#p#p0#sp#s0.ibd
t77_restart#p#p0#sp#s1.ibd
t77_restart#p#p1#sp#s2.ibd
t77_restart#p#p1#sp#s3.ibd
---- MYSQL_TMP_DIR/new_dir/test
# Moving tablespace 't4_restart' from MYSQL_DATA_DIR to MYSQL_TMP_DIR/new_dir
# Moving tablespace 't55_restart' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
# Moving tablespace 't66_restart' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
# Moving tablespace 't77_restart' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
---- MYSQL_DATA_DIR/test
db.opt
t4_restart.frm
t4_restart.isl
t55_restart.frm
t55_restart.isl
t66_restart#p#p0.isl
t66_restart#p#p1.isl
t66_restart#p#p2.isl
t66_restart.frm
t66_restart.par
t77_restart#p#p0#sp#s0.isl
t77_restart#p#p0#sp#s1.isl
t77_restart#p#p1#sp#s2.isl
t77_restart#p#p1#sp#s3.isl
t77_restart.frm
t77_restart.par
---- MYSQL_TMP_DIR/alt_dir/test
---- MYSQL_TMP_DIR/new_dir/test
t4_restart.ibd
t55_restart.ibd
t66_restart#p#p0.ibd
t66_restart#p#p1.ibd
t66_restart#p#p2.ibd
t77_restart#p#p0#sp#s0.ibd
t77_restart#p#p0#sp#s1.ibd
t77_restart#p#p1#sp#s2.ibd
t77_restart#p#p1#sp#s3.ibd
#
# Start the server and check tablespaces.
#
# restart
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
Space_Name	Page_Size	Zip_Size	Path
innodb_undo001	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo001
innodb_undo002	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo002
innodb_undo003	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo003
innodb_temporary	DEFAULT	DEFAULT	MYSQLD_DATADIR/ibtmp1
INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
SELECT count(*) FROM t4_restart;
count(*)
64
SHOW CREATE TABLE t4_restart;
Table	Create Table
t4_restart	CREATE TABLE `t4_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000099 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/new_dir/'
INSERT INTO t55_restart (SELECT 0, c2, c3, c4, c5 FROM t55_restart);
SELECT count(*) FROM t55_restart;
count(*)
64
SHOW CREATE TABLE t55_restart;
Table	Create Table
t55_restart	CREATE TABLE `t55_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000096 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/new_dir/'
INSERT INTO t66_restart (SELECT 0, c2, c3, c4, c5 FROM t66_restart);
SELECT count(*) FROM t66_restart;
count(*)
168
SHOW CREATE TABLE t66_restart;
Table	Create Table
t66_restart	CREATE TABLE `t66_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
 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_restart (SELECT 0, c2, c3, c4, c5 FROM t77_restart);
SELECT count(*) FROM t77_restart;
count(*)
72
SHOW CREATE TABLE t77_restart;
Table	Create Table
t77_restart	CREATE TABLE `t77_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
 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
db.opt
t4_restart.frm
t4_restart.isl
t55_restart.frm
t55_restart.isl
t66_restart#p#p0.isl
t66_restart#p#p1.isl
t66_restart#p#p2.isl
t66_restart.frm
t66_restart.par
t77_restart#p#p0#sp#s0.isl
t77_restart#p#p0#sp#s1.isl
t77_restart#p#p1#sp#s2.isl
t77_restart#p#p1#sp#s3.isl
t77_restart.frm
t77_restart.par
---- MYSQL_TMP_DIR/new_dir/test
t4_restart.ibd
t55_restart.ibd
t66_restart#p#p0.ibd
t66_restart#p#p1.ibd
t66_restart#p#p2.ibd
t77_restart#p#p0#sp#s0.ibd
t77_restart#p#p0#sp#s1.ibd
t77_restart#p#p1#sp#s2.ibd
t77_restart#p#p1#sp#s3.ibd
# Moving 't4_restart' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
# Moving 't55_restart' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
# Moving 't66_restart' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
# Moving 't77_restart' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
---- MYSQL_DATA_DIR/test
db.opt
t4_restart.frm
t4_restart.ibd
t55_restart.frm
t55_restart.ibd
t66_restart#p#p0.ibd
t66_restart#p#p1.ibd
t66_restart#p#p2.ibd
t66_restart.frm
t66_restart.par
t77_restart#p#p0#sp#s0.ibd
t77_restart#p#p0#sp#s1.ibd
t77_restart#p#p1#sp#s2.ibd
t77_restart#p#p1#sp#s3.ibd
t77_restart.frm
t77_restart.par
---- MYSQL_TMP_DIR/new_dir/test
#
# Start the server and check tablespaces.
#
# restart
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
Space_Name	Page_Size	Zip_Size	Path
innodb_undo001	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo001
innodb_undo002	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo002
innodb_undo003	DEFAULT	DEFAULT	MYSQLD_DATADIR//undo003
innodb_temporary	DEFAULT	DEFAULT	MYSQLD_DATADIR/ibtmp1
INSERT INTO t4_restart (SELECT 0, c2, c3, c4, c5 FROM t4_restart);
SELECT count(*) FROM t4_restart;
count(*)
128
SHOW CREATE TABLE t4_restart;
Table	Create Table
t4_restart	CREATE TABLE `t4_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000195 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
INSERT INTO t55_restart (SELECT 0, c2, c3, c4, c5 FROM t55_restart);
SELECT count(*) FROM t55_restart;
count(*)
128
SHOW CREATE TABLE t55_restart;
Table	Create Table
t55_restart	CREATE TABLE `t55_restart` (
  `c1` double NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000192 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
INSERT INTO t66_restart (SELECT 0, c2, c3, c4, c5 FROM t66_restart);
SELECT count(*) FROM t66_restart;
count(*)
336
SHOW CREATE TABLE t66_restart;
Table	Create Table
t66_restart	CREATE TABLE `t66_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
 PARTITION BY HASH (`c1`)
(PARTITION `p0` ENGINE = InnoDB,
 PARTITION `p1` ENGINE = InnoDB,
 PARTITION `p2` ENGINE = InnoDB)
INSERT INTO t77_restart (SELECT 0, c2, c3, c4, c5 FROM t77_restart);
SELECT count(*) FROM t77_restart;
count(*)
144
SHOW CREATE TABLE t77_restart;
Table	Create Table
t77_restart	CREATE TABLE `t77_restart` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(100) DEFAULT NULL,
  `c4` date DEFAULT NULL,
  `c5` text DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
 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_restart;
DROP TABLE t55_restart;
DROP TABLE t66_restart;
DROP TABLE t77_restart;
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;