Debian Codegraph @

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

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