^^&&^^

Edit File: innodb-tablespace.test

#
# 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_ILLEGAL_HA
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_ILLEGAL_HA
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
       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
       WHERE path LIKE '%test%' ORDER BY space;

--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%'
       ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       ORDER BY name;
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--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%'
       ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       ORDER BY name;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;
--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%'
       ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       ORDER BY name;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;
--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%'
       ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       ORDER BY name;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;
--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%'
       ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       ORDER BY name;
--replace_result ./ MYSQLD_DATADIR/ $MYSQLD_DATADIR MYSQLD_DATADIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;
--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%'
       ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       ORDER BY name;
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;
--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_ILLEGAL_HA
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
       WHERE path LIKE '%test%' ORDER BY space;
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
       WHERE path LIKE '%test%' ORDER BY space;
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
       WHERE path LIKE '%test%' ORDER BY space;
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
       WHERE path LIKE '%test%' ORDER BY space;
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
       WHERE path LIKE '%test%' ORDER BY space;
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
       WHERE path LIKE '%test%' ORDER BY space;
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