1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
|
--echo #
--echo # ALLOW IN-PLACE DDL OPERATIONS ON MISSING AND/OR DISCARDED TABLESPACES
--echo #
let $MYSQLD_DATADIR=`select @@datadir`;
SET GLOBAL innodb_file_per_table=1;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
INSERT INTO t1 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
INSERT INTO t2 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
INSERT INTO t3 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
CREATE TABLE t4 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
INSERT INTO t4 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
--echo # Check also with tables having special characters
CREATE TABLE `t_.._a` (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
INSERT INTO `t_.._a` VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
CREATE TABLE `t_.._b` (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
INSERT INTO `t_.._b` VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
--echo #
--echo # Remove and Discard tablespaces
--echo #
--source include/shutdown_mysqld.inc
--remove_file $MYSQLD_DATADIR/test/t1.ibd
--remove_file $MYSQLD_DATADIR/test/t2.ibd
--remove_file $MYSQLD_DATADIR/test/t_@002e@002e_a.ibd
--source include/start_mysqld.inc
ALTER TABLE t3 DISCARD TABLESPACE;
ALTER TABLE t4 DISCARD TABLESPACE;
ALTER TABLE `t_.._b` DISCARD TABLESPACE;
--echo #
--echo # SELECT * FROM __
--echo #
# The ER_NO_SUCH_TABLE is being thrown by ha_innobase::open().
# The table does exist, only the tablespace does not exist.
--error ER_TABLESPACE_MISSING
SELECT * FROM t1;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
SELECT * FROM t2;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t3;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t4;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
SELECT * FROM `t_.._a`;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM `t_.._b`;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ TABLESPACE innodb_system
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 TABLESPACE innodb_system, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 TABLESPACE innodb_system, ALGORITHM=COPY;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t3 TABLESPACE innodb_system, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 TABLESPACE innodb_system, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ TABLESPACE ts1
--echo #
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd';
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 TABLESPACE ts1, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 TABLESPACE ts1, ALGORITHM=COPY;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t3 TABLESPACE ts1, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 TABLESPACE ts1, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ ADD INDEX
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 ADD INDEX (a), ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 ADD INDEX (a), ALGORITHM=COPY;
SHOW WARNINGS;
--error ER_KEY_COLUMN_DOES_NOT_EXITS
ALTER TABLE t3 ADD INDEX (a), ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_KEY_COLUMN_DOES_NOT_EXITS
ALTER TABLE t4 ADD INDEX (a), ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ DROP INDEX
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 DROP INDEX c2, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 DROP INDEX c2, ALGORITHM=COPY;
SHOW WARNINGS;
ALTER TABLE t3 DROP INDEX c2, ALGORITHM=INPLACE;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 DROP INDEX c2, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ ADD COLUMN
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 ADD COLUMN c3 INT, ALGORITHM=COPY;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t3 ADD COLUMN c3 INT, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 ADD COLUMN c3 INT, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ DROP COLUMN
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 DROP COLUMN ct, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 DROP COLUMN ct, ALGORITHM=COPY;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t3 DROP COLUMN ct, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 DROP COLUMN ct, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ ALTER c2 DROP DEFAULT
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 ALTER c2 DROP DEFAULT, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 ALTER c2 DROP DEFAULT, ALGORITHM=COPY;
SHOW WARNINGS;
ALTER TABLE t3 ALTER c2 DROP DEFAULT, ALGORITHM=INPLACE;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 ALTER c2 DROP DEFAULT, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ ALTER c2 SET DEFAULT 42
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 ALTER c2 SET DEFAULT 42, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 ALTER c2 SET DEFAULT 42, ALGORITHM=COPY;
SHOW WARNINGS;
ALTER TABLE t3 ALTER c2 SET DEFAULT 42, ALGORITHM=INPLACE;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 ALTER c2 SET DEFAULT 42, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ ADD FULLTEXT INDEX
--echo #
#--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 ADD FULLTEXT INDEX (ct), ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 ADD FULLTEXT INDEX (ct), ALGORITHM=COPY;
SHOW WARNINGS;
ALTER TABLE t3 ADD FULLTEXT INDEX (ct), ALGORITHM=INPLACE;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 ADD FULLTEXT INDEX (ct), ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ CHANGE c2 c2 INT AFTER c1
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 CHANGE c2 c2 INT AFTER c1, ALGORITHM=COPY;
SHOW WARNINGS;
ALTER TABLE t3 CHANGE c2 c2 INT AFTER c1, ALGORITHM=INPLACE;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 CHANGE c2 c2 INT AFTER c1, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ CHANGE c1 c1 INT FIRST
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 CHANGE c1 c1 INT FIRST, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 CHANGE c1 c1 INT FIRST, ALGORITHM=COPY;
SHOW WARNINGS;
ALTER TABLE t3 CHANGE c1 c1 INT FIRST, ALGORITHM=INPLACE;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 CHANGE c1 c1 INT FIRST, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ CHANGE c2 c3 INT
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 CHANGE c2 c3 INT, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 CHANGE c2 c3 INT, ALGORITHM=COPY;
SHOW WARNINGS;
ALTER TABLE t3 CHANGE c2 c3 INT, ALGORITHM=INPLACE;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 CHANGE c2 c3 INT, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ ROW_FORMAT=REDUNDANT
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
SHOW WARNINGS;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t3 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ RENAME TO __
--echo #
--error ER_TABLESPACE_MISSING
ALTER TABLE t1 RENAME TO t11, ALGORITHM=INPLACE;
SHOW WARNINGS;
--error ER_TABLESPACE_MISSING
ALTER TABLE t2 RENAME TO t22, ALGORITHM=COPY;
SHOW WARNINGS;
ALTER TABLE t3 RENAME TO t33, ALGORITHM=INPLACE;
ALTER TABLE t33 RENAME TO t3, ALGORITHM=INPLACE;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t4 RENAME TO t44, ALGORITHM=COPY;
SHOW WARNINGS;
--echo #
--echo # DISCARD an already missing tablespace;
--echo #
ALTER TABLE t1 DISCARD TABLESPACE;
--echo #
--echo # Show the resultant tables before dropping them
--echo #
SHOW CREATE TABLE t1;
--error ER_TABLESPACE_MISSING
SHOW CREATE TABLE t2;
SHOW WARNINGS;
SHOW CREATE TABLE t3;
SHOW CREATE TABLE t4;
--error ER_TABLESPACE_MISSING
SHOW CREATE TABLE `t_.._a`;
SHOW WARNINGS;
SHOW CREATE TABLE `t_.._b`;
--echo #
--echo # DROP TABLE __ while mising or discarded
--echo #
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE `t_.._a`;
DROP TABLE `t_.._b`;
--echo #
--echo # Recreate the 6 tables; 1 & 2 missing, 3 & 4 discarded, 5 & 6 normal
--echo #
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
CREATE TABLE t4 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
CREATE TABLE t5 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
CREATE TABLE t6 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2));
INSERT INTO t1 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
INSERT INTO t2 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
INSERT INTO t3 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
INSERT INTO t4 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
INSERT INTO t5 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
INSERT INTO t6 VALUES (1, 1, 'one'), (2, 2, 'two'), (3, 3, 'three');
CREATE TABLE t1p LIKE t1;
CREATE TABLE t2p LIKE t1;
CREATE TABLE t3p LIKE t1;
CREATE TABLE t4p LIKE t1;
CREATE TABLE t5p LIKE t1;
CREATE TABLE t6p LIKE t1;
--echo #
--echo # Remove t1 and Discard t3 & t4
--echo #
--source include/shutdown_mysqld.inc
--remove_file $MYSQLD_DATADIR/test/t1.ibd
--source include/start_mysqld.inc
ALTER TABLE t3 DISCARD TABLESPACE;
ALTER TABLE t4 DISCARD TABLESPACE;
--echo #
--echo # Add some foreign key constraints
--echo #
CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES t1(c2),
CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2));
CREATE TABLE t2c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
CONSTRAINT t2c2 FOREIGN KEY (c2) REFERENCES t2(c2),
CONSTRAINT t2c3 FOREIGN KEY (c3) REFERENCES t2p(c2));
CREATE TABLE t3c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
CONSTRAINT t3c2 FOREIGN KEY (c2) REFERENCES t3(c2),
CONSTRAINT t3c3 FOREIGN KEY (c3) REFERENCES t3p(c2));
CREATE TABLE t4c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
CONSTRAINT t4c2 FOREIGN KEY (c2) REFERENCES t4(c2),
CONSTRAINT t4c3 FOREIGN KEY (c3) REFERENCES t4p(c2));
CREATE TABLE t5c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
CONSTRAINT t5c2 FOREIGN KEY (c2) REFERENCES t5(c2),
CONSTRAINT t5c3 FOREIGN KEY (c3) REFERENCES t5p(c2));
CREATE TABLE t6c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
CONSTRAINT t6c2 FOREIGN KEY (c2) REFERENCES t6(c2),
CONSTRAINT t6c3 FOREIGN KEY (c3) REFERENCES t6p(c2));
--echo #
--echo # Remove t2, try to discard t3 & t4 already discarded
--echo #
--source include/shutdown_mysqld.inc
--remove_file $MYSQLD_DATADIR/test/t2.ibd
--source include/start_mysqld.inc
--error ER_ROW_IS_REFERENCED_2
ALTER TABLE t3 DISCARD TABLESPACE;
SHOW WARNINGS;
--error ER_ROW_IS_REFERENCED_2
ALTER TABLE t4 DISCARD TABLESPACE;
SHOW WARNINGS;
--error ER_ROW_IS_REFERENCED_2
ALTER TABLE t5 DISCARD TABLESPACE;
SHOW WARNINGS;
--echo #
--echo # ALTER TABLE __ DROP FOREIGN KEY
--echo #
ALTER TABLE t1c DROP FOREIGN KEY t1c2, ALGORITHM=INPLACE;
ALTER TABLE t2c DROP FOREIGN KEY t2c2, ALGORITHM=COPY;
ALTER TABLE t3c DROP FOREIGN KEY t3c2, ALGORITHM=INPLACE;
ALTER TABLE t4c DROP FOREIGN KEY t4c2, ALGORITHM=COPY;
ALTER TABLE t5c DROP FOREIGN KEY t5c2, ALGORITHM=INPLACE;
ALTER TABLE t6c DROP FOREIGN KEY t6c2, ALGORITHM=COPY;
--echo #
--echo # Cleanup
--echo #
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE t5;
DROP TABLE t6;
DROP TABLE t1c;
DROP TABLE t2c;
DROP TABLE t3c;
DROP TABLE t4c;
DROP TABLE t5c;
DROP TABLE t6c;
DROP TABLE t1p;
DROP TABLE t2p;
DROP TABLE t3p;
DROP TABLE t4p;
DROP TABLE t5p;
DROP TABLE t6p;
DROP TABLESPACE ts1;
--disable_query_log
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Tablespace for table `test`.`.*` is set as discarded");
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Tablespace .*, name '.*', file '.*' is missing");
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Cannot calculate statistics for table `test`\.`.*` because the \.ibd file is missing");
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Cannot delete tablespace [0-9]+ in DISCARD TABLESPACE: Tablespace not found");
call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Ignoring tablespace .* because it could not be opened");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot delete tablespace [0-9]+ because it is not found in the tablespace memory cache");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot open datafile for read-only:");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Could not find a valid tablespace file for");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Failed to find tablespace for table `test`.`.*` in the cache");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Operating system error number 2 in a file operation.");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Tablespace open failed for `test`\.`t`, ignored");
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* The error means the system cannot find the path specified.");
--enable_query_log
|
