Debian Codegraph @

mysql-test/suite/innodb/r/create_tablespace.result

raw
1 #
2 # CREATE TABLESPACE related tests.
3 #
4 SET DEFAULT_STORAGE_ENGINE=InnoDB;
5 SET NAMES utf8;
6 Warnings:
7 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.
8 SHOW VARIABLES LIKE 'innodb_directories';
9 Variable_name Value
10 innodb_directories MYSQL_TMP_DIR
11 # Strict-mode has no effect on CREATE TABLESPACE.
12 # It rejects all invalid input, as if strict mode is always ON.
13 SHOW VARIABLES LIKE 'innodb_strict_mode';
14 Variable_name Value
15 innodb_strict_mode ON
16 # The setting innodb_file_per_table=OFF will not prevent
17 # CREATE TABLESPACE from working because the tablespace is
18 # an empty shell that can contain multiple row formats.
19 SHOW VARIABLES LIKE 'innodb_file_per_table';
20 Variable_name Value
21 innodb_file_per_table ON
22 #
23 # Try to create a tablespace without specifying the name
24 #
25 CREATE TABLESPACE;
26 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
27 # Try to create a tablespace with bad characters in the tablespace name identifier.
28 #
29 CREATE TABLESPACE 's_bad' ADD DATAFILE 's_bad.ibd';
30 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
31 CREATE TABLESPACE "s_bad" ADD DATAFILE 's_bad.ibd';
32 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
33 CREATE TABLESPACE `` ADD DATAFILE 's_bad.ibd';
34 ERROR 42000: Incorrect tablespace name ``
35 CREATE TABLESPACE s@bad ADD DATAFILE 's_bad.ibd';
36 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
37 CREATE TABLESPACE s-bad ADD DATAFILE 's_bad.ibd';
38 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
39 CREATE TABLESPACE test/s_bad ADD DATAFILE 's_bad.ibd';
40 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
41 CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd';
42 ERROR 42000: InnoDB: A general tablespace name cannot contain '/'.
43 CREATE TABLESPACE `s_too_long_file_name` ADD DATAFILE '../xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.ibd';
44 ERROR HY000: The DATAFILE location must be in a known directory.
45 SHOW WARNINGS;
46 Level Code Message
47 Error 3121 The DATAFILE location must be in a known directory.
48 Error 1528 Failed to create TABLESPACE s_too_long_file_name
49 Error 3121 Incorrect File Name '../xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.ibd'.
50 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=1k FILE_BLOCK_SIZE=2k;
51 ERROR HY000: It is not allowed to specify FILE_BLOCK_SIZE more than once
52 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=3k;
53 ERROR HY000: InnoDB does not support FILE_BLOCK_SIZE=3072
54 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=65k;
55 ERROR HY000: InnoDB does not support FILE_BLOCK_SIZE=66560
56 CREATE TABLESPACE `s_bad` ADD DATAFILE '../sub/dir/////s_bad.ibd';
57 ERROR HY000: The directory does not exist or is incorrect.
58 SHOW WARNINGS;
59 Level Code Message
60 Error 3121 The directory does not exist or is incorrect.
61 Error 3121 The DATAFILE location must be in a known directory.
62 Error 1528 Failed to create TABLESPACE s_bad
63 Error 3121 Incorrect File Name '../sub/dir/////s_bad.ibd'.
64 DROP TABLESPACE 's_bad';
65 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
66 DROP TABLESPACE "s_bad";
67 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
68 DROP TABLESPACE ``;
69 ERROR 42000: Incorrect tablespace name ``
70 SHOW WARNINGS;
71 Level Code Message
72 Error 3119 Incorrect tablespace name ``
73 DROP TABLESPACE s#bad;
74 ERROR HY000: Tablespace s doesn't exist.
75 DROP TABLESPACE s@bad;
76 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
77 DROP TABLESPACE s-bad;
78 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
79 DROP TABLESPACE `test/s_bad`;
80 ERROR HY000: Tablespace test/s_bad doesn't exist.
81 DROP TABLESPACE s_does_not_exist;
82 ERROR HY000: Tablespace s_does_not_exist doesn't exist.
83 #
84 # InnoDB does not allow General tablespace names with '/'
85 #
86 CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd';
87 ERROR 42000: InnoDB: A general tablespace name cannot contain '/'.
88 SHOW WARNINGS;
89 Level Code Message
90 Error 3119 InnoDB: A general tablespace name cannot contain '/'.
91 Error 3119 Incorrect tablespace name `test/s_bad`
92 CREATE TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` ADD DATAFILE 's_utf8.ibd';
93 CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` (a int, b text) TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `;
94 INSERT INTO `t !@#$%^&*()_+-={}[]|\?<>,.` VALUES(1,'one');
95 SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
96 Table Create Table
97 t !@#$%^&*()_+-={}[]|\?<>,. CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` (
98 `a` int DEFAULT NULL,
99 `b` text
100 ) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
101 CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2` (
102 `a` int(11) DEFAULT NULL,
103 `b` text
104 ) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=latin1;
105 Warnings:
106 Warning 1681 Integer display width is deprecated and will be removed in a future release.
107 SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
108 Table Create Table
109 t !@#$%^&*()_+-={}[]|\?<>,. CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` (
110 `a` int DEFAULT NULL,
111 `b` text
112 ) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
113 DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2`;
114 CREATE TABLESPACE `#sql_1` ADD DATAFILE '#sql_1.ibd';
115 CREATE TABLE `#sql_1` (a int, b text) TABLESPACE `#sql_1`;
116 INSERT INTO `#sql_1` VALUES(1,'one');
117 SHOW CREATE TABLE `#sql_1`;
118 Table Create Table
119 #sql_1 CREATE TABLE `#sql_1` (
120 `a` int DEFAULT NULL,
121 `b` text
122 ) /*!50100 TABLESPACE `#sql_1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
123 CREATE TABLESPACE `s_Cöŀumň` ADD DATAFILE 's_utf8_a.ibd';
124 CREATE TABLE `t_utf8_1` (a int, b text) TABLESPACE `s_Cöŀumň`;
125 SHOW CREATE TABLE `t_utf8_1`;
126 Table Create Table
127 t_utf8_1 CREATE TABLE `t_utf8_1` (
128 `a` int DEFAULT NULL,
129 `b` text
130 ) /*!50100 TABLESPACE `s_Cöŀumň` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
131 CREATE TABLESPACE `s_cöĿǖmň` ADD DATAFILE 's_utf8_b.ibd';
132 CREATE TABLE `t_utf8_2` (a int, b text) TABLESPACE `s_cöĿǖmň`;
133 SHOW CREATE TABLE `t_utf8_2`;
134 Table Create Table
135 t_utf8_2 CREATE TABLE `t_utf8_2` (
136 `a` int DEFAULT NULL,
137 `b` text
138 ) /*!50100 TABLESPACE `s_cöĿǖmň` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
139 CREATE TABLESPACE `s_😲` ADD DATAFILE 's_utf8_c.ibd';
140 ERROR HY000: Invalid utf8mb3 character string: 's_\xF0\x9F\x98\xB2'
141 CREATE TABLESPACE `s_𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜` ADD DATAFILE 's_utf8_d.ibd';
142 ERROR HY000: Invalid utf8mb3 character string: 's_\xF0\x90\x8C\x80\xF0\x90\x8C\x81\xF0\x90\x8C\x82\xF0\x90\x8C\x'
143 CREATE TABLESPACE `s_வணக்கம்` ADD DATAFILE 'ஆவணம்.ibd';
144 CREATE TABLE `t_utf8_3` (a int, b text) TABLESPACE `s_வணக்கம்`;
145 SHOW CREATE TABLE `t_utf8_3`;
146 Table Create Table
147 t_utf8_3 CREATE TABLE `t_utf8_3` (
148 `a` int DEFAULT NULL,
149 `b` text
150 ) /*!50100 TABLESPACE `s_வணக்கம்` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
151 === information_schema.innodb_tablespaces and innodb_datafiles ===
152 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
153 #sql_1 General DEFAULT 0 1 1 Any #sql_1.ibd
154 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
155 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
156 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
157 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
158 s_ !@#$%^&*()_+-={}[]|\?<>,. General DEFAULT 0 1 1 Any s_utf8.ibd
159 s_Cöŀumň General DEFAULT 0 1 1 Any s_utf8_a.ibd
160 s_cöĿǖmň General DEFAULT 0 1 1 Any s_utf8_b.ibd
161 s_வணக்கம் General DEFAULT 0 1 1 Any ஆவணம்.ibd
162 === information_schema.files ===
163 Space_Name File_Type Engine Status Tablespace_Name Path
164 #sql_1 TABLESPACE InnoDB NORMAL #sql_1 MYSQLD_DATADIR/#sql_1.ibd
165 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
166 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
167 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
168 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
169 s_ !@#$%^&*()_+-={}[]|\?<>,. TABLESPACE InnoDB NORMAL s_ !@#$%^&*()_+-={}[]|\?<>,. MYSQLD_DATADIR/s_utf8.ibd
170 s_Cöŀumň TABLESPACE InnoDB NORMAL s_Cöŀumň MYSQLD_DATADIR/s_utf8_a.ibd
171 s_cöĿǖmň TABLESPACE InnoDB NORMAL s_cöĿǖmň MYSQLD_DATADIR/s_utf8_b.ibd
172 s_வணக்கம் TABLESPACE InnoDB NORMAL s_வணக்கம் MYSQLD_DATADIR/ஆவணம்.ibd
173 === information_schema.innodb_tables and innodb_tablespaces ===
174 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
175 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
176 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
177 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
178 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
179 test/@0023sql_1 #sql_1 161 5 Dynamic 0 General
180 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 5 Dynamic 0 General
181 test/t_utf8_1 s_Cöŀumň 161 5 Dynamic 0 General
182 test/t_utf8_2 s_cöĿǖmň 161 5 Dynamic 0 General
183 test/t_utf8_3 s_வணக்கம் 161 5 Dynamic 0 General
184 # Directory listing of MYSQLD_DATADIR/
185 #sql_1.ibd
186 mysql.ibd
187 s_utf8.ibd
188 s_utf8_a.ibd
189 s_utf8_b.ibd
190 ஆவணம்.ibd
191 CHECK TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
192 Table Op Msg_type Msg_text
193 test.t !@#$%^&*()_+-={}[]|\?<>,. check status OK
194 DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
195 DROP TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `;
196 CHECK TABLE `#sql_1`;
197 Table Op Msg_type Msg_text
198 test.#sql_1 check status OK
199 DROP TABLE `#sql_1`;
200 DROP TABLESPACE `#sql_1`;
201 DROP TABLE `t_utf8_1`;
202 DROP TABLESPACE `s_Cöŀumň`;
203 DROP TABLE `t_utf8_2` ;
204 DROP TABLESPACE `s_cöĿǖmň` ;
205 DROP TABLE `t_utf8_3`;
206 DROP TABLESPACE `s_வணக்கம்`;
207 #
208 # Try to create a tablespace with the reserved case-sensitive prefix 'innodb_'
209 #
210 CREATE TABLESPACE `innodb_system` ADD DATAFILE 's_bad.ibd';
211 ERROR 42000: InnoDB: `innodb_system` is a reserved tablespace name.
212 DROP TABLESPACE `innodb_system`;
213 ERROR 42000: InnoDB: `innodb_system` is a reserved tablespace name.
214 SHOW WARNINGS;
215 Level Code Message
216 Error 3119 InnoDB: `innodb_system` is a reserved tablespace name.
217 Error 3119 Incorrect tablespace name `innodb_system`
218 CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 's_InnoDB_System.ibd';
219 DROP TABLESPACE `InnoDB_System`;
220 CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1';
221 ERROR HY000: Duplicate file name for tablespace 'InnoDB_System'
222 SHOW WARNINGS;
223 Level Code Message
224 Error 3606 Duplicate file name for tablespace 'InnoDB_System'
225 CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1.ibd';
226 DROP TABLESPACE `InnoDB_System`;
227 CREATE TABLESPACE `innodb_temporary` ADD DATAFILE 's_bad.ibd';
228 ERROR 42000: InnoDB: `innodb_temporary` is a reserved tablespace name.
229 SHOW WARNINGS;
230 Level Code Message
231 Error 3119 InnoDB: `innodb_temporary` is a reserved tablespace name.
232 Error 3119 Incorrect tablespace name `innodb_temporary`
233 DROP TABLESPACE `innodb_temporary`;
234 ERROR 42000: InnoDB: `innodb_temporary` is a reserved tablespace name.
235 CREATE TABLESPACE `InnoDB_Temporary` ADD DATAFILE 's_InnoDB_Temporary.ibd';
236 DROP TABLESPACE `InnoDB_Temporary`;
237 CREATE TABLESPACE `innodb_custom` ADD DATAFILE 's_bad.ibd';
238 ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved.
239 SHOW WARNINGS;
240 Level Code Message
241 Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved.
242 Error 3119 Incorrect tablespace name `innodb_custom`
243 DROP TABLESPACE `innodb_custom`;
244 ERROR HY000: Tablespace innodb_custom doesn't exist.
245 CREATE TABLESPACE `InnoDB_Custom` ADD DATAFILE 's_InnoDB_Custom.ibd';
246 DROP TABLESPACE `InnoDB_Custom`;
247 CREATE TABLESPACE `INNODB_CUSTOM` ADD DATAFILE 's_INNODB_CUSTOM.ibd';
248 DROP TABLESPACE `INNODB_CUSTOM`;
249 DROP TABLESPACE `ib_logfile0`;
250 ERROR HY000: Tablespace ib_logfile0 doesn't exist.
251 CREATE TABLESPACE `ib_logfile0` ADD DATAFILE 'ib_logfile0.ibd';
252 DROP TABLESPACE `ib_logfile0`;
253 DROP TABLESPACE `ib_logfile1`;
254 ERROR HY000: Tablespace ib_logfile1 doesn't exist.
255 CREATE TABLESPACE `ib_logfile1` ADD DATAFILE 'ib_logfile1.ibd';
256 DROP TABLESPACE `ib_logfile1`;
257 DROP TABLESPACE `ibdata1`;
258 ERROR HY000: Tablespace ibdata1 doesn't exist.
259 CREATE TABLESPACE `ibdata1` ADD DATAFILE 'ibdata1.ibd';
260 DROP TABLESPACE `ibdata1`;
261 DROP TABLESPACE `undo_001`;
262 ERROR HY000: Tablespace undo_001 doesn't exist.
263 CREATE TABLESPACE `undo_001` ADD DATAFILE 'undo_001.ibd';
264 DROP TABLESPACE `undo_001`;
265 DROP TABLESPACE `undo_002`;
266 ERROR HY000: Tablespace undo_002 doesn't exist.
267 CREATE TABLESPACE `undo_002` ADD DATAFILE 'undo_002.ibd';
268 DROP TABLESPACE `undo_002`;
269 #
270 # Test various forms of ADD DATAFILE
271 #
272 CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd' ENGINE=InnoDB;
273 CREATE TABLESPACE `s1_#_hash` ADD DATAFILE 's1_#_hash.ibd';
274 CREATE TABLESPACE s1_remote ADD DATAFILE 'MYSQL_TMP_DIR/s1.ibd.ibd';
275 CREATE TABLESPACE s2_remote ADD DATAFILE 'MYSQL_TMP_DIR/s2_#_dir/s2.ibd';
276 CREATE TABLESPACE s_bad ADD DATAFILE '.ibd';
277 ERROR HY000: The ADD DATAFILE filepath does not have a proper filename.
278 SHOW WARNINGS;
279 Level Code Message
280 Error 3121 The ADD DATAFILE filepath does not have a proper filename.
281 Error 1528 Failed to create TABLESPACE s_bad
282 Error 3121 Incorrect File Name '.ibd'.
283 CREATE TABLESPACE s_bad ADD DATAFILE '../s_dir/s_subdir/.ibd';
284 ERROR HY000: The ADD DATAFILE filepath does not have a proper filename.
285 SHOW WARNINGS;
286 Level Code Message
287 Error 3121 The ADD DATAFILE filepath does not have a proper filename.
288 Error 3121 The directory does not exist or is incorrect.
289 Error 3121 The DATAFILE location must be in a known directory.
290 Error 1528 Failed to create TABLESPACE s_bad
291 Error 3121 Incorrect File Name '../s_dir/s_subdir/.ibd'.
292 CREATE TABLESPACE s_bad ADD DATAFILE '../s_dir/s_bad.ibs';
293 ERROR HY000: The ADD DATAFILE filepath must end with '.ibd'.
294 SHOW WARNINGS;
295 Level Code Message
296 Error 3121 The ADD DATAFILE filepath must end with '.ibd'.
297 Error 3121 The directory does not exist or is incorrect.
298 Error 3121 The DATAFILE location must be in a known directory.
299 Error 1528 Failed to create TABLESPACE s_bad
300 Error 3121 Incorrect File Name '../s_dir/s_bad.ibs'.
301 CREATE TABLESPACE s_bad ADD DATAFILE 'c:s_bad.ibd';
302 ERROR HY000: Incorrect File Name 'c:s_bad.ibd'.
303 SHOW WARNINGS;
304 Level Code Message
305 Error 3121 Incorrect File Name 'c:s_bad.ibd'.
306 Error 3121 Invalid use of ':'.
307 Error 1528 Failed to create TABLESPACE s_bad
308 Error 3121 Incorrect File Name 'c:s_bad.ibd'.
309 CREATE TABLESPACE s_bad ADD DATAFILE 'drive:s_bad.ibd';
310 ERROR HY000: Incorrect File Name 'drive:s_bad.ibd'.
311 SHOW WARNINGS;
312 Level Code Message
313 Error 3121 Incorrect File Name 'drive:s_bad.ibd'.
314 Error 3121 Invalid use of ':'.
315 Error 1528 Failed to create TABLESPACE s_bad
316 Error 3121 Incorrect File Name 'drive:s_bad.ibd'.
317 #
318 # Try to create a tablespace where a same-named directory and file exist.
319 #
320 CREATE TABLESPACE s4_def ADD DATAFILE 'MYSQL_TMP_DIR/tablespace.ibd/s4.ibd';
321 CREATE TABLESPACE s5_def ADD DATAFILE 'MYSQL_TMP_DIR/tablespace.ibd/s4.ibd';
322 ERROR HY000: Duplicate file name for tablespace 's5_def'
323 CREATE TABLESPACE s6_def ADD DATAFILE 'MYSQL_TMP_DIR/tablespace.ibd';
324 ERROR HY000: The ADD DATAFILE filepath already exists.
325 === information_schema.innodb_tablespaces and innodb_datafiles ===
326 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
327 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
328 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
329 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
330 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
331 s1_#_hash General DEFAULT 0 1 1 Any s1_#_hash.ibd
332 s1_remote General DEFAULT 0 1 1 Any MYSQL_TMP_DIR/s1.ibd.ibd
333 s2_remote General DEFAULT 0 1 1 Any MYSQL_TMP_DIR/s2_#_dir/s2.ibd
334 s4_def General DEFAULT 0 1 1 Any MYSQL_TMP_DIR/tablespace.ibd/s4.ibd
335 s_def General DEFAULT 0 1 1 Any s_def.ibd
336 === information_schema.files ===
337 Space_Name File_Type Engine Status Tablespace_Name Path
338 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
339 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
340 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
341 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
342 s1_#_hash TABLESPACE InnoDB NORMAL s1_#_hash MYSQLD_DATADIR/s1_#_hash.ibd
343 s1_remote TABLESPACE InnoDB NORMAL s1_remote MYSQL_TMP_DIR/s1.ibd.ibd
344 s2_remote TABLESPACE InnoDB NORMAL s2_remote MYSQL_TMP_DIR/s2_#_dir/s2.ibd
345 s4_def TABLESPACE InnoDB NORMAL s4_def MYSQL_TMP_DIR/tablespace.ibd/s4.ibd
346 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
347 # Directory listing of MYSQLD_DATADIR/
348 mysql.ibd
349 s1_#_hash.ibd
350 s_def.ibd
351 # Directory listing of MYSQL_TMP_DIR/
352 s1.ibd.ibd
353 tablespace.ibd
354 # Directory listing of MYSQL_TMP_DIR/tablespace.ibd/
355 s4.ibd
356 # Directory listing of MYSQL_TMP_DIR/s2_#_dir/
357 s2.ibd
358 DROP TABLESPACE s4_def;
359 DROP TABLESPACE s1_remote;
360 DROP TABLESPACE s2_remote;
361 DROP TABLESPACE `s1_#_hash`;
362 #
363 # Try to create a tablespace that already exists.
364 # Make sure that the tablespace name is compared in a CASE SENSITIVE way.
365 #
366 CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd';
367 ERROR HY000: Tablespace 's_def' exists.
368 CREATE TABLESPACE s_def ADD DATAFILE 's_def_2.ibd';
369 ERROR HY000: Tablespace 's_def' exists.
370 CREATE TABLESPACE S_new ADD DATAFILE 's_def.ibd';
371 ERROR HY000: Duplicate file name for tablespace 'S_new'
372 CREATE TABLESPACE `s_DEF` ADD DATAFILE 's_def_2.ibd';
373 DROP TABLESPACE `s_DEF`;
374 #
375 # Make a file-per-table tablespace name and try to use it as a General tablespace
376 #
377 CREATE TABLE t_single (a int, b text);
378 === information_schema.innodb_tables and innodb_tablespaces ===
379 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
380 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
381 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
382 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
383 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
384 test/t_single test/t_single 33 5 Dynamic 0 Single
385 === information_schema.innodb_tablespaces and innodb_datafiles ===
386 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
387 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
388 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
389 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
390 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
391 s_def General DEFAULT 0 1 1 Any s_def.ibd
392 test/t_single Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t_single.ibd
393 === information_schema.files ===
394 Space_Name File_Type Engine Status Tablespace_Name Path
395 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
396 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
397 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
398 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
399 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
400 test/t_single TABLESPACE InnoDB NORMAL test/t_single MYSQLD_DATADIR/test/t_single.ibd
401 CREATE TABLE t_general (a int, b text) TABLESPACE test/t_single engine=InnoDB;
402 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
403 CREATE TABLE t_general (a int, b text) TABLESPACE `test/t_single` engine=InnoDB;
404 ERROR 42000: InnoDB: A general tablespace name cannot contain '/'.
405 SHOW WARNINGS;
406 Level Code Message
407 Error 3119 InnoDB: A general tablespace name cannot contain '/'.
408 Error 3119 Incorrect tablespace name `test/t_single`
409 CREATE TABLE t_general (a int, b text) TABLESPACE `S_Def` engine=InnoDB;
410 ERROR HY000: Tablespace S_Def doesn't exist.
411 SHOW WARNINGS;
412 Level Code Message
413 Error 3510 Tablespace S_Def doesn't exist.
414 CREATE TABLE t_general (a int, b text) TABLESPACE `S_DEF` engine=InnoDB;
415 ERROR HY000: Tablespace S_DEF doesn't exist.
416 SHOW WARNINGS;
417 Level Code Message
418 Error 3510 Tablespace S_DEF doesn't exist.
419 CREATE TABLESPACE `test/t_single` ADD DATAFILE 's_single.ibd';
420 ERROR 42000: InnoDB: A general tablespace name cannot contain '/'.
421 SHOW WARNINGS;
422 Level Code Message
423 Error 3119 InnoDB: A general tablespace name cannot contain '/'.
424 Error 3119 Incorrect tablespace name `test/t_single`
425 CREATE TABLESPACE `Test/t_Single` ADD DATAFILE 's_single.ibd';
426 ERROR 42000: InnoDB: A general tablespace name cannot contain '/'.
427 SHOW WARNINGS;
428 Level Code Message
429 Error 3119 InnoDB: A general tablespace name cannot contain '/'.
430 Error 3119 Incorrect tablespace name `Test/t_Single`
431 CREATE TABLESPACE `TEST/T_SINGLE` ADD DATAFILE 's_single.ibd';
432 ERROR 42000: InnoDB: A general tablespace name cannot contain '/'.
433 SHOW WARNINGS;
434 Level Code Message
435 Error 3119 InnoDB: A general tablespace name cannot contain '/'.
436 Error 3119 Incorrect tablespace name `TEST/T_SINGLE`
437 DROP TABLE t_single;
438 === information_schema.innodb_tablespaces and innodb_datafiles ===
439 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
440 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
441 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
442 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
443 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
444 s_def General DEFAULT 0 1 1 Any s_def.ibd
445 === information_schema.files ===
446 Space_Name File_Type Engine Status Tablespace_Name Path
447 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
448 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
449 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
450 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
451 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
452 #
453 # Row format is not allowed on CREATE TABLESPACE
454 #
455 CREATE TABLESPACE s_red ADD DATAFILE 's_red.ibd' ROW_FORMAT=redundant;
456 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
457 #
458 # Add tables to the tablespaces.
459 #
460 CREATE TABLE t_def_in_def (a int, b text) TABLESPACE s_def;
461 CREATE TABLE t_red_in_def (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_def;
462 SET GLOBAL innodb_file_per_table = OFF;
463 CREATE TABLE t_dyn_in_def (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_def;
464 # Add data to the existing Tables
465 INSERT INTO t_def_in_def VALUES (1,'a'),(2,'b'),(3,'c');
466 INSERT INTO t_red_in_def VALUES (1,'a'),(2,'b'),(3,'c');
467 INSERT INTO t_dyn_in_def VALUES (1,'a'),(2,'b'),(3,'c');
468 #
469 # Try to drop a tablespace which is not empty
470 #
471 DROP TABLESPACE s_def;
472 ERROR HY000: Tablespace `s_def` is not empty.
473 === information_schema.innodb_tablespaces and innodb_datafiles ===
474 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
475 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
476 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
477 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
478 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
479 s_def General DEFAULT 0 1 1 Any s_def.ibd
480 === information_schema.files ===
481 Space_Name File_Type Engine Status Tablespace_Name Path
482 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
483 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
484 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
485 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
486 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
487 === information_schema.innodb_tables and innodb_tablespaces ===
488 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
489 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
490 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
491 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
492 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
493 test/t_def_in_def s_def 161 5 Dynamic 0 General
494 test/t_dyn_in_def s_def 161 5 Dynamic 0 General
495 test/t_red_in_def s_def 128 5 Redundant 0 General
496 # Directory of MYSQLD_DATADIR/
497 mysql.ibd
498 s_def.ibd
499 # Directory of MYSQLD_DATADIR/test/
500 #
501 # Drop the tables we no longer need.
502 #
503 CHECK TABLE t_dyn_in_def;
504 Table Op Msg_type Msg_text
505 test.t_dyn_in_def check status OK
506 CHECK TABLE t_red_in_def;
507 Table Op Msg_type Msg_text
508 test.t_red_in_def check status OK
509 DROP TABLE t_dyn_in_def;
510 DROP TABLE t_red_in_def;
511 #
512 # Try to make a table using the database name with an existing table name
513 #
514 CREATE TABLE test/t_def_in_def (a int, b text) TABLESPACE s_def;
515 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
516 CREATE TABLE `test/t_def_in_def` (a int, b text) TABLESPACE s_def;
517 === information_schema.innodb_tables and innodb_tablespaces ===
518 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
519 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
520 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
521 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
522 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
523 test/test@002ft_def_in_def s_def 161 5 Dynamic 0 General
524 test/t_def_in_def s_def 161 5 Dynamic 0 General
525 === information_schema.innodb_tablespaces and innodb_datafiles ===
526 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
527 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
528 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
529 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
530 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
531 s_def General DEFAULT 0 1 1 Any s_def.ibd
532 === information_schema.files ===
533 Space_Name File_Type Engine Status Tablespace_Name Path
534 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
535 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
536 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
537 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
538 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
539 # Directory listing of MYSQLD_DATADIR/
540 mysql.ibd
541 s_def.ibd
542 # Directory listing of MYSQLD_DATADIR/test/
543 #
544 # Try to create a temporary tablespace
545 #
546 CREATE TEMPORARY TABLESPACE s_temp ADD DATAFILE 's_temp.ibd';
547 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
548 SHOW WARNINGS;
549 Level Code Message
550 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
551 #
552 # Try to put a temporary table into a non-temporary tablespace
553 #
554 CREATE TEMPORARY TABLE t_temp_red (a int, b text) ROW_FORMAT=redundant TABLESPACE s_def;
555 ERROR HY000: InnoDB: Tablespace `s_def` cannot contain TEMPORARY tables.
556 SHOW WARNINGS;
557 Level Code Message
558 Error 1478 InnoDB: Tablespace `s_def` cannot contain TEMPORARY tables.
559 Error 1031 Table storage engine for 't_temp_red' doesn't have this option
560 #
561 # Try to put a compressed temporary table into the system temporary tablespace
562 #
563 CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=compressed TABLESPACE=`innodb_temporary`;
564 ERROR HY000: InnoDB: Temporary tablespace `innodb_temporary` cannot contain COMPRESSED tables.
565 SHOW WARNINGS;
566 Level Code Message
567 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.
568 Error 1478 InnoDB: Temporary tablespace `innodb_temporary` cannot contain COMPRESSED tables.
569 Error 1031 Table storage engine for 't_temp_zip' doesn't have this option
570 #
571 # Try to add a second table to a single-table tablespace
572 #
573 SET GLOBAL innodb_file_per_table = ON;
574 CREATE TABLE s_single (a int, b text) ROW_FORMAT=dynamic;
575 CREATE TABLE t_second (a int, b text) TABLESPACE s_single;
576 ERROR HY000: Tablespace s_single doesn't exist.
577 SHOW WARNINGS;
578 Level Code Message
579 Error 3510 Tablespace s_single doesn't exist.
580 CREATE TABLE t_second (a int, b text) TABLESPACE=`test/s_single`;
581 ERROR 42000: InnoDB: A general tablespace name cannot contain '/'.
582 SHOW WARNINGS;
583 Level Code Message
584 Error 3119 InnoDB: A general tablespace name cannot contain '/'.
585 Error 3119 Incorrect tablespace name `test/s_single`
586 === information_schema.innodb_tablespaces and innodb_datafiles ===
587 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
588 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
589 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
590 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
591 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
592 s_def General DEFAULT 0 1 1 Any s_def.ibd
593 test/s_single Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/s_single.ibd
594 === information_schema.files ===
595 Space_Name File_Type Engine Status Tablespace_Name Path
596 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
597 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
598 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
599 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
600 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
601 test/s_single TABLESPACE InnoDB NORMAL test/s_single MYSQLD_DATADIR/test/s_single.ibd
602 DROP TABLE s_single;
603 #
604 # Try to use both TABLESPACE and DATA DIRECTORY in the same CREATE TABLE.
605 #
606 # Strict mode and innodb_file_per_table should make no difference
607 # when using general tablespaces. If TABLESPACE=innodb_file-per-table,
608 # innodb_strict_mode will apply.
609 #
610 SET GLOBAL innodb_file_per_table = OFF;
611 SET innodb_strict_mode = OFF;
612 CREATE TABLE t3 (a int, b text) DATA DIRECTORY='MYSQL_TMP_DIR' TABLESPACE s_def;
613 ERROR HY000: InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment.
614 SHOW WARNINGS;
615 Level Code Message
616 Error 1478 InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment.
617 Error 1031 Table storage engine for 't3' doesn't have this option
618 # Cannot use both a general tablespace and a DATA DIRECTORY
619 CREATE TABLE t4 (a int, b text) TABLESPACE=s_def DATA DIRECTORY='MYSQL_TMP_DIR';
620 ERROR HY000: InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment.
621 SHOW WARNINGS;
622 Level Code Message
623 Error 1478 InnoDB: DATA DIRECTORY cannot be used with a TABLESPACE assignment.
624 Error 1031 Table storage engine for 't4' doesn't have this option
625 # TABLESPACE=innodb_file_per_table can be used with DATA DIRECTORY
626 CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
627 SHOW CREATE TABLE t4;
628 Table Create Table
629 t4 CREATE TABLE `t4` (
630 `a` int DEFAULT NULL,
631 `b` text
632 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/'
633 # An altered table should retain both TABLESPACE=innodb_file_per_table and the DATA DIRECTORY
634 ALTER TABLE t4 ROW_FORMAT=dynamic, algorithm=copy;
635 SHOW CREATE TABLE t4;
636 Table Create Table
637 t4 CREATE TABLE `t4` (
638 `a` int DEFAULT NULL,
639 `b` text
640 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC DATA DIRECTORY='MYSQL_TMP_DIR/'
641 DROP TABLE t4;
642 # An empty string in DATA DIRECTORY='' indicates the current directory,
643 # which is not allowed.
644 CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='';
645 ERROR HY000: Incorrect path value: ''
646 SHOW WARNINGS;
647 Level Code Message
648 Error 1525 Incorrect path value: ''
649 SET GLOBAL innodb_file_per_table = ON;
650 SET innodb_strict_mode = ON;
651 CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='';
652 ERROR HY000: Incorrect path value: ''
653 SHOW WARNINGS;
654 Level Code Message
655 Error 1525 Incorrect path value: ''
656 CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='.';
657 ERROR HY000: Incorrect path value: '.'
658 SHOW WARNINGS;
659 Level Code Message
660 Error 1525 Incorrect path value: '.'
661 CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='test';
662 ERROR HY000: Incorrect path value: 'test'
663 SHOW WARNINGS;
664 Level Code Message
665 Error 1525 Incorrect path value: 'test'
666 === information_schema.innodb_tablespaces and innodb_datafiles ===
667 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
668 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
669 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
670 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
671 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
672 s_def General DEFAULT 0 1 1 Any s_def.ibd
673 === information_schema.files ===
674 Space_Name File_Type Engine Status Tablespace_Name Path
675 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
676 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
677 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
678 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
679 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
680 === information_schema.innodb_tables and innodb_tablespaces ===
681 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
682 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
683 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
684 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
685 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
686 test/test@002ft_def_in_def s_def 161 5 Dynamic 0 General
687 test/t_def_in_def s_def 161 5 Dynamic 0 General
688 DROP TABLE t_def_in_def;
689 DROP TABLE `test/t_def_in_def`;
690 #
691 # Create tables explicitly in the system tablespace.
692 #
693 CREATE TABLE t_red_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=redundant;
694 CREATE TABLE t_com_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compact;
695 CREATE TABLE t_dyn_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=dynamic;
696 CREATE TABLE t_zip_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compressed;
697 ERROR HY000: InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table
698 SHOW WARNINGS;
699 Level Code Message
700 Error 1478 InnoDB: Tablespace `innodb_system` cannot contain a COMPRESSED table
701 Error 1031 Table storage engine for 't_zip_in_system' doesn't have this option
702 === information_schema.innodb_tablespaces and innodb_datafiles ===
703 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
704 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
705 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
706 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
707 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
708 s_def General DEFAULT 0 1 1 Any s_def.ibd
709 === information_schema.files ===
710 Space_Name File_Type Engine Status Tablespace_Name Path
711 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
712 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
713 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
714 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
715 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
716 === information_schema.innodb_tables and innodb_tablespaces ===
717 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
718 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
719 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
720 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
721 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
722 test/t_com_in_system innodb_system 129 5 Compact 0 System
723 test/t_dyn_in_system innodb_system 161 5 Dynamic 0 System
724 test/t_red_in_system innodb_system 128 5 Redundant 0 System
725 CHECK TABLE t_red_in_system;
726 Table Op Msg_type Msg_text
727 test.t_red_in_system check status OK
728 CHECK TABLE t_com_in_system;
729 Table Op Msg_type Msg_text
730 test.t_com_in_system check status OK
731 CHECK TABLE t_dyn_in_system;
732 Table Op Msg_type Msg_text
733 test.t_dyn_in_system check status OK
734 DROP TABLE t_red_in_system;
735 DROP TABLE t_com_in_system;
736 DROP TABLE t_dyn_in_system;
737 #
738 # Create tables explicitly as file_per_table tablespaces.
739 #
740 CREATE TABLE t_red_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=redundant;
741 CREATE TABLE t_com_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compact;
742 CREATE TABLE t_dyn_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=dynamic;
743 CREATE TABLE t_def_as_remote (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='MYSQL_TMP_DIR';
744 === information_schema.innodb_tablespaces and innodb_datafiles ===
745 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
746 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
747 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
748 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
749 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
750 s_def General DEFAULT 0 1 1 Any s_def.ibd
751 test/t_com_as_file_per_table Single DEFAULT 0 1 1 Compact or Redundant MYSQLD_DATADIR/test/t_com_as_file_per_table.ibd
752 test/t_def_as_remote Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/test/t_def_as_remote.ibd
753 test/t_dyn_as_file_per_table Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t_dyn_as_file_per_table.ibd
754 test/t_red_as_file_per_table Single DEFAULT 0 1 1 Compact or Redundant MYSQLD_DATADIR/test/t_red_as_file_per_table.ibd
755 === information_schema.files ===
756 Space_Name File_Type Engine Status Tablespace_Name Path
757 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
758 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
759 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
760 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
761 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
762 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
763 test/t_def_as_remote TABLESPACE InnoDB NORMAL test/t_def_as_remote MYSQL_TMP_DIR/test/t_def_as_remote.ibd
764 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
765 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
766 === information_schema.innodb_tables and innodb_tablespaces ===
767 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
768 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
769 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
770 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
771 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
772 test/t_com_as_file_per_table test/t_com_as_file_per_table 1 5 Compact 0 Single
773 test/t_def_as_remote test/t_def_as_remote 97 5 Dynamic 0 Single
774 test/t_dyn_as_file_per_table test/t_dyn_as_file_per_table 33 5 Dynamic 0 Single
775 test/t_red_as_file_per_table test/t_red_as_file_per_table 0 5 Redundant 0 Single
776 # Directory listing of MYSQLD_DATADIR/
777 mysql.ibd
778 s_def.ibd
779 # Directory listing of MYSQLD_DATADIR/test/
780 t_com_as_file_per_table.ibd
781 t_dyn_as_file_per_table.ibd
782 t_red_as_file_per_table.ibd
783 SHOW CREATE TABLE t_red_as_file_per_table;
784 Table Create Table
785 t_red_as_file_per_table CREATE TABLE `t_red_as_file_per_table` (
786 `a` int DEFAULT NULL,
787 `b` text
788 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
789 SHOW CREATE TABLE t_com_as_file_per_table;
790 Table Create Table
791 t_com_as_file_per_table CREATE TABLE `t_com_as_file_per_table` (
792 `a` int DEFAULT NULL,
793 `b` text
794 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
795 SHOW CREATE TABLE t_dyn_as_file_per_table;
796 Table Create Table
797 t_dyn_as_file_per_table CREATE TABLE `t_dyn_as_file_per_table` (
798 `a` int DEFAULT NULL,
799 `b` text
800 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
801 SHOW CREATE TABLE t_def_as_remote;
802 Table Create Table
803 t_def_as_remote CREATE TABLE `t_def_as_remote` (
804 `a` int DEFAULT NULL,
805 `b` text
806 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/'
807 #
808 # These explicit file_per_table tables can be moved to a general tablespace.
809 #
810 CREATE TABLESPACE s_multiple ADD DATAFILE 'multiple.ibd';
811 ALTER TABLE `t_red_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_red_was_file_per_table`;
812 ALTER TABLE `t_com_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_com_was_file_per_table`;
813 ALTER TABLE `t_dyn_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_dyn_was_file_per_table`;
814 ALTER TABLE `t_def_as_remote` TABLESPACE=`s_multiple`, RENAME TO `t_def_was_remote`;
815 === information_schema.innodb_tablespaces and innodb_datafiles ===
816 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
817 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
818 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
819 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
820 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
821 s_def General DEFAULT 0 1 1 Any s_def.ibd
822 s_multiple General DEFAULT 0 1 1 Any multiple.ibd
823 === information_schema.files ===
824 Space_Name File_Type Engine Status Tablespace_Name Path
825 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
826 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
827 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
828 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
829 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
830 s_multiple TABLESPACE InnoDB NORMAL s_multiple MYSQLD_DATADIR/multiple.ibd
831 === information_schema.innodb_tables and innodb_tablespaces ===
832 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
833 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
834 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
835 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
836 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
837 test/t_com_was_file_per_table s_multiple 129 5 Compact 0 General
838 test/t_def_was_remote s_multiple 161 5 Dynamic 0 General
839 test/t_dyn_was_file_per_table s_multiple 161 5 Dynamic 0 General
840 test/t_red_was_file_per_table s_multiple 128 5 Redundant 0 General
841 # Directory listing of MYSQLD_DATADIR/
842 multiple.ibd
843 mysql.ibd
844 s_def.ibd
845 # Directory listing of MYSQLD_DATADIR/test/
846 #
847 # Tables in a general tablespace can be moved to file_per_table locations.
848 #
849 ALTER TABLE `t_red_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_red_to_file_per_table`;
850 ALTER TABLE `t_com_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_com_to_file_per_table`;
851 ALTER TABLE `t_dyn_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_dyn_to_file_per_table`;
852 # Note that MySQL ignores DATA DIRECTORY on all ALTER TABLE statements.
853 ALTER TABLE `t_def_was_remote` TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='MYSQL_TMP_DIR', RENAME TO `t_def_to_file_per_table`;
854 Warnings:
855 Warning 1618 <DATA DIRECTORY> option ignored
856 === information_schema.innodb_tablespaces and innodb_datafiles ===
857 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
858 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
859 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
860 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
861 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
862 s_def General DEFAULT 0 1 1 Any s_def.ibd
863 s_multiple General DEFAULT 0 1 1 Any multiple.ibd
864 test/t_com_to_file_per_table Single DEFAULT 0 1 1 Compact or Redundant MYSQLD_DATADIR/test/t_com_to_file_per_table.ibd
865 test/t_def_to_file_per_table Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t_def_to_file_per_table.ibd
866 test/t_dyn_to_file_per_table Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t_dyn_to_file_per_table.ibd
867 test/t_red_to_file_per_table Single DEFAULT 0 1 1 Compact or Redundant MYSQLD_DATADIR/test/t_red_to_file_per_table.ibd
868 === information_schema.files ===
869 Space_Name File_Type Engine Status Tablespace_Name Path
870 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
871 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
872 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
873 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
874 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
875 s_multiple TABLESPACE InnoDB NORMAL s_multiple MYSQLD_DATADIR/multiple.ibd
876 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
877 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
878 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
879 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
880 === information_schema.innodb_tables and innodb_tablespaces ===
881 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
882 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
883 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
884 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
885 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
886 test/t_com_to_file_per_table test/t_com_to_file_per_table 1 5 Compact 0 Single
887 test/t_def_to_file_per_table test/t_def_to_file_per_table 33 5 Dynamic 0 Single
888 test/t_dyn_to_file_per_table test/t_dyn_to_file_per_table 33 5 Dynamic 0 Single
889 test/t_red_to_file_per_table test/t_red_to_file_per_table 0 5 Redundant 0 Single
890 # Directory listing of MYSQLD_DATADIR/
891 multiple.ibd
892 mysql.ibd
893 s_def.ibd
894 # Directory listing of MYSQLD_DATADIR/test/
895 t_com_to_file_per_table.ibd
896 t_def_to_file_per_table.ibd
897 t_dyn_to_file_per_table.ibd
898 t_red_to_file_per_table.ibd
899 DROP TABLE t_red_to_file_per_table;
900 DROP TABLE t_com_to_file_per_table;
901 DROP TABLE t_dyn_to_file_per_table;
902 DROP TABLE t_def_to_file_per_table;
903 DROP TABLESPACE s_multiple;
904 #
905 # Try the syntax that InnoDB does not support.
906 #
907 # Try NDB settings for CREATE TABLESPACE. These are currently accepted
908 # even if they probably should not be. This is valid syntax from the
909 # parser's POW
910 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd'
911 USE LOGFILE GROUP logfile_group;
912 DROP TABLESPACE s_bad;
913 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' EXTENT_SIZE = 100M;
914 DROP TABLESPACE s_bad;
915 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' NO_WAIT;
916 DROP TABLESPACE s_bad;
917 #
918 # Checking LOGFILE GROUP commands (only supported by NDB)
919 #
920 CREATE LOGFILE GROUP s_bad;
921 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
922 CREATE LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat';
923 ERROR HY000: Feature LOGFILE GROUP is unsupported (by InnoDB).
924 ALTER LOGFILE GROUP s_bad;
925 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
926 ALTER LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat'
927 INITIAL_SIZE = 100M, NO_WAIT;
928 ERROR HY000: Feature LOGFILE GROUP is unsupported (by InnoDB).
929 DROP LOGFILE GROUP s_bad;
930 ERROR HY000: Feature LOGFILE GROUP is unsupported (by InnoDB).
931 #
932 # Checking ALTER TABLESPACE (only NDB in 5.7)
933 #
934 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd';
935 ALTER TABLESPACE s_bad;
936 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
937 ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd';
938 ERROR HY000: Failed to alter: TABLESPACE s_bad
939 SHOW WARNINGS;
940 Level Code Message
941 Error 1533 Failed to alter: TABLESPACE s_bad
942 Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... ADD DATAFILE
943 ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd';
944 ERROR HY000: Failed to alter: TABLESPACE s_bad
945 SHOW WARNINGS;
946 Level Code Message
947 Error 1533 Failed to alter: TABLESPACE s_bad
948 Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... DROP DATAFILE
949 ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd' INITIAL_SIZE = 1G;
950 ERROR HY000: Failed to alter: TABLESPACE s_bad
951 SHOW WARNINGS;
952 Level Code Message
953 Error 1533 Failed to alter: TABLESPACE s_bad
954 Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... ADD DATAFILE
955 ALTER TABLESPACE s_def ADD DATAFILE 'bad2.ibd' NO_WAIT;
956 ERROR HY000: Failed to alter: TABLESPACE s_def
957 SHOW WARNINGS;
958 Level Code Message
959 Error 1533 Failed to alter: TABLESPACE s_def
960 Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... ADD DATAFILE
961 ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd' INITIAL_SIZE = 1G;
962 ERROR HY000: Failed to alter: TABLESPACE s_bad
963 SHOW WARNINGS;
964 Level Code Message
965 Error 1533 Failed to alter: TABLESPACE s_bad
966 Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... DROP DATAFILE
967 ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd' NO_WAIT;
968 ERROR HY000: Failed to alter: TABLESPACE s_bad
969 SHOW WARNINGS;
970 Level Code Message
971 Error 1533 Failed to alter: TABLESPACE s_bad
972 Error 1178 The storage engine for the table doesn't support ALTER TABLESPACE ... DROP DATAFILE
973 #
974 # Undocumented ALTER TABLESPACE syntax which was removed by wl#8972
975 #
976 ALTER TABLESPACE s_bad CHANGE 's_bad.ibd'
977 INITIAL_SIZE = 1G, AUTOEXTEND_SIZE = 100M, MAX_SIZE = 2G;
978 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'
979 INITIAL_SIZE = 1G, AUTOEXTEND_SIZE = 100M, MAX_SIZE = 2' at line 1
980 ALTER TABLESPACE s_bad READ_ONLY;
981 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
982 ALTER TABLESPACE s_bad READ_WRITE;
983 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
984 ALTER TABLESPACE s_bad NOT ACCESSIBLE;
985 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
986 DROP TABLESPACE s_bad;
987 #
988 # Try the syntax that MySQL docs say is ignored.
989 #
990 CREATE TABLESPACE s_ignore1 ADD DATAFILE 's_ignore1.ibd' NODEGROUP = 5;
991 CREATE TABLESPACE s_ignore2 ADD DATAFILE 's_ignore2.ibd' WAIT;
992 CREATE TABLESPACE s_ignore3 ADD DATAFILE 's_ignore3.ibd' COMMENT = 'This comment is ignored';
993 === information_schema.innodb_tablespaces and innodb_datafiles ===
994 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
995 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
996 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
997 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
998 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
999 s_def General DEFAULT 0 1 1 Any s_def.ibd
1000 s_ignore1 General DEFAULT 0 1 1 Any s_ignore1.ibd
1001 s_ignore2 General DEFAULT 0 1 1 Any s_ignore2.ibd
1002 s_ignore3 General DEFAULT 0 1 1 Any s_ignore3.ibd
1003 === information_schema.files ===
1004 Space_Name File_Type Engine Status Tablespace_Name Path
1005 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
1006 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
1007 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
1008 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
1009 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
1010 s_ignore1 TABLESPACE InnoDB NORMAL s_ignore1 MYSQLD_DATADIR/s_ignore1.ibd
1011 s_ignore2 TABLESPACE InnoDB NORMAL s_ignore2 MYSQLD_DATADIR/s_ignore2.ibd
1012 s_ignore3 TABLESPACE InnoDB NORMAL s_ignore3 MYSQLD_DATADIR/s_ignore3.ibd
1013 DROP TABLESPACE s_ignore1;
1014 DROP TABLESPACE s_ignore2;
1015 DROP TABLESPACE s_ignore3;
1016 #
1017 # Try various ALTER TABLE statements.
1018 #
1019 CREATE TABLE t_in_def (a serial key, b text) TABLESPACE s_def;
1020 INSERT INTO t_in_def(b) VALUES(repeat("a short string - ",5));
1021 INSERT INTO t_in_def(b) VALUES(repeat("a long string - ",50));
1022 INSERT INTO t_in_def(b) SELECT b FROM t_in_def;
1023 SELECT a, left(b,50) FROM t_in_def;
1024 a left(b,50)
1025 1 a short string - a short string - a short string -
1026 2 a long string - a long string - a long string - a
1027 3 a short string - a short string - a short string -
1028 4 a long string - a long string - a long string - a
1029 SHOW CREATE TABLE t_in_def;
1030 Table Create Table
1031 t_in_def CREATE TABLE `t_in_def` (
1032 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1033 `b` text,
1034 PRIMARY KEY (`a`),
1035 UNIQUE KEY `a` (`a`)
1036 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1037 === information_schema.innodb_tables and innodb_tablespaces ===
1038 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1039 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1040 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1041 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1042 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1043 test/t_in_def s_def 161 5 Dynamic 0 General
1044 ALTER TABLE t_in_def ROW_FORMAT=redundant;
1045 SHOW CREATE TABLE t_in_def;
1046 Table Create Table
1047 t_in_def CREATE TABLE `t_in_def` (
1048 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1049 `b` text,
1050 PRIMARY KEY (`a`),
1051 UNIQUE KEY `a` (`a`)
1052 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1053 ALTER TABLE t_in_def ROW_FORMAT=dynamic;
1054 SHOW CREATE TABLE t_in_def;
1055 Table Create Table
1056 t_in_def CREATE TABLE `t_in_def` (
1057 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1058 `b` text,
1059 PRIMARY KEY (`a`),
1060 UNIQUE KEY `a` (`a`)
1061 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1062 ALTER TABLE t_in_def ADD COLUMN (c int);
1063 SHOW CREATE TABLE t_in_def;
1064 Table Create Table
1065 t_in_def CREATE TABLE `t_in_def` (
1066 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1067 `b` text,
1068 `c` int DEFAULT NULL,
1069 PRIMARY KEY (`a`),
1070 UNIQUE KEY `a` (`a`)
1071 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1072 ALTER TABLE t_in_def ADD INDEX c (c);
1073 SHOW CREATE TABLE t_in_def;
1074 Table Create Table
1075 t_in_def CREATE TABLE `t_in_def` (
1076 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1077 `b` text,
1078 `c` int DEFAULT NULL,
1079 PRIMARY KEY (`a`),
1080 UNIQUE KEY `a` (`a`),
1081 KEY `c` (`c`)
1082 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1083 ALTER TABLE t_in_def DROP INDEX c;
1084 SHOW CREATE TABLE t_in_def;
1085 Table Create Table
1086 t_in_def CREATE TABLE `t_in_def` (
1087 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1088 `b` text,
1089 `c` int DEFAULT NULL,
1090 PRIMARY KEY (`a`),
1091 UNIQUE KEY `a` (`a`)
1092 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1093 ALTER TABLE t_in_def DROP COLUMN c;
1094 SHOW CREATE TABLE t_in_def;
1095 Table Create Table
1096 t_in_def CREATE TABLE `t_in_def` (
1097 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1098 `b` text,
1099 PRIMARY KEY (`a`),
1100 UNIQUE KEY `a` (`a`)
1101 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1102 ALTER TABLE t_in_def AUTO_INCREMENT=100;
1103 SHOW CREATE TABLE t_in_def;
1104 Table Create Table
1105 t_in_def CREATE TABLE `t_in_def` (
1106 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1107 `b` text,
1108 PRIMARY KEY (`a`),
1109 UNIQUE KEY `a` (`a`)
1110 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1111 INSERT INTO t_in_def(b) VALUES("after ALTER TABLE AUTO_INCREMENT");
1112 SELECT a, left(b,50) FROM t_in_def;
1113 a left(b,50)
1114 1 a short string - a short string - a short string -
1115 2 a long string - a long string - a long string - a
1116 3 a short string - a short string - a short string -
1117 4 a long string - a long string - a long string - a
1118 100 after ALTER TABLE AUTO_INCREMENT
1119 SHOW CREATE TABLE t_in_def;
1120 Table Create Table
1121 t_in_def CREATE TABLE `t_in_def` (
1122 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1123 `b` text,
1124 PRIMARY KEY (`a`),
1125 UNIQUE KEY `a` (`a`)
1126 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1127 ALTER TABLE t_in_def CHANGE a aa int;
1128 SHOW CREATE TABLE t_in_def;
1129 Table Create Table
1130 t_in_def CREATE TABLE `t_in_def` (
1131 `aa` int NOT NULL,
1132 `b` text,
1133 PRIMARY KEY (`aa`),
1134 UNIQUE KEY `a` (`aa`)
1135 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1136 ALTER TABLE t_in_def CHANGE b bb varchar(1000);
1137 SHOW CREATE TABLE t_in_def;
1138 Table Create Table
1139 t_in_def CREATE TABLE `t_in_def` (
1140 `aa` int NOT NULL,
1141 `bb` varchar(1000) DEFAULT NULL,
1142 PRIMARY KEY (`aa`),
1143 UNIQUE KEY `a` (`aa`)
1144 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1145 ALTER TABLE t_in_def DROP PRIMARY KEY;
1146 SHOW CREATE TABLE t_in_def;
1147 Table Create Table
1148 t_in_def CREATE TABLE `t_in_def` (
1149 `aa` int NOT NULL,
1150 `bb` varchar(1000) DEFAULT NULL,
1151 UNIQUE KEY `a` (`aa`)
1152 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1153 ALTER TABLE t_in_def ADD PRIMARY KEY aa(aa), ALGORITHM=INPLACE;
1154 SHOW CREATE TABLE t_in_def;
1155 Table Create Table
1156 t_in_def CREATE TABLE `t_in_def` (
1157 `aa` int NOT NULL,
1158 `bb` varchar(1000) DEFAULT NULL,
1159 PRIMARY KEY (`aa`),
1160 UNIQUE KEY `a` (`aa`)
1161 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1162 ALTER TABLE t_in_def ADD INDEX ab(aa,bb(25)), ALGORITHM=COPY;
1163 SHOW CREATE TABLE t_in_def;
1164 Table Create Table
1165 t_in_def CREATE TABLE `t_in_def` (
1166 `aa` int NOT NULL,
1167 `bb` varchar(1000) DEFAULT NULL,
1168 PRIMARY KEY (`aa`),
1169 UNIQUE KEY `a` (`aa`),
1170 KEY `ab` (`aa`,`bb`(25))
1171 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1172 ALTER TABLE t_in_def ADD INDEX bb(bb(50));
1173 SHOW CREATE TABLE t_in_def;
1174 Table Create Table
1175 t_in_def CREATE TABLE `t_in_def` (
1176 `aa` int NOT NULL,
1177 `bb` varchar(1000) DEFAULT NULL,
1178 PRIMARY KEY (`aa`),
1179 UNIQUE KEY `a` (`aa`),
1180 KEY `ab` (`aa`,`bb`(25)),
1181 KEY `bb` (`bb`(50))
1182 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1183 RENAME TABLE t_in_def TO t;
1184 SHOW CREATE TABLE t;
1185 Table Create Table
1186 t CREATE TABLE `t` (
1187 `aa` int NOT NULL,
1188 `bb` varchar(1000) DEFAULT NULL,
1189 PRIMARY KEY (`aa`),
1190 UNIQUE KEY `a` (`aa`),
1191 KEY `ab` (`aa`,`bb`(25)),
1192 KEY `bb` (`bb`(50))
1193 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1194 === information_schema.innodb_tables and innodb_tablespaces ===
1195 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1196 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1197 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1198 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1199 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1200 test/t s_def 161 5 Dynamic 0 General
1201 INSERT INTO t VALUES(200, "after RENAME TABLE t_in_def TO t");
1202 SELECT aa, left(bb,50) FROM t;
1203 aa left(bb,50)
1204 1 a short string - a short string - a short string -
1205 2 a long string - a long string - a long string - a
1206 3 a short string - a short string - a short string -
1207 4 a long string - a long string - a long string - a
1208 100 after ALTER TABLE AUTO_INCREMENT
1209 200 after RENAME TABLE t_in_def TO t
1210 #
1211 # Move tables between tablespaces.
1212 #
1213 CREATE TABLESPACE s_alt1 ADD DATAFILE 's_alt1.ibd';
1214 CREATE TABLESPACE s_alt2 ADD DATAFILE 's_alt2.ibd';
1215 === information_schema.innodb_tablespaces and innodb_datafiles ===
1216 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
1217 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
1218 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
1219 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
1220 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
1221 s_alt1 General DEFAULT 0 1 1 Any s_alt1.ibd
1222 s_alt2 General DEFAULT 0 1 1 Any s_alt2.ibd
1223 s_def General DEFAULT 0 1 1 Any s_def.ibd
1224 === information_schema.files ===
1225 Space_Name File_Type Engine Status Tablespace_Name Path
1226 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
1227 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
1228 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
1229 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
1230 s_alt1 TABLESPACE InnoDB NORMAL s_alt1 MYSQLD_DATADIR/s_alt1.ibd
1231 s_alt2 TABLESPACE InnoDB NORMAL s_alt2 MYSQLD_DATADIR/s_alt2.ibd
1232 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
1233 ALTER TABLE t RENAME TO t_nomad, TABLESPACE s_alt1;
1234 SHOW CREATE TABLE t_nomad;
1235 Table Create Table
1236 t_nomad CREATE TABLE `t_nomad` (
1237 `aa` int NOT NULL,
1238 `bb` varchar(1000) DEFAULT NULL,
1239 PRIMARY KEY (`aa`),
1240 UNIQUE KEY `a` (`aa`),
1241 KEY `ab` (`aa`,`bb`(25)),
1242 KEY `bb` (`bb`(50))
1243 ) /*!50100 TABLESPACE `s_alt1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1244 === information_schema.innodb_tables and innodb_tablespaces ===
1245 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1246 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1247 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1248 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1249 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1250 test/t_nomad s_alt1 161 5 Dynamic 0 General
1251 ALTER TABLE t_nomad TABLESPACE s_alt2;
1252 SHOW CREATE TABLE t_nomad;
1253 Table Create Table
1254 t_nomad CREATE TABLE `t_nomad` (
1255 `aa` int NOT NULL,
1256 `bb` varchar(1000) DEFAULT NULL,
1257 PRIMARY KEY (`aa`),
1258 UNIQUE KEY `a` (`aa`),
1259 KEY `ab` (`aa`,`bb`(25)),
1260 KEY `bb` (`bb`(50))
1261 ) /*!50100 TABLESPACE `s_alt2` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1262 === information_schema.innodb_tables and innodb_tablespaces ===
1263 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1264 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1265 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1266 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1267 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1268 test/t_nomad s_alt2 161 5 Dynamic 0 General
1269 ALTER TABLE t_nomad TABLESPACE s_def, DROP COLUMN bb;
1270 SHOW CREATE TABLE t_nomad;
1271 Table Create Table
1272 t_nomad CREATE TABLE `t_nomad` (
1273 `aa` int NOT NULL,
1274 PRIMARY KEY (`aa`),
1275 UNIQUE KEY `a` (`aa`),
1276 KEY `ab` (`aa`)
1277 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1278 === information_schema.innodb_tables and innodb_tablespaces ===
1279 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1280 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1281 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1282 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1283 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1284 test/t_nomad s_def 161 4 Dynamic 0 General
1285 === information_schema.innodb_tablespaces and innodb_datafiles ===
1286 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
1287 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
1288 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
1289 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
1290 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
1291 s_alt1 General DEFAULT 0 1 1 Any s_alt1.ibd
1292 s_alt2 General DEFAULT 0 1 1 Any s_alt2.ibd
1293 s_def General DEFAULT 0 1 1 Any s_def.ibd
1294 === information_schema.files ===
1295 Space_Name File_Type Engine Status Tablespace_Name Path
1296 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
1297 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
1298 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
1299 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
1300 s_alt1 TABLESPACE InnoDB NORMAL s_alt1 MYSQLD_DATADIR/s_alt1.ibd
1301 s_alt2 TABLESPACE InnoDB NORMAL s_alt2 MYSQLD_DATADIR/s_alt2.ibd
1302 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
1303 DROP TABLE t_nomad;
1304 DROP TABLESPACE s_alt1;
1305 DROP TABLESPACE s_alt2;
1306 #
1307 # Move a table from the System Tablespace into a General Tablespace
1308 # and then back out to the system tablespace again.
1309 #
1310 SET GLOBAL innodb_file_per_table=OFF;
1311 CREATE TABLE t_system (a serial key, b text);
1312 INSERT INTO t_system(b) VALUES(repeat("a short string - ",5));
1313 INSERT INTO t_system(b) VALUES(repeat("a long string - ",50));
1314 SHOW CREATE TABLE t_system;
1315 Table Create Table
1316 t_system CREATE TABLE `t_system` (
1317 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1318 `b` text,
1319 PRIMARY KEY (`a`),
1320 UNIQUE KEY `a` (`a`)
1321 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1322 === information_schema.innodb_tables and innodb_tablespaces ===
1323 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1324 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1325 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1326 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1327 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1328 test/t_system innodb_system 33 5 Dynamic 0 System
1329 ALTER TABLE t_system TABLESPACE s_def;
1330 SHOW CREATE TABLE t_system;
1331 Table Create Table
1332 t_system CREATE TABLE `t_system` (
1333 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1334 `b` text,
1335 PRIMARY KEY (`a`),
1336 UNIQUE KEY `a` (`a`)
1337 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1338 === information_schema.innodb_tables and innodb_tablespaces ===
1339 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1340 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1341 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1342 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1343 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1344 test/t_system s_def 161 5 Dynamic 0 General
1345 ALTER TABLE t_system TABLESPACE=`innodb_system`;
1346 SHOW CREATE TABLE t_system;
1347 Table Create Table
1348 t_system CREATE TABLE `t_system` (
1349 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1350 `b` text,
1351 PRIMARY KEY (`a`),
1352 UNIQUE KEY `a` (`a`)
1353 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1354 === information_schema.innodb_tables and innodb_tablespaces ===
1355 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1356 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1357 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1358 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1359 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1360 test/t_system innodb_system 161 5 Dynamic 0 System
1361 #
1362 # Do a few ALTER TABLES for this table that was moved out and back into the system tablespace.
1363 #
1364 ALTER TABLE t_system ROW_FORMAT=redundant;
1365 SHOW CREATE TABLE t_system;
1366 Table Create Table
1367 t_system CREATE TABLE `t_system` (
1368 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1369 `b` text,
1370 PRIMARY KEY (`a`),
1371 UNIQUE KEY `a` (`a`)
1372 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1373 ALTER TABLE t_system ROW_FORMAT=dynamic;
1374 SHOW CREATE TABLE t_system;
1375 Table Create Table
1376 t_system CREATE TABLE `t_system` (
1377 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1378 `b` text,
1379 PRIMARY KEY (`a`),
1380 UNIQUE KEY `a` (`a`)
1381 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1382 ALTER TABLE t_system ADD COLUMN (c int);
1383 SHOW CREATE TABLE t_system;
1384 Table Create Table
1385 t_system CREATE TABLE `t_system` (
1386 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1387 `b` text,
1388 `c` int DEFAULT NULL,
1389 PRIMARY KEY (`a`),
1390 UNIQUE KEY `a` (`a`)
1391 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1392 ALTER TABLE t_system ADD INDEX c (c);
1393 SHOW CREATE TABLE t_system;
1394 Table Create Table
1395 t_system CREATE TABLE `t_system` (
1396 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1397 `b` text,
1398 `c` int DEFAULT NULL,
1399 PRIMARY KEY (`a`),
1400 UNIQUE KEY `a` (`a`),
1401 KEY `c` (`c`)
1402 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1403 ALTER TABLE t_system DROP INDEX c;
1404 SHOW CREATE TABLE t_system;
1405 Table Create Table
1406 t_system CREATE TABLE `t_system` (
1407 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1408 `b` text,
1409 `c` int DEFAULT NULL,
1410 PRIMARY KEY (`a`),
1411 UNIQUE KEY `a` (`a`)
1412 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1413 ALTER TABLE t_system DROP COLUMN c;
1414 SHOW CREATE TABLE t_system;
1415 Table Create Table
1416 t_system CREATE TABLE `t_system` (
1417 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1418 `b` text,
1419 PRIMARY KEY (`a`),
1420 UNIQUE KEY `a` (`a`)
1421 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1422 #
1423 # Now try to put that table into its own Single-Table tablespace.
1424 # It is not allowed since the table is now marked with the system tablespace
1425 #
1426 SET GLOBAL innodb_file_per_table=ON;
1427 ALTER TABLE t_system RENAME TO t_still_system;
1428 SHOW CREATE TABLE t_still_system;
1429 Table Create Table
1430 t_still_system CREATE TABLE `t_still_system` (
1431 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1432 `b` text,
1433 PRIMARY KEY (`a`),
1434 UNIQUE KEY `a` (`a`)
1435 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1436 === information_schema.innodb_tables and innodb_tablespaces ===
1437 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1438 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1439 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1440 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1441 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1442 test/t_still_system innodb_system 161 5 Dynamic 0 System
1443 DROP TABLE t_still_system;
1444 #
1445 # Try to create or move a table into the temporary tablespace.
1446 #
1447 CREATE TABLE t_not_temp (a int, b text) TABLESPACE=`innodb_temporary`;
1448 ERROR HY000: InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables.
1449 SHOW WARNINGS;
1450 Level Code Message
1451 Error 1478 InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables.
1452 Error 1031 Table storage engine for 't_not_temp' doesn't have this option
1453 CREATE TABLE t_not_temp (a int, b text) TABLESPACE s_def;
1454 ALTER TABLE t_not_temp TABLESPACE=`innodb_temporary`;
1455 ERROR HY000: InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables.
1456 SHOW WARNINGS;
1457 Level Code Message
1458 Error 1478 InnoDB: Tablespace `innodb_temporary` can only contain TEMPORARY tables.
1459 Error 1478 Table storage engine 'InnoDB' does not support the create option 'TABLESPACE'
1460 DROP TABLE t_not_temp;
1461 SET SESSION innodb_strict_mode=OFF;
1462 CREATE TEMPORARY TABLE t_my_temp (a int, b text) TABLESPACE=`innodb_temporary`;
1463 Warnings:
1464 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.
1465 SHOW CREATE TABLE t_my_temp;
1466 Table Create Table
1467 t_my_temp CREATE TEMPORARY TABLE `t_my_temp` (
1468 `a` int DEFAULT NULL,
1469 `b` text
1470 ) /*!50100 TABLESPACE `innodb_temporary` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1471 DROP TABLE t_my_temp;
1472 SET SESSION innodb_strict_mode=ON;
1473 #
1474 # Try to create or move a table into the redo tablespace
1475 # or any tablespace using the reserved `innodb_` prefix.
1476 #
1477 CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_redo_log`;
1478 ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved.
1479 SHOW WARNINGS;
1480 Level Code Message
1481 Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved.
1482 Error 3119 Incorrect tablespace name `innodb_redo_log`
1483 CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_anything`;
1484 ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved.
1485 SHOW WARNINGS;
1486 Level Code Message
1487 Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved.
1488 Error 3119 Incorrect tablespace name `innodb_anything`
1489 CREATE TABLE t_data (a int, b text) TABLESPACE s_def;
1490 ALTER TABLE t_data TABLESPACE=`innodb_redo_log`;
1491 ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved.
1492 SHOW WARNINGS;
1493 Level Code Message
1494 Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved.
1495 Error 3119 Incorrect tablespace name `innodb_redo_log`
1496 ALTER TABLE t_data TABLESPACE=`innodb_anything`;
1497 ERROR 42000: InnoDB: Tablespace names starting with `innodb_` are reserved.
1498 SHOW WARNINGS;
1499 Level Code Message
1500 Error 3119 InnoDB: Tablespace names starting with `innodb_` are reserved.
1501 Error 3119 Incorrect tablespace name `innodb_anything`
1502 DROP TABLE t_data;
1503 #
1504 # Move a table from a Single-Table tablespace into a General Tablespace.
1505 #
1506 CREATE TABLE t_single (a serial key, b text);
1507 INSERT INTO t_single(b) VALUES(repeat("a short string - ",5));
1508 INSERT INTO t_single(b) VALUES(repeat("a long string - ",50));
1509 SHOW CREATE TABLE t_single;
1510 Table Create Table
1511 t_single CREATE TABLE `t_single` (
1512 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1513 `b` text,
1514 PRIMARY KEY (`a`),
1515 UNIQUE KEY `a` (`a`)
1516 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1517 === information_schema.innodb_tables and innodb_tablespaces ===
1518 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1519 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1520 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1521 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1522 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1523 test/t_single test/t_single 33 5 Dynamic 0 Single
1524 ALTER TABLE t_single TABLESPACE s_def;
1525 SHOW CREATE TABLE t_single;
1526 Table Create Table
1527 t_single CREATE TABLE `t_single` (
1528 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1529 `b` text,
1530 PRIMARY KEY (`a`),
1531 UNIQUE KEY `a` (`a`)
1532 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1533 === information_schema.innodb_tables and innodb_tablespaces ===
1534 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1535 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1536 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1537 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1538 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1539 test/t_single s_def 161 5 Dynamic 0 General
1540 DROP TABLE t_single;
1541 #
1542 # Move a MyISAM table into an InnoDB General Tablespace and back.
1543 #
1544 CREATE TABLE t_myisam (a serial key, b int) ENGINE=MyISAM;
1545 INSERT INTO t_myisam(b) VALUES(11);
1546 SHOW CREATE TABLE t_myisam;
1547 Table Create Table
1548 t_myisam CREATE TABLE `t_myisam` (
1549 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1550 `b` int DEFAULT NULL,
1551 PRIMARY KEY (`a`),
1552 UNIQUE KEY `a` (`a`)
1553 ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1554 ALTER TABLE t_myisam RENAME TO t_innodb, TABLESPACE s_def, ENGINE=InnoDB;
1555 INSERT INTO t_innodb(b) VALUES(22);
1556 SHOW CREATE TABLE t_innodb;
1557 Table Create Table
1558 t_innodb CREATE TABLE `t_innodb` (
1559 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1560 `b` int DEFAULT NULL,
1561 PRIMARY KEY (`a`),
1562 UNIQUE KEY `a` (`a`)
1563 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1564 === information_schema.innodb_tables and innodb_tablespaces ===
1565 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1566 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1567 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1568 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1569 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1570 test/t_innodb s_def 161 5 Dynamic 0 General
1571 ALTER TABLE t_innodb RENAME TO t_myisam, ENGINE=MyISAM;
1572 INSERT INTO t_myisam(b) VALUES(33);
1573 SELECT * FROM t_myisam;
1574 a b
1575 1 11
1576 2 22
1577 3 33
1578 SHOW CREATE TABLE t_myisam;
1579 Table Create Table
1580 t_myisam CREATE TABLE `t_myisam` (
1581 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1582 `b` int DEFAULT NULL,
1583 PRIMARY KEY (`a`),
1584 UNIQUE KEY `a` (`a`)
1585 ) /*!50100 TABLESPACE `s_def` */ ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1586 ALTER TABLE t_myisam RENAME TO t_innodb, ENGINE=InnoDB;
1587 INSERT INTO t_innodb(b) VALUES(44);
1588 SHOW CREATE TABLE t_innodb;
1589 Table Create Table
1590 t_innodb CREATE TABLE `t_innodb` (
1591 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1592 `b` int DEFAULT NULL,
1593 PRIMARY KEY (`a`),
1594 UNIQUE KEY `a` (`a`)
1595 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1596 === information_schema.innodb_tables and innodb_tablespaces ===
1597 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1598 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1599 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1600 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1601 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1602 test/t_innodb s_def 161 5 Dynamic 0 General
1603 DROP TABLE t_innodb;
1604 #
1605 # Move a Dynamic table from an InnoDB General tablespace to MyISAM
1606 # and Memory and back without having to re-specify the tablespace
1607 # and row_format.
1608 #
1609 CREATE TABLE t_dyn_in_s_def (a serial key, b int) ROW_FORMAT=Dynamic TABLESPACE=s_def ENGINE=InnoDB;
1610 INSERT INTO t_dyn_in_s_def(b) VALUES(11);
1611 SHOW CREATE TABLE t_dyn_in_s_def;
1612 Table Create Table
1613 t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` (
1614 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1615 `b` int DEFAULT NULL,
1616 PRIMARY KEY (`a`),
1617 UNIQUE KEY `a` (`a`)
1618 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1619 === information_schema.innodb_tables and innodb_tablespaces ===
1620 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1621 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1622 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1623 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1624 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1625 test/t_dyn_in_s_def s_def 161 5 Dynamic 0 General
1626 ALTER TABLE t_dyn_in_s_def ENGINE=MyISAM;
1627 INSERT INTO t_dyn_in_s_def(b) VALUES(22);
1628 SHOW CREATE TABLE t_dyn_in_s_def;
1629 Table Create Table
1630 t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` (
1631 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1632 `b` int DEFAULT NULL,
1633 PRIMARY KEY (`a`),
1634 UNIQUE KEY `a` (`a`)
1635 ) /*!50100 TABLESPACE `s_def` */ ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1636 === information_schema.innodb_tables and innodb_tablespaces ===
1637 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1638 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1639 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1640 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1641 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1642 ALTER TABLE t_dyn_in_s_def ENGINE=InnoDB;
1643 INSERT INTO t_dyn_in_s_def(b) VALUES(33);
1644 SHOW CREATE TABLE t_dyn_in_s_def;
1645 Table Create Table
1646 t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` (
1647 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1648 `b` int DEFAULT NULL,
1649 PRIMARY KEY (`a`),
1650 UNIQUE KEY `a` (`a`)
1651 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1652 === information_schema.innodb_tables and innodb_tablespaces ===
1653 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1654 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1655 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1656 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1657 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1658 test/t_dyn_in_s_def s_def 161 5 Dynamic 0 General
1659 ALTER TABLE t_dyn_in_s_def ENGINE=Memory;
1660 INSERT INTO t_dyn_in_s_def(b) VALUES(44);
1661 SHOW CREATE TABLE t_dyn_in_s_def;
1662 Table Create Table
1663 t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` (
1664 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1665 `b` int DEFAULT NULL,
1666 PRIMARY KEY (`a`),
1667 UNIQUE KEY `a` (`a`)
1668 ) /*!50100 TABLESPACE `s_def` */ ENGINE=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1669 === information_schema.innodb_tables and innodb_tablespaces ===
1670 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1671 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1672 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1673 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1674 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1675 ALTER TABLE t_dyn_in_s_def ENGINE=InnoDB;
1676 INSERT INTO t_dyn_in_s_def(b) VALUES(55);
1677 SELECT * FROM t_dyn_in_s_def;
1678 a b
1679 1 11
1680 2 22
1681 3 33
1682 4 44
1683 5 55
1684 SHOW CREATE TABLE t_dyn_in_s_def;
1685 Table Create Table
1686 t_dyn_in_s_def CREATE TABLE `t_dyn_in_s_def` (
1687 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1688 `b` int DEFAULT NULL,
1689 PRIMARY KEY (`a`),
1690 UNIQUE KEY `a` (`a`)
1691 ) /*!50100 TABLESPACE `s_def` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1692 === information_schema.innodb_tables and innodb_tablespaces ===
1693 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1694 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1695 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1696 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1697 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1698 test/t_dyn_in_s_def s_def 161 5 Dynamic 0 General
1699 CHECK TABLE t_dyn_in_s_def;
1700 Table Op Msg_type Msg_text
1701 test.t_dyn_in_s_def check status OK
1702 DROP TABLE t_dyn_in_s_def;
1703 #
1704 # If a tablespace name is associated with a table in another SE, and that tablespace
1705 # does not exist in InnoDB, the ALTER TABLE will fail
1706 #
1707 CREATE TABLESPACE s_short_life ADD DATAFILE 'short_life.ibd';
1708 CREATE TABLE t_nomad (a serial key, b int) TABLESPACE=s_short_life ENGINE=InnoDB;
1709 INSERT INTO t_nomad(b) VALUES(11);
1710 SHOW CREATE TABLE t_nomad;
1711 Table Create Table
1712 t_nomad CREATE TABLE `t_nomad` (
1713 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1714 `b` int DEFAULT NULL,
1715 PRIMARY KEY (`a`),
1716 UNIQUE KEY `a` (`a`)
1717 ) /*!50100 TABLESPACE `s_short_life` */ ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1718 === information_schema.innodb_tables and innodb_tablespaces ===
1719 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1720 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1721 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1722 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1723 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1724 test/t_nomad s_short_life 161 5 Dynamic 0 General
1725 ALTER TABLE t_nomad ENGINE=MyISAM;
1726 INSERT INTO t_nomad(b) VALUES(22);
1727 SHOW CREATE TABLE t_nomad;
1728 Table Create Table
1729 t_nomad CREATE TABLE `t_nomad` (
1730 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1731 `b` int DEFAULT NULL,
1732 PRIMARY KEY (`a`),
1733 UNIQUE KEY `a` (`a`)
1734 ) /*!50100 TABLESPACE `s_short_life` */ ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1735 === information_schema.innodb_tables and innodb_tablespaces ===
1736 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1737 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1738 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1739 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1740 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1741 DROP TABLESPACE s_short_life;
1742 ALTER TABLE t_nomad ENGINE=InnoDB;
1743 ERROR HY000: Tablespace s_short_life doesn't exist.
1744 CREATE TABLESPACE s_shorter_life ADD DATAFILE 'shorter_life.ibd';
1745 ALTER TABLE t_nomad ENGINE=InnoDB TABLESPACE=s_shorter_life;
1746 SHOW CREATE TABLE t_nomad;
1747 Table Create Table
1748 t_nomad CREATE TABLE `t_nomad` (
1749 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
1750 `b` int DEFAULT NULL,
1751 PRIMARY KEY (`a`),
1752 UNIQUE KEY `a` (`a`)
1753 ) /*!50100 TABLESPACE `s_shorter_life` */ ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1754 === information_schema.innodb_tables and innodb_tablespaces ===
1755 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1756 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1757 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1758 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1759 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1760 test/t_nomad s_shorter_life 161 5 Dynamic 0 General
1761 DROP TABLE t_nomad;
1762 DROP TABLESPACE s_shorter_life;
1763 #
1764 # Show that a table with the same SQL name can be created in two different
1765 # databases, whether they are in the system tablespace, a general tablespace
1766 # or their own file-per-table tablespace.
1767 # Also show that DROP DATABASE will remove tables from inside a General Tablespace
1768 # without removing the tablespace or other tables in it for another databases.
1769 #
1770 SET GLOBAL innodb_file_per_table=OFF;
1771 CREATE TABLE t_system (a serial key, b text);
1772 CREATE TABLE t_general (a serial key, b text) TABLESPACE s_def;
1773 CREATE TABLE t_single (a serial key, b text) TABLESPACE=`innodb_file_per_table`;
1774 SHOW TABLES;
1775 Tables_in_test
1776 t_general
1777 t_single
1778 t_system
1779 CREATE DATABASE test1;
1780 USE test1;
1781 CREATE TABLESPACE s_empty1 ADD DATAFILE 's_empty1.ibd' ENGINE InnoDB;
1782 CREATE TABLE t_system (a serial key, b text);
1783 CREATE TABLE t_general (a serial key, b text) TABLESPACE s_def;
1784 CREATE TABLE t_single (a serial key, b text) TABLESPACE=`innodb_file_per_table`;
1785 SHOW TABLES;
1786 Tables_in_test1
1787 t_general
1788 t_single
1789 t_system
1790 USE test;
1791 === information_schema.innodb_tables and innodb_tablespaces ===
1792 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1793 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1794 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1795 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1796 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1797 test/t_general s_def 161 5 Dynamic 0 General
1798 test/t_single test/t_single 33 5 Dynamic 0 Single
1799 test/t_system innodb_system 33 5 Dynamic 0 System
1800 test1/t_general s_def 161 5 Dynamic 0 General
1801 test1/t_single test1/t_single 33 5 Dynamic 0 Single
1802 test1/t_system innodb_system 33 5 Dynamic 0 System
1803 === information_schema.innodb_tablespaces and innodb_datafiles ===
1804 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
1805 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
1806 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
1807 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
1808 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
1809 s_def General DEFAULT 0 1 1 Any s_def.ibd
1810 s_empty1 General DEFAULT 0 1 1 Any s_empty1.ibd
1811 test/t_single Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t_single.ibd
1812 test1/t_single Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test1/t_single.ibd
1813 === information_schema.files ===
1814 Space_Name File_Type Engine Status Tablespace_Name Path
1815 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
1816 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
1817 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
1818 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
1819 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
1820 s_empty1 TABLESPACE InnoDB NORMAL s_empty1 MYSQLD_DATADIR/s_empty1.ibd
1821 test/t_single TABLESPACE InnoDB NORMAL test/t_single MYSQLD_DATADIR/test/t_single.ibd
1822 test1/t_single TABLESPACE InnoDB NORMAL test1/t_single MYSQLD_DATADIR/test1/t_single.ibd
1823 DROP DATABASE test1;
1824 === information_schema.innodb_tables and innodb_tablespaces ===
1825 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1826 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1827 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1828 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1829 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1830 test/t_general s_def 161 5 Dynamic 0 General
1831 test/t_single test/t_single 33 5 Dynamic 0 Single
1832 test/t_system innodb_system 33 5 Dynamic 0 System
1833 === information_schema.innodb_tablespaces and innodb_datafiles ===
1834 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
1835 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
1836 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
1837 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
1838 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
1839 s_def General DEFAULT 0 1 1 Any s_def.ibd
1840 s_empty1 General DEFAULT 0 1 1 Any s_empty1.ibd
1841 test/t_single Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t_single.ibd
1842 === information_schema.files ===
1843 Space_Name File_Type Engine Status Tablespace_Name Path
1844 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
1845 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
1846 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
1847 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
1848 s_def TABLESPACE InnoDB NORMAL s_def MYSQLD_DATADIR/s_def.ibd
1849 s_empty1 TABLESPACE InnoDB NORMAL s_empty1 MYSQLD_DATADIR/s_empty1.ibd
1850 test/t_single TABLESPACE InnoDB NORMAL test/t_single MYSQLD_DATADIR/test/t_single.ibd
1851 DROP TABLE t_system;
1852 DROP TABLE t_general;
1853 DROP TABLE t_single;
1854 DROP TABLESPACE s_empty1;
1855 #
1856 # Assign different tablespace for PK-FK tables
1857 #
1858 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
1859 CREATE TABLE t1 (a int primary key) ENGINE=innodb TABLESPACE=s1;
1860 CREATE TABLE t2 (b int , foreign key (b) references t1(a)) ENGINE=innodb ;
1861 INSERT INTO t1 VALUES (100);
1862 INSERT INTO t1 VALUES (200);
1863 INSERT INTO t2 VALUES (100);
1864 === information_schema.innodb_tables and innodb_tablespaces ===
1865 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1866 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1867 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1868 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1869 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1870 test/t1 s1 161 4 Dynamic 0 General
1871 test/t2 innodb_system 33 4 Dynamic 0 System
1872 SELECT * FROM t1;
1873 a
1874 100
1875 200
1876 SELECT * FROM t2;
1877 b
1878 100
1879 SELECT * FROM t1,t2 WHERE t1.a=t2.b;
1880 a b
1881 100 100
1882 DROP TABLE t2,t1;
1883 DROP TABLESPACE s1;
1884 #
1885 # Assigning general tablespace from procedure
1886 #
1887 USE test;
1888 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
1889 CREATE TABLE t1 (a geometry NOT NULL SRID 0, SPATIAL (a)) ENGINE=InnoDB;
1890 INSERT INTO t1 VALUES (st_GeomFromText("LINESTRING(100 100, 200 200, 300 300)")) ;
1891 CREATE PROCEDURE p1()
1892 BEGIN
1893 ALTER TABLE t1 TABLESPACE=s1;
1894 END|
1895 === information_schema.innodb_tables and innodb_tablespaces ===
1896 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1897 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1898 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1899 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1900 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1901 test/t1 innodb_system 33 4 Dynamic 0 System
1902 CALL p1();
1903 === information_schema.innodb_tables and innodb_tablespaces ===
1904 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
1905 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
1906 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
1907 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
1908 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
1909 test/t1 s1 161 4 Dynamic 0 General
1910 DROP PROCEDURE p1;
1911 #
1912 # Show that DISCARD/IMPORT tablespace does not work on a general tablespace.
1913 #
1914 ALTER TABLE t1 DISCARD TABLESPACE;
1915 ERROR 42000: InnoDB: Cannot discard table `test/t1` because it is in a general tablespace. It must be file-per-table.
1916 SHOW WARNINGS;
1917 Level Code Message
1918 Error 1148 InnoDB: Cannot discard table `test/t1` because it is in a general tablespace. It must be file-per-table.
1919 Error 1148 The used command is not allowed with this MySQL version
1920 ALTER TABLE t1 IMPORT TABLESPACE;
1921 ERROR 42000: InnoDB: Cannot import table `test/t1` because it is in a general tablespace. It must be file-per-table.
1922 SHOW WARNINGS;
1923 Level Code Message
1924 Error 1148 InnoDB: Cannot import table `test/t1` because it is in a general tablespace. It must be file-per-table.
1925 Error 1148 The used command is not allowed with this MySQL version
1926 DROP TABLE t1;
1927 DROP TABLESPACE s1;
1928 #
1929 # Clean-up.
1930 #
1931 DROP TABLESPACE s_def;
1932 SET GLOBAL innodb_file_per_table=default;
1933 SET GLOBAL innodb_strict_mode=default;
1934 #
1935 # Bug #20763179 SEGV WHEN CREATE TABLESPACE IS EXECUTED IN READ ONLY MODE
1936 #
1937 # Check CREATE TABLESPACE is handled properly in read-only mode
1938 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
1939 SET GLOBAL innodb_fast_shutdown = 0;
1940 # restart
1941 CREATE TABLESPACE s2 ADD DATAFILE 's2.ibd' ENGINE InnoDB;
1942 ERROR HY000: Running in read-only mode
1943 SHOW WARNINGS;
1944 Level Code Message
1945 Warning 1836 InnoDB: Running in read-only mode
1946 Error 1836 Running in read-only mode
1947 DROP TABLESPACE s1;
1948 ERROR HY000: Changes to undo tablespaces are not allowed in read_only mode
1949 SHOW WARNINGS;
1950 Level Code Message
1951 Error 1874 Changes to undo tablespaces are not allowed in read_only mode
1952 Error 1529 Failed to drop TABLESPACE s1
1953 Error 1874 InnoDB is in read only mode.
1954 # Restart in normal mode for cleanup.
1955 # restart: --innodb-directories=MYSQL_TMP_DIR
1956 DROP TABLESPACE s1;
1957 #
1958 # Bug#26199233 create like ignores innodb_file_per_table
1959 #
1960 # - Verify if table picks correct tablespace based on
1961 # innodb_file_per_table settings.
1962 # - Check if SHOW CREATE displays correct tablespace name.
1963 CREATE DATABASE db1;
1964 SET @@global.innodb_file_per_table = OFF;
1965 # Case 1
1966 # SHOW CREATE should not show TABLESPACE clause after the fix.
1967 # Because it was not explicitly specified by user.
1968 CREATE TABLE db1.t1 (c1 INT) ENGINE=InnoDB;
1969 SHOW CREATE TABLE db1.t1;
1970 Table Create Table
1971 t1 CREATE TABLE `t1` (
1972 `c1` int DEFAULT NULL
1973 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1974 SET @@global.innodb_file_per_table = ON;
1975 # Case 2
1976 # After the fix table, t2 should use innodb_file_per_table tablespace.
1977 CREATE TABLE db1.t2 LIKE db1.t1;
1978 SHOW CREATE TABLE db1.t2;
1979 Table Create Table
1980 t2 CREATE TABLE `t2` (
1981 `c1` int DEFAULT NULL
1982 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1983 # Verify the same by listing the .ibd file.
1984 t2.ibd
1985 # Case 3
1986 # db1.tables TABLESPACE clause should show 'ts0' after the fix.
1987 CREATE TABLESPACE ts0 ADD DATAFILE 'df0.ibd';
1988 CREATE TABLE db1.tables (c1 INT) ENGINE=InnoDB tablespace=ts0;
1989 SHOW CREATE TABLE db1.tables;
1990 Table Create Table
1991 tables CREATE TABLE `tables` (
1992 `c1` int DEFAULT NULL
1993 ) /*!50100 TABLESPACE `ts0` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1994 DROP TABLE db1.tables;
1995 DROP TABLESPACE ts0;
1996 # Cleanup
1997 DROP DATABASE db1;
1998 # Additional test cases for Bug#26199233 to
1999 # test various combinations.
2000 CREATE SCHEMA s1;
2001 USE s1;
2002 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd';
2003 #
2004 # Tables with explicitly assigned tablespaces.
2005 #
2006 # Table with an explicitly assigned user tablespace.
2007 CREATE TABLE t_expl_usr (i INT) TABLESPACE s1;
2008 SHOW CREATE TABLE t_expl_usr;
2009 Table Create Table
2010 t_expl_usr CREATE TABLE `t_expl_usr` (
2011 `i` int DEFAULT NULL
2012 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2013 #
2014 # Table with an explicitly assigned system tablespace.
2015 CREATE TABLE t_expl_sys (i INT) TABLESPACE innodb_system;
2016 SHOW CREATE TABLE t_expl_sys;
2017 Table Create Table
2018 t_expl_sys CREATE TABLE `t_expl_sys` (
2019 `i` int DEFAULT NULL
2020 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2021 #
2022 # Table with an explicitly assigned f_p_t tablespace.
2023 CREATE TABLE t_expl_fpt (i INT) TABLESPACE innodb_file_per_table;
2024 SHOW CREATE TABLE t_expl_fpt;
2025 Table Create Table
2026 t_expl_fpt CREATE TABLE `t_expl_fpt` (
2027 `i` int DEFAULT NULL
2028 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2029 #
2030 # Temporary table with an explicitly assigned tmp tablespace.
2031 SET SESSION innodb_strict_mode=OFF;
2032 CREATE TEMPORARY TABLE t_expl_tmp (i INT) TABLESPACE innodb_temporary;
2033 Warnings:
2034 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.
2035 SHOW WARNINGS;
2036 Level Code Message
2037 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.
2038 SET SESSION innodb_strict_mode=ON;
2039 #
2040 # Tables with implicitly assigned tablespaces.
2041 #
2042 # Table with an implicitly assigned system tablespace.
2043 SET GLOBAL innodb_file_per_table= OFF;
2044 CREATE TABLE t_impl_sys (i INT);
2045 SHOW CREATE TABLE t_impl_sys;
2046 Table Create Table
2047 t_impl_sys CREATE TABLE `t_impl_sys` (
2048 `i` int DEFAULT NULL
2049 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2050 #
2051 # Table with an implicitly assigned f_p_t tablespace.
2052 SET GLOBAL innodb_file_per_table= ON;
2053 CREATE TABLE t_impl_fpt (i INT);
2054 SHOW CREATE TABLE t_impl_fpt;
2055 Table Create Table
2056 t_impl_fpt CREATE TABLE `t_impl_fpt` (
2057 `i` int DEFAULT NULL
2058 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2059 #
2060 # Temporary table with an implicitly assigned tmp tablespace.
2061 CREATE TEMPORARY TABLE t_impl_tmp (i INT);
2062 SHOW CREATE TABLE t_impl_tmp;
2063 Table Create Table
2064 t_impl_tmp CREATE TEMPORARY TABLE `t_impl_tmp` (
2065 `i` int DEFAULT NULL
2066 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2067 #
2068 # CREATE LIKE when innodb_file_per_table is ON.
2069 #
2070 # Tablespace preserved for all source tables that
2071 # had an explicitly assigned tablesapce.
2072 SET GLOBAL innodb_file_per_table= ON;
2073 CREATE TABLE fpt_on_like_t_expl_usr LIKE t_expl_usr;
2074 SHOW CREATE TABLE fpt_on_like_t_expl_usr;
2075 Table Create Table
2076 fpt_on_like_t_expl_usr CREATE TABLE `fpt_on_like_t_expl_usr` (
2077 `i` int DEFAULT NULL
2078 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2079 CREATE TABLE fpt_on_like_t_expl_sys LIKE t_expl_sys;
2080 SHOW CREATE TABLE fpt_on_like_t_expl_sys;
2081 Table Create Table
2082 fpt_on_like_t_expl_sys CREATE TABLE `fpt_on_like_t_expl_sys` (
2083 `i` int DEFAULT NULL
2084 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2085 CREATE TABLE fpt_on_like_t_expl_fpt LIKE t_expl_fpt;
2086 SHOW CREATE TABLE fpt_on_like_t_expl_fpt;
2087 Table Create Table
2088 fpt_on_like_t_expl_fpt CREATE TABLE `fpt_on_like_t_expl_fpt` (
2089 `i` int DEFAULT NULL
2090 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2091 SET SESSION innodb_strict_mode=OFF;
2092 CREATE TEMPORARY TABLE fpt_on_like_t_expl_tmp LIKE t_expl_tmp;
2093 Warnings:
2094 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.
2095 SHOW WARNINGS;
2096 Level Code Message
2097 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.
2098 SET SESSION innodb_strict_mode=OFF;
2099 # Tablespace not preserved for all source tables that
2100 # had an implicitly assigned tablesapce.
2101 CREATE TABLE fpt_on_like_t_impl_sys LIKE t_impl_sys;
2102 SHOW CREATE TABLE fpt_on_like_t_impl_sys;
2103 Table Create Table
2104 fpt_on_like_t_impl_sys CREATE TABLE `fpt_on_like_t_impl_sys` (
2105 `i` int DEFAULT NULL
2106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2107 CREATE TABLE fpt_on_like_t_impl_fpt LIKE t_impl_fpt;
2108 SHOW CREATE TABLE fpt_on_like_t_impl_fpt;
2109 Table Create Table
2110 fpt_on_like_t_impl_fpt CREATE TABLE `fpt_on_like_t_impl_fpt` (
2111 `i` int DEFAULT NULL
2112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2113 CREATE TEMPORARY TABLE fpt_on_like_t_impl_tmp LIKE t_impl_tmp;
2114 SHOW CREATE TABLE fpt_on_like_t_impl_tmp;
2115 Table Create Table
2116 fpt_on_like_t_impl_tmp CREATE TEMPORARY TABLE `fpt_on_like_t_impl_tmp` (
2117 `i` int DEFAULT NULL
2118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2119 #
2120 # CREATE LIKE when innodb_file_per_table is OFF.
2121 #
2122 # Tablespace preserved for all source tables that
2123 # had an explicitly assigned tablesapce.
2124 SET GLOBAL innodb_file_per_table= OFF;
2125 CREATE TABLE fpt_off_like_t_expl_usr LIKE t_expl_usr;
2126 SHOW CREATE TABLE fpt_off_like_t_expl_usr;
2127 Table Create Table
2128 fpt_off_like_t_expl_usr CREATE TABLE `fpt_off_like_t_expl_usr` (
2129 `i` int DEFAULT NULL
2130 ) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2131 CREATE TABLE fpt_off_like_t_expl_sys LIKE t_expl_sys;
2132 CREATE TABLE fpt_off_like_t_expl_fpt LIKE t_expl_fpt;
2133 SHOW CREATE TABLE fpt_off_like_t_expl_fpt;
2134 Table Create Table
2135 fpt_off_like_t_expl_fpt CREATE TABLE `fpt_off_like_t_expl_fpt` (
2136 `i` int DEFAULT NULL
2137 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2138 CREATE TEMPORARY TABLE fpt_off_like_t_expl_tmp LIKE t_expl_tmp;
2139 Warnings:
2140 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.
2141 # Tablespace not preserved for all source tables that
2142 # had an implicitly assigned tablesapce.
2143 CREATE TABLE fpt_off_like_t_impl_sys LIKE t_impl_sys;
2144 SHOW CREATE TABLE fpt_off_like_t_impl_sys;
2145 Table Create Table
2146 fpt_off_like_t_impl_sys CREATE TABLE `fpt_off_like_t_impl_sys` (
2147 `i` int DEFAULT NULL
2148 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2149 CREATE TABLE fpt_off_like_t_impl_fpt LIKE t_impl_fpt;
2150 SHOW CREATE TABLE fpt_off_like_t_impl_fpt;
2151 Table Create Table
2152 fpt_off_like_t_impl_fpt CREATE TABLE `fpt_off_like_t_impl_fpt` (
2153 `i` int DEFAULT NULL
2154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2155 CREATE TEMPORARY TABLE fpt_off_like_t_impl_tmp LIKE t_impl_tmp;
2156 SHOW CREATE TABLE fpt_off_like_t_impl_tmp;
2157 Table Create Table
2158 fpt_off_like_t_impl_tmp CREATE TEMPORARY TABLE `fpt_off_like_t_impl_tmp` (
2159 `i` int DEFAULT NULL
2160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2161 # The end result should be that we have .ibd files for:
2162 # fpt_off_like_t_expl_fpt.ibd
2163 # fpt_on_like_t_expl_fpt.ibd
2164 # fpt_on_like_t_impl_fpt.ibd
2165 # fpt_on_like_t_impl_sys.ibd
2166 # t_expl_fpt.ibd
2167 # t_impl_fpt.ibd
2168 fpt_off_like_t_expl_fpt.ibd
2169 fpt_on_like_t_expl_fpt.ibd
2170 fpt_on_like_t_impl_fpt.ibd
2171 fpt_on_like_t_impl_sys.ibd
2172 t_expl_fpt.ibd
2173 t_impl_fpt.ibd
2174 # Now, we can ALTER the tables that have implicitly
2175 # assigned tablespaces to instead assign the spaces
2176 # explicitly. This will make SHOW CREATE show the
2177 # tablespace names.
2178 ALTER TABLE t_impl_sys TABLESPACE innodb_system;
2179 SHOW CREATE TABLE t_impl_sys;
2180 Table Create Table
2181 t_impl_sys CREATE TABLE `t_impl_sys` (
2182 `i` int DEFAULT NULL
2183 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2184 ALTER TABLE t_impl_fpt TABLESPACE innodb_file_per_table;
2185 SHOW CREATE TABLE t_impl_fpt;
2186 Table Create Table
2187 t_impl_fpt CREATE TABLE `t_impl_fpt` (
2188 `i` int DEFAULT NULL
2189 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2190 ALTER TABLE t_impl_tmp TABLESPACE innodb_temporary;
2191 Warnings:
2192 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.
2193 SHOW CREATE TABLE t_impl_tmp;
2194 Table Create Table
2195 t_impl_tmp CREATE TEMPORARY TABLE `t_impl_tmp` (
2196 `i` int DEFAULT NULL
2197 ) /*!50100 TABLESPACE `innodb_temporary` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2198 # And CREATE TABLE LIKE will now preserve the
2199 # tablespace name of the above tables.
2200 CREATE TABLE after_alter_like_t_impl_sys LIKE t_impl_sys;
2201 SHOW CREATE TABLE after_alter_like_t_impl_sys;
2202 Table Create Table
2203 after_alter_like_t_impl_sys CREATE TABLE `after_alter_like_t_impl_sys` (
2204 `i` int DEFAULT NULL
2205 ) /*!50100 TABLESPACE `innodb_system` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2206 CREATE TABLE after_alter_like_t_impl_fpt LIKE t_impl_fpt;
2207 SHOW CREATE TABLE after_alter_like_t_impl_fpt;
2208 Table Create Table
2209 after_alter_like_t_impl_fpt CREATE TABLE `after_alter_like_t_impl_fpt` (
2210 `i` int DEFAULT NULL
2211 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2212 CREATE TEMPORARY TABLE after_alter_like_t_impl_tmp LIKE t_impl_tmp;
2213 Warnings:
2214 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.
2215 SHOW CREATE TABLE after_alter_like_t_impl_tmp;
2216 Table Create Table
2217 after_alter_like_t_impl_tmp CREATE TEMPORARY TABLE `after_alter_like_t_impl_tmp` (
2218 `i` int DEFAULT NULL
2219 ) /*!50100 TABLESPACE `innodb_temporary` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
2220 DROP SCHEMA s1;
2221 DROP TABLESPACE s1;
2222 SET GLOBAL innodb_file_per_table= DEFAULT;