Debian Codegraph @

mysql-test/suite/innodb/r/choose_tbsp_location-discard.result

raw
1 #
2 # This test shows DISCARD/IMPORT of a remote tablespace.
3 #
4 SET default_storage_engine=InnoDB;
5 SET GLOBAL innodb_file_per_table=ON;
6 DROP TABLE IF EXISTS t5980;
7 #
8 # CREATE TABLE ... DATA DIRECTORY
9 # combined with WL#5522 - Transportable Tablespace
10 # Create the tablespace in MYSQL_TMP_DIR/alt_dir
11 # InnoDB will create the sub-directories if needed.
12 # Test that DISCARD and IMPORT work correctly.
13 #
14 CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
15 INSERT INTO t5980 VALUES (1, "Create the tablespace");
16 SELECT * FROM t5980;
17 a b
18 1 Create the tablespace
19 ### files in MYSQLD_DATADIR/test
20 ### files in MYSQL_TMP_DIR/alt_dir/test
21 t5980.ibd
22 #
23 # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results.
24 #
25 SHOW CREATE TABLE t5980;
26 Table Create Table
27 t5980 CREATE TABLE `t5980` (
28 `a` int NOT NULL,
29 `b` text,
30 PRIMARY KEY (`a`)
31 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
32 #
33 # Backup the cfg and ibd files.
34 #
35 FLUSH TABLES t5980 FOR EXPORT;
36 SELECT * FROM t5980;
37 a b
38 1 Create the tablespace
39 UNLOCK TABLES;
40 ### files in MYSQLD_DATADIR/test
41 ### files in MYSQL_TMP_DIR/alt_dir/test
42 t5980.cfg.bak
43 t5980.ibd
44 t5980.ibd.bak
45 #
46 # Do some DDL and DML.
47 #
48 INSERT INTO t5980 VALUES (2,'Remote table has been FLUSHed and UNLOCKed');
49 START TRANSACTION;
50 INSERT INTO t5980 VALUES (12,'Transactional record inserted');
51 COMMIT;
52 START TRANSACTION;
53 INSERT INTO t5980 VALUES (13,'Rollback this transactional record');
54 ROLLBACK;
55 SELECT COUNT(*) FROM t5980;
56 COUNT(*)
57 3
58 SELECT * FROM t5980;
59 a b
60 1 Create the tablespace
61 2 Remote table has been FLUSHed and UNLOCKed
62 12 Transactional record inserted
63 ALTER TABLE t5980 DROP PRIMARY KEY;
64 ALTER TABLE t5980 ADD COLUMN c VARCHAR(50) DEFAULT NULL;
65 INSERT INTO t5980(a,b,c) VALUES (2,'Duplicate value since primary key has been dropped','third column added');
66 SELECT * FROM t5980;
67 a b c
68 1 Create the tablespace NULL
69 2 Remote table has been FLUSHed and UNLOCKed NULL
70 12 Transactional record inserted NULL
71 2 Duplicate value since primary key has been dropped third column added
72 #
73 # Make a second backup of the cfg and ibd files.
74 #
75 FLUSH TABLES t5980 FOR EXPORT;
76 SELECT * FROM t5980;
77 a b c
78 1 Create the tablespace NULL
79 2 Remote table has been FLUSHed and UNLOCKed NULL
80 12 Transactional record inserted NULL
81 2 Duplicate value since primary key has been dropped third column added
82 UNLOCK TABLES;
83 ### files in MYSQLD_DATADIR/test
84 ### files in MYSQL_TMP_DIR/alt_dir/test
85 t5980.cfg.bak
86 t5980.cfg.bak2
87 t5980.ibd
88 t5980.ibd.bak
89 t5980.ibd.bak2
90 #
91 # DROP the table and make sure all files except the backups are gone.
92 #
93 DROP TABLE t5980;
94 ### files in MYSQLD_DATADIR/test
95 ### files in MYSQL_TMP_DIR/alt_dir/test
96 t5980.cfg.bak
97 t5980.cfg.bak2
98 t5980.ibd.bak
99 t5980.ibd.bak2
100 #
101 # CREATE the table again.
102 #
103 CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
104 INSERT INTO t5980 VALUES (1, "Create the tablespace a second time");
105 SELECT * FROM t5980;
106 a b
107 1 Create the tablespace a second time
108 #
109 # DISCARD existing tablespace so backed-up .ibd which can be imported/restored
110 #
111 ALTER TABLE t5980 DISCARD TABLESPACE;
112 SELECT * FROM t5980;
113 ERROR HY000: Tablespace has been discarded for table 't5980'
114 ### files in MYSQLD_DATADIR/test
115 ### files in MYSQL_TMP_DIR/alt_dir/test
116 t5980.cfg.bak
117 t5980.cfg.bak2
118 t5980.ibd.bak
119 t5980.ibd.bak2
120 #
121 # Restore the second backup of cfg and ibd files.
122 #
123 "### files in MYSQL_TMP_DIR/alt_dir/test"
124 t5980.cfg
125 t5980.cfg.bak
126 t5980.cfg.bak2
127 t5980.ibd
128 t5980.ibd.bak
129 t5980.ibd.bak2
130 #
131 # Try to Import the second backup. These backups have extra DDL and
132 # do not match the current frm file.
133 #
134 ALTER TABLE t5980 IMPORT TABLESPACE;
135 ERROR HY000: Schema mismatch (Number of columns don't match, table has 5 columns but the tablespace meta-data file has 6 columns)
136 CHECK TABLE t5980;
137 Table Op Msg_type Msg_text
138 test.t5980 check Error Tablespace has been discarded for table 't5980'
139 test.t5980 check error Corrupt
140 ### files in MYSQL_TMP_DIR/alt_dir/test
141 t5980.cfg.bak
142 t5980.cfg.bak2
143 t5980.ibd.bak
144 t5980.ibd.bak2
145 #
146 # Restore the first backup of cfg and ibd files.
147 #
148 ### files in MYSQL_TMP_DIR/alt_dir/test
149 t5980.cfg
150 t5980.cfg.bak
151 t5980.cfg.bak2
152 t5980.ibd
153 t5980.ibd.bak
154 t5980.ibd.bak2
155 #
156 # Import the tablespace and do some DDL and DML.
157 #
158 ALTER TABLE t5980 IMPORT TABLESPACE;
159 ### files in MYSQLD_DATADIR/test
160 ### files in MYSQL_TMP_DIR/alt_dir/test
161 t5980.cfg
162 t5980.cfg.bak
163 t5980.cfg.bak2
164 t5980.ibd
165 t5980.ibd.bak
166 t5980.ibd.bak2
167 CHECK TABLE t5980;
168 Table Op Msg_type Msg_text
169 test.t5980 check status OK
170 SELECT COUNT(*) FROM t5980;
171 COUNT(*)
172 1
173 SELECT * FROM t5980;
174 a b
175 1 Create the tablespace
176 INSERT INTO t5980 VALUES (2,'Inserted record after IMPORT');
177 SELECT * FROM t5980;
178 a b
179 1 Create the tablespace
180 2 Inserted record after IMPORT
181 START TRANSACTION;
182 INSERT INTO t5980 VALUES (12,'Transactional record inserted');
183 COMMIT;
184 START TRANSACTION;
185 INSERT INTO t5980 VALUES (13,'Rollback this transactional record');
186 ROLLBACK;
187 SELECT * FROM t5980;
188 a b
189 1 Create the tablespace
190 2 Inserted record after IMPORT
191 12 Transactional record inserted
192 ALTER TABLE t5980 DROP PRIMARY KEY;
193 ALTER TABLE t5980 ADD COLUMN c VARCHAR(50) DEFAULT NULL;
194 INSERT INTO t5980(a,b,c) VALUES (2,'Duplicate value since primary key has been dropped','third column added');
195 SELECT * FROM t5980;
196 a b c
197 1 Create the tablespace NULL
198 2 Inserted record after IMPORT NULL
199 12 Transactional record inserted NULL
200 2 Duplicate value since primary key has been dropped third column added
201 #
202 # Show that the system tables have this table in them correctly.
203 #
204 === information_schema.innodb_tables and innodb_tablespaces ===
205 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
206 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
207 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
208 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
209 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
210 test/t5980 test/t5980 97 6 Dynamic 0 Single
211 === information_schema.innodb_tablespaces and innodb_datafiles ===
212 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
213 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
214 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
215 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
216 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
217 test/t5980 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
218 === information_schema.files ===
219 Space_Name File_Type Engine Status Tablespace_Name Path
220 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
221 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
222 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
223 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
224 test/t5980 TABLESPACE InnoDB NORMAL test/t5980 MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
225 #
226 # Drop the imported table and show that the system tables are updated.
227 #
228 DROP TABLE t5980;
229 === information_schema.innodb_tables and innodb_tablespaces ===
230 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
231 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
232 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
233 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
234 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
235 === information_schema.innodb_tablespaces and innodb_datafiles ===
236 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
237 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
238 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
239 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
240 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
241 === information_schema.files ===
242 Space_Name File_Type Engine Status Tablespace_Name Path
243 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
244 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
245 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
246 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
247 ### files in MYSQLD_DATADIR/test
248 ### files in MYSQL_TMP_DIR/alt_dir/test
249 t5980.cfg.bak
250 t5980.cfg.bak2
251 t5980.ibd.bak
252 t5980.ibd.bak2
253 #
254 # CREATE the table a third time.
255 #
256 CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
257 INSERT INTO t5980 VALUES (1, "Create the tablespace a third time");
258 SELECT * FROM t5980;
259 a b
260 1 Create the tablespace a third time
261 ### files in MYSQLD_DATADIR/test
262 ### files in MYSQL_TMP_DIR/alt_dir/test
263 t5980.cfg.bak
264 t5980.cfg.bak2
265 t5980.ibd
266 t5980.ibd.bak
267 t5980.ibd.bak2
268 #
269 # Restart the server
270 # This test makes sure that you can still execute the FLUSH TABLES command
271 # after restarting the server and the tablespace can still be found.
272 #
273 # restart
274 SET GLOBAL innodb_file_per_table=ON;
275 ### files in MYSQLD_DATADIR/test
276 ### files in MYSQL_TMP_DIR/alt_dir/test
277 t5980.cfg.bak
278 t5980.cfg.bak2
279 t5980.ibd
280 t5980.ibd.bak
281 t5980.ibd.bak2
282 SELECT * FROM t5980;
283 a b
284 1 Create the tablespace a third time
285 FLUSH TABLES t5980 FOR EXPORT;
286 SELECT * FROM t5980;
287 a b
288 1 Create the tablespace a third time
289 UNLOCK TABLES;
290 #
291 # Restart the server again. This test makes sure that you can
292 # still DISCARD a remote table after restarting the server.
293 #
294 # restart
295 SET GLOBAL innodb_file_per_table=ON;
296 SELECT * FROM t5980;
297 a b
298 1 Create the tablespace a third time
299 ### files in MYSQLD_DATADIR/test
300 ### files in MYSQL_TMP_DIR/alt_dir/test
301 t5980.cfg.bak
302 t5980.cfg.bak2
303 t5980.ibd
304 t5980.ibd.bak
305 t5980.ibd.bak2
306 ALTER TABLE t5980 DISCARD TABLESPACE;
307 SELECT * FROM t5980;
308 ERROR HY000: Tablespace has been discarded for table 't5980'
309 ### files in MYSQLD_DATADIR/test
310 ### files in MYSQL_TMP_DIR/alt_dir/test
311 t5980.cfg.bak
312 t5980.cfg.bak2
313 t5980.ibd.bak
314 t5980.ibd.bak2
315 #
316 # Restore the backup of *.ibd and *.cfg files
317 #
318 ### files in MYSQLD_DATADIR/test
319 ### files in MYSQL_TMP_DIR/alt_dir/test
320 t5980.cfg
321 t5980.cfg.bak
322 t5980.cfg.bak2
323 t5980.ibd
324 t5980.ibd.bak
325 t5980.ibd.bak2
326 #
327 # Import the tablespace and check it out.
328 #
329 ALTER TABLE t5980 IMPORT TABLESPACE;
330 SELECT * FROM t5980;
331 a b
332 1 Create the tablespace
333 SHOW CREATE TABLE t5980;
334 Table Create Table
335 t5980 CREATE TABLE `t5980` (
336 `a` int NOT NULL,
337 `b` text,
338 PRIMARY KEY (`a`)
339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
340 ### files in MYSQLD_DATADIR/test
341 ### files in MYSQL_TMP_DIR/alt_dir/test
342 t5980.cfg
343 t5980.cfg.bak
344 t5980.cfg.bak2
345 t5980.ibd
346 t5980.ibd.bak
347 t5980.ibd.bak2
348 #
349 # DISCARD the tablespace again
350 #
351 ALTER TABLE t5980 DISCARD TABLESPACE;
352 SELECT * FROM t5980;
353 ERROR HY000: Tablespace has been discarded for table 't5980'
354 ### files in MYSQLD_DATADIR/test
355 ### files in MYSQL_TMP_DIR/alt_dir/test
356 t5980.cfg.bak
357 t5980.cfg.bak2
358 t5980.ibd.bak
359 t5980.ibd.bak2
360 #
361 # Restart the engine while the tablespace is in the discarded state
362 #
363 # restart
364 SET GLOBAL innodb_file_per_table=ON;
365 SELECT * FROM t5980;
366 ERROR HY000: Tablespace has been discarded for table 't5980'
367 CHECK TABLE t5980;
368 Table Op Msg_type Msg_text
369 test.t5980 check Error Tablespace has been discarded for table 't5980'
370 test.t5980 check error Corrupt
371 #
372 # Relocate this discarded file to the default directory
373 # instead of the remote directory it was discarded from.
374 # Put cfg and idb files into the default directory.
375 # Restart the engine again.
376 # The tablespace is still in the discarded state.
377 #
378 ### files in MYSQLD_DATADIR/test
379 t5980.cfg
380 t5980.ibd
381 ### files in MYSQL_TMP_DIR/alt_dir/test
382 t5980.cfg.bak
383 t5980.cfg.bak2
384 t5980.ibd.bak
385 t5980.ibd.bak2
386 # Restarting ...
387 # restart
388 SET GLOBAL innodb_file_per_table=ON;
389 SELECT * FROM t5980;
390 ERROR HY000: Tablespace has been discarded for table 't5980'
391 CHECK TABLE t5980;
392 Table Op Msg_type Msg_text
393 test.t5980 check Error Tablespace has been discarded for table 't5980'
394 test.t5980 check error Corrupt
395 #
396 # Try to import the tablespace. It can only be imported from
397 # the location it was discarded from.
398 # The error message for 1810 (IO_READ_ERROR) refers to a local path
399 # so do not display it.
400 #
401 ALTER TABLE t5980 IMPORT TABLESPACE;
402 SELECT * FROM t5980;
403 ERROR HY000: Tablespace has been discarded for table 't5980'
404 CHECK TABLE t5980;
405 Table Op Msg_type Msg_text
406 test.t5980 check Error Tablespace has been discarded for table 't5980'
407 test.t5980 check error Corrupt
408 #
409 # Restore the ibd and cfg files to the remote directory.
410 # Delete the ibd and cfg files from the default directory.
411 # Import the tablespace and check it out.
412 #
413 ### files in MYSQLD_DATADIR/test
414 ### files in MYSQL_TMP_DIR/alt_dir/test
415 t5980.cfg
416 t5980.cfg.bak
417 t5980.cfg.bak2
418 t5980.ibd
419 t5980.ibd.bak
420 t5980.ibd.bak2
421 ALTER TABLE t5980 IMPORT TABLESPACE;
422 INSERT INTO t5980 VALUES (2, "Insert this record after IMPORT");
423 SELECT * FROM t5980;
424 a b
425 1 Create the tablespace
426 2 Insert this record after IMPORT
427 SHOW CREATE TABLE t5980;
428 Table Create Table
429 t5980 CREATE TABLE `t5980` (
430 `a` int NOT NULL,
431 `b` text,
432 PRIMARY KEY (`a`)
433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
434 #
435 # Show that the system tables have this table in them correctly.
436 #
437 === information_schema.innodb_tables and innodb_tablespaces ===
438 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
439 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
440 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
441 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
442 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
443 test/t5980 test/t5980 97 5 Dynamic 0 Single
444 === information_schema.innodb_tablespaces and innodb_datafiles ===
445 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
446 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
447 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
448 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
449 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
450 test/t5980 Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
451 === information_schema.files ===
452 Space_Name File_Type Engine Status Tablespace_Name Path
453 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
454 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
455 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
456 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
457 test/t5980 TABLESPACE InnoDB NORMAL test/t5980 MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
458 DROP TABLE t5980;
459 #
460 # Create a local and remote tablespaces, discard two and make
461 # the other two missing upon restart, and try some DDL and DML
462 # on these discarded and missing tablespaces.
463 #
464 SET GLOBAL innodb_file_per_table=ON;
465 CREATE TABLE t5980a (a int, b text) engine=InnoDB;
466 CREATE TABLE t5980b (a int, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
467 CREATE TABLE t5980c (a int, b text) engine=InnoDB;
468 CREATE TABLE t5980d (a int, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
469 INSERT INTO t5980a VALUES (1, "Default location, discarded.");
470 INSERT INTO t5980b VALUES (1, "Remote location, discarded");
471 INSERT INTO t5980c VALUES (1, "Default location, missing");
472 INSERT INTO t5980d VALUES (1, "Remote location, missing");
473 SELECT * FROM t5980a;
474 a b
475 1 Default location, discarded.
476 SELECT * FROM t5980b;
477 a b
478 1 Remote location, discarded
479 SELECT * FROM t5980c;
480 a b
481 1 Default location, missing
482 SELECT * FROM t5980d;
483 a b
484 1 Remote location, missing
485 SHOW CREATE TABLE t5980a;
486 Table Create Table
487 t5980a CREATE TABLE `t5980a` (
488 `a` int DEFAULT NULL,
489 `b` text
490 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
491 SHOW CREATE TABLE t5980b;
492 Table Create Table
493 t5980b CREATE TABLE `t5980b` (
494 `a` int DEFAULT NULL,
495 `b` text
496 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
497 SHOW CREATE TABLE t5980c;
498 Table Create Table
499 t5980c CREATE TABLE `t5980c` (
500 `a` int DEFAULT NULL,
501 `b` text
502 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
503 SHOW CREATE TABLE t5980d;
504 Table Create Table
505 t5980d CREATE TABLE `t5980d` (
506 `a` int DEFAULT NULL,
507 `b` text
508 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
509 === information_schema.innodb_tables and innodb_tablespaces ===
510 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
511 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
512 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
513 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
514 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
515 test/t5980a test/t5980a 33 5 Dynamic 0 Single
516 test/t5980b test/t5980b 97 5 Dynamic 0 Single
517 test/t5980c test/t5980c 33 5 Dynamic 0 Single
518 test/t5980d test/t5980d 97 5 Dynamic 0 Single
519 === information_schema.innodb_tablespaces and innodb_datafiles ===
520 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
521 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
522 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
523 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
524 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
525 test/t5980a Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t5980a.ibd
526 test/t5980b Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd
527 test/t5980c Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t5980c.ibd
528 test/t5980d Single DEFAULT 0 1 1 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd
529 === information_schema.files ===
530 Space_Name File_Type Engine Status Tablespace_Name Path
531 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
532 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
533 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
534 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
535 test/t5980a TABLESPACE InnoDB NORMAL test/t5980a MYSQLD_DATADIR/test/t5980a.ibd
536 test/t5980b TABLESPACE InnoDB NORMAL test/t5980b MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd
537 test/t5980c TABLESPACE InnoDB NORMAL test/t5980c MYSQLD_DATADIR/test/t5980c.ibd
538 test/t5980d TABLESPACE InnoDB NORMAL test/t5980d MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd
539 ### files in MYSQLD_DATADIR/test
540 t5980a.ibd
541 t5980c.ibd
542 ### files in MYSQL_TMP_DIR/alt_dir/test
543 t5980b.ibd
544 t5980d.ibd
545 #
546 # Shutdown the server, remove two tablespaces, restart server.
547 #
548 # restart
549 FLUSH TABLES t5980a, t5980b FOR EXPORT;
550 UNLOCK TABLES;
551 ALTER TABLE t5980a DISCARD TABLESPACE;
552 ALTER TABLE t5980b DISCARD TABLESPACE;
553 ### files in MYSQLD_DATADIR/test
554 ### files in MYSQL_TMP_DIR/alt_dir/test
555 SELECT * FROM t5980a;
556 ERROR HY000: Tablespace has been discarded for table 't5980a'
557 SELECT * FROM t5980b;
558 ERROR HY000: Tablespace has been discarded for table 't5980b'
559 SELECT * FROM t5980c;
560 ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
561 SELECT * FROM t5980d;
562 ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
563 SHOW CREATE TABLE t5980a;
564 Table Create Table
565 t5980a CREATE TABLE `t5980a` (
566 `a` int DEFAULT NULL,
567 `b` text
568 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
569 SHOW CREATE TABLE t5980b;
570 Table Create Table
571 t5980b CREATE TABLE `t5980b` (
572 `a` int DEFAULT NULL,
573 `b` text
574 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
575 SHOW CREATE TABLE t5980c;
576 ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
577 SHOW CREATE TABLE t5980d;
578 ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
579 === information_schema.innodb_tables and innodb_tablespaces ===
580 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
581 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
582 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
583 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
584 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
585 test/t5980a test/t5980a 33 5 Dynamic 0 Single
586 test/t5980b test/t5980b 97 5 Dynamic 0 Single
587 test/t5980c test/t5980c 33 5 Dynamic 0 Single
588 test/t5980d test/t5980d 97 5 Dynamic 0 Single
589 === information_schema.innodb_tablespaces and innodb_datafiles ===
590 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
591 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
592 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
593 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
594 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
595 test/t5980a Single DEFAULT 0 0 0 Dynamic MYSQLD_DATADIR/test/t5980a.ibd
596 test/t5980b Single DEFAULT 0 0 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd
597 test/t5980c Single DEFAULT 0 0 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd
598 test/t5980d Single DEFAULT 0 0 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd
599 === information_schema.files ===
600 Space_Name File_Type Engine Status Tablespace_Name Path
601 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
602 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
603 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
604 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
605 ### files in MYSQLD_DATADIR/test
606 ### files in MYSQL_TMP_DIR/alt_dir/test
607 #
608 # Discarded and missing tablespaces cannot be TRUNCATED
609 #
610 TRUNCATE TABLE t5980a;
611 ERROR HY000: Tablespace has been discarded for table 'test/t5980a'
612 call mtr.add_suppression("Table .* does not exist .* though MySQL .* rename");
613 TRUNCATE TABLE t5980b;
614 ERROR HY000: Tablespace has been discarded for table 'test/t5980b'
615 TRUNCATE TABLE t5980c;
616 ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
617 TRUNCATE TABLE t5980d;
618 ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
619 #
620 # Discarded tablespaces can be RENAMED but they remain discarded
621 #
622 RENAME TABLE t5980a TO t5980aa;
623 RENAME TABLE t5980b TO t5980bb;
624 #
625 # Missing tablespaces cannot be RENAMED
626 #
627 RENAME TABLE t5980c TO t5980cc;
628 ERROR HY000: Error on rename of './test/t5980c' to './test/t5980cc' (errno: 155 - The table does not exist in engine)
629 RENAME TABLE t5980d TO t5980dd;
630 ERROR HY000: Error on rename of './test/t5980d' to './test/t5980dd' (errno: 155 - The table does not exist in engine)
631 SELECT * FROM t5980a;
632 ERROR 42S02: Table 'test.t5980a' doesn't exist
633 SELECT * FROM t5980b;
634 ERROR 42S02: Table 'test.t5980b' doesn't exist
635 SELECT * FROM t5980aa;
636 ERROR HY000: Tablespace has been discarded for table 't5980aa'
637 SELECT * FROM t5980bb;
638 ERROR HY000: Tablespace has been discarded for table 't5980bb'
639 SELECT * FROM t5980c;
640 ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
641 SELECT * FROM t5980d;
642 ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
643 SHOW CREATE TABLE t5980aa;
644 Table Create Table
645 t5980aa CREATE TABLE `t5980aa` (
646 `a` int DEFAULT NULL,
647 `b` text
648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
649 SHOW CREATE TABLE t5980bb;
650 Table Create Table
651 t5980bb CREATE TABLE `t5980bb` (
652 `a` int DEFAULT NULL,
653 `b` text
654 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
655 SHOW CREATE TABLE t5980c;
656 ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
657 SHOW CREATE TABLE t5980d;
658 ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
659 === information_schema.innodb_tables and innodb_tablespaces ===
660 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
661 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
662 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
663 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
664 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
665 test/t5980aa test/t5980aa 33 5 Dynamic 0 Single
666 test/t5980bb test/t5980bb 97 5 Dynamic 0 Single
667 test/t5980c test/t5980c 33 5 Dynamic 0 Single
668 test/t5980d test/t5980d 97 5 Dynamic 0 Single
669 === information_schema.innodb_tablespaces and innodb_datafiles ===
670 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
671 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
672 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
673 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
674 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
675 test/t5980aa Single DEFAULT 0 0 0 Dynamic MYSQLD_DATADIR/test/t5980aa.ibd
676 test/t5980bb Single DEFAULT 0 0 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980bb.ibd
677 test/t5980c Single DEFAULT 0 0 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd
678 test/t5980d Single DEFAULT 0 0 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd
679 === information_schema.files ===
680 Space_Name File_Type Engine Status Tablespace_Name Path
681 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
682 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
683 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
684 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
685 #
686 # Discarded tablespaces cannot be ALTERED with ALGORITHM=COPY.
687 #
688 ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=COPY;
689 ERROR HY000: Tablespace has been discarded for table 't5980aa'
690 ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=COPY;
691 ERROR HY000: Tablespace has been discarded for table 't5980bb'
692 ALTER TABLE t5980aa CHANGE a c INT, ALGORITHM=COPY;
693 ERROR HY000: Tablespace has been discarded for table 't5980aa'
694 ALTER TABLE t5980bb CHANGE a c INT, ALGORITHM=COPY;
695 ERROR HY000: Tablespace has been discarded for table 't5980bb'
696 #
697 # Discarded tablespaces can be ALTERED with ALGORITHM=INPLACE
698 # only if a rebuild is not required.
699 #
700 ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
701 ERROR HY000: Tablespace has been discarded for table 'test/t5980aa'
702 ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
703 ERROR HY000: Tablespace has been discarded for table 'test/t5980bb'
704 ALTER TABLE t5980aa RENAME t5980aaa, ALGORITHM=INPLACE;
705 ALTER TABLE t5980aaa RENAME t5980aa, ALGORITHM=INPLACE;
706 Warnings:
707 Warning 1814 InnoDB: Tablespace has been discarded for table 't5980aaa'
708 ALTER TABLE t5980bb RENAME t5980bbb, ALGORITHM=INPLACE;
709 ALTER TABLE t5980bbb RENAME t5980bb, ALGORITHM=INPLACE;
710 Warnings:
711 Warning 1814 InnoDB: Tablespace has been discarded for table 't5980bbb'
712 ### files in MYSQLD_DATADIR/test
713 ### files in MYSQL_TMP_DIR/alt_dir/test
714 #
715 # Missing tablespaces cannot be ALTERED.
716 #
717 ALTER TABLE t5980c ADD PRIMARY KEY(a);
718 ERROR HY000: Tablespace is missing for table `test`.`t5980c`.
719 ALTER TABLE t5980d ADD PRIMARY KEY(a);
720 ERROR HY000: Tablespace is missing for table `test`.`t5980d`.
721 SELECT * FROM t5980aa;
722 ERROR HY000: Tablespace has been discarded for table 't5980aa'
723 SELECT * FROM t5980bb;
724 ERROR HY000: Tablespace has been discarded for table 't5980bb'
725 SHOW CREATE TABLE t5980aa;
726 Table Create Table
727 t5980aa CREATE TABLE `t5980aa` (
728 `a` int DEFAULT NULL,
729 `b` text
730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
731 SHOW CREATE TABLE t5980bb;
732 Table Create Table
733 t5980bb CREATE TABLE `t5980bb` (
734 `a` int DEFAULT NULL,
735 `b` text
736 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
737 === information_schema.innodb_tables and innodb_tablespaces ===
738 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
739 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
740 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
741 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
742 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
743 test/t5980aa test/t5980aa 33 5 Dynamic 0 Single
744 test/t5980bb test/t5980bb 97 5 Dynamic 0 Single
745 test/t5980c test/t5980c 33 5 Dynamic 0 Single
746 test/t5980d test/t5980d 97 5 Dynamic 0 Single
747 === information_schema.innodb_tablespaces and innodb_datafiles ===
748 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
749 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
750 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
751 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
752 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
753 test/t5980aa Single DEFAULT 0 0 0 Dynamic MYSQLD_DATADIR/test/t5980aa.ibd
754 test/t5980bb Single DEFAULT 0 0 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980bb.ibd
755 test/t5980c Single DEFAULT 0 0 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd
756 test/t5980d Single DEFAULT 0 0 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd
757 === information_schema.files ===
758 Space_Name File_Type Engine Status Tablespace_Name Path
759 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
760 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
761 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
762 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
763 ### files in MYSQLD_DATADIR/test
764 ### files in MYSQL_TMP_DIR/alt_dir/test
765 #
766 # Restart the server to check if the discarded flag is persistent
767 #
768 # restart
769 #
770 # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
771 #
772 INSERT INTO t5980aa VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE");
773 ERROR HY000: Tablespace has been discarded for table 't5980aa'
774 INSERT INTO t5980bb VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE");
775 ERROR HY000: Tablespace has been discarded for table 't5980bb'
776 SELECT * FROM t5980aa;
777 ERROR HY000: Tablespace has been discarded for table 't5980aa'
778 SELECT * FROM t5980bb;
779 ERROR HY000: Tablespace has been discarded for table 't5980bb'
780 RENAME TABLE t5980aa TO t5980a;
781 RENAME TABLE t5980bb TO t5980b;
782 SHOW CREATE TABLE t5980a;
783 Table Create Table
784 t5980a CREATE TABLE `t5980a` (
785 `a` int DEFAULT NULL,
786 `b` text
787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
788 Warnings:
789 Warning 1814 InnoDB: Tablespace has been discarded for table 't5980a'
790 SHOW CREATE TABLE t5980b;
791 Table Create Table
792 t5980b CREATE TABLE `t5980b` (
793 `a` int DEFAULT NULL,
794 `b` text
795 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
796 Warnings:
797 Warning 1814 InnoDB: Tablespace has been discarded for table 't5980b'
798 === information_schema.innodb_tables and innodb_tablespaces ===
799 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
800 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
801 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
802 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
803 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
804 test/t5980a test/t5980a 33 5 Dynamic 0 Single
805 test/t5980b test/t5980b 97 5 Dynamic 0 Single
806 test/t5980c test/t5980c 33 5 Dynamic 0 Single
807 test/t5980d test/t5980d 97 5 Dynamic 0 Single
808 === information_schema.innodb_tablespaces and innodb_datafiles ===
809 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
810 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
811 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
812 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
813 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
814 test/t5980a Single DEFAULT 0 0 0 Dynamic MYSQLD_DATADIR/test/t5980a.ibd
815 test/t5980b Single DEFAULT 0 0 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd
816 test/t5980c Single DEFAULT 0 0 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd
817 test/t5980d Single DEFAULT 0 0 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd
818 === information_schema.files ===
819 Space_Name File_Type Engine Status Tablespace_Name Path
820 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
821 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
822 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
823 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
824 ### files in MYSQLD_DATADIR/test
825 ### files in MYSQL_TMP_DIR/alt_dir/test
826 #
827 # Discard tablespaces again and try another ALTER TABLE ROW_FORMAT.
828 #
829 ALTER TABLE t5980a DISCARD TABLESPACE;
830 Warnings:
831 Warning 1812 InnoDB: Tablespace is missing for table test/t5980a.
832 ALTER TABLE t5980b DISCARD TABLESPACE;
833 Warnings:
834 Warning 1812 InnoDB: Tablespace is missing for table test/t5980b.
835 SELECT * FROM t5980a;
836 ERROR HY000: Tablespace has been discarded for table 't5980a'
837 SELECT * FROM t5980b;
838 ERROR HY000: Tablespace has been discarded for table 't5980b'
839 #
840 # ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace.
841 #
842 ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
843 ERROR HY000: Tablespace has been discarded for table 't5980a'
844 ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
845 ERROR HY000: Tablespace has been discarded for table 't5980b'
846 #
847 # ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace.
848 # only if a rebuild is not required.
849 #
850 ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
851 ERROR HY000: Tablespace has been discarded for table 'test/t5980a'
852 ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
853 ERROR HY000: Tablespace has been discarded for table 'test/t5980b'
854 #
855 # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
856 #
857 INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
858 ERROR HY000: Tablespace has been discarded for table 't5980a'
859 INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
860 ERROR HY000: Tablespace has been discarded for table 't5980b'
861 SELECT * FROM t5980a;
862 ERROR HY000: Tablespace has been discarded for table 't5980a'
863 SELECT * FROM t5980b;
864 ERROR HY000: Tablespace has been discarded for table 't5980b'
865 ### files in MYSQLD_DATADIR/test
866 ### files in MYSQL_TMP_DIR/alt_dir/test
867 #
868 # Discard tablespaces again and try ALTER TABLE ADD COLUMN.
869 #
870 ALTER TABLE t5980a DISCARD TABLESPACE;
871 Warnings:
872 Warning 1812 InnoDB: Tablespace is missing for table test/t5980a.
873 ALTER TABLE t5980b DISCARD TABLESPACE;
874 Warnings:
875 Warning 1812 InnoDB: Tablespace is missing for table test/t5980b.
876 SELECT * FROM t5980a;
877 ERROR HY000: Tablespace has been discarded for table 't5980a'
878 SELECT * FROM t5980b;
879 ERROR HY000: Tablespace has been discarded for table 't5980b'
880 #
881 # ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace.
882 #
883 ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=COPY;
884 ERROR HY000: Tablespace has been discarded for table 't5980a'
885 ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=COPY;
886 ERROR HY000: Tablespace has been discarded for table 't5980b'
887 #
888 # ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace.
889 # only if a rebuild is not required.
890 #
891 ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=INPLACE;
892 ERROR HY000: Tablespace has been discarded for table 'test/t5980a'
893 ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=INPLACE;
894 ERROR HY000: Tablespace has been discarded for table 'test/t5980b'
895 #
896 # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
897 #
898 DELETE FROM t5980a;
899 ERROR HY000: Tablespace has been discarded for table 't5980a'
900 UPDATE t5980a SET b="Tablespace is DISCARDED";
901 ERROR HY000: Tablespace has been discarded for table 't5980a'
902 INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
903 ERROR HY000: Tablespace has been discarded for table 't5980a'
904 INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
905 ERROR HY000: Tablespace has been discarded for table 't5980b'
906 SELECT * FROM t5980a;
907 ERROR HY000: Tablespace has been discarded for table 't5980a'
908 SELECT * FROM t5980b;
909 ERROR HY000: Tablespace has been discarded for table 't5980b'
910 ### files in MYSQLD_DATADIR/test
911 ### files in MYSQL_TMP_DIR/alt_dir/test
912 DROP TABLE t5980a;
913 DROP TABLE t5980b;
914 DROP TABLE t5980c;
915 DROP TABLE t5980d;
916 === information_schema.innodb_tables and innodb_tablespaces ===
917 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
918 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
919 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
920 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
921 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
922 === information_schema.innodb_tablespaces and innodb_datafiles ===
923 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
924 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
925 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
926 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
927 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
928 === information_schema.files ===
929 Space_Name File_Type Engine Status Tablespace_Name Path
930 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
931 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
932 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
933 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
934 ### files in MYSQLD_DATADIR/test
935 ### files in MYSQL_TMP_DIR/alt_dir/test
936 #
937 # Cleanup
938 #