Debian Codegraph @

mysql-test/suite/innodb/t/choose_tbsp_location-discard.test

raw
1 --echo #
2 --echo # This test shows DISCARD/IMPORT of a remote tablespace.
3 --echo #
4
5 --source include/no_valgrind_without_big.inc
6
7 # These values can change during the test
8 LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
9
10 # Set up some variables
11 --disable_query_log
12 LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
13 LET $MYSQLD_DATADIR = `select @@datadir`;
14 LET $data_directory_clause = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
15 --enable_query_log
16
17 SET default_storage_engine=InnoDB;
18
19 SET GLOBAL innodb_file_per_table=ON;
20
21 --disable_warnings
22 DROP TABLE IF EXISTS t5980;
23 --enable_warnings
24
25 --echo #
26 --echo # CREATE TABLE ... DATA DIRECTORY
27 --echo # combined with WL#5522 - Transportable Tablespace
28 --echo # Create the tablespace in MYSQL_TMP_DIR/alt_dir
29 --echo # InnoDB will create the sub-directories if needed.
30 --echo # Test that DISCARD and IMPORT work correctly.
31 --echo #
32
33 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
34 eval CREATE TABLE t5980 (a int KEY, b text) $data_directory_clause;
35 INSERT INTO t5980 VALUES (1, "Create the tablespace");
36 SELECT * FROM t5980;
37 --echo ### files in MYSQLD_DATADIR/test
38 --list_files $MYSQLD_DATADIR/test
39 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
40 --list_files $MYSQL_TMP_DIR/alt_dir/test
41
42 --echo #
43 --echo # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
44 --echo #
45 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
46 SHOW CREATE TABLE t5980;
47
48
49 --echo #
50 --echo # Backup the cfg and ibd files.
51 --echo #
52 FLUSH TABLES t5980 FOR EXPORT;
53 SELECT * FROM t5980;
54 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak
55 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak
56 UNLOCK TABLES;
57 --echo ### files in MYSQLD_DATADIR/test
58 --list_files $MYSQLD_DATADIR/test
59 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
60 --list_files $MYSQL_TMP_DIR/alt_dir/test
61
62 --echo #
63 --echo # Do some DDL and DML.
64 --echo #
65 INSERT INTO t5980 VALUES (2,'Remote table has been FLUSHed and UNLOCKed');
66 START TRANSACTION;
67 INSERT INTO t5980 VALUES (12,'Transactional record inserted');
68 COMMIT;
69 START TRANSACTION;
70 INSERT INTO t5980 VALUES (13,'Rollback this transactional record');
71 ROLLBACK;
72 SELECT COUNT(*) FROM t5980;
73 SELECT * FROM t5980;
74 ALTER TABLE t5980 DROP PRIMARY KEY;
75 ALTER TABLE t5980 ADD COLUMN c VARCHAR(50) DEFAULT NULL;
76 INSERT INTO t5980(a,b,c) VALUES (2,'Duplicate value since primary key has been dropped','third column added');
77 SELECT * FROM t5980;
78
79 --echo #
80 --echo # Make a second backup of the cfg and ibd files.
81 --echo #
82 FLUSH TABLES t5980 FOR EXPORT;
83 SELECT * FROM t5980;
84 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak2
85 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak2
86 UNLOCK TABLES;
87 --echo ### files in MYSQLD_DATADIR/test
88 --list_files $MYSQLD_DATADIR/test
89 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
90 --list_files $MYSQL_TMP_DIR/alt_dir/test
91
92 --echo #
93 --echo # DROP the table and make sure all files except the backups are gone.
94 --echo #
95 DROP TABLE t5980;
96 --echo ### files in MYSQLD_DATADIR/test
97 --list_files $MYSQLD_DATADIR/test
98 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
99 --list_files $MYSQL_TMP_DIR/alt_dir/test
100
101 --echo #
102 --echo # CREATE the table again.
103 --echo #
104 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
105 eval CREATE TABLE t5980 (a int KEY, b text) $data_directory_clause;
106 INSERT INTO t5980 VALUES (1, "Create the tablespace a second time");
107 SELECT * FROM t5980;
108
109 --echo #
110 --echo # DISCARD existing tablespace so backed-up .ibd which can be imported/restored
111 --echo #
112 ALTER TABLE t5980 DISCARD TABLESPACE;
113 --error ER_TABLESPACE_DISCARDED
114 SELECT * FROM t5980;
115 --echo ### files in MYSQLD_DATADIR/test
116 --list_files $MYSQLD_DATADIR/test
117 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
118 --list_files $MYSQL_TMP_DIR/alt_dir/test
119
120 --echo #
121 --echo # Restore the second backup of cfg and ibd files.
122 --echo #
123 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak2 $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
124 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak2 $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
125 --echo "### files in MYSQL_TMP_DIR/alt_dir/test"
126 --list_files $MYSQL_TMP_DIR/alt_dir/test/
127
128 --echo #
129 --echo # Try to Import the second backup. These backups have extra DDL and
130 --echo # do not match the current frm file.
131 --echo #
132 --error ER_TABLE_SCHEMA_MISMATCH
133 ALTER TABLE t5980 IMPORT TABLESPACE;
134 CHECK TABLE t5980;
135 --remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
136 --remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
137 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
138 --list_files $MYSQL_TMP_DIR/alt_dir/test
139
140 --echo #
141 --echo # Restore the first backup of cfg and ibd files.
142 --echo #
143 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
144 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
145 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
146 --list_files $MYSQL_TMP_DIR/alt_dir/test
147
148 --echo #
149 --echo # Import the tablespace and do some DDL and DML.
150 --echo #
151 ALTER TABLE t5980 IMPORT TABLESPACE;
152 --echo ### files in MYSQLD_DATADIR/test
153 --list_files $MYSQLD_DATADIR/test
154 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
155 --list_files $MYSQL_TMP_DIR/alt_dir/test
156 CHECK TABLE t5980;
157 SELECT COUNT(*) FROM t5980;
158 SELECT * FROM t5980;
159 INSERT INTO t5980 VALUES (2,'Inserted record after IMPORT');
160 SELECT * FROM t5980;
161 START TRANSACTION;
162 INSERT INTO t5980 VALUES (12,'Transactional record inserted');
163 COMMIT;
164 START TRANSACTION;
165 INSERT INTO t5980 VALUES (13,'Rollback this transactional record');
166 ROLLBACK;
167 SELECT * FROM t5980;
168 ALTER TABLE t5980 DROP PRIMARY KEY;
169 ALTER TABLE t5980 ADD COLUMN c VARCHAR(50) DEFAULT NULL;
170 INSERT INTO t5980(a,b,c) VALUES (2,'Duplicate value since primary key has been dropped','third column added');
171 SELECT * FROM t5980;
172
173 --echo #
174 --echo # Show that the system tables have this table in them correctly.
175 --echo #
176 --source suite/innodb/include/show_i_s_tables.inc
177 --source suite/innodb/include/show_i_s_tablespaces.inc
178
179 --echo #
180 --echo # Drop the imported table and show that the system tables are updated.
181 --echo #
182 DROP TABLE t5980;
183 --source suite/innodb/include/show_i_s_tables.inc
184 --source suite/innodb/include/show_i_s_tablespaces.inc
185
186 --echo ### files in MYSQLD_DATADIR/test
187 --list_files $MYSQLD_DATADIR/test
188 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
189 --list_files $MYSQL_TMP_DIR/alt_dir/test
190
191 --echo #
192 --echo # CREATE the table a third time.
193 --echo #
194 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
195 eval CREATE TABLE t5980 (a int KEY, b text) $data_directory_clause;
196 INSERT INTO t5980 VALUES (1, "Create the tablespace a third time");
197 SELECT * FROM t5980;
198 --echo ### files in MYSQLD_DATADIR/test
199 --list_files $MYSQLD_DATADIR/test
200 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
201 --list_files $MYSQL_TMP_DIR/alt_dir/test
202
203 --echo #
204 --echo # Restart the server
205 --echo # This test makes sure that you can still execute the FLUSH TABLES command
206 --echo # after restarting the server and the tablespace can still be found.
207 --echo #
208 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
209 --source include/restart_mysqld.inc
210 SET GLOBAL innodb_file_per_table=ON;
211 --echo ### files in MYSQLD_DATADIR/test
212 --list_files $MYSQLD_DATADIR/test
213 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
214 --list_files $MYSQL_TMP_DIR/alt_dir/test
215 SELECT * FROM t5980;
216 FLUSH TABLES t5980 FOR EXPORT;
217 SELECT * FROM t5980;
218 UNLOCK TABLES;
219
220 --echo #
221 --echo # Restart the server again. This test makes sure that you can
222 --echo # still DISCARD a remote table after restarting the server.
223 --echo #
224 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
225 --source include/restart_mysqld.inc
226 SET GLOBAL innodb_file_per_table=ON;
227 SELECT * FROM t5980;
228 --echo ### files in MYSQLD_DATADIR/test
229 --list_files $MYSQLD_DATADIR/test
230 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
231 --list_files $MYSQL_TMP_DIR/alt_dir/test
232 ALTER TABLE t5980 DISCARD TABLESPACE;
233 --error ER_TABLESPACE_DISCARDED
234 SELECT * FROM t5980;
235 --echo ### files in MYSQLD_DATADIR/test
236 --list_files $MYSQLD_DATADIR/test
237 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
238 --list_files $MYSQL_TMP_DIR/alt_dir/test
239
240 --echo #
241 --echo # Restore the backup of *.ibd and *.cfg files
242 --echo #
243 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
244 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
245 --echo ### files in MYSQLD_DATADIR/test
246 --list_files $MYSQLD_DATADIR/test
247 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
248 --list_files $MYSQL_TMP_DIR/alt_dir/test
249
250 --echo #
251 --echo # Import the tablespace and check it out.
252 --echo #
253 ALTER TABLE t5980 IMPORT TABLESPACE;
254 SELECT * FROM t5980;
255 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
256 SHOW CREATE TABLE t5980;
257 --echo ### files in MYSQLD_DATADIR/test
258 --list_files $MYSQLD_DATADIR/test
259 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
260 --list_files $MYSQL_TMP_DIR/alt_dir/test
261
262 --echo #
263 --echo # DISCARD the tablespace again
264 --echo #
265 ALTER TABLE t5980 DISCARD TABLESPACE;
266 --error ER_TABLESPACE_DISCARDED
267 SELECT * FROM t5980;
268 --echo ### files in MYSQLD_DATADIR/test
269 --list_files $MYSQLD_DATADIR/test
270 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
271 --list_files $MYSQL_TMP_DIR/alt_dir/test
272
273 --echo #
274 --echo # Restart the engine while the tablespace is in the discarded state
275 --echo #
276 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
277 --source include/restart_mysqld.inc
278 SET GLOBAL innodb_file_per_table=ON;
279 --error ER_TABLESPACE_DISCARDED
280 SELECT * FROM t5980;
281 CHECK TABLE t5980;
282
283 --echo #
284 --echo # Relocate this discarded file to the default directory
285 --echo # instead of the remote directory it was discarded from.
286 --echo # Put cfg and idb files into the default directory.
287 --echo # Restart the engine again.
288 --echo # The tablespace is still in the discarded state.
289 --echo #
290 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak $MYSQLD_DATADIR/test/t5980.ibd
291 --copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak $MYSQLD_DATADIR/test/t5980.cfg
292 --echo ### files in MYSQLD_DATADIR/test
293 --list_files $MYSQLD_DATADIR/test
294 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
295 --list_files $MYSQL_TMP_DIR/alt_dir/test
296 --echo # Restarting ...
297 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
298 --source include/restart_mysqld.inc
299 SET GLOBAL innodb_file_per_table=ON;
300 --error ER_TABLESPACE_DISCARDED
301 SELECT * FROM t5980;
302 CHECK TABLE t5980;
303
304 --echo #
305 --echo # Try to import the tablespace. It can only be imported from
306 --echo # the location it was discarded from.
307 --echo # The error message for 1810 (IO_READ_ERROR) refers to a local path
308 --echo # so do not display it.
309 --echo #
310 --disable_result_log
311
312 --error ER_TABLESPACE_MISSING
313 ALTER TABLE t5980 IMPORT TABLESPACE;
314 --enable_result_log
315 --error ER_TABLESPACE_DISCARDED
316 SELECT * FROM t5980;
317 CHECK TABLE t5980;
318
319 --echo #
320 --echo # Restore the ibd and cfg files to the remote directory.
321 --echo # Delete the ibd and cfg files from the default directory.
322 --echo # Import the tablespace and check it out.
323 --echo #
324 --copy_file $MYSQLD_DATADIR/test/t5980.ibd $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
325 --copy_file $MYSQLD_DATADIR/test/t5980.cfg $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
326 --remove_file $MYSQLD_DATADIR/test/t5980.ibd
327 --remove_file $MYSQLD_DATADIR/test/t5980.cfg
328 --echo ### files in MYSQLD_DATADIR/test
329 --list_files $MYSQLD_DATADIR/test
330 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
331 --list_files $MYSQL_TMP_DIR/alt_dir/test
332 ALTER TABLE t5980 IMPORT TABLESPACE;
333 INSERT INTO t5980 VALUES (2, "Insert this record after IMPORT");
334 SELECT * FROM t5980;
335 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
336 SHOW CREATE TABLE t5980;
337
338 --echo #
339 --echo # Show that the system tables have this table in them correctly.
340 --echo #
341 --source suite/innodb/include/show_i_s_tables.inc
342 --source suite/innodb/include/show_i_s_tablespaces.inc
343
344 DROP TABLE t5980;
345 --remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak
346 --remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak
347 --remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak2
348 --remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak2
349
350 --echo #
351 --echo # Create a local and remote tablespaces, discard two and make
352 --echo # the other two missing upon restart, and try some DDL and DML
353 --echo # on these discarded and missing tablespaces.
354 --echo #
355
356 SET GLOBAL innodb_file_per_table=ON;
357 CREATE TABLE t5980a (a int, b text) engine=InnoDB;
358 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
359 eval CREATE TABLE t5980b (a int, b text) engine=InnoDB $data_directory_clause;
360 CREATE TABLE t5980c (a int, b text) engine=InnoDB;
361 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
362 eval CREATE TABLE t5980d (a int, b text) engine=InnoDB $data_directory_clause;
363
364 INSERT INTO t5980a VALUES (1, "Default location, discarded.");
365 INSERT INTO t5980b VALUES (1, "Remote location, discarded");
366 INSERT INTO t5980c VALUES (1, "Default location, missing");
367 INSERT INTO t5980d VALUES (1, "Remote location, missing");
368
369 SELECT * FROM t5980a;
370 SELECT * FROM t5980b;
371 SELECT * FROM t5980c;
372 SELECT * FROM t5980d;
373
374 SHOW CREATE TABLE t5980a;
375 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
376 SHOW CREATE TABLE t5980b;
377 SHOW CREATE TABLE t5980c;
378 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
379 SHOW CREATE TABLE t5980d;
380
381 --source suite/innodb/include/show_i_s_tables.inc
382 --source suite/innodb/include/show_i_s_tablespaces.inc
383
384 --echo ### files in MYSQLD_DATADIR/test
385 --list_files $MYSQLD_DATADIR/test
386 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
387 --list_files $MYSQL_TMP_DIR/alt_dir/test
388
389 --echo #
390 --echo # Shutdown the server, remove two tablespaces, restart server.
391 --echo #
392 --source include/shutdown_mysqld.inc
393 --remove_file $MYSQLD_DATADIR/test/t5980c.ibd
394 --remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd
395 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
396 --source include/start_mysqld.inc
397
398 FLUSH TABLES t5980a, t5980b FOR EXPORT;
399 UNLOCK TABLES;
400
401 ALTER TABLE t5980a DISCARD TABLESPACE;
402 ALTER TABLE t5980b DISCARD TABLESPACE;
403
404 --echo ### files in MYSQLD_DATADIR/test
405 --list_files $MYSQLD_DATADIR/test
406 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
407 --list_files $MYSQL_TMP_DIR/alt_dir/test
408
409 --error ER_TABLESPACE_DISCARDED
410 SELECT * FROM t5980a;
411 --error ER_TABLESPACE_DISCARDED
412 SELECT * FROM t5980b;
413 --error ER_TABLESPACE_MISSING
414 SELECT * FROM t5980c;
415 --error ER_TABLESPACE_MISSING
416 SELECT * FROM t5980d;
417
418 SHOW CREATE TABLE t5980a;
419 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
420 SHOW CREATE TABLE t5980b;
421 --error ER_TABLESPACE_MISSING
422 SHOW CREATE TABLE t5980c;
423 --error ER_TABLESPACE_MISSING
424 SHOW CREATE TABLE t5980d;
425
426 --source suite/innodb/include/show_i_s_tables.inc
427 # Mask the table name because the order of these 4 warning
428 # messages is not predictable.
429 LET $EXTRA_I_S_FILES_REGEX=/t5980[a-d]+/t5980#/;
430 --source suite/innodb/include/show_i_s_tablespaces.inc
431
432 --echo ### files in MYSQLD_DATADIR/test
433 --list_files $MYSQLD_DATADIR/test
434 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
435 --list_files $MYSQL_TMP_DIR/alt_dir/test
436
437 --echo #
438 --echo # Discarded and missing tablespaces cannot be TRUNCATED
439 --echo #
440
441 --error ER_TABLESPACE_DISCARDED
442 TRUNCATE TABLE t5980a;
443 call mtr.add_suppression("Table .* does not exist .* though MySQL .* rename");
444 --error ER_TABLESPACE_DISCARDED
445 TRUNCATE TABLE t5980b;
446 --error ER_TABLESPACE_MISSING
447 TRUNCATE TABLE t5980c;
448 --error ER_TABLESPACE_MISSING
449 TRUNCATE TABLE t5980d;
450
451 --echo #
452 --echo # Discarded tablespaces can be RENAMED but they remain discarded
453 --echo #
454
455 RENAME TABLE t5980a TO t5980aa;
456 RENAME TABLE t5980b TO t5980bb;
457
458 --echo #
459 --echo # Missing tablespaces cannot be RENAMED
460 --echo #
461
462 --error ER_ERROR_ON_RENAME
463 RENAME TABLE t5980c TO t5980cc;
464 --error ER_ERROR_ON_RENAME
465 RENAME TABLE t5980d TO t5980dd;
466
467 --error ER_NO_SUCH_TABLE
468 SELECT * FROM t5980a;
469 --error ER_NO_SUCH_TABLE
470 SELECT * FROM t5980b;
471 --error ER_TABLESPACE_DISCARDED
472 SELECT * FROM t5980aa;
473 --error ER_TABLESPACE_DISCARDED
474 SELECT * FROM t5980bb;
475 --error ER_TABLESPACE_MISSING
476 SELECT * FROM t5980c;
477 --error ER_TABLESPACE_MISSING
478 SELECT * FROM t5980d;
479
480 SHOW CREATE TABLE t5980aa;
481 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
482 SHOW CREATE TABLE t5980bb;
483 --error ER_TABLESPACE_MISSING
484 SHOW CREATE TABLE t5980c;
485 --error ER_TABLESPACE_MISSING
486 SHOW CREATE TABLE t5980d;
487
488 --source suite/innodb/include/show_i_s_tables.inc
489 --source suite/innodb/include/show_i_s_tablespaces.inc
490
491 --echo #
492 --echo # Discarded tablespaces cannot be ALTERED with ALGORITHM=COPY.
493 --echo #
494
495 --error ER_TABLESPACE_DISCARDED
496 ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=COPY;
497 --error ER_TABLESPACE_DISCARDED
498 ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=COPY;
499 --error ER_TABLESPACE_DISCARDED
500 ALTER TABLE t5980aa CHANGE a c INT, ALGORITHM=COPY;
501 --error ER_TABLESPACE_DISCARDED
502 ALTER TABLE t5980bb CHANGE a c INT, ALGORITHM=COPY;
503
504 --echo #
505 --echo # Discarded tablespaces can be ALTERED with ALGORITHM=INPLACE
506 --echo # only if a rebuild is not required.
507 --echo #
508
509 --error ER_TABLESPACE_DISCARDED
510 ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
511 --error ER_TABLESPACE_DISCARDED
512 ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
513
514 ALTER TABLE t5980aa RENAME t5980aaa, ALGORITHM=INPLACE;
515 ALTER TABLE t5980aaa RENAME t5980aa, ALGORITHM=INPLACE;
516 ALTER TABLE t5980bb RENAME t5980bbb, ALGORITHM=INPLACE;
517 ALTER TABLE t5980bbb RENAME t5980bb, ALGORITHM=INPLACE;
518
519 --echo ### files in MYSQLD_DATADIR/test
520 --list_files $MYSQLD_DATADIR/test
521 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
522 --list_files $MYSQL_TMP_DIR/alt_dir/test
523
524 --echo #
525 --echo # Missing tablespaces cannot be ALTERED.
526 --echo #
527
528 --error ER_TABLESPACE_MISSING
529 ALTER TABLE t5980c ADD PRIMARY KEY(a);
530 --error ER_TABLESPACE_MISSING
531 ALTER TABLE t5980d ADD PRIMARY KEY(a);
532
533 --error ER_TABLESPACE_DISCARDED
534 SELECT * FROM t5980aa;
535 --error ER_TABLESPACE_DISCARDED
536 SELECT * FROM t5980bb;
537
538 SHOW CREATE TABLE t5980aa;
539 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
540 SHOW CREATE TABLE t5980bb;
541
542 --source suite/innodb/include/show_i_s_tables.inc
543 --source suite/innodb/include/show_i_s_tablespaces.inc
544
545 --echo ### files in MYSQLD_DATADIR/test
546 --list_files $MYSQLD_DATADIR/test
547 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
548 --list_files $MYSQL_TMP_DIR/alt_dir/test
549
550 --echo #
551 --echo # Restart the server to check if the discarded flag is persistent
552 --echo #
553 --source include/shutdown_mysqld.inc
554 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
555 --source include/start_mysqld.inc
556
557 --echo #
558 --echo # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
559 --echo #
560
561 --error ER_TABLESPACE_DISCARDED
562 INSERT INTO t5980aa VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE");
563 --error ER_TABLESPACE_DISCARDED
564 INSERT INTO t5980bb VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE");
565
566 --error ER_TABLESPACE_DISCARDED
567 SELECT * FROM t5980aa;
568 --error ER_TABLESPACE_DISCARDED
569 SELECT * FROM t5980bb;
570
571 RENAME TABLE t5980aa TO t5980a;
572 RENAME TABLE t5980bb TO t5980b;
573
574 SHOW CREATE TABLE t5980a;
575 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
576 SHOW CREATE TABLE t5980b;
577
578 --source suite/innodb/include/show_i_s_tables.inc
579 --source suite/innodb/include/show_i_s_tablespaces.inc
580 LET $EXTRA_I_S_FILES_REGEX=;
581
582 --echo ### files in MYSQLD_DATADIR/test
583 --list_files $MYSQLD_DATADIR/test
584 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
585 --list_files $MYSQL_TMP_DIR/alt_dir/test
586
587 --echo #
588 --echo # Discard tablespaces again and try another ALTER TABLE ROW_FORMAT.
589 --echo #
590
591 ALTER TABLE t5980a DISCARD TABLESPACE;
592 ALTER TABLE t5980b DISCARD TABLESPACE;
593 --error ER_TABLESPACE_DISCARDED
594 SELECT * FROM t5980a;
595 --error ER_TABLESPACE_DISCARDED
596 SELECT * FROM t5980b;
597
598 --echo #
599 --echo # ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace.
600 --echo #
601
602 --error ER_TABLESPACE_DISCARDED
603 ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
604 --error ER_TABLESPACE_DISCARDED
605 ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
606
607 --echo #
608 --echo # ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace.
609 --echo # only if a rebuild is not required.
610 --echo #
611
612 --error ER_TABLESPACE_DISCARDED
613 ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
614 --error ER_TABLESPACE_DISCARDED
615 ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
616
617 --echo #
618 --echo # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
619 --echo #
620
621 --error ER_TABLESPACE_DISCARDED
622 INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
623 --error ER_TABLESPACE_DISCARDED
624 INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
625 --error ER_TABLESPACE_DISCARDED
626 SELECT * FROM t5980a;
627 --error ER_TABLESPACE_DISCARDED
628 SELECT * FROM t5980b;
629 --echo ### files in MYSQLD_DATADIR/test
630 --list_files $MYSQLD_DATADIR/test
631 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
632 --list_files $MYSQL_TMP_DIR/alt_dir/test
633
634 --echo #
635 --echo # Discard tablespaces again and try ALTER TABLE ADD COLUMN.
636 --echo #
637
638 ALTER TABLE t5980a DISCARD TABLESPACE;
639 ALTER TABLE t5980b DISCARD TABLESPACE;
640 --error ER_TABLESPACE_DISCARDED
641 SELECT * FROM t5980a;
642 --error ER_TABLESPACE_DISCARDED
643 SELECT * FROM t5980b;
644
645 --echo #
646 --echo # ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace.
647 --echo #
648
649 --error ER_TABLESPACE_DISCARDED
650 ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=COPY;
651 --error ER_TABLESPACE_DISCARDED
652 ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=COPY;
653
654 --echo #
655 --echo # ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace.
656 --echo # only if a rebuild is not required.
657 --echo #
658
659 --error ER_TABLESPACE_DISCARDED
660 ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=INPLACE;
661 --error ER_TABLESPACE_DISCARDED
662 ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=INPLACE;
663
664 --echo #
665 --echo # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
666 --echo #
667
668 --error ER_TABLESPACE_DISCARDED
669 DELETE FROM t5980a;
670 --error ER_TABLESPACE_DISCARDED
671 UPDATE t5980a SET b="Tablespace is DISCARDED";
672 --error ER_TABLESPACE_DISCARDED
673 INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
674 --error ER_TABLESPACE_DISCARDED
675 INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
676 --error ER_TABLESPACE_DISCARDED
677 SELECT * FROM t5980a;
678 --error ER_TABLESPACE_DISCARDED
679 SELECT * FROM t5980b;
680 --echo ### files in MYSQLD_DATADIR/test
681 --list_files $MYSQLD_DATADIR/test
682 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
683 --list_files $MYSQL_TMP_DIR/alt_dir/test
684
685 DROP TABLE t5980a;
686 DROP TABLE t5980b;
687 DROP TABLE t5980c;
688 DROP TABLE t5980d;
689
690 --source suite/innodb/include/show_i_s_tables.inc
691 --source suite/innodb/include/show_i_s_tablespaces.inc
692
693 --echo ### files in MYSQLD_DATADIR/test
694 --list_files $MYSQLD_DATADIR/test
695 --echo ### files in MYSQL_TMP_DIR/alt_dir/test
696 --list_files $MYSQL_TMP_DIR/alt_dir/test
697
698 --echo #
699 --echo # Cleanup
700 --echo #
701
702 --rmdir $MYSQL_TMP_DIR/alt_dir/test
703 --rmdir $MYSQL_TMP_DIR/alt_dir
704
705 -- disable_query_log
706 eval set global innodb_file_per_table=$innodb_file_per_table_orig;
707
708 # These warnings are expected in the log
709 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot delete tablespace .+ because it is not found in the tablespace memory cache");
710 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot open datafile for read-only:");
711 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Could not find a valid tablespace file for");
712 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Failed to find tablespace for table .* in the cache. Attempting to load the tablespace with space id");
713 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.");
714 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* In file .*, tablespace id and flags are .*, but in the InnoDB data dictionary they are");
715 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Operating system error number 2 in a file operation.");
716 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Table .* does not have an \.ibd file in the database directory.");
717 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Table .* does not exist in the InnoDB internal data dictionary though MySQL is trying to rename the table");
718 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* The error means the system cannot find the path specified.");
719 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* The table .* doesn't have a corresponding tablespace, it was discarded.");
720 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Trying to import a tablespace, but could not open the tablespace file");
721 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Cannot calculate statistics for table .* because the \.ibd file is missing");
722 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Cannot delete tablespace .+ in DISCARD TABLESPACE. Tablespace not found");
723 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Ignoring tablespace .* because it could not be opened");
724 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Tablespace for table .* is set as discarded");
725 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Unable to open tablespace .* \\(flags=.*, filename=");
726 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Trying to access missing tablespace .*");
727 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Tablespace .*, name '.*', file '.*' is missing!");
728 -- enable_query_log