#
# A series of tests to show the correct behavior for
# CREATE TABLESPACE and associated SQL statements.
#

--echo #
--echo # CREATE TABLESPACE related tests.
--echo #

SET DEFAULT_STORAGE_ENGINE=InnoDB;
SET NAMES utf8;
LET $MYSQLD_DATADIR = `select @@datadir`;
LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW VARIABLES LIKE 'innodb_directories';

--echo # Strict-mode has no effect on CREATE TABLESPACE.
--echo # It rejects all invalid input, as if strict mode is always ON.
SHOW VARIABLES LIKE 'innodb_strict_mode';

--echo # The setting innodb_file_per_table=OFF will not prevent
--echo # CREATE TABLESPACE from working because the tablespace is
--echo # an empty shell that can contain multiple row formats.
SHOW VARIABLES LIKE 'innodb_file_per_table';

--echo #
--echo # Try to create a tablespace without specifying the name
--echo #
--error ER_PARSE_ERROR
CREATE TABLESPACE;

--echo # Try to create a tablespace with bad characters in the tablespace name identifier.
--echo #
# From;  http://dev.mysql.com/doc/refman/5.7/en/identifiers.html
# An identifier must be either unquoted or quoted with (`).
# Identifiers are converted to Unicode internally. They may contain these characters:
# Permitted characters in unquoted identifiers:
#   ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
#   Extended: U+0080 .. U+FFFF
# Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:
#   ASCII: U+0001 .. U+007F
#   Extended: U+0080 .. U+FFFF
# ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers.
# Identifiers may begin with a digit but unless quoted may not consist solely of digits.
# Database, table, and column names cannot end with space characters.
# The identifier quote character is the backtick (`)

--error ER_PARSE_ERROR
CREATE TABLESPACE 's_bad' ADD DATAFILE 's_bad.ibd';
--error ER_PARSE_ERROR
CREATE TABLESPACE "s_bad" ADD DATAFILE 's_bad.ibd';
--error ER_WRONG_TABLESPACE_NAME
CREATE TABLESPACE `` ADD DATAFILE 's_bad.ibd';
--error ER_PARSE_ERROR
CREATE TABLESPACE s@bad ADD DATAFILE 's_bad.ibd';
--error ER_PARSE_ERROR
CREATE TABLESPACE s-bad ADD DATAFILE 's_bad.ibd';
--error ER_PARSE_ERROR
CREATE TABLESPACE test/s_bad ADD DATAFILE 's_bad.ibd';
--error ER_WRONG_TABLESPACE_NAME
CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd';
LET $TWOFIFTYFIVE=../xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_WRONG_FILE_NAME
eval CREATE TABLESPACE `s_too_long_file_name` ADD DATAFILE '$TWOFIFTYFIVE.ibd';
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW WARNINGS;
--error ER_FILEGROUP_OPTION_ONLY_ONCE
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=1k FILE_BLOCK_SIZE=2k;
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=3k;
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=65k;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_WRONG_FILE_NAME
CREATE TABLESPACE `s_bad` ADD DATAFILE '../sub/dir/////s_bad.ibd';
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW WARNINGS;
# Show that those directories were not created.
--mkdir $MYSQLD_DATADIR/sub
--mkdir $MYSQLD_DATADIR/sub/dir
--rmdir $MYSQLD_DATADIR/sub/dir
--rmdir $MYSQLD_DATADIR/sub

--error ER_PARSE_ERROR
DROP TABLESPACE 's_bad';
--error ER_PARSE_ERROR
DROP TABLESPACE "s_bad";
--error ER_WRONG_TABLESPACE_NAME
DROP TABLESPACE ``;
SHOW WARNINGS;
# "DROP TABLESPACE s#bad" is the same as "DROP TABLESPACE s".
# Unlike "CREATE TABLESPACE s", this is valid syntax, so we
# don't get parse error here.
--error ER_TABLESPACE_MISSING_WITH_NAME
DROP TABLESPACE s#bad;
--error ER_PARSE_ERROR
DROP TABLESPACE s@bad;
--error ER_PARSE_ERROR
DROP TABLESPACE s-bad;
--error ER_TABLESPACE_MISSING_WITH_NAME
DROP TABLESPACE `test/s_bad`;
--error ER_TABLESPACE_MISSING_WITH_NAME
DROP TABLESPACE s_does_not_exist;


--echo #
--echo # InnoDB does not allow General tablespace names with '/'
--echo #
--error ER_WRONG_TABLESPACE_NAME
CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd';
SHOW WARNINGS;

# When identifiers are quoted, most anything is allowed.
# Table names cannot have a trailing space, but tablespaces can.
CREATE TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` ADD DATAFILE 's_utf8.ibd';
CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` (a int, b text) TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `;
INSERT INTO `t !@#$%^&*()_+-={}[]|\?<>,.` VALUES(1,'one');
SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2` (
  `a` int(11) DEFAULT NULL,
  `b` text
) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=latin1;
SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2`;

CREATE TABLESPACE `#sql_1` ADD DATAFILE '#sql_1.ibd';
CREATE TABLE `#sql_1` (a int, b text) TABLESPACE `#sql_1`;
INSERT INTO `#sql_1` VALUES(1,'one');
SHOW CREATE TABLE `#sql_1`;

CREATE TABLESPACE `s_Cöŀumň` ADD DATAFILE 's_utf8_a.ibd';
CREATE TABLE `t_utf8_1` (a int, b text) TABLESPACE `s_Cöŀumň`;
SHOW CREATE TABLE `t_utf8_1`;

CREATE TABLESPACE `s_cöĿǖmň` ADD DATAFILE 's_utf8_b.ibd';
CREATE TABLE `t_utf8_2` (a int, b text) TABLESPACE `s_cöĿǖmň`;
SHOW CREATE TABLE `t_utf8_2`;

--error ER_INVALID_CHARACTER_STRING
CREATE TABLESPACE `s_😲` ADD DATAFILE 's_utf8_c.ibd';
--error ER_INVALID_CHARACTER_STRING
CREATE TABLESPACE `s_𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜` ADD DATAFILE 's_utf8_d.ibd';

CREATE TABLESPACE `s_வணக்கம்` ADD DATAFILE 'ஆவணம்.ibd';
CREATE TABLE `t_utf8_3` (a int, b text) TABLESPACE `s_வணக்கம்`;
SHOW CREATE TABLE `t_utf8_3`;

--source suite/innodb/include/show_i_s_tablespaces.inc
--source suite/innodb/include/show_i_s_tables.inc
--echo # Directory listing of MYSQLD_DATADIR/
--list_files $MYSQLD_DATADIR *.ibd

CHECK TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
DROP TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `;
CHECK TABLE `#sql_1`;
DROP TABLE `#sql_1`;
DROP TABLESPACE `#sql_1`;
DROP TABLE `t_utf8_1`;
DROP TABLESPACE `s_Cöŀumň`;
DROP TABLE `t_utf8_2` ;
DROP TABLESPACE `s_cöĿǖmň` ;
DROP TABLE `t_utf8_3`;
DROP TABLESPACE `s_வணக்கம்`;

--echo #
--echo # Try to create a tablespace with the reserved case-sensitive prefix 'innodb_'
--echo #
--error ER_WRONG_TABLESPACE_NAME
CREATE TABLESPACE `innodb_system` ADD DATAFILE 's_bad.ibd';
--error ER_WRONG_TABLESPACE_NAME
DROP TABLESPACE `innodb_system`;
SHOW WARNINGS;
# Possible, but not wise!
CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 's_InnoDB_System.ibd';
DROP TABLESPACE `InnoDB_System`;
--error ER_TABLESPACE_DUP_FILENAME
CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1';
SHOW WARNINGS;
# Possible, but not wise!
CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1.ibd';
DROP TABLESPACE `InnoDB_System`;

--error ER_WRONG_TABLESPACE_NAME
CREATE TABLESPACE `innodb_temporary` ADD DATAFILE 's_bad.ibd';
SHOW WARNINGS;
--error ER_WRONG_TABLESPACE_NAME
DROP TABLESPACE `innodb_temporary`;
# Possible, but not wise!
CREATE TABLESPACE `InnoDB_Temporary` ADD DATAFILE 's_InnoDB_Temporary.ibd';
DROP TABLESPACE `InnoDB_Temporary`;

--error ER_WRONG_TABLESPACE_NAME
CREATE TABLESPACE `innodb_custom` ADD DATAFILE 's_bad.ibd';
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING_WITH_NAME
DROP TABLESPACE `innodb_custom`;
CREATE TABLESPACE `InnoDB_Custom` ADD DATAFILE 's_InnoDB_Custom.ibd';
DROP TABLESPACE `InnoDB_Custom`;
CREATE TABLESPACE `INNODB_CUSTOM` ADD DATAFILE 's_INNODB_CUSTOM.ibd';
DROP TABLESPACE `INNODB_CUSTOM`;

--error ER_TABLESPACE_MISSING_WITH_NAME
DROP TABLESPACE `ib_logfile0`;
CREATE TABLESPACE `ib_logfile0` ADD DATAFILE 'ib_logfile0.ibd';
DROP TABLESPACE `ib_logfile0`;

--error ER_TABLESPACE_MISSING_WITH_NAME
DROP TABLESPACE `ib_logfile1`;
CREATE TABLESPACE `ib_logfile1` ADD DATAFILE 'ib_logfile1.ibd';
DROP TABLESPACE `ib_logfile1`;

--error ER_TABLESPACE_MISSING_WITH_NAME
DROP TABLESPACE `ibdata1`;
CREATE TABLESPACE `ibdata1` ADD DATAFILE 'ibdata1.ibd';
DROP TABLESPACE `ibdata1`;

--error ER_TABLESPACE_MISSING_WITH_NAME
DROP TABLESPACE `undo_001`;
CREATE TABLESPACE `undo_001` ADD DATAFILE 'undo_001.ibd';
DROP TABLESPACE `undo_001`;

--error ER_TABLESPACE_MISSING_WITH_NAME
DROP TABLESPACE `undo_002`;
CREATE TABLESPACE `undo_002` ADD DATAFILE 'undo_002.ibd';
DROP TABLESPACE `undo_002`;

--echo #
--echo # Test various forms of ADD DATAFILE
--echo #

--mkdir $MYSQL_TMP_DIR/tablespace.ibd
--rmdir $MYSQL_TMP_DIR/tablespace.ibd
--mkdir $MYSQL_TMP_DIR/tablespace.ibd
--mkdir $MYSQL_TMP_DIR/s2_#_dir
--mkdir $MYSQL_TMP_DIR/test

CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd' ENGINE=InnoDB;
CREATE TABLESPACE `s1_#_hash` ADD DATAFILE 's1_#_hash.ibd';

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLESPACE s1_remote ADD DATAFILE '$MYSQL_TMP_DIR/s1.ibd.ibd';
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLESPACE s2_remote ADD DATAFILE '$MYSQL_TMP_DIR/s2_#_dir/s2.ibd';

--error ER_WRONG_FILE_NAME
CREATE TABLESPACE s_bad ADD DATAFILE '.ibd';
SHOW WARNINGS;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_WRONG_FILE_NAME
CREATE TABLESPACE s_bad ADD DATAFILE '../s_dir/s_subdir/.ibd';
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW WARNINGS;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_WRONG_FILE_NAME
CREATE TABLESPACE s_bad ADD DATAFILE '../s_dir/s_bad.ibs';
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW WARNINGS;

# A colon is always rejected on Linux, but it could be part of a valid absolute path on Windows.
# These should be rejected even on Windows.
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_WRONG_FILE_NAME
CREATE TABLESPACE s_bad ADD DATAFILE 'c:s_bad.ibd';
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW WARNINGS;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_WRONG_FILE_NAME
CREATE TABLESPACE s_bad ADD DATAFILE 'drive:s_bad.ibd';
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW WARNINGS;

--echo #
--echo # Try to create a tablespace where a same-named directory and file exist.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLESPACE s4_def ADD DATAFILE '$MYSQL_TMP_DIR/tablespace.ibd/s4.ibd';
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_TABLESPACE_DUP_FILENAME
eval CREATE TABLESPACE s5_def ADD DATAFILE '$MYSQL_TMP_DIR/tablespace.ibd/s4.ibd';
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_WRONG_FILE_NAME
eval CREATE TABLESPACE s6_def ADD DATAFILE '$MYSQL_TMP_DIR/tablespace.ibd';

--source suite/innodb/include/show_i_s_tablespaces.inc

--echo # Directory listing of MYSQLD_DATADIR/
--list_files $MYSQLD_DATADIR/ *.ibd

--echo # Directory listing of MYSQL_TMP_DIR/
--list_files $MYSQL_TMP_DIR/ *.ibd

--echo # Directory listing of MYSQL_TMP_DIR/tablespace.ibd/
--list_files $MYSQL_TMP_DIR/tablespace.ibd/ *.ibd

--echo # Directory listing of MYSQL_TMP_DIR/s2_#_dir/
--list_files $MYSQL_TMP_DIR/s2_#_dir/ *.ibd

DROP TABLESPACE s4_def;
DROP TABLESPACE s1_remote;
DROP TABLESPACE s2_remote;
DROP TABLESPACE `s1_#_hash`;

--echo #
--echo # Try to create a tablespace that already exists.
--echo # Make sure that the tablespace name is compared in a CASE SENSITIVE way.
--echo #
--error ER_TABLESPACE_EXISTS
CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd';
--error ER_TABLESPACE_EXISTS
CREATE TABLESPACE s_def ADD DATAFILE 's_def_2.ibd';
--error ER_TABLESPACE_DUP_FILENAME
CREATE TABLESPACE S_new ADD DATAFILE 's_def.ibd';
CREATE TABLESPACE `s_DEF` ADD DATAFILE 's_def_2.ibd';
DROP TABLESPACE `s_DEF`;

--echo #
--echo # Make a file-per-table tablespace name and try to use it as a General tablespace
--echo #
CREATE TABLE t_single (a int, b text);
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
# A parse error occurs if an identifier contains a '/' unless the identifier is quoted.
--error ER_PARSE_ERROR
CREATE TABLE t_general (a int, b text) TABLESPACE test/t_single engine=InnoDB;
--error ER_WRONG_TABLESPACE_NAME
CREATE TABLE t_general (a int, b text) TABLESPACE `test/t_single` engine=InnoDB;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING_WITH_NAME
CREATE TABLE t_general (a int, b text) TABLESPACE `S_Def` engine=InnoDB;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING_WITH_NAME
CREATE TABLE t_general (a int, b text) TABLESPACE `S_DEF` engine=InnoDB;
SHOW WARNINGS;

--error ER_WRONG_TABLESPACE_NAME
CREATE TABLESPACE `test/t_single` ADD DATAFILE 's_single.ibd';
SHOW WARNINGS;
--error ER_WRONG_TABLESPACE_NAME
CREATE TABLESPACE `Test/t_Single` ADD DATAFILE 's_single.ibd';
SHOW WARNINGS;
--error ER_WRONG_TABLESPACE_NAME
CREATE TABLESPACE `TEST/T_SINGLE` ADD DATAFILE 's_single.ibd';
SHOW WARNINGS;

DROP TABLE t_single;

--source suite/innodb/include/show_i_s_tablespaces.inc

--echo #
--echo # Row format is not allowed on CREATE TABLESPACE
--echo #
--error ER_PARSE_ERROR
CREATE TABLESPACE s_red ADD DATAFILE 's_red.ibd' ROW_FORMAT=redundant;

--echo #
--echo # Add tables to the tablespaces.
--echo #
CREATE TABLE t_def_in_def (a int, b text) TABLESPACE s_def;
CREATE TABLE t_red_in_def (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_def;
# Since these are creating tables within existing general tablespaces the value
# of INNODB_FILE_PER_TABLE does not matter.
SET GLOBAL innodb_file_per_table = OFF;
CREATE TABLE t_dyn_in_def (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_def;

--echo # Add data to the existing Tables
INSERT INTO t_def_in_def VALUES (1,'a'),(2,'b'),(3,'c');
INSERT INTO t_red_in_def VALUES (1,'a'),(2,'b'),(3,'c');
INSERT INTO t_dyn_in_def VALUES (1,'a'),(2,'b'),(3,'c');

--echo #
--echo # Try to drop a tablespace which is not empty
--echo #
--error ER_TABLESPACE_IS_NOT_EMPTY
DROP TABLESPACE s_def;

--source suite/innodb/include/show_i_s_tablespaces.inc
--source suite/innodb/include/show_i_s_tables.inc
--echo # Directory of MYSQLD_DATADIR/
--list_files $MYSQLD_DATADIR/ *.ibd
--echo # Directory of MYSQLD_DATADIR/test/
--list_files $MYSQLD_DATADIR/test/ *.ibd

--echo #
--echo # Drop the tables we no longer need.
--echo #

CHECK TABLE t_dyn_in_def;
CHECK TABLE t_red_in_def;

DROP TABLE t_dyn_in_def;
DROP TABLE t_red_in_def;

--echo #
--echo # Try to make a table using the database name with an existing table name
--echo #
--error ER_PARSE_ERROR
CREATE TABLE test/t_def_in_def (a int, b text) TABLESPACE s_def;
# With the back-quotes, MySQL converts the tablename to `test@002ft_def_in_def`.
CREATE TABLE `test/t_def_in_def` (a int, b text) TABLESPACE s_def;

--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
--echo # Directory listing of MYSQLD_DATADIR/
--list_files $MYSQLD_DATADIR/ *.ibd
--echo # Directory listing of MYSQLD_DATADIR/test/
--list_files $MYSQLD_DATADIR/test/ *.ibd

--echo #
--echo # Try to create a temporary tablespace
--echo #
--error ER_PARSE_ERROR
CREATE TEMPORARY TABLESPACE s_temp ADD DATAFILE 's_temp.ibd';
SHOW WARNINGS;

--echo #
--echo # Try to put a temporary table into a non-temporary tablespace
--echo #
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TEMPORARY TABLE t_temp_red (a int, b text) ROW_FORMAT=redundant TABLESPACE s_def;
SHOW WARNINGS;

--echo #
--echo # Try to put a compressed temporary table into the system temporary tablespace
--echo #
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=compressed TABLESPACE=`innodb_temporary`;
SHOW WARNINGS;

--echo #
--echo # Try to add a second table to a single-table tablespace
--echo #
SET GLOBAL innodb_file_per_table = ON;
CREATE TABLE s_single (a int, b text) ROW_FORMAT=dynamic;
--error ER_TABLESPACE_MISSING_WITH_NAME
CREATE TABLE t_second (a int, b text) TABLESPACE s_single;
SHOW WARNINGS;
--error ER_WRONG_TABLESPACE_NAME
CREATE TABLE t_second (a int, b text) TABLESPACE=`test/s_single`;
SHOW WARNINGS;
--source suite/innodb/include/show_i_s_tablespaces.inc
DROP TABLE s_single;

--echo #
--echo # Try to use both TABLESPACE and DATA DIRECTORY in the same CREATE TABLE.
--echo #
--echo # Strict mode and innodb_file_per_table should make no difference
--echo # when using general tablespaces. If TABLESPACE=innodb_file-per-table,
--echo # innodb_strict_mode will apply.
--echo #
SET GLOBAL innodb_file_per_table = OFF;
SET innodb_strict_mode = OFF;

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_ILLEGAL_HA_CREATE_OPTION
eval CREATE TABLE t3 (a int, b text) DATA DIRECTORY='$MYSQL_TMP_DIR' TABLESPACE s_def;
SHOW WARNINGS;

--echo # Cannot use both a general tablespace and a DATA DIRECTORY
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
--error ER_ILLEGAL_HA_CREATE_OPTION
eval CREATE TABLE t4 (a int, b text) TABLESPACE=s_def DATA DIRECTORY='$MYSQL_TMP_DIR';
SHOW WARNINGS;

--echo # TABLESPACE=innodb_file_per_table can be used with DATA DIRECTORY
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR';
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t4;

--echo # An altered table should retain both TABLESPACE=innodb_file_per_table and the DATA DIRECTORY
ALTER TABLE t4 ROW_FORMAT=dynamic, algorithm=copy;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t4;
DROP TABLE t4;

--echo # An empty string in DATA DIRECTORY='' indicates the current directory,
--echo # which is not allowed.
--error ER_WRONG_VALUE
CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='';
SHOW WARNINGS;

SET GLOBAL innodb_file_per_table = ON;
SET innodb_strict_mode = ON;

--error ER_WRONG_VALUE
CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='';
SHOW WARNINGS;
--error ER_WRONG_VALUE
CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='.';
SHOW WARNINGS;
--error ER_WRONG_VALUE
CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='test';
SHOW WARNINGS;

--source suite/innodb/include/show_i_s_tablespaces.inc
--source suite/innodb/include/show_i_s_tables.inc
DROP TABLE t_def_in_def;
DROP TABLE `test/t_def_in_def`;

--echo #
--echo # Create tables explicitly in the system tablespace.
--echo #
CREATE TABLE t_red_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=redundant;
CREATE TABLE t_com_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compact;
CREATE TABLE t_dyn_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=dynamic;
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLE t_zip_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compressed;
SHOW WARNINGS;

--source suite/innodb/include/show_i_s_tablespaces.inc
--source suite/innodb/include/show_i_s_tables.inc
CHECK TABLE t_red_in_system;
CHECK TABLE t_com_in_system;
CHECK TABLE t_dyn_in_system;
DROP TABLE t_red_in_system;
DROP TABLE t_com_in_system;
DROP TABLE t_dyn_in_system;

--echo #
--echo # Create tables explicitly as file_per_table tablespaces.
--echo #
CREATE TABLE t_red_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=redundant;
CREATE TABLE t_com_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compact;
CREATE TABLE t_dyn_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=dynamic;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t_def_as_remote (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR';
--source suite/innodb/include/show_i_s_tablespaces.inc
--source suite/innodb/include/show_i_s_tables.inc
--echo # Directory listing of MYSQLD_DATADIR/
--list_files $MYSQLD_DATADIR/ *.ibd
--echo # Directory listing of MYSQLD_DATADIR/test/
--list_files $MYSQLD_DATADIR/test/ *.ibd
SHOW CREATE TABLE t_red_as_file_per_table;
SHOW CREATE TABLE t_com_as_file_per_table;
SHOW CREATE TABLE t_dyn_as_file_per_table;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t_def_as_remote;

--echo #
--echo # These explicit file_per_table tables can be moved to a general tablespace.
--echo #
CREATE TABLESPACE s_multiple ADD DATAFILE 'multiple.ibd';
ALTER TABLE `t_red_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_red_was_file_per_table`;
ALTER TABLE `t_com_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_com_was_file_per_table`;
ALTER TABLE `t_dyn_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_dyn_was_file_per_table`;
ALTER TABLE `t_def_as_remote` TABLESPACE=`s_multiple`, RENAME TO `t_def_was_remote`;

--source suite/innodb/include/show_i_s_tablespaces.inc
--source suite/innodb/include/show_i_s_tables.inc
--echo # Directory listing of MYSQLD_DATADIR/
--list_files $MYSQLD_DATADIR/ *.ibd
--echo # Directory listing of MYSQLD_DATADIR/test/
--list_files $MYSQLD_DATADIR/test/ *.ibd

--echo #
--echo # Tables in a general tablespace can be moved to file_per_table locations.
--echo #
ALTER TABLE `t_red_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_red_to_file_per_table`;
ALTER TABLE `t_com_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_com_to_file_per_table`;
ALTER TABLE `t_dyn_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_dyn_to_file_per_table`;
--echo # Note that MySQL ignores DATA DIRECTORY on all ALTER TABLE statements.
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval ALTER TABLE `t_def_was_remote` TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='$MYSQL_TMP_DIR', RENAME TO `t_def_to_file_per_table`;

--source suite/innodb/include/show_i_s_tablespaces.inc
--source suite/innodb/include/show_i_s_tables.inc
--echo # Directory listing of MYSQLD_DATADIR/
--list_files $MYSQLD_DATADIR/ *.ibd
--echo # Directory listing of MYSQLD_DATADIR/test/
--list_files $MYSQLD_DATADIR/test/ *.ibd

DROP TABLE t_red_to_file_per_table;
DROP TABLE t_com_to_file_per_table;
DROP TABLE t_dyn_to_file_per_table;
DROP TABLE t_def_to_file_per_table;
DROP TABLESPACE s_multiple;

--echo #
--echo # Try the syntax that InnoDB does not support.
--echo #

--echo # Try NDB settings for CREATE TABLESPACE. These are currently accepted
--echo # even if they probably should not be. This is valid syntax from the
--echo # parser's POW

CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd'
       USE LOGFILE GROUP logfile_group;
DROP TABLESPACE s_bad;
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' EXTENT_SIZE = 100M;
DROP TABLESPACE s_bad;
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' NO_WAIT;
DROP TABLESPACE s_bad;

--echo #
--echo # Checking LOGFILE GROUP commands (only supported by NDB)
--echo #
--error ER_PARSE_ERROR
CREATE LOGFILE GROUP s_bad;
--error ER_FEATURE_UNSUPPORTED
CREATE LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat';
--error ER_PARSE_ERROR
ALTER LOGFILE GROUP s_bad;
--error ER_FEATURE_UNSUPPORTED
ALTER LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat'
      INITIAL_SIZE = 100M, NO_WAIT;
--error ER_FEATURE_UNSUPPORTED
DROP LOGFILE GROUP s_bad;

--echo #
--echo # Checking ALTER TABLESPACE (only NDB in 5.7)
--echo #
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd';

--error ER_PARSE_ERROR
ALTER TABLESPACE s_bad;

--error ER_ALTER_FILEGROUP_FAILED
ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd';
SHOW WARNINGS;

--error ER_ALTER_FILEGROUP_FAILED
ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd';
SHOW WARNINGS;

--error ER_ALTER_FILEGROUP_FAILED
ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd' INITIAL_SIZE = 1G;
SHOW WARNINGS;

--error ER_ALTER_FILEGROUP_FAILED
ALTER TABLESPACE s_def ADD DATAFILE 'bad2.ibd' NO_WAIT;
SHOW WARNINGS;

--error ER_ALTER_FILEGROUP_FAILED
ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd' INITIAL_SIZE = 1G;
SHOW WARNINGS;

--error ER_ALTER_FILEGROUP_FAILED
ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd' NO_WAIT;
SHOW WARNINGS;

--echo #
--echo # Undocumented ALTER TABLESPACE syntax which was removed by wl#8972
--echo #
--error ER_PARSE_ERROR
ALTER TABLESPACE s_bad CHANGE 's_bad.ibd'
      INITIAL_SIZE = 1G, AUTOEXTEND_SIZE = 100M, MAX_SIZE = 2G;
--error ER_PARSE_ERROR
ALTER TABLESPACE s_bad READ_ONLY;
--error ER_PARSE_ERROR
ALTER TABLESPACE s_bad READ_WRITE;
--error ER_PARSE_ERROR
ALTER TABLESPACE s_bad NOT ACCESSIBLE;

DROP TABLESPACE s_bad;

--echo #
--echo # Try the syntax that MySQL docs say is ignored.
--echo #
CREATE TABLESPACE s_ignore1 ADD DATAFILE 's_ignore1.ibd' NODEGROUP = 5;
CREATE TABLESPACE s_ignore2 ADD DATAFILE 's_ignore2.ibd' WAIT;
CREATE TABLESPACE s_ignore3 ADD DATAFILE 's_ignore3.ibd' COMMENT = 'This comment is ignored';

--source suite/innodb/include/show_i_s_tablespaces.inc

DROP TABLESPACE s_ignore1;
DROP TABLESPACE s_ignore2;
DROP TABLESPACE s_ignore3;

--echo #
--echo # Try various ALTER TABLE statements.
--echo #
CREATE TABLE t_in_def (a serial key, b text) TABLESPACE s_def;
INSERT INTO t_in_def(b) VALUES(repeat("a short string - ",5));
INSERT INTO t_in_def(b) VALUES(repeat("a long string - ",50));
INSERT INTO t_in_def(b) SELECT b FROM t_in_def;
SELECT a, left(b,50) FROM t_in_def;
SHOW CREATE TABLE t_in_def;
--source suite/innodb/include/show_i_s_tables.inc

ALTER TABLE t_in_def ROW_FORMAT=redundant;
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def ROW_FORMAT=dynamic;
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def ADD COLUMN (c int);
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def ADD INDEX c (c);
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def DROP INDEX c;
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def DROP COLUMN c;
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def AUTO_INCREMENT=100;
SHOW CREATE TABLE t_in_def;
INSERT INTO t_in_def(b) VALUES("after ALTER TABLE AUTO_INCREMENT");
SELECT a, left(b,50) FROM t_in_def;
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def CHANGE a aa int;
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def CHANGE b bb varchar(1000);
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def DROP PRIMARY KEY;
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def ADD PRIMARY KEY aa(aa), ALGORITHM=INPLACE;
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def ADD INDEX ab(aa,bb(25)), ALGORITHM=COPY;
SHOW CREATE TABLE t_in_def;

ALTER TABLE t_in_def ADD INDEX bb(bb(50));
SHOW CREATE TABLE t_in_def;

RENAME TABLE t_in_def TO t;
SHOW CREATE TABLE t;
--source suite/innodb/include/show_i_s_tables.inc
INSERT INTO t VALUES(200, "after RENAME TABLE t_in_def TO t");
SELECT aa, left(bb,50) FROM t;

--echo #
--echo # Move tables between tablespaces.
--echo #
CREATE TABLESPACE s_alt1 ADD DATAFILE 's_alt1.ibd';
CREATE TABLESPACE s_alt2 ADD DATAFILE 's_alt2.ibd';
--source suite/innodb/include/show_i_s_tablespaces.inc
ALTER TABLE t RENAME TO t_nomad, TABLESPACE s_alt1;
SHOW CREATE TABLE t_nomad;
--source suite/innodb/include/show_i_s_tables.inc
ALTER TABLE t_nomad TABLESPACE s_alt2;
SHOW CREATE TABLE t_nomad;
--source suite/innodb/include/show_i_s_tables.inc
ALTER TABLE t_nomad TABLESPACE s_def, DROP COLUMN bb;
SHOW CREATE TABLE t_nomad;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
DROP TABLE t_nomad;
DROP TABLESPACE s_alt1;
DROP TABLESPACE s_alt2;

--echo #
--echo # Move a table from the System Tablespace into a General Tablespace
--echo # and then back out to the system tablespace again.
--echo #
SET GLOBAL innodb_file_per_table=OFF;
CREATE TABLE t_system (a serial key, b text);
INSERT INTO t_system(b) VALUES(repeat("a short string - ",5));
INSERT INTO t_system(b) VALUES(repeat("a long string - ",50));
SHOW CREATE TABLE t_system;
--source suite/innodb/include/show_i_s_tables.inc

ALTER TABLE t_system TABLESPACE s_def;
SHOW CREATE TABLE t_system;
--source suite/innodb/include/show_i_s_tables.inc

ALTER TABLE t_system TABLESPACE=`innodb_system`;
SHOW CREATE TABLE t_system;
--source suite/innodb/include/show_i_s_tables.inc

--echo #
--echo # Do a few ALTER TABLES for this table that was moved out and back into the system tablespace.
--echo #
ALTER TABLE t_system ROW_FORMAT=redundant;
SHOW CREATE TABLE t_system;
ALTER TABLE t_system ROW_FORMAT=dynamic;
SHOW CREATE TABLE t_system;
ALTER TABLE t_system ADD COLUMN (c int);
SHOW CREATE TABLE t_system;
ALTER TABLE t_system ADD INDEX c (c);
SHOW CREATE TABLE t_system;
ALTER TABLE t_system DROP INDEX c;
SHOW CREATE TABLE t_system;
ALTER TABLE t_system DROP COLUMN c;
SHOW CREATE TABLE t_system;

--echo #
--echo # Now try to put that table into its own Single-Table tablespace.
--echo # It is not allowed since the table is now marked with the system tablespace
--echo #
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE t_system RENAME TO t_still_system;
SHOW CREATE TABLE t_still_system;
--source suite/innodb/include/show_i_s_tables.inc
DROP TABLE t_still_system;

--echo #
--echo # Try to create or move a table into the temporary tablespace.
--echo #
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLE t_not_temp (a int, b text) TABLESPACE=`innodb_temporary`;
SHOW WARNINGS;

CREATE TABLE t_not_temp (a int, b text) TABLESPACE s_def;
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE t_not_temp TABLESPACE=`innodb_temporary`;
SHOW WARNINGS;
DROP TABLE t_not_temp;

SET SESSION innodb_strict_mode=OFF;
CREATE TEMPORARY TABLE t_my_temp (a int, b text) TABLESPACE=`innodb_temporary`;
SHOW CREATE TABLE t_my_temp;
DROP TABLE t_my_temp;
SET SESSION innodb_strict_mode=ON;

--echo #
--echo # Try to create or move a table into the redo tablespace
--echo # or any tablespace using the reserved `innodb_` prefix.
--echo #
--error ER_WRONG_TABLESPACE_NAME
CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_redo_log`;
SHOW WARNINGS;
--error ER_WRONG_TABLESPACE_NAME
CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_anything`;
SHOW WARNINGS;

CREATE TABLE t_data (a int, b text) TABLESPACE s_def;
--error ER_WRONG_TABLESPACE_NAME
ALTER TABLE t_data TABLESPACE=`innodb_redo_log`;
SHOW WARNINGS;
--error ER_WRONG_TABLESPACE_NAME
ALTER TABLE t_data TABLESPACE=`innodb_anything`;
SHOW WARNINGS;
DROP TABLE t_data;

--echo #
--echo # Move a table from a Single-Table tablespace into a General Tablespace.
--echo #
CREATE TABLE t_single (a serial key, b text);
INSERT INTO t_single(b) VALUES(repeat("a short string - ",5));
INSERT INTO t_single(b) VALUES(repeat("a long string - ",50));
SHOW CREATE TABLE t_single;
--source suite/innodb/include/show_i_s_tables.inc
ALTER TABLE t_single TABLESPACE s_def;
SHOW CREATE TABLE t_single;
--source suite/innodb/include/show_i_s_tables.inc
DROP TABLE t_single;

--echo #
--echo # Move a MyISAM table into an InnoDB General Tablespace and back.
--echo #
CREATE TABLE t_myisam (a serial key, b int) ENGINE=MyISAM;
INSERT INTO t_myisam(b) VALUES(11);
SHOW CREATE TABLE t_myisam;
ALTER TABLE t_myisam RENAME TO t_innodb, TABLESPACE s_def, ENGINE=InnoDB;
INSERT INTO t_innodb(b) VALUES(22);
SHOW CREATE TABLE t_innodb;
--source suite/innodb/include/show_i_s_tables.inc
ALTER TABLE t_innodb RENAME TO t_myisam, ENGINE=MyISAM;
INSERT INTO t_myisam(b) VALUES(33);
SELECT * FROM t_myisam;
SHOW CREATE TABLE t_myisam;
ALTER TABLE t_myisam RENAME TO t_innodb, ENGINE=InnoDB;
INSERT INTO t_innodb(b) VALUES(44);
SHOW CREATE TABLE t_innodb;
--source suite/innodb/include/show_i_s_tables.inc
DROP TABLE t_innodb;

--echo #
--echo # Move a Dynamic table from an InnoDB General tablespace to MyISAM
--echo # and Memory and back without having to re-specify the tablespace
--echo # and row_format.
--echo #
CREATE TABLE t_dyn_in_s_def (a serial key, b int) ROW_FORMAT=Dynamic TABLESPACE=s_def ENGINE=InnoDB;
INSERT INTO t_dyn_in_s_def(b) VALUES(11);
SHOW CREATE TABLE t_dyn_in_s_def;
--source suite/innodb/include/show_i_s_tables.inc
ALTER TABLE t_dyn_in_s_def ENGINE=MyISAM;
INSERT INTO t_dyn_in_s_def(b) VALUES(22);
SHOW CREATE TABLE t_dyn_in_s_def;
--source suite/innodb/include/show_i_s_tables.inc
ALTER TABLE t_dyn_in_s_def ENGINE=InnoDB;
INSERT INTO t_dyn_in_s_def(b) VALUES(33);
SHOW CREATE TABLE t_dyn_in_s_def;
--source suite/innodb/include/show_i_s_tables.inc
ALTER TABLE t_dyn_in_s_def ENGINE=Memory;
INSERT INTO t_dyn_in_s_def(b) VALUES(44);
SHOW CREATE TABLE t_dyn_in_s_def;
--source suite/innodb/include/show_i_s_tables.inc
ALTER TABLE t_dyn_in_s_def ENGINE=InnoDB;
INSERT INTO t_dyn_in_s_def(b) VALUES(55);
SELECT * FROM t_dyn_in_s_def;
SHOW CREATE TABLE t_dyn_in_s_def;
--source suite/innodb/include/show_i_s_tables.inc
CHECK TABLE t_dyn_in_s_def;
DROP TABLE t_dyn_in_s_def;

--echo #
--echo # If a tablespace name is associated with a table in another SE, and that tablespace
--echo # does not exist in InnoDB, the ALTER TABLE will fail
--echo #
CREATE TABLESPACE s_short_life ADD DATAFILE 'short_life.ibd';
CREATE TABLE t_nomad (a serial key, b int) TABLESPACE=s_short_life ENGINE=InnoDB;
INSERT INTO t_nomad(b) VALUES(11);
SHOW CREATE TABLE t_nomad;
--source suite/innodb/include/show_i_s_tables.inc
ALTER TABLE t_nomad ENGINE=MyISAM;
INSERT INTO t_nomad(b) VALUES(22);
SHOW CREATE TABLE t_nomad;
--source suite/innodb/include/show_i_s_tables.inc
DROP TABLESPACE s_short_life;
--error ER_TABLESPACE_MISSING_WITH_NAME
ALTER TABLE t_nomad ENGINE=InnoDB;
CREATE TABLESPACE s_shorter_life ADD DATAFILE 'shorter_life.ibd';
ALTER TABLE t_nomad ENGINE=InnoDB TABLESPACE=s_shorter_life;
SHOW CREATE TABLE t_nomad;
--source suite/innodb/include/show_i_s_tables.inc
DROP TABLE t_nomad;
DROP TABLESPACE s_shorter_life;

--echo #
--echo # Show that a table with the same SQL name can be created in two different
--echo # databases, whether they are in the system tablespace, a general tablespace
--echo # or their own file-per-table tablespace.
--echo # Also show that DROP DATABASE will remove tables from inside a General Tablespace
--echo # without removing the tablespace or other tables in it for another databases.
--echo #
SET GLOBAL innodb_file_per_table=OFF;
CREATE TABLE t_system (a serial key, b text);
CREATE TABLE t_general (a serial key, b text) TABLESPACE s_def;
CREATE TABLE t_single (a serial key, b text) TABLESPACE=`innodb_file_per_table`;
SHOW TABLES;

CREATE DATABASE test1;
USE test1;
# A general tablespace is independent of the current database.
CREATE TABLESPACE s_empty1 ADD DATAFILE 's_empty1.ibd' ENGINE InnoDB;
CREATE TABLE t_system (a serial key, b text);
CREATE TABLE t_general (a serial key, b text) TABLESPACE s_def;
CREATE TABLE t_single (a serial key, b text) TABLESPACE=`innodb_file_per_table`;
SHOW TABLES;

USE test;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
DROP DATABASE test1;
--source suite/innodb/include/show_i_s_tables.inc
--source suite/innodb/include/show_i_s_tablespaces.inc
DROP TABLE t_system;
DROP TABLE t_general;
DROP TABLE t_single;
DROP TABLESPACE s_empty1;

--echo #
--echo # Assign different tablespace for PK-FK tables
--echo #
CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
CREATE TABLE t1 (a int primary key) ENGINE=innodb TABLESPACE=s1;
CREATE TABLE t2 (b int , foreign key (b) references t1(a)) ENGINE=innodb ;
INSERT INTO t1 VALUES (100);
INSERT INTO t1 VALUES (200);
INSERT INTO t2 VALUES (100);
--source suite/innodb/include/show_i_s_tables.inc
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t1,t2 WHERE t1.a=t2.b;
DROP TABLE t2,t1;
DROP TABLESPACE s1;

--echo #
--echo # Assigning general tablespace from procedure
--echo #
USE test;
CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
CREATE TABLE t1 (a geometry NOT NULL SRID 0, SPATIAL (a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (st_GeomFromText("LINESTRING(100 100, 200 200, 300 300)")) ;
DELIMITER |;
CREATE PROCEDURE p1()
BEGIN
       ALTER TABLE t1 TABLESPACE=s1;
END|
DELIMITER ;|
--source suite/innodb/include/show_i_s_tables.inc
CALL p1();
--source suite/innodb/include/show_i_s_tables.inc
DROP PROCEDURE p1;

--echo #
--echo # Show that DISCARD/IMPORT tablespace does not work on a general tablespace.
--echo #
--error ER_NOT_ALLOWED_COMMAND
ALTER TABLE t1 DISCARD TABLESPACE;
SHOW WARNINGS;
--error ER_NOT_ALLOWED_COMMAND
ALTER TABLE t1 IMPORT TABLESPACE;
SHOW WARNINGS;
DROP TABLE t1;
DROP TABLESPACE s1;

--echo #
--echo # Clean-up.
--echo #

DROP TABLESPACE s_def;

SET GLOBAL innodb_file_per_table=default;
SET GLOBAL innodb_strict_mode=default;

--rmdir $MYSQL_TMP_DIR/s2_#_dir
--rmdir $MYSQL_TMP_DIR/test
--rmdir $MYSQL_TMP_DIR/tablespace.ibd

--echo #
--echo # Bug #20763179 SEGV WHEN CREATE TABLESPACE IS EXECUTED IN READ ONLY MODE
--echo #
--echo # Check CREATE TABLESPACE is handled properly in read-only mode

CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;

let $do_not_echo_parameters = 1;
let $extra_restart_parameters = --innodb-directories=$MYSQL_TMP_DIR;
--source include/restart_innodb_read_only.inc
let $do_not_echo_parameters =;
--error ER_READ_ONLY_MODE
CREATE TABLESPACE s2 ADD DATAFILE 's2.ibd' ENGINE InnoDB;
SHOW WARNINGS;
--error ER_INNODB_READ_ONLY
DROP TABLESPACE s1;
SHOW WARNINGS;

--echo # Restart in normal mode for cleanup.
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
let $restart_parameters = restart: --innodb-directories=$MYSQL_TMP_DIR;
--source include/restart_mysqld.inc
DROP TABLESPACE s1;

--echo #
--echo # Bug#26199233 create like ignores innodb_file_per_table
--echo #
--echo # - Verify if table picks correct tablespace based on
--echo # innodb_file_per_table settings.
--echo # - Check if SHOW CREATE displays correct tablespace name.

CREATE DATABASE db1;

SET @@global.innodb_file_per_table = OFF;

--echo # Case 1
--echo # SHOW CREATE should not show TABLESPACE clause after the fix.
--echo # Because it was not explicitly specified by user.
CREATE TABLE db1.t1 (c1 INT) ENGINE=InnoDB;
SHOW CREATE TABLE db1.t1;

SET @@global.innodb_file_per_table = ON;

--echo # Case 2
--echo # After the fix table, t2 should use innodb_file_per_table tablespace.
CREATE TABLE db1.t2 LIKE db1.t1;
SHOW CREATE TABLE db1.t2;
--echo # Verify the same by listing the .ibd file.
--list_files $MYSQLD_DATADIR/db1/

--echo # Case 3
--echo # db1.tables TABLESPACE clause should show 'ts0' after the fix.
CREATE TABLESPACE ts0 ADD DATAFILE 'df0.ibd';
CREATE TABLE db1.tables (c1 INT) ENGINE=InnoDB tablespace=ts0;
SHOW CREATE TABLE db1.tables;
DROP TABLE db1.tables;
DROP TABLESPACE ts0;

--echo # Cleanup
DROP DATABASE db1;

--echo # Additional test cases for Bug#26199233 to
--echo # test various combinations.

CREATE SCHEMA s1;
USE s1;
CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd';

--echo #
--echo # Tables with explicitly assigned tablespaces.
--echo #
--echo # Table with an explicitly assigned user tablespace.
CREATE TABLE t_expl_usr (i INT) TABLESPACE s1;
SHOW CREATE TABLE t_expl_usr;
--echo #
--echo # Table with an explicitly assigned system tablespace.
CREATE TABLE t_expl_sys (i INT) TABLESPACE innodb_system;
SHOW CREATE TABLE t_expl_sys;
--echo #
--echo # Table with an explicitly assigned f_p_t tablespace.
CREATE TABLE t_expl_fpt (i INT) TABLESPACE innodb_file_per_table;
SHOW CREATE TABLE t_expl_fpt;
--echo #
--echo # Temporary table with an explicitly assigned tmp tablespace.
SET SESSION innodb_strict_mode=OFF;
CREATE TEMPORARY TABLE t_expl_tmp (i INT) TABLESPACE innodb_temporary;
SHOW WARNINGS;
SET SESSION innodb_strict_mode=ON;

--echo #
--echo # Tables with implicitly assigned tablespaces.
--echo #
--echo # Table with an implicitly assigned system tablespace.
SET GLOBAL innodb_file_per_table= OFF;
CREATE TABLE t_impl_sys (i INT);
SHOW CREATE TABLE t_impl_sys;
--echo #
--echo # Table with an implicitly assigned f_p_t tablespace.
SET GLOBAL innodb_file_per_table= ON;
CREATE TABLE t_impl_fpt (i INT);
SHOW CREATE TABLE t_impl_fpt;
--echo #
--echo # Temporary table with an implicitly assigned tmp tablespace.
CREATE TEMPORARY TABLE t_impl_tmp (i INT);
SHOW CREATE TABLE t_impl_tmp;

--echo #
--echo # CREATE LIKE when innodb_file_per_table is ON.
--echo #
--echo # Tablespace preserved for all source tables that
--echo # had an explicitly assigned tablesapce.
SET GLOBAL innodb_file_per_table= ON;
CREATE TABLE fpt_on_like_t_expl_usr LIKE t_expl_usr;
SHOW CREATE TABLE fpt_on_like_t_expl_usr;
CREATE TABLE fpt_on_like_t_expl_sys LIKE t_expl_sys;
SHOW CREATE TABLE fpt_on_like_t_expl_sys;
CREATE TABLE fpt_on_like_t_expl_fpt LIKE t_expl_fpt;
SHOW CREATE TABLE fpt_on_like_t_expl_fpt;
SET SESSION innodb_strict_mode=OFF;
CREATE TEMPORARY TABLE fpt_on_like_t_expl_tmp LIKE t_expl_tmp;
SHOW WARNINGS;
SET SESSION innodb_strict_mode=OFF;

--echo # Tablespace not preserved for all source tables that
--echo # had an implicitly assigned tablesapce.
CREATE TABLE fpt_on_like_t_impl_sys LIKE t_impl_sys;
SHOW CREATE TABLE fpt_on_like_t_impl_sys;
CREATE TABLE fpt_on_like_t_impl_fpt LIKE t_impl_fpt;
SHOW CREATE TABLE fpt_on_like_t_impl_fpt;
CREATE TEMPORARY TABLE fpt_on_like_t_impl_tmp LIKE t_impl_tmp;
SHOW CREATE TABLE fpt_on_like_t_impl_tmp;

--echo #
--echo # CREATE LIKE when innodb_file_per_table is OFF.
--echo #
--echo # Tablespace preserved for all source tables that
--echo # had an explicitly assigned tablesapce.
SET GLOBAL innodb_file_per_table= OFF;
CREATE TABLE fpt_off_like_t_expl_usr LIKE t_expl_usr;
SHOW CREATE TABLE fpt_off_like_t_expl_usr;
CREATE TABLE fpt_off_like_t_expl_sys LIKE t_expl_sys;
CREATE TABLE fpt_off_like_t_expl_fpt LIKE t_expl_fpt;
SHOW CREATE TABLE fpt_off_like_t_expl_fpt;
CREATE TEMPORARY TABLE fpt_off_like_t_expl_tmp LIKE t_expl_tmp;

--echo # Tablespace not preserved for all source tables that
--echo # had an implicitly assigned tablesapce.
CREATE TABLE fpt_off_like_t_impl_sys LIKE t_impl_sys;
SHOW CREATE TABLE fpt_off_like_t_impl_sys;
CREATE TABLE fpt_off_like_t_impl_fpt LIKE t_impl_fpt;
SHOW CREATE TABLE fpt_off_like_t_impl_fpt;
CREATE TEMPORARY TABLE fpt_off_like_t_impl_tmp LIKE t_impl_tmp;
SHOW CREATE TABLE fpt_off_like_t_impl_tmp;

--echo # The end result should be that we have .ibd files for:
--echo #   fpt_off_like_t_expl_fpt.ibd
--echo #   fpt_on_like_t_expl_fpt.ibd
--echo #   fpt_on_like_t_impl_fpt.ibd
--echo #   fpt_on_like_t_impl_sys.ibd
--echo #   t_expl_fpt.ibd
--echo #   t_impl_fpt.ibd

let $MYSQLD_DATADIR = `select @@datadir`;
--list_files $MYSQLD_DATADIR/s1/

--echo # Now, we can ALTER the tables that have implicitly
--echo # assigned tablespaces to instead assign the spaces
--echo # explicitly. This will make SHOW CREATE show the
--echo # tablespace names.
ALTER TABLE t_impl_sys TABLESPACE innodb_system;
SHOW CREATE TABLE t_impl_sys;
ALTER TABLE t_impl_fpt TABLESPACE innodb_file_per_table;
SHOW CREATE TABLE t_impl_fpt;
ALTER TABLE t_impl_tmp TABLESPACE innodb_temporary;
SHOW CREATE TABLE t_impl_tmp;

--echo # And CREATE TABLE LIKE will now preserve the
--echo # tablespace name of the above tables.

CREATE TABLE after_alter_like_t_impl_sys LIKE t_impl_sys;
SHOW CREATE TABLE after_alter_like_t_impl_sys;
CREATE TABLE after_alter_like_t_impl_fpt LIKE t_impl_fpt;
SHOW CREATE TABLE after_alter_like_t_impl_fpt;
CREATE TEMPORARY TABLE after_alter_like_t_impl_tmp LIKE t_impl_tmp;
SHOW CREATE TABLE after_alter_like_t_impl_tmp;

DROP SCHEMA s1;
DROP TABLESPACE s1;
SET GLOBAL innodb_file_per_table= DEFAULT;

--disable_query_log
call mtr.add_suppression("\\[Warning\\].* '.*tablespace.ibd' is a directory, can't delete!");
call mtr.add_suppression("\\[Warning\\].* Can't create test file");
call mtr.add_suppression("\\[ERROR\\].* File .* 'delete' returned OS error .*");
call mtr.add_suppression("\\[ERROR\\].* File .* 'stat' returned OS error .*");
call mtr.add_suppression("\\[ERROR\\].* File .*\.ibd: 'create' returned OS error .*");
call mtr.add_suppression("\\[ERROR\\].* The error means the system cannot find the path specified");
call mtr.add_suppression("\\[ERROR\\].* The error means mysqld does not have the access rights to the directory. It may also be you have created a subdirectory of the same name as a data file.");
call mtr.add_suppression("\\[ERROR\\].* Operating system error number .* in a file operation");
call mtr.add_suppression("\\[ERROR\\].* Error number .* means");
call mtr.add_suppression("\\[ERROR\\].* Cannot create file");
call mtr.add_suppression("\\[ERROR\\].* Invalid use of ':' in");
call mtr.add_suppression("\\[ERROR\\].* The file .* already exists though the corresponding table did not exist.");
call mtr.add_suppression("\\[ERROR\\].* The directory for tablespace .* does not exist or is incorrect");
call mtr.add_suppression("\\[ERROR\\].* Cannot create tablespace .* because the directory is not a valid location. The DATAFILE location must be in a known directory");
--enable_query_log