^^&&^^

Edit File: innodb-restart.test

#
# These test make sure that tables are visible after rebooting
#

--source include/have_innodb.inc
--source include/have_partition.inc
--source include/not_embedded.inc
SET default_storage_engine=InnoDB;

--disable_query_log
# This error is expected in the error log for this test.
call mtr.add_suppression("InnoDB: Error number 17 means 'File exists'");
--enable_query_log

--echo #
--echo # A series of tests to make sure tables are opened after restart.
--echo # Bug#13357607 Compressed file-per-table tablespaces fail to open
--echo #
# This bug was introduced without a regression test failing since
# there were no tests showing that tablespaces could e created and
# then read after reboot.
#

--disable_query_log
let $MYSQL_DATA_DIR= `select @@datadir`;
let $data_directory = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';

let $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
let $innodb_file_format_orig=`select @@innodb_file_format`;
--enable_query_log

set global innodb_file_per_table=on;
set global innodb_file_format='Barracuda';

--echo #
--echo # Create and insert records into a DYNAMIC row formatted table.
--echo #
CREATE TABLE t1(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
	ROW_FORMAT=REDUNDANT  ENGINE=InnoDB;
INSERT INTO t1 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t1 (SELECT 0, c2, c3, c4, c5 FROM t1);
INSERT INTO t1 (SELECT 0, c2, c3, c4, c5 FROM t1);
INSERT INTO t1 (SELECT 0, c2, c3, c4, c5 FROM t1);
INSERT INTO t1 (SELECT 0, c2, c3, c4, c5 FROM t1);
SHOW CREATE TABLE t1;
SELECT count(*) FROM t1;

--echo #
--echo # Create and insert records into a COMPACT row formatted table.
--echo #
CREATE TABLE t2(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
	ROW_FORMAT=COMPACT  ENGINE=InnoDB;
INSERT INTO t2 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t2 (SELECT 0, c2, c3, c4, c5 FROM t2);
INSERT INTO t2 (SELECT 0, c2, c3, c4, c5 FROM t2);
INSERT INTO t2 (SELECT 0, c2, c3, c4, c5 FROM t2);
INSERT INTO t2 (SELECT 0, c2, c3, c4, c5 FROM t2);
SHOW CREATE TABLE t2;
SELECT count(*) FROM t2;

--echo #
--echo # Create and insert records into a COMPRESSED row formatted table.
--echo #
CREATE TABLE t3(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
	ROW_FORMAT=COMPRESSED  KEY_BLOCK_SIZE=4  ENGINE=InnoDB;
INSERT INTO t3 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t3 (SELECT 0, c2, c3, c4, c5 FROM t3);
INSERT INTO t3 (SELECT 0, c2, c3, c4, c5 FROM t3);
INSERT INTO t3 (SELECT 0, c2, c3, c4, c5 FROM t3);
INSERT INTO t3 (SELECT 0, c2, c3, c4, c5 FROM t3);
SHOW CREATE TABLE t3;
SELECT count(*) FROM t3;

--echo #
--echo # Create and insert records into a DYNAMIC row formatted table.
--echo #
CREATE TABLE t4(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
	ROW_FORMAT=DYNAMIC  ENGINE=InnoDB;
INSERT INTO t4 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
SHOW CREATE TABLE t4;
SELECT count(*) FROM t4;

--echo #
--echo # Create and insert records into a table that uses a remote DATA DIRECTORY.
--echo #
--mkdir $MYSQL_TMP_DIR/alt_dir
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5(c1 DOUBLE AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
	ROW_FORMAT=DYNAMIC  ENGINE=InnoDB  $data_directory;
INSERT INTO t5 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5;
SELECT count(*) FROM t5;

--echo #
--echo # Create and insert records into a partitioned table that uses
--echo # a remote DATA DIRECTORY for each partition.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t6(
  c1 INT AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
  ROW_FORMAT=COMPRESSED  ENGINE=InnoDB
  PARTITION BY HASH(c1) (
    PARTITION p0  DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir',
    PARTITION p1  DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir',
    PARTITION p2  DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir');
INSERT INTO t6 VALUES (0, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t6 (SELECT 0, c2, c3, c4, c5 FROM t6);
INSERT INTO t6 (SELECT 0, c2, c3, c4, c5 FROM t6);
INSERT INTO t6 (SELECT 0, c2, c3, c4, c5 FROM t6);
INSERT INTO t6 (SELECT 0, c2, c3, c4, c5 FROM t6);
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t6;
SELECT count(*) FROM t6;

--echo #
--echo # Create and insert records into a subpartitioned table that uses
--echo # a remote DATA DIRECTORY for each subpartition.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t7(
	c1 INT AUTO_INCREMENT KEY, c2 CHAR(10), c3 VARCHAR(100), c4 DATE, c5 TEXT)
	ROW_FORMAT=DYNAMIC  ENGINE=InnoDB
        PARTITION BY RANGE(c1) SUBPARTITION BY HASH(c1) (
          PARTITION p0 VALUES LESS THAN (10) (
	    SUBPARTITION s0 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir',
	    SUBPARTITION s1 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir'),
	  PARTITION p1 VALUES LESS THAN MAXVALUE (
	    SUBPARTITION s2 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir',
	    SUBPARTITION s3 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir'));
INSERT INTO t7 VALUES (0, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t7 (SELECT 0, c2, c3, c4, c5 FROM t7);
INSERT INTO t7 (SELECT 0, c2, c3, c4, c5 FROM t7);
INSERT INTO t7 (SELECT 0, c2, c3, c4, c5 FROM t7);
INSERT INTO t7 (SELECT 0, c2, c3, c4, c5 FROM t7);
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t7;
SELECT count(*) FROM t7;

--echo #
--echo # Show these tables in information_schema.
--echo #
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
SELECT name,n_cols,file_format,row_format
       FROM information_schema.innodb_sys_tables
       WHERE name like 'test%' ORDER BY name;
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY name;
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo #
--echo # Shutdown the server and list the tablespace OS files
--echo #
--source include/shutdown_mysqld.inc

--echo ---- MYSQL_DATA_DIR/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_DATA_DIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir
--list_files $MYSQL_TMP_DIR/alt_dir
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Start the server and show that tables are still visible and accessible.
--echo #
--source include/start_mysqld.inc

SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
SHOW CREATE TABLE t3;
SHOW CREATE TABLE t4;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t6;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t7;

INSERT INTO t1 (SELECT 0, c2, c3, c4, c5 FROM t1);
INSERT INTO t2 (SELECT 0, c2, c3, c4, c5 FROM t2);
INSERT INTO t3 (SELECT 0, c2, c3, c4, c5 FROM t3);
INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t6 (SELECT 0, c2, c3, c4, c5 FROM t6);
INSERT INTO t7 (SELECT 0, c2, c3, c4, c5 FROM t7);

SELECT count(*) FROM t1;
SELECT count(*) FROM t2;
SELECT count(*) FROM t3;
SELECT count(*) FROM t4;
SELECT count(*) FROM t5;
SELECT count(*) FROM t6;
SELECT count(*) FROM t7;

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
# Tablespace for t4 will be moved later from default directory to a new directory
# and a isl file will be created not using InnoDB.

--echo #
--echo # Truncate the remote tablespaces.
--echo #
TRUNCATE TABLE t5;
ALTER TABLE t6 TRUNCATE PARTITION p2;
ALTER TABLE t7 TRUNCATE PARTITION p1;

--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR  $MYSQL_DATA_DIR MYSQL_DATA_DIR  ./ MYSQL_DATA_DIR/
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

INSERT INTO t5 VALUES (1000000000, 'MySQL', 'InnoDB', '2011-11-11', 'Read this after reboot');
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);
INSERT INTO t5 (SELECT 0, c2, c3, c4, c5 FROM t5);

SELECT count(*) FROM t5;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5;

SELECT count(*) FROM t6;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t6;

SELECT count(*) FROM t7;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t7;

--echo #
--echo # Shutdown the server and make a backup of a tablespace
--echo #
--source include/shutdown_mysqld.inc

--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5.ibd $MYSQL_TMP_DIR/alt_dir/test/t5.ibd.bak
--copy_file $MYSQL_DATA_DIR/test/t5.isl $MYSQL_DATA_DIR/test/t5.isl.bak
--copy_file $MYSQL_DATA_DIR/test/t5.frm $MYSQL_DATA_DIR/test/t5.frm.bak

--echo ---- MYSQL_DATA_DIR/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/  /#SP#/#sp#/
--list_files $MYSQL_DATA_DIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Start the server and show the tablespaces.
--echo #
--source include/start_mysqld.inc

SHOW VARIABLES LIKE 'innodb_file_per_table';

SELECT count(*) FROM t5;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5;

SELECT count(*) FROM t6;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t6;

SELECT count(*) FROM t7;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t7;

--echo #
--echo # Try to rename a tablespace to a file that already exists
--echo #

--copy_file $MYSQL_DATA_DIR/test/t5.frm.bak $MYSQL_DATA_DIR/test/t55.frm
--error ER_TABLE_EXISTS_ERROR
RENAME TABLE t5 TO t55;
--remove_file $MYSQL_DATA_DIR/test/t55.frm
--remove_file $MYSQL_DATA_DIR/test/t5.frm.bak

--copy_file $MYSQL_DATA_DIR/test/t5.isl.bak $MYSQL_DATA_DIR/test/t55.isl
--error ER_ERROR_ON_RENAME
RENAME TABLE t5 TO t55;
--remove_file $MYSQL_DATA_DIR/test/t55.isl
--remove_file $MYSQL_DATA_DIR/test/t5.isl.bak

#--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5.ibd.bak $MYSQL_TMP_DIR/alt_dir/test/t55.ibd
# This RENAME TABLE works of Linux but gets ER_ERROR_ON_RENAME on Windows
#--error ER_ERROR_ON_RENAME
#RENAME TABLE t5 TO t55;
#--remove_file $MYSQL_TMP_DIR/alt_dir/test/t55.ibd
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5.ibd.bak

--echo #
--echo # Rename file table and tablespace
--echo #

RENAME TABLE t5 TO t55;
RENAME TABLE t6 TO t66;
RENAME TABLE t7 TO t77;

INSERT INTO t55 (SELECT 0, c2, c3, c4, c5 FROM t55);
SELECT count(*) FROM t55;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t55;

INSERT INTO t66 (SELECT 0, c2, c3, c4, c5 FROM t66);
SELECT count(*) FROM t66;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t66;

INSERT INTO t77 (SELECT 0, c2, c3, c4, c5 FROM t77);
SELECT count(*) FROM t77;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t77;

--echo #
--echo # Restart the server
--echo #
--source include/restart_mysqld.inc
SHOW VARIABLES LIKE 'innodb_file_per_table';

INSERT INTO t55 (SELECT 0, c2, c3, c4, c5 FROM t55);
SELECT count(*) FROM t55;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t55;

INSERT INTO t66 (SELECT 0, c2, c3, c4, c5 FROM t66);
SELECT count(*) FROM t66;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t66;

INSERT INTO t77 (SELECT 0, c2, c3, c4, c5 FROM t77);
SELECT count(*) FROM t77;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t77;

--echo #
--echo # Shutdown the server
--echo #
--source include/shutdown_mysqld.inc

--echo #
--echo # Move the remote tablespaces to a new location and change the ISL files
--echo #
--mkdir $MYSQL_TMP_DIR/new_dir
--mkdir $MYSQL_TMP_DIR/new_dir/test
--echo ---- MYSQL_DATA_DIR/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_DATA_DIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_TMP_DIR/alt_dir/test
--echo ---- MYSQL_TMP_DIR/new_dir/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_TMP_DIR/new_dir/test

--echo # Moving tablespace 't4' from MYSQL_DATA_DIR to MYSQL_TMP_DIR/new_dir
--copy_file $MYSQL_DATA_DIR/test/t4.ibd $MYSQL_TMP_DIR/new_dir/test/t4.ibd
--remove_file $MYSQL_DATA_DIR/test/t4.ibd
--exec echo $MYSQL_TMP_DIR/new_dir/test/t4.ibd > $MYSQL_DATA_DIR/test/t4.isl

--echo # Moving tablespace 't55' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t55.ibd $MYSQL_TMP_DIR/new_dir/test/t55.ibd
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t55.ibd
--remove_file $MYSQL_DATA_DIR/test/t55.isl
--exec echo $MYSQL_TMP_DIR/new_dir/test/t55.ibd > $MYSQL_DATA_DIR/test/t55.isl

--echo # Moving tablespace 't66' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t66#P#p0.ibd $MYSQL_TMP_DIR/new_dir/test/t66#P#p0.ibd
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t66#P#p1.ibd $MYSQL_TMP_DIR/new_dir/test/t66#P#p1.ibd
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t66#P#p2.ibd $MYSQL_TMP_DIR/new_dir/test/t66#P#p2.ibd
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t66#P#p0.ibd
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t66#P#p1.ibd
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t66#P#p2.ibd
--remove_file $MYSQL_DATA_DIR/test/t66#P#p0.isl
--remove_file $MYSQL_DATA_DIR/test/t66#P#p1.isl
--remove_file $MYSQL_DATA_DIR/test/t66#P#p2.isl
--exec echo $MYSQL_TMP_DIR/new_dir/test/t66#P#p0.ibd > $MYSQL_DATA_DIR/test/t66#P#p0.isl
--exec echo $MYSQL_TMP_DIR/new_dir/test/t66#P#p1.ibd > $MYSQL_DATA_DIR/test/t66#P#p1.isl
--exec echo $MYSQL_TMP_DIR/new_dir/test/t66#P#p2.ibd > $MYSQL_DATA_DIR/test/t66#P#p2.isl

--echo # Moving tablespace 't77' from MYSQL_TMP_DIR/alt_dir to MYSQL_TMP_DIR/new_dir
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t77#P#p0#SP#s0.ibd $MYSQL_TMP_DIR/new_dir/test/t77#P#p0#SP#s0.ibd
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t77#P#p0#SP#s1.ibd $MYSQL_TMP_DIR/new_dir/test/t77#P#p0#SP#s1.ibd
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t77#P#p1#SP#s2.ibd $MYSQL_TMP_DIR/new_dir/test/t77#P#p1#SP#s2.ibd
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t77#P#p1#SP#s3.ibd $MYSQL_TMP_DIR/new_dir/test/t77#P#p1#SP#s3.ibd
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t77#P#p0#SP#s0.ibd
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t77#P#p0#SP#s1.ibd
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t77#P#p1#SP#s2.ibd
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t77#P#p1#SP#s3.ibd
--remove_file $MYSQL_DATA_DIR/test/t77#P#p0#SP#s0.isl
--remove_file $MYSQL_DATA_DIR/test/t77#P#p0#SP#s1.isl
--remove_file $MYSQL_DATA_DIR/test/t77#P#p1#SP#s2.isl
--remove_file $MYSQL_DATA_DIR/test/t77#P#p1#SP#s3.isl
--exec echo $MYSQL_TMP_DIR/new_dir/test/t77#P#p0#SP#s0.ibd > $MYSQL_DATA_DIR/test/t77#P#p0#SP#s0.isl
--exec echo $MYSQL_TMP_DIR/new_dir/test/t77#P#p0#SP#s1.ibd > $MYSQL_DATA_DIR/test/t77#P#p0#SP#s1.isl
--exec echo $MYSQL_TMP_DIR/new_dir/test/t77#P#p1#SP#s2.ibd > $MYSQL_DATA_DIR/test/t77#P#p1#SP#s2.isl
--exec echo $MYSQL_TMP_DIR/new_dir/test/t77#P#p1#SP#s3.ibd > $MYSQL_DATA_DIR/test/t77#P#p1#SP#s3.isl

--echo ---- MYSQL_DATA_DIR/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_DATA_DIR/test
--echo ---- MYSQL_TMP_DIR/alt_dir/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_TMP_DIR/alt_dir/test
--echo ---- MYSQL_TMP_DIR/new_dir/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_TMP_DIR/new_dir/test

--echo #
--echo # Start the server and check tablespaces.
--echo #
--source include/start_mysqld.inc

INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
SELECT count(*) FROM t4;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t4;

INSERT INTO t55 (SELECT 0, c2, c3, c4, c5 FROM t55);
SELECT count(*) FROM t55;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t55;

INSERT INTO t66 (SELECT 0, c2, c3, c4, c5 FROM t66);
SELECT count(*) FROM t66;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t66;

INSERT INTO t77 (SELECT 0, c2, c3, c4, c5 FROM t77);
SELECT count(*) FROM t77;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t77;

--echo #
--echo # Shutdown the server
--echo #
--source include/shutdown_mysqld.inc

--echo #
--echo # Move the remote tablespaces back to the default datadir and delete the ISL file.
--echo #

--echo ---- MYSQL_DATA_DIR/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_DATA_DIR/test
--echo ---- MYSQL_TMP_DIR/new_dir/test
--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--list_files $MYSQL_TMP_DIR/new_dir/test

--echo # Moving 't4' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
--copy_file $MYSQL_TMP_DIR/new_dir/test/t4.ibd $MYSQL_DATA_DIR/test/t4.ibd
--remove_file $MYSQL_TMP_DIR/new_dir/test/t4.ibd
--remove_file $MYSQL_DATA_DIR/test/t4.isl

--echo # Moving 't55' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
--copy_file $MYSQL_TMP_DIR/new_dir/test/t55.ibd $MYSQL_DATA_DIR/test/t55.ibd
--remove_file $MYSQL_TMP_DIR/new_dir/test/t55.ibd
--remove_file $MYSQL_DATA_DIR/test/t55.isl

--echo # Moving 't66' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
--copy_file $MYSQL_TMP_DIR/new_dir/test/t66#P#p0.ibd $MYSQL_DATA_DIR/test/t66#P#p0.ibd
--copy_file $MYSQL_TMP_DIR/new_dir/test/t66#P#p1.ibd $MYSQL_DATA_DIR/test/t66#P#p1.ibd
--copy_file $MYSQL_TMP_DIR/new_dir/test/t66#P#p2.ibd $MYSQL_DATA_DIR/test/t66#P#p2.ibd
--remove_file $MYSQL_TMP_DIR/new_dir/test/t66#P#p0.ibd
--remove_file $MYSQL_TMP_DIR/new_dir/test/t66#P#p1.ibd
--remove_file $MYSQL_TMP_DIR/new_dir/test/t66#P#p2.ibd
--remove_file $MYSQL_DATA_DIR/test/t66#P#p0.isl
--remove_file $MYSQL_DATA_DIR/test/t66#P#p1.isl
--remove_file $MYSQL_DATA_DIR/test/t66#P#p2.isl

--echo # Moving 't77' from MYSQL_TMP_DIR/new_dir to MYSQL_DATA_DIR
--copy_file $MYSQL_TMP_DIR/new_dir/test/t77#P#p0#SP#s0.ibd $MYSQL_DATA_DIR/test/t77#P#p0#SP#s0.ibd
--copy_file $MYSQL_TMP_DIR/new_dir/test/t77#P#p0#SP#s1.ibd $MYSQL_DATA_DIR/test/t77#P#p0#SP#s1.ibd
--copy_file $MYSQL_TMP_DIR/new_dir/test/t77#P#p1#SP#s2.ibd $MYSQL_DATA_DIR/test/t77#P#p1#SP#s2.ibd
--copy_file $MYSQL_TMP_DIR/new_dir/test/t77#P#p1#SP#s3.ibd $MYSQL_DATA_DIR/test/t77#P#p1#SP#s3.ibd
--remove_file $MYSQL_TMP_DIR/new_dir/test/t77#P#p0#SP#s0.ibd
--remove_file $MYSQL_TMP_DIR/new_dir/test/t77#P#p0#SP#s1.ibd
--remove_file $MYSQL_TMP_DIR/new_dir/test/t77#P#p1#SP#s2.ibd
--remove_file $MYSQL_TMP_DIR/new_dir/test/t77#P#p1#SP#s3.ibd
--remove_file $MYSQL_DATA_DIR/test/t77#P#p0#SP#s0.isl
--remove_file $MYSQL_DATA_DIR/test/t77#P#p0#SP#s1.isl
--remove_file $MYSQL_DATA_DIR/test/t77#P#p1#SP#s2.isl
--remove_file $MYSQL_DATA_DIR/test/t77#P#p1#SP#s3.isl

--echo #
--echo # Start the server and check tablespaces.
--echo #
-- source include/start_mysqld.inc

--replace_regex  /#P#/#p#/  /#SP#/#sp#/
--replace_result $MYSQL_DATA_DIR MYSQL_DATA_DIR ./ MYSQL_DATA_DIR/
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

INSERT INTO t4 (SELECT 0, c2, c3, c4, c5 FROM t4);
SELECT count(*) FROM t4;
--replace_result $MYSQL_DATA_DIR MYSQL_DATA_DIR ./ MYSQL_DATA_DIR/
SHOW CREATE TABLE t4;

INSERT INTO t55 (SELECT 0, c2, c3, c4, c5 FROM t55);
SELECT count(*) FROM t55;
--replace_result $MYSQL_DATA_DIR MYSQL_DATA_DIR ./ MYSQL_DATA_DIR/
SHOW CREATE TABLE t55;

INSERT INTO t66 (SELECT 0, c2, c3, c4, c5 FROM t66);
SELECT count(*) FROM t66;
--replace_result $MYSQL_DATA_DIR MYSQL_DATA_DIR ./ MYSQL_DATA_DIR/
SHOW CREATE TABLE t66;

INSERT INTO t77 (SELECT 0, c2, c3, c4, c5 FROM t77);
SELECT count(*) FROM t77;
--replace_result $MYSQL_DATA_DIR MYSQL_DATA_DIR ./ MYSQL_DATA_DIR/
SHOW CREATE TABLE t77;

--echo #
--echo # Cleanup
--echo #

DROP TABLE t4;
DROP TABLE t55;
DROP TABLE t66;
DROP TABLE t77;

--rmdir $MYSQL_TMP_DIR/alt_dir/test
--rmdir $MYSQL_TMP_DIR/alt_dir
--rmdir $MYSQL_TMP_DIR/new_dir/test
--rmdir $MYSQL_TMP_DIR/new_dir

-- disable_query_log
eval set global innodb_file_format=$innodb_file_format_orig;
eval set global innodb_file_per_table=$innodb_file_per_table_orig;
-- enable_query_log