Debian Codegraph @

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

raw
1 #
2 # A series of tests to show the correct tablespace behavior.
3 # See also; parts.partition_basic_symlink_innodb.test for
4 # partition related tests with remote tablespaces.
5 # See innodb.innodb-restart for tablespace migration tests.
6 #
7 SET default_storage_engine=InnoDB;
8
9 --echo #
10 --echo # TABLESPACE related tests.
11 --echo #
12
13 # Set up some variables
14 LET $MYSQLD_DATADIR = `select @@datadir`;
15 LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
16 LET $data_directory_clause = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
17 LET $index_directory_clause = INDEX DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
18
19 # These values can change during the test
20 LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
21
22 --echo #
23 --echo # Restart with innodb-directories set to MYSQL_TMP_DIR.
24 --echo #
25 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
26 let $restart_parameters = restart: --innodb-directories=$MYSQL_TMP_DIR;
27 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
28 --source include/restart_mysqld.inc
29
30 --echo #
31 --echo # CREATE TABLE ... DATA DIRECTORY
32 --echo # Innodb does not support INDEX DIRECTORY.
33 --echo #
34 SET SESSION innodb_strict_mode = ON;
35 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
36 --error ER_ILLEGAL_HA
37 eval CREATE TABLE t1 (a int KEY, b text) $index_directory_clause;
38 SHOW WARNINGS;
39
40 --echo #
41 --echo # Without strict mode, INDEX DIRECTORY is just ignored
42 --echo #
43 SET SESSION innodb_strict_mode = OFF;
44 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
45 eval CREATE TABLE t1 (a int KEY, b text) $index_directory_clause;
46 SHOW WARNINGS;
47 SHOW CREATE TABLE t1;
48 DROP TABLE t1;
49
50 --echo #
51 --echo # Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON.
52 --echo #
53 SET SESSION innodb_strict_mode = ON;
54 SET GLOBAL innodb_file_per_table=OFF;
55 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
56 --error ER_ILLEGAL_HA
57 eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
58 SHOW WARNINGS;
59
60 --echo #
61 --echo # Without strict mode, DATA DIRECTORY without innodb_file_per_table=ON is just ignored.
62 --echo #
63 SET SESSION innodb_strict_mode = OFF;
64 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
65 eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
66 SHOW WARNINGS;
67 SHOW CREATE TABLE t1;
68 DROP TABLE t1;
69
70 --echo # Now set innodb_file_per_table so that DATA DIRECTORY can be tested.
71 SET GLOBAL innodb_file_per_table=ON;
72
73 --echo #
74 --echo # Create the tablespace in MYSQL_TMP_DIR/alt_dir
75 --echo # InnoDB will create the sub-directories if needed.
76 --echo #
77 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
78 eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
79 SHOW WARNINGS;
80 INSERT INTO t1 VALUES (1, "Create the tablespace");
81 SELECT * FROM t1;
82
83 --echo #
84 --echo # Check if link file exists in MYSQLD_DATADIR
85 --echo #
86 --echo ---- MYSQLD_DATADIR/test
87 --list_files $MYSQLD_DATADIR/test
88 --echo # Check if tablespace file exists where we specified in DATA DIRECTORY
89 --echo ---- MYSQL_TMP_DIR/alt_dir/test
90 --list_files $MYSQL_TMP_DIR/alt_dir/test
91
92 --echo #
93 --echo # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
94 --echo #
95 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
96 SHOW CREATE TABLE t1;
97
98 --echo # Show that the new system tables have this table in them correctly
99 --source suite/innodb/include/show_i_s_tables.inc
100 --source suite/innodb/include/show_i_s_tablespaces.inc
101
102 --echo #
103 --echo # Show that the system tables are updated on drop table
104 --echo #
105 DROP TABLE t1;
106 --source suite/innodb/include/show_i_s_tables.inc
107 --source suite/innodb/include/show_i_s_tablespaces.inc
108
109 --echo #
110 --echo # Create the same table a second time in the same place
111 --echo #
112 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
113 eval CREATE TABLE t1 (a int KEY, b text) $data_directory_clause;
114
115 INSERT INTO t1 VALUES (2, "Create the same table a second time in the same place");
116 SELECT * FROM t1;
117 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
118 SHOW CREATE TABLE t1;
119 --source suite/innodb/include/show_i_s_tables.inc
120 --source suite/innodb/include/show_i_s_tablespaces.inc
121 --echo ---- MYSQLD_DATADIR/test
122 --list_files $MYSQLD_DATADIR/test
123 --echo ---- MYSQL_TMP_DIR/alt_dir/test
124 --list_files $MYSQL_TMP_DIR/alt_dir/test
125
126 --echo #
127 --echo # Truncate the table, then insert and verify
128 --echo #
129 TRUNCATE TABLE t1;
130 INSERT INTO t1 VALUES (3, "Truncate the table, then insert");
131 SELECT * FROM t1;
132
133 --source suite/innodb/include/show_i_s_tables.inc
134 --source suite/innodb/include/show_i_s_tablespaces.inc
135
136 --replace_regex /[1-9].[0-9].[0-9]+/X.Y.Z/
137 SELECT name, server_version, space_version FROM information_schema.innodb_tablespaces where name="mysql";
138
139 --echo ---- MYSQLD_DATADIR/test
140 --list_files $MYSQLD_DATADIR/test
141 --echo ---- MYSQL_TMP_DIR/alt_dir/test
142 --list_files $MYSQL_TMP_DIR/alt_dir/test
143
144 --echo #
145 --echo # Rename the table, then insert and verify
146 --echo #
147 RENAME TABLE t1 TO t2;
148 INSERT INTO t2 VALUES (4, "Rename the table, then insert");
149 SELECT * FROM t2;
150
151 --source suite/innodb/include/show_i_s_tables.inc
152 --source suite/innodb/include/show_i_s_tablespaces.inc
153 --echo ---- MYSQLD_DATADIR/test
154 --list_files $MYSQLD_DATADIR/test
155 --echo ---- MYSQL_TMP_DIR/alt_dir/test
156 --list_files $MYSQL_TMP_DIR/alt_dir/test
157
158 --echo #
159 --echo # CREATE TABLE LIKE does not retain DATA DIRECTORY automatically.
160 --echo #
161 CREATE TABLE t3 LIKE t2;
162 INSERT INTO t3 VALUES (5, "CREATE TABLE LIKE");
163 SELECT * FROM t3;
164 --source suite/innodb/include/show_i_s_tables.inc
165 --source suite/innodb/include/show_i_s_tablespaces.inc
166 --echo ---- MYSQLD_DATADIR/test
167 --list_files $MYSQLD_DATADIR/test
168
169 --echo #
170 --echo # Now make sure the tables can be fully dropped.
171 --echo #
172 DROP TABLE t2, t3;
173 --source suite/innodb/include/show_i_s_tables.inc
174 --source suite/innodb/include/show_i_s_tablespaces.inc
175 --echo ---- MYSQLD_DATADIR/test
176 --list_files $MYSQLD_DATADIR/test
177 --echo ---- MYSQL_TMP_DIR/alt_dir/test
178 --list_files $MYSQL_TMP_DIR/alt_dir/test
179
180 --echo #
181 --echo # Be sure SQL MODE "NO_DIR_IN_CREATE" prevents the use of DATA DIRECTORY
182 --echo #
183 SET @org_mode=@@sql_mode;
184 SET @@sql_mode='NO_DIR_IN_CREATE';
185 SELECT @@sql_mode;
186 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
187 eval CREATE TABLE t1 (a int, b text) $data_directory_clause;
188 SHOW WARNINGS;
189 INSERT INTO t1 VALUES (6, "SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY");
190 # Checking if tablespace exists in --datadir since DATA DIRECTORY was ignored.
191 --file_exists $MYSQLD_DATADIR/test/t1.ibd
192 DROP TABLE t1;
193 set @@sql_mode=@org_mode;
194
195 --echo #
196 --echo # MySQL engine does not allow DATA DIRECTORY to be
197 --echo # within --datadir for any engine, including InnoDB
198 --echo #
199 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
200 --error ER_WRONG_ARGUMENTS
201 eval CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY '$MYSQLD_DATADIR/test';
202
203 --echo #
204 --echo # TEMPORARY tables are incompatible with DATA DIRECTORY
205 --echo #
206 SET SESSION innodb_strict_mode = ON;
207
208 --echo #
209 --echo # CREATE TEMPORARY TABLE with DATA DIRECTORY is rejected in strict mode.
210 --echo #
211 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
212 --error ER_ILLEGAL_HA
213 eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
214 SHOW WARNINGS;
215
216 SET SESSION innodb_strict_mode = OFF;
217
218 --echo #
219 --echo # DATA DIRECTORY is ignored in CREATE TEMPORARY TABLE in non-strict mode.
220 --echo #
221 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
222 eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
223 SHOW CREATE TABLE t1;
224 DROP TABLE t1;
225
226 --echo #
227 --echo # A warning should be issued if DATA DIR and TEMP is used without
228 --echo # innodb_file_per_table
229 --echo #
230 SET GLOBAL innodb_file_per_table=OFF;
231 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
232 eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
233 DROP TABLE t1;
234 SET SESSION innodb_strict_mode = ON;
235 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
236 --error ER_ILLEGAL_HA
237 eval CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB $data_directory_clause;
238 SHOW WARNINGS;
239 SET SESSION innodb_strict_mode = OFF;
240 SET GLOBAL innodb_file_per_table=ON;
241 --echo ---- MYSQLD_DATADIR/test
242 --list_files $MYSQLD_DATADIR/test
243 --echo ---- MYSQL_TMP_DIR/alt_dir/test
244 --list_files $MYSQL_TMP_DIR/alt_dir/test
245
246 --echo #
247 --echo # Create the remote table via static DDL statements in a stored procedure
248 --echo #
249 DELIMITER |;
250 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
251 eval CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) $data_directory_clause; END |
252 DELIMITER ;|
253 CALL static_proc;
254 --source suite/innodb/include/show_i_s_tablespaces.inc
255 INSERT INTO t1 VALUES (7, "Create the remote table via static DDL statements");
256 SELECT * FROM t1;
257 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
258 SHOW CREATE TABLE t1;
259 --echo ---- MYSQLD_DATADIR/test
260 --list_files $MYSQLD_DATADIR/test
261 --echo ---- MYSQL_TMP_DIR/alt_dir/test
262 --list_files $MYSQL_TMP_DIR/alt_dir/test
263 DROP PROCEDURE static_proc;
264 DROP TABLE t1;
265
266 --echo #
267 --echo # Create the remote table via dynamic DDL statements in a stored procedure
268 --echo #
269 DELIMITER |;
270 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
271 eval CREATE PROCEDURE dynamic_proc() BEGIN PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text) $data_directory_clause"; EXECUTE stmt1; END |
272 DELIMITER ;|
273 CALL dynamic_proc;
274 --source suite/innodb/include/show_i_s_tablespaces.inc
275 INSERT INTO t1 VALUES (8, "Create the remote table via dynamic DDL statements");
276 SELECT * FROM t1;
277 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
278 SHOW CREATE TABLE t1;
279 --echo ---- MYSQLD_DATADIR/test
280 --list_files $MYSQLD_DATADIR/test
281 --echo ---- MYSQL_TMP_DIR/alt_dir/test
282 --list_files $MYSQL_TMP_DIR/alt_dir/test
283 DROP PROCEDURE dynamic_proc;
284 DEALLOCATE PREPARE stmt1;
285 DROP TABLE t1;
286
287 --echo #
288 --echo # CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY
289 --echo #
290 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
291 eval CREATE TABLE emp (
292 id INT NOT NULL,
293 store_name VARCHAR(30),
294 parts VARCHAR(30),
295 store_id INT
296 )
297 PARTITION BY LIST(store_id) (
298 PARTITION east VALUES IN (10,20,30)
299 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_east',
300 PARTITION north VALUES IN (40,50,60)
301 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_north',
302 PARTITION west VALUES IN (70,80,100)
303 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west'
304 );
305
306 INSERT INTO emp values(1,'Oracle','NUTT',10);
307 INSERT INTO emp values(2,'HUAWEI','BOLT',40);
308 INSERT INTO emp values(3,'IBM','NAIL',70);
309
310 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
311 SHOW CREATE TABLE emp;
312 --source suite/innodb/include/show_i_s_tables.inc
313 --source suite/innodb/include/show_i_s_tablespaces.inc
314 SELECT * FROM emp;
315 --echo ---- MYSQLD_DATADIR/test
316 --replace_regex /emp#P#/emp#p#/
317 --list_files $MYSQLD_DATADIR/test
318 --echo ---- MYSQL_TMP_DIR/alt_dir_east/test
319 --replace_regex /emp#P#/emp#p#/
320 --list_files $MYSQL_TMP_DIR/alt_dir_east/test
321 --echo ---- MYSQL_TMP_DIR/alt_dir_north/test
322 --replace_regex /emp#P#/emp#p#/
323 --list_files $MYSQL_TMP_DIR/alt_dir_north/test
324 --echo ---- MYSQL_TMP_DIR/alt_dir_west/test
325 --replace_regex /emp#P#/emp#p#/
326 --list_files $MYSQL_TMP_DIR/alt_dir_west/test
327
328 --echo #
329 --echo # DROP one PARTITION.
330 --echo #
331 ALTER TABLE emp DROP PARTITION west;
332 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
333 SHOW CREATE TABLE emp;
334 --source suite/innodb/include/show_i_s_tables.inc
335 --source suite/innodb/include/show_i_s_tablespaces.inc
336 SELECT * FROM emp;
337 --echo ---- MYSQLD_DATADIR/test
338 --replace_regex /emp#P#/emp#p#/
339 --list_files $MYSQLD_DATADIR/test
340 --echo ---- MYSQL_TMP_DIR/alt_dir_east/test
341 --replace_regex /emp#P#/emp#p#/
342 --list_files $MYSQL_TMP_DIR/alt_dir_east/test
343 --echo ---- MYSQL_TMP_DIR/alt_dir_north/test
344 --replace_regex /emp#P#/emp#p#/
345 --list_files $MYSQL_TMP_DIR/alt_dir_north/test
346 --echo ---- MYSQL_TMP_DIR/alt_dir_west/test
347 --replace_regex /emp#P#/emp#p#/
348 --list_files $MYSQL_TMP_DIR/alt_dir_west/test
349
350 --echo #
351 --echo # ADD the PARTITION back.
352 --echo #
353 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
354 eval ALTER TABLE emp ADD PARTITION (
355 PARTITION west VALUES IN (70,80,100)
356 DATA DIRECTORY = '$MYSQL_TMP_DIR/alt_dir_west');
357 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
358 SHOW CREATE TABLE emp;
359 --source suite/innodb/include/show_i_s_tables.inc
360 --source suite/innodb/include/show_i_s_tablespaces.inc
361
362 INSERT INTO emp VALUES(3,'IBM','NAIL',70);
363 SELECT * FROM emp;
364
365 --echo ---- MYSQLD_DATADIR/test
366 --replace_regex /emp#P#/emp#p#/
367 --list_files $MYSQLD_DATADIR/test
368 --echo ---- MYSQL_TMP_DIR/alt_dir_east/test
369 --replace_regex /emp#P#/emp#p#/
370 --list_files $MYSQL_TMP_DIR/alt_dir_east/test
371 --echo ---- MYSQL_TMP_DIR/alt_dir_north/test
372 --replace_regex /emp#P#/emp#p#/
373 --list_files $MYSQL_TMP_DIR/alt_dir_north/test
374 --echo ---- MYSQL_TMP_DIR/alt_dir_west/test
375 --replace_regex /emp#P#/emp#p#/
376 --list_files $MYSQL_TMP_DIR/alt_dir_west/test
377
378 --echo #
379 --echo # TRUNCATE one PARTITION.
380 --echo #
381 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
382 ALTER TABLE emp TRUNCATE PARTITION west;
383 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
384 SHOW CREATE TABLE emp;
385 --source suite/innodb/include/show_i_s_tables.inc
386 --source suite/innodb/include/show_i_s_tablespaces.inc
387
388 SELECT * FROM emp;
389 INSERT INTO emp VALUES(3,'IBM','NAIL',70);
390
391 SELECT * FROM emp;
392 --echo ---- MYSQLD_DATADIR/test
393 --replace_regex /emp#P#/emp#p#/
394 --list_files $MYSQLD_DATADIR/test
395 --echo ---- MYSQL_TMP_DIR/alt_dir_east/test
396 --replace_regex /emp#P#/emp#p#/
397 --list_files $MYSQL_TMP_DIR/alt_dir_east/test
398 --echo ---- MYSQL_TMP_DIR/alt_dir_north/test
399 --replace_regex /emp#P#/emp#p#/
400 --list_files $MYSQL_TMP_DIR/alt_dir_north/test
401 --echo ---- MYSQL_TMP_DIR/alt_dir_west/test
402 --replace_regex /emp#P#/emp#p#/
403 --list_files $MYSQL_TMP_DIR/alt_dir_west/test
404
405 DROP TABLE emp;
406
407 --echo #
408 --echo # Cleanup
409 --echo #
410
411 --rmdir $MYSQL_TMP_DIR/alt_dir_east/test
412 --rmdir $MYSQL_TMP_DIR/alt_dir_east
413 --rmdir $MYSQL_TMP_DIR/alt_dir_north/test
414 --rmdir $MYSQL_TMP_DIR/alt_dir_north
415 --rmdir $MYSQL_TMP_DIR/alt_dir_west/test
416 --rmdir $MYSQL_TMP_DIR/alt_dir_west
417 --rmdir $MYSQL_TMP_DIR/alt_dir/test
418 --rmdir $MYSQL_TMP_DIR/alt_dir
419
420 --disable_query_log
421 EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
422 --enable_query_log
423
424 --echo #
425 --echo # Bug#22899690 FAILING ASSERT: FIL_SPACE_GET(TABLE->SPACE) !=__NULL
426 --echo # IN ROW0QUIESCE.CC LINE 724
427 --echo #
428 CREATE TABLE t1(c1 CHAR (1));
429 ALTER TABLE t1 DISCARD TABLESPACE;
430 --error ER_TABLESPACE_DISCARDED
431 FLUSH TABLES t1 FOR EXPORT;
432 UNLOCK TABLES;
433 DROP TABLE t1;
434
435 --echo #
436 --echo # Bug#27903881 [MYSQL 8.0 GA RELEASE & DEBUG BUILD]
437 --echo # FIL_SPACE_GET(TABLE->SPACE) != __NULL
438 --echo #
439 --disable_query_log
440 call mtr.add_suppression("\\[Warning\\] Missing .ibd file for table `test`\.`t1` .* ");
441 --enable_query_log
442 CREATE TABLE t1(c1 INT,c2 CHAR,c3 DATE)PARTITION BY HASH(DAYOFWEEK(c3));
443 ALTER TABLE t1 DISCARD TABLESPACE;
444 --error ER_TABLESPACE_DISCARDED
445 FLUSH TABLES t1 FOR EXPORT;
446 UNLOCK TABLES;
447 DROP TABLE t1;
448
449 --echo #
450 --echo # Bug#29492113 TABLE NOT ACCESSIBLE AFTER MOVING THE IBD FILE,
451 --echo # WHEN THE DATABASE NAME HAS "/"
452 --echo #
453
454 --echo #
455 --echo # Scenario-1
456 --echo # create database `abc/def`
457 --echo # create table `abc/def`.`xyz/123` in MYSQL_TMP_DIR/my_dir1
458 --echo # The directory structure after creating the table should be
459 --echo # MYSQL_TMP_DIR/my_dir1/abc@002fdef/[email protected]
460 --echo #
461
462 CREATE DATABASE `abc/def`;
463
464 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
465 eval CREATE TABLE `abc/def`.`xyz/123` (
466 c1 INT AUTO_INCREMENT KEY, c2 CHAR(10))
467 PARTITION BY RANGE(c1) SUBPARTITION BY HASH(c1) (
468 PARTITION `p0/part` VALUES LESS THAN (10) (
469 SUBPARTITION s0 DATA DIRECTORY='$MYSQL_TMP_DIR/my_dir1',
470 SUBPARTITION `s1/subpart` DATA DIRECTORY='$MYSQL_TMP_DIR/my_dir1'),
471 PARTITION p1 VALUES LESS THAN MAXVALUE (
472 SUBPARTITION s2 DATA DIRECTORY='$MYSQL_TMP_DIR/my_dir1',
473 SUBPARTITION s3 DATA DIRECTORY='$MYSQL_TMP_DIR/my_dir1'));
474 SHOW WARNINGS;
475
476 --echo #
477 --echo # Validate the directory structure
478 --echo #
479 --list_files $MYSQL_TMP_DIR/my_dir1 *
480 --replace_regex /#P#/#p#/ /#SP#/#sp#/
481 --list_files $MYSQL_TMP_DIR/my_dir1/abc@002fdef *
482
483 INSERT INTO `abc/def`.`xyz/123` VALUES (0, 'MySQL');
484 INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
485 INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
486 INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
487 INSERT INTO `abc/def`.`xyz/123` (SELECT 0, c2 FROM `abc/def`.`xyz/123`);
488 SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
489
490 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
491 --replace_regex /#P#/#p#/ /#SP#/#sp#/
492 SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
493 --replace_regex /#P#/#p#/ /#SP#/#sp#/
494 SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
495
496 --echo #
497 --echo # Shutdown the server
498 --echo #
499 --source include/shutdown_mysqld.inc
500
501 --echo #
502 --echo # Scenario-2
503 --echo # Move the IBD file from MYSQL_TMP_DIR/my_dir1 to MYSQL_TMP_DIR/my_dir2
504 --echo # Restart the server with --innodb_directories MYSQL_TMP_DIR/my_dir2
505 --echo # Query the table
506 --echo # Restart the server without --innodb_directories option
507 --echo #
508
509 --echo #
510 --echo # Move IBD file in MYSQL_TMP_DIR/my_dir1 to MYSQL_TMP_DIR/my_dir2
511 --echo #
512 --mkdir $MYSQL_TMP_DIR/my_dir2
513 --mkdir $MYSQL_TMP_DIR/my_dir2/abc@002fdef
514 --copy_files_wildcard $MYSQL_TMP_DIR/my_dir1/abc@002fdef/ $MYSQL_TMP_DIR/my_dir2/abc@002fdef/ *
515 --remove_files_wildcard $MYSQL_TMP_DIR/my_dir1/abc@002fdef/ *
516
517 --echo #
518 --echo # Resart server with --innodb_directories MYSQL_TMP_DIR/my_dir2
519 --echo #
520 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
521 --let $restart_parameters = "restart: --innodb_directories=$MYSQL_TMP_DIR/my_dir2"
522 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
523 --source include/start_mysqld.inc
524
525 SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
526
527 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
528 --replace_regex /#P#/#p#/ /#SP#/#sp#/
529 SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
530 --replace_regex /#P#/#p#/ /#SP#/#sp#/
531 SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
532
533 --echo #
534 --echo # Restart server without --innodb_directories option
535 --echo #
536 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
537 --let $restart_parameters = "restart:"
538 --source include/restart_mysqld.inc
539
540 SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
541
542 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
543 --replace_regex /#P#/#p#/ /#SP#/#sp#/
544 SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
545 --replace_regex /#P#/#p#/ /#SP#/#sp#/
546 SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
547
548 --echo #
549 --echo # Shutdown server
550 --echo #
551 --source include/shutdown_mysqld.inc
552
553 --echo #
554 --echo # Scenario-3
555 --echo # Move the IBD file from MYSQL_TMP_DIR/my_dir2 to MYSQL_TMP_DIR/my_dir1
556 --echo # Restart the server with --innodb_directories MYSQL_TMP_DIR/my_dir1
557 --echo #
558
559 --echo #
560 --echo # Move IBD file from MYSQL_TMP_DIR/my_dir2 to MYSQL_TMP_DIR/my_dir1
561 --echo #
562 --copy_files_wildcard $MYSQL_TMP_DIR/my_dir2/abc@002fdef/ $MYSQL_TMP_DIR/my_dir1/abc@002fdef/ *
563 --force-rmdir $MYSQL_TMP_DIR/my_dir2
564
565 --echo #
566 --echo # Restart server with --innodb_directories MYSQL_TMP_DIR/my_dir1
567 --echo #
568 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
569 --let $restart_parameters="restart: --innodb_directories=$MYSQL_TMP_DIR/my_dir1"
570 --source include/start_mysqld.inc
571
572 SELECT COUNT(*) FROM `abc/def`.`xyz/123`;
573
574 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
575 --replace_regex /#P#/#p#/ /#SP#/#sp#/
576 SELECT file_name, tablespace_name FROM information_schema.files WHERE file_name LIKE '%123%';
577 --replace_regex /#P#/#p#/ /#SP#/#sp#/
578 SELECT name, space_type FROM information_schema.innodb_tablespaces WHERE name LIKE '%123%';
579
580 --echo #
581 --echo # Cleanup
582 --echo #
583 DROP DATABASE `abc/def`;
584 --force-rmdir $MYSQL_TMP_DIR/my_dir1
585
586 --let $restart_parameters= restart:
587 --source include/restart_mysqld.inc
588
589 --disable_query_log
590 call mtr.add_suppression("\\[Warning\\].* The datafile '.*' for tablespace .* is in an unprotected location. This file cannot be recovered after a crash until this location is added to innodb_directories");
591 call mtr.add_suppression("\\[Warning\\].* Tablespace .*, name '.*', file '.*' is missing");
592 --enable_query_log