2100 lines
101 KiB
Plaintext
2100 lines
101 KiB
Plaintext
#
|
||
# 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 <DATA DIRECTORY> 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;
|