Debian Codegraph @

mysql-test/suite/innodb/r/alter_missing_tablespace.result

raw
1 #
2 # ALLOW IN-PLACE DDL OPERATIONS ON MISSING AND/OR DISCARDED TABLESPACES
3 #
4 SET GLOBAL innodb_file_per_table=1;
5 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
6 INSERT INTO t1 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
7 CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
8 INSERT INTO t2 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
9 CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
10 INSERT INTO t3 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
11 CREATE TABLE t4 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
12 INSERT INTO t4 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
13 # Check also with tables having special characters
14 CREATE TABLE `t_.._a` (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
15 INSERT INTO `t_.._a` VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
16 CREATE TABLE `t_.._b` (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
17 INSERT INTO `t_.._b` VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
18 #
19 # Remove and Discard tablespaces
20 #
21 # restart
22 ALTER TABLE t3 DISCARD TABLESPACE;
23 ALTER TABLE t4 DISCARD TABLESPACE;
24 ALTER TABLE `t_.._b` DISCARD TABLESPACE;
25 #
26 # SELECT * FROM __
27 #
28 SELECT * FROM t1;
29 ERROR HY000: Tablespace is missing for table `test`.`t1`.
30 SHOW WARNINGS;
31 Level Code Message
32 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
33 Error 1812 Tablespace is missing for table `test`.`t1`.
34 SELECT * FROM t2;
35 ERROR HY000: Tablespace is missing for table `test`.`t2`.
36 SHOW WARNINGS;
37 Level Code Message
38 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
39 Error 1812 Tablespace is missing for table `test`.`t2`.
40 SELECT * FROM t3;
41 ERROR HY000: Tablespace has been discarded for table 't3'
42 SHOW WARNINGS;
43 Level Code Message
44 Error 1814 Tablespace has been discarded for table 't3'
45 Error 1146 Table 'test.t3' doesn't exist
46 SELECT * FROM t4;
47 ERROR HY000: Tablespace has been discarded for table 't4'
48 SHOW WARNINGS;
49 Level Code Message
50 Error 1814 Tablespace has been discarded for table 't4'
51 Error 1146 Table 'test.t4' doesn't exist
52 SELECT * FROM `t_.._a`;
53 ERROR HY000: Tablespace is missing for table `test`.`t_.._a`.
54 SHOW WARNINGS;
55 Level Code Message
56 Warning 1812 InnoDB: Tablespace is missing for table test/t_@002e@002e_a.
57 Error 1812 Tablespace is missing for table `test`.`t_.._a`.
58 SELECT * FROM `t_.._b`;
59 ERROR HY000: Tablespace has been discarded for table 't_.._b'
60 SHOW WARNINGS;
61 Level Code Message
62 Error 1814 Tablespace has been discarded for table 't_.._b'
63 Error 1146 Table 'test.t_.._b' doesn't exist
64 #
65 # ALTER TABLE __ TABLESPACE innodb_system
66 #
67 ALTER TABLE t1 TABLESPACE innodb_system, ALGORITHM=INPLACE;
68 ERROR HY000: Tablespace is missing for table `test`.`t1`.
69 SHOW WARNINGS;
70 Level Code Message
71 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
72 Error 1812 Tablespace is missing for table `test`.`t1`.
73 ALTER TABLE t2 TABLESPACE innodb_system, ALGORITHM=COPY;
74 ERROR HY000: Tablespace is missing for table `test`.`t2`.
75 SHOW WARNINGS;
76 Level Code Message
77 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
78 Error 1812 Tablespace is missing for table `test`.`t2`.
79 ALTER TABLE t3 TABLESPACE innodb_system, ALGORITHM=INPLACE;
80 ERROR HY000: Tablespace has been discarded for table 'test/t3'
81 SHOW WARNINGS;
82 Level Code Message
83 Error 1814 Tablespace has been discarded for table 'test/t3'
84 ALTER TABLE t4 TABLESPACE innodb_system, ALGORITHM=COPY;
85 ERROR HY000: Tablespace has been discarded for table 't4'
86 SHOW WARNINGS;
87 Level Code Message
88 Error 1814 Tablespace has been discarded for table 't4'
89 Error 1146 Table 'test.t4' doesn't exist
90 #
91 # ALTER TABLE __ TABLESPACE ts1
92 #
93 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
94 ALTER TABLE t1 TABLESPACE ts1, ALGORITHM=INPLACE;
95 ERROR HY000: Tablespace is missing for table `test`.`t1`.
96 SHOW WARNINGS;
97 Level Code Message
98 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
99 Error 1812 Tablespace is missing for table `test`.`t1`.
100 ALTER TABLE t2 TABLESPACE ts1, ALGORITHM=COPY;
101 ERROR HY000: Tablespace is missing for table `test`.`t2`.
102 SHOW WARNINGS;
103 Level Code Message
104 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
105 Error 1812 Tablespace is missing for table `test`.`t2`.
106 ALTER TABLE t3 TABLESPACE ts1, ALGORITHM=INPLACE;
107 ERROR HY000: Tablespace has been discarded for table 'test/t3'
108 SHOW WARNINGS;
109 Level Code Message
110 Error 1814 Tablespace has been discarded for table 'test/t3'
111 ALTER TABLE t4 TABLESPACE ts1, ALGORITHM=COPY;
112 ERROR HY000: Tablespace has been discarded for table 't4'
113 SHOW WARNINGS;
114 Level Code Message
115 Error 1814 Tablespace has been discarded for table 't4'
116 Error 1146 Table 'test.t4' doesn't exist
117 #
118 # ALTER TABLE __ ADD INDEX
119 #
120 ALTER TABLE t1 ADD INDEX (a), ALGORITHM=INPLACE;
121 ERROR HY000: Tablespace is missing for table `test`.`t1`.
122 SHOW WARNINGS;
123 Level Code Message
124 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
125 Error 1812 Tablespace is missing for table `test`.`t1`.
126 ALTER TABLE t2 ADD INDEX (a), ALGORITHM=COPY;
127 ERROR HY000: Tablespace is missing for table `test`.`t2`.
128 SHOW WARNINGS;
129 Level Code Message
130 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
131 Error 1812 Tablespace is missing for table `test`.`t2`.
132 ALTER TABLE t3 ADD INDEX (a), ALGORITHM=INPLACE;
133 ERROR 42000: Key column 'a' doesn't exist in table
134 SHOW WARNINGS;
135 Level Code Message
136 Error 1072 Key column 'a' doesn't exist in table
137 ALTER TABLE t4 ADD INDEX (a), ALGORITHM=COPY;
138 ERROR 42000: Key column 'a' doesn't exist in table
139 SHOW WARNINGS;
140 Level Code Message
141 Error 1072 Key column 'a' doesn't exist in table
142 #
143 # ALTER TABLE __ DROP INDEX
144 #
145 ALTER TABLE t1 DROP INDEX c2, ALGORITHM=INPLACE;
146 ERROR HY000: Tablespace is missing for table `test`.`t1`.
147 SHOW WARNINGS;
148 Level Code Message
149 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
150 Error 1812 Tablespace is missing for table `test`.`t1`.
151 ALTER TABLE t2 DROP INDEX c2, ALGORITHM=COPY;
152 ERROR HY000: Tablespace is missing for table `test`.`t2`.
153 SHOW WARNINGS;
154 Level Code Message
155 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
156 Error 1812 Tablespace is missing for table `test`.`t2`.
157 ALTER TABLE t3 DROP INDEX c2, ALGORITHM=INPLACE;
158 Warnings:
159 Warning 1814 InnoDB: Tablespace has been discarded for table 't3'
160 ALTER TABLE t4 DROP INDEX c2, ALGORITHM=COPY;
161 ERROR HY000: Tablespace has been discarded for table 't4'
162 SHOW WARNINGS;
163 Level Code Message
164 Error 1814 Tablespace has been discarded for table 't4'
165 Error 1146 Table 'test.t4' doesn't exist
166 #
167 # ALTER TABLE __ ADD COLUMN
168 #
169 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INPLACE;
170 ERROR HY000: Tablespace is missing for table `test`.`t1`.
171 SHOW WARNINGS;
172 Level Code Message
173 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
174 Error 1812 Tablespace is missing for table `test`.`t1`.
175 ALTER TABLE t2 ADD COLUMN c3 INT, ALGORITHM=COPY;
176 ERROR HY000: Tablespace is missing for table `test`.`t2`.
177 SHOW WARNINGS;
178 Level Code Message
179 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
180 Error 1812 Tablespace is missing for table `test`.`t2`.
181 ALTER TABLE t3 ADD COLUMN c3 INT, ALGORITHM=INPLACE;
182 ERROR HY000: Tablespace has been discarded for table 'test/t3'
183 SHOW WARNINGS;
184 Level Code Message
185 Error 1814 Tablespace has been discarded for table 'test/t3'
186 ALTER TABLE t4 ADD COLUMN c3 INT, ALGORITHM=COPY;
187 ERROR HY000: Tablespace has been discarded for table 't4'
188 SHOW WARNINGS;
189 Level Code Message
190 Error 1814 Tablespace has been discarded for table 't4'
191 Error 1146 Table 'test.t4' doesn't exist
192 #
193 # ALTER TABLE __ DROP COLUMN
194 #
195 ALTER TABLE t1 DROP COLUMN ct, ALGORITHM=INPLACE;
196 ERROR HY000: Tablespace is missing for table `test`.`t1`.
197 SHOW WARNINGS;
198 Level Code Message
199 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
200 Error 1812 Tablespace is missing for table `test`.`t1`.
201 ALTER TABLE t2 DROP COLUMN ct, ALGORITHM=COPY;
202 ERROR HY000: Tablespace is missing for table `test`.`t2`.
203 SHOW WARNINGS;
204 Level Code Message
205 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
206 Error 1812 Tablespace is missing for table `test`.`t2`.
207 ALTER TABLE t3 DROP COLUMN ct, ALGORITHM=INPLACE;
208 ERROR HY000: Tablespace has been discarded for table 'test/t3'
209 SHOW WARNINGS;
210 Level Code Message
211 Error 1814 Tablespace has been discarded for table 'test/t3'
212 ALTER TABLE t4 DROP COLUMN ct, ALGORITHM=COPY;
213 ERROR HY000: Tablespace has been discarded for table 't4'
214 SHOW WARNINGS;
215 Level Code Message
216 Error 1814 Tablespace has been discarded for table 't4'
217 Error 1146 Table 'test.t4' doesn't exist
218 #
219 # ALTER TABLE __ ALTER c2 DROP DEFAULT
220 #
221 ALTER TABLE t1 ALTER c2 DROP DEFAULT, ALGORITHM=INPLACE;
222 ERROR HY000: Tablespace is missing for table `test`.`t1`.
223 SHOW WARNINGS;
224 Level Code Message
225 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
226 Error 1812 Tablespace is missing for table `test`.`t1`.
227 ALTER TABLE t2 ALTER c2 DROP DEFAULT, ALGORITHM=COPY;
228 ERROR HY000: Tablespace is missing for table `test`.`t2`.
229 SHOW WARNINGS;
230 Level Code Message
231 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
232 Error 1812 Tablespace is missing for table `test`.`t2`.
233 ALTER TABLE t3 ALTER c2 DROP DEFAULT, ALGORITHM=INPLACE;
234 Warnings:
235 Warning 1814 InnoDB: Tablespace has been discarded for table 't3'
236 ALTER TABLE t4 ALTER c2 DROP DEFAULT, ALGORITHM=COPY;
237 ERROR HY000: Tablespace has been discarded for table 't4'
238 SHOW WARNINGS;
239 Level Code Message
240 Error 1814 Tablespace has been discarded for table 't4'
241 Error 1146 Table 'test.t4' doesn't exist
242 #
243 # ALTER TABLE __ ALTER c2 SET DEFAULT 42
244 #
245 ALTER TABLE t1 ALTER c2 SET DEFAULT 42, ALGORITHM=INPLACE;
246 ERROR HY000: Tablespace is missing for table `test`.`t1`.
247 SHOW WARNINGS;
248 Level Code Message
249 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
250 Error 1812 Tablespace is missing for table `test`.`t1`.
251 ALTER TABLE t2 ALTER c2 SET DEFAULT 42, ALGORITHM=COPY;
252 ERROR HY000: Tablespace is missing for table `test`.`t2`.
253 SHOW WARNINGS;
254 Level Code Message
255 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
256 Error 1812 Tablespace is missing for table `test`.`t2`.
257 ALTER TABLE t3 ALTER c2 SET DEFAULT 42, ALGORITHM=INPLACE;
258 Warnings:
259 Warning 1814 InnoDB: Tablespace has been discarded for table 't3'
260 ALTER TABLE t4 ALTER c2 SET DEFAULT 42, ALGORITHM=COPY;
261 ERROR HY000: Tablespace has been discarded for table 't4'
262 SHOW WARNINGS;
263 Level Code Message
264 Error 1814 Tablespace has been discarded for table 't4'
265 Error 1146 Table 'test.t4' doesn't exist
266 #
267 # ALTER TABLE __ ADD FULLTEXT INDEX
268 #
269 ALTER TABLE t1 ADD FULLTEXT INDEX (ct), ALGORITHM=INPLACE;
270 ERROR HY000: Tablespace is missing for table `test`.`t1`.
271 SHOW WARNINGS;
272 Level Code Message
273 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
274 Error 1812 Tablespace is missing for table `test`.`t1`.
275 ALTER TABLE t2 ADD FULLTEXT INDEX (ct), ALGORITHM=COPY;
276 ERROR HY000: Tablespace is missing for table `test`.`t2`.
277 SHOW WARNINGS;
278 Level Code Message
279 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
280 Error 1812 Tablespace is missing for table `test`.`t2`.
281 ALTER TABLE t3 ADD FULLTEXT INDEX (ct), ALGORITHM=INPLACE;
282 Warnings:
283 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
284 Warning 1814 InnoDB: Tablespace has been discarded for table 't3'
285 ALTER TABLE t4 ADD FULLTEXT INDEX (ct), ALGORITHM=COPY;
286 ERROR HY000: Tablespace has been discarded for table 't4'
287 SHOW WARNINGS;
288 Level Code Message
289 Error 1814 Tablespace has been discarded for table 't4'
290 Error 1146 Table 'test.t4' doesn't exist
291 #
292 # ALTER TABLE __ CHANGE c2 c2 INT AFTER c1
293 #
294 ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1, ALGORITHM=INPLACE;
295 ERROR HY000: Tablespace is missing for table `test`.`t1`.
296 SHOW WARNINGS;
297 Level Code Message
298 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
299 Error 1812 Tablespace is missing for table `test`.`t1`.
300 ALTER TABLE t2 CHANGE c2 c2 INT AFTER c1, ALGORITHM=COPY;
301 ERROR HY000: Tablespace is missing for table `test`.`t2`.
302 SHOW WARNINGS;
303 Level Code Message
304 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
305 Error 1812 Tablespace is missing for table `test`.`t2`.
306 ALTER TABLE t3 CHANGE c2 c2 INT AFTER c1, ALGORITHM=INPLACE;
307 Warnings:
308 Warning 1814 InnoDB: Tablespace has been discarded for table 't3'
309 ALTER TABLE t4 CHANGE c2 c2 INT AFTER c1, ALGORITHM=COPY;
310 ERROR HY000: Tablespace has been discarded for table 't4'
311 SHOW WARNINGS;
312 Level Code Message
313 Error 1814 Tablespace has been discarded for table 't4'
314 Error 1146 Table 'test.t4' doesn't exist
315 #
316 # ALTER TABLE __ CHANGE c1 c1 INT FIRST
317 #
318 ALTER TABLE t1 CHANGE c1 c1 INT FIRST, ALGORITHM=INPLACE;
319 ERROR HY000: Tablespace is missing for table `test`.`t1`.
320 SHOW WARNINGS;
321 Level Code Message
322 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
323 Error 1812 Tablespace is missing for table `test`.`t1`.
324 ALTER TABLE t2 CHANGE c1 c1 INT FIRST, ALGORITHM=COPY;
325 ERROR HY000: Tablespace is missing for table `test`.`t2`.
326 SHOW WARNINGS;
327 Level Code Message
328 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
329 Error 1812 Tablespace is missing for table `test`.`t2`.
330 ALTER TABLE t3 CHANGE c1 c1 INT FIRST, ALGORITHM=INPLACE;
331 Warnings:
332 Warning 1814 InnoDB: Tablespace has been discarded for table 't3'
333 ALTER TABLE t4 CHANGE c1 c1 INT FIRST, ALGORITHM=COPY;
334 ERROR HY000: Tablespace has been discarded for table 't4'
335 SHOW WARNINGS;
336 Level Code Message
337 Error 1814 Tablespace has been discarded for table 't4'
338 Error 1146 Table 'test.t4' doesn't exist
339 #
340 # ALTER TABLE __ CHANGE c2 c3 INT
341 #
342 ALTER TABLE t1 CHANGE c2 c3 INT, ALGORITHM=INPLACE;
343 ERROR HY000: Tablespace is missing for table `test`.`t1`.
344 SHOW WARNINGS;
345 Level Code Message
346 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
347 Error 1812 Tablespace is missing for table `test`.`t1`.
348 ALTER TABLE t2 CHANGE c2 c3 INT, ALGORITHM=COPY;
349 ERROR HY000: Tablespace is missing for table `test`.`t2`.
350 SHOW WARNINGS;
351 Level Code Message
352 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
353 Error 1812 Tablespace is missing for table `test`.`t2`.
354 ALTER TABLE t3 CHANGE c2 c3 INT, ALGORITHM=INPLACE;
355 Warnings:
356 Warning 1814 InnoDB: Tablespace has been discarded for table 't3'
357 ALTER TABLE t4 CHANGE c2 c3 INT, ALGORITHM=COPY;
358 ERROR HY000: Tablespace has been discarded for table 't4'
359 SHOW WARNINGS;
360 Level Code Message
361 Error 1814 Tablespace has been discarded for table 't4'
362 Error 1146 Table 'test.t4' doesn't exist
363 #
364 # ALTER TABLE __ ROW_FORMAT=REDUNDANT
365 #
366 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE;
367 ERROR HY000: Tablespace is missing for table `test`.`t1`.
368 SHOW WARNINGS;
369 Level Code Message
370 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
371 Error 1812 Tablespace is missing for table `test`.`t1`.
372 ALTER TABLE t2 ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
373 ERROR HY000: Tablespace is missing for table `test`.`t2`.
374 SHOW WARNINGS;
375 Level Code Message
376 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
377 Error 1812 Tablespace is missing for table `test`.`t2`.
378 ALTER TABLE t3 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE;
379 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY.
380 SHOW WARNINGS;
381 Level Code Message
382 Error 1846 ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY.
383 ALTER TABLE t4 ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
384 ERROR HY000: Tablespace has been discarded for table 't4'
385 SHOW WARNINGS;
386 Level Code Message
387 Error 1814 Tablespace has been discarded for table 't4'
388 Error 1146 Table 'test.t4' doesn't exist
389 #
390 # ALTER TABLE __ RENAME TO __
391 #
392 ALTER TABLE t1 RENAME TO t11, ALGORITHM=INPLACE;
393 ERROR HY000: Tablespace is missing for table `test`.`t1`.
394 SHOW WARNINGS;
395 Level Code Message
396 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
397 Error 1812 Tablespace is missing for table `test`.`t1`.
398 ALTER TABLE t2 RENAME TO t22, ALGORITHM=COPY;
399 ERROR HY000: Tablespace is missing for table `test`.`t2`.
400 SHOW WARNINGS;
401 Level Code Message
402 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
403 Error 1812 Tablespace is missing for table `test`.`t2`.
404 ALTER TABLE t3 RENAME TO t33, ALGORITHM=INPLACE;
405 ALTER TABLE t33 RENAME TO t3, ALGORITHM=INPLACE;
406 Warnings:
407 Warning 1814 InnoDB: Tablespace has been discarded for table 't33'
408 ALTER TABLE t4 RENAME TO t44, ALGORITHM=COPY;
409 ERROR HY000: Tablespace has been discarded for table 't4'
410 SHOW WARNINGS;
411 Level Code Message
412 Error 1814 Tablespace has been discarded for table 't4'
413 Error 1146 Table 'test.t4' doesn't exist
414 #
415 # DISCARD an already missing tablespace;
416 #
417 ALTER TABLE t1 DISCARD TABLESPACE;
418 Warnings:
419 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
420 Warning 1812 InnoDB: Tablespace is missing for table test/t1.
421 #
422 # Show the resultant tables before dropping them
423 #
424 SHOW CREATE TABLE t1;
425 Table Create Table
426 t1 CREATE TABLE `t1` (
427 `c1` int NOT NULL,
428 `c2` int DEFAULT '1',
429 `ct` text,
430 PRIMARY KEY (`c1`),
431 KEY `c2` (`c2`)
432 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
433 SHOW CREATE TABLE t2;
434 ERROR HY000: Tablespace is missing for table `test`.`t2`.
435 SHOW WARNINGS;
436 Level Code Message
437 Warning 1812 InnoDB: Tablespace is missing for table test/t2.
438 Error 1812 Tablespace is missing for table `test`.`t2`.
439 SHOW CREATE TABLE t3;
440 Table Create Table
441 t3 CREATE TABLE `t3` (
442 `c1` int NOT NULL,
443 `c3` int DEFAULT NULL,
444 `ct` text,
445 PRIMARY KEY (`c1`),
446 FULLTEXT KEY `ct` (`ct`)
447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
448 Warnings:
449 Warning 1814 InnoDB: Tablespace has been discarded for table 't3'
450 SHOW CREATE TABLE t4;
451 Table Create Table
452 t4 CREATE TABLE `t4` (
453 `c1` int NOT NULL,
454 `c2` int DEFAULT '1',
455 `ct` text,
456 PRIMARY KEY (`c1`),
457 KEY `c2` (`c2`)
458 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
459 SHOW CREATE TABLE `t_.._a`;
460 ERROR HY000: Tablespace is missing for table `test`.`t_.._a`.
461 SHOW WARNINGS;
462 Level Code Message
463 Warning 1812 InnoDB: Tablespace is missing for table test/t_@002e@002e_a.
464 Error 1812 Tablespace is missing for table `test`.`t_.._a`.
465 SHOW CREATE TABLE `t_.._b`;
466 Table Create Table
467 t_.._b CREATE TABLE `t_.._b` (
468 `c1` int NOT NULL,
469 `c2` int DEFAULT '1',
470 `ct` text,
471 PRIMARY KEY (`c1`),
472 KEY `c2` (`c2`)
473 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
474 #
475 # DROP TABLE __ while mising or discarded
476 #
477 DROP TABLE t1;
478 DROP TABLE t2;
479 DROP TABLE t3;
480 DROP TABLE t4;
481 DROP TABLE `t_.._a`;
482 DROP TABLE `t_.._b`;
483 #
484 # Recreate the 6 tables; 1 & 2 missing, 3 & 4 discarded, 5 & 6 normal
485 #
486 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
487 CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
488 CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
489 CREATE TABLE t4 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
490 CREATE TABLE t5 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
491 CREATE TABLE t6 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
492 INSERT INTO t1 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
493 INSERT INTO t2 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
494 INSERT INTO t3 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
495 INSERT INTO t4 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
496 INSERT INTO t5 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
497 INSERT INTO t6 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
498 CREATE TABLE t1p LIKE t1;
499 CREATE TABLE t2p LIKE t1;
500 CREATE TABLE t3p LIKE t1;
501 CREATE TABLE t4p LIKE t1;
502 CREATE TABLE t5p LIKE t1;
503 CREATE TABLE t6p LIKE t1;
504 #
505 # Remove t1 and Discard t3 & t4
506 #
507 # restart
508 ALTER TABLE t3 DISCARD TABLESPACE;
509 ALTER TABLE t4 DISCARD TABLESPACE;
510 #
511 # Add some foreign key constraints
512 #
513 CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
514 CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES t1(c2),
515 CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2));
516 CREATE TABLE t2c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
517 CONSTRAINT t2c2 FOREIGN KEY (c2) REFERENCES t2(c2),
518 CONSTRAINT t2c3 FOREIGN KEY (c3) REFERENCES t2p(c2));
519 CREATE TABLE t3c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
520 CONSTRAINT t3c2 FOREIGN KEY (c2) REFERENCES t3(c2),
521 CONSTRAINT t3c3 FOREIGN KEY (c3) REFERENCES t3p(c2));
522 CREATE TABLE t4c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
523 CONSTRAINT t4c2 FOREIGN KEY (c2) REFERENCES t4(c2),
524 CONSTRAINT t4c3 FOREIGN KEY (c3) REFERENCES t4p(c2));
525 CREATE TABLE t5c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
526 CONSTRAINT t5c2 FOREIGN KEY (c2) REFERENCES t5(c2),
527 CONSTRAINT t5c3 FOREIGN KEY (c3) REFERENCES t5p(c2));
528 CREATE TABLE t6c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
529 CONSTRAINT t6c2 FOREIGN KEY (c2) REFERENCES t6(c2),
530 CONSTRAINT t6c3 FOREIGN KEY (c3) REFERENCES t6p(c2));
531 #
532 # Remove t2, try to discard t3 & t4 already discarded
533 #
534 # restart
535 ALTER TABLE t3 DISCARD TABLESPACE;
536 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails ()
537 SHOW WARNINGS;
538 Level Code Message
539 Warning 1814 InnoDB: Tablespace has been discarded for table 't3'
540 Warning 1812 InnoDB: Tablespace is missing for table test/t3.
541 Error 1451 Cannot delete or update a parent row: a foreign key constraint fails ()
542 ALTER TABLE t4 DISCARD TABLESPACE;
543 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails ()
544 SHOW WARNINGS;
545 Level Code Message
546 Warning 1814 InnoDB: Tablespace has been discarded for table 't4'
547 Warning 1812 InnoDB: Tablespace is missing for table test/t4.
548 Error 1451 Cannot delete or update a parent row: a foreign key constraint fails ()
549 ALTER TABLE t5 DISCARD TABLESPACE;
550 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails ()
551 SHOW WARNINGS;
552 Level Code Message
553 Error 1451 Cannot delete or update a parent row: a foreign key constraint fails ()
554 #
555 # ALTER TABLE __ DROP FOREIGN KEY
556 #
557 ALTER TABLE t1c DROP FOREIGN KEY t1c2, ALGORITHM=INPLACE;
558 ALTER TABLE t2c DROP FOREIGN KEY t2c2, ALGORITHM=COPY;
559 ALTER TABLE t3c DROP FOREIGN KEY t3c2, ALGORITHM=INPLACE;
560 ALTER TABLE t4c DROP FOREIGN KEY t4c2, ALGORITHM=COPY;
561 ALTER TABLE t5c DROP FOREIGN KEY t5c2, ALGORITHM=INPLACE;
562 ALTER TABLE t6c DROP FOREIGN KEY t6c2, ALGORITHM=COPY;
563 #
564 # Cleanup
565 #
566 DROP TABLE t1;
567 DROP TABLE t2;
568 DROP TABLE t3;
569 DROP TABLE t4;
570 DROP TABLE t5;
571 DROP TABLE t6;
572 DROP TABLE t1c;
573 DROP TABLE t2c;
574 DROP TABLE t3c;
575 DROP TABLE t4c;
576 DROP TABLE t5c;
577 DROP TABLE t6c;
578 DROP TABLE t1p;
579 DROP TABLE t2p;
580 DROP TABLE t3p;
581 DROP TABLE t4p;
582 DROP TABLE t5p;
583 DROP TABLE t6p;
584 DROP TABLESPACE ts1;