EVOLUTION-MANAGER

Edit File: partition_repair_myisam.result

# REPAIR USE_FRM is not implemented for partitioned tables. # Test what happens if we exchange a crashed partition with a table SHOW VARIABLES LIKE 'myisam_recover_options'; Variable_name Value myisam_recover_options OFF CREATE TABLE t (a INT, KEY (a)) ENGINE=MyISAM; CREATE TABLE tp (a INT, KEY (a)) ENGINE=MyISAM PARTITION BY RANGE (a) (PARTITION pCrashed VALUES LESS THAN (15), PARTITION pMAX VALUES LESS THAN MAXVALUE); INSERT INTO t VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); INSERT INTO tp VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); FLUSH TABLES; # replacing tp#P#pCrashed.MYI with a corrupt + unclosed one created by doing: # 'create table t1 (a int key(a))' head -c1024 t1.MYI > corrupt_t1.MYI CHECK TABLE tp; Table Op Msg_type Msg_text test.tp check warning 1 client is using or hasn't closed the table properly test.tp check error Size of indexfile is: 1024 Should be: 2048 test.tp check warning Size of datafile is: 77 Should be: 7 test.tp check error Partition pCrashed returned error test.tp check error Corrupt ALTER TABLE tp EXCHANGE PARTITION pCrashed WITH TABLE t; ERROR HY000: Table './test/tp#P#pCrashed' is marked as crashed and should be repaired REPAIR TABLE tp; Table Op Msg_type Msg_text test.tp repair warning Number of rows changed from 1 to 11 test.tp repair status OK CHECK TABLE tp; Table Op Msg_type Msg_text test.tp check status OK ALTER TABLE tp EXCHANGE PARTITION pCrashed WITH TABLE t; CHECK TABLE t; Table Op Msg_type Msg_text test.t check status OK CHECK TABLE tp; Table Op Msg_type Msg_text test.tp check status OK FLUSH TABLES; CHECK TABLE t; Table Op Msg_type Msg_text test.t check warning 1 client is using or hasn't closed the table properly test.t check error Size of indexfile is: 1024 Should be: 2048 test.t check warning Size of datafile is: 77 Should be: 7 test.t check error Corrupt ALTER TABLE tp EXCHANGE PARTITION pCrashed WITH TABLE t; ERROR HY000: Table './test/t' is marked as crashed and should be repaired REPAIR TABLE t; Table Op Msg_type Msg_text test.t repair warning Number of rows changed from 1 to 11 test.t repair status OK CHECK TABLE t; Table Op Msg_type Msg_text test.t check status OK ALTER TABLE tp EXCHANGE PARTITION pCrashed WITH TABLE t; CHECK TABLE tp; Table Op Msg_type Msg_text test.tp check status OK CHECK TABLE t; Table Op Msg_type Msg_text test.t check status OK DROP TABLE t, tp; # test of non partitioned myisam for reference CREATE TABLE t1_will_crash (a INT, KEY (a)) ENGINE=MyISAM; INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); FLUSH TABLES; # replacing t1.MYI with a corrupt + unclosed one created by doing: # 'create table t1 (a int key(a))' head -c1024 t1.MYI > corrupt_t1.MYI CHECK TABLE t1_will_crash; Table Op Msg_type Msg_text test.t1_will_crash check warning 1 client is using or hasn't closed the table properly test.t1_will_crash check error Size of indexfile is: 1024 Should be: 2048 test.t1_will_crash check warning Size of datafile is: 77 Should be: 7 test.t1_will_crash check error Corrupt REPAIR TABLE t1_will_crash; Table Op Msg_type Msg_text test.t1_will_crash repair warning Number of rows changed from 1 to 11 test.t1_will_crash repair status OK SELECT * FROM t1_will_crash; a 1 2 3 4 5 6 7 8 9 10 11 DROP TABLE t1_will_crash; # test of check/repair of a damaged partition's MYI-file CREATE TABLE t1_will_crash (a INT, KEY (a)) ENGINE=MyISAM PARTITION BY HASH (a) PARTITIONS 3; INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); FLUSH TABLES; # test with CHECK/REPAIR TABLE # replacing t1#P#p1.MYI with a corrupt + unclosed one created by doing: # 'create table t1 (a int key(a)) partition by hash (a) partitions 3' # head -c1024 t1#P#p1.MYI > corrupt_t1#P#p1.MYI CHECK TABLE t1_will_crash; Table Op Msg_type Msg_text test.t1_will_crash check warning 1 client is using or hasn't closed the table properly test.t1_will_crash check error Size of indexfile is: 1024 Should be: 2048 test.t1_will_crash check warning Size of datafile is: 28 Should be: 7 test.t1_will_crash check error Partition p1 returned error test.t1_will_crash check error Corrupt REPAIR TABLE t1_will_crash; Table Op Msg_type Msg_text test.t1_will_crash repair warning Number of rows changed from 1 to 4 test.t1_will_crash repair status OK SELECT * FROM t1_will_crash; a 1 2 3 4 5 6 7 8 9 10 11 FLUSH TABLES; # test with ALTER TABLE ... CHECK/REPAIR PARTITION # replacing t1_will_crash#P#p1.MYI with a corrupt + unclosed one ALTER TABLE t1_will_crash CHECK PARTITION p0, p2; Table Op Msg_type Msg_text test.t1_will_crash check status OK ALTER TABLE t1_will_crash CHECK PARTITION p0, p1; Table Op Msg_type Msg_text test.t1_will_crash check warning 1 client is using or hasn't closed the table properly test.t1_will_crash check error Size of indexfile is: 1024 Should be: 2048 test.t1_will_crash check warning Size of datafile is: 28 Should be: 7 test.t1_will_crash check error Partition p1 returned error test.t1_will_crash check error Corrupt ALTER TABLE t1_will_crash CHECK PARTITION p1, p2; Table Op Msg_type Msg_text test.t1_will_crash check warning Table is marked as crashed test.t1_will_crash check warning 1 client is using or hasn't closed the table properly test.t1_will_crash check error Size of indexfile is: 1024 Should be: 2048 test.t1_will_crash check warning Size of datafile is: 28 Should be: 7 test.t1_will_crash check error Partition p1 returned error test.t1_will_crash check error Corrupt ALTER TABLE t1_will_crash REPAIR PARTITION p0, p2; Table Op Msg_type Msg_text test.t1_will_crash repair status OK ALTER TABLE t1_will_crash REPAIR PARTITION p0, p1; Table Op Msg_type Msg_text test.t1_will_crash repair warning Number of rows changed from 1 to 4 test.t1_will_crash repair status OK SELECT * FROM t1_will_crash; a 1 2 3 4 5 6 7 8 9 10 11 DROP TABLE t1_will_crash; # test of check/repair of a damaged subpartition's MYI-file CREATE TABLE t1_will_crash (a INT, KEY (a)) ENGINE=MyISAM PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (7), PARTITION p1 VALUES LESS THAN MAXVALUE); INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); SELECT * FROM t1_will_crash; a 1 2 3 4 5 6 7 8 9 10 11 FLUSH TABLES; # test with CHECK/REPAIR TABLE # replacing t1_will_crash#P#p1#SP#p1sp0.MYI with a corrupt + unclosed one CHECK TABLE t1_will_crash; Table Op Msg_type Msg_text test.t1_will_crash check warning 1 client is using or hasn't closed the table properly test.t1_will_crash check error Size of indexfile is: 1024 Should be: 2048 test.t1_will_crash check warning Size of datafile is: 14 Should be: 7 test.t1_will_crash check error Subpartition p1sp0 returned error test.t1_will_crash check error Corrupt REPAIR TABLE t1_will_crash; Table Op Msg_type Msg_text test.t1_will_crash repair warning Number of rows changed from 1 to 2 test.t1_will_crash repair status OK SELECT * FROM t1_will_crash; a 1 2 3 4 5 6 7 8 9 10 11 FLUSH TABLES; # test with ALTER TABLE ... CHECK/REPAIR PARTITION # replacing t1_will_crash#P#p1#SP#p1sp0.MYI with a corrupt + unclosed one ALTER TABLE t1_will_crash CHECK PARTITION p0; Table Op Msg_type Msg_text test.t1_will_crash check status OK ALTER TABLE t1_will_crash CHECK PARTITION all; Table Op Msg_type Msg_text test.t1_will_crash check warning 1 client is using or hasn't closed the table properly test.t1_will_crash check error Size of indexfile is: 1024 Should be: 2048 test.t1_will_crash check warning Size of datafile is: 14 Should be: 7 test.t1_will_crash check error Subpartition p1sp0 returned error test.t1_will_crash check error Corrupt ALTER TABLE t1_will_crash CHECK PARTITION p1; Table Op Msg_type Msg_text test.t1_will_crash check warning Table is marked as crashed test.t1_will_crash check warning 1 client is using or hasn't closed the table properly test.t1_will_crash check error Size of indexfile is: 1024 Should be: 2048 test.t1_will_crash check warning Size of datafile is: 14 Should be: 7 test.t1_will_crash check error Subpartition p1sp0 returned error test.t1_will_crash check error Corrupt ALTER TABLE t1_will_crash REPAIR PARTITION p0; Table Op Msg_type Msg_text test.t1_will_crash repair status OK ALTER TABLE t1_will_crash REPAIR PARTITION p0, p1; Table Op Msg_type Msg_text test.t1_will_crash repair warning Number of rows changed from 1 to 2 test.t1_will_crash repair status OK SELECT * FROM t1_will_crash; a 1 2 3 4 5 6 7 8 9 10 11 DROP TABLE t1_will_crash; # test of check/repair of crashed partitions in variuos states CREATE TABLE t1_will_crash ( a VARCHAR(255), b INT, c LONGTEXT, PRIMARY KEY (a, b)) ENGINE=MyISAM PARTITION BY HASH (b) PARTITIONS 7; SELECT COUNT(*) FROM t1_will_crash; COUNT(*) 33 SELECT (b % 7) AS `partition`, COUNT(*) AS rows_cnt FROM t1_will_crash GROUP BY (b % 7); partition rows_cnt 0 2 1 5 2 5 3 5 4 4 5 4 6 8 SELECT (b % 7) AS `partition`, b, a, length(c) FROM t1_will_crash ORDER BY `partition`, b, a; partition b a length(c) 0 0 lost 64 0 7 z lost 64 1 1 abc 64 1 8 tuw 64 1 29 kkkkkkkkKkk 64 1 71 1 broken when head -c1024 on datafile 1024 1 71 eee 64 2 2 def 64 2 9 vxy 64 2 23 lll 64 2 30 2 crashed after _mi_mark_changed 64 2 79 ccc 64 3 3 ghi 64 3 10 aaa 64 3 17 nnn 64 3 24 3 crashed after write_record 64 3 73 ddd 64 4 4 pqr 64 4 11 bbb 64 4 18 4 crashed after flush_cached_blocks 64 4 67 fff 64 5 5 mno 64 5 19 mmm 64 5 40 5 still here since crash in next row in multirow insert? 64 5 89 a 64 6 6 jkl 64 6 13 ooo 64 6 27 6 row 7 (crash before completely written to datafile) 128 6 34 6 row 2 64 6 48 6 row 4 64 6 62 6 row 6 64 6 83 64 6 97 zzzzzZzzzzz 64 FLUSH TABLES; # truncating p0 to simulate an empty datafile (not recovered!) # replacing p1 with only the first 1024 bytes (not recovered!) # replacing p3 with a crashed one at the last row in first insert # (crashed right after *share->write_record()) # replacing p6 with a crashed MYD file (1) (splitted dynamic record) ANALYZE TABLE t1_will_crash; Table Op Msg_type Msg_text test.t1_will_crash analyze status OK OPTIMIZE TABLE t1_will_crash; Table Op Msg_type Msg_text test.t1_will_crash optimize info Found row block followed by deleted block test.t1_will_crash optimize warning Number of rows changed from 8 to 7 test.t1_will_crash optimize status OK CHECK TABLE t1_will_crash; Table Op Msg_type Msg_text test.t1_will_crash check error Size of datafile is: 0 Should be: 164 test.t1_will_crash check error Partition p0 returned error test.t1_will_crash check error Corrupt REPAIR TABLE t1_will_crash; Table Op Msg_type Msg_text test.t1_will_crash repair warning Number of rows changed from 2 to 0 test.t1_will_crash repair info Found block that points outside data file at 344 test.t1_will_crash repair warning Number of rows changed from 5 to 4 test.t1_will_crash repair warning Number of rows changed from 0 to 5 test.t1_will_crash repair status OK SELECT COUNT(*) FROM t1_will_crash; COUNT(*) 29 SELECT (b % 7) AS `partition`, COUNT(*) AS rows_cnt FROM t1_will_crash GROUP BY (b % 7); partition rows_cnt 1 4 2 5 3 5 4 4 5 4 6 7 SELECT (b % 7) AS `partition`, b, a, length(c) FROM t1_will_crash ORDER BY `partition`, b, a; partition b a length(c) 1 1 abc 64 1 8 tuw 64 1 29 kkkkkkkkKkk 64 1 71 eee 64 2 2 def 64 2 9 vxy 64 2 23 lll 64 2 30 2 crashed after _mi_mark_changed 64 2 79 ccc 64 3 3 ghi 64 3 10 aaa 64 3 17 nnn 64 3 24 3 crashed after write_record 64 3 73 ddd 64 4 4 pqr 64 4 11 bbb 64 4 18 4 crashed after flush_cached_blocks 64 4 67 fff 64 5 5 mno 64 5 19 mmm 64 5 40 5 still here since crash in next row in multirow insert? 64 5 89 a 64 6 6 jkl 64 6 13 ooo 64 6 34 6 row 2 64 6 48 6 row 4 64 6 62 6 row 6 64 6 83 64 6 97 zzzzzZzzzzz 64 FLUSH TABLES; # # replacing p2 with crashed files (after _mi_mark_changed) ALTER TABLE t1_will_crash CHECK PARTITION p2; Table Op Msg_type Msg_text test.t1_will_crash check warning 1 client is using or hasn't closed the table properly test.t1_will_crash check status OK # crash was when index only marked as opened, no real corruption ALTER TABLE t1_will_crash CHECK PARTITION p2; Table Op Msg_type Msg_text test.t1_will_crash check status OK FLUSH TABLES; # # replacing p4 with updated but not closed index file ALTER TABLE t1_will_crash OPTIMIZE PARTITION p4; Table Op Msg_type Msg_text test.t1_will_crash optimize error Found key at page 2048 that points to record outside datafile test.t1_will_crash optimize error Partition p4 returned error test.t1_will_crash optimize status Operation failed ALTER TABLE t1_will_crash CHECK PARTITION p4; Table Op Msg_type Msg_text test.t1_will_crash check warning Table is marked as crashed and last repair failed test.t1_will_crash check warning 1 client is using or hasn't closed the table properly test.t1_will_crash check warning Size of datafile is: 368 Should be: 252 test.t1_will_crash check error Found 4 keys of 3 test.t1_will_crash check error Partition p4 returned error test.t1_will_crash check error Corrupt ALTER TABLE t1_will_crash REPAIR PARTITION p4; Table Op Msg_type Msg_text test.t1_will_crash repair warning Number of rows changed from 3 to 4 test.t1_will_crash repair status OK FLUSH TABLES; # # replacing p6 with a crashed MYD file (2) (splitted dynamic record) ALTER TABLE t1_will_crash CHECK PARTITION p6; Table Op Msg_type Msg_text test.t1_will_crash check warning Size of datafile is: 868 Should be: 604 test.t1_will_crash check error Unexpected byte: 0 at link: 340 test.t1_will_crash check error Partition p6 returned error test.t1_will_crash check error Corrupt ALTER TABLE t1_will_crash REPAIR PARTITION p6; Table Op Msg_type Msg_text test.t1_will_crash repair info Delete link points outside datafile at 340 test.t1_will_crash repair info Delete link points outside datafile at 340 test.t1_will_crash repair status OK SELECT (b % 7) AS `partition`, b, a, length(c) FROM t1_will_crash WHERE (b % 7) = 6 ORDER BY `partition`, b, a; partition b a length(c) 6 6 jkl 64 6 13 ooo 64 6 34 6 row 2 64 6 48 6 row 4 64 6 62 6 row 6 64 6 83 64 6 97 zzzzzZzzzzz 64 FLUSH TABLES; # # replacing p6 with a crashed MYD file (3) (splitted dynamic record) # Different results from the corrupt table, which can lead to dropping # of the not completely written rows when using REBUILD on a corrupt # table, depending if one reads via index or direct on datafile. # Since crash when reuse of deleted row space, CHECK MEDIUM or EXTENDED # is required (MEDIUM is default) to verify correct behavior! SELECT (b % 7) AS `partition`, b, a, length(c) FROM t1_will_crash WHERE (b % 7) = 6 ORDER BY `partition`, b, a; partition b a length(c) 6 6 jkl 64 6 13 ooo 64 6 34 6 row 2 64 6 83 64 6 97 zzzzzZzzzzz 64 SET @save_optimizer_switch= @@optimizer_switch; SET @@optimizer_switch='derived_merge=off'; SELECT (b % 7) AS `partition`, b, a FROM (SELECT b,a FROM t1_will_crash) q WHERE (b % 7) = 6 ORDER BY `partition`, b, a; partition b a 6 6 jkl 6 13 ooo 6 34 6 row 2 6 48 6 row 4 6 62 6 row 6 6 83 6 97 zzzzzZzzzzz SET @@optimizer_switch=@save_optimizer_switch; ALTER TABLE t1_will_crash CHECK PARTITION p6; Table Op Msg_type Msg_text test.t1_will_crash check warning Size of datafile is: 868 Should be: 604 test.t1_will_crash check error Record-count is not ok; is 8 Should be: 7 test.t1_will_crash check warning Found 10 key parts. Should be: 7 test.t1_will_crash check error Partition p6 returned error test.t1_will_crash check error Corrupt ALTER TABLE t1_will_crash REPAIR PARTITION p6; Table Op Msg_type Msg_text test.t1_will_crash repair warning Number of rows changed from 7 to 8 test.t1_will_crash repair status OK SELECT COUNT(*) FROM t1_will_crash; COUNT(*) 29 SELECT (b % 7) AS `partition`, COUNT(*) AS rows_cnt FROM t1_will_crash GROUP BY (b % 7); partition rows_cnt 1 4 2 4 3 5 4 4 5 4 6 8 SELECT (b % 7) AS `partition`, b, a, length(c) FROM t1_will_crash ORDER BY `partition`, b, a; partition b a length(c) 1 1 abc 64 1 8 tuw 64 1 29 kkkkkkkkKkk 64 1 71 eee 64 2 2 def 64 2 9 vxy 64 2 23 lll 64 2 79 ccc 64 3 3 ghi 64 3 10 aaa 64 3 17 nnn 64 3 24 3 crashed after write_record 64 3 73 ddd 64 4 4 pqr 64 4 11 bbb 64 4 18 4 crashed after flush_cached_blocks 64 4 67 fff 64 5 5 mno 64 5 19 mmm 64 5 40 5 still here since crash in next row in multirow insert? 64 5 89 a 64 6 6 jkl 64 6 13 ooo 64 6 27 6 row 7 (crash before completely written to datafile) 128 6 34 6 row 2 64 6 48 6 row 4 64 6 62 6 row 6 64 6 83 64 6 97 zzzzzZzzzzz 64 ALTER TABLE t1_will_crash CHECK PARTITION all EXTENDED; Table Op Msg_type Msg_text test.t1_will_crash check status OK DROP TABLE t1_will_crash;