Debian Codegraph @

mysql-test/suite/innodb/t/innodb_corrupt_bit.test

raw
1 #
2 # Test for persistent corrupt bit for corrupted index and table
3 #
4 --source include/no_valgrind_without_big.inc
5 # This test needs debug server
6 --source include/have_debug.inc
7 --source include/not_hypergraph.inc # Depends on using indexes, which are not supported.
8
9 --disable_query_log
10 call mtr.add_suppression("Flagged corruption of.* in table .* in CHECK TABLE");
11 --enable_query_log
12
13 set names utf8;
14
15 CREATE TABLE corrupt_bit_test_ā(
16 a INT AUTO_INCREMENT PRIMARY KEY,
17 b CHAR(100),
18 c INT,
19 z INT,
20 INDEX idx(b))
21 ENGINE=InnoDB STATS_PERSISTENT=0;
22
23 INSERT INTO corrupt_bit_test_ā VALUES(0,'x',1, 1);
24
25 CREATE UNIQUE INDEX idxā ON corrupt_bit_test_ā(c, b);
26
27 CREATE UNIQUE INDEX idxē ON corrupt_bit_test_ā(z, b);
28
29 SELECT * FROM corrupt_bit_test_ā;
30
31 INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+1,z+1 FROM corrupt_bit_test_ā;
32
33 select count(*) from corrupt_bit_test_ā;
34
35 # This will flag all secondary indexes corrupted
36 SET SESSION debug="+d,dict_set_index_corrupted";
37 check table corrupt_bit_test_ā;
38 SET SESSION debug="-d,dict_set_index_corrupted";
39 # Cannot create new indexes while corrupted indexes exist
40 --error ER_CHECK_NO_SUCH_TABLE
41 CREATE INDEX idx3 ON corrupt_bit_test_ā(b, c);
42 --error ER_CHECK_NO_SUCH_TABLE
43 CREATE INDEX idx4 ON corrupt_bit_test_ā(b, z);
44
45 # This selection intend to use the corrupted index. Expect to fail
46 -- error ER_INDEX_CORRUPT
47 select c from corrupt_bit_test_ā;
48
49 -- error ER_INDEX_CORRUPT
50 select z from corrupt_bit_test_ā;
51
52 show warnings;
53
54 # Since corrupted index is a secondary index, we only disable such
55 # index and allow other DML to proceed
56 insert into corrupt_bit_test_ā values (10001, "a", 20001, 20001);
57
58 # This does not use the corrupted index, expect to succeed
59 select * from corrupt_bit_test_ā use index(primary) where a = 10001;
60
61 # Some more DMLs
62 begin;
63 insert into corrupt_bit_test_ā values (10002, "a", 20002, 20002);
64 delete from corrupt_bit_test_ā where a = 10001;
65 insert into corrupt_bit_test_ā values (10001, "a", 20001, 20001);
66 rollback;
67
68 drop index idxā on corrupt_bit_test_ā;
69
70 check table corrupt_bit_test_ā;
71
72 set names utf8;
73
74 -- error ER_INDEX_CORRUPT
75 select z from corrupt_bit_test_ā;
76
77 # Drop the corrupted index
78 drop index idxē on corrupt_bit_test_ā;
79
80 # Cannot create new indexes while a corrupt index exists.
81 --error ER_CHECK_NO_SUCH_TABLE
82 CREATE INDEX idx3 ON corrupt_bit_test_ā(b, c);
83 --error ER_CHECK_NO_SUCH_TABLE
84 CREATE INDEX idx4 ON corrupt_bit_test_ā(b, z);
85
86 drop index idx on corrupt_bit_test_ā;
87
88 # Now that there exist no corrupted indexes, we can create new indexes.
89 CREATE INDEX idx3 ON corrupt_bit_test_ā(b, c);
90 CREATE INDEX idx4 ON corrupt_bit_test_ā(b, z);
91
92 # Now select back to normal
93 select z from corrupt_bit_test_ā limit 10;
94
95 # Drop table
96 drop table corrupt_bit_test_ā;
97
98 --disable_query_log
99 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Load table `corrupted`\\.`t[16]` failed, the table contains a corrupted clustered index");
100 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Cannot open table corrupted/t[16]");
101 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Failed to find tablespace for table `corrupted`\\.`t[56]` in the cache");
102 --enable_query_log
103
104 CREATE DATABASE corrupted;
105 use corrupted;
106
107 CREATE TABLE t1 (
108 a INT NOT NULL PRIMARY KEY,
109 b INT,
110 c CHAR(10),
111 v VARCHAR(100),
112 t TEXT,
113 p POINT NOT NULL);
114
115 CREATE TABLE t2 LIKE t1;
116 CREATE TEMPORARY TABLE t3 LIKE t2;
117
118 CREATE FULLTEXT INDEX ft ON t2(t);
119 CREATE INDEX idx_c ON t3(c);
120
121 INSERT INTO t1 VALUES (1, 2, 'aaa', 'abcdefghij', 'This is a', st_pointfromtext('POINT(0 0)')),
122 (2, 4, 'bb', 'qwerty', 'test case', st_pointfromtext('POINT(1 1)')),
123 (3, 6, 'ccccc', 'poiuy', 'for corrupted index', st_pointfromtext('POINT(2 2)'));
124
125 INSERT INTO t2 SELECT * FROM t1;
126 INSERT INTO t3 SELECT * FROM t1;
127
128 SELECT count(*) FROM t1;
129 SELECT count(*) FROM t2;
130 SELECT count(*) FROM t3;
131
132 --echo # Marking clustered index as corrupted results in ER_NO_SUCH_TABLE
133 set debug = "+d, dict_set_clust_index_corrupted";
134 CHECK TABLE t1;
135 set debug = "-d, dict_set_clust_index_corrupted";
136
137 --error ER_NO_SUCH_TABLE
138 CREATE INDEX b ON t1(b);
139
140 --echo # Mark the secondary index as corrupted
141 set debug = "+d, dict_set_index_corrupted";
142 CHECK TABLE t2;
143 set debug = "-d, dict_set_index_corrupted";
144
145 --error ER_TABLE_HAS_NO_FT
146 SELECT * FROM t2 WHERE MATCH(t) AGAINST('corrupted');
147 SELECT a, b, c FROM t2 WHERE ST_Equals(p, st_pointfromtext('POINT(2 2)'));
148
149 set debug = "+d, dict_set_index_corrupted";
150 CHECK TABLE t3;
151 set debug = "-d, dict_set_index_corrupted";
152
153 --error ER_INDEX_CORRUPT
154 SELECT c FROM t3 WHERE c = 'bb';
155
156 --source include/restart_mysqld.inc
157
158 --echo # Make sure the corrupted indexes are still corrupted
159 use corrupted;
160 SHOW TABLES;
161
162 --error ER_CHECK_NO_SUCH_TABLE
163 CREATE INDEX b ON t1(b);
164
165 --error ER_TABLE_HAS_NO_FT
166 SELECT t FROM t2 WHERE MATCH(t) AGAINST('corrupted');
167
168 # Re-create the fulltext indee
169 DROP INDEX ft ON t2;
170
171 --error ER_CHECK_NO_SUCH_TABLE
172 ALTER TABLE t2 ADD FULLTEXT INDEX(t);
173 --echo # Have to use ALTER TABLE ... COPY, in case 'Incorrect key file for table...'
174 --echo # would be reported
175 ALTER TABLE t2 ADD FULLTEXT INDEX(t), ALGORITHM=COPY;
176 --echo # This should be fine
177 SELECT t FROM t2 WHERE MATCH(t) AGAINST('corrupted');
178
179 CHECK TABLE t1;
180 CHECK TABLE t2;
181
182 --echo # We can drop the corrupted table t1 directly here
183 DROP TABLE t1, t2;
184
185 use corrupted;
186
187 CREATE TABLE t3 (
188 a INT NOT NULL PRIMARY KEY,
189 b INT,
190 c CHAR(10),
191 v VARCHAR(100),
192 t TEXT,
193 p POINT NOT NULL SRID 0);
194
195 CREATE INDEX idx_b ON t3(b);
196
197 INSERT INTO t3 VALUES (1, 2, 'aaa', 'abcdefghij', 'This is a', st_pointfromtext('POINT(0 0)')),
198 (2, 4, 'bb', 'qwerty', 'test case', st_pointfromtext('POINT(1 1)')),
199 (3, 6, 'ccccc', 'poiuy', 'for corrupted index', st_pointfromtext('POINT(2 2)'));
200
201 set debug = "+d, dict_set_index_corrupted";
202 CHECK TABLE t3;
203 set debug = "-d, dict_set_index_corrupted";
204
205 --error ER_INDEX_CORRUPT
206 SELECT b FROM t3 WHERE b > 2;
207
208 CREATE TABLE t4 LIKE t3;
209 CREATE INDEX idx_c ON t4(c);
210 CREATE INDEX idx_v ON t4(v);
211
212 INSERT INTO t4 SELECT * FROM t3;
213
214 set debug = "+d, dict_set_index_corrupted";
215 CHECK TABLE t4;
216 set debug = "-d, dict_set_index_corrupted";
217
218 --error ER_INDEX_CORRUPT
219 SELECT b FROM t4 WHERE b > 3;
220 show warnings;
221 --error ER_INDEX_CORRUPT
222 SELECT c FROM t4 WHERE c = 'bb';
223 --error ER_INDEX_CORRUPT
224 SELECT v FROM t4 WHERE v = 'abcdefg';
225
226 CREATE TABLE t5 LIKE t3;
227 CREATE SPATIAL INDEX idx_p ON t5(p);
228
229 INSERT INTO t5 SELECT * FROM t3;
230
231 set debug = "+d, dict_set_index_corrupted";
232 CHECK TABLE t5;
233 set debug = "-d, dict_set_index_corrupted";
234
235 --error ER_INDEX_CORRUPT
236 SELECT b FROM t5 WHERE b > 5;
237
238 --source include/kill_and_restart_mysqld.inc
239
240 use corrupted;
241
242 --echo # Check that all corrupted indexes are still marked as corrupted,
243 --echo # and non-corrupted indexes are still good.
244 CHECK TABLE t3;
245 --error ER_INDEX_CORRUPT
246 SELECT b FROM t3 WHERE b > 2;
247
248 CHECK TABLE t4;
249 --error ER_INDEX_CORRUPT
250 SELECT b FROM t4 WHERE b > 3;
251 --error ER_INDEX_CORRUPT
252 SELECT c FROM t4 WHERE c = 'bb';
253 --error ER_INDEX_CORRUPT
254 SELECT v FROM t4 WHERE v = 'abcdefg';
255
256 CHECK TABLE t5;
257 --error ER_INDEX_CORRUPT
258 SELECT b FROM t5 WHERE b > 5;
259
260 --echo # Let's just fix corrupted index for t5
261 DROP INDEX idx_b ON t5;
262 DROP INDEX idx_p ON t5;
263 CREATE INDEX idx_b ON t5(b);
264
265 --source include/restart_mysqld.inc
266
267 use corrupted;
268
269 CHECK TABLE t3;
270 --error ER_INDEX_CORRUPT
271 SELECT b FROM t3 WHERE b > 2;
272
273 CHECK TABLE t4;
274 --error ER_INDEX_CORRUPT
275 SELECT b FROM t4 WHERE b > 3;
276 --error ER_INDEX_CORRUPT
277 SELECT c FROM t4 WHERE c = 'bb';
278 --error ER_INDEX_CORRUPT
279 SELECT v FROM t4 WHERE v = 'abcdefg';
280
281 CHECK TABLE t5;
282 SELECT b FROM t5 WHERE b > 5;
283
284 --echo # Based on these tables, let's test more on checkpoint
285 CREATE TABLE t6 LIKE t3;
286
287 CREATE INDEX idx_c ON t6(c);
288
289 INSERT INTO t6 SELECT * FROM t3;
290
291 --echo # Corrupt new table indexes
292 set debug = "+d, dict_set_index_corrupted";
293 CHECK TABLE t6;
294 set debug = "-d, dict_set_index_corrupted";
295
296 SET @start_global_value = @@global.innodb_log_checkpoint_now;
297 SELECT @start_global_value;
298
299 --echo # Invoke a checkpoint, which would persist the corrupted bits
300 --echo # of t6 into DD Table Buffer
301 set global innodb_log_checkpoint_now=ON;
302
303 --echo # Then mark the clustered index as corrupted, make sure that new
304 --echo # corruption bits would take effect as well
305 set debug = "+d, dict_set_clust_index_corrupted";
306 CHECK TABLE t6;
307 set debug = "-d, dict_set_clust_index_corrupted";
308
309 --echo # Corrupt old table indexes
310 set debug = "+d, dict_set_index_corrupted";
311 CHECK TABLE t5;
312 set debug = "-d, dict_set_index_corrupted";
313
314 SELECT @start_global_value;
315 SET @@global.innodb_log_checkpoint_now = @start_global_value;
316 SELECT @@global.innodb_log_checkpoint_now;
317
318 --echo # This crash would leave latest corruption bits un-persisted
319 --source include/kill_and_restart_mysqld.inc
320
321 use corrupted;
322
323 CHECK TABLE t3;
324
325 CHECK TABLE t4;
326
327 CHECK TABLE t5;
328 --error ER_INDEX_CORRUPT
329 SELECT b FROM t5 WHERE b > 5;
330
331 CHECK TABLE t6;
332 --error ER_NO_SUCH_TABLE
333 CREATE INDEX idx_v ON t6(v);
334
335 --echo # Invoke checkpoint to persist the latest corruption bits again
336 SET @start_global_value = @@global.innodb_log_checkpoint_now;
337 SELECT @start_global_value;
338 set global innodb_log_checkpoint_now=ON;
339 SELECT @start_global_value;
340 SET @@global.innodb_log_checkpoint_now = @start_global_value;
341 SELECT @@global.innodb_log_checkpoint_now;
342
343 --source include/restart_mysqld.inc
344
345 use corrupted;
346
347 --echo # After a shutdown and restart, the corrupted bits should be persisted
348 --echo # to DD Table Buffer, and we can get them correctly.
349 CHECK TABLE t5;
350 CHECK TABLE t6;
351
352 --echo # An IN-PLACE 'ALTER TABLE' would not recover the table
353 ALTER TABLE t5 CHANGE c c1 CHAR(10), ALGORITHM = INPLACE;
354 CHECK TABLE t5;
355
356 --echo # An 'ALTER TABLE' requiring rebuild would recover the table
357 ALTER TABLE t5 ADD COLUMN d INT NOT NULL, ALGORITHM=INPLACE;
358 CHECK TABLE t5;
359 SELECT a, b, c1, v, t, st_astext(p) FROM t5 WHERE b > 2;
360
361 --echo # Re-create the table should recover the table
362 DROP TABLE t6;
363 CREATE TABLE t6 LIKE t3;
364 INSERT INTO t6 SELECT * FROM t3;
365
366 CHECK TABLE t6;
367
368 --source include/kill_and_restart_mysqld.inc
369
370 use corrupted;
371
372 CHECK TABLE t5;
373 SELECT a, b, c1, v, t, st_astext(p) FROM t5 WHERE b > 2;
374 CHECK TABLE t6;
375
376 --source include/restart_mysqld.inc
377
378 use corrupted;
379
380 CHECK TABLE t5;
381 CHECK TABLE t6;
382
383 # Uncomment these after WL#6795.
384 #--echo # Truncate table should recover the corrupted indexes
385 #set debug = "+d, dict_set_sec_index_corrupted";
386 #CHECK TABLE t5;
387 #set debug = "-d, dict_set_sec_index_corrupted";
388
389 #--error ER_INDEX_CORRUPT
390 #SELECT b FROM t5 WHERE b > 5;
391
392 #TRUNCATE TABLE t5;
393 #CHECK TABLE t5;
394
395 #INSERT INTO t5 SELECT * FROM t3;
396
397 #--source include/restart_mysqld.inc
398
399 #use corrupted;
400 #CHECK TABLE t5;
401 #SELECT b FROM t5 WHERE b > 5;
402
403 DROP TABLE t3, t4, t5, t6;
404 DROP DATABASE corrupted;