List:Commits« Previous MessageNext Message »
From:kevin.lewis Date:May 22 2012 9:41pm
Subject:bzr push into mysql-trunk branch (kevin.lewis:3903 to 3904)
View as plain text  
 3904 kevin.lewis@stripped	2012-05-22
      Add unknown files to bzr

    added:
      mysql-test/include/shutdown_mysqld.inc
      mysql-test/include/start_mysqld.inc
      mysql-test/r/partition_innodb_tablespace.result
      mysql-test/suite/innodb/r/innodb-multiple-tablespaces.result
      mysql-test/suite/innodb/r/innodb-tablespace.result
      mysql-test/suite/innodb/t/innodb-multiple-tablespaces.test
      mysql-test/suite/innodb/t/innodb-tablespace.test
      mysql-test/suite/parts/r/partition_reorganize_innodb.result
      mysql-test/suite/parts/r/partition_reorganize_myisam.result
      mysql-test/suite/parts/t/partition_reorganize_innodb.test
      mysql-test/suite/parts/t/partition_reorganize_myisam.test
      mysql-test/t/partition_innodb_tablespace.test
 3903 kevin.lewis@stripped	2012-05-22
      Update to current mysql-trunk.
      Add a fix for not comparing DATA_DIR flag in the tablespace flags to the DATA_DIR
      flag in SYS_TABLES table flags when opening a tablespace, whether local or
      remote, since the tablespace may have been moved.

    modified:
      include/my_base.h
      mysql-test/r/mysqlshow.result
      mysql-test/suite/innodb/r/innodb-restart.result
      mysql-test/suite/innodb/r/innodb-system-table-view.result
      mysql-test/suite/innodb/r/innodb_16k.result
      mysql-test/suite/innodb/r/innodb_4k.result
      mysql-test/suite/innodb/r/innodb_8k.result
      mysql-test/suite/innodb/t/innodb-restart.test
      mysql-test/suite/innodb/t/innodb-system-table-view.test
      mysql-test/suite/innodb/t/innodb_16k.test
      mysql-test/suite/innodb/t/innodb_4k.test
      mysql-test/suite/innodb/t/innodb_8k.test
      mysql-test/suite/parts/r/partition_basic_symlink_innodb.result
      mysql-test/suite/parts/t/partition_basic_symlink_innodb.test
      mysys/my_handler_errors.h
      sql/ha_partition.cc
      sql/ha_partition.h
      sql/handler.cc
      sql/share/errmsg-utf8.txt
      sql/sql_partition.cc
      sql/sql_partition.h
      sql/sql_table.cc
      storage/innobase/dict/dict0boot.cc
      storage/innobase/dict/dict0crea.cc
      storage/innobase/dict/dict0dict.cc
      storage/innobase/dict/dict0load.cc
      storage/innobase/fil/fil0fil.cc
      storage/innobase/handler/ha_innodb.cc
      storage/innobase/handler/ha_innodb.h
      storage/innobase/handler/handler0alter.cc
      storage/innobase/handler/i_s.cc
      storage/innobase/handler/i_s.h
      storage/innobase/include/db0err.h
      storage/innobase/include/dict0boot.h
      storage/innobase/include/dict0crea.h
      storage/innobase/include/dict0dict.h
      storage/innobase/include/dict0dict.ic
      storage/innobase/include/dict0load.h
      storage/innobase/include/dict0mem.h
      storage/innobase/include/fil0fil.h
      storage/innobase/include/fsp0fsp.h
      storage/innobase/include/fsp0fsp.ic
      storage/innobase/include/os0file.h
      storage/innobase/include/row0merge.h
      storage/innobase/lock/lock0lock.cc
      storage/innobase/os/os0file.cc
      storage/innobase/row/row0merge.cc
      storage/innobase/row/row0mysql.cc
      storage/innobase/srv/srv0start.cc
      storage/innobase/trx/trx0rec.cc
      storage/innobase/ut/ut0ut.cc
=== added file 'mysql-test/include/shutdown_mysqld.inc'
--- a/mysql-test/include/shutdown_mysqld.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/shutdown_mysqld.inc	revid:kevin.lewis@stripped
@@ -0,0 +1,17 @@
+
+if ($rpl_inited)
+{
+  if (!$allow_rpl_inited)
+  {
+    --die ERROR IN TEST: When using the replication test framework (master-slave.inc, rpl_init.inc etc), use rpl_restart_server.inc instead of restart_mysqld.inc. If you know what you are doing and you really have to use restart_mysqld.inc, set allow_rpl_inited=1 before you source restart_mysqld.inc
+  }
+}
+
+# Write file to make mysql-test-run.pl expect the "crash", but don't start it
+--let $_server_id= `SELECT @@server_id`
+--let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect
+--exec echo "wait" > $_expect_file_name
+
+# Send shutdown to the connected server
+--shutdown_server 10
+--source include/wait_until_disconnected.inc 

=== added file 'mysql-test/include/start_mysqld.inc'
--- a/mysql-test/include/start_mysqld.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/start_mysqld.inc	revid:kevin.lewis@stripped
@@ -0,0 +1,14 @@
+# Include this script only after using shutdown_mysqld.inc
+# where $_expect_file_name was initialized.
+# Write file to make mysql-test-run.pl start up the server again
+--exec echo "restart" > $_expect_file_name
+
+# Turn on reconnect
+--enable_reconnect
+
+# Call script that will poll the server waiting for it to be back online again
+--source include/wait_until_connected_again.inc
+
+# Turn off reconnect again
+--disable_reconnect
+

=== added file 'mysql-test/r/partition_innodb_tablespace.result'
--- a/mysql-test/r/partition_innodb_tablespace.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/partition_innodb_tablespace.result	revid:kevin.lewis@stripped
@@ -0,0 +1,315 @@
+SET default_storage_engine=InnoDB;
+#
+# TABLESPACE related tests for the partition engine and InnoDB.
+#
+# The partition engine can send DATA DIRECTORY to InnoDB.
+# In strict mode, it is an error if innodb_file_per_table = OFF
+# or INDEX DIRECTORY is used.
+SET SESSION innodb_strict_mode = ON;
+SET GLOBAL innodb_file_per_table = OFF;
+CREATE TABLE t1 (a int KEY, b text) ENGINE = InnoDB PARTITION BY HASH (a)
+(PARTITION p0 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data' INDEX DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data',
+PARTITION p1 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data' INDEX DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data');
+ERROR HY000: Can't create table 'test.t1' (errno: 1478)
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1478	InnoDB: DATA DIRECTORY requires innodb_file_per_table.
+Warning	1478	InnoDB: INDEX DIRECTORY is not supported
+Error	1005	Can't create table 'test.t1' (errno: 1478)
+Error	6	Error on delete of './test/t1.par' (Errcode: 2 - No such file or directory)
+# Try again with innodb_file_per_table = ON and no INDEX DIRECTORY.
+SET GLOBAL innodb_file_per_table = ON;
+CREATE TABLE t1 (a int KEY, b text) ENGINE = InnoDB PARTITION BY HASH (a)
+(PARTITION p0 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data',
+PARTITION p1 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2');
+SHOW WARNINGS;
+Level	Code	Message
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (a)
+(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data' ENGINE = InnoDB,
+ PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2' ENGINE = InnoDB) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t1#p#p0	5	Antelope	Compact
+test/t1#p#p1	5	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t1#p#p0	Antelope	Compact or Redundant
+test/t1#p#p1	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0.ibd
+MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1.ibd
+# Verifying .frm, .par, .isl & .ibd files
+# Verifying that there are no MyISAM files
+# Test TRUNCATE TABLE with partitioned InnoDB tables
+INSERT INTO t1 VALUES (1, "red");
+INSERT INTO t1 VALUES (2, "green");
+INSERT INTO t1 VALUES (3, "blue");
+SELECT * FROM t1;
+a	b
+2	green
+1	red
+3	blue
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+a	b
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (a)
+(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data' ENGINE = InnoDB,
+ PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2' ENGINE = InnoDB) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t1#p#p0	5	Antelope	Compact
+test/t1#p#p1	5	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t1#p#p0	Antelope	Compact or Redundant
+test/t1#p#p1	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0.ibd
+MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1.ibd
+# Verifying .frm, .par and MyISAM files (.MYD, MYI)
+# Test RENAME TABLE with partitioned InnoDB tables
+RENAME TABLE t1 TO t11;
+SHOW CREATE TABLE t11;
+Table	Create Table
+t11	CREATE TABLE `t11` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (a)
+(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data' ENGINE = InnoDB,
+ PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2' ENGINE = InnoDB) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t11#p#p0	5	Antelope	Compact
+test/t11#p#p1	5	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t11#p#p0	Antelope	Compact or Redundant
+test/t11#p#p1	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alternate_dir/data/test/t11#p#p0.ibd
+MYSQL_TMP_DIR/alternate_dir/data2/test/t11#p#p1.ibd
+# Verifying .frm, .par and MyISAM files (.MYD, MYI)
+DROP TABLE t11;
+# Test the previous DDL as a prepared statement.
+SET GLOBAL innodb_file_per_table = ON;
+PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text)
+     ENGINE = InnoDB PARTITION BY HASH (a)
+     (PARTITION p0 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data',
+      PARTITION p1 engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2')";
+EXECUTE stmt1;
+SHOW WARNINGS;
+Level	Code	Message
+DEALLOCATE PREPARE stmt1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (a)
+(PARTITION p0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data' ENGINE = InnoDB,
+ PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2' ENGINE = InnoDB) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t1#p#p0	5	Antelope	Compact
+test/t1#p#p1	5	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t1#p#p0	Antelope	Compact or Redundant
+test/t1#p#p1	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0.ibd
+MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1.ibd
+DROP TABLE t1;
+# Test DATA DIRECTORY with Sub-partitions.
+SET GLOBAL innodb_file_per_table = ON;
+CREATE TABLE t1 (id INT, purchased DATE) engine=InnoDB
+PARTITION BY RANGE( YEAR(purchased) )
+SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
+PARTITION p0 VALUES LESS THAN (1990) (
+SUBPARTITION s0 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data',
+SUBPARTITION s1 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2'
+),
+PARTITION p1 VALUES LESS THAN (2000) (
+SUBPARTITION s2 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data',
+SUBPARTITION s3 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2'
+),
+PARTITION p2 VALUES LESS THAN MAXVALUE (
+SUBPARTITION s4 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data',
+SUBPARTITION s5 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2'
+)
+);
+SHOW WARNINGS;
+Level	Code	Message
+INSERT INTO t1 VALUES(1,'1980-05-31');
+INSERT INTO t1 VALUES(2,'2090-05-31');
+INSERT INTO t1 VALUES(3,'2012-05-31');
+INSERT INTO t1 VALUES(4,'1970-05-31');
+INSERT INTO t1 VALUES(5,'1985-05-31');
+INSERT INTO t1 VALUES(6,'2006-05-31');
+SELECT * FROM t1;
+id	purchased
+4	1970-05-31
+1	1980-05-31
+5	1985-05-31
+2	2090-05-31
+3	2012-05-31
+6	2006-05-31
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) DEFAULT NULL,
+  `purchased` date DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE ( YEAR(purchased))
+SUBPARTITION BY HASH ( TO_DAYS(purchased))
+(PARTITION p0 VALUES LESS THAN (1990)
+ (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data' ENGINE = InnoDB,
+  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2' ENGINE = InnoDB),
+ PARTITION p1 VALUES LESS THAN (2000)
+ (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data' ENGINE = InnoDB,
+  SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2' ENGINE = InnoDB),
+ PARTITION p2 VALUES LESS THAN MAXVALUE
+ (SUBPARTITION s4 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data' ENGINE = InnoDB,
+  SUBPARTITION s5 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2' ENGINE = InnoDB)) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t1#p#p0#sp#s0	5	Antelope	Compact
+test/t1#p#p0#sp#s1	5	Antelope	Compact
+test/t1#p#p1#sp#s2	5	Antelope	Compact
+test/t1#p#p1#sp#s3	5	Antelope	Compact
+test/t1#p#p2#sp#s4	5	Antelope	Compact
+test/t1#p#p2#sp#s5	5	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t1#p#p0#sp#s0	Antelope	Compact or Redundant
+test/t1#p#p0#sp#s1	Antelope	Compact or Redundant
+test/t1#p#p1#sp#s2	Antelope	Compact or Redundant
+test/t1#p#p1#sp#s3	Antelope	Compact or Redundant
+test/t1#p#p2#sp#s4	Antelope	Compact or Redundant
+test/t1#p#p2#sp#s5	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0#sp#s0.ibd
+MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p0#sp#s1.ibd
+MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p1#sp#s2.ibd
+MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1#sp#s3.ibd
+MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p2#sp#s4.ibd
+MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p2#sp#s5.ibd
+# Verifying .frm, .par, .isl & .ibd files
+DROP TABLE t1;
+# Same as above except with ROW_FORMAT=Dyamic.
+SET GLOBAL innodb_file_format = Barracuda;
+SET GLOBAL innodb_file_per_table = ON;
+CREATE TABLE t1 (id INT, purchased DATE)
+engine = innodb row_format = dynamic
+PARTITION BY RANGE( YEAR(purchased) )
+SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
+PARTITION p0 VALUES LESS THAN (1990) (
+SUBPARTITION s0 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data',
+SUBPARTITION s1 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2'
+),
+PARTITION p1 VALUES LESS THAN (2000) (
+SUBPARTITION s2 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data',
+SUBPARTITION s3 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2'
+),
+PARTITION p2 VALUES LESS THAN MAXVALUE (
+SUBPARTITION s4 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data',
+SUBPARTITION s5 DATA DIRECTORY='MYSQL_TMP_DIR/alternate_dir/data2'
+)
+);
+SHOW WARNINGS;
+Level	Code	Message
+INSERT INTO t1 VALUES(1,'1980-05-31');
+INSERT INTO t1 VALUES(2,'2090-05-31');
+INSERT INTO t1 VALUES(3,'2012-05-31');
+INSERT INTO t1 VALUES(4,'1970-05-31');
+INSERT INTO t1 VALUES(5,'1985-05-31');
+INSERT INTO t1 VALUES(6,'2006-05-31');
+SELECT * FROM t1;
+id	purchased
+4	1970-05-31
+1	1980-05-31
+5	1985-05-31
+2	2090-05-31
+3	2012-05-31
+6	2006-05-31
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) DEFAULT NULL,
+  `purchased` date DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
+/*!50100 PARTITION BY RANGE ( YEAR(purchased))
+SUBPARTITION BY HASH ( TO_DAYS(purchased))
+(PARTITION p0 VALUES LESS THAN (1990)
+ (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data' ENGINE = InnoDB,
+  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2' ENGINE = InnoDB),
+ PARTITION p1 VALUES LESS THAN (2000)
+ (SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data' ENGINE = InnoDB,
+  SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2' ENGINE = InnoDB),
+ PARTITION p2 VALUES LESS THAN MAXVALUE
+ (SUBPARTITION s4 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data' ENGINE = InnoDB,
+  SUBPARTITION s5 DATA DIRECTORY = 'MYSQL_TMP_DIR/alternate_dir/data2' ENGINE = InnoDB)) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t1#p#p0#sp#s0	5	Barracuda	Dynamic
+test/t1#p#p0#sp#s1	5	Barracuda	Dynamic
+test/t1#p#p1#sp#s2	5	Barracuda	Dynamic
+test/t1#p#p1#sp#s3	5	Barracuda	Dynamic
+test/t1#p#p2#sp#s4	5	Barracuda	Dynamic
+test/t1#p#p2#sp#s5	5	Barracuda	Dynamic
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t1#p#p0#sp#s0	Barracuda	Dynamic
+test/t1#p#p0#sp#s1	Barracuda	Dynamic
+test/t1#p#p1#sp#s2	Barracuda	Dynamic
+test/t1#p#p1#sp#s3	Barracuda	Dynamic
+test/t1#p#p2#sp#s4	Barracuda	Dynamic
+test/t1#p#p2#sp#s5	Barracuda	Dynamic
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p0#sp#s0.ibd
+MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p0#sp#s1.ibd
+MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p1#sp#s2.ibd
+MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p1#sp#s3.ibd
+MYSQL_TMP_DIR/alternate_dir/data/test/t1#p#p2#sp#s4.ibd
+MYSQL_TMP_DIR/alternate_dir/data2/test/t1#p#p2#sp#s5.ibd
+# Verifying .frm, .par, .isl & .ibd files
+#
+# Cleanup
+#
+DROP TABLE t1;

=== added file 'mysql-test/suite/innodb/r/innodb-multiple-tablespaces.result'
--- a/mysql-test/suite/innodb/r/innodb-multiple-tablespaces.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/r/innodb-multiple-tablespaces.result	revid:kevin.lewis@stripped
@@ -0,0 +1,504 @@
+SET default_storage_engine=InnoDB;
+#
+# Test when tablespaces can be found at multiple places
+# SYS_DATAFILES will refer to the file at alt_dir.
+# Link File will refer to the file at new_dir.
+#  Tablename   Default   SYS_DATAFILES    Link_File
+#      yyy       Yes          Yes            Yes
+#      nyy       No           Yes            Yes
+#      yny       Yes          No             Yes
+#      yyn       Yes          Yes            No
+#      nyw       No           Yes            WrongFile
+#      nwy       No           WrongFile      Yes
+#      wny       WrongFile    No             Yes
+#      ynw       Yes          No             WrongFile
+#      wyn       WrongFile    Yes            No
+#      ywn       Yes          WrongFile      No
+#      nnn       No           No             No
+#      www       WrongFile    WrongFile      WrongFile
+#   nolink       No           Yes, No ISL    No
+#
+set global innodb_file_per_table=on;
+set global innodb_file_format='Barracuda';
+CREATE TABLE yyy (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO yyy VALUES (1, 'yyy');
+CREATE TABLE nyy (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO nyy VALUES (1, 'nyy');
+CREATE TABLE yny (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO yny VALUES (1, 'yny');
+CREATE TABLE yyn (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO yyn VALUES (1, 'yyn');
+CREATE TABLE nyw (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO nyw VALUES (1, 'nyw');
+CREATE TABLE nwy (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO nwy VALUES (1, 'nwy');
+CREATE TABLE wny (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO wny VALUES (1, 'wny');
+CREATE TABLE ynw (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO ynw VALUES (1, 'ynw');
+CREATE TABLE wyn (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO wyn VALUES (1, 'wyn');
+CREATE TABLE ywn (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO ywn VALUES (1, 'ywn');
+CREATE TABLE nnn (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO nnn VALUES (1, 'nnn');
+CREATE TABLE www (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO www VALUES (1, 'www');
+CREATE TABLE nolink (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO nolink VALUES (1, 'no link file');
+#
+# Shutdown the server, copy and remove files.
+#
+---- MYSQLD_DATADIR/test
+nnn.frm
+nnn.isl
+nolink.frm
+nolink.isl
+nwy.frm
+nwy.isl
+nyw.frm
+nyw.isl
+nyy.frm
+nyy.isl
+wny.frm
+wny.isl
+www.frm
+www.isl
+wyn.frm
+wyn.isl
+ynw.frm
+ynw.isl
+yny.frm
+yny.isl
+ywn.frm
+ywn.isl
+yyn.frm
+yyn.isl
+yyy.frm
+yyy.isl
+---- MYSQL_TMP_DIR/alt_dir/test
+nnn.ibd
+nolink.ibd
+nwy.ibd
+nyw.ibd
+nyy.ibd
+wny.ibd
+www.ibd
+wyn.ibd
+ynw.ibd
+yny.ibd
+ywn.ibd
+yyn.ibd
+yyy.ibd
+---- MYSQL_TMP_DIR/new_dir/test
+# YYY; Tablespace found in 3 places
+# NYY; Tablespace found in alt_dir and new_dir
+# YNY; Tablespace found in default and new_dir
+# YYN; Tablespace found in default and alt_dir
+# NYW; Copy the wrong file to new_dir
+# NWY; Copy the wrong file to alt_dir, good one to new_dir.
+# WNY; Copy the wrong file to default, good one to new_dir, delete it form alt_dir
+# YNW; Copy the file to default, wrong one to new_dir, delete it form alt_dir
+# WYN; Copy the wrong file to default
+# YWN; Copy the file to default, wrong one to alt_dir
+# NNN; Delete the tablespace and ISL
+# WWW; Put the wrong file in all three locations
+# NOLINK; Delete the ISL file Since remote location is still in SYS_DATAFILES,
+# it should still be found. And the ISL file should be re-created.
+---- MYSQLD_DATADIR/test
+nnn.frm
+nolink.frm
+nwy.frm
+nwy.isl
+nyw.frm
+nyw.isl
+nyy.frm
+nyy.isl
+wny.frm
+wny.ibd
+wny.isl
+www.frm
+www.ibd
+www.isl
+wyn.frm
+wyn.ibd
+wyn.isl
+ynw.frm
+ynw.ibd
+ynw.isl
+yny.frm
+yny.ibd
+yny.isl
+ywn.frm
+ywn.ibd
+ywn.isl
+yyn.frm
+yyn.ibd
+yyn.isl
+yyy.frm
+yyy.ibd
+yyy.isl
+---- MYSQL_TMP_DIR/alt_dir/test
+nolink.ibd
+nwy.ibd
+nyw.ibd
+nyy.ibd
+www.ibd
+wyn.ibd
+ywn.ibd
+yyn.ibd
+yyy.ibd
+---- MYSQL_TMP_DIR/new_dir/test
+nwy.ibd
+nyw.ibd
+nyy.ibd
+wny.ibd
+www.ibd
+ynw.ibd
+yny.ibd
+yyy.ibd
+#
+# Start the server and show the tablespaces.
+#
+SELECT path FROM information_schema.innodb_sys_datafiles ORDER BY path;
+path
+./test/ynw.ibd
+./test/ywn.ibd
+MYSQL_TMP_DIR/alt_dir/test/nnn.ibd
+MYSQL_TMP_DIR/alt_dir/test/nolink.ibd
+MYSQL_TMP_DIR/alt_dir/test/nyw.ibd
+MYSQL_TMP_DIR/alt_dir/test/nyy.ibd
+MYSQL_TMP_DIR/alt_dir/test/www.ibd
+MYSQL_TMP_DIR/alt_dir/test/wyn.ibd
+MYSQL_TMP_DIR/alt_dir/test/yny.ibd
+MYSQL_TMP_DIR/alt_dir/test/yyn.ibd
+MYSQL_TMP_DIR/alt_dir/test/yyy.ibd
+MYSQL_TMP_DIR/new_dir/test/nwy.ibd
+MYSQL_TMP_DIR/new_dir/test/wny.ibd
+SELECT * FROM yyy;
+ERROR 42S02: Table 'test.yyy' doesn't exist
+SELECT * FROM nyy;
+ERROR 42S02: Table 'test.nyy' doesn't exist
+SELECT * FROM yny;
+ERROR 42S02: Table 'test.yny' doesn't exist
+SELECT * FROM yyn;
+ERROR 42S02: Table 'test.yyn' doesn't exist
+SELECT * FROM nyw;
+c1	c2
+1	nyw
+SELECT * FROM nwy;
+c1	c2
+1	nwy
+SELECT * FROM wny;
+c1	c2
+1	wny
+SELECT * FROM ynw;
+c1	c2
+1	ynw
+SELECT * FROM wyn;
+c1	c2
+1	wyn
+SELECT * FROM ywn;
+c1	c2
+1	ywn
+SELECT * FROM nnn;
+ERROR 42S02: Table 'test.nnn' doesn't exist
+SELECT * FROM www;
+ERROR 42S02: Table 'test.www' doesn't exist
+SELECT * FROM nolink;
+c1	c2
+1	no link file
+SHOW CREATE TABLE yyy;
+ERROR 42S02: Table 'test.yyy' doesn't exist
+SHOW CREATE TABLE nyy;
+ERROR 42S02: Table 'test.nyy' doesn't exist
+SHOW CREATE TABLE yny;
+ERROR 42S02: Table 'test.yny' doesn't exist
+SHOW CREATE TABLE yyn;
+ERROR 42S02: Table 'test.yyn' doesn't exist
+SHOW CREATE TABLE nyw;
+Table	Create Table
+nyw	CREATE TABLE `nyw` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+SHOW CREATE TABLE nwy;
+Table	Create Table
+nwy	CREATE TABLE `nwy` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/new_dir/'
+SHOW CREATE TABLE wny;
+Table	Create Table
+wny	CREATE TABLE `wny` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/new_dir/'
+SHOW CREATE TABLE ynw;
+Table	Create Table
+ynw	CREATE TABLE `ynw` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE wyn;
+Table	Create Table
+wyn	CREATE TABLE `wyn` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+SHOW CREATE TABLE ywn;
+Table	Create Table
+ywn	CREATE TABLE `ywn` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE nnn;
+ERROR 42S02: Table 'test.nnn' doesn't exist
+SHOW CREATE TABLE www;
+ERROR 42S02: Table 'test.www' doesn't exist
+SHOW CREATE TABLE nolink;
+Table	Create Table
+nolink	CREATE TABLE `nolink` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+#
+# Cleanup after restart
+#
+DROP TABLE yyy;
+DROP TABLE nyy;
+DROP TABLE yny;
+DROP TABLE yyn;
+DROP TABLE nyw;
+DROP TABLE nwy;
+DROP TABLE wny;
+DROP TABLE ynw;
+DROP TABLE wyn;
+DROP TABLE ywn;
+DROP TABLE nnn;
+DROP TABLE www;
+DROP TABLE nolink;
+---- MYSQLD_DATADIR/test
+wny.ibd
+www.ibd
+wyn.ibd
+yny.ibd
+yyn.ibd
+yyy.ibd
+---- MYSQL_TMP_DIR/alt_dir/test
+nwy.ibd
+nyy.ibd
+www.ibd
+ywn.ibd
+yyn.ibd
+yyy.ibd
+---- MYSQL_TMP_DIR/new_dir/test
+nyw.ibd
+nyy.ibd
+www.ibd
+ynw.ibd
+yny.ibd
+yyy.ibd
+#
+# Create some tables again and this time, crash instead of shutdown
+# InnoDB recovery does not have the ability at this time to query
+# the data dictionary in order to determine if the table it is
+# openeing is the correct one, or to finde the previous location
+# of a tablespace from SYS_DATAFILES.  It must rely on the ISL file
+# to tell the truth. But it can compare the current linked location 
+# with a tablespace found in the default location and use the most
+# recent.
+#
+# Test recovery when tablespaces can be found at multiple places.
+# SYS_DATAFILES is unavailable during recovery.
+# Link File will refer to the file at alt_dir.
+# In each case except the control tablespace, the Link file will
+# exist with a file name in alt_dir.  'aa' is the control table and
+# is alphabetically before the other tablespace names so it will be
+# recovered first by InnoDB.  It is the source of the 'wrong' tablespaces.
+# Tablename   Default_Tablespace  Remote_Tablespace
+#    aa              Yes                 No
+#    yy              Yes                 Yes  (both the same file)
+#    yn              Yes                 No
+#    ny              Yes                 Yes
+#    yw              Yes                 Wrong
+#    wy              Wrong               Yes
+#
+set global innodb_file_per_table=on;
+set global innodb_file_format='Barracuda';
+CREATE TABLE aa (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO aa VALUES (1, 'aa - control table');
+CREATE TABLE yy (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO yy VALUES (1, 'yy');
+CREATE TABLE yn (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO yn VALUES (1, 'yn');
+CREATE TABLE ny (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO ny VALUES (1, 'ny');
+CREATE TABLE yw (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO yw VALUES (1, 'yw');
+CREATE TABLE wy (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO wy VALUES (1, 'wy');
+#
+# Crash the server, copy and remove files.
+#
+BEGIN;
+INSERT INTO aa VALUES (2, 'aa');
+INSERT INTO yy VALUES (2, 'yy');
+INSERT INTO yn VALUES (2, 'yn');
+INSERT INTO ny VALUES (2, 'ny');
+INSERT INTO yw VALUES (2, 'yw');
+INSERT INTO wy VALUES (2, 'wy');
+SET SESSION debug="+d,crash_commit_before";
+COMMIT;
+ERROR HY000: Lost connection to MySQL server during query
+# YY; Found in both default directory and alt-dir.
+---- MYSQLD_DATADIR/test
+aa.frm
+aa.isl
+ny.frm
+ny.isl
+wy.frm
+wy.isl
+yn.frm
+yn.isl
+yw.frm
+yw.isl
+yy.frm
+yy.ibd
+yy.isl
+---- MYSQL_TMP_DIR/alt_dir/test
+aa.ibd
+ny.ibd
+wy.ibd
+yn.ibd
+yw.ibd
+yy.ibd
+#
+# Start the server and let it do recovery.
+# It will crash when it finds two copies of yy.
+#
+# Delete the extra copy of yy.
+# AA; Control table, used as the source of 'wrong' tablespaces and opened first during recovery.
+# YN; Tablespace found the default directory but not in alt_dir 
+# NY; Tablespace found in alt_dir but not the default directory.
+# YW; Tablespace found in the default directory but the wrong file in alt_dir
+# WY; The wrong tablespace found in the default directory and the corect one in alt_dir 
+---- MYSQLD_DATADIR/test
+aa.frm
+aa.isl
+ny.frm
+ny.isl
+wy.frm
+wy.ibd
+wy.isl
+yn.frm
+yn.ibd
+yn.isl
+yw.frm
+yw.ibd
+yw.isl
+yy.frm
+yy.isl
+---- MYSQL_TMP_DIR/alt_dir/test
+aa.ibd
+ny.ibd
+wy.ibd
+yw.ibd
+yy.ibd
+#
+# Start the server and let it do recovery.
+#
+SHOW VARIABLES LIKE 'innodb_strict_mode';
+Variable_name	Value
+innodb_strict_mode	OFF
+SELECT path FROM information_schema.innodb_sys_datafiles ORDER BY path;
+path
+MYSQL_TMP_DIR/alt_dir/test/aa.ibd
+MYSQL_TMP_DIR/alt_dir/test/ny.ibd
+MYSQL_TMP_DIR/alt_dir/test/wy.ibd
+MYSQL_TMP_DIR/alt_dir/test/yn.ibd
+MYSQL_TMP_DIR/alt_dir/test/yw.ibd
+MYSQL_TMP_DIR/alt_dir/test/yy.ibd
+SELECT * FROM aa;
+c1	c2
+1	aa - control table
+SELECT * FROM yy;
+c1	c2
+1	yy
+SELECT * FROM yn;
+c1	c2
+1	yn
+SELECT * FROM ny;
+c1	c2
+1	ny
+SELECT * FROM yw;
+c1	c2
+1	yw
+SELECT * FROM wy;
+c1	c2
+1	wy
+SHOW CREATE TABLE aa;
+Table	Create Table
+aa	CREATE TABLE `aa` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+SHOW CREATE TABLE yy;
+Table	Create Table
+yy	CREATE TABLE `yy` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+SHOW CREATE TABLE yn;
+Table	Create Table
+yn	CREATE TABLE `yn` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE ny;
+Table	Create Table
+ny	CREATE TABLE `ny` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+SHOW CREATE TABLE yw;
+Table	Create Table
+yw	CREATE TABLE `yw` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE wy;
+Table	Create Table
+wy	CREATE TABLE `wy` (
+  `c1` int(11) NOT NULL,
+  `c2` text,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+#
+# Cleanup after recovery
+#
+DROP TABLE aa;
+DROP TABLE yy;
+DROP TABLE yn;
+DROP TABLE ny;
+DROP TABLE yw;
+DROP TABLE wy;
+---- MYSQLD_DATADIR/test
+wy.ibd
+---- MYSQL_TMP_DIR/alt_dir/test
+yw.ibd
+#
+# Cleanup
+#

=== added file 'mysql-test/suite/innodb/r/innodb-tablespace.result'
--- a/mysql-test/suite/innodb/r/innodb-tablespace.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/r/innodb-tablespace.result	revid:kevin.lewis@stripped
@@ -0,0 +1,580 @@
+SET default_storage_engine=InnoDB;
+#
+# TABLESPACE related tests.
+#
+#
+# CREATE TABLE ... DATA DIRECTORY
+# Innodb does not support INDEX DIRECTORY.
+#
+SET SESSION innodb_strict_mode = ON;
+CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+ERROR HY000: Can't create table 'test.t1' (errno: 1478)
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1478	InnoDB: INDEX DIRECTORY is not supported
+Error	1005	Can't create table 'test.t1' (errno: 1478)
+#
+# Without strict mode, INDEX DIRECTORY is just ignored
+#
+SET SESSION innodb_strict_mode = OFF;
+CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+Warnings:
+Warning	1618	<INDEX DIRECTORY> option ignored
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1618	<INDEX DIRECTORY> option ignored
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON.
+#
+SET SESSION innodb_strict_mode = ON;
+SET GLOBAL innodb_file_per_table=OFF;
+CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+ERROR HY000: Can't create table 'test.t1' (errno: 1478)
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1478	InnoDB: DATA DIRECTORY requires innodb_file_per_table.
+Error	1005	Can't create table 'test.t1' (errno: 1478)
+#
+# Without strict mode, DATA DIRECTORY without innodb_file_per_table=ON is just ignored.
+#
+SET SESSION innodb_strict_mode = OFF;
+CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+Warnings:
+Warning	1478	InnoDB: DATA DIRECTORY requires innodb_file_per_table.
+Warning	1618	<DATA DIRECTORY> option ignored
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1478	InnoDB: DATA DIRECTORY requires innodb_file_per_table.
+Warning	1618	<DATA DIRECTORY> option ignored
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+# Now set innodb_file_per_table so that DATA DIRECTORY can be tested.
+SET GLOBAL innodb_file_per_table=ON;
+#
+# Create the tablespace in MYSQL_TMP_DIR/alt_dir
+# InnoDB will create the sub-directories if needed.
+#
+CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+SHOW WARNINGS;
+Level	Code	Message
+INSERT INTO t1 VALUES (1, "Create the tablespace");
+SELECT * FROM t1;
+a	b
+1	Create the tablespace
+#
+# Check if link file exists  in MYSQLD_DATADIR
+#
+---- MYSQLD_DATADIR/test
+t1.frm
+t1.isl
+# Check if tablespace file exists where we specified in DATA DIRECTORY
+---- MYSQL_TMP_DIR/alt_dir/test
+t1.ibd
+#
+# Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
+#
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+# Show that the new system tables have this table in them correctly
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t1	5	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t1	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alt_dir/test/t1.ibd
+#
+# Show that the system tables are updated on drop table
+#
+DROP TABLE t1;
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+#
+# Create the same table a second time in the same place
+#
+CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+INSERT INTO t1 VALUES (2, "Create the same table a second time in the same place");
+SELECT * FROM t1;
+a	b
+2	Create the same table a second time in the same place
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t1	5	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t1	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alt_dir/test/t1.ibd
+---- MYSQLD_DATADIR/test
+t1.frm
+t1.isl
+---- MYSQL_TMP_DIR/alt_dir/test
+t1.ibd
+#
+# Truncate the table, then insert and verify
+#
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (3, "Truncate the table, then insert");
+SELECT * FROM t1;
+a	b
+3	Truncate the table, then insert
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t1	5	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t1	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alt_dir/test/t1.ibd
+---- MYSQLD_DATADIR/test
+t1.frm
+t1.isl
+---- MYSQL_TMP_DIR/alt_dir/test
+t1.ibd
+#
+# Rename the table, then insert and verify
+#
+RENAME TABLE t1 TO t2;
+INSERT INTO t2 VALUES (4, "Rename the table, then insert");
+SELECT * FROM t2;
+a	b
+3	Truncate the table, then insert
+4	Rename the table, then insert
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t2	5	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t2	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alt_dir/test/t2.ibd
+---- MYSQLD_DATADIR/test
+t2.frm
+t2.isl
+---- MYSQL_TMP_DIR/alt_dir/test
+t2.ibd
+#
+# CREATE TABLE LIKE does not retain DATA DIRECTORY automatically.
+#
+CREATE TABLE t3 LIKE t2;
+INSERT INTO t3 VALUES (5, "CREATE TABLE LIKE");
+SELECT * FROM t3;
+a	b
+5	CREATE TABLE LIKE
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+test/t2	5	Antelope	Compact
+test/t3	5	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+test/t2	Antelope	Compact or Redundant
+test/t3	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alt_dir/test/t2.ibd
+MYSQLD_DATADIR/test/t3.ibd
+---- MYSQLD_DATADIR/test
+t2.frm
+t2.isl
+t3.frm
+t3.ibd
+#
+# Now make sure the tables can be fully dropped.
+#
+DROP TABLE t2, t3;
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+name	n_cols	file_format	row_format
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces;
+name	file_format	row_format
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+---- MYSQLD_DATADIR/test
+---- MYSQL_TMP_DIR/alt_dir/test
+#
+# Be sure SQL MODE "NO_DIR_IN_CREATE" prevents the use of DATA DIRECTORY
+#
+SET @org_mode=@@sql_mode;
+SET @@sql_mode='NO_DIR_IN_CREATE';
+SELECT @@sql_mode;
+@@sql_mode
+NO_DIR_IN_CREATE
+CREATE TABLE t1 (a int, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+Warnings:
+Warning	1618	<DATA DIRECTORY> option ignored
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1618	<DATA DIRECTORY> option ignored
+INSERT INTO t1 VALUES (6, "SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY");
+DROP TABLE t1;
+set @@sql_mode=@org_mode;
+#
+# MySQL engine does not allow DATA DIRECTORY to be
+# within --datadir for any engine, including InnoDB
+#
+CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY 'MYSQLD_DATADIR/test';
+ERROR HY000: Incorrect arguments to DATA DIRECTORY
+# TEMPORARY tables are incompatible with DATA DIRECTORY
+SET SESSION innodb_strict_mode = ON;
+CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+ERROR HY000: Can't create table 'test.t1' (errno: 1478)
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1478	InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
+Error	1005	Can't create table 'test.t1' (errno: 1478)
+SET SESSION innodb_strict_mode = OFF;
+CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
+Warnings:
+Warning	1478	InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
+Warning	1618	<DATA DIRECTORY> option ignored
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1478	InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables.
+Warning	1618	<DATA DIRECTORY> option ignored
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TEMPORARY TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+---- MYSQLD_DATADIR/test
+---- MYSQL_TMP_DIR/alt_dir/test
+#
+# Create the remote table via static DDL statements in a stored procedure
+#
+CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; END |
+CALL static_proc;
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alt_dir/test/t1.ibd
+INSERT INTO t1 VALUES (7, "Create the remote table via static DDL statements");
+SELECT * FROM t1;
+a	b
+7	Create the remote table via static DDL statements
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+---- MYSQLD_DATADIR/test
+t1.frm
+t1.isl
+---- MYSQL_TMP_DIR/alt_dir/test
+t1.ibd
+DROP PROCEDURE static_proc;
+DROP TABLE t1;
+#
+# Create the remote table via dynamic DDL statements in a stored procedure
+#
+CREATE PROCEDURE dynamic_proc() BEGIN PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'"; EXECUTE stmt1; END |
+CALL dynamic_proc;
+SELECT path FROM information_schema.innodb_sys_datafiles;
+path
+MYSQL_TMP_DIR/alt_dir/test/t1.ibd
+INSERT INTO t1 VALUES (8, "Create the remote table via dynamic DDL statements");
+SELECT * FROM t1;
+a	b
+8	Create the remote table via dynamic DDL statements
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` text,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
+---- MYSQLD_DATADIR/test
+t1.frm
+t1.isl
+---- MYSQL_TMP_DIR/alt_dir/test
+t1.ibd
+DROP PROCEDURE dynamic_proc;
+DROP TABLE t1;
+#
+# CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY
+#
+CREATE TABLE emp (
+id INT NOT NULL,
+store_name VARCHAR(30),
+parts VARCHAR(30),
+store_id INT
+)
+PARTITION BY LIST(store_id) (
+PARTITION east VALUES IN (10,20,30)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east',
+PARTITION north VALUES IN (40,50,60)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north',
+PARTITION west VALUES IN (70,80,100)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'
+);
+INSERT INTO emp values(1,'Oracle','NUTT',10);
+INSERT INTO emp values(2,'HUAWEI','BOLT',40);
+INSERT INTO emp values(3,'IBM','NAIL',70);
+SHOW CREATE TABLE emp;
+Table	Create Table
+emp	CREATE TABLE `emp` (
+  `id` int(11) NOT NULL,
+  `store_name` varchar(30) DEFAULT NULL,
+  `parts` varchar(30) DEFAULT NULL,
+  `store_id` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY LIST (store_id)
+(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
+ PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
+ PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables
+WHERE name LIKE 'test%'
+       ORDER BY name;
+name	n_cols	file_format	row_format
+test/emp#p#east	7	Antelope	Compact
+test/emp#p#north	7	Antelope	Compact
+test/emp#p#west	7	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces
+ORDER BY name;
+name	file_format	row_format
+test/emp#p#east	Antelope	Compact or Redundant
+test/emp#p#north	Antelope	Compact or Redundant
+test/emp#p#west	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles
+ORDER BY path;
+path
+MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
+MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
+MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
+SELECT * FROM emp;
+id	store_name	parts	store_id
+1	Oracle	NUTT	10
+2	HUAWEI	BOLT	40
+3	IBM	NAIL	70
+---- MYSQLD_DATADIR/test
+emp#p#east.isl
+emp#p#north.isl
+emp#p#west.isl
+emp.frm
+emp.par
+---- MYSQL_TMP_DIR/alt_dir_east/test
+emp#p#east.ibd
+---- MYSQL_TMP_DIR/alt_dir_north/test
+emp#p#north.ibd
+---- MYSQL_TMP_DIR/alt_dir_west/test
+emp#p#west.ibd
+#
+# DROP one PARTITION.
+#
+ALTER TABLE emp DROP PARTITION west;
+SHOW CREATE TABLE emp;
+Table	Create Table
+emp	CREATE TABLE `emp` (
+  `id` int(11) NOT NULL,
+  `store_name` varchar(30) DEFAULT NULL,
+  `parts` varchar(30) DEFAULT NULL,
+  `store_id` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY LIST (store_id)
+(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
+ PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables
+WHERE name LIKE 'test%'
+       ORDER BY name;
+name	n_cols	file_format	row_format
+test/emp#p#east	7	Antelope	Compact
+test/emp#p#north	7	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces
+ORDER BY name;
+name	file_format	row_format
+test/emp#p#east	Antelope	Compact or Redundant
+test/emp#p#north	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles
+ORDER BY path;
+path
+MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
+MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
+SELECT * FROM emp;
+id	store_name	parts	store_id
+1	Oracle	NUTT	10
+2	HUAWEI	BOLT	40
+---- MYSQLD_DATADIR/test
+emp#p#east.isl
+emp#p#north.isl
+emp.frm
+emp.par
+---- MYSQL_TMP_DIR/alt_dir_east/test
+emp#p#east.ibd
+---- MYSQL_TMP_DIR/alt_dir_north/test
+emp#p#north.ibd
+---- MYSQL_TMP_DIR/alt_dir_west/test
+#
+# ADD the PARTITION back.
+#
+ALTER TABLE emp ADD PARTITION (
+PARTITION west VALUES IN (70,80,100)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west');
+SHOW CREATE TABLE emp;
+Table	Create Table
+emp	CREATE TABLE `emp` (
+  `id` int(11) NOT NULL,
+  `store_name` varchar(30) DEFAULT NULL,
+  `parts` varchar(30) DEFAULT NULL,
+  `store_id` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY LIST (store_id)
+(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
+ PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
+ PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables
+WHERE name LIKE 'test%'
+       ORDER BY name;
+name	n_cols	file_format	row_format
+test/emp#p#east	7	Antelope	Compact
+test/emp#p#north	7	Antelope	Compact
+test/emp#p#west	7	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces
+ORDER BY name;
+name	file_format	row_format
+test/emp#p#east	Antelope	Compact or Redundant
+test/emp#p#north	Antelope	Compact or Redundant
+test/emp#p#west	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles
+ORDER BY path;
+path
+MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
+MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
+MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
+INSERT INTO emp VALUES(3,'IBM','NAIL',70);
+SELECT * FROM emp;
+id	store_name	parts	store_id
+1	Oracle	NUTT	10
+2	HUAWEI	BOLT	40
+3	IBM	NAIL	70
+---- MYSQLD_DATADIR/test
+emp#p#east.isl
+emp#p#north.isl
+emp#p#west.isl
+emp.frm
+emp.par
+---- MYSQL_TMP_DIR/alt_dir_east/test
+emp#p#east.ibd
+---- MYSQL_TMP_DIR/alt_dir_north/test
+emp#p#north.ibd
+---- MYSQL_TMP_DIR/alt_dir_west/test
+emp#p#west.ibd
+#
+# TRUNCATE one PARTITION.
+#
+ALTER TABLE emp TRUNCATE PARTITION west;
+SHOW CREATE TABLE emp;
+Table	Create Table
+emp	CREATE TABLE `emp` (
+  `id` int(11) NOT NULL,
+  `store_name` varchar(30) DEFAULT NULL,
+  `parts` varchar(30) DEFAULT NULL,
+  `store_id` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY LIST (store_id)
+(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
+ PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
+ PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables
+WHERE name LIKE 'test%'
+       ORDER BY name;
+name	n_cols	file_format	row_format
+test/emp#p#east	7	Antelope	Compact
+test/emp#p#north	7	Antelope	Compact
+test/emp#p#west	7	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces
+ORDER BY name;
+name	file_format	row_format
+test/emp#p#east	Antelope	Compact or Redundant
+test/emp#p#north	Antelope	Compact or Redundant
+test/emp#p#west	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles
+ORDER BY path;
+path
+MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd
+MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd
+MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd
+SELECT * FROM emp;
+id	store_name	parts	store_id
+1	Oracle	NUTT	10
+2	HUAWEI	BOLT	40
+INSERT INTO emp VALUES(3,'IBM','NAIL',70);
+SELECT * FROM emp;
+id	store_name	parts	store_id
+1	Oracle	NUTT	10
+2	HUAWEI	BOLT	40
+3	IBM	NAIL	70
+---- MYSQLD_DATADIR/test
+emp#p#east.isl
+emp#p#north.isl
+emp#p#west.isl
+emp.frm
+emp.par
+---- MYSQL_TMP_DIR/alt_dir_east/test
+emp#p#east.ibd
+---- MYSQL_TMP_DIR/alt_dir_north/test
+emp#p#north.ibd
+---- MYSQL_TMP_DIR/alt_dir_west/test
+emp#p#west.ibd
+DROP TABLE emp;
+#
+# Cleanup
+#

=== added file 'mysql-test/suite/innodb/t/innodb-multiple-tablespaces.test'
--- a/mysql-test/suite/innodb/t/innodb-multiple-tablespaces.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/t/innodb-multiple-tablespaces.test	revid:kevin.lewis@stripped
@@ -0,0 +1,493 @@
+#
+# These test make sure that tables are visible after rebooting
+#
+
+--source include/have_innodb.inc
+
+# Must have debug code to use SET SESSION debug
+--source include/have_debug.inc
+
+# Valgrind would complain about memory leaks when we crash on purpose.
+--source include/not_valgrind.inc
+
+# Embedded server does not support crashing
+--source include/not_embedded.inc
+
+# Avoid CrashReporter popup on Mac
+#--source include/not_crashrep.inc
+
+SET default_storage_engine=InnoDB;
+
+--disable_query_log
+let $MYSQLD_DATADIR= `select @@datadir`;
+let $data_directory = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
+
+let $innodb_file_per_table=`select @@innodb_file_per_table`;
+let $innodb_file_format=`select @@innodb_file_format`;
+--enable_query_log
+
+--mkdir $MYSQL_TMP_DIR/alt_dir
+--mkdir $MYSQL_TMP_DIR/alt_dir/test
+--mkdir $MYSQL_TMP_DIR/new_dir
+--mkdir $MYSQL_TMP_DIR/new_dir/test
+
+--disable_query_log
+# These errors are expected in the error log for this test.
+call mtr.add_suppression("InnoDB: Error: trying to open a table, but could not");
+call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file");
+call mtr.add_suppression("InnoDB: Error: table 'test");
+call mtr.add_suppression("InnoDB: Error: The tablespace for test");
+call mtr.add_suppression("has been found in multiple places");
+call mtr.add_suppression("MySQL is trying to open a table handle but the .ibd file for");
+call mtr.add_suppression("InnoDB: Error: Attempt to open a tablespace previously opened.");
+call mtr.add_suppression("InnoDB: Error: A link file was found named");
+--enable_query_log
+
+--echo #
+--echo # Test when tablespaces can be found at multiple places
+--echo # SYS_DATAFILES will refer to the file at alt_dir.
+--echo # Link File will refer to the file at new_dir.
+--echo #  Tablename   Default   SYS_DATAFILES    Link_File
+--echo #      yyy       Yes          Yes            Yes
+--echo #      nyy       No           Yes            Yes
+--echo #      yny       Yes          No             Yes
+--echo #      yyn       Yes          Yes            No
+--echo #      nyw       No           Yes            WrongFile
+--echo #      nwy       No           WrongFile      Yes
+--echo #      wny       WrongFile    No             Yes
+--echo #      ynw       Yes          No             WrongFile
+--echo #      wyn       WrongFile    Yes            No
+--echo #      ywn       Yes          WrongFile      No
+--echo #      nnn       No           No             No
+--echo #      www       WrongFile    WrongFile      WrongFile
+--echo #   nolink       No           Yes, No ISL    No
+--echo #
+
+set global innodb_file_per_table=on;
+set global innodb_file_format='Barracuda';
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE yyy (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO yyy VALUES (1, 'yyy');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE nyy (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO nyy VALUES (1, 'nyy');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE yny (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO yny VALUES (1, 'yny');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE yyn (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO yyn VALUES (1, 'yyn');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE nyw (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO nyw VALUES (1, 'nyw');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE nwy (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO nwy VALUES (1, 'nwy');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE wny (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO wny VALUES (1, 'wny');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE ynw (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO ynw VALUES (1, 'ynw');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE wyn (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO wyn VALUES (1, 'wyn');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE ywn (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO ywn VALUES (1, 'ywn');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE nnn (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO nnn VALUES (1, 'nnn');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE www (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO www VALUES (1, 'www');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE nolink (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO nolink VALUES (1, 'no link file');
+
+--echo #
+--echo # Shutdown the server, copy and remove files.
+--echo #
+--source include/shutdown_mysqld.inc
+
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQLD_DATADIR/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 # YYY; Tablespace found in 3 places
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQLD_DATADIR/test/yyy.ibd
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQL_TMP_DIR/new_dir/test/yyy.ibd 
+--exec echo $MYSQL_TMP_DIR/new_dir/test/yyy.ibd > $MYSQLD_DATADIR/test/yyy.isl
+
+--echo # NYY; Tablespace found in alt_dir and new_dir
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/nyy.ibd $MYSQL_TMP_DIR/new_dir/test/nyy.ibd 
+--exec echo $MYSQL_TMP_DIR/new_dir/test/nyy.ibd > $MYSQLD_DATADIR/test/nyy.isl
+
+--echo # YNY; Tablespace found in default and new_dir
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yny.ibd $MYSQLD_DATADIR/test/yny.ibd
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yny.ibd $MYSQL_TMP_DIR/new_dir/test/yny.ibd 
+--exec echo $MYSQL_TMP_DIR/new_dir/test/yny.ibd > $MYSQLD_DATADIR/test/yny.isl
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/yny.ibd
+
+--echo # YYN; Tablespace found in default and alt_dir
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyn.ibd $MYSQLD_DATADIR/test/yyn.ibd
+
+--echo # NYW; Copy the wrong file to new_dir
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQL_TMP_DIR/new_dir/test/nyw.ibd 
+--exec echo $MYSQL_TMP_DIR/new_dir/test/nyw.ibd > $MYSQLD_DATADIR/test/nyw.isl
+
+--echo # NWY; Copy the wrong file to alt_dir, good one to new_dir.
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/nwy.ibd $MYSQL_TMP_DIR/new_dir/test/nwy.ibd 
+--exec echo $MYSQL_TMP_DIR/new_dir/test/nwy.ibd > $MYSQLD_DATADIR/test/nwy.isl
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/nwy.ibd
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQL_TMP_DIR/alt_dir/test/nwy.ibd 
+
+--echo # WNY; Copy the wrong file to default, good one to new_dir, delete it form alt_dir
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQLD_DATADIR/test/wny.ibd
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/wny.ibd $MYSQL_TMP_DIR/new_dir/test/wny.ibd 
+--exec echo $MYSQL_TMP_DIR/new_dir/test/wny.ibd > $MYSQLD_DATADIR/test/wny.isl
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/wny.ibd
+
+--echo # YNW; Copy the file to default, wrong one to new_dir, delete it form alt_dir
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/ynw.ibd $MYSQLD_DATADIR/test/ynw.ibd
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQL_TMP_DIR/new_dir/test/ynw.ibd 
+--exec echo $MYSQL_TMP_DIR/new_dir/test/ynw.ibd > $MYSQLD_DATADIR/test/ynw.isl
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/ynw.ibd
+
+--echo # WYN; Copy the wrong file to default
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQLD_DATADIR/test/wyn.ibd
+
+--echo # YWN; Copy the file to default, wrong one to alt_dir
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/ywn.ibd $MYSQLD_DATADIR/test/ywn.ibd
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/ywn.ibd
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQL_TMP_DIR/alt_dir/test/ywn.ibd
+
+--echo # NNN; Delete the tablespace and ISL
+--remove_file $MYSQLD_DATADIR/test/nnn.isl
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/nnn.ibd
+
+--echo # WWW; Put the wrong file in all three locations
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQLD_DATADIR/test/www.ibd
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/www.ibd
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQL_TMP_DIR/alt_dir/test/www.ibd
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd $MYSQL_TMP_DIR/new_dir/test/www.ibd
+--exec echo $MYSQL_TMP_DIR/new_dir/test/www.ibd > $MYSQLD_DATADIR/test/www.isl
+
+--echo # NOLINK; Delete the ISL file Since remote location is still in SYS_DATAFILES,
+--echo # it should still be found. And the ISL file should be re-created.
+--remove_file $MYSQLD_DATADIR/test/nolink.isl
+
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQLD_DATADIR/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 show the tablespaces.
+--echo #
+--source include/start_mysqld.inc
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles ORDER BY path;
+
+--error ER_NO_SUCH_TABLE
+SELECT * FROM yyy;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM nyy;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM yny;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM yyn;
+SELECT * FROM nyw;
+SELECT * FROM nwy;
+SELECT * FROM wny;
+SELECT * FROM ynw;
+SELECT * FROM wyn;
+SELECT * FROM ywn;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM nnn;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM www;
+SELECT * FROM nolink;
+
+--error ER_NO_SUCH_TABLE
+SHOW CREATE TABLE yyy;
+--error ER_NO_SUCH_TABLE
+SHOW CREATE TABLE nyy;
+--error ER_NO_SUCH_TABLE
+SHOW CREATE TABLE yny;
+--error ER_NO_SUCH_TABLE
+SHOW CREATE TABLE yyn;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE nyw;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE nwy;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE wny;
+SHOW CREATE TABLE ynw;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE wyn;
+SHOW CREATE TABLE ywn;
+--error ER_NO_SUCH_TABLE
+SHOW CREATE TABLE nnn;
+--error ER_NO_SUCH_TABLE
+SHOW CREATE TABLE www;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE nolink;
+
+--echo #
+--echo # Cleanup after restart
+--echo #
+
+DROP TABLE yyy;
+DROP TABLE nyy;
+DROP TABLE yny;
+DROP TABLE yyn;
+DROP TABLE nyw;
+DROP TABLE nwy;
+DROP TABLE wny;
+DROP TABLE ynw;
+DROP TABLE wyn;
+DROP TABLE ywn;
+DROP TABLE nnn;
+DROP TABLE www;
+DROP TABLE nolink;
+
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQLD_DATADIR/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
+
+--remove_file $MYSQLD_DATADIR/test/www.ibd
+--remove_file $MYSQLD_DATADIR/test/wny.ibd
+--remove_file $MYSQLD_DATADIR/test/wyn.ibd
+--remove_file $MYSQLD_DATADIR/test/yny.ibd
+--remove_file $MYSQLD_DATADIR/test/yyn.ibd
+--remove_file $MYSQLD_DATADIR/test/yyy.ibd
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/nwy.ibd
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/nyy.ibd
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/www.ibd
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/ywn.ibd
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/yyn.ibd
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/yyy.ibd
+--remove_file $MYSQL_TMP_DIR/new_dir/test/nyw.ibd
+--remove_file $MYSQL_TMP_DIR/new_dir/test/nyy.ibd
+--remove_file $MYSQL_TMP_DIR/new_dir/test/www.ibd
+--remove_file $MYSQL_TMP_DIR/new_dir/test/ynw.ibd
+--remove_file $MYSQL_TMP_DIR/new_dir/test/yny.ibd
+--remove_file $MYSQL_TMP_DIR/new_dir/test/yyy.ibd
+
+
+--echo #
+--echo # Create some tables again and this time, crash instead of shutdown
+--echo # InnoDB recovery does not have the ability at this time to query
+--echo # the data dictionary in order to determine if the table it is
+--echo # openeing is the correct one, or to finde the previous location
+--echo # of a tablespace from SYS_DATAFILES.  It must rely on the ISL file
+--echo # to tell the truth. But it can compare the current linked location 
+--echo # with a tablespace found in the default location and use the most
+--echo # recent.
+--echo #
+--echo # Test recovery when tablespaces can be found at multiple places.
+--echo # SYS_DATAFILES is unavailable during recovery.
+--echo # Link File will refer to the file at alt_dir.
+--echo # In each case except the control tablespace, the Link file will
+--echo # exist with a file name in alt_dir.  'aa' is the control table and
+--echo # is alphabetically before the other tablespace names so it will be
+--echo # recovered first by InnoDB.  It is the source of the 'wrong' tablespaces.
+--echo # Tablename   Default_Tablespace  Remote_Tablespace
+--echo #    aa              Yes                 No
+--echo #    yy              Yes                 Yes  (both the same file)
+--echo #    yn              Yes                 No
+--echo #    ny              Yes                 Yes
+--echo #    yw              Yes                 Wrong
+--echo #    wy              Wrong               Yes
+--echo #
+
+# Combinations that would cause no tablespace to be opened for recovery will
+# cause recovery to stop will a log file message.
+# These include nn, ww, nw, wn and are not tested here.
+
+set global innodb_file_per_table=on;
+set global innodb_file_format='Barracuda';
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE aa (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO aa VALUES (1, 'aa - control table');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE yy (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO yy VALUES (1, 'yy');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE yn (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO yn VALUES (1, 'yn');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE ny (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO ny VALUES (1, 'ny');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE yw (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO yw VALUES (1, 'yw');
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE wy (c1 INT KEY, c2 TEXT) ENGINE=InnoDB  $data_directory;
+INSERT INTO wy VALUES (1, 'wy');
+
+--echo #
+--echo # Crash the server, copy and remove files.
+--echo #
+BEGIN;
+INSERT INTO aa VALUES (2, 'aa');
+INSERT INTO yy VALUES (2, 'yy');
+INSERT INTO yn VALUES (2, 'yn');
+INSERT INTO ny VALUES (2, 'ny');
+INSERT INTO yw VALUES (2, 'yw');
+INSERT INTO wy VALUES (2, 'wy');
+
+# Request a crash on next execution of commit.
+SET SESSION debug="+d,crash_commit_before";
+--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+
+# Execute the statement that causes the crash.
+--error 2013
+COMMIT;
+
+--echo # YY; Found in both default directory and alt-dir.
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yy.ibd $MYSQLD_DATADIR/test/yy.ibd
+
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQLD_DATADIR/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 let it do recovery.
+--echo # It will crash when it finds two copies of yy.
+--echo #
+--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+
+--echo # Delete the extra copy of yy.
+--remove_file $MYSQLD_DATADIR/test/yy.ibd
+
+--echo # AA; Control table, used as the source of 'wrong' tablespaces and opened first during recovery.
+
+--echo # YN; Tablespace found the default directory but not in alt_dir 
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yn.ibd $MYSQLD_DATADIR/test/yn.ibd 
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/yn.ibd
+
+--echo # NY; Tablespace found in alt_dir but not the default directory.
+
+--echo # YW; Tablespace found in the default directory but the wrong file in alt_dir
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/yw.ibd $MYSQLD_DATADIR/test/yw.ibd 
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/yw.ibd
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/aa.ibd $MYSQL_TMP_DIR/alt_dir/test/yw.ibd 
+
+--echo # WY; The wrong tablespace found in the default directory and the corect one in alt_dir 
+--copy_file $MYSQL_TMP_DIR/alt_dir/test/aa.ibd $MYSQLD_DATADIR/test/wy.ibd 
+
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQLD_DATADIR/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 let it do recovery.
+--echo #
+--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+--disable_reconnect
+
+SHOW VARIABLES LIKE 'innodb_strict_mode';
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles ORDER BY path;
+
+SELECT * FROM aa;
+SELECT * FROM yy;
+SELECT * FROM yn;
+SELECT * FROM ny;
+SELECT * FROM yw;
+SELECT * FROM wy;
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE aa;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE yy;
+SHOW CREATE TABLE yn;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE ny;
+SHOW CREATE TABLE yw;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE wy;
+
+--echo #
+--echo # Cleanup after recovery
+--echo #
+
+DROP TABLE aa;
+DROP TABLE yy;
+DROP TABLE yn;
+DROP TABLE ny;
+DROP TABLE yw;
+DROP TABLE wy;
+
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQL_TMP_DIR/alt_dir/test
+
+--remove_file $MYSQLD_DATADIR/test/wy.ibd
+--remove_file $MYSQL_TMP_DIR/alt_dir/test/yw.ibd
+
+--echo #
+--echo # Cleanup
+--echo #
+
+--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;
+eval set global innodb_file_per_table=$innodb_file_per_table;
+

=== added file 'mysql-test/suite/innodb/t/innodb-tablespace.test'
--- a/mysql-test/suite/innodb/t/innodb-tablespace.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/t/innodb-tablespace.test	revid:kevin.lewis@stripped
@@ -0,0 +1,451 @@
+#
+# A series of tests to show the correct tablespace behavior.
+# See also; parts.partition_basic_symlink_innodb.test for
+# partition related tests with remote tablespaces.
+# See innodb.innodb-restart for tablespace migration tests.
+#
+--source include/have_innodb.inc
+--source include/have_partition.inc
+SET default_storage_engine=InnoDB;
+
+--echo #
+--echo # TABLESPACE related tests.
+--echo #
+
+# Set up some variables
+LET $MYSQLD_DATADIR = `select @@datadir`;
+LET $data_directory_clause = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
+LET $index_directory_clause = INDEX DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
+
+# These values can change during the test
+LET $innodb_file_format_orig=`select @@innodb_file_format`;
+LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
+LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`;
+
+--echo #
+--echo # CREATE TABLE ... DATA DIRECTORY
+--echo # Innodb does not support INDEX DIRECTORY.
+--echo #
+SET SESSION innodb_strict_mode = ON;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+--error ER_CANT_CREATE_TABLE
+eval CREATE TABLE t1 (a int KEY, b text) $index_directory_clause;
+SHOW WARNINGS;
+
+--echo #
+--echo # Without strict mode, INDEX DIRECTORY is just ignored
+--echo #
+SET SESSION innodb_strict_mode = OFF;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE t1 (a int KEY, b text) $index_directory_clause;
+SHOW WARNINGS;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON.
+--echo #
+SET SESSION innodb_strict_mode = ON;
+SET GLOBAL innodb_file_per_table=OFF;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+--error ER_CANT_CREATE_TABLE
+eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
+SHOW WARNINGS;
+
+--echo #
+--echo # Without strict mode, DATA DIRECTORY without innodb_file_per_table=ON is just ignored.
+--echo #
+SET SESSION innodb_strict_mode = OFF;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
+SHOW WARNINGS;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--echo # Now set innodb_file_per_table so that DATA DIRECTORY can be tested.
+SET GLOBAL innodb_file_per_table=ON;
+
+--echo #
+--echo # Create the tablespace in MYSQL_TMP_DIR/alt_dir
+--echo # InnoDB will create the sub-directories if needed.
+--echo #
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
+SHOW WARNINGS;
+INSERT INTO t1 VALUES (1, "Create the tablespace");
+SELECT * FROM t1;
+
+--echo #
+--echo # Check if link file exists  in MYSQLD_DATADIR
+--echo #
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+--echo # Check if tablespace file exists where we specified in DATA DIRECTORY
+--echo ---- MYSQL_TMP_DIR/alt_dir/test
+--list_files $MYSQL_TMP_DIR/alt_dir/test
+
+--echo #
+--echo # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
+--echo #
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE t1;
+
+--echo # Show that the new system tables have this table in them correctly
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_regex /emp#P#/emp#p#/
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+
+--echo #
+--echo # Show that the system tables are updated on drop table
+--echo #
+DROP TABLE t1;
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+SELECT path FROM information_schema.innodb_sys_datafiles;
+
+--echo #
+--echo # Create the same table a second time in the same place
+--echo #
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
+
+INSERT INTO t1 VALUES (2, "Create the same table a second time in the same place");
+SELECT * FROM t1;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE t1;
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir/test
+--list_files $MYSQL_TMP_DIR/alt_dir/test
+
+--echo #
+--echo # Truncate the table, then insert and verify
+--echo #
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (3, "Truncate the table, then insert");
+SELECT * FROM t1;
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir/test
+--list_files $MYSQL_TMP_DIR/alt_dir/test
+
+--echo #
+--echo # Rename the table, then insert and verify
+--echo #
+RENAME TABLE t1 TO t2;
+INSERT INTO t2 VALUES (4, "Rename the table, then insert");
+SELECT * FROM t2;
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir/test
+--list_files $MYSQL_TMP_DIR/alt_dir/test
+
+--echo #
+--echo # CREATE TABLE LIKE does not retain DATA DIRECTORY automatically.
+--echo #
+CREATE TABLE t3 LIKE t2;
+INSERT INTO t3 VALUES (5, "CREATE TABLE LIKE");
+SELECT * FROM t3;
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_result ./ MYSQLD_DATADIR/ $MYSQLD_DATADIR MYSQLD_DATADIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+
+--echo #
+--echo # Now make sure the tables can be fully dropped.
+--echo #
+DROP TABLE t2, t3;
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+SELECT path FROM information_schema.innodb_sys_datafiles;
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir/test
+--list_files $MYSQL_TMP_DIR/alt_dir/test
+
+--echo #
+--echo # Be sure SQL MODE "NO_DIR_IN_CREATE" prevents the use of DATA DIRECTORY
+--echo #
+SET @org_mode=@@sql_mode;
+SET @@sql_mode='NO_DIR_IN_CREATE';
+SELECT @@sql_mode;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE t1 (a int, b text) $data_directory_clause;
+SHOW WARNINGS;
+INSERT INTO t1 VALUES (6, "SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY");
+# Checking if tablespace exists in --datadir since DATA DIRECTORY was ignored.
+--file_exists $MYSQLD_DATADIR/test/t1.ibd
+DROP TABLE t1;
+set @@sql_mode=@org_mode;
+
+--echo #
+--echo # MySQL engine does not allow DATA DIRECTORY to be
+--echo # within --datadir for any engine, including InnoDB
+--echo #
+--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
+--error ER_WRONG_ARGUMENTS
+eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY '$MYSQLD_DATADIR/test';
+
+--echo # TEMPORARY tables are incompatible with DATA DIRECTORY
+SET SESSION innodb_strict_mode = ON;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+--error ER_CANT_CREATE_TABLE
+eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
+SHOW WARNINGS;
+SET SESSION innodb_strict_mode = OFF;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
+SHOW WARNINGS;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir/test
+--list_files $MYSQL_TMP_DIR/alt_dir/test
+
+--echo #
+--echo # Create the remote table via static DDL statements in a stored procedure
+--echo #
+DELIMITER |;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) $data_directory_clause; END |
+DELIMITER ;|
+CALL static_proc;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+INSERT INTO t1 VALUES (7, "Create the remote table via static DDL statements");
+SELECT * FROM t1;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE t1;
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir/test
+--list_files $MYSQL_TMP_DIR/alt_dir/test
+DROP PROCEDURE static_proc;
+DROP TABLE t1;
+
+--echo #
+--echo # Create the remote table via dynamic DDL statements in a stored procedure
+--echo #
+DELIMITER |;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE PROCEDURE dynamic_proc() BEGIN PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text) $data_directory_clause"; EXECUTE stmt1; END |
+DELIMITER ;|
+CALL dynamic_proc;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+INSERT INTO t1 VALUES (8, "Create the remote table via dynamic DDL statements");
+SELECT * FROM t1;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE t1;
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir/test
+--list_files $MYSQL_TMP_DIR/alt_dir/test
+DROP PROCEDURE dynamic_proc;
+DROP TABLE t1;
+
+--echo #
+--echo # CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY
+--echo #
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE emp (
+    id INT NOT NULL,
+    store_name VARCHAR(30),
+    parts VARCHAR(30),
+    store_id INT
+)
+PARTITION BY LIST(store_id) (
+  PARTITION east VALUES IN (10,20,30)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east',
+  PARTITION north VALUES IN (40,50,60)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north',
+  PARTITION west VALUES IN (70,80,100)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
+);
+
+INSERT INTO emp values(1,'Oracle','NUTT',10);
+INSERT INTO emp values(2,'HUAWEI','BOLT',40);
+INSERT INTO emp values(3,'IBM','NAIL',70);
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE emp;
+# InnoDB always converts table names to lower case on Windows
+--replace_regex /emp#P#/emp#p#/
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables
+       WHERE name LIKE 'test%'
+       ORDER BY name;
+--replace_regex /emp#P#/emp#p#/
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces
+       ORDER BY name;
+--replace_regex /emp#P#/emp#p#/
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles
+       ORDER BY path;
+SELECT * FROM emp;
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_east/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_north/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_west/test
+
+--echo #
+--echo # DROP one PARTITION.
+--echo #
+ALTER TABLE emp DROP PARTITION west;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE emp;
+--replace_regex /emp#P#/emp#p#/
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables
+       WHERE name LIKE 'test%'
+       ORDER BY name;
+--replace_regex /emp#P#/emp#p#/
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces
+       ORDER BY name;
+--replace_regex /emp#P#/emp#p#/
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles
+       ORDER BY path;
+SELECT * FROM emp;
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_east/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_north/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_west/test
+
+--echo #
+--echo # ADD the PARTITION back.
+--echo #
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval ALTER TABLE emp ADD PARTITION (
+  PARTITION west VALUES IN (70,80,100)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west');
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE emp;
+--replace_regex /emp#P#/emp#p#/
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables
+       WHERE name LIKE 'test%'
+       ORDER BY name;
+--replace_regex /emp#P#/emp#p#/
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces
+       ORDER BY name;
+--replace_regex /emp#P#/emp#p#/
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles
+       ORDER BY path;
+INSERT INTO emp VALUES(3,'IBM','NAIL',70);
+SELECT * FROM emp;
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_east/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_north/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_west/test
+
+--echo #
+--echo # TRUNCATE one PARTITION.
+--echo #
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+ALTER TABLE emp TRUNCATE PARTITION west;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE emp;
+--replace_regex /emp#P#/emp#p#/
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables
+       WHERE name LIKE 'test%'
+       ORDER BY name;
+--replace_regex /emp#P#/emp#p#/
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces
+       ORDER BY name;
+--replace_regex /emp#P#/emp#p#/
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles
+       ORDER BY path;
+SELECT * FROM emp;
+INSERT INTO emp VALUES(3,'IBM','NAIL',70);
+SELECT * FROM emp;
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_east/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_north/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
+--replace_regex /emp#P#/emp#p#/
+--list_files $MYSQL_TMP_DIR/alt_dir_west/test
+
+DROP TABLE emp;
+
+--echo #
+--echo # Cleanup
+--echo #
+
+--rmdir $MYSQL_TMP_DIR/alt_dir/test
+--rmdir $MYSQL_TMP_DIR/alt_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;
+EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig;
+--enable_query_log
+

=== added file 'mysql-test/suite/parts/r/partition_reorganize_innodb.result'
--- a/mysql-test/suite/parts/r/partition_reorganize_innodb.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_reorganize_innodb.result	revid:kevin.lewis@stripped
@@ -0,0 +1,199 @@
+SET default_storage_engine=InnoDB;
+SET SESSION innodb_strict_mode = ON;
+SET GLOBAL innodb_file_per_table=ON;
+SET GLOBAL innodb_file_format=barracuda;
+#
+# CREATE a table with SUBPARTITIONS
+#
+CREATE TABLE emp (
+id INT NOT NULL,
+store_name VARCHAR(30),
+parts VARCHAR(30),
+store_id INT
+) engine InnoDB
+PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id)
+(
+PARTITION northeast VALUES LESS THAN (50)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast'
+    (SUBPARTITION ne0, SUBPARTITION ne1),
+PARTITION southwest VALUES LESS THAN (100)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest'
+    (SUBPARTITION sw2, SUBPARTITION sw3)
+);
+INSERT INTO emp values(1,'Oracle','NUT',10);
+INSERT INTO emp values(2,'SAP','BOLT',40);
+INSERT INTO emp values(3,'IBM','NAIL',60);
+INSERT INTO emp values(4,'SUN','SCREW',90);
+SELECT * FROM emp;
+id	store_name	parts	store_id
+1	Oracle	NUT	10
+2	SAP	BOLT	40
+3	IBM	NAIL	60
+4	SUN	SCREW	90
+SHOW CREATE TABLE emp;
+Table	Create Table
+emp	CREATE TABLE `emp` (
+  `id` int(11) NOT NULL,
+  `store_name` varchar(30) DEFAULT NULL,
+  `parts` varchar(30) DEFAULT NULL,
+  `store_id` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (store_id)
+SUBPARTITION BY HASH (store_id)
+(PARTITION northeast VALUES LESS THAN (50)
+ (SUBPARTITION ne0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' ENGINE = InnoDB,
+  SUBPARTITION ne1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' ENGINE = InnoDB),
+ PARTITION southwest VALUES LESS THAN (100)
+ (SUBPARTITION sw2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' ENGINE = InnoDB,
+  SUBPARTITION sw3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' ENGINE = InnoDB)) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables
+WHERE name LIKE 'test%'
+       ORDER BY name;
+name	n_cols	file_format	row_format
+test/emp#p#northeast#sp#ne0	7	Antelope	Compact
+test/emp#p#northeast#sp#ne1	7	Antelope	Compact
+test/emp#p#southwest#sp#sw2	7	Antelope	Compact
+test/emp#p#southwest#sp#sw3	7	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces
+ORDER BY name;
+name	file_format	row_format
+test/emp#p#northeast#sp#ne0	Antelope	Compact or Redundant
+test/emp#p#northeast#sp#ne1	Antelope	Compact or Redundant
+test/emp#p#southwest#sp#sw2	Antelope	Compact or Redundant
+test/emp#p#southwest#sp#sw3	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles
+ORDER BY path;
+path
+MYSQL_TMP_DIR/alt_dir_northeast/test/emp#p#northeast#sp#ne0.ibd
+MYSQL_TMP_DIR/alt_dir_northeast/test/emp#p#northeast#sp#ne1.ibd
+MYSQL_TMP_DIR/alt_dir_southwest/test/emp#p#southwest#sp#sw2.ibd
+MYSQL_TMP_DIR/alt_dir_southwest/test/emp#p#southwest#sp#sw3.ibd
+---- MYSQLD_DATADIR/test
+emp#p#northeast#sp#ne0.isl
+emp#p#northeast#sp#ne1.isl
+emp#p#southwest#sp#sw2.isl
+emp#p#southwest#sp#sw3.isl
+emp.frm
+emp.par
+---- MYSQL_TMP_DIR/alt_dir_northeast/test
+emp#p#northeast#sp#ne0.ibd
+emp#p#northeast#sp#ne1.ibd
+---- MYSQL_TMP_DIR/alt_dir_southwest/test
+emp#p#southwest#sp#sw2.ibd
+emp#p#southwest#sp#sw3.ibd
+#
+# REORGANIZE the PARTITIONS and SUBPARTITIONS
+#
+ALTER TABLE emp REORGANIZE PARTITION northeast INTO
+(
+PARTITION east VALUES LESS THAN (25)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east'
+    (SUBPARTITION e0, SUBPARTITION e1),
+PARTITION north VALUES LESS THAN (50)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north'
+    (SUBPARTITION n0, SUBPARTITION n1)
+);
+ALTER TABLE emp REORGANIZE PARTITION southwest INTO
+(
+PARTITION west VALUES LESS THAN (75)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'
+    (SUBPARTITION w0, SUBPARTITION w1),
+PARTITION south VALUES LESS THAN (100)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south'
+    (SUBPARTITION s0, SUBPARTITION s1)
+);
+SELECT * FROM emp;
+id	store_name	parts	store_id
+1	Oracle	NUT	10
+2	SAP	BOLT	40
+3	IBM	NAIL	60
+4	SUN	SCREW	90
+SHOW CREATE TABLE emp;
+Table	Create Table
+emp	CREATE TABLE `emp` (
+  `id` int(11) NOT NULL,
+  `store_name` varchar(30) DEFAULT NULL,
+  `parts` varchar(30) DEFAULT NULL,
+  `store_id` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (store_id)
+SUBPARTITION BY HASH (store_id)
+(PARTITION east VALUES LESS THAN (25)
+ (SUBPARTITION e0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB,
+  SUBPARTITION e1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB),
+ PARTITION north VALUES LESS THAN (50)
+ (SUBPARTITION n0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB,
+  SUBPARTITION n1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB),
+ PARTITION west VALUES LESS THAN (75)
+ (SUBPARTITION w0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB,
+  SUBPARTITION w1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB),
+ PARTITION south VALUES LESS THAN (100)
+ (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = InnoDB,
+  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = InnoDB)) */
+SELECT name,n_cols,file_format,row_format
+FROM information_schema.innodb_sys_tables
+WHERE name LIKE 'test%'
+       ORDER BY name;
+name	n_cols	file_format	row_format
+test/emp#p#east#sp#e0	7	Antelope	Compact
+test/emp#p#east#sp#e1	7	Antelope	Compact
+test/emp#p#north#sp#n0	7	Antelope	Compact
+test/emp#p#north#sp#n1	7	Antelope	Compact
+test/emp#p#south#sp#s0	7	Antelope	Compact
+test/emp#p#south#sp#s1	7	Antelope	Compact
+test/emp#p#west#sp#w0	7	Antelope	Compact
+test/emp#p#west#sp#w1	7	Antelope	Compact
+SELECT name,file_format,row_format
+FROM information_schema.innodb_sys_tablespaces
+ORDER BY name;
+name	file_format	row_format
+test/emp#p#east#sp#e0	Antelope	Compact or Redundant
+test/emp#p#east#sp#e1	Antelope	Compact or Redundant
+test/emp#p#north#sp#n0	Antelope	Compact or Redundant
+test/emp#p#north#sp#n1	Antelope	Compact or Redundant
+test/emp#p#south#sp#s0	Antelope	Compact or Redundant
+test/emp#p#south#sp#s1	Antelope	Compact or Redundant
+test/emp#p#west#sp#w0	Antelope	Compact or Redundant
+test/emp#p#west#sp#w1	Antelope	Compact or Redundant
+SELECT path FROM information_schema.innodb_sys_datafiles
+ORDER BY path;
+path
+MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east#sp#e0.ibd
+MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east#sp#e1.ibd
+MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north#sp#n0.ibd
+MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north#sp#n1.ibd
+MYSQL_TMP_DIR/alt_dir_south/test/emp#p#south#sp#s0.ibd
+MYSQL_TMP_DIR/alt_dir_south/test/emp#p#south#sp#s1.ibd
+MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west#sp#w0.ibd
+MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west#sp#w1.ibd
+---- MYSQLD_DATADIR/test
+emp#p#east#sp#e0.isl
+emp#p#east#sp#e1.isl
+emp#p#north#sp#n0.isl
+emp#p#north#sp#n1.isl
+emp#p#south#sp#s0.isl
+emp#p#south#sp#s1.isl
+emp#p#west#sp#w0.isl
+emp#p#west#sp#w1.isl
+emp.frm
+emp.par
+---- MYSQL_TMP_DIR/alt_dir_northeast/test
+---- MYSQL_TMP_DIR/alt_dir_southwest/test
+---- MYSQL_TMP_DIR/alt_dir_east/test
+emp#p#east#sp#e0.ibd
+emp#p#east#sp#e1.ibd
+---- MYSQL_TMP_DIR/alt_dir_north/test
+emp#p#north#sp#n0.ibd
+emp#p#north#sp#n1.ibd
+---- MYSQL_TMP_DIR/alt_dir_west/test
+emp#p#west#sp#w0.ibd
+emp#p#west#sp#w1.ibd
+---- MYSQL_TMP_DIR/alt_dir_south/test
+emp#p#south#sp#s0.ibd
+emp#p#south#sp#s1.ibd
+DROP TABLE emp;
+#
+# Cleanup
+#

=== added file 'mysql-test/suite/parts/r/partition_reorganize_myisam.result'
--- a/mysql-test/suite/parts/r/partition_reorganize_myisam.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_reorganize_myisam.result	revid:kevin.lewis@stripped
@@ -0,0 +1,166 @@
+SET default_storage_engine=MyISAM;
+#
+# CREATE a table with SUBPARTITIONS
+#
+CREATE TABLE emp (
+id INT NOT NULL,
+store_name VARCHAR(30),
+parts VARCHAR(30),
+store_id INT
+) engine MyISAM
+PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id)
+(
+PARTITION northeast VALUES LESS THAN (50)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast'
+    INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast'
+    (SUBPARTITION ne0, SUBPARTITION ne1),
+PARTITION southwest VALUES LESS THAN (100)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest'
+    INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest'
+    (SUBPARTITION sw2, SUBPARTITION sw3)
+);
+INSERT INTO emp values(1,'Oracle','NUT',10);
+INSERT INTO emp values(2,'SAP','BOLT',40);
+INSERT INTO emp values(3,'IBM','NAIL',60);
+INSERT INTO emp values(4,'SUN','SCREW',90);
+SELECT * FROM emp;
+id	store_name	parts	store_id
+1	Oracle	NUT	10
+2	SAP	BOLT	40
+3	IBM	NAIL	60
+4	SUN	SCREW	90
+SHOW CREATE TABLE emp;
+Table	Create Table
+emp	CREATE TABLE `emp` (
+  `id` int(11) NOT NULL,
+  `store_name` varchar(30) DEFAULT NULL,
+  `parts` varchar(30) DEFAULT NULL,
+  `store_id` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (store_id)
+SUBPARTITION BY HASH (store_id)
+(PARTITION northeast VALUES LESS THAN (50)
+ (SUBPARTITION ne0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' ENGINE = MyISAM,
+  SUBPARTITION ne1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' ENGINE = MyISAM),
+ PARTITION southwest VALUES LESS THAN (100)
+ (SUBPARTITION sw2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' ENGINE = MyISAM,
+  SUBPARTITION sw3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' ENGINE = MyISAM)) */
+---- MYSQLD_DATADIR/test
+emp#P#northeast#SP#ne0.MYD
+emp#P#northeast#SP#ne0.MYI
+emp#P#northeast#SP#ne1.MYD
+emp#P#northeast#SP#ne1.MYI
+emp#P#southwest#SP#sw2.MYD
+emp#P#southwest#SP#sw2.MYI
+emp#P#southwest#SP#sw3.MYD
+emp#P#southwest#SP#sw3.MYI
+emp.frm
+emp.par
+---- MYSQL_TMP_DIR/alt_dir_northeast
+emp#P#northeast#SP#ne0.MYD
+emp#P#northeast#SP#ne0.MYI
+emp#P#northeast#SP#ne1.MYD
+emp#P#northeast#SP#ne1.MYI
+---- MYSQL_TMP_DIR/alt_dir_southwest
+emp#P#southwest#SP#sw2.MYD
+emp#P#southwest#SP#sw2.MYI
+emp#P#southwest#SP#sw3.MYD
+emp#P#southwest#SP#sw3.MYI
+#
+# REORGANIZE the PARTITIONS and SUBPARTITIONS
+#
+ALTER TABLE emp REORGANIZE PARTITION northeast INTO
+(
+PARTITION east VALUES LESS THAN (25)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east'
+    INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east'
+    (SUBPARTITION e0, SUBPARTITION e1),
+PARTITION north VALUES LESS THAN (50)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north'
+    INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north'
+    (SUBPARTITION n0, SUBPARTITION n1)
+);
+ALTER TABLE emp REORGANIZE PARTITION southwest INTO
+(
+PARTITION west VALUES LESS THAN (75)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'
+    INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'
+    (SUBPARTITION w0, SUBPARTITION w1),
+PARTITION south VALUES LESS THAN (100)
+DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south'
+    INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south'
+    (SUBPARTITION s0, SUBPARTITION s1)
+);
+SELECT * FROM emp;
+id	store_name	parts	store_id
+1	Oracle	NUT	10
+2	SAP	BOLT	40
+3	IBM	NAIL	60
+4	SUN	SCREW	90
+SHOW CREATE TABLE emp;
+Table	Create Table
+emp	CREATE TABLE `emp` (
+  `id` int(11) NOT NULL,
+  `store_name` varchar(30) DEFAULT NULL,
+  `parts` varchar(30) DEFAULT NULL,
+  `store_id` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (store_id)
+SUBPARTITION BY HASH (store_id)
+(PARTITION east VALUES LESS THAN (25)
+ (SUBPARTITION e0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = MyISAM,
+  SUBPARTITION e1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = MyISAM),
+ PARTITION north VALUES LESS THAN (50)
+ (SUBPARTITION n0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = MyISAM,
+  SUBPARTITION n1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = MyISAM),
+ PARTITION west VALUES LESS THAN (75)
+ (SUBPARTITION w0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = MyISAM,
+  SUBPARTITION w1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = MyISAM),
+ PARTITION south VALUES LESS THAN (100)
+ (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = MyISAM,
+  SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = MyISAM)) */
+---- MYSQLD_DATADIR/test
+emp#P#east#SP#e0.MYD
+emp#P#east#SP#e0.MYI
+emp#P#east#SP#e1.MYD
+emp#P#east#SP#e1.MYI
+emp#P#north#SP#n0.MYD
+emp#P#north#SP#n0.MYI
+emp#P#north#SP#n1.MYD
+emp#P#north#SP#n1.MYI
+emp#P#south#SP#s0.MYD
+emp#P#south#SP#s0.MYI
+emp#P#south#SP#s1.MYD
+emp#P#south#SP#s1.MYI
+emp#P#west#SP#w0.MYD
+emp#P#west#SP#w0.MYI
+emp#P#west#SP#w1.MYD
+emp#P#west#SP#w1.MYI
+emp.frm
+emp.par
+---- MYSQL_TMP_DIR/alt_dir_northeast
+---- MYSQL_TMP_DIR/alt_dir_southwest
+---- MYSQL_TMP_DIR/alt_dir_east
+emp#P#east#SP#e0.MYD
+emp#P#east#SP#e0.MYI
+emp#P#east#SP#e1.MYD
+emp#P#east#SP#e1.MYI
+---- MYSQL_TMP_DIR/alt_dir_north
+emp#P#north#SP#n0.MYD
+emp#P#north#SP#n0.MYI
+emp#P#north#SP#n1.MYD
+emp#P#north#SP#n1.MYI
+---- MYSQL_TMP_DIR/alt_dir_west
+emp#P#west#SP#w0.MYD
+emp#P#west#SP#w0.MYI
+emp#P#west#SP#w1.MYD
+emp#P#west#SP#w1.MYI
+---- MYSQL_TMP_DIR/alt_dir_south
+emp#P#south#SP#s0.MYD
+emp#P#south#SP#s0.MYI
+emp#P#south#SP#s1.MYD
+emp#P#south#SP#s1.MYI
+DROP TABLE emp;
+#
+# Cleanup
+#

=== added file 'mysql-test/suite/parts/t/partition_reorganize_innodb.test'
--- a/mysql-test/suite/parts/t/partition_reorganize_innodb.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_reorganize_innodb.test	revid:kevin.lewis@stripped
@@ -0,0 +1,162 @@
+#
+# A series of tests to show the correct behavior when using
+# ALTER TABLE ... REORGANIZE PARTITION with InnoDB
+# See also; parts.partition_basic_symlink_innodb.test for
+# partition related tests with remote tablespaces.
+# See innodb.innodb-restart for tablespace migration tests.
+#
+--source include/have_innodb.inc
+--source include/have_partition.inc
+SET default_storage_engine=InnoDB;
+LET $MYSQLD_DATADIR = `select @@datadir`;
+
+# These values can change during the test
+LET $innodb_file_format_orig=`select @@innodb_file_format`;
+LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
+LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`;
+
+SET SESSION innodb_strict_mode = ON;
+SET GLOBAL innodb_file_per_table=ON;
+SET GLOBAL innodb_file_format=barracuda;
+
+# Unlike MyISAM, InnoDB creates the subdirectories given to it in the
+# DATA DIRECTORY clauses.  Another difference is that InnoDB uses an extra
+# directory under DATA DIRECTORY with the name of the database.
+--echo #
+--echo # CREATE a table with SUBPARTITIONS
+--echo #
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE emp (
+    id INT NOT NULL,
+    store_name VARCHAR(30),
+    parts VARCHAR(30),
+    store_id INT
+) engine InnoDB
+PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id)
+(
+  PARTITION northeast VALUES LESS THAN (50)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_northeast'
+    (SUBPARTITION ne0, SUBPARTITION ne1),
+  PARTITION southwest VALUES LESS THAN (100)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_southwest'
+    (SUBPARTITION sw2, SUBPARTITION sw3)
+);
+INSERT INTO emp values(1,'Oracle','NUT',10);
+INSERT INTO emp values(2,'SAP','BOLT',40);
+INSERT INTO emp values(3,'IBM','NAIL',60);
+INSERT INTO emp values(4,'SUN','SCREW',90);
+SELECT * FROM emp;
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE emp;
+--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
+       ORDER BY name;
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles
+       ORDER BY path;
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_northeast/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQL_TMP_DIR/alt_dir_northeast/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_southwest/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQL_TMP_DIR/alt_dir_southwest/test
+
+
+--echo #
+--echo # REORGANIZE the PARTITIONS and SUBPARTITIONS
+--echo #
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval ALTER TABLE emp REORGANIZE PARTITION northeast INTO
+(
+  PARTITION east VALUES LESS THAN (25)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east'
+    (SUBPARTITION e0, SUBPARTITION e1),
+  PARTITION north VALUES LESS THAN (50)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north'
+    (SUBPARTITION n0, SUBPARTITION n1)
+);
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval ALTER TABLE emp REORGANIZE PARTITION southwest INTO
+(
+  PARTITION west VALUES LESS THAN (75)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
+    (SUBPARTITION w0, SUBPARTITION w1),
+  PARTITION south VALUES LESS THAN (100)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_south'
+    (SUBPARTITION s0, SUBPARTITION s1)
+);
+SELECT * FROM emp;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE emp;
+--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
+       ORDER BY name;
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles
+       ORDER BY path;
+--echo ---- MYSQLD_DATADIR/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_northeast/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQL_TMP_DIR/alt_dir_northeast/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_southwest/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQL_TMP_DIR/alt_dir_southwest/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_east/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQL_TMP_DIR/alt_dir_east/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_north/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQL_TMP_DIR/alt_dir_north/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_west/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQL_TMP_DIR/alt_dir_west/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_south/test
+--replace_regex  /#P#/#p#/  /#SP#/#sp#/
+--list_files $MYSQL_TMP_DIR/alt_dir_south/test
+
+DROP TABLE emp;
+
+--echo #
+--echo # Cleanup
+--echo #
+
+--rmdir $MYSQL_TMP_DIR/alt_dir_northeast/test
+--rmdir $MYSQL_TMP_DIR/alt_dir_northeast
+--rmdir $MYSQL_TMP_DIR/alt_dir_southwest/test
+--rmdir $MYSQL_TMP_DIR/alt_dir_southwest
+--rmdir $MYSQL_TMP_DIR/alt_dir_east/test
+--rmdir $MYSQL_TMP_DIR/alt_dir_east
+--rmdir $MYSQL_TMP_DIR/alt_dir_north/test
+--rmdir $MYSQL_TMP_DIR/alt_dir_north
+--rmdir $MYSQL_TMP_DIR/alt_dir_west/test
+--rmdir $MYSQL_TMP_DIR/alt_dir_west
+--rmdir $MYSQL_TMP_DIR/alt_dir_south/test
+--rmdir $MYSQL_TMP_DIR/alt_dir_south
+
+--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;
+EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig;
+--enable_query_log
+

=== added file 'mysql-test/suite/parts/t/partition_reorganize_myisam.test'
--- a/mysql-test/suite/parts/t/partition_reorganize_myisam.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_reorganize_myisam.test	revid:kevin.lewis@stripped
@@ -0,0 +1,115 @@
+#
+# A series of tests to show the correct behavior when using
+# ALTER TABLE ... REORGANIZE PARTITION with InnoDB
+#
+--source include/have_partition.inc
+--source include/have_symlink.inc
+--source include/not_windows.inc
+
+SET default_storage_engine=MyISAM;
+LET $MYSQLD_DATADIR = `select @@datadir`;
+
+--mkdir $MYSQL_TMP_DIR/alt_dir_northeast
+--mkdir $MYSQL_TMP_DIR/alt_dir_southwest
+
+--echo #
+--echo # CREATE a table with SUBPARTITIONS
+--echo #
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE emp (
+    id INT NOT NULL,
+    store_name VARCHAR(30),
+    parts VARCHAR(30),
+    store_id INT
+) engine MyISAM
+PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id)
+(
+  PARTITION northeast VALUES LESS THAN (50)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_northeast'
+    INDEX DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_northeast'
+    (SUBPARTITION ne0, SUBPARTITION ne1),
+  PARTITION southwest VALUES LESS THAN (100)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_southwest'
+    INDEX DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_southwest'
+    (SUBPARTITION sw2, SUBPARTITION sw3)
+);
+INSERT INTO emp values(1,'Oracle','NUT',10);
+INSERT INTO emp values(2,'SAP','BOLT',40);
+INSERT INTO emp values(3,'IBM','NAIL',60);
+INSERT INTO emp values(4,'SUN','SCREW',90);
+SELECT * FROM emp;
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE emp;
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_northeast
+--list_files $MYSQL_TMP_DIR/alt_dir_northeast
+--echo ---- MYSQL_TMP_DIR/alt_dir_southwest
+--list_files $MYSQL_TMP_DIR/alt_dir_southwest
+
+
+--echo #
+--echo # REORGANIZE the PARTITIONS and SUBPARTITIONS
+--echo #
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+--mkdir $MYSQL_TMP_DIR/alt_dir_east
+--mkdir $MYSQL_TMP_DIR/alt_dir_north
+eval ALTER TABLE emp REORGANIZE PARTITION northeast INTO
+(
+  PARTITION east VALUES LESS THAN (25)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east'
+    INDEX DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east'
+    (SUBPARTITION e0, SUBPARTITION e1),
+  PARTITION north VALUES LESS THAN (50)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north'
+    INDEX DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north'
+    (SUBPARTITION n0, SUBPARTITION n1)
+);
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+--mkdir $MYSQL_TMP_DIR/alt_dir_west
+--mkdir $MYSQL_TMP_DIR/alt_dir_south
+eval ALTER TABLE emp REORGANIZE PARTITION southwest INTO
+(
+  PARTITION west VALUES LESS THAN (75)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
+    INDEX DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
+    (SUBPARTITION w0, SUBPARTITION w1),
+  PARTITION south VALUES LESS THAN (100)
+    DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_south'
+    INDEX DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_south'
+    (SUBPARTITION s0, SUBPARTITION s1)
+);
+SELECT * FROM emp;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE emp;
+--echo ---- MYSQLD_DATADIR/test
+--list_files $MYSQLD_DATADIR/test
+--echo ---- MYSQL_TMP_DIR/alt_dir_northeast
+--list_files $MYSQL_TMP_DIR/alt_dir_northeast
+--echo ---- MYSQL_TMP_DIR/alt_dir_southwest
+--list_files $MYSQL_TMP_DIR/alt_dir_southwest
+--echo ---- MYSQL_TMP_DIR/alt_dir_east
+--list_files $MYSQL_TMP_DIR/alt_dir_east
+--echo ---- MYSQL_TMP_DIR/alt_dir_north
+--list_files $MYSQL_TMP_DIR/alt_dir_north
+--echo ---- MYSQL_TMP_DIR/alt_dir_west
+--list_files $MYSQL_TMP_DIR/alt_dir_west
+--echo ---- MYSQL_TMP_DIR/alt_dir_south
+--list_files $MYSQL_TMP_DIR/alt_dir_south
+
+DROP TABLE emp;
+
+--echo #
+--echo # Cleanup
+--echo #
+
+--rmdir $MYSQL_TMP_DIR/alt_dir_northeast
+--rmdir $MYSQL_TMP_DIR/alt_dir_southwest
+--rmdir $MYSQL_TMP_DIR/alt_dir_east
+--rmdir $MYSQL_TMP_DIR/alt_dir_north
+--rmdir $MYSQL_TMP_DIR/alt_dir_west
+--rmdir $MYSQL_TMP_DIR/alt_dir_south
+
+

=== added file 'mysql-test/t/partition_innodb_tablespace.test'
--- a/mysql-test/t/partition_innodb_tablespace.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/partition_innodb_tablespace.test	revid:kevin.lewis@stripped
@@ -0,0 +1,290 @@
+#
+# A series of tests to show the correct tablespace behavior when the
+# partition engine uses InnoDB.
+#
+--source include/not_embedded.inc
+--source include/have_partition.inc
+--source include/have_innodb.inc
+SET default_storage_engine=InnoDB;
+
+--echo #
+--echo # TABLESPACE related tests for the partition engine and InnoDB.
+--echo #
+
+# Set up some variables
+LET $MYSQLD_DATADIR = `select @@datadir`;
+LET $data_directory = DATA DIRECTORY='$MYSQL_TMP_DIR/alternate_dir/data';
+LET $data_directory2 = DATA DIRECTORY='$MYSQL_TMP_DIR/alternate_dir/data2';
+LET $index_directory = INDEX DIRECTORY='$MYSQL_TMP_DIR/alternate_dir/data';
+
+# These values can change during the test
+LET $innodb_file_format_orig=`select @@innodb_file_format`;
+LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
+LET $innodb_strict_mode_orig=`select @@session.innodb_strict_mode`;
+
+--echo # The partition engine can send DATA DIRECTORY to InnoDB.
+--echo # In strict mode, it is an error if innodb_file_per_table = OFF
+--echo # or INDEX DIRECTORY is used.
+SET SESSION innodb_strict_mode = ON;
+SET GLOBAL innodb_file_per_table = OFF;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+--error ER_CANT_CREATE_TABLE
+eval CREATE TABLE t1 (a int KEY, b text) ENGINE = InnoDB PARTITION BY HASH (a)
+     (PARTITION p0 engine=InnoDB $data_directory $index_directory,
+      PARTITION p1 engine=InnoDB $data_directory $index_directory);
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW WARNINGS;
+
+--echo # Try again with innodb_file_per_table = ON and no INDEX DIRECTORY.
+SET GLOBAL innodb_file_per_table = ON;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE t1 (a int KEY, b text) ENGINE = InnoDB PARTITION BY HASH (a)
+     (PARTITION p0 engine=InnoDB $data_directory,
+      PARTITION p1 engine=InnoDB $data_directory2);
+SHOW WARNINGS;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE t1;
+# replace_result 't1#P' with 't1#p' because on Linux, InnoDB is always
+# case sensitive with table names and on Windows, it is always lower case.
+# This is independent of the value of lower_case_table_names.  This cannot
+# be changes without breaking backward compatibility.
+--replace_result t1#P t1#p
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+--replace_result t1#P t1#p
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_result t1#P t1#p  $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+
+--echo # Verifying .frm, .par, .isl & .ibd files
+--file_exists $MYSQLD_DATADIR/test/t1.frm
+--file_exists $MYSQLD_DATADIR/test/t1.par
+--file_exists $MYSQLD_DATADIR/test/t1#P#p0.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p1.isl
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/test/t1#P#p0.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/test/t1#P#p1.ibd
+
+--echo # Verifying that there are no MyISAM files
+--error 1
+--file_exists $MYSQLD_DATADIR/test/t1#P#p0.myd
+--error 1
+--file_exists $MYSQLD_DATADIR/test/t1#P#p0.myi
+--error 1
+--file_exists $MYSQLD_DATADIR/test/t1#P#p1.myd
+--error 1
+--file_exists $MYSQLD_DATADIR/test/t1#P#p1.myi
+--error 1
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/t1#P#p0.myd
+--error 1
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/t1#P#p0.myi
+--error 1
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/t1#P#p1.myd
+--error 1
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/t1#P#p1.myi
+# The ibd tablespaces should not be directly under the DATA DIRECTORY
+--error 1
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/t1#P#p0.ibd
+--error 1
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/t1#P#p1.ibd
+
+--echo # Test TRUNCATE TABLE with partitioned InnoDB tables
+INSERT INTO t1 VALUES (1, "red");
+INSERT INTO t1 VALUES (2, "green");
+INSERT INTO t1 VALUES (3, "blue");
+SELECT * FROM t1;
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE t1;
+--replace_result t1#P t1#p
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+--replace_result t1#P t1#p
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_result t1#P t1#p  $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+--echo # Verifying .frm, .par and MyISAM files (.MYD, MYI)
+--file_exists $MYSQLD_DATADIR/test/t1.frm
+--file_exists $MYSQLD_DATADIR/test/t1.par
+--file_exists $MYSQLD_DATADIR/test/t1#P#p0.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p1.isl
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/test/t1#P#p0.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/test/t1#P#p1.ibd
+
+--echo # Test RENAME TABLE with partitioned InnoDB tables
+RENAME TABLE t1 TO t11;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE t11;
+--replace_result t11#P t11#p
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+--replace_result t11#P t11#p
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_result t11#P t11#p  $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+--echo # Verifying .frm, .par and MyISAM files (.MYD, MYI)
+--file_exists $MYSQLD_DATADIR/test/t11.frm
+--file_exists $MYSQLD_DATADIR/test/t11.par
+--file_exists $MYSQLD_DATADIR/test/t11#P#p0.isl
+--file_exists $MYSQLD_DATADIR/test/t11#P#p1.isl
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/test/t11#P#p0.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/test/t11#P#p1.ibd
+
+DROP TABLE t11;
+
+--echo # Test the previous DDL as a prepared statement.
+SET GLOBAL innodb_file_per_table = ON;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text)
+     ENGINE = InnoDB PARTITION BY HASH (a)
+     (PARTITION p0 engine=InnoDB $data_directory,
+      PARTITION p1 engine=InnoDB $data_directory2)";
+EXECUTE stmt1;
+SHOW WARNINGS;
+DEALLOCATE PREPARE stmt1;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE t1;
+--replace_result t1#P t1#p
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+--replace_result t1#P t1#p
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_result t1#P t1#p  $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+DROP TABLE t1;
+
+
+--echo # Test DATA DIRECTORY with Sub-partitions.
+SET GLOBAL innodb_file_per_table = ON;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE t1 (id INT, purchased DATE) engine=InnoDB
+    PARTITION BY RANGE( YEAR(purchased) )
+    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
+        PARTITION p0 VALUES LESS THAN (1990) (
+            SUBPARTITION s0 $data_directory,
+            SUBPARTITION s1 $data_directory2
+        ),
+        PARTITION p1 VALUES LESS THAN (2000) (
+            SUBPARTITION s2 $data_directory,
+            SUBPARTITION s3 $data_directory2
+        ),
+        PARTITION p2 VALUES LESS THAN MAXVALUE (
+            SUBPARTITION s4 $data_directory,
+            SUBPARTITION s5 $data_directory2
+        )
+    );
+SHOW WARNINGS;
+INSERT INTO t1 VALUES(1,'1980-05-31');
+INSERT INTO t1 VALUES(2,'2090-05-31');
+INSERT INTO t1 VALUES(3,'2012-05-31');
+INSERT INTO t1 VALUES(4,'1970-05-31');
+INSERT INTO t1 VALUES(5,'1985-05-31');
+INSERT INTO t1 VALUES(6,'2006-05-31');
+SELECT * FROM t1;
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE t1;
+--replace_result #P# #p#  #SP# #sp#
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+--replace_result #P# #p#  #SP# #sp#
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_result #P# #p#  #SP# #sp#  $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+
+--echo # Verifying .frm, .par, .isl & .ibd files
+--file_exists $MYSQLD_DATADIR/test/t1.frm
+--file_exists $MYSQLD_DATADIR/test/t1.par
+--file_exists $MYSQLD_DATADIR/test/t1#P#p0#SP#s0.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p0#SP#s1.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p1#SP#s2.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p1#SP#s3.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p2#SP#s4.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p2#SP#s5.isl
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/test/t1#P#p0#SP#s0.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/test/t1#P#p0#SP#s1.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/test/t1#P#p1#SP#s2.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/test/t1#P#p1#SP#s3.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/test/t1#P#p2#SP#s4.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/test/t1#P#p2#SP#s5.ibd
+DROP TABLE t1;
+
+--echo # Same as above except with ROW_FORMAT=Dyamic.
+SET GLOBAL innodb_file_format = Barracuda;
+SET GLOBAL innodb_file_per_table = ON;
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+eval CREATE TABLE t1 (id INT, purchased DATE)
+    engine = innodb row_format = dynamic
+    PARTITION BY RANGE( YEAR(purchased) )
+    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
+        PARTITION p0 VALUES LESS THAN (1990) (
+            SUBPARTITION s0 $data_directory,
+            SUBPARTITION s1 $data_directory2
+        ),
+        PARTITION p1 VALUES LESS THAN (2000) (
+            SUBPARTITION s2 $data_directory,
+            SUBPARTITION s3 $data_directory2
+        ),
+        PARTITION p2 VALUES LESS THAN MAXVALUE (
+            SUBPARTITION s4 $data_directory,
+            SUBPARTITION s5 $data_directory2
+        )
+    );
+SHOW WARNINGS;
+INSERT INTO t1 VALUES(1,'1980-05-31');
+INSERT INTO t1 VALUES(2,'2090-05-31');
+INSERT INTO t1 VALUES(3,'2012-05-31');
+INSERT INTO t1 VALUES(4,'1970-05-31');
+INSERT INTO t1 VALUES(5,'1985-05-31');
+INSERT INTO t1 VALUES(6,'2006-05-31');
+SELECT * FROM t1;
+
+--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SHOW CREATE TABLE t1;
+--replace_result #P# #p#  #SP# #sp#
+SELECT name,n_cols,file_format,row_format
+       FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%';
+--replace_result #P# #p#  #SP# #sp#
+SELECT name,file_format,row_format
+       FROM information_schema.innodb_sys_tablespaces;
+--replace_result #P# #p#  #SP# #sp#  $MYSQL_TMP_DIR MYSQL_TMP_DIR
+SELECT path FROM information_schema.innodb_sys_datafiles;
+
+--echo # Verifying .frm, .par, .isl & .ibd files
+--file_exists $MYSQLD_DATADIR/test/t1.frm
+--file_exists $MYSQLD_DATADIR/test/t1.par
+--file_exists $MYSQLD_DATADIR/test/t1#P#p0#SP#s0.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p0#SP#s1.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p1#SP#s2.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p1#SP#s3.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p2#SP#s4.isl
+--file_exists $MYSQLD_DATADIR/test/t1#P#p2#SP#s5.isl
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/test/t1#P#p0#SP#s0.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/test/t1#P#p0#SP#s1.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/test/t1#P#p1#SP#s2.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/test/t1#P#p1#SP#s3.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data/test/t1#P#p2#SP#s4.ibd
+--file_exists $MYSQL_TMP_DIR/alternate_dir/data2/test/t1#P#p2#SP#s5.ibd
+
+--echo #
+--echo # Cleanup
+--echo #
+
+DROP TABLE t1;
+--rmdir $MYSQL_TMP_DIR/alternate_dir/data/test
+--rmdir $MYSQL_TMP_DIR/alternate_dir/data
+--rmdir $MYSQL_TMP_DIR/alternate_dir/data2/test
+--rmdir $MYSQL_TMP_DIR/alternate_dir/data2
+--rmdir $MYSQL_TMP_DIR/alternate_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;
+EVAL SET SESSION innodb_strict_mode=$innodb_strict_mode_orig;
+--enable_query_log
+

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (kevin.lewis:3903 to 3904) kevin.lewis23 May