Debian Codegraph @

mysql-test/suite/innodb/t/create_tablespace.test

raw
1 #
2 # A series of tests to show the correct behavior for
3 # CREATE TABLESPACE and associated SQL statements.
4 #
5
6 --echo #
7 --echo # CREATE TABLESPACE related tests.
8 --echo #
9
10 SET DEFAULT_STORAGE_ENGINE=InnoDB;
11 SET NAMES utf8;
12 LET $MYSQLD_DATADIR = `select @@datadir`;
13 LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
14
15 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
16 SHOW VARIABLES LIKE 'innodb_directories';
17
18 --echo # Strict-mode has no effect on CREATE TABLESPACE.
19 --echo # It rejects all invalid input, as if strict mode is always ON.
20 SHOW VARIABLES LIKE 'innodb_strict_mode';
21
22 --echo # The setting innodb_file_per_table=OFF will not prevent
23 --echo # CREATE TABLESPACE from working because the tablespace is
24 --echo # an empty shell that can contain multiple row formats.
25 SHOW VARIABLES LIKE 'innodb_file_per_table';
26
27 --echo #
28 --echo # Try to create a tablespace without specifying the name
29 --echo #
30 --error ER_PARSE_ERROR
31 CREATE TABLESPACE;
32
33 --echo # Try to create a tablespace with bad characters in the tablespace name identifier.
34 --echo #
35 # From; http://dev.mysql.com/doc/refman/5.7/en/identifiers.html
36 # An identifier must be either unquoted or quoted with (`).
37 # Identifiers are converted to Unicode internally. They may contain these characters:
38 # Permitted characters in unquoted identifiers:
39 # ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
40 # Extended: U+0080 .. U+FFFF
41 # Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:
42 # ASCII: U+0001 .. U+007F
43 # Extended: U+0080 .. U+FFFF
44 # ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers.
45 # Identifiers may begin with a digit but unless quoted may not consist solely of digits.
46 # Database, table, and column names cannot end with space characters.
47 # The identifier quote character is the backtick (`)
48
49 --error ER_PARSE_ERROR
50 CREATE TABLESPACE 's_bad' ADD DATAFILE 's_bad.ibd';
51 --error ER_PARSE_ERROR
52 CREATE TABLESPACE "s_bad" ADD DATAFILE 's_bad.ibd';
53 --error ER_WRONG_TABLESPACE_NAME
54 CREATE TABLESPACE `` ADD DATAFILE 's_bad.ibd';
55 --error ER_PARSE_ERROR
56 CREATE TABLESPACE s@bad ADD DATAFILE 's_bad.ibd';
57 --error ER_PARSE_ERROR
58 CREATE TABLESPACE s-bad ADD DATAFILE 's_bad.ibd';
59 --error ER_PARSE_ERROR
60 CREATE TABLESPACE test/s_bad ADD DATAFILE 's_bad.ibd';
61 --error ER_WRONG_TABLESPACE_NAME
62 CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd';
63 LET $TWOFIFTYFIVE=../xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;
64 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
65 --error ER_WRONG_FILE_NAME
66 eval CREATE TABLESPACE `s_too_long_file_name` ADD DATAFILE '$TWOFIFTYFIVE.ibd';
67 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
68 SHOW WARNINGS;
69 --error ER_FILEGROUP_OPTION_ONLY_ONCE
70 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=1k FILE_BLOCK_SIZE=2k;
71 --error ER_ILLEGAL_HA_CREATE_OPTION
72 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=3k;
73 --error ER_ILLEGAL_HA_CREATE_OPTION
74 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=65k;
75 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
76 --error ER_WRONG_FILE_NAME
77 CREATE TABLESPACE `s_bad` ADD DATAFILE '../sub/dir/////s_bad.ibd';
78 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
79 SHOW WARNINGS;
80 # Show that those directories were not created.
81 --mkdir $MYSQLD_DATADIR/sub
82 --mkdir $MYSQLD_DATADIR/sub/dir
83 --rmdir $MYSQLD_DATADIR/sub/dir
84 --rmdir $MYSQLD_DATADIR/sub
85
86 --error ER_PARSE_ERROR
87 DROP TABLESPACE 's_bad';
88 --error ER_PARSE_ERROR
89 DROP TABLESPACE "s_bad";
90 --error ER_WRONG_TABLESPACE_NAME
91 DROP TABLESPACE ``;
92 SHOW WARNINGS;
93 # "DROP TABLESPACE s#bad" is the same as "DROP TABLESPACE s".
94 # Unlike "CREATE TABLESPACE s", this is valid syntax, so we
95 # don't get parse error here.
96 --error ER_TABLESPACE_MISSING_WITH_NAME
97 DROP TABLESPACE s#bad;
98 --error ER_PARSE_ERROR
99 DROP TABLESPACE s@bad;
100 --error ER_PARSE_ERROR
101 DROP TABLESPACE s-bad;
102 --error ER_TABLESPACE_MISSING_WITH_NAME
103 DROP TABLESPACE `test/s_bad`;
104 --error ER_TABLESPACE_MISSING_WITH_NAME
105 DROP TABLESPACE s_does_not_exist;
106
107
108 --echo #
109 --echo # InnoDB does not allow General tablespace names with '/'
110 --echo #
111 --error ER_WRONG_TABLESPACE_NAME
112 CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd';
113 SHOW WARNINGS;
114
115 # When identifiers are quoted, most anything is allowed.
116 # Table names cannot have a trailing space, but tablespaces can.
117 CREATE TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` ADD DATAFILE 's_utf8.ibd';
118 CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` (a int, b text) TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `;
119 INSERT INTO `t !@#$%^&*()_+-={}[]|\?<>,.` VALUES(1,'one');
120 SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
121 CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2` (
122 `a` int(11) DEFAULT NULL,
123 `b` text
124 ) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=latin1;
125 SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
126 DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2`;
127
128 CREATE TABLESPACE `#sql_1` ADD DATAFILE '#sql_1.ibd';
129 CREATE TABLE `#sql_1` (a int, b text) TABLESPACE `#sql_1`;
130 INSERT INTO `#sql_1` VALUES(1,'one');
131 SHOW CREATE TABLE `#sql_1`;
132
133 CREATE TABLESPACE `s_Cöŀumň` ADD DATAFILE 's_utf8_a.ibd';
134 CREATE TABLE `t_utf8_1` (a int, b text) TABLESPACE `s_Cöŀumň`;
135 SHOW CREATE TABLE `t_utf8_1`;
136
137 CREATE TABLESPACE `s_cöĿǖmň` ADD DATAFILE 's_utf8_b.ibd';
138 CREATE TABLE `t_utf8_2` (a int, b text) TABLESPACE `s_cöĿǖmň`;
139 SHOW CREATE TABLE `t_utf8_2`;
140
141 --error ER_INVALID_CHARACTER_STRING
142 CREATE TABLESPACE `s_😲` ADD DATAFILE 's_utf8_c.ibd';
143 --error ER_INVALID_CHARACTER_STRING
144 CREATE TABLESPACE `s_𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜` ADD DATAFILE 's_utf8_d.ibd';
145
146 CREATE TABLESPACE `s_வணக்கம்` ADD DATAFILE 'ஆவணம்.ibd';
147 CREATE TABLE `t_utf8_3` (a int, b text) TABLESPACE `s_வணக்கம்`;
148 SHOW CREATE TABLE `t_utf8_3`;
149
150 --source suite/innodb/include/show_i_s_tablespaces.inc
151 --source suite/innodb/include/show_i_s_tables.inc
152 --echo # Directory listing of MYSQLD_DATADIR/
153 --list_files $MYSQLD_DATADIR *.ibd
154
155 CHECK TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
156 DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`;
157 DROP TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `;
158 CHECK TABLE `#sql_1`;
159 DROP TABLE `#sql_1`;
160 DROP TABLESPACE `#sql_1`;
161 DROP TABLE `t_utf8_1`;
162 DROP TABLESPACE `s_Cöŀumň`;
163 DROP TABLE `t_utf8_2` ;
164 DROP TABLESPACE `s_cöĿǖmň` ;
165 DROP TABLE `t_utf8_3`;
166 DROP TABLESPACE `s_வணக்கம்`;
167
168 --echo #
169 --echo # Try to create a tablespace with the reserved case-sensitive prefix 'innodb_'
170 --echo #
171 --error ER_WRONG_TABLESPACE_NAME
172 CREATE TABLESPACE `innodb_system` ADD DATAFILE 's_bad.ibd';
173 --error ER_WRONG_TABLESPACE_NAME
174 DROP TABLESPACE `innodb_system`;
175 SHOW WARNINGS;
176 # Possible, but not wise!
177 CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 's_InnoDB_System.ibd';
178 DROP TABLESPACE `InnoDB_System`;
179 --error ER_TABLESPACE_DUP_FILENAME
180 CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1';
181 SHOW WARNINGS;
182 # Possible, but not wise!
183 CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1.ibd';
184 DROP TABLESPACE `InnoDB_System`;
185
186 --error ER_WRONG_TABLESPACE_NAME
187 CREATE TABLESPACE `innodb_temporary` ADD DATAFILE 's_bad.ibd';
188 SHOW WARNINGS;
189 --error ER_WRONG_TABLESPACE_NAME
190 DROP TABLESPACE `innodb_temporary`;
191 # Possible, but not wise!
192 CREATE TABLESPACE `InnoDB_Temporary` ADD DATAFILE 's_InnoDB_Temporary.ibd';
193 DROP TABLESPACE `InnoDB_Temporary`;
194
195 --error ER_WRONG_TABLESPACE_NAME
196 CREATE TABLESPACE `innodb_custom` ADD DATAFILE 's_bad.ibd';
197 SHOW WARNINGS;
198 --error ER_TABLESPACE_MISSING_WITH_NAME
199 DROP TABLESPACE `innodb_custom`;
200 CREATE TABLESPACE `InnoDB_Custom` ADD DATAFILE 's_InnoDB_Custom.ibd';
201 DROP TABLESPACE `InnoDB_Custom`;
202 CREATE TABLESPACE `INNODB_CUSTOM` ADD DATAFILE 's_INNODB_CUSTOM.ibd';
203 DROP TABLESPACE `INNODB_CUSTOM`;
204
205 --error ER_TABLESPACE_MISSING_WITH_NAME
206 DROP TABLESPACE `ib_logfile0`;
207 CREATE TABLESPACE `ib_logfile0` ADD DATAFILE 'ib_logfile0.ibd';
208 DROP TABLESPACE `ib_logfile0`;
209
210 --error ER_TABLESPACE_MISSING_WITH_NAME
211 DROP TABLESPACE `ib_logfile1`;
212 CREATE TABLESPACE `ib_logfile1` ADD DATAFILE 'ib_logfile1.ibd';
213 DROP TABLESPACE `ib_logfile1`;
214
215 --error ER_TABLESPACE_MISSING_WITH_NAME
216 DROP TABLESPACE `ibdata1`;
217 CREATE TABLESPACE `ibdata1` ADD DATAFILE 'ibdata1.ibd';
218 DROP TABLESPACE `ibdata1`;
219
220 --error ER_TABLESPACE_MISSING_WITH_NAME
221 DROP TABLESPACE `undo_001`;
222 CREATE TABLESPACE `undo_001` ADD DATAFILE 'undo_001.ibd';
223 DROP TABLESPACE `undo_001`;
224
225 --error ER_TABLESPACE_MISSING_WITH_NAME
226 DROP TABLESPACE `undo_002`;
227 CREATE TABLESPACE `undo_002` ADD DATAFILE 'undo_002.ibd';
228 DROP TABLESPACE `undo_002`;
229
230 --echo #
231 --echo # Test various forms of ADD DATAFILE
232 --echo #
233
234 --mkdir $MYSQL_TMP_DIR/tablespace.ibd
235 --rmdir $MYSQL_TMP_DIR/tablespace.ibd
236 --mkdir $MYSQL_TMP_DIR/tablespace.ibd
237 --mkdir $MYSQL_TMP_DIR/s2_#_dir
238 --mkdir $MYSQL_TMP_DIR/test
239
240 CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd' ENGINE=InnoDB;
241 CREATE TABLESPACE `s1_#_hash` ADD DATAFILE 's1_#_hash.ibd';
242
243 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
244 eval CREATE TABLESPACE s1_remote ADD DATAFILE '$MYSQL_TMP_DIR/s1.ibd.ibd';
245 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
246 eval CREATE TABLESPACE s2_remote ADD DATAFILE '$MYSQL_TMP_DIR/s2_#_dir/s2.ibd';
247
248 --error ER_WRONG_FILE_NAME
249 CREATE TABLESPACE s_bad ADD DATAFILE '.ibd';
250 SHOW WARNINGS;
251 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
252 --error ER_WRONG_FILE_NAME
253 CREATE TABLESPACE s_bad ADD DATAFILE '../s_dir/s_subdir/.ibd';
254 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
255 SHOW WARNINGS;
256 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
257 --error ER_WRONG_FILE_NAME
258 CREATE TABLESPACE s_bad ADD DATAFILE '../s_dir/s_bad.ibs';
259 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
260 SHOW WARNINGS;
261
262 # A colon is always rejected on Linux, but it could be part of a valid absolute path on Windows.
263 # These should be rejected even on Windows.
264 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
265 --error ER_WRONG_FILE_NAME
266 CREATE TABLESPACE s_bad ADD DATAFILE 'c:s_bad.ibd';
267 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
268 SHOW WARNINGS;
269 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
270 --error ER_WRONG_FILE_NAME
271 CREATE TABLESPACE s_bad ADD DATAFILE 'drive:s_bad.ibd';
272 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
273 SHOW WARNINGS;
274
275 --echo #
276 --echo # Try to create a tablespace where a same-named directory and file exist.
277 --echo #
278 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
279 eval CREATE TABLESPACE s4_def ADD DATAFILE '$MYSQL_TMP_DIR/tablespace.ibd/s4.ibd';
280 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
281 --error ER_TABLESPACE_DUP_FILENAME
282 eval CREATE TABLESPACE s5_def ADD DATAFILE '$MYSQL_TMP_DIR/tablespace.ibd/s4.ibd';
283 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
284 --error ER_WRONG_FILE_NAME
285 eval CREATE TABLESPACE s6_def ADD DATAFILE '$MYSQL_TMP_DIR/tablespace.ibd';
286
287 --source suite/innodb/include/show_i_s_tablespaces.inc
288
289 --echo # Directory listing of MYSQLD_DATADIR/
290 --list_files $MYSQLD_DATADIR/ *.ibd
291
292 --echo # Directory listing of MYSQL_TMP_DIR/
293 --list_files $MYSQL_TMP_DIR/ *.ibd
294
295 --echo # Directory listing of MYSQL_TMP_DIR/tablespace.ibd/
296 --list_files $MYSQL_TMP_DIR/tablespace.ibd/ *.ibd
297
298 --echo # Directory listing of MYSQL_TMP_DIR/s2_#_dir/
299 --list_files $MYSQL_TMP_DIR/s2_#_dir/ *.ibd
300
301 DROP TABLESPACE s4_def;
302 DROP TABLESPACE s1_remote;
303 DROP TABLESPACE s2_remote;
304 DROP TABLESPACE `s1_#_hash`;
305
306 --echo #
307 --echo # Try to create a tablespace that already exists.
308 --echo # Make sure that the tablespace name is compared in a CASE SENSITIVE way.
309 --echo #
310 --error ER_TABLESPACE_EXISTS
311 CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd';
312 --error ER_TABLESPACE_EXISTS
313 CREATE TABLESPACE s_def ADD DATAFILE 's_def_2.ibd';
314 --error ER_TABLESPACE_DUP_FILENAME
315 CREATE TABLESPACE S_new ADD DATAFILE 's_def.ibd';
316 CREATE TABLESPACE `s_DEF` ADD DATAFILE 's_def_2.ibd';
317 DROP TABLESPACE `s_DEF`;
318
319 --echo #
320 --echo # Make a file-per-table tablespace name and try to use it as a General tablespace
321 --echo #
322 CREATE TABLE t_single (a int, b text);
323 --source suite/innodb/include/show_i_s_tables.inc
324 --source suite/innodb/include/show_i_s_tablespaces.inc
325 # A parse error occurs if an identifier contains a '/' unless the identifier is quoted.
326 --error ER_PARSE_ERROR
327 CREATE TABLE t_general (a int, b text) TABLESPACE test/t_single engine=InnoDB;
328 --error ER_WRONG_TABLESPACE_NAME
329 CREATE TABLE t_general (a int, b text) TABLESPACE `test/t_single` engine=InnoDB;
330 SHOW WARNINGS;
331 --error ER_TABLESPACE_MISSING_WITH_NAME
332 CREATE TABLE t_general (a int, b text) TABLESPACE `S_Def` engine=InnoDB;
333 SHOW WARNINGS;
334 --error ER_TABLESPACE_MISSING_WITH_NAME
335 CREATE TABLE t_general (a int, b text) TABLESPACE `S_DEF` engine=InnoDB;
336 SHOW WARNINGS;
337
338 --error ER_WRONG_TABLESPACE_NAME
339 CREATE TABLESPACE `test/t_single` ADD DATAFILE 's_single.ibd';
340 SHOW WARNINGS;
341 --error ER_WRONG_TABLESPACE_NAME
342 CREATE TABLESPACE `Test/t_Single` ADD DATAFILE 's_single.ibd';
343 SHOW WARNINGS;
344 --error ER_WRONG_TABLESPACE_NAME
345 CREATE TABLESPACE `TEST/T_SINGLE` ADD DATAFILE 's_single.ibd';
346 SHOW WARNINGS;
347
348 DROP TABLE t_single;
349
350 --source suite/innodb/include/show_i_s_tablespaces.inc
351
352 --echo #
353 --echo # Row format is not allowed on CREATE TABLESPACE
354 --echo #
355 --error ER_PARSE_ERROR
356 CREATE TABLESPACE s_red ADD DATAFILE 's_red.ibd' ROW_FORMAT=redundant;
357
358 --echo #
359 --echo # Add tables to the tablespaces.
360 --echo #
361 CREATE TABLE t_def_in_def (a int, b text) TABLESPACE s_def;
362 CREATE TABLE t_red_in_def (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_def;
363 # Since these are creating tables within existing general tablespaces the value
364 # of INNODB_FILE_PER_TABLE does not matter.
365 SET GLOBAL innodb_file_per_table = OFF;
366 CREATE TABLE t_dyn_in_def (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_def;
367
368 --echo # Add data to the existing Tables
369 INSERT INTO t_def_in_def VALUES (1,'a'),(2,'b'),(3,'c');
370 INSERT INTO t_red_in_def VALUES (1,'a'),(2,'b'),(3,'c');
371 INSERT INTO t_dyn_in_def VALUES (1,'a'),(2,'b'),(3,'c');
372
373 --echo #
374 --echo # Try to drop a tablespace which is not empty
375 --echo #
376 --error ER_TABLESPACE_IS_NOT_EMPTY
377 DROP TABLESPACE s_def;
378
379 --source suite/innodb/include/show_i_s_tablespaces.inc
380 --source suite/innodb/include/show_i_s_tables.inc
381 --echo # Directory of MYSQLD_DATADIR/
382 --list_files $MYSQLD_DATADIR/ *.ibd
383 --echo # Directory of MYSQLD_DATADIR/test/
384 --list_files $MYSQLD_DATADIR/test/ *.ibd
385
386 --echo #
387 --echo # Drop the tables we no longer need.
388 --echo #
389
390 CHECK TABLE t_dyn_in_def;
391 CHECK TABLE t_red_in_def;
392
393 DROP TABLE t_dyn_in_def;
394 DROP TABLE t_red_in_def;
395
396 --echo #
397 --echo # Try to make a table using the database name with an existing table name
398 --echo #
399 --error ER_PARSE_ERROR
400 CREATE TABLE test/t_def_in_def (a int, b text) TABLESPACE s_def;
401 # With the back-quotes, MySQL converts the tablename to `test@002ft_def_in_def`.
402 CREATE TABLE `test/t_def_in_def` (a int, b text) TABLESPACE s_def;
403
404 --source suite/innodb/include/show_i_s_tables.inc
405 --source suite/innodb/include/show_i_s_tablespaces.inc
406 --echo # Directory listing of MYSQLD_DATADIR/
407 --list_files $MYSQLD_DATADIR/ *.ibd
408 --echo # Directory listing of MYSQLD_DATADIR/test/
409 --list_files $MYSQLD_DATADIR/test/ *.ibd
410
411 --echo #
412 --echo # Try to create a temporary tablespace
413 --echo #
414 --error ER_PARSE_ERROR
415 CREATE TEMPORARY TABLESPACE s_temp ADD DATAFILE 's_temp.ibd';
416 SHOW WARNINGS;
417
418 --echo #
419 --echo # Try to put a temporary table into a non-temporary tablespace
420 --echo #
421 --error ER_ILLEGAL_HA_CREATE_OPTION
422 CREATE TEMPORARY TABLE t_temp_red (a int, b text) ROW_FORMAT=redundant TABLESPACE s_def;
423 SHOW WARNINGS;
424
425 --echo #
426 --echo # Try to put a compressed temporary table into the system temporary tablespace
427 --echo #
428 --error ER_ILLEGAL_HA_CREATE_OPTION
429 CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=compressed TABLESPACE=`innodb_temporary`;
430 SHOW WARNINGS;
431
432 --echo #
433 --echo # Try to add a second table to a single-table tablespace
434 --echo #
435 SET GLOBAL innodb_file_per_table = ON;
436 CREATE TABLE s_single (a int, b text) ROW_FORMAT=dynamic;
437 --error ER_TABLESPACE_MISSING_WITH_NAME
438 CREATE TABLE t_second (a int, b text) TABLESPACE s_single;
439 SHOW WARNINGS;
440 --error ER_WRONG_TABLESPACE_NAME
441 CREATE TABLE t_second (a int, b text) TABLESPACE=`test/s_single`;
442 SHOW WARNINGS;
443 --source suite/innodb/include/show_i_s_tablespaces.inc
444 DROP TABLE s_single;
445
446 --echo #
447 --echo # Try to use both TABLESPACE and DATA DIRECTORY in the same CREATE TABLE.
448 --echo #
449 --echo # Strict mode and innodb_file_per_table should make no difference
450 --echo # when using general tablespaces. If TABLESPACE=innodb_file-per-table,
451 --echo # innodb_strict_mode will apply.
452 --echo #
453 SET GLOBAL innodb_file_per_table = OFF;
454 SET innodb_strict_mode = OFF;
455
456 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
457 --error ER_ILLEGAL_HA_CREATE_OPTION
458 eval CREATE TABLE t3 (a int, b text) DATA DIRECTORY='$MYSQL_TMP_DIR' TABLESPACE s_def;
459 SHOW WARNINGS;
460
461 --echo # Cannot use both a general tablespace and a DATA DIRECTORY
462 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
463 --error ER_ILLEGAL_HA_CREATE_OPTION
464 eval CREATE TABLE t4 (a int, b text) TABLESPACE=s_def DATA DIRECTORY='$MYSQL_TMP_DIR';
465 SHOW WARNINGS;
466
467 --echo # TABLESPACE=innodb_file_per_table can be used with DATA DIRECTORY
468 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
469 eval CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR';
470 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
471 SHOW CREATE TABLE t4;
472
473 --echo # An altered table should retain both TABLESPACE=innodb_file_per_table and the DATA DIRECTORY
474 ALTER TABLE t4 ROW_FORMAT=dynamic, algorithm=copy;
475 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
476 SHOW CREATE TABLE t4;
477 DROP TABLE t4;
478
479 --echo # An empty string in DATA DIRECTORY='' indicates the current directory,
480 --echo # which is not allowed.
481 --error ER_WRONG_VALUE
482 CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='';
483 SHOW WARNINGS;
484
485 SET GLOBAL innodb_file_per_table = ON;
486 SET innodb_strict_mode = ON;
487
488 --error ER_WRONG_VALUE
489 CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='';
490 SHOW WARNINGS;
491 --error ER_WRONG_VALUE
492 CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='.';
493 SHOW WARNINGS;
494 --error ER_WRONG_VALUE
495 CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='test';
496 SHOW WARNINGS;
497
498 --source suite/innodb/include/show_i_s_tablespaces.inc
499 --source suite/innodb/include/show_i_s_tables.inc
500 DROP TABLE t_def_in_def;
501 DROP TABLE `test/t_def_in_def`;
502
503 --echo #
504 --echo # Create tables explicitly in the system tablespace.
505 --echo #
506 CREATE TABLE t_red_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=redundant;
507 CREATE TABLE t_com_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compact;
508 CREATE TABLE t_dyn_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=dynamic;
509 --error ER_ILLEGAL_HA_CREATE_OPTION
510 CREATE TABLE t_zip_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compressed;
511 SHOW WARNINGS;
512
513 --source suite/innodb/include/show_i_s_tablespaces.inc
514 --source suite/innodb/include/show_i_s_tables.inc
515 CHECK TABLE t_red_in_system;
516 CHECK TABLE t_com_in_system;
517 CHECK TABLE t_dyn_in_system;
518 DROP TABLE t_red_in_system;
519 DROP TABLE t_com_in_system;
520 DROP TABLE t_dyn_in_system;
521
522 --echo #
523 --echo # Create tables explicitly as file_per_table tablespaces.
524 --echo #
525 CREATE TABLE t_red_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=redundant;
526 CREATE TABLE t_com_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compact;
527 CREATE TABLE t_dyn_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=dynamic;
528 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
529 eval CREATE TABLE t_def_as_remote (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR';
530 --source suite/innodb/include/show_i_s_tablespaces.inc
531 --source suite/innodb/include/show_i_s_tables.inc
532 --echo # Directory listing of MYSQLD_DATADIR/
533 --list_files $MYSQLD_DATADIR/ *.ibd
534 --echo # Directory listing of MYSQLD_DATADIR/test/
535 --list_files $MYSQLD_DATADIR/test/ *.ibd
536 SHOW CREATE TABLE t_red_as_file_per_table;
537 SHOW CREATE TABLE t_com_as_file_per_table;
538 SHOW CREATE TABLE t_dyn_as_file_per_table;
539 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
540 SHOW CREATE TABLE t_def_as_remote;
541
542 --echo #
543 --echo # These explicit file_per_table tables can be moved to a general tablespace.
544 --echo #
545 CREATE TABLESPACE s_multiple ADD DATAFILE 'multiple.ibd';
546 ALTER TABLE `t_red_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_red_was_file_per_table`;
547 ALTER TABLE `t_com_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_com_was_file_per_table`;
548 ALTER TABLE `t_dyn_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_dyn_was_file_per_table`;
549 ALTER TABLE `t_def_as_remote` TABLESPACE=`s_multiple`, RENAME TO `t_def_was_remote`;
550
551 --source suite/innodb/include/show_i_s_tablespaces.inc
552 --source suite/innodb/include/show_i_s_tables.inc
553 --echo # Directory listing of MYSQLD_DATADIR/
554 --list_files $MYSQLD_DATADIR/ *.ibd
555 --echo # Directory listing of MYSQLD_DATADIR/test/
556 --list_files $MYSQLD_DATADIR/test/ *.ibd
557
558 --echo #
559 --echo # Tables in a general tablespace can be moved to file_per_table locations.
560 --echo #
561 ALTER TABLE `t_red_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_red_to_file_per_table`;
562 ALTER TABLE `t_com_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_com_to_file_per_table`;
563 ALTER TABLE `t_dyn_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_dyn_to_file_per_table`;
564 --echo # Note that MySQL ignores DATA DIRECTORY on all ALTER TABLE statements.
565 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
566 eval ALTER TABLE `t_def_was_remote` TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='$MYSQL_TMP_DIR', RENAME TO `t_def_to_file_per_table`;
567
568 --source suite/innodb/include/show_i_s_tablespaces.inc
569 --source suite/innodb/include/show_i_s_tables.inc
570 --echo # Directory listing of MYSQLD_DATADIR/
571 --list_files $MYSQLD_DATADIR/ *.ibd
572 --echo # Directory listing of MYSQLD_DATADIR/test/
573 --list_files $MYSQLD_DATADIR/test/ *.ibd
574
575 DROP TABLE t_red_to_file_per_table;
576 DROP TABLE t_com_to_file_per_table;
577 DROP TABLE t_dyn_to_file_per_table;
578 DROP TABLE t_def_to_file_per_table;
579 DROP TABLESPACE s_multiple;
580
581 --echo #
582 --echo # Try the syntax that InnoDB does not support.
583 --echo #
584
585 --echo # Try NDB settings for CREATE TABLESPACE. These are currently accepted
586 --echo # even if they probably should not be. This is valid syntax from the
587 --echo # parser's POW
588
589 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd'
590 USE LOGFILE GROUP logfile_group;
591 DROP TABLESPACE s_bad;
592 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' EXTENT_SIZE = 100M;
593 DROP TABLESPACE s_bad;
594 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' NO_WAIT;
595 DROP TABLESPACE s_bad;
596
597 --echo #
598 --echo # Checking LOGFILE GROUP commands (only supported by NDB)
599 --echo #
600 --error ER_PARSE_ERROR
601 CREATE LOGFILE GROUP s_bad;
602 --error ER_FEATURE_UNSUPPORTED
603 CREATE LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat';
604 --error ER_PARSE_ERROR
605 ALTER LOGFILE GROUP s_bad;
606 --error ER_FEATURE_UNSUPPORTED
607 ALTER LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat'
608 INITIAL_SIZE = 100M, NO_WAIT;
609 --error ER_FEATURE_UNSUPPORTED
610 DROP LOGFILE GROUP s_bad;
611
612 --echo #
613 --echo # Checking ALTER TABLESPACE (only NDB in 5.7)
614 --echo #
615 CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd';
616
617 --error ER_PARSE_ERROR
618 ALTER TABLESPACE s_bad;
619
620 --error ER_ALTER_FILEGROUP_FAILED
621 ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd';
622 SHOW WARNINGS;
623
624 --error ER_ALTER_FILEGROUP_FAILED
625 ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd';
626 SHOW WARNINGS;
627
628 --error ER_ALTER_FILEGROUP_FAILED
629 ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd' INITIAL_SIZE = 1G;
630 SHOW WARNINGS;
631
632 --error ER_ALTER_FILEGROUP_FAILED
633 ALTER TABLESPACE s_def ADD DATAFILE 'bad2.ibd' NO_WAIT;
634 SHOW WARNINGS;
635
636 --error ER_ALTER_FILEGROUP_FAILED
637 ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd' INITIAL_SIZE = 1G;
638 SHOW WARNINGS;
639
640 --error ER_ALTER_FILEGROUP_FAILED
641 ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd' NO_WAIT;
642 SHOW WARNINGS;
643
644 --echo #
645 --echo # Undocumented ALTER TABLESPACE syntax which was removed by wl#8972
646 --echo #
647 --error ER_PARSE_ERROR
648 ALTER TABLESPACE s_bad CHANGE 's_bad.ibd'
649 INITIAL_SIZE = 1G, AUTOEXTEND_SIZE = 100M, MAX_SIZE = 2G;
650 --error ER_PARSE_ERROR
651 ALTER TABLESPACE s_bad READ_ONLY;
652 --error ER_PARSE_ERROR
653 ALTER TABLESPACE s_bad READ_WRITE;
654 --error ER_PARSE_ERROR
655 ALTER TABLESPACE s_bad NOT ACCESSIBLE;
656
657 DROP TABLESPACE s_bad;
658
659 --echo #
660 --echo # Try the syntax that MySQL docs say is ignored.
661 --echo #
662 CREATE TABLESPACE s_ignore1 ADD DATAFILE 's_ignore1.ibd' NODEGROUP = 5;
663 CREATE TABLESPACE s_ignore2 ADD DATAFILE 's_ignore2.ibd' WAIT;
664 CREATE TABLESPACE s_ignore3 ADD DATAFILE 's_ignore3.ibd' COMMENT = 'This comment is ignored';
665
666 --source suite/innodb/include/show_i_s_tablespaces.inc
667
668 DROP TABLESPACE s_ignore1;
669 DROP TABLESPACE s_ignore2;
670 DROP TABLESPACE s_ignore3;
671
672 --echo #
673 --echo # Try various ALTER TABLE statements.
674 --echo #
675 CREATE TABLE t_in_def (a serial key, b text) TABLESPACE s_def;
676 INSERT INTO t_in_def(b) VALUES(repeat("a short string - ",5));
677 INSERT INTO t_in_def(b) VALUES(repeat("a long string - ",50));
678 INSERT INTO t_in_def(b) SELECT b FROM t_in_def;
679 SELECT a, left(b,50) FROM t_in_def;
680 SHOW CREATE TABLE t_in_def;
681 --source suite/innodb/include/show_i_s_tables.inc
682
683 ALTER TABLE t_in_def ROW_FORMAT=redundant;
684 SHOW CREATE TABLE t_in_def;
685
686 ALTER TABLE t_in_def ROW_FORMAT=dynamic;
687 SHOW CREATE TABLE t_in_def;
688
689 ALTER TABLE t_in_def ADD COLUMN (c int);
690 SHOW CREATE TABLE t_in_def;
691
692 ALTER TABLE t_in_def ADD INDEX c (c);
693 SHOW CREATE TABLE t_in_def;
694
695 ALTER TABLE t_in_def DROP INDEX c;
696 SHOW CREATE TABLE t_in_def;
697
698 ALTER TABLE t_in_def DROP COLUMN c;
699 SHOW CREATE TABLE t_in_def;
700
701 ALTER TABLE t_in_def AUTO_INCREMENT=100;
702 SHOW CREATE TABLE t_in_def;
703 INSERT INTO t_in_def(b) VALUES("after ALTER TABLE AUTO_INCREMENT");
704 SELECT a, left(b,50) FROM t_in_def;
705 SHOW CREATE TABLE t_in_def;
706
707 ALTER TABLE t_in_def CHANGE a aa int;
708 SHOW CREATE TABLE t_in_def;
709
710 ALTER TABLE t_in_def CHANGE b bb varchar(1000);
711 SHOW CREATE TABLE t_in_def;
712
713 ALTER TABLE t_in_def DROP PRIMARY KEY;
714 SHOW CREATE TABLE t_in_def;
715
716 ALTER TABLE t_in_def ADD PRIMARY KEY aa(aa), ALGORITHM=INPLACE;
717 SHOW CREATE TABLE t_in_def;
718
719 ALTER TABLE t_in_def ADD INDEX ab(aa,bb(25)), ALGORITHM=COPY;
720 SHOW CREATE TABLE t_in_def;
721
722 ALTER TABLE t_in_def ADD INDEX bb(bb(50));
723 SHOW CREATE TABLE t_in_def;
724
725 RENAME TABLE t_in_def TO t;
726 SHOW CREATE TABLE t;
727 --source suite/innodb/include/show_i_s_tables.inc
728 INSERT INTO t VALUES(200, "after RENAME TABLE t_in_def TO t");
729 SELECT aa, left(bb,50) FROM t;
730
731 --echo #
732 --echo # Move tables between tablespaces.
733 --echo #
734 CREATE TABLESPACE s_alt1 ADD DATAFILE 's_alt1.ibd';
735 CREATE TABLESPACE s_alt2 ADD DATAFILE 's_alt2.ibd';
736 --source suite/innodb/include/show_i_s_tablespaces.inc
737 ALTER TABLE t RENAME TO t_nomad, TABLESPACE s_alt1;
738 SHOW CREATE TABLE t_nomad;
739 --source suite/innodb/include/show_i_s_tables.inc
740 ALTER TABLE t_nomad TABLESPACE s_alt2;
741 SHOW CREATE TABLE t_nomad;
742 --source suite/innodb/include/show_i_s_tables.inc
743 ALTER TABLE t_nomad TABLESPACE s_def, DROP COLUMN bb;
744 SHOW CREATE TABLE t_nomad;
745 --source suite/innodb/include/show_i_s_tables.inc
746 --source suite/innodb/include/show_i_s_tablespaces.inc
747 DROP TABLE t_nomad;
748 DROP TABLESPACE s_alt1;
749 DROP TABLESPACE s_alt2;
750
751 --echo #
752 --echo # Move a table from the System Tablespace into a General Tablespace
753 --echo # and then back out to the system tablespace again.
754 --echo #
755 SET GLOBAL innodb_file_per_table=OFF;
756 CREATE TABLE t_system (a serial key, b text);
757 INSERT INTO t_system(b) VALUES(repeat("a short string - ",5));
758 INSERT INTO t_system(b) VALUES(repeat("a long string - ",50));
759 SHOW CREATE TABLE t_system;
760 --source suite/innodb/include/show_i_s_tables.inc
761
762 ALTER TABLE t_system TABLESPACE s_def;
763 SHOW CREATE TABLE t_system;
764 --source suite/innodb/include/show_i_s_tables.inc
765
766 ALTER TABLE t_system TABLESPACE=`innodb_system`;
767 SHOW CREATE TABLE t_system;
768 --source suite/innodb/include/show_i_s_tables.inc
769
770 --echo #
771 --echo # Do a few ALTER TABLES for this table that was moved out and back into the system tablespace.
772 --echo #
773 ALTER TABLE t_system ROW_FORMAT=redundant;
774 SHOW CREATE TABLE t_system;
775 ALTER TABLE t_system ROW_FORMAT=dynamic;
776 SHOW CREATE TABLE t_system;
777 ALTER TABLE t_system ADD COLUMN (c int);
778 SHOW CREATE TABLE t_system;
779 ALTER TABLE t_system ADD INDEX c (c);
780 SHOW CREATE TABLE t_system;
781 ALTER TABLE t_system DROP INDEX c;
782 SHOW CREATE TABLE t_system;
783 ALTER TABLE t_system DROP COLUMN c;
784 SHOW CREATE TABLE t_system;
785
786 --echo #
787 --echo # Now try to put that table into its own Single-Table tablespace.
788 --echo # It is not allowed since the table is now marked with the system tablespace
789 --echo #
790 SET GLOBAL innodb_file_per_table=ON;
791 ALTER TABLE t_system RENAME TO t_still_system;
792 SHOW CREATE TABLE t_still_system;
793 --source suite/innodb/include/show_i_s_tables.inc
794 DROP TABLE t_still_system;
795
796 --echo #
797 --echo # Try to create or move a table into the temporary tablespace.
798 --echo #
799 --error ER_ILLEGAL_HA_CREATE_OPTION
800 CREATE TABLE t_not_temp (a int, b text) TABLESPACE=`innodb_temporary`;
801 SHOW WARNINGS;
802
803 CREATE TABLE t_not_temp (a int, b text) TABLESPACE s_def;
804 --error ER_ILLEGAL_HA_CREATE_OPTION
805 ALTER TABLE t_not_temp TABLESPACE=`innodb_temporary`;
806 SHOW WARNINGS;
807 DROP TABLE t_not_temp;
808
809 SET SESSION innodb_strict_mode=OFF;
810 CREATE TEMPORARY TABLE t_my_temp (a int, b text) TABLESPACE=`innodb_temporary`;
811 SHOW CREATE TABLE t_my_temp;
812 DROP TABLE t_my_temp;
813 SET SESSION innodb_strict_mode=ON;
814
815 --echo #
816 --echo # Try to create or move a table into the redo tablespace
817 --echo # or any tablespace using the reserved `innodb_` prefix.
818 --echo #
819 --error ER_WRONG_TABLESPACE_NAME
820 CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_redo_log`;
821 SHOW WARNINGS;
822 --error ER_WRONG_TABLESPACE_NAME
823 CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_anything`;
824 SHOW WARNINGS;
825
826 CREATE TABLE t_data (a int, b text) TABLESPACE s_def;
827 --error ER_WRONG_TABLESPACE_NAME
828 ALTER TABLE t_data TABLESPACE=`innodb_redo_log`;
829 SHOW WARNINGS;
830 --error ER_WRONG_TABLESPACE_NAME
831 ALTER TABLE t_data TABLESPACE=`innodb_anything`;
832 SHOW WARNINGS;
833 DROP TABLE t_data;
834
835 --echo #
836 --echo # Move a table from a Single-Table tablespace into a General Tablespace.
837 --echo #
838 CREATE TABLE t_single (a serial key, b text);
839 INSERT INTO t_single(b) VALUES(repeat("a short string - ",5));
840 INSERT INTO t_single(b) VALUES(repeat("a long string - ",50));
841 SHOW CREATE TABLE t_single;
842 --source suite/innodb/include/show_i_s_tables.inc
843 ALTER TABLE t_single TABLESPACE s_def;
844 SHOW CREATE TABLE t_single;
845 --source suite/innodb/include/show_i_s_tables.inc
846 DROP TABLE t_single;
847
848 --echo #
849 --echo # Move a MyISAM table into an InnoDB General Tablespace and back.
850 --echo #
851 CREATE TABLE t_myisam (a serial key, b int) ENGINE=MyISAM;
852 INSERT INTO t_myisam(b) VALUES(11);
853 SHOW CREATE TABLE t_myisam;
854 ALTER TABLE t_myisam RENAME TO t_innodb, TABLESPACE s_def, ENGINE=InnoDB;
855 INSERT INTO t_innodb(b) VALUES(22);
856 SHOW CREATE TABLE t_innodb;
857 --source suite/innodb/include/show_i_s_tables.inc
858 ALTER TABLE t_innodb RENAME TO t_myisam, ENGINE=MyISAM;
859 INSERT INTO t_myisam(b) VALUES(33);
860 SELECT * FROM t_myisam;
861 SHOW CREATE TABLE t_myisam;
862 ALTER TABLE t_myisam RENAME TO t_innodb, ENGINE=InnoDB;
863 INSERT INTO t_innodb(b) VALUES(44);
864 SHOW CREATE TABLE t_innodb;
865 --source suite/innodb/include/show_i_s_tables.inc
866 DROP TABLE t_innodb;
867
868 --echo #
869 --echo # Move a Dynamic table from an InnoDB General tablespace to MyISAM
870 --echo # and Memory and back without having to re-specify the tablespace
871 --echo # and row_format.
872 --echo #
873 CREATE TABLE t_dyn_in_s_def (a serial key, b int) ROW_FORMAT=Dynamic TABLESPACE=s_def ENGINE=InnoDB;
874 INSERT INTO t_dyn_in_s_def(b) VALUES(11);
875 SHOW CREATE TABLE t_dyn_in_s_def;
876 --source suite/innodb/include/show_i_s_tables.inc
877 ALTER TABLE t_dyn_in_s_def ENGINE=MyISAM;
878 INSERT INTO t_dyn_in_s_def(b) VALUES(22);
879 SHOW CREATE TABLE t_dyn_in_s_def;
880 --source suite/innodb/include/show_i_s_tables.inc
881 ALTER TABLE t_dyn_in_s_def ENGINE=InnoDB;
882 INSERT INTO t_dyn_in_s_def(b) VALUES(33);
883 SHOW CREATE TABLE t_dyn_in_s_def;
884 --source suite/innodb/include/show_i_s_tables.inc
885 ALTER TABLE t_dyn_in_s_def ENGINE=Memory;
886 INSERT INTO t_dyn_in_s_def(b) VALUES(44);
887 SHOW CREATE TABLE t_dyn_in_s_def;
888 --source suite/innodb/include/show_i_s_tables.inc
889 ALTER TABLE t_dyn_in_s_def ENGINE=InnoDB;
890 INSERT INTO t_dyn_in_s_def(b) VALUES(55);
891 SELECT * FROM t_dyn_in_s_def;
892 SHOW CREATE TABLE t_dyn_in_s_def;
893 --source suite/innodb/include/show_i_s_tables.inc
894 CHECK TABLE t_dyn_in_s_def;
895 DROP TABLE t_dyn_in_s_def;
896
897 --echo #
898 --echo # If a tablespace name is associated with a table in another SE, and that tablespace
899 --echo # does not exist in InnoDB, the ALTER TABLE will fail
900 --echo #
901 CREATE TABLESPACE s_short_life ADD DATAFILE 'short_life.ibd';
902 CREATE TABLE t_nomad (a serial key, b int) TABLESPACE=s_short_life ENGINE=InnoDB;
903 INSERT INTO t_nomad(b) VALUES(11);
904 SHOW CREATE TABLE t_nomad;
905 --source suite/innodb/include/show_i_s_tables.inc
906 ALTER TABLE t_nomad ENGINE=MyISAM;
907 INSERT INTO t_nomad(b) VALUES(22);
908 SHOW CREATE TABLE t_nomad;
909 --source suite/innodb/include/show_i_s_tables.inc
910 DROP TABLESPACE s_short_life;
911 --error ER_TABLESPACE_MISSING_WITH_NAME
912 ALTER TABLE t_nomad ENGINE=InnoDB;
913 CREATE TABLESPACE s_shorter_life ADD DATAFILE 'shorter_life.ibd';
914 ALTER TABLE t_nomad ENGINE=InnoDB TABLESPACE=s_shorter_life;
915 SHOW CREATE TABLE t_nomad;
916 --source suite/innodb/include/show_i_s_tables.inc
917 DROP TABLE t_nomad;
918 DROP TABLESPACE s_shorter_life;
919
920 --echo #
921 --echo # Show that a table with the same SQL name can be created in two different
922 --echo # databases, whether they are in the system tablespace, a general tablespace
923 --echo # or their own file-per-table tablespace.
924 --echo # Also show that DROP DATABASE will remove tables from inside a General Tablespace
925 --echo # without removing the tablespace or other tables in it for another databases.
926 --echo #
927 SET GLOBAL innodb_file_per_table=OFF;
928 CREATE TABLE t_system (a serial key, b text);
929 CREATE TABLE t_general (a serial key, b text) TABLESPACE s_def;
930 CREATE TABLE t_single (a serial key, b text) TABLESPACE=`innodb_file_per_table`;
931 SHOW TABLES;
932
933 CREATE DATABASE test1;
934 USE test1;
935 # A general tablespace is independent of the current database.
936 CREATE TABLESPACE s_empty1 ADD DATAFILE 's_empty1.ibd' ENGINE InnoDB;
937 CREATE TABLE t_system (a serial key, b text);
938 CREATE TABLE t_general (a serial key, b text) TABLESPACE s_def;
939 CREATE TABLE t_single (a serial key, b text) TABLESPACE=`innodb_file_per_table`;
940 SHOW TABLES;
941
942 USE test;
943 --source suite/innodb/include/show_i_s_tables.inc
944 --source suite/innodb/include/show_i_s_tablespaces.inc
945 DROP DATABASE test1;
946 --source suite/innodb/include/show_i_s_tables.inc
947 --source suite/innodb/include/show_i_s_tablespaces.inc
948 DROP TABLE t_system;
949 DROP TABLE t_general;
950 DROP TABLE t_single;
951 DROP TABLESPACE s_empty1;
952
953 --echo #
954 --echo # Assign different tablespace for PK-FK tables
955 --echo #
956 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
957 CREATE TABLE t1 (a int primary key) ENGINE=innodb TABLESPACE=s1;
958 CREATE TABLE t2 (b int , foreign key (b) references t1(a)) ENGINE=innodb ;
959 INSERT INTO t1 VALUES (100);
960 INSERT INTO t1 VALUES (200);
961 INSERT INTO t2 VALUES (100);
962 --source suite/innodb/include/show_i_s_tables.inc
963 SELECT * FROM t1;
964 SELECT * FROM t2;
965 SELECT * FROM t1,t2 WHERE t1.a=t2.b;
966 DROP TABLE t2,t1;
967 DROP TABLESPACE s1;
968
969 --echo #
970 --echo # Assigning general tablespace from procedure
971 --echo #
972 USE test;
973 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
974 CREATE TABLE t1 (a geometry NOT NULL SRID 0, SPATIAL (a)) ENGINE=InnoDB;
975 INSERT INTO t1 VALUES (st_GeomFromText("LINESTRING(100 100, 200 200, 300 300)")) ;
976 DELIMITER |;
977 CREATE PROCEDURE p1()
978 BEGIN
979 ALTER TABLE t1 TABLESPACE=s1;
980 END|
981 DELIMITER ;|
982 --source suite/innodb/include/show_i_s_tables.inc
983 CALL p1();
984 --source suite/innodb/include/show_i_s_tables.inc
985 DROP PROCEDURE p1;
986
987 --echo #
988 --echo # Show that DISCARD/IMPORT tablespace does not work on a general tablespace.
989 --echo #
990 --error ER_NOT_ALLOWED_COMMAND
991 ALTER TABLE t1 DISCARD TABLESPACE;
992 SHOW WARNINGS;
993 --error ER_NOT_ALLOWED_COMMAND
994 ALTER TABLE t1 IMPORT TABLESPACE;
995 SHOW WARNINGS;
996 DROP TABLE t1;
997 DROP TABLESPACE s1;
998
999 --echo #
1000 --echo # Clean-up.
1001 --echo #
1002
1003 DROP TABLESPACE s_def;
1004
1005 SET GLOBAL innodb_file_per_table=default;
1006 SET GLOBAL innodb_strict_mode=default;
1007
1008 --rmdir $MYSQL_TMP_DIR/s2_#_dir
1009 --rmdir $MYSQL_TMP_DIR/test
1010 --rmdir $MYSQL_TMP_DIR/tablespace.ibd
1011
1012 --echo #
1013 --echo # Bug #20763179 SEGV WHEN CREATE TABLESPACE IS EXECUTED IN READ ONLY MODE
1014 --echo #
1015 --echo # Check CREATE TABLESPACE is handled properly in read-only mode
1016
1017 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB;
1018
1019 let $do_not_echo_parameters = 1;
1020 let $extra_restart_parameters = --innodb-directories=$MYSQL_TMP_DIR;
1021 --source include/restart_innodb_read_only.inc
1022 let $do_not_echo_parameters =;
1023 --error ER_READ_ONLY_MODE
1024 CREATE TABLESPACE s2 ADD DATAFILE 's2.ibd' ENGINE InnoDB;
1025 SHOW WARNINGS;
1026 --error ER_INNODB_READ_ONLY
1027 DROP TABLESPACE s1;
1028 SHOW WARNINGS;
1029
1030 --echo # Restart in normal mode for cleanup.
1031 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
1032 let $restart_parameters = restart: --innodb-directories=$MYSQL_TMP_DIR;
1033 --source include/restart_mysqld.inc
1034 DROP TABLESPACE s1;
1035
1036 --echo #
1037 --echo # Bug#26199233 create like ignores innodb_file_per_table
1038 --echo #
1039 --echo # - Verify if table picks correct tablespace based on
1040 --echo # innodb_file_per_table settings.
1041 --echo # - Check if SHOW CREATE displays correct tablespace name.
1042
1043 CREATE DATABASE db1;
1044
1045 SET @@global.innodb_file_per_table = OFF;
1046
1047 --echo # Case 1
1048 --echo # SHOW CREATE should not show TABLESPACE clause after the fix.
1049 --echo # Because it was not explicitly specified by user.
1050 CREATE TABLE db1.t1 (c1 INT) ENGINE=InnoDB;
1051 SHOW CREATE TABLE db1.t1;
1052
1053 SET @@global.innodb_file_per_table = ON;
1054
1055 --echo # Case 2
1056 --echo # After the fix table, t2 should use innodb_file_per_table tablespace.
1057 CREATE TABLE db1.t2 LIKE db1.t1;
1058 SHOW CREATE TABLE db1.t2;
1059 --echo # Verify the same by listing the .ibd file.
1060 --list_files $MYSQLD_DATADIR/db1/
1061
1062 --echo # Case 3
1063 --echo # db1.tables TABLESPACE clause should show 'ts0' after the fix.
1064 CREATE TABLESPACE ts0 ADD DATAFILE 'df0.ibd';
1065 CREATE TABLE db1.tables (c1 INT) ENGINE=InnoDB tablespace=ts0;
1066 SHOW CREATE TABLE db1.tables;
1067 DROP TABLE db1.tables;
1068 DROP TABLESPACE ts0;
1069
1070 --echo # Cleanup
1071 DROP DATABASE db1;
1072
1073 --echo # Additional test cases for Bug#26199233 to
1074 --echo # test various combinations.
1075
1076 CREATE SCHEMA s1;
1077 USE s1;
1078 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd';
1079
1080 --echo #
1081 --echo # Tables with explicitly assigned tablespaces.
1082 --echo #
1083 --echo # Table with an explicitly assigned user tablespace.
1084 CREATE TABLE t_expl_usr (i INT) TABLESPACE s1;
1085 SHOW CREATE TABLE t_expl_usr;
1086 --echo #
1087 --echo # Table with an explicitly assigned system tablespace.
1088 CREATE TABLE t_expl_sys (i INT) TABLESPACE innodb_system;
1089 SHOW CREATE TABLE t_expl_sys;
1090 --echo #
1091 --echo # Table with an explicitly assigned f_p_t tablespace.
1092 CREATE TABLE t_expl_fpt (i INT) TABLESPACE innodb_file_per_table;
1093 SHOW CREATE TABLE t_expl_fpt;
1094 --echo #
1095 --echo # Temporary table with an explicitly assigned tmp tablespace.
1096 SET SESSION innodb_strict_mode=OFF;
1097 CREATE TEMPORARY TABLE t_expl_tmp (i INT) TABLESPACE innodb_temporary;
1098 SHOW WARNINGS;
1099 SET SESSION innodb_strict_mode=ON;
1100
1101 --echo #
1102 --echo # Tables with implicitly assigned tablespaces.
1103 --echo #
1104 --echo # Table with an implicitly assigned system tablespace.
1105 SET GLOBAL innodb_file_per_table= OFF;
1106 CREATE TABLE t_impl_sys (i INT);
1107 SHOW CREATE TABLE t_impl_sys;
1108 --echo #
1109 --echo # Table with an implicitly assigned f_p_t tablespace.
1110 SET GLOBAL innodb_file_per_table= ON;
1111 CREATE TABLE t_impl_fpt (i INT);
1112 SHOW CREATE TABLE t_impl_fpt;
1113 --echo #
1114 --echo # Temporary table with an implicitly assigned tmp tablespace.
1115 CREATE TEMPORARY TABLE t_impl_tmp (i INT);
1116 SHOW CREATE TABLE t_impl_tmp;
1117
1118 --echo #
1119 --echo # CREATE LIKE when innodb_file_per_table is ON.
1120 --echo #
1121 --echo # Tablespace preserved for all source tables that
1122 --echo # had an explicitly assigned tablesapce.
1123 SET GLOBAL innodb_file_per_table= ON;
1124 CREATE TABLE fpt_on_like_t_expl_usr LIKE t_expl_usr;
1125 SHOW CREATE TABLE fpt_on_like_t_expl_usr;
1126 CREATE TABLE fpt_on_like_t_expl_sys LIKE t_expl_sys;
1127 SHOW CREATE TABLE fpt_on_like_t_expl_sys;
1128 CREATE TABLE fpt_on_like_t_expl_fpt LIKE t_expl_fpt;
1129 SHOW CREATE TABLE fpt_on_like_t_expl_fpt;
1130 SET SESSION innodb_strict_mode=OFF;
1131 CREATE TEMPORARY TABLE fpt_on_like_t_expl_tmp LIKE t_expl_tmp;
1132 SHOW WARNINGS;
1133 SET SESSION innodb_strict_mode=OFF;
1134
1135 --echo # Tablespace not preserved for all source tables that
1136 --echo # had an implicitly assigned tablesapce.
1137 CREATE TABLE fpt_on_like_t_impl_sys LIKE t_impl_sys;
1138 SHOW CREATE TABLE fpt_on_like_t_impl_sys;
1139 CREATE TABLE fpt_on_like_t_impl_fpt LIKE t_impl_fpt;
1140 SHOW CREATE TABLE fpt_on_like_t_impl_fpt;
1141 CREATE TEMPORARY TABLE fpt_on_like_t_impl_tmp LIKE t_impl_tmp;
1142 SHOW CREATE TABLE fpt_on_like_t_impl_tmp;
1143
1144 --echo #
1145 --echo # CREATE LIKE when innodb_file_per_table is OFF.
1146 --echo #
1147 --echo # Tablespace preserved for all source tables that
1148 --echo # had an explicitly assigned tablesapce.
1149 SET GLOBAL innodb_file_per_table= OFF;
1150 CREATE TABLE fpt_off_like_t_expl_usr LIKE t_expl_usr;
1151 SHOW CREATE TABLE fpt_off_like_t_expl_usr;
1152 CREATE TABLE fpt_off_like_t_expl_sys LIKE t_expl_sys;
1153 CREATE TABLE fpt_off_like_t_expl_fpt LIKE t_expl_fpt;
1154 SHOW CREATE TABLE fpt_off_like_t_expl_fpt;
1155 CREATE TEMPORARY TABLE fpt_off_like_t_expl_tmp LIKE t_expl_tmp;
1156
1157 --echo # Tablespace not preserved for all source tables that
1158 --echo # had an implicitly assigned tablesapce.
1159 CREATE TABLE fpt_off_like_t_impl_sys LIKE t_impl_sys;
1160 SHOW CREATE TABLE fpt_off_like_t_impl_sys;
1161 CREATE TABLE fpt_off_like_t_impl_fpt LIKE t_impl_fpt;
1162 SHOW CREATE TABLE fpt_off_like_t_impl_fpt;
1163 CREATE TEMPORARY TABLE fpt_off_like_t_impl_tmp LIKE t_impl_tmp;
1164 SHOW CREATE TABLE fpt_off_like_t_impl_tmp;
1165
1166 --echo # The end result should be that we have .ibd files for:
1167 --echo # fpt_off_like_t_expl_fpt.ibd
1168 --echo # fpt_on_like_t_expl_fpt.ibd
1169 --echo # fpt_on_like_t_impl_fpt.ibd
1170 --echo # fpt_on_like_t_impl_sys.ibd
1171 --echo # t_expl_fpt.ibd
1172 --echo # t_impl_fpt.ibd
1173
1174 let $MYSQLD_DATADIR = `select @@datadir`;
1175 --list_files $MYSQLD_DATADIR/s1/
1176
1177 --echo # Now, we can ALTER the tables that have implicitly
1178 --echo # assigned tablespaces to instead assign the spaces
1179 --echo # explicitly. This will make SHOW CREATE show the
1180 --echo # tablespace names.
1181 ALTER TABLE t_impl_sys TABLESPACE innodb_system;
1182 SHOW CREATE TABLE t_impl_sys;
1183 ALTER TABLE t_impl_fpt TABLESPACE innodb_file_per_table;
1184 SHOW CREATE TABLE t_impl_fpt;
1185 ALTER TABLE t_impl_tmp TABLESPACE innodb_temporary;
1186 SHOW CREATE TABLE t_impl_tmp;
1187
1188 --echo # And CREATE TABLE LIKE will now preserve the
1189 --echo # tablespace name of the above tables.
1190
1191 CREATE TABLE after_alter_like_t_impl_sys LIKE t_impl_sys;
1192 SHOW CREATE TABLE after_alter_like_t_impl_sys;
1193 CREATE TABLE after_alter_like_t_impl_fpt LIKE t_impl_fpt;
1194 SHOW CREATE TABLE after_alter_like_t_impl_fpt;
1195 CREATE TEMPORARY TABLE after_alter_like_t_impl_tmp LIKE t_impl_tmp;
1196 SHOW CREATE TABLE after_alter_like_t_impl_tmp;
1197
1198 DROP SCHEMA s1;
1199 DROP TABLESPACE s1;
1200 SET GLOBAL innodb_file_per_table= DEFAULT;
1201
1202 --disable_query_log
1203 call mtr.add_suppression("\\[Warning\\].* '.*tablespace.ibd' is a directory, can't delete!");
1204 call mtr.add_suppression("\\[Warning\\].* Can't create test file");
1205 call mtr.add_suppression("\\[ERROR\\].* File .* 'delete' returned OS error .*");
1206 call mtr.add_suppression("\\[ERROR\\].* File .* 'stat' returned OS error .*");
1207 call mtr.add_suppression("\\[ERROR\\].* File .*\.ibd: 'create' returned OS error .*");
1208 call mtr.add_suppression("\\[ERROR\\].* The error means the system cannot find the path specified");
1209 call mtr.add_suppression("\\[ERROR\\].* The error means mysqld does not have the access rights to the directory. It may also be you have created a subdirectory of the same name as a data file.");
1210 call mtr.add_suppression("\\[ERROR\\].* Operating system error number .* in a file operation");
1211 call mtr.add_suppression("\\[ERROR\\].* Error number .* means");
1212 call mtr.add_suppression("\\[ERROR\\].* Cannot create file");
1213 call mtr.add_suppression("\\[ERROR\\].* Invalid use of ':' in");
1214 call mtr.add_suppression("\\[ERROR\\].* The file .* already exists though the corresponding table did not exist.");
1215 call mtr.add_suppression("\\[ERROR\\].* The directory for tablespace .* does not exist or is incorrect");
1216 call mtr.add_suppression("\\[ERROR\\].* Cannot create tablespace .* because the directory is not a valid location. The DATAFILE location must be in a known directory");
1217 --enable_query_log
1218