From 2e814d4702d71a04388386a9f591d14a35980bfe Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Fri, 12 Aug 2016 11:17:45 +0300 Subject: Merge InnoDB 5.7 from mysql-5.7.9. Contains also MDEV-10547: Test multi_update_innodb fails with InnoDB 5.7 The failure happened because 5.7 has changed the signature of the bool handler::primary_key_is_clustered() const virtual function ("const" was added). InnoDB was using the old signature which caused the function not to be used. MDEV-10550: Parallel replication lock waits/deadlock handling does not work with InnoDB 5.7 Fixed mutexing problem on lock_trx_handle_wait. Note that rpl_parallel and rpl_optimistic_parallel tests still fail. MDEV-10156 : Group commit tests fail on 10.2 InnoDB (branch bb-10.2-jan) Reason: incorrect merge MDEV-10550: Parallel replication can't sync with master in InnoDB 5.7 (branch bb-10.2-jan) Reason: incorrect merge --- mysql-test/suite/innodb_zip/r/restart.result | 1236 ++++++++++++++++++++++++++ 1 file changed, 1236 insertions(+) create mode 100644 mysql-test/suite/innodb_zip/r/restart.result (limited to 'mysql-test/suite/innodb_zip/r/restart.result') 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 00000000000..5645b1ee310 --- /dev/null +++ b/mysql-test/suite/innodb_zip/r/restart.result @@ -0,0 +1,1236 @@ +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, + 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, + 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, + 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, + 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, + 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, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 +/*!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_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, + 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_restart; +count(*) +16 +# +# Create and insert records into a table that uses a general tablespace. +# +CREATE TABLESPACE s1_restart ADD DATAFILE 's1_restart.ibd'; +CREATE TABLE t8_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT) +ROW_FORMAT=COMPACT ENGINE=InnoDB TABLESPACE=s1_restart; +INSERT INTO t8_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot'); +INSERT INTO t8_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart); +INSERT INTO t8_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart); +INSERT INTO t8_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart); +INSERT INTO t8_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart); +SHOW CREATE TABLE t8_restart; +Table Create Table +t8_restart CREATE TABLE `t8_restart` ( + `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`) +) /*!50100 TABLESPACE `s1_restart` */ ENGINE=InnoDB AUTO_INCREMENT=1000000125 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT +SELECT count(*) FROM t8_restart; +count(*) +65 +CREATE TABLE t9_restart(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT) +ROW_FORMAT=DYNAMIC ENGINE=InnoDB TABLESPACE=s1_restart; +INSERT INTO t9_restart VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot'); +INSERT INTO t9_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart); +INSERT INTO t9_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart); +INSERT INTO t9_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart); +INSERT INTO t9_restart (SELECT 0, c2, c3, c4, c5 FROM t2_restart); +SHOW CREATE TABLE t9_restart; +Table Create Table +t9_restart CREATE TABLE `t9_restart` ( + `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`) +) /*!50100 TABLESPACE `s1_restart` */ ENGINE=InnoDB AUTO_INCREMENT=1000000125 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +SELECT count(*) FROM t9_restart; +count(*) +65 +# +# 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 Space Type +test/t1_restart test/t1_restart 0 8 Redundant 0 Single +test/t2_restart test/t2_restart 1 8 Compact 0 Single +test/t3_restart test/t3_restart 37 8 Compressed 2048 Single +test/t4_restart test/t4_restart 33 8 Dynamic 0 Single +test/t5_restart test/t5_restart 97 8 Dynamic 0 Single +test/t6_restart#p#p0 test/t6_restart#p#p0 101 8 Compressed 2048 Single +test/t6_restart#p#p1 test/t6_restart#p#p1 101 8 Compressed 2048 Single +test/t6_restart#p#p2 test/t6_restart#p#p2 101 8 Compressed 2048 Single +test/t7_restart#p#p0#sp#s0 test/t7_restart#p#p0#sp#s0 97 8 Dynamic 0 Single +test/t7_restart#p#p0#sp#s1 test/t7_restart#p#p0#sp#s1 97 8 Dynamic 0 Single +test/t7_restart#p#p1#sp#s2 test/t7_restart#p#p1#sp#s2 97 8 Dynamic 0 Single +test/t7_restart#p#p1#sp#s3 test/t7_restart#p#p1#sp#s3 97 8 Dynamic 0 Single +test/t8_restart s1_restart 129 8 Compact 0 General +test/t9_restart s1_restart 161 8 Dynamic 0 General +=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === +Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path +test/t1_restart Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t1_restart.ibd +test/t2_restart Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t2_restart.ibd +test/t3_restart Single DEFAULT 2048 Compressed MYSQLD_DATADIR/test/t3_restart.ibd +test/t4_restart Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t4_restart.ibd +test/t5_restart Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd +test/t6_restart#p#p0 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd +test/t6_restart#p#p2 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd +test/t7_restart#p#p0#sp#s0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd +test/t7_restart#p#p0#sp#s1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd +test/t7_restart#p#p1#sp#s2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd +test/t7_restart#p#p1#sp#s3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd +s1_restart General DEFAULT 0 Any MYSQLD_DATADIR/s1_restart.ibd +=== information_schema.files === +Space_Name File_Type Engine Status Tablespace_Name Path +test/t1_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t1_restart.ibd +test/t2_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t2_restart.ibd +test/t3_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t3_restart.ibd +test/t4_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t4_restart.ibd +test/t5_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd +test/t6_restart#p#p0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd +test/t6_restart#p#p2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd +test/t7_restart#p#p0#sp#s0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd +test/t7_restart#p#p0#sp#s1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd +test/t7_restart#p#p1#sp#s2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd +test/t7_restart#p#p1#sp#s3 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd +s1_restart TABLESPACE InnoDB NORMAL s1_restart MYSQLD_DATADIR/s1_restart.ibd +# +# Shutdown the server and list the tablespace OS files +# +---- MYSQL_DATA_DIR/test +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 +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 +t8_restart.frm +t9_restart.frm +---- 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, + 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, + 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, + 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, + 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, + 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, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 +/*!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_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, + 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)) */ +SHOW CREATE TABLE t8_restart; +Table Create Table +t8_restart CREATE TABLE `t8_restart` ( + `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`) +) /*!50100 TABLESPACE `s1_restart` */ ENGINE=InnoDB AUTO_INCREMENT=1000000110 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT +SHOW CREATE TABLE t9_restart; +Table Create Table +t9_restart CREATE TABLE `t9_restart` ( + `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`) +) /*!50100 TABLESPACE `s1_restart` */ ENGINE=InnoDB AUTO_INCREMENT=1000000110 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +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); +INSERT INTO t8_restart (SELECT 0, c2, c3, c4, c5 FROM t8_restart); +INSERT INTO t9_restart (SELECT 0, c2, c3, c4, c5 FROM t9_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 +SELECT count(*) FROM t8_restart; +count(*) +130 +SELECT count(*) FROM t9_restart; +count(*) +130 +# +# 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 Space Type +test/t1_restart test/t1_restart 0 8 Redundant 0 Single +test/t2_restart test/t2_restart 1 8 Compact 0 Single +test/t3_restart test/t3_restart 37 8 Compressed 2048 Single +test/t4_restart test/t4_restart 33 8 Dynamic 0 Single +test/t5_restart test/t5_restart 97 8 Dynamic 0 Single +test/t6_restart#p#p0 test/t6_restart#p#p0 101 8 Compressed 2048 Single +test/t6_restart#p#p1 test/t6_restart#p#p1 101 8 Compressed 2048 Single +test/t6_restart#p#p2 test/t6_restart#p#p2 101 8 Compressed 2048 Single +test/t7_restart#p#p0#sp#s0 test/t7_restart#p#p0#sp#s0 97 8 Dynamic 0 Single +test/t7_restart#p#p0#sp#s1 test/t7_restart#p#p0#sp#s1 97 8 Dynamic 0 Single +test/t7_restart#p#p1#sp#s2 test/t7_restart#p#p1#sp#s2 97 8 Dynamic 0 Single +test/t7_restart#p#p1#sp#s3 test/t7_restart#p#p1#sp#s3 97 8 Dynamic 0 Single +test/t8_restart s1_restart 129 8 Compact 0 General +test/t9_restart s1_restart 161 8 Dynamic 0 General +=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === +Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path +test/t1_restart Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t1_restart.ibd +test/t2_restart Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t2_restart.ibd +test/t3_restart Single DEFAULT 2048 Compressed MYSQLD_DATADIR/test/t3_restart.ibd +test/t4_restart Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t4_restart.ibd +test/t5_restart Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd +test/t6_restart#p#p0 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd +test/t6_restart#p#p2 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd +test/t7_restart#p#p0#sp#s0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd +test/t7_restart#p#p0#sp#s1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd +test/t7_restart#p#p1#sp#s2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd +test/t7_restart#p#p1#sp#s3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd +s1_restart General DEFAULT 0 Any MYSQLD_DATADIR/s1_restart.ibd +=== information_schema.files === +Space_Name File_Type Engine Status Tablespace_Name Path +test/t1_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t1_restart.ibd +test/t2_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t2_restart.ibd +test/t3_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t3_restart.ibd +test/t4_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t4_restart.ibd +test/t5_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd +test/t6_restart#p#p0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd +test/t6_restart#p#p2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd +test/t7_restart#p#p0#sp#s0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd +test/t7_restart#p#p0#sp#s1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd +test/t7_restart#p#p1#sp#s2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd +test/t7_restart#p#p1#sp#s3 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd +s1_restart TABLESPACE InnoDB NORMAL s1_restart MYSQLD_DATADIR/s1_restart.ibd +DROP TABLE t1_restart; +DROP TABLE t2_restart; +DROP TABLE t3_restart; +DROP TABLE t8_restart; +DROP TABLE t9_restart; +DROP TABLESPACE s1_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 Space_Type Page_Size Zip_Size Formats_Permitted Path +test/t4_restart Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t4_restart.ibd +test/t5_restart Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd +test/t6_restart#p#p0 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd +test/t6_restart#p#p2 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd +test/t7_restart#p#p0#sp#s0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd +test/t7_restart#p#p0#sp#s1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd +test/t7_restart#p#p1#sp#s2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd +test/t7_restart#p#p1#sp#s3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd +=== information_schema.files === +Space_Name File_Type Engine Status Tablespace_Name Path +test/t4_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t4_restart.ibd +test/t5_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd +test/t6_restart#p#p0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd +test/t6_restart#p#p2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd +test/t7_restart#p#p0#sp#s0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd +test/t7_restart#p#p0#sp#s1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd +test/t7_restart#p#p1#sp#s2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd +test/t7_restart#p#p1#sp#s3 TABLESPACE InnoDB NORMAL innodb_file_per_table.## 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, + 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, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 +/*!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_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, + 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_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 +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 +---- 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 Space_Type Page_Size Zip_Size Formats_Permitted Path +test/t4_restart Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t4_restart.ibd +test/t5_restart Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd +test/t6_restart#p#p0 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd +test/t6_restart#p#p2 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd +test/t7_restart#p#p0#sp#s0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd +test/t7_restart#p#p0#sp#s1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd +test/t7_restart#p#p1#sp#s2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd +test/t7_restart#p#p1#sp#s3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s3.ibd +=== information_schema.files === +Space_Name File_Type Engine Status Tablespace_Name Path +test/t4_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t4_restart.ibd +test/t5_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t5_restart.ibd +test/t6_restart#p#p0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p0.ibd +test/t6_restart#p#p1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p1.ibd +test/t6_restart#p#p2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t6_restart#p#p2.ibd +test/t7_restart#p#p0#sp#s0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s0.ibd +test/t7_restart#p#p0#sp#s1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p0#sp#s1.ibd +test/t7_restart#p#p1#sp#s2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t7_restart#p#p1#sp#s2.ibd +test/t7_restart#p#p1#sp#s3 TABLESPACE InnoDB NORMAL innodb_file_per_table.## 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, + 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, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 +/*!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_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, + 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_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 +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 +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 +---- 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 Space_Type Page_Size Zip_Size Formats_Permitted Path +test/t4_restart Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t4_restart.ibd +test/t55_restart Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t55_restart.ibd +test/t66_restart#p#p0 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd +test/t66_restart#p#p2 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p2.ibd +test/t77_restart#p#p0#sp#s0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s0.ibd +test/t77_restart#p#p0#sp#s1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s1.ibd +test/t77_restart#p#p1#sp#s2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s2.ibd +test/t77_restart#p#p1#sp#s3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s3.ibd +=== information_schema.files === +Space_Name File_Type Engine Status Tablespace_Name Path +test/t4_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t4_restart.ibd +test/t55_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t55_restart.ibd +test/t66_restart#p#p0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd +test/t66_restart#p#p2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p2.ibd +test/t77_restart#p#p0#sp#s0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s0.ibd +test/t77_restart#p#p0#sp#s1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s1.ibd +test/t77_restart#p#p1#sp#s2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s2.ibd +test/t77_restart#p#p1#sp#s3 TABLESPACE InnoDB NORMAL innodb_file_per_table.## 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, + 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, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 +/*!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_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, + 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_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 +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 +---- 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 Space_Type Page_Size Zip_Size Formats_Permitted Path +test/t4_restart Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t4_restart.ibd +test/t55_restart Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t55_restart.ibd +test/t66_restart#p#p0 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd +test/t66_restart#p#p2 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p2.ibd +test/t77_restart#p#p0#sp#s0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s0.ibd +test/t77_restart#p#p0#sp#s1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s1.ibd +test/t77_restart#p#p1#sp#s2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s2.ibd +test/t77_restart#p#p1#sp#s3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s3.ibd +=== information_schema.files === +Space_Name File_Type Engine Status Tablespace_Name Path +test/t4_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t4_restart.ibd +test/t55_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t55_restart.ibd +test/t66_restart#p#p0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p1.ibd +test/t66_restart#p#p2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t66_restart#p#p2.ibd +test/t77_restart#p#p0#sp#s0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s0.ibd +test/t77_restart#p#p0#sp#s1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p0#sp#s1.ibd +test/t77_restart#p#p1#sp#s2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t77_restart#p#p1#sp#s2.ibd +test/t77_restart#p#p1#sp#s3 TABLESPACE InnoDB NORMAL innodb_file_per_table.## 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, + 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, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 +/*!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_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, + 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_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 +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 +---- 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 +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 +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 +---- 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 Space_Type Page_Size Zip_Size Formats_Permitted Path +test/t4_restart Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/new_dir/test/t4_restart.ibd +test/t55_restart Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/new_dir/test/t55_restart.ibd +test/t66_restart#p#p0 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p1.ibd +test/t66_restart#p#p2 Single DEFAULT 2048 Compressed MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p2.ibd +test/t77_restart#p#p0#sp#s0 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p0#sp#s0.ibd +test/t77_restart#p#p0#sp#s1 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p0#sp#s1.ibd +test/t77_restart#p#p1#sp#s2 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p1#sp#s2.ibd +test/t77_restart#p#p1#sp#s3 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p1#sp#s3.ibd +=== information_schema.files === +Space_Name File_Type Engine Status Tablespace_Name Path +test/t4_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/new_dir/test/t4_restart.ibd +test/t55_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/new_dir/test/t55_restart.ibd +test/t66_restart#p#p0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p1.ibd +test/t66_restart#p#p2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/new_dir/test/t66_restart#p#p2.ibd +test/t77_restart#p#p0#sp#s0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p0#sp#s0.ibd +test/t77_restart#p#p0#sp#s1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p0#sp#s1.ibd +test/t77_restart#p#p1#sp#s2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/new_dir/test/t77_restart#p#p1#sp#s2.ibd +test/t77_restart#p#p1#sp#s3 TABLESPACE InnoDB NORMAL innodb_file_per_table.## 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, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=1000000099 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(*) +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, + 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, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 +/*!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_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, + 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_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 +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 +---- 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 +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 +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 +---- MYSQL_TMP_DIR/new_dir/test +# +# Start the server and check tablespaces. +# +# restart +=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === +Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path +test/t4_restart Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t4_restart.ibd +test/t55_restart Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t55_restart.ibd +test/t66_restart#p#p0 Single DEFAULT 2048 Compressed MYSQLD_DATADIR/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 Single DEFAULT 2048 Compressed MYSQLD_DATADIR/test/t66_restart#p#p1.ibd +test/t66_restart#p#p2 Single DEFAULT 2048 Compressed MYSQLD_DATADIR/test/t66_restart#p#p2.ibd +test/t77_restart#p#p0#sp#s0 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t77_restart#p#p0#sp#s0.ibd +test/t77_restart#p#p0#sp#s1 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t77_restart#p#p0#sp#s1.ibd +test/t77_restart#p#p1#sp#s2 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t77_restart#p#p1#sp#s2.ibd +test/t77_restart#p#p1#sp#s3 Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t77_restart#p#p1#sp#s3.ibd +=== information_schema.files === +Space_Name File_Type Engine Status Tablespace_Name Path +test/t4_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t4_restart.ibd +test/t55_restart TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t55_restart.ibd +test/t66_restart#p#p0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t66_restart#p#p0.ibd +test/t66_restart#p#p1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t66_restart#p#p1.ibd +test/t66_restart#p#p2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t66_restart#p#p2.ibd +test/t77_restart#p#p0#sp#s0 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t77_restart#p#p0#sp#s0.ibd +test/t77_restart#p#p0#sp#s1 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t77_restart#p#p0#sp#s1.ibd +test/t77_restart#p#p1#sp#s2 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t77_restart#p#p1#sp#s2.ibd +test/t77_restart#p#p1#sp#s3 TABLESPACE InnoDB NORMAL innodb_file_per_table.## 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, + 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, + 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, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 +/*!50100 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, + 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_restart; +DROP TABLE t55_restart; +DROP TABLE t66_restart; +DROP TABLE t77_restart; -- cgit v1.2.1