diff options
Diffstat (limited to 'mysql-test/suite/parts/r/partition_repair_myisam.result')
| -rw-r--r-- | mysql-test/suite/parts/r/partition_repair_myisam.result | 529 |
1 files changed, 529 insertions, 0 deletions
diff --git a/mysql-test/suite/parts/r/partition_repair_myisam.result b/mysql-test/suite/parts/r/partition_repair_myisam.result new file mode 100644 index 00000000..f534adf9 --- /dev/null +++ b/mysql-test/suite/parts/r/partition_repair_myisam.result @@ -0,0 +1,529 @@ +# 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 Warning Engine-independent statistics are not collected for column 'c' +test.t1_will_crash analyze status Operation failed +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; |