diff options
Diffstat (limited to 'mysql-test/suite/innodb_zip/r/restart.result')
| -rw-r--r-- | mysql-test/suite/innodb_zip/r/restart.result | 1081 |
1 files changed, 1081 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_zip/r/restart.result b/mysql-test/suite/innodb_zip/r/restart.result new file mode 100644 index 00000000..59d739db --- /dev/null +++ b/mysql-test/suite/innodb_zip/r/restart.result @@ -0,0 +1,1081 @@ +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; +# +# 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 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 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 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 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 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 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 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 +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 DEFAULT MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd +test/t6_restart#p#p2 DEFAULT DEFAULT 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 +# +# 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 VARIABLES LIKE 'innodb_file_per_table'; +Variable_name Value +innodb_file_per_table ON +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 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 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 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 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 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 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 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 +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 DEFAULT MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd +test/t6_restart#p#p2 DEFAULT DEFAULT 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 +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 +test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd +test/t6_restart#p#p0 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 DEFAULT DEFAULT 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 DEFAULT 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 +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 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 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 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 +SHOW VARIABLES LIKE 'innodb_file_per_table'; +Variable_name Value +innodb_file_per_table ON +=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === +Space_Name Page_Size Zip_Size Path +test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd +test/t6_restart#p#p0 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 DEFAULT DEFAULT 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 DEFAULT 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 +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 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 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 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 +test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd +test/t66_restart#p#p0 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 DEFAULT DEFAULT 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 DEFAULT 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 +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 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 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 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 +SHOW VARIABLES LIKE 'innodb_file_per_table'; +Variable_name Value +innodb_file_per_table ON +=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === +Space_Name Page_Size Zip_Size Path +test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd +test/t66_restart#p#p0 DEFAULT DEFAULT MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 DEFAULT DEFAULT 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 DEFAULT 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 +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 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 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 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 +test/t4_restart DEFAULT DEFAULT MYSQL_TMP_DIR/new_dir/test/t4_restart.ibd +test/t66_restart#p#p0 DEFAULT DEFAULT MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 DEFAULT DEFAULT MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p1.ibd +test/t77_restart#p#p0#sp#s0 DEFAULT DEFAULT MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p0#sp#s0.ibd +test/t77_restart#p#p0#sp#s1 DEFAULT DEFAULT MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p0#sp#s1.ibd +test/t55_restart DEFAULT DEFAULT MYSQL_TMP_DIR/new_dir/test/t55_restart.ibd +test/t66_restart#p#p2 DEFAULT DEFAULT MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p2.ibd +test/t77_restart#p#p1#sp#s2 DEFAULT DEFAULT MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p1#sp#s2.ibd +test/t77_restart#p#p1#sp#s3 DEFAULT DEFAULT MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p1#sp#s3.ibd +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 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 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 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 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 +test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd +test/t66_restart#p#p0 DEFAULT DEFAULT MYSQLD_DATADIR/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 DEFAULT DEFAULT MYSQLD_DATADIR/test/t66_restart#p#p1.ibd +test/t77_restart#p#p0#sp#s0 DEFAULT DEFAULT MYSQLD_DATADIR/test/t77_restart#p#p0#sp#s0.ibd +test/t77_restart#p#p0#sp#s1 DEFAULT DEFAULT MYSQLD_DATADIR/test/t77_restart#p#p0#sp#s1.ibd +test/t55_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t55_restart.ibd +test/t66_restart#p#p2 DEFAULT DEFAULT MYSQLD_DATADIR/test/t66_restart#p#p2.ibd +test/t77_restart#p#p1#sp#s2 DEFAULT DEFAULT MYSQLD_DATADIR/test/t77_restart#p#p1#sp#s2.ibd +test/t77_restart#p#p1#sp#s3 DEFAULT DEFAULT MYSQLD_DATADIR/test/t77_restart#p#p1#sp#s3.ibd +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 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 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 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 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; |