Debian Codegraph @

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

raw
1 --echo #
2 --echo #Bug #33398681 Innodb ibd tablespace file is moved back from innodb_directories to datadir after ALTER TABLE
3 --echo #
4
5 #This test will check whether the ibd tablespace file is moved back or not
6
7 #Table t1a: Covers scenario create non-partitioned table without data directory specification and alter table force
8 #Table t1b: Covers scenario create non-partitioned table without data directory specification and alter table force algo=inplace
9 #Table t1c: Covers scenario create non-partitioned table without data directory specification and alter table force algo=copy
10 #Table t2a: Covers scenario create non-partitioned table with data directory specification and alter table force
11 #Table t2b: Covers scenario create non-partitioned table with data directory specification and alter table force algo=inplace
12 #Table t2c: Covers scenario create non-partitioned table with data directory specification and alter table force algo=copy
13 #Table t3a: Covers scenario create partitioned table without data directory specification and alter table force
14 #Table t3b: Covers scenario create partitioned table without data directory specification and alter table force algo=inplace
15 #Table t3c: Covers scenario create partitioned table without data directory specification and alter table force algo=copy
16 #Table t4a: Covers scenario create partitioned table with data directory specification and alter table force
17 #Table t4b: Covers scenario create partitioned table with data directory specification and alter table force algo=inplace
18 #Table t4c: Covers scenario create partitioned table with data directory specification and alter table force algo=copy
19 #Table t5a: Covers scenario create non-partitioned table with data directory specification and alter table force but moving ibd files from external dir to default dir
20 #Table t5b: Covers scenario create non-partitioned table with data directory specification and alter table force algo=inplace but moving ibd files from external dir to default dir
21 #Table t5c: Covers scenario create non-partitioned table with data directory specification and alter table force algo=copy but moving ibd files from external dir to default dir
22 #Table t6a: Covers scenario create partitioned table with data directory specification and alter table force but moving ibd files from external dir to default dir
23 #Table t6b: Covers scenario create partitioned table with data directory specification and alter table force algo=inplace but moving ibd files from external dir to default dir
24 #Table t6c: Covers scenario create partitioned table with data directory specification and alter table force algo=copy but moving ibd files from external dir to default dir
25 #Table t7a: Covers scenario create non-partitioned table with data directory specification and move from external dir 1 to external dir 2 and alter table force
26 #Table t7b: Covers scenario create non-partitioned table with data directory specification and move from external dir 1 to external dir 2 and alter table force algo=inplace
27 #Table t7c: Covers scenario create non-partitioned table with data directory specification and move from external dir 1 to external dir 2 and alter table force algo=copy
28 #Table t8a: Covers scenario create partitioned table with data directory clause and both partitions in different directories, move to default dir and alter table force
29 #Table t8b: Covers scenario create partitioned table with data directory clause and both partitions in different directories, move to default dir and alter table force algo=inplace
30 #Table t8c: Covers scenario create partitioned table with data directory clause and both partitions in different directories, move to default dir and alter table force algo=copy
31 #Table t8d: Covers scenario create partitioned table with one partition in data directory clause and the other partition in default data dir path, move to default dir and alter table force algo=copy
32 #Table t9a: Covers scenario create partitioned table without data directory clause but moving each partition to different external directories and alter table force
33 #Table t9b: Covers scenario create partitioned table without data directory clause but moving each partition to different external directories and alter table force algo=inplace
34 #Table t9c: Covers scenario create partitioned table without data directory clause but moving each partition to different external directories and alter table force algo=copy
35
36 --echo # Set different paths for --datadir
37 LET $MYSQLD_DATADIR = `select @@datadir`;
38 LET $EXTERNAL_DIR = $MYSQL_TMP_DIR/external_dir;
39 LET $EXTERNAL_DIR2 = $MYSQL_TMP_DIR/external_dir2;
40 LET $INNODB_DIRECTORIES = $MYSQLTEST_VARDIR/innodb_directories;
41 LET $data_directory_clause = DATA DIRECTORY='$EXTERNAL_DIR';
42 LET $data_directory_clause2 = DATA DIRECTORY='$EXTERNAL_DIR2';
43
44 --echo # Create innodb_directories folder
45 --mkdir $INNODB_DIRECTORIES
46 --mkdir $INNODB_DIRECTORIES/dbtest
47
48 --echo # Create external datadir folder
49 --mkdir $EXTERNAL_DIR
50 --mkdir $EXTERNAL_DIR/dbtest
51
52 --echo # Create external datadir2 folder
53 --mkdir $EXTERNAL_DIR2
54 --mkdir $EXTERNAL_DIR2/dbtest
55
56 --echo # Restart the engine to make the external directory known.
57 --let $external_dirs=$EXTERNAL_DIR;$EXTERNAL_DIR2
58 let restart_parameters = restart: --innodb-directories=$external_dirs;
59 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
60 --source include/restart_mysqld.inc
61
62 --echo # Create table
63 CREATE DATABASE dbtest;
64 USE dbtest;
65
66 --echo # Normal tables
67 CREATE TABLE `t1a` (`c1` INT);
68 CREATE TABLE `t1b` (`c1` INT);
69 CREATE TABLE `t1c` (`c1` INT);
70
71 --echo # Normal tables with data directory clause
72 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
73 eval CREATE TABLE `t2a` (`c1` INT) $data_directory_clause;
74 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
75 eval CREATE TABLE `t2b` (`c1` INT) $data_directory_clause;
76 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
77 eval CREATE TABLE `t2c` (`c1` INT) $data_directory_clause;
78
79 --echo # Partition tables
80 CREATE TABLE `t3a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
81 CREATE TABLE `t3b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
82 CREATE TABLE `t3c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
83
84 --echo # Partition tables with data directory clause
85 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
86 eval CREATE TABLE `t4a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) $data_directory_clause, PARTITION p1 VALUES LESS THAN (20) $data_directory_clause);
87 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
88 eval CREATE TABLE `t4b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) $data_directory_clause, PARTITION p1 VALUES LESS THAN (20) $data_directory_clause);
89 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
90 eval CREATE TABLE `t4c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) $data_directory_clause, PARTITION p1 VALUES LESS THAN (20) $data_directory_clause);
91
92 --echo # Normal tables with data directory clause 2
93 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
94 eval CREATE TABLE `t5a` (`c1` INT) $data_directory_clause2;
95 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
96 eval CREATE TABLE `t5b` (`c1` INT) $data_directory_clause2;
97 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
98 eval CREATE TABLE `t5c` (`c1` INT) $data_directory_clause2;
99
100 --echo # Partition tables with data directory clause
101 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
102 eval CREATE TABLE `t6a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) $data_directory_clause2, PARTITION p1 VALUES LESS THAN (20) $data_directory_clause2);
103 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
104 eval CREATE TABLE `t6b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) $data_directory_clause2, PARTITION p1 VALUES LESS THAN (20) $data_directory_clause2);
105 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
106 eval CREATE TABLE `t6c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) $data_directory_clause2, PARTITION p1 VALUES LESS THAN (20) $data_directory_clause2);
107
108 --echo # Shutdown server
109 --source include/shutdown_mysqld.inc
110
111 --echo # 1. ibd files for t1a, t1b, t1c, t3a, t3b, t3c
112
113 --echo # Check that the ibd files for each table exist in the working (dbtest) folder
114 --list_files $MYSQLD_DATADIR/dbtest *
115
116 --echo # Move ibd files from default directory to new innodb directory
117 --copy_files_wildcard $MYSQLD_DATADIR/dbtest/ $INNODB_DIRECTORIES/dbtest/ *
118 --remove_files_wildcard $MYSQLD_DATADIR/dbtest/ *
119
120 --echo # Check that the files exist in the innodb_directories folder before alter after moving
121 --list_files $INNODB_DIRECTORIES/dbtest *
122
123 --echo # 2. ibd files for t2a, t2b, t2c, t4a, t4b, t4c
124
125 --echo # Check that the ibd files for each table exist in the external directory where the tables are created
126 --list_files $EXTERNAL_DIR/dbtest *
127
128 --echo # 3. ibd files for t5a, t5b, t5c, t6a, t6b, t6c
129
130 --echo # Check that the ibd files for each table exist in the external directory2 where the tables are created
131 --list_files $EXTERNAL_DIR2/dbtest *
132
133 --echo # Move ibd files for t5a, t5b, t5c, t6a, t6b, t6c from external directory2 to default directory
134 --copy_files_wildcard $EXTERNAL_DIR2/dbtest/ $MYSQLD_DATADIR/dbtest/ *
135 --remove_files_wildcard $EXTERNAL_DIR2/dbtest/ *
136
137 --echo # Check that the ibd files for t5a, t5b, t5c, t6a, t6b, t6c now exist in working (dbtest) folder
138 --list_files $MYSQLD_DATADIR/dbtest *
139
140 --echo # Restart the server
141 --let $innodb_dirs=$INNODB_DIRECTORIES;$EXTERNAL_DIR;$EXTERNAL_DIR2
142 let restart_parameters = restart: --innodb-directories=$innodb_dirs;
143 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
144 --source include/start_mysqld.inc
145
146 --echo # Alter table
147 ALTER TABLE dbtest.t1a FORCE;
148 ALTER TABLE dbtest.t1b FORCE, ALGORITHM=INPLACE;
149 ALTER TABLE dbtest.t1c FORCE, ALGORITHM=COPY;
150
151 ALTER TABLE dbtest.t2a FORCE;
152 ALTER TABLE dbtest.t2b FORCE, ALGORITHM=INPLACE;
153 ALTER TABLE dbtest.t2c FORCE, ALGORITHM=COPY;
154
155 ALTER TABLE dbtest.t3a FORCE;
156 ALTER TABLE dbtest.t3b FORCE, ALGORITHM=INPLACE;
157 ALTER TABLE dbtest.t3c FORCE, ALGORITHM=COPY;
158
159 ALTER TABLE dbtest.t4a FORCE;
160 ALTER TABLE dbtest.t4b FORCE, ALGORITHM=INPLACE;
161 ALTER TABLE dbtest.t4c FORCE, ALGORITHM=COPY;
162
163 ALTER TABLE dbtest.t5a FORCE;
164 ALTER TABLE dbtest.t5b FORCE, ALGORITHM=INPLACE;
165 ALTER TABLE dbtest.t5c FORCE, ALGORITHM=COPY;
166
167 ALTER TABLE dbtest.t6a FORCE;
168 ALTER TABLE dbtest.t6b FORCE, ALGORITHM=INPLACE;
169 ALTER TABLE dbtest.t6c FORCE, ALGORITHM=COPY;
170
171 --echo # Check that the ibd files for t1a, t1b, t1c, t3a, t3b, t3c still exist in the innodb_directories folder
172 --list_files $INNODB_DIRECTORIES/dbtest *
173
174 --echo # Check that the ibd files for t1a, t1b, t1c, t3a, t3b, t3c do not exist and ibd files for t5a, t5b, t5c, t6a, t6b, t6c exist in the data directory folder
175 --list_files $MYSQLD_DATADIR/dbtest *
176
177 --echo # Check that the ibd files for t5a, t5b, t5c, t6a, t6b, t6c do not exist in external directory2 where the tables are created
178 --list_files $EXTERNAL_DIR2/dbtest *
179
180 --echo # Check that the ibd files for t2a, t2b, t2c, t4a, t4b, t4c still exist in the external directory where the tables are created
181 --list_files $EXTERNAL_DIR/dbtest *
182
183 --echo # Shutdown server
184 --source include/shutdown_mysqld.inc
185
186 --echo # Now move ibd files for t1a, t1b, t1c, t3a, t3b, t3c from innodb_directories folder to default directory
187 --copy_files_wildcard $INNODB_DIRECTORIES/dbtest/ $MYSQLD_DATADIR/dbtest/ *
188 --remove_files_wildcard $INNODB_DIRECTORIES/dbtest/ *
189
190 --echo # Restart the server
191 --let $innodb_dirs=$INNODB_DIRECTORIES;$EXTERNAL_DIR;$EXTERNAL_DIR2
192 let restart_parameters = restart: --innodb-directories=$innodb_dirs;
193 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
194 --source include/start_mysqld.inc
195
196 --echo # Alter table
197 ALTER TABLE dbtest.t1a FORCE;
198 ALTER TABLE dbtest.t1b FORCE, ALGORITHM=INPLACE;
199 ALTER TABLE dbtest.t1c FORCE, ALGORITHM=COPY;
200
201 ALTER TABLE dbtest.t3a FORCE;
202 ALTER TABLE dbtest.t3b FORCE, ALGORITHM=INPLACE;
203 ALTER TABLE dbtest.t3c FORCE, ALGORITHM=COPY;
204
205 --echo # Check ibd files for t1a, t1b, t1c, t3a, t3b, t3c exist in default directory
206 --list_files $MYSQLD_DATADIR/dbtest *
207
208 --echo # Check ibd files for t1a, t1b, t1c, t3a, t3b, t3c do not exist in innodb_directories
209 --list_files $INNODB_DIRECTORIES/dbtest *
210
211 --echo # Clean Up
212
213 --echo # Drop tables
214 DROP TABLE dbtest.t1a;
215 DROP TABLE dbtest.t1b;
216 DROP TABLE dbtest.t1c;
217 DROP TABLE dbtest.t2a;
218 DROP TABLE dbtest.t2b;
219 DROP TABLE dbtest.t2c;
220 DROP TABLE dbtest.t3a;
221 DROP TABLE dbtest.t3b;
222 DROP TABLE dbtest.t3c;
223 DROP TABLE dbtest.t4a;
224 DROP TABLE dbtest.t4b;
225 DROP TABLE dbtest.t4c;
226 DROP TABLE dbtest.t5a;
227 DROP TABLE dbtest.t5b;
228 DROP TABLE dbtest.t5c;
229 DROP TABLE dbtest.t6a;
230 DROP TABLE dbtest.t6b;
231 DROP TABLE dbtest.t6c;
232
233 --echo # Clean up for other tests
234 --remove_files_wildcard $INNODB_DIRECTORIES/dbtest/ *
235 --remove_files_wildcard $EXTERNAL_DIR/dbtest/ *
236 --remove_files_wildcard $EXTERNAL_DIR2/dbtest/ *
237
238 --echo # Additional test for tables with data directory clause
239
240 --echo # Move non-partitioned table with data dir clause at EXTERNAL_DIR1 to EXTERNAL_DIR2
241 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
242 eval CREATE TABLE `t7a` (`c1` INT) $data_directory_clause;
243 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
244 eval CREATE TABLE `t7b` (`c1` INT) $data_directory_clause;
245 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
246 eval CREATE TABLE `t7c` (`c1` INT) $data_directory_clause;
247
248 --echo # Shutdown server
249 --source include/shutdown_mysqld.inc
250
251 --echo # Check that the ibd files for t7a, t7b, t7c exist in EXTERNAL_DIR
252 --list_files $EXTERNAL_DIR/dbtest/ *
253
254 --echo # Move files from EXTERNAL_DIR to EXTERNAL_DIR2
255 --copy_files_wildcard $EXTERNAL_DIR/dbtest/ $EXTERNAL_DIR2/dbtest/ *
256 --remove_files_wildcard $EXTERNAL_DIR/dbtest/ *
257
258 --echo # Check that the ibd files now exist in EXTERNAL_DIR2
259 --list_files $EXTERNAL_DIR2/dbtest/ *
260
261 --echo # Restart the server
262 --let $innodb_dirs=$EXTERNAL_DIR;$EXTERNAL_DIR2
263 let restart_parameters = restart: --innodb-directories=$innodb_dirs;
264 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
265 --source include/start_mysqld.inc
266
267 --echo # Alter table
268 ALTER TABLE dbtest.t7a FORCE;
269 ALTER TABLE dbtest.t7b FORCE, ALGORITHM=INPLACE;
270 ALTER TABLE dbtest.t7c FORCE, ALGORITHM=COPY;
271
272 --echo # Check that the ibd files for t7a, t7b, t7c still exist in EXTERNAL_DIR2
273 --list_files $EXTERNAL_DIR2/dbtest/ *
274
275 --echo # Check that the ibd files for t7a, t7b, t7c do not exist in EXTERNAL_DIR
276 --list_files $EXTERNAL_DIR/dbtest/ *
277
278 --echo # Clean up
279
280 --echo # Drop tables
281 DROP TABLE dbtest.t7a;
282 DROP TABLE dbtest.t7b;
283 DROP TABLE dbtest.t7c;
284
285 --echo # Clean up for other tests
286 --remove_files_wildcard $EXTERNAL_DIR/dbtest/ *
287 --remove_files_wildcard $EXTERNAL_DIR2/dbtest/ *
288
289
290 --echo # Additional tests for partitioned tables
291
292 --echo # 1. Creating partitioned table with data directory clause and both partitions in different directories
293 --echo # Partition tables with data directory clause
294 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
295 eval CREATE TABLE `t8a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) $data_directory_clause, PARTITION p1 VALUES LESS THAN (20) $data_directory_clause2);
296 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
297 eval CREATE TABLE `t8b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) $data_directory_clause, PARTITION p1 VALUES LESS THAN (20) $data_directory_clause2);
298 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
299 eval CREATE TABLE `t8c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) $data_directory_clause, PARTITION p1 VALUES LESS THAN (20) $data_directory_clause2);
300 --echo # Partition table with one partition in external directory and one in default directory
301 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
302 eval CREATE TABLE `t8d` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10) $data_directory_clause, PARTITION p1 VALUES LESS THAN (20) );
303
304 --echo # Shutdown server
305 --source include/shutdown_mysqld.inc
306
307 --echo # Check that for partition p0 for t8a, t8b, t8c and t8d exist in EXTERNAL_DIR and partition p1 for for t8a, t8b, t8c exist in EXTERNAL_DIR2
308
309 --echo # Listing EXTERNAL_DIR
310 --list_files $EXTERNAL_DIR/dbtest/ *
311
312 --echo # Listing EXTERNAL_DIR2
313 --list_files $EXTERNAL_DIR2/dbtest/ *
314
315 --echo # List default directory. t8d's p1 partition should be present
316 --list_files $MYSQLD_DATADIR/dbtest *
317
318 --echo # Restart the server
319 --let $innodb_dirs=$EXTERNAL_DIR;$EXTERNAL_DIR2
320 let restart_parameters = restart: --innodb-directories=$innodb_dirs;
321 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
322 --source include/start_mysqld.inc
323
324 --echo # Alter table
325 ALTER TABLE dbtest.t8a FORCE;
326 ALTER TABLE dbtest.t8b FORCE, ALGORITHM=INPLACE;
327 ALTER TABLE dbtest.t8c FORCE, ALGORITHM=COPY;
328 ALTER TABLE dbtest.t8d FORCE, ALGORITHM=COPY;
329 --echo # Check that for partition p0 for t8a, t8b, t8c still exist in EXTERNAL_DIR and partition p1 for for t8a, t8b, t8c still exist in EXTERNAL_DIR2
330
331 --echo # Listing EXTERNAL_DIR
332 --list_files $EXTERNAL_DIR/dbtest/ *
333
334 --echo # Listing EXTERNAL_DIR2
335 --list_files $EXTERNAL_DIR2/dbtest/ *
336
337 --echo # List default directory. t8d's p1 partition should be present
338 --list_files $MYSQLD_DATADIR/dbtest *
339
340 --echo # Shutdown server
341 --source include/shutdown_mysqld.inc
342
343 --echo # Now lets move all partitioned tables p0 of t8a, t8b, t8c, t8d from EXTERNAL_DIR and partitioned tables p1 of t8a, t8b, t8c from EXTERNAL_DIR2 to default directory
344 --copy_files_wildcard $EXTERNAL_DIR/dbtest/ $MYSQLD_DATADIR/dbtest/ *
345 --remove_files_wildcard $EXTERNAL_DIR/dbtest/ *
346 --copy_files_wildcard $EXTERNAL_DIR2/dbtest/ $MYSQLD_DATADIR/dbtest/ *
347 --remove_files_wildcard $EXTERNAL_DIR2/dbtest/ *
348
349 --echo # Restart the server
350 --let $innodb_dirs=$EXTERNAL_DIR;$EXTERNAL_DIR2
351 let restart_parameters = restart: --innodb-directories=$innodb_dirs;
352 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
353 --source include/start_mysqld.inc
354
355 --echo # Alter table
356 ALTER TABLE dbtest.t8a FORCE;
357 ALTER TABLE dbtest.t8b FORCE, ALGORITHM=INPLACE;
358 ALTER TABLE dbtest.t8c FORCE, ALGORITHM=COPY;
359
360 --echo # Check that all the partitioned tables for t8a, t8b, t8c and t8d now exist in default directory
361 --list_files $MYSQLD_DATADIR/dbtest *
362
363 --echo # Check that EXTERNAL_DIR and EXTERNAL_DIR2 are empty
364 --echo # Listing EXTERNAL_DIR
365 --list_files $EXTERNAL_DIR/dbtest/ *
366
367 --echo # Listing EXTERNAL_DIR2
368 --list_files $EXTERNAL_DIR2/dbtest/ *
369
370 ALTER TABLE dbtest.t8d FORCE, ALGORITHM=COPY;
371
372 --echo # Check that all the partitioned tables for t8a, t8b, t8c and t8d now exist in default directory
373 --list_files $MYSQLD_DATADIR/dbtest *
374
375 --echo # Check that EXTERNAL_DIR and EXTERNAL_DIR2 are empty
376 --echo # Listing EXTERNAL_DIR
377 --list_files $EXTERNAL_DIR/dbtest/ *
378
379 --echo # Listing EXTERNAL_DIR2
380 --list_files $EXTERNAL_DIR2/dbtest/ *
381
382 --echo # Drop tables
383 DROP TABLE dbtest.t8a;
384 DROP TABLE dbtest.t8b;
385 DROP TABLE dbtest.t8c;
386 DROP TABLE dbtest.t8d;
387
388 --echo # Clean up for other tests
389 --remove_files_wildcard $EXTERNAL_DIR/dbtest/ *
390 --remove_files_wildcard $EXTERNAL_DIR2/dbtest/ *
391
392 --echo # 2. Creating partitioned table without data directory clause but moving each partition to different external directories
393 --echo # Partition tables
394 CREATE TABLE `t9a` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
395 CREATE TABLE `t9b` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
396 CREATE TABLE `t9c` (a INT, b INT) PARTITION BY RANGE(a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20));
397
398 --echo # Shutdown server
399 --source include/shutdown_mysqld.inc
400
401 --echo # Check that the ibd files for all p0 and p1 partitions of t9a, t9b, t9c table exist in the working (dbtest) folder
402 --list_files $MYSQLD_DATADIR/dbtest *
403
404 --echo # Now move only p1 partitioned tables of t9a, t9b, t9c to EXTERNAL_DIR
405 --copy_files_wildcard $MYSQLD_DATADIR/dbtest/ $EXTERNAL_DIR/dbtest/ t9a#p#p1.ibd
406 --remove_files_wildcard $MYSQLD_DATADIR/dbtest/ t9a#p#p1.ibd
407 --copy_files_wildcard $MYSQLD_DATADIR/dbtest/ $EXTERNAL_DIR/dbtest/ t9b#p#p1.ibd
408 --remove_files_wildcard $MYSQLD_DATADIR/dbtest/ t9b#p#p1.ibd
409 --copy_files_wildcard $MYSQLD_DATADIR/dbtest/ $EXTERNAL_DIR/dbtest/ t9c#p#p1.ibd
410 --remove_files_wildcard $MYSQLD_DATADIR/dbtest/ t9c#p#p1.ibd
411
412 --echo # Check that the ibd files for all p0 of t9a, t9b, t9c table exist in the working (dbtest) folder
413 --list_files $MYSQLD_DATADIR/dbtest *
414
415 --echo # Check that the ibd files for all p1 of t9a, t9b, t9c table exist in EXTERNAL_DIR
416 --list_files $EXTERNAL_DIR/dbtest/ *
417
418 --echo # Restart the server
419 --let $innodb_dirs=$EXTERNAL_DIR
420 let restart_parameters = restart: --innodb-directories=$innodb_dirs;
421 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
422 --source include/start_mysqld.inc
423
424 --echo # Alter table
425 ALTER TABLE dbtest.t9a FORCE;
426 ALTER TABLE dbtest.t9b FORCE, ALGORITHM=INPLACE;
427 ALTER TABLE dbtest.t9c FORCE, ALGORITHM=COPY;
428
429
430 --echo # Check that the ibd files for all p0 of t9a, t9b, t9c table still exist in the working (dbtest) folder
431 --list_files $MYSQLD_DATADIR/dbtest *
432
433 --echo # Check that the ibd files for all p1 of t9a, t9b, t9c table still exist in EXTERNAL_DIR
434 --list_files $EXTERNAL_DIR/dbtest/ *
435
436
437 --echo # Drop tables and database
438 DROP TABLE dbtest.t9a;
439 DROP TABLE dbtest.t9b;
440 DROP TABLE dbtest.t9c;
441 DROP DATABASE dbtest;
442
443 --let restart_parameters="restart: "
444 --source include/restart_mysqld.inc
445
446 --rmdir $INNODB_DIRECTORIES/dbtest
447 --rmdir $INNODB_DIRECTORIES
448
449 --rmdir $EXTERNAL_DIR/dbtest
450 --rmdir $EXTERNAL_DIR
451
452 --rmdir $EXTERNAL_DIR2/dbtest
453 --rmdir $EXTERNAL_DIR2
454
455 --echo # End Test