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
|
call mtr.add_suppression("InnoDB: Tablespace for table .* is set as discarded\\.");
call mtr.add_suppression("InnoDB: Cannot calculate statistics for table .* because the .ibd file is missing. Please refer to .* for how to resolve the issue\\.");
call mtr.add_suppression("InnoDB: Page 0 at offset 0 looks corrupted in file");
call mtr.add_suppression("InnoDB: Page for tablespace ");
call mtr.add_suppression("InnoDB: Invalid FSP_SPACE_FLAGS=0x");
call mtr.add_suppression("InnoDB: Unknown index id .* on page");
call mtr.add_suppression("InnoDB: Operating system error number");
call mtr.add_suppression("InnoDB: Error number \\d+ means");
call mtr.add_suppression("InnoDB: Cannot open datafile .*t1\\.ibd");
call mtr.add_suppression("InnoDB: Ignoring tablespace for test/t1 ");
call mtr.add_suppression("InnoDB: Cannot save statistics for table `test`\\.`t1` because the \\.ibd file is missing");
call mtr.add_suppression("InnoDB: Database page corruption on disk or a failed read of file '.*ibdata1' page");
call mtr.add_suppression("InnoDB: File '.*ibdata1' is corrupted");
FLUSH TABLES;
SET SESSION innodb_strict_mode=1;
CREATE TABLE t1 (c1 INT) ENGINE = Innodb
ROW_FORMAT=COMPRESSED;
INSERT INTO t1 VALUES (1), (2), (3), (4);
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = Innodb
ROW_FORMAT=COMPRESSED;
INSERT INTO t1 VALUES (1);
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,ib_import_before_commit_crash";
SELECT * FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Lost connection to server during query
SET SESSION debug_dbug="+d,ib_import_before_checkpoint_crash";
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Lost connection to server during query
unlink: t1.ibd
unlink: t1.cfg
# Restart and reconnect to the server
DROP TABLE t1;
SET SESSION innodb_strict_mode=1;
CREATE TABLE t1 (c1 INT) ENGINE = Innodb
ROW_FORMAT=COMPRESSED;
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
SELECT COUNT(*) FROM t1;
COUNT(*)
4
INSERT INTO t1 VALUES(400), (500), (600);
SELECT * FROM t1;
c1
1
2
3
4
400
500
600
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = Innodb
ROW_FORMAT=COMPRESSED;
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
restore: t1 .ibd and .cfg files
SET @saved_debug_dbug = @@SESSION.debug_dbug;
SET SESSION debug_dbug="+d,ib_import_internal_error";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Internal error: While updating the <space, root page number> of index GEN_CLUST_INDEX - Generic error
SET SESSION debug_dbug=@saved_debug_dbug;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = Innodb
ROW_FORMAT=COMPRESSED;
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,ib_import_reset_space_and_lsn_failure";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Internal error: Cannot reset LSNs in table `test`.`t1` : Too many concurrent transactions
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug=@saved_debug_dbug;
SET SESSION debug_dbug="+d,ib_import_open_tablespace_failure";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Got error 42 'Tablespace not found' from ./test/t1.ibd
SET SESSION debug_dbug=@saved_debug_dbug;
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,ib_import_check_bitmap_failure";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Index for table 't1' is corrupt; try to repair it
SET SESSION debug_dbug=@saved_debug_dbug;
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,ib_import_cluster_root_adjust_failure";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Index for table 't1' is corrupt; try to repair it
SET SESSION debug_dbug=@saved_debug_dbug;
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,ib_import_cluster_failure";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Index for table 't1' is corrupt; try to repair it
SET SESSION debug_dbug=@saved_debug_dbug;
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,ib_import_sec_root_adjust_failure";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Index for table 't1' is corrupt; try to repair it
SET SESSION debug_dbug=@saved_debug_dbug;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
DROP TABLE t1;
CREATE TABLE t1 (
c1 BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c2 BIGINT,
c3 VARCHAR(2048),
c4 VARCHAR(2048),
INDEX idx1(c2),
INDEX idx2(c3(512)),
INDEX idx3(c4(512))) Engine=InnoDB
ROW_FORMAT=COMPRESSED;
connect purge_control,localhost,root;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connection default;
INSERT INTO t1
SELECT 1 + seq, 1 + (seq MOD 4),
REPEAT(SUBSTR('abcd', 1 + (seq MOD 4), 1), 2048),
REPEAT(SUBSTR('abcd', 1 + (seq MOD 4), 1), 2048)
FROM seq_0_to_127;
DELETE FROM t1 WHERE c2 = 1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c2 = c2 + c1;
UPDATE t1 SET c3 = REPEAT("c2", 1024);
UPDATE t1 SET c4 = REPEAT("c4", 1024);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` bigint(20) NOT NULL AUTO_INCREMENT,
`c2` bigint(20) DEFAULT NULL,
`c3` varchar(2048) DEFAULT NULL,
`c4` varchar(2048) DEFAULT NULL,
PRIMARY KEY (`c1`),
KEY `idx1` (`c2`),
KEY `idx2` (`c3`(512)),
KEY `idx3` (`c4`(512))
) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
connection purge_control;
COMMIT;
disconnect purge_control;
connection default;
DROP TABLE t1;
CREATE TABLE t1 (
c1 BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c2 BIGINT,
c3 VARCHAR(2048),
c4 VARCHAR(2048),
INDEX idx1(c2),
INDEX idx2(c3(512)),
INDEX idx3(c4(512))) Engine=InnoDB
ROW_FORMAT=COMPRESSED;
SELECT c1, c2 FROM t1;
c1 c2
ALTER TABLE t1 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
SELECT c1,c2 FROM t1;
c1 c2
2 32
3 48
4 64
6 92
7 108
8 124
10 152
11 168
12 184
14 212
15 228
16 244
18 272
19 288
20 304
22 332
23 348
24 364
26 392
27 408
28 424
30 452
31 468
32 484
34 512
35 528
36 544
38 572
39 588
40 604
42 632
43 648
44 664
46 692
47 708
48 724
50 752
51 768
52 784
54 812
55 828
56 844
58 872
59 888
60 904
62 932
63 948
64 964
66 992
67 1008
68 1024
70 1052
71 1068
72 1084
74 1112
75 1128
76 1144
78 1172
79 1188
80 1204
82 1232
83 1248
84 1264
86 1292
87 1308
88 1324
90 1352
91 1368
92 1384
94 1412
95 1428
96 1444
98 1472
99 1488
100 1504
102 1532
103 1548
104 1564
106 1592
107 1608
108 1624
110 1652
111 1668
112 1684
114 1712
115 1728
116 1744
118 1772
119 1788
120 1804
122 1832
123 1848
124 1864
126 1892
127 1908
128 1924
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` bigint(20) NOT NULL AUTO_INCREMENT,
`c2` bigint(20) DEFAULT NULL,
`c3` varchar(2048) DEFAULT NULL,
`c4` varchar(2048) DEFAULT NULL,
PRIMARY KEY (`c1`),
KEY `idx1` (`c2`),
KEY `idx2` (`c3`(512)),
KEY `idx3` (`c4`(512))
) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=COMPRESSED
DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 VARCHAR(1024), c3 BLOB) ENGINE = Innodb
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
INSERT INTO t1
SELECT 100, REPEAT('Karanbir', 128), REPEAT('Ajeeth', 1200)
FROM seq_1_to_256;
FLUSH TABLES t1 FOR EXPORT;
backup: t1
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 VARCHAR(1024), c3 BLOB) ENGINE = Innodb
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,ib_import_trigger_corruption_1";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Internal error: Cannot reset LSNs in table `test`.`t1` : Data structure corruption
SET SESSION debug_dbug=@saved_debug_dbug;
DROP TABLE t1;
unlink: t1.ibd
unlink: t1.cfg
CREATE TABLE t1 (c1 INT, c2 VARCHAR(1024), c3 BLOB) ENGINE = Innodb
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
restore: t1 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
Got one of the listed errors
DROP TABLE t1;
unlink: t1.ibd
unlink: t1.cfg
CREATE TABLE t1 (c1 INT, c2 VARCHAR(1024), c3 BLOB) ENGINE = Innodb
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,ib_import_trigger_corruption_2";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Index corrupt: Externally stored column(5) has a reference length of 19 in the cluster index GEN_CLUST_INDEX
SET SESSION debug_dbug=@saved_debug_dbug;
DROP TABLE t1;
unlink: t1.ibd
unlink: t1.cfg
CREATE TABLE t1 (c1 INT, c2 VARCHAR(1024), c3 BLOB) ENGINE = Innodb
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,ib_import_trigger_corruption_3";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Index for table 't1' is corrupt; try to repair it
SET SESSION debug_dbug=@saved_debug_dbug;
DROP TABLE t1;
unlink: t1.ibd
unlink: t1.cfg
CREATE TABLE t1 (c1 INT, c2 VARCHAR(1024), c3 BLOB) ENGINE = Innodb
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
SET SESSION debug_dbug="+d,ib_import_create_index_failure_1";
ALTER TABLE t1 ADD INDEX idx(c1);
SET SESSION debug_dbug=@saved_debug_dbug;
DROP TABLE t1;
unlink: t1.ibd
unlink: t1.cfg
CREATE TABLE t1 (c1 INT, c2 VARCHAR(1024), c3 BLOB) ENGINE = Innodb
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,fil_space_create_failure";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Got error 11 'Generic error' from ./test/t1.ibd
SET SESSION debug_dbug=@saved_debug_dbug;
DROP TABLE t1;
unlink: t1.ibd
unlink: t1.cfg
CREATE TABLE t1 (c1 INT, c2 VARCHAR(1024), c3 BLOB) ENGINE = Innodb
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,dict_tf_to_fsp_flags_failure";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Got error 37 'Data structure corruption' from ./test/t1.ibd
SET SESSION debug_dbug=@saved_debug_dbug;
DROP TABLE t1;
unlink: t1.ibd
unlink: t1.cfg
CREATE TABLE t1 (c1 INT, c2 VARCHAR(1024), c3 BLOB) ENGINE = Innodb
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE t1 DISCARD TABLESPACE;
SELECT COUNT(*) FROM t1;
ERROR HY000: Tablespace has been discarded for table `t1`
restore: t1 .ibd and .cfg files
SET SESSION debug_dbug="+d,fsp_flags_is_valid_failure";
ALTER TABLE t1 IMPORT TABLESPACE;
ERROR HY000: Index for table 't1' is corrupt; try to repair it
SET SESSION debug_dbug=@saved_debug_dbug;
DROP TABLE t1;
unlink: t1.ibd
unlink: t1.cfg
|
