# # CREATE TABLESPACE related tests. # SET DEFAULT_STORAGE_ENGINE=InnoDB; SET NAMES utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. # restart:--innodb-directories=MYSQL_TMP_DIR SHOW VARIABLES LIKE 'innodb_directories'; Variable_name Value innodb_directories MYSQL_TMP_DIR # Strict-mode has no effect on CREATE TABLESPACE. # It rejects all invalid input, as if strict mode is always ON. SHOW VARIABLES LIKE 'innodb_strict_mode'; Variable_name Value innodb_strict_mode ON # The setting innodb_file_per_table=OFF will not prevent # CREATE TABLESPACE from working because the tablespace is # an empty shell that can contain multiple row formats. SHOW VARIABLES LIKE 'innodb_file_per_table'; Variable_name Value innodb_file_per_table ON # # Try to create a tablespace without specifying the name # CREATE TABLESPACE; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 # Try to create a tablespace with bad characters in the tablespace name identifier. # CREATE TABLESPACE 's_bad' ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''s_bad' ADD DATAFILE 's_bad.ibd'' at line 1 CREATE TABLESPACE "s_bad" ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"s_bad" ADD DATAFILE 's_bad.ibd'' at line 1 CREATE TABLESPACE `` ADD DATAFILE 's_bad.ibd'; ERROR 42000: Incorrect tablespace name `` CREATE TABLESPACE s@bad ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@bad ADD DATAFILE 's_bad.ibd'' at line 1 CREATE TABLESPACE s-bad ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-bad ADD DATAFILE 's_bad.ibd'' at line 1 CREATE TABLESPACE test/s_bad ADD DATAFILE 's_bad.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/s_bad ADD DATAFILE 's_bad.ibd'' at line 1 CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. CREATE TABLESPACE `s_too_long_file_name` ADD DATAFILE '../xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.ibd'; ERROR HY000: CREATE TABLESPACE data file must be in one of these directories './;MYSQL_TMP_DIR/'. SHOW WARNINGS; Level Code Message Error 3121 CREATE TABLESPACE data file must be in one of these directories './;MYSQL_TMP_DIR/'. Error 1528 Failed to create TABLESPACE s_too_long_file_name Error 3121 Incorrect File Name '../xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.ibd'. CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=1k FILE_BLOCK_SIZE=2k; ERROR HY000: It is not allowed to specify FILE_BLOCK_SIZE more than once CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=3k; ERROR HY000: InnoDB does not support FILE_BLOCK_SIZE=3072 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=65k; ERROR HY000: InnoDB does not support FILE_BLOCK_SIZE=66560 CREATE TABLESPACE `s_bad` ADD DATAFILE '../sub/dir/////s_bad.ibd'; ERROR HY000: The directory does not exist. SHOW WARNINGS; Level Code Message Error 3121 The directory does not exist. Error 3121 CREATE TABLESPACE data file must be in one of these directories './;MYSQL_TMP_DIR/'. Error 1528 Failed to create TABLESPACE s_bad Error 3121 Incorrect File Name '../sub/dir/////s_bad.ibd'. DROP TABLESPACE 's_bad'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''s_bad'' at line 1 DROP TABLESPACE "s_bad"; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"s_bad"' at line 1 DROP TABLESPACE ``; ERROR 42000: Incorrect tablespace name `` SHOW WARNINGS; Level Code Message Error 3119 Incorrect tablespace name `` DROP TABLESPACE s#bad; ERROR HY000: Tablespace s doesn't exist. DROP TABLESPACE s@bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@bad' at line 1 DROP TABLESPACE s-bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-bad' at line 1 DROP TABLESPACE `test/s_bad`; ERROR HY000: Tablespace test/s_bad doesn't exist. DROP TABLESPACE s_does_not_exist; ERROR HY000: Tablespace s_does_not_exist doesn't exist. # # InnoDB does not allow General tablespace names with '/' # CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 3119 Incorrect tablespace name `test/s_bad` 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 !@#$%^&*()_+-={}[]|\?<>,.`; Table Create Table t !@#$%^&*()_+-={}[]|\?<>,. CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=latin1; Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; Table Create Table t !@#$%^&*()_+-={}[]|\?<>,. CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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`; Table Create Table #sql_1 CREATE TABLE `#sql_1` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `#sql_1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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`; Table Create Table t_utf8_1 CREATE TABLE `t_utf8_1` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_Cöŀumň` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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`; Table Create Table t_utf8_2 CREATE TABLE `t_utf8_2` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_cöĿǖmň` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TABLESPACE `s_😲` ADD DATAFILE 's_utf8_c.ibd'; ERROR HY000: Invalid utf8 character string: 's_\xF0\x9F\x98\xB2' CREATE TABLESPACE `s_𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜` ADD DATAFILE 's_utf8_d.ibd'; ERROR HY000: Invalid utf8 character string: 's_\xF0\x90\x8C\x80\xF0\x90\x8C\x81\xF0\x90\x8C\x82\xF0\x90\x8C\x' CREATE TABLESPACE `s_வணக்கம்` ADD DATAFILE 'ஆவணம்.ibd'; CREATE TABLE `t_utf8_3` (a int, b text) TABLESPACE `s_வணக்கம்`; SHOW CREATE TABLE `t_utf8_3`; Table Create Table t_utf8_3 CREATE TABLE `t_utf8_3` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `s_வணக்கம்` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path #sql_1 General DEFAULT 0 Any #sql_1.ibd mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_ !@#$%^&*()_+-={}[]|\?<>,. General DEFAULT 0 Any s_utf8.ibd s_Cöŀumň General DEFAULT 0 Any s_utf8_a.ibd s_cöĿǖmň General DEFAULT 0 Any s_utf8_b.ibd s_வணக்கம் General DEFAULT 0 Any ஆவணம்.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path #sql_1 TABLESPACE InnoDB NORMAL #sql_1 MYSQLD_DATADIR/#sql_1.ibd mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_ !@#$%^&*()_+-={}[]|\?<>,. TABLESPACE InnoDB NORMAL s_ !@#$%^&*()_+-={}[]|\?<>,. MYSQLD_DATADIR/s_utf8.ibd s_Cöŀumň TABLESPACE InnoDB NORMAL s_Cöŀumň MYSQLD_DATADIR/s_utf8_a.ibd s_cöĿǖmň TABLESPACE InnoDB NORMAL s_cöĿǖmň MYSQLD_DATADIR/s_utf8_b.ibd s_வணக்கம் TABLESPACE InnoDB NORMAL s_வணக்கம் MYSQLD_DATADIR/ஆவணம்.ibd === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/@0023sql_1 #sql_1 161 7 Dynamic 0 General test/t@0020@0021@0040@0023@0024@0025@005e@0026@002a@0028@0029_@002b@002d@003d@007b@007d@005b@005d@007c@005c@003f@003c@003e@002c@002e s_ !@#$%^&*()_+-={}[]|\?<>,. 161 7 Dynamic 0 General test/t_utf8_1 s_Cöŀumň 161 7 Dynamic 0 General test/t_utf8_2 s_cöĿǖmň 161 7 Dynamic 0 General test/t_utf8_3 s_வணக்கம் 161 7 Dynamic 0 General # Directory listing of MYSQLD_DATADIR/ #sql_1.ibd lizard.ibd mysql.ibd s_utf8.ibd s_utf8_a.ibd s_utf8_b.ibd ஆவணம்.ibd CHECK TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; Table Op Msg_type Msg_text test.t !@#$%^&*()_+-={}[]|\?<>,. check status OK DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; DROP TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `; CHECK TABLE `#sql_1`; Table Op Msg_type Msg_text test.#sql_1 check status OK 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_வணக்கம்`; # # Try to create a tablespace with the reserved case-sensitive prefix 'innodb_' # CREATE TABLESPACE `innodb_system` ADD DATAFILE 's_bad.ibd'; ERROR 42000: InnoDB: `innodb_system` is a reserved tablespace name. DROP TABLESPACE `innodb_system`; ERROR 42000: InnoDB: `innodb_system` is a reserved tablespace name. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: `innodb_system` is a reserved tablespace name. Error 3119 Incorrect tablespace name `innodb_system` CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 's_InnoDB_System.ibd'; DROP TABLESPACE `InnoDB_System`; CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1'; ERROR HY000: Duplicate file name for tablespace 'InnoDB_System' SHOW WARNINGS; Level Code Message Error 3606 Duplicate file name for tablespace 'InnoDB_System' CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1.ibd'; DROP TABLESPACE `InnoDB_System`; CREATE TABLESPACE `innodb_temporary` ADD DATAFILE 's_bad.ibd'; ERROR 42000: InnoDB: `innodb_temporary` is a reserved tablespace name. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: `innodb_temporary` is a reserved tablespace name. Error 3119 Incorrect tablespace name `innodb_temporary` DROP TABLESPACE `innodb_temporary`; ERROR 42000: InnoDB: `innodb_temporary` is a reserved tablespace name. CREATE TABLESPACE `InnoDB_Temporary` ADD DATAFILE 's_InnoDB_Temporary.ibd'; DROP TABLESPACE `InnoDB_Temporary`; CREATE TABLESPACE `innodb_custom` ADD DATAFILE 's_bad.ibd'; ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved. Error 3119 Incorrect tablespace name `innodb_custom` DROP TABLESPACE `innodb_custom`; ERROR HY000: Tablespace innodb_custom doesn't exist. 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`; DROP TABLESPACE `ib_logfile0`; ERROR HY000: Tablespace ib_logfile0 doesn't exist. CREATE TABLESPACE `ib_logfile0` ADD DATAFILE 'ib_logfile0.ibd'; DROP TABLESPACE `ib_logfile0`; DROP TABLESPACE `ib_logfile1`; ERROR HY000: Tablespace ib_logfile1 doesn't exist. CREATE TABLESPACE `ib_logfile1` ADD DATAFILE 'ib_logfile1.ibd'; DROP TABLESPACE `ib_logfile1`; DROP TABLESPACE `ibdata1`; ERROR HY000: Tablespace ibdata1 doesn't exist. CREATE TABLESPACE `ibdata1` ADD DATAFILE 'ibdata1.ibd'; DROP TABLESPACE `ibdata1`; DROP TABLESPACE `undo_001`; ERROR HY000: Tablespace undo_001 doesn't exist. CREATE TABLESPACE `undo_001` ADD DATAFILE 'undo_001.ibd'; DROP TABLESPACE `undo_001`; DROP TABLESPACE `undo_002`; ERROR HY000: Tablespace undo_002 doesn't exist. CREATE TABLESPACE `undo_002` ADD DATAFILE 'undo_002.ibd'; DROP TABLESPACE `undo_002`; # # Test various forms of ADD DATAFILE # CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd' ENGINE=InnoDB; CREATE TABLESPACE `s1_#_hash` ADD DATAFILE 's1_#_hash.ibd'; CREATE TABLESPACE s1_remote ADD DATAFILE 'MYSQL_TMP_DIR/s1.ibd.ibd'; CREATE TABLESPACE s2_remote ADD DATAFILE 'MYSQL_TMP_DIR/s2_#_dir/s2.ibd'; CREATE TABLESPACE s_bad ADD DATAFILE '.ibd'; ERROR HY000: The ADD DATAFILE filepath does not have a proper filename. SHOW WARNINGS; Level Code Message Error 3121 The ADD DATAFILE filepath does not have a proper filename. Error 1528 Failed to create TABLESPACE s_bad Error 3121 Incorrect File Name '.ibd'. CREATE TABLESPACE s_bad ADD DATAFILE '../s_dir/s_subdir/.ibd'; ERROR HY000: The ADD DATAFILE filepath does not have a proper filename. SHOW WARNINGS; Level Code Message Error 3121 The ADD DATAFILE filepath does not have a proper filename. Error 3121 The directory does not exist. Error 3121 CREATE TABLESPACE data file must be in one of these directories './;MYSQL_TMP_DIR/'. Error 1528 Failed to create TABLESPACE s_bad Error 3121 Incorrect File Name '../s_dir/s_subdir/.ibd'. CREATE TABLESPACE s_bad ADD DATAFILE '../s_dir/s_bad.ibs'; ERROR HY000: The ADD DATAFILE filepath must end with '.ibd'. SHOW WARNINGS; Level Code Message Error 3121 The ADD DATAFILE filepath must end with '.ibd'. Error 3121 The directory does not exist. Error 3121 CREATE TABLESPACE data file must be in one of these directories './;MYSQL_TMP_DIR/'. Error 1528 Failed to create TABLESPACE s_bad Error 3121 Incorrect File Name '../s_dir/s_bad.ibs'. CREATE TABLESPACE s_bad ADD DATAFILE 'c:s_bad.ibd'; ERROR HY000: Incorrect File Name 'c:s_bad.ibd'. SHOW WARNINGS; Level Code Message Error 3121 Incorrect File Name 'c:s_bad.ibd'. Error 3121 Invalid use of ':'. Error 1528 Failed to create TABLESPACE s_bad Error 3121 Incorrect File Name 'c:s_bad.ibd'. CREATE TABLESPACE s_bad ADD DATAFILE 'drive:s_bad.ibd'; ERROR HY000: Incorrect File Name 'drive:s_bad.ibd'. SHOW WARNINGS; Level Code Message Error 3121 Incorrect File Name 'drive:s_bad.ibd'. Error 3121 Invalid use of ':'. Error 1528 Failed to create TABLESPACE s_bad Error 3121 Incorrect File Name 'drive:s_bad.ibd'. # # Try to create a tablespace where a same-named directory and file exist. # CREATE TABLESPACE s4_def ADD DATAFILE 'MYSQL_TMP_DIR/tablespace.ibd/s4.ibd'; CREATE TABLESPACE s5_def ADD DATAFILE 'MYSQL_TMP_DIR/tablespace.ibd/s4.ibd'; ERROR HY000: Duplicate file name for tablespace 's5_def' CREATE TABLESPACE s6_def ADD DATAFILE 'MYSQL_TMP_DIR/tablespace.ibd'; ERROR HY000: The ADD DATAFILE filepath already exists. === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s1_#_hash General DEFAULT 0 Any s1_#_hash.ibd s1_remote General DEFAULT 0 Any MYSQL_TMP_DIR/s1.ibd.ibd s2_remote General DEFAULT 0 Any MYSQL_TMP_DIR/s2_#_dir/s2.ibd s4_def General DEFAULT 0 Any MYSQL_TMP_DIR/tablespace.ibd/s4.ibd s_def General DEFAULT 0 Any s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s1_#_hash TABLESPACE InnoDB NORMAL s1_#_hash MYSQLD_DATADIR/s1_#_hash.ibd s1_remote TABLESPACE InnoDB NORMAL s1_remote MYSQL_TMP_DIR/s1.ibd.ibd s2_remote TABLESPACE InnoDB NORMAL s2_remote MYSQL_TMP_DIR/s2_#_dir/s2.ibd s4_def TABLESPACE InnoDB NORMAL s4_def MYSQL_TMP_DIR/tablespace.ibd/s4.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd # Directory listing of MYSQLD_DATADIR/ lizard.ibd mysql.ibd s1_#_hash.ibd s_def.ibd # Directory listing of MYSQL_TMP_DIR/ s1.ibd.ibd tablespace.ibd # Directory listing of MYSQL_TMP_DIR/tablespace.ibd/ s4.ibd # Directory listing of MYSQL_TMP_DIR/s2_#_dir/ s2.ibd DROP TABLESPACE s4_def; DROP TABLESPACE s1_remote; DROP TABLESPACE s2_remote; DROP TABLESPACE `s1_#_hash`; # # Try to create a tablespace that already exists. # Make sure that the tablespace name is compared in a CASE SENSITIVE way. # CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd'; ERROR HY000: Tablespace 's_def' exists. CREATE TABLESPACE s_def ADD DATAFILE 's_def_2.ibd'; ERROR HY000: Tablespace 's_def' exists. CREATE TABLESPACE S_new ADD DATAFILE 's_def.ibd'; ERROR HY000: Duplicate file name for tablespace 'S_new' CREATE TABLESPACE `s_DEF` ADD DATAFILE 's_def_2.ibd'; DROP TABLESPACE `s_DEF`; # # Make a file-per-table tablespace name and try to use it as a General tablespace # CREATE TABLE t_single (a int, b text); === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_single test/t_single 33 7 Dynamic 0 Single === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd test/t_single Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_single.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd test/t_single TABLESPACE InnoDB NORMAL test/t_single MYSQLD_DATADIR/test/t_single.ibd CREATE TABLE t_general (a int, b text) TABLESPACE test/t_single engine=InnoDB; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/t_single engine=InnoDB' at line 1 CREATE TABLE t_general (a int, b text) TABLESPACE `test/t_single` engine=InnoDB; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 3119 Incorrect tablespace name `test/t_single` CREATE TABLE t_general (a int, b text) TABLESPACE `S_Def` engine=InnoDB; ERROR HY000: Tablespace S_Def doesn't exist. SHOW WARNINGS; Level Code Message Error 3510 Tablespace S_Def doesn't exist. CREATE TABLE t_general (a int, b text) TABLESPACE `S_DEF` engine=InnoDB; ERROR HY000: Tablespace S_DEF doesn't exist. SHOW WARNINGS; Level Code Message Error 3510 Tablespace S_DEF doesn't exist. CREATE TABLESPACE `test/t_single` ADD DATAFILE 's_single.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 3119 Incorrect tablespace name `test/t_single` CREATE TABLESPACE `Test/t_Single` ADD DATAFILE 's_single.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 3119 Incorrect tablespace name `Test/t_Single` CREATE TABLESPACE `TEST/T_SINGLE` ADD DATAFILE 's_single.ibd'; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 3119 Incorrect tablespace name `TEST/T_SINGLE` DROP TABLE t_single; === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd # # Row format is not allowed on CREATE TABLESPACE # CREATE TABLESPACE s_red ADD DATAFILE 's_red.ibd' ROW_FORMAT=redundant; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROW_FORMAT=redundant' at line 1 # # Add tables to the tablespaces. # 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; SET GLOBAL innodb_file_per_table = OFF; CREATE TABLE t_dyn_in_def (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_def; # 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'); # # Try to drop a tablespace which is not empty # DROP TABLESPACE s_def; ERROR HY000: Tablespace `s_def` is not empty. === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_def_in_def s_def 161 7 Dynamic 0 General test/t_dyn_in_def s_def 161 7 Dynamic 0 General test/t_red_in_def s_def 128 7 Redundant 0 General # Directory of MYSQLD_DATADIR/ lizard.ibd mysql.ibd s_def.ibd # Directory of MYSQLD_DATADIR/test/ # # Drop the tables we no longer need. # CHECK TABLE t_dyn_in_def; Table Op Msg_type Msg_text test.t_dyn_in_def check status OK CHECK TABLE t_red_in_def; Table Op Msg_type Msg_text test.t_red_in_def check status OK DROP TABLE t_dyn_in_def; DROP TABLE t_red_in_def; # # Try to make a table using the database name with an existing table name # CREATE TABLE test/t_def_in_def (a int, b text) TABLESPACE s_def; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/t_def_in_def (a int, b text) TABLESPACE s_def' at line 1 CREATE TABLE `test/t_def_in_def` (a int, b text) TABLESPACE s_def; === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/test@002ft_def_in_def s_def 161 7 Dynamic 0 General test/t_def_in_def s_def 161 7 Dynamic 0 General === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd # Directory listing of MYSQLD_DATADIR/ lizard.ibd mysql.ibd s_def.ibd # Directory listing of MYSQLD_DATADIR/test/ # # Try to create a temporary tablespace # CREATE TEMPORARY TABLESPACE s_temp ADD DATAFILE 's_temp.ibd'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLESPACE s_temp ADD DATAFILE 's_temp.ibd'' at line 1 SHOW WARNINGS; Level Code Message Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLESPACE s_temp ADD DATAFILE 's_temp.ibd'' at line 1 # # Try to put a temporary table into a non-temporary tablespace # CREATE TEMPORARY TABLE t_temp_red (a int, b text) ROW_FORMAT=redundant TABLESPACE s_def; ERROR HY000: InnoDB: Tablespace `s_def` cannot contain TEMPORARY tables. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: Tablespace `s_def` cannot contain TEMPORARY tables. Error 1031 Table storage engine for 't_temp_red' doesn't have this option # # Try to put a compressed temporary table into the system temporary tablespace # CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=compressed TABLESPACE=`innodb_temporary`; ERROR HY000: InnoDB: Temporary tablespace `innodb_temporary` cannot contain COMPRESSED tables. SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release. Error 1478 InnoDB: Temporary tablespace `innodb_temporary` cannot contain COMPRESSED tables. Error 1031 Table storage engine for 't_temp_zip' doesn't have this option # # Try to add a second table to a single-table tablespace # SET GLOBAL innodb_file_per_table = ON; CREATE TABLE s_single (a int, b text) ROW_FORMAT=dynamic; CREATE TABLE t_second (a int, b text) TABLESPACE s_single; ERROR HY000: Tablespace s_single doesn't exist. SHOW WARNINGS; Level Code Message Error 3510 Tablespace s_single doesn't exist. CREATE TABLE t_second (a int, b text) TABLESPACE=`test/s_single`; ERROR 42000: InnoDB: A general tablespace name cannot contain '/'. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: A general tablespace name cannot contain '/'. Error 3119 Incorrect tablespace name `test/s_single` === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd test/s_single Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/s_single.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd test/s_single TABLESPACE InnoDB NORMAL test/s_single MYSQLD_DATADIR/test/s_single.ibd DROP TABLE s_single; # # Try to use both TABLESPACE and DATA DIRECTORY in the same CREATE TABLE. # # Strict mode and innodb_file_per_table should make no difference # when using general tablespaces. If TABLESPACE=innodb_file-per-table, # innodb_strict_mode will apply. # SET GLOBAL innodb_file_per_table = OFF; SET innodb_strict_mode = OFF; CREATE TABLE t3 (a int, b text) DATA DIRECTORY='MYSQL_TMP_DIR' TABLESPACE s_def; ERROR HY000: InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment. Error 1031 Table storage engine for 't3' doesn't have this option # Cannot use both a general tablespace and a DATA DIRECTORY CREATE TABLE t4 (a int, b text) TABLESPACE=s_def DATA DIRECTORY='MYSQL_TMP_DIR'; ERROR HY000: InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment. Error 1031 Table storage engine for 't4' doesn't have this option # TABLESPACE=innodb_file_per_table can be used with DATA DIRECTORY CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR'; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/' # An altered table should retain both TABLESPACE=innodb_file_per_table and the DATA DIRECTORY ALTER TABLE t4 ROW_FORMAT=dynamic, algorithm=copy; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/' DROP TABLE t4; # An empty string in DATA DIRECTORY='' indicates the current directory, # which is not allowed. CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY=''; ERROR HY000: Incorrect path value: '' SHOW WARNINGS; Level Code Message Error 1525 Incorrect path value: '' SET GLOBAL innodb_file_per_table = ON; SET innodb_strict_mode = ON; CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY=''; ERROR HY000: Incorrect path value: '' SHOW WARNINGS; Level Code Message Error 1525 Incorrect path value: '' CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='.'; ERROR HY000: Incorrect path value: '.' SHOW WARNINGS; Level Code Message Error 1525 Incorrect path value: '.' CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='test'; ERROR HY000: Incorrect path value: 'test' SHOW WARNINGS; Level Code Message Error 1525 Incorrect path value: 'test' === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/test@002ft_def_in_def s_def 161 7 Dynamic 0 General test/t_def_in_def s_def 161 7 Dynamic 0 General DROP TABLE t_def_in_def; DROP TABLE `test/t_def_in_def`; # # Create tables explicitly in the system tablespace. # 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; CREATE TABLE t_zip_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compressed; ERROR HY000: InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table SHOW WARNINGS; Level Code Message Error 1478 InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table Error 1031 Table storage engine for 't_zip_in_system' doesn't have this option === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_com_in_system innodb_system 129 7 Compact 0 System test/t_dyn_in_system innodb_system 161 7 Dynamic 0 System test/t_red_in_system innodb_system 128 7 Redundant 0 System CHECK TABLE t_red_in_system; Table Op Msg_type Msg_text test.t_red_in_system check status OK CHECK TABLE t_com_in_system; Table Op Msg_type Msg_text test.t_com_in_system check status OK CHECK TABLE t_dyn_in_system; Table Op Msg_type Msg_text test.t_dyn_in_system check status OK DROP TABLE t_red_in_system; DROP TABLE t_com_in_system; DROP TABLE t_dyn_in_system; # # Create tables explicitly as file_per_table tablespaces. # 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; CREATE TABLE t_def_as_remote (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR'; === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd test/t_com_as_file_per_table Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t_com_as_file_per_table.ibd test/t_def_as_remote Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/test/t_def_as_remote.ibd test/t_dyn_as_file_per_table Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_dyn_as_file_per_table.ibd test/t_red_as_file_per_table Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t_red_as_file_per_table.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd test/t_com_as_file_per_table TABLESPACE InnoDB NORMAL test/t_com_as_file_per_table MYSQLD_DATADIR/test/t_com_as_file_per_table.ibd test/t_def_as_remote TABLESPACE InnoDB NORMAL test/t_def_as_remote MYSQL_TMP_DIR/test/t_def_as_remote.ibd test/t_dyn_as_file_per_table TABLESPACE InnoDB NORMAL test/t_dyn_as_file_per_table MYSQLD_DATADIR/test/t_dyn_as_file_per_table.ibd test/t_red_as_file_per_table TABLESPACE InnoDB NORMAL test/t_red_as_file_per_table MYSQLD_DATADIR/test/t_red_as_file_per_table.ibd === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_com_as_file_per_table test/t_com_as_file_per_table 1 7 Compact 0 Single test/t_def_as_remote test/t_def_as_remote 97 7 Dynamic 0 Single test/t_dyn_as_file_per_table test/t_dyn_as_file_per_table 33 7 Dynamic 0 Single test/t_red_as_file_per_table test/t_red_as_file_per_table 0 7 Redundant 0 Single # Directory listing of MYSQLD_DATADIR/ lizard.ibd mysql.ibd s_def.ibd # Directory listing of MYSQLD_DATADIR/test/ t_com_as_file_per_table.ibd t_dyn_as_file_per_table.ibd t_red_as_file_per_table.ibd SHOW CREATE TABLE t_red_as_file_per_table; Table Create Table t_red_as_file_per_table CREATE TABLE `t_red_as_file_per_table` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT SHOW CREATE TABLE t_com_as_file_per_table; Table Create Table t_com_as_file_per_table CREATE TABLE `t_com_as_file_per_table` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT SHOW CREATE TABLE t_dyn_as_file_per_table; Table Create Table t_dyn_as_file_per_table CREATE TABLE `t_dyn_as_file_per_table` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC SHOW CREATE TABLE t_def_as_remote; Table Create Table t_def_as_remote CREATE TABLE `t_def_as_remote` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/' # # These explicit file_per_table tables can be moved to a general tablespace. # 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`; === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd s_multiple General DEFAULT 0 Any multiple.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s_multiple TABLESPACE InnoDB NORMAL s_multiple MYSQLD_DATADIR/multiple.ibd === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_com_was_file_per_table s_multiple 129 7 Compact 0 General test/t_def_was_remote s_multiple 161 7 Dynamic 0 General test/t_dyn_was_file_per_table s_multiple 161 7 Dynamic 0 General test/t_red_was_file_per_table s_multiple 128 7 Redundant 0 General # Directory listing of MYSQLD_DATADIR/ lizard.ibd multiple.ibd mysql.ibd s_def.ibd # Directory listing of MYSQLD_DATADIR/test/ # # Tables in a general tablespace can be moved to file_per_table locations. # 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`; # Note that MySQL ignores DATA DIRECTORY on all ALTER TABLE statements. ALTER TABLE `t_def_was_remote` TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='MYSQL_TMP_DIR', RENAME TO `t_def_to_file_per_table`; Warnings: Warning 1618 option ignored === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd s_multiple General DEFAULT 0 Any multiple.ibd test/t_com_to_file_per_table Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t_com_to_file_per_table.ibd test/t_def_to_file_per_table Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_def_to_file_per_table.ibd test/t_dyn_to_file_per_table Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_dyn_to_file_per_table.ibd test/t_red_to_file_per_table Single DEFAULT 0 Compact or Redundant MYSQLD_DATADIR/test/t_red_to_file_per_table.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s_multiple TABLESPACE InnoDB NORMAL s_multiple MYSQLD_DATADIR/multiple.ibd test/t_com_to_file_per_table TABLESPACE InnoDB NORMAL test/t_com_to_file_per_table MYSQLD_DATADIR/test/t_com_to_file_per_table.ibd test/t_def_to_file_per_table TABLESPACE InnoDB NORMAL test/t_def_to_file_per_table MYSQLD_DATADIR/test/t_def_to_file_per_table.ibd test/t_dyn_to_file_per_table TABLESPACE InnoDB NORMAL test/t_dyn_to_file_per_table MYSQLD_DATADIR/test/t_dyn_to_file_per_table.ibd test/t_red_to_file_per_table TABLESPACE InnoDB NORMAL test/t_red_to_file_per_table MYSQLD_DATADIR/test/t_red_to_file_per_table.ibd === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_com_to_file_per_table test/t_com_to_file_per_table 1 7 Compact 0 Single test/t_def_to_file_per_table test/t_def_to_file_per_table 33 7 Dynamic 0 Single test/t_dyn_to_file_per_table test/t_dyn_to_file_per_table 33 7 Dynamic 0 Single test/t_red_to_file_per_table test/t_red_to_file_per_table 0 7 Redundant 0 Single # Directory listing of MYSQLD_DATADIR/ lizard.ibd multiple.ibd mysql.ibd s_def.ibd # Directory listing of MYSQLD_DATADIR/test/ t_com_to_file_per_table.ibd t_def_to_file_per_table.ibd t_dyn_to_file_per_table.ibd t_red_to_file_per_table.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; # # Try the syntax that InnoDB does not support. # # Try NDB settings for CREATE TABLESPACE. These are currently accepted # even if they probably should not be. This is valid syntax from the # 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; # # Checking LOGFILE GROUP commands (only supported by NDB) # CREATE LOGFILE GROUP s_bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 CREATE LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat'; ERROR HY000: Feature LOGFILE GROUP is unsupported (by InnoDB). ALTER LOGFILE GROUP s_bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 ALTER LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat' INITIAL_SIZE = 100M, NO_WAIT; ERROR HY000: Feature LOGFILE GROUP is unsupported (by InnoDB). DROP LOGFILE GROUP s_bad; ERROR HY000: Feature LOGFILE GROUP is unsupported (by InnoDB). # # Checking ALTER TABLESPACE (only NDB in 5.7) # CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd'; ALTER TABLESPACE s_bad; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd'; ERROR HY000: Failed to alter: TABLESPACE s_bad SHOW WARNINGS; Level Code Message Error 1533 Failed to alter: TABLESPACE s_bad Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... ADD DATAFILE ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd'; ERROR HY000: Failed to alter: TABLESPACE s_bad SHOW WARNINGS; Level Code Message Error 1533 Failed to alter: TABLESPACE s_bad Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... DROP DATAFILE ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd' INITIAL_SIZE = 1G; ERROR HY000: Failed to alter: TABLESPACE s_bad SHOW WARNINGS; Level Code Message Error 1533 Failed to alter: TABLESPACE s_bad Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... ADD DATAFILE ALTER TABLESPACE s_def ADD DATAFILE 'bad2.ibd' NO_WAIT; ERROR HY000: Failed to alter: TABLESPACE s_def SHOW WARNINGS; Level Code Message Error 1533 Failed to alter: TABLESPACE s_def Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... ADD DATAFILE ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd' INITIAL_SIZE = 1G; ERROR HY000: Failed to alter: TABLESPACE s_bad SHOW WARNINGS; Level Code Message Error 1533 Failed to alter: TABLESPACE s_bad Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... DROP DATAFILE ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd' NO_WAIT; ERROR HY000: Failed to alter: TABLESPACE s_bad SHOW WARNINGS; Level Code Message Error 1533 Failed to alter: TABLESPACE s_bad Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... DROP DATAFILE # # Undocumented ALTER TABLESPACE syntax which was removed by wl#8972 # ALTER TABLESPACE s_bad CHANGE 's_bad.ibd' INITIAL_SIZE = 1G, AUTOEXTEND_SIZE = 100M, MAX_SIZE = 2G; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHANGE 's_bad.ibd' INITIAL_SIZE = 1G, AUTOEXTEND_SIZE = 100M, MAX_SIZE = 2' at line 1 ALTER TABLESPACE s_bad READ_ONLY; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'READ_ONLY' at line 1 ALTER TABLESPACE s_bad READ_WRITE; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'READ_WRITE' at line 1 ALTER TABLESPACE s_bad NOT ACCESSIBLE; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT ACCESSIBLE' at line 1 DROP TABLESPACE s_bad; # # Try the syntax that MySQL docs say is ignored. # CREATE TABLESPACE s_ignore1 ADD DATAFILE 's_ignore1.ibd' AUTOEXTEND_SIZE = 10M; CREATE TABLESPACE s_ignore2 ADD DATAFILE 's_ignore2.ibd' MAX_SIZE = 1G; CREATE TABLESPACE s_ignore3 ADD DATAFILE 's_ignore3.ibd' NODEGROUP = 5; CREATE TABLESPACE s_ignore4 ADD DATAFILE 's_ignore4.ibd' WAIT; CREATE TABLESPACE s_ignore5 ADD DATAFILE 's_ignore5.ibd' COMMENT = 'This comment is ignored'; === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd s_ignore1 General DEFAULT 0 Any s_ignore1.ibd s_ignore2 General DEFAULT 0 Any s_ignore2.ibd s_ignore3 General DEFAULT 0 Any s_ignore3.ibd s_ignore4 General DEFAULT 0 Any s_ignore4.ibd s_ignore5 General DEFAULT 0 Any s_ignore5.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s_ignore1 TABLESPACE InnoDB NORMAL s_ignore1 MYSQLD_DATADIR/s_ignore1.ibd s_ignore2 TABLESPACE InnoDB NORMAL s_ignore2 MYSQLD_DATADIR/s_ignore2.ibd s_ignore3 TABLESPACE InnoDB NORMAL s_ignore3 MYSQLD_DATADIR/s_ignore3.ibd s_ignore4 TABLESPACE InnoDB NORMAL s_ignore4 MYSQLD_DATADIR/s_ignore4.ibd s_ignore5 TABLESPACE InnoDB NORMAL s_ignore5 MYSQLD_DATADIR/s_ignore5.ibd DROP TABLESPACE s_ignore1; DROP TABLESPACE s_ignore2; DROP TABLESPACE s_ignore3; DROP TABLESPACE s_ignore4; DROP TABLESPACE s_ignore5; # # Try various ALTER TABLE statements. # 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; a left(b,50) 1 a short string - a short string - a short string - 2 a long string - a long string - a long string - a 3 a short string - a short string - a short string - 4 a long string - a long string - a long string - a SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_in_def s_def 161 7 Dynamic 0 General ALTER TABLE t_in_def ROW_FORMAT=redundant; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT ALTER TABLE t_in_def ROW_FORMAT=dynamic; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def ADD COLUMN (c int); SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def ADD INDEX c (c); SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`), KEY `c` (`c`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def DROP INDEX c; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def DROP COLUMN c; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def AUTO_INCREMENT=100; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC INSERT INTO t_in_def(b) VALUES("after ALTER TABLE AUTO_INCREMENT"); SELECT a, left(b,50) FROM t_in_def; a left(b,50) 1 a short string - a short string - a short string - 2 a long string - a long string - a long string - a 3 a short string - a short string - a short string - 4 a long string - a long string - a long string - a 100 after ALTER TABLE AUTO_INCREMENT SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def CHANGE a aa int; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `b` text, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def CHANGE b bb varchar(1000); SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def DROP PRIMARY KEY; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, UNIQUE KEY `a` (`aa`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def ADD PRIMARY KEY aa(aa), ALGORITHM=INPLACE; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def ADD INDEX ab(aa,bb(25)), ALGORITHM=COPY; SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`,`bb`(25)) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_in_def ADD INDEX bb(bb(50)); SHOW CREATE TABLE t_in_def; Table Create Table t_in_def CREATE TABLE `t_in_def` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`,`bb`(25)), KEY `bb` (`bb`(50)) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC RENAME TABLE t_in_def TO t; SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`,`bb`(25)), KEY `bb` (`bb`(50)) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t s_def 161 7 Dynamic 0 General INSERT INTO t VALUES(200, "after RENAME TABLE t_in_def TO t"); SELECT aa, left(bb,50) FROM t; aa left(bb,50) 1 a short string - a short string - a short string - 2 a long string - a long string - a long string - a 3 a short string - a short string - a short string - 4 a long string - a long string - a long string - a 100 after ALTER TABLE AUTO_INCREMENT 200 after RENAME TABLE t_in_def TO t # # Move tables between tablespaces. # CREATE TABLESPACE s_alt1 ADD DATAFILE 's_alt1.ibd'; CREATE TABLESPACE s_alt2 ADD DATAFILE 's_alt2.ibd'; === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_alt1 General DEFAULT 0 Any s_alt1.ibd s_alt2 General DEFAULT 0 Any s_alt2.ibd s_def General DEFAULT 0 Any s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_alt1 TABLESPACE InnoDB NORMAL s_alt1 MYSQLD_DATADIR/s_alt1.ibd s_alt2 TABLESPACE InnoDB NORMAL s_alt2 MYSQLD_DATADIR/s_alt2.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd ALTER TABLE t RENAME TO t_nomad, TABLESPACE s_alt1; SHOW CREATE TABLE t_nomad; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`,`bb`(25)), KEY `bb` (`bb`(50)) ) /*!50100 TABLESPACE `s_alt1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_nomad s_alt1 161 7 Dynamic 0 General ALTER TABLE t_nomad TABLESPACE s_alt2; SHOW CREATE TABLE t_nomad; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `aa` int(11) NOT NULL, `bb` varchar(1000) DEFAULT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`,`bb`(25)), KEY `bb` (`bb`(50)) ) /*!50100 TABLESPACE `s_alt2` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_nomad s_alt2 161 7 Dynamic 0 General ALTER TABLE t_nomad TABLESPACE s_def, DROP COLUMN bb; SHOW CREATE TABLE t_nomad; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `aa` int(11) NOT NULL, PRIMARY KEY (`aa`), UNIQUE KEY `a` (`aa`), KEY `ab` (`aa`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_nomad s_def 161 6 Dynamic 0 General === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_alt1 General DEFAULT 0 Any s_alt1.ibd s_alt2 General DEFAULT 0 Any s_alt2.ibd s_def General DEFAULT 0 Any s_def.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_alt1 TABLESPACE InnoDB NORMAL s_alt1 MYSQLD_DATADIR/s_alt1.ibd s_alt2 TABLESPACE InnoDB NORMAL s_alt2 MYSQLD_DATADIR/s_alt2.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd DROP TABLE t_nomad; DROP TABLESPACE s_alt1; DROP TABLESPACE s_alt2; # # Move a table from the System Tablespace into a General Tablespace # and then back out to the system tablespace again. # 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; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_system innodb_system 33 7 Dynamic 0 System ALTER TABLE t_system TABLESPACE s_def; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_system s_def 161 7 Dynamic 0 General ALTER TABLE t_system TABLESPACE=`innodb_system`; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_system innodb_system 161 7 Dynamic 0 System # # Do a few ALTER TABLES for this table that was moved out and back into the system tablespace. # ALTER TABLE t_system ROW_FORMAT=redundant; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT ALTER TABLE t_system ROW_FORMAT=dynamic; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_system ADD COLUMN (c int); SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_system ADD INDEX c (c); SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`), KEY `c` (`c`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_system DROP INDEX c; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ALTER TABLE t_system DROP COLUMN c; SHOW CREATE TABLE t_system; Table Create Table t_system CREATE TABLE `t_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC # # Now try to put that table into its own Single-Table tablespace. # It is not allowed since the table is now marked with the system tablespace # SET GLOBAL innodb_file_per_table=ON; ALTER TABLE t_system RENAME TO t_still_system; SHOW CREATE TABLE t_still_system; Table Create Table t_still_system CREATE TABLE `t_still_system` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_still_system innodb_system 161 7 Dynamic 0 System DROP TABLE t_still_system; # # Try to create or move a table into the temporary tablespace. # CREATE TABLE t_not_temp (a int, b text) TABLESPACE=`innodb_temporary`; ERROR HY000: InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables. Error 1031 Table storage engine for 't_not_temp' doesn't have this option CREATE TABLE t_not_temp (a int, b text) TABLESPACE s_def; ALTER TABLE t_not_temp TABLESPACE=`innodb_temporary`; ERROR HY000: InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables. SHOW WARNINGS; Level Code Message Error 1478 InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables. Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE' DROP TABLE t_not_temp; SET SESSION innodb_strict_mode=OFF; CREATE TEMPORARY TABLE t_my_temp (a int, b text) TABLESPACE=`innodb_temporary`; Warnings: Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release. SHOW CREATE TABLE t_my_temp; Table Create Table t_my_temp CREATE TEMPORARY TABLE `t_my_temp` ( `a` int(11) DEFAULT NULL, `b` text ) /*!50100 TABLESPACE `innodb_temporary` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP TABLE t_my_temp; SET SESSION innodb_strict_mode=ON; # # Try to create or move a table into the redo tablespace # or any tablespace using the reserved `innodb_` prefix. # CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_redo_log`; ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved. Error 3119 Incorrect tablespace name `innodb_redo_log` CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_anything`; ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved. Error 3119 Incorrect tablespace name `innodb_anything` CREATE TABLE t_data (a int, b text) TABLESPACE s_def; ALTER TABLE t_data TABLESPACE=`innodb_redo_log`; ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved. Error 3119 Incorrect tablespace name `innodb_redo_log` ALTER TABLE t_data TABLESPACE=`innodb_anything`; ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved. SHOW WARNINGS; Level Code Message Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved. Error 3119 Incorrect tablespace name `innodb_anything` DROP TABLE t_data; # # Move a table from a Single-Table tablespace into a General Tablespace. # 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; Table Create Table t_single CREATE TABLE `t_single` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_single test/t_single 33 7 Dynamic 0 Single ALTER TABLE t_single TABLESPACE s_def; SHOW CREATE TABLE t_single; Table Create Table t_single CREATE TABLE `t_single` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` text, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_single s_def 161 7 Dynamic 0 General DROP TABLE t_single; # # Move a MyISAM table into an InnoDB General Tablespace and back. # CREATE TABLE t_myisam (a serial key, b int) ENGINE=MyISAM; INSERT INTO t_myisam(b) VALUES(11); SHOW CREATE TABLE t_myisam; Table Create Table t_myisam CREATE TABLE `t_myisam` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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; Table Create Table t_innodb CREATE TABLE `t_innodb` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_innodb s_def 161 7 Dynamic 0 General ALTER TABLE t_innodb RENAME TO t_myisam, ENGINE=MyISAM; INSERT INTO t_myisam(b) VALUES(33); SELECT * FROM t_myisam; a b 1 11 2 22 3 33 SHOW CREATE TABLE t_myisam; Table Create Table t_myisam CREATE TABLE `t_myisam` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ALTER TABLE t_myisam RENAME TO t_innodb, ENGINE=InnoDB; INSERT INTO t_innodb(b) VALUES(44); SHOW CREATE TABLE t_innodb; Table Create Table t_innodb CREATE TABLE `t_innodb` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_innodb s_def 161 7 Dynamic 0 General DROP TABLE t_innodb; # # Move a Dynamic table from an InnoDB General tablespace to MyISAM # and Memory and back without having to re-specify the tablespace # and row_format. # 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; Table Create Table t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_dyn_in_s_def s_def 161 7 Dynamic 0 General 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; Table Create Table t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single 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; Table Create Table t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_dyn_in_s_def s_def 161 7 Dynamic 0 General 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; Table Create Table t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single 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; a b 1 11 2 22 3 33 4 44 5 55 SHOW CREATE TABLE t_dyn_in_s_def; Table Create Table t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_dyn_in_s_def s_def 161 7 Dynamic 0 General CHECK TABLE t_dyn_in_s_def; Table Op Msg_type Msg_text test.t_dyn_in_s_def check status OK DROP TABLE t_dyn_in_s_def; # # If a tablespace name is associated with a table in another SE, and that tablespace # does not exist in InnoDB, the ALTER TABLE will fail # 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; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_short_life` */ ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_nomad s_short_life 161 7 Dynamic 0 General ALTER TABLE t_nomad ENGINE=MyISAM; INSERT INTO t_nomad(b) VALUES(22); SHOW CREATE TABLE t_nomad; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_short_life` */ ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single DROP TABLESPACE s_short_life; ALTER TABLE t_nomad ENGINE=InnoDB; ERROR HY000: Tablespace s_short_life doesn't exist. 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; Table Create Table t_nomad CREATE TABLE `t_nomad` ( `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `a` (`a`) ) /*!50100 TABLESPACE `s_shorter_life` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_nomad s_shorter_life 161 7 Dynamic 0 General DROP TABLE t_nomad; DROP TABLESPACE s_shorter_life; # # Show that a table with the same SQL name can be created in two different # databases, whether they are in the system tablespace, a general tablespace # or their own file-per-table tablespace. # Also show that DROP DATABASE will remove tables from inside a General Tablespace # without removing the tablespace or other tables in it for another databases. # 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; Tables_in_test t_general t_single t_system CREATE DATABASE test1; USE test1; 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; Tables_in_test1 t_general t_single t_system USE test; === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_general s_def 161 7 Dynamic 0 General test/t_single test/t_single 33 7 Dynamic 0 Single test/t_system innodb_system 33 7 Dynamic 0 System test1/t_general s_def 161 7 Dynamic 0 General test1/t_single test1/t_single 33 7 Dynamic 0 Single test1/t_system innodb_system 33 7 Dynamic 0 System === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd s_empty1 General DEFAULT 0 Any s_empty1.ibd test/t_single Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_single.ibd test1/t_single Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test1/t_single.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s_empty1 TABLESPACE InnoDB NORMAL s_empty1 MYSQLD_DATADIR/s_empty1.ibd test/t_single TABLESPACE InnoDB NORMAL test/t_single MYSQLD_DATADIR/test/t_single.ibd test1/t_single TABLESPACE InnoDB NORMAL test1/t_single MYSQLD_DATADIR/test1/t_single.ibd DROP DATABASE test1; === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t_general s_def 161 7 Dynamic 0 General test/t_single test/t_single 33 7 Dynamic 0 Single test/t_system innodb_system 33 7 Dynamic 0 System === information_schema.innodb_tablespaces and innodb_datafiles === Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path mtr/global_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions Single DEFAULT 0 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def General DEFAULT 0 Any s_def.ibd s_empty1 General DEFAULT 0 Any s_empty1.ibd test/t_single Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t_single.ibd === information_schema.files === Space_Name File_Type Engine Status Tablespace_Name Path mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd s_empty1 TABLESPACE InnoDB NORMAL s_empty1 MYSQLD_DATADIR/s_empty1.ibd test/t_single TABLESPACE InnoDB NORMAL test/t_single MYSQLD_DATADIR/test/t_single.ibd DROP TABLE t_system; DROP TABLE t_general; DROP TABLE t_single; DROP TABLESPACE s_empty1; # # Assign different tablespace for PK-FK tables # 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); === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t1 s1 161 6 Dynamic 0 General test/t2 innodb_system 33 6 Dynamic 0 System SELECT * FROM t1; a 100 200 SELECT * FROM t2; b 100 SELECT * FROM t1,t2 WHERE t1.a=t2.b; a b 100 100 DROP TABLE t2,t1; DROP TABLESPACE s1; # # Assigning general tablespace from procedure # 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)")) ; CREATE PROCEDURE p1() BEGIN ALTER TABLE t1 TABLESPACE=s1; END| === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t1 innodb_system 33 6 Dynamic 0 System CALL p1(); === information_schema.innodb_tables and innodb_tablespaces === Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type mtr/global_suppressions mtr/global_suppressions 33 6 Dynamic 0 Single mtr/test_suppressions mtr/test_suppressions 33 6 Dynamic 0 Single test/t1 s1 161 6 Dynamic 0 General DROP PROCEDURE p1; # # Show that DISCARD/IMPORT tablespace does not work on a general tablespace. # ALTER TABLE t1 DISCARD TABLESPACE; ERROR 42000: InnoDB: Cannot discard table `test/t1` because it is in a general tablespace. It must be file-per-table. SHOW WARNINGS; Level Code Message Error 1148 InnoDB: Cannot discard table `test/t1` because it is in a general tablespace. It must be file-per-table. Error 1148 The used command is not allowed with this MySQL version ALTER TABLE t1 IMPORT TABLESPACE; ERROR 42000: InnoDB: Cannot import table `test/t1` because it is in a general tablespace. It must be file-per-table. SHOW WARNINGS; Level Code Message Error 1148 InnoDB: Cannot import table `test/t1` because it is in a general tablespace. It must be file-per-table. Error 1148 The used command is not allowed with this MySQL version DROP TABLE t1; DROP TABLESPACE s1; # # Clean-up. # DROP TABLESPACE s_def; SET GLOBAL innodb_file_per_table=default; SET GLOBAL innodb_strict_mode=default; # # Bug #20763179 SEGV WHEN CREATE TABLESPACE IS EXECUTED IN READ ONLY MODE # # Check CREATE TABLESPACE is handled properly in read-only mode CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB; # restart: --innodb_read_only --innodb-directories=MYSQL_TMP_DIR CREATE TABLESPACE s2 ADD DATAFILE 's2.ibd' ENGINE InnoDB; ERROR HY000: Running in read-only mode SHOW WARNINGS; Level Code Message Warning 1836 InnoDB: Running in read-only mode Error 1836 Running in read-only mode DROP TABLESPACE s1; ERROR HY000: Changes to undo tablespaces are not allowed in read_only mode SHOW WARNINGS; Level Code Message Error 1874 Changes to undo tablespaces are not allowed in read_only mode Error 1529 Failed to drop TABLESPACE s1 Error 1874 InnoDB is in read only mode. # Restart in normal mode for cleanup. # restart --innodb-directories=MYSQL_TMP_DIR DROP TABLESPACE s1; # # Bug#26199233 create like ignores innodb_file_per_table # # - Verify if table picks correct tablespace based on # innodb_file_per_table settings. # - Check if SHOW CREATE displays correct tablespace name. CREATE DATABASE db1; SET @@global.innodb_file_per_table = OFF; # Case 1 # SHOW CREATE should not show TABLESPACE clause after the fix. # Because it was not explicitly specified by user. CREATE TABLE db1.t1 (c1 INT) ENGINE=InnoDB; SHOW CREATE TABLE db1.t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SET @@global.innodb_file_per_table = ON; # Case 2 # After the fix table, t2 should use innodb_file_per_table tablespace. CREATE TABLE db1.t2 LIKE db1.t1; SHOW CREATE TABLE db1.t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # Verify the same by listing the .ibd file. t2.ibd # Case 3 # 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; Table Create Table tables CREATE TABLE `tables` ( `c1` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `ts0` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP TABLE db1.tables; DROP TABLESPACE ts0; # Cleanup DROP DATABASE db1; # Additional test cases for Bug#26199233 to # test various combinations. CREATE SCHEMA s1; USE s1; CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd'; # # Tables with explicitly assigned tablespaces. # # Table with an explicitly assigned user tablespace. CREATE TABLE t_expl_usr (i INT) TABLESPACE s1; SHOW CREATE TABLE t_expl_usr; Table Create Table t_expl_usr CREATE TABLE `t_expl_usr` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # # Table with an explicitly assigned system tablespace. CREATE TABLE t_expl_sys (i INT) TABLESPACE innodb_system; SHOW CREATE TABLE t_expl_sys; Table Create Table t_expl_sys CREATE TABLE `t_expl_sys` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # # 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; Table Create Table t_expl_fpt CREATE TABLE `t_expl_fpt` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # # 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; Warnings: Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release. SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release. SET SESSION innodb_strict_mode=ON; # # Tables with implicitly assigned tablespaces. # # 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; Table Create Table t_impl_sys CREATE TABLE `t_impl_sys` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # # 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; Table Create Table t_impl_fpt CREATE TABLE `t_impl_fpt` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # # Temporary table with an implicitly assigned tmp tablespace. CREATE TEMPORARY TABLE t_impl_tmp (i INT); SHOW CREATE TABLE t_impl_tmp; Table Create Table t_impl_tmp CREATE TEMPORARY TABLE `t_impl_tmp` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # # CREATE LIKE when innodb_file_per_table is ON. # # Tablespace preserved for all source tables that # 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; Table Create Table fpt_on_like_t_expl_usr CREATE TABLE `fpt_on_like_t_expl_usr` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TABLE fpt_on_like_t_expl_sys LIKE t_expl_sys; SHOW CREATE TABLE fpt_on_like_t_expl_sys; Table Create Table fpt_on_like_t_expl_sys CREATE TABLE `fpt_on_like_t_expl_sys` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TABLE fpt_on_like_t_expl_fpt LIKE t_expl_fpt; SHOW CREATE TABLE fpt_on_like_t_expl_fpt; Table Create Table fpt_on_like_t_expl_fpt CREATE TABLE `fpt_on_like_t_expl_fpt` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SET SESSION innodb_strict_mode=OFF; CREATE TEMPORARY TABLE fpt_on_like_t_expl_tmp LIKE t_expl_tmp; Warnings: Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release. SHOW WARNINGS; Level Code Message Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release. SET SESSION innodb_strict_mode=OFF; # Tablespace not preserved for all source tables that # 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; Table Create Table fpt_on_like_t_impl_sys CREATE TABLE `fpt_on_like_t_impl_sys` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TABLE fpt_on_like_t_impl_fpt LIKE t_impl_fpt; SHOW CREATE TABLE fpt_on_like_t_impl_fpt; Table Create Table fpt_on_like_t_impl_fpt CREATE TABLE `fpt_on_like_t_impl_fpt` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TEMPORARY TABLE fpt_on_like_t_impl_tmp LIKE t_impl_tmp; SHOW CREATE TABLE fpt_on_like_t_impl_tmp; Table Create Table fpt_on_like_t_impl_tmp CREATE TEMPORARY TABLE `fpt_on_like_t_impl_tmp` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # # CREATE LIKE when innodb_file_per_table is OFF. # # Tablespace preserved for all source tables that # 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; Table Create Table fpt_off_like_t_expl_usr CREATE TABLE `fpt_off_like_t_expl_usr` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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; Table Create Table fpt_off_like_t_expl_fpt CREATE TABLE `fpt_off_like_t_expl_fpt` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TEMPORARY TABLE fpt_off_like_t_expl_tmp LIKE t_expl_tmp; Warnings: Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release. # Tablespace not preserved for all source tables that # 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; Table Create Table fpt_off_like_t_impl_sys CREATE TABLE `fpt_off_like_t_impl_sys` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TABLE fpt_off_like_t_impl_fpt LIKE t_impl_fpt; SHOW CREATE TABLE fpt_off_like_t_impl_fpt; Table Create Table fpt_off_like_t_impl_fpt CREATE TABLE `fpt_off_like_t_impl_fpt` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TEMPORARY TABLE fpt_off_like_t_impl_tmp LIKE t_impl_tmp; SHOW CREATE TABLE fpt_off_like_t_impl_tmp; Table Create Table fpt_off_like_t_impl_tmp CREATE TEMPORARY TABLE `fpt_off_like_t_impl_tmp` ( `i` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # The end result should be that we have .ibd files for: # fpt_off_like_t_expl_fpt.ibd # fpt_on_like_t_expl_fpt.ibd # fpt_on_like_t_impl_fpt.ibd # fpt_on_like_t_impl_sys.ibd # t_expl_fpt.ibd # t_impl_fpt.ibd fpt_off_like_t_expl_fpt.ibd fpt_on_like_t_expl_fpt.ibd fpt_on_like_t_impl_fpt.ibd fpt_on_like_t_impl_sys.ibd t_expl_fpt.ibd t_impl_fpt.ibd # Now, we can ALTER the tables that have implicitly # assigned tablespaces to instead assign the spaces # explicitly. This will make SHOW CREATE show the # tablespace names. ALTER TABLE t_impl_sys TABLESPACE innodb_system; SHOW CREATE TABLE t_impl_sys; Table Create Table t_impl_sys CREATE TABLE `t_impl_sys` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ALTER TABLE t_impl_fpt TABLESPACE innodb_file_per_table; SHOW CREATE TABLE t_impl_fpt; Table Create Table t_impl_fpt CREATE TABLE `t_impl_fpt` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ALTER TABLE t_impl_tmp TABLESPACE innodb_temporary; Warnings: Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release. SHOW CREATE TABLE t_impl_tmp; Table Create Table t_impl_tmp CREATE TEMPORARY TABLE `t_impl_tmp` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_temporary` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # And CREATE TABLE LIKE will now preserve the # 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; Table Create Table after_alter_like_t_impl_sys CREATE TABLE `after_alter_like_t_impl_sys` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TABLE after_alter_like_t_impl_fpt LIKE t_impl_fpt; SHOW CREATE TABLE after_alter_like_t_impl_fpt; Table Create Table after_alter_like_t_impl_fpt CREATE TABLE `after_alter_like_t_impl_fpt` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci CREATE TEMPORARY TABLE after_alter_like_t_impl_tmp LIKE t_impl_tmp; Warnings: Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release. SHOW CREATE TABLE after_alter_like_t_impl_tmp; Table Create Table after_alter_like_t_impl_tmp CREATE TEMPORARY TABLE `after_alter_like_t_impl_tmp` ( `i` int(11) DEFAULT NULL ) /*!50100 TABLESPACE `innodb_temporary` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP SCHEMA s1; DROP TABLESPACE s1; SET GLOBAL innodb_file_per_table= DEFAULT;