diff options
Diffstat (limited to 'mysql-test')
13 files changed, 722 insertions, 16 deletions
diff --git a/mysql-test/std_data/parts/t1_will_crash#P#p1_first_1024.MYD b/mysql-test/std_data/parts/t1_will_crash#P#p1_first_1024.MYD Binary files differnew file mode 100644 index 00000000000..a1bb8f98f91 --- /dev/null +++ b/mysql-test/std_data/parts/t1_will_crash#P#p1_first_1024.MYD diff --git a/mysql-test/std_data/parts/t1_will_crash#P#p2.MYD b/mysql-test/std_data/parts/t1_will_crash#P#p2.MYD Binary files differnew file mode 100644 index 00000000000..098d7a83a4a --- /dev/null +++ b/mysql-test/std_data/parts/t1_will_crash#P#p2.MYD diff --git a/mysql-test/std_data/parts/t1_will_crash#P#p2.MYI b/mysql-test/std_data/parts/t1_will_crash#P#p2.MYI Binary files differnew file mode 100644 index 00000000000..74085545751 --- /dev/null +++ b/mysql-test/std_data/parts/t1_will_crash#P#p2.MYI diff --git a/mysql-test/std_data/parts/t1_will_crash#P#p3.MYI b/mysql-test/std_data/parts/t1_will_crash#P#p3.MYI Binary files differnew file mode 100644 index 00000000000..38150da0bb5 --- /dev/null +++ b/mysql-test/std_data/parts/t1_will_crash#P#p3.MYI diff --git a/mysql-test/std_data/parts/t1_will_crash#P#p4.MYI b/mysql-test/std_data/parts/t1_will_crash#P#p4.MYI Binary files differnew file mode 100644 index 00000000000..df7eee53a81 --- /dev/null +++ b/mysql-test/std_data/parts/t1_will_crash#P#p4.MYI diff --git a/mysql-test/std_data/parts/t1_will_crash#P#p6.MYD b/mysql-test/std_data/parts/t1_will_crash#P#p6.MYD Binary files differnew file mode 100644 index 00000000000..10fd7d2fbce --- /dev/null +++ b/mysql-test/std_data/parts/t1_will_crash#P#p6.MYD diff --git a/mysql-test/std_data/parts/t1_will_crash#P#p6_2.MYD b/mysql-test/std_data/parts/t1_will_crash#P#p6_2.MYD Binary files differnew file mode 100644 index 00000000000..7ebcec17bb5 --- /dev/null +++ b/mysql-test/std_data/parts/t1_will_crash#P#p6_2.MYD diff --git a/mysql-test/std_data/parts/t1_will_crash#P#p6_3.MYD b/mysql-test/std_data/parts/t1_will_crash#P#p6_3.MYD Binary files differnew file mode 100644 index 00000000000..fb8f1a0f687 --- /dev/null +++ b/mysql-test/std_data/parts/t1_will_crash#P#p6_3.MYD diff --git a/mysql-test/suite/parts/r/partition_recover_myisam.result b/mysql-test/suite/parts/r/partition_recover_myisam.result new file mode 100644 index 00000000000..df737ec2853 --- /dev/null +++ b/mysql-test/suite/parts/r/partition_recover_myisam.result @@ -0,0 +1,56 @@ +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 +SELECT * FROM t1_will_crash; +a +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +Warnings: +Error 145 Table './test/t1_will_crash' is marked as crashed and should be repaired +Error 1194 Table 't1_will_crash' is marked as crashed and should be repaired +Error 1034 1 client is using or hasn't closed the table properly +Error 1034 Size of indexfile is: 1024 Should be: 2048 +Error 1034 Size of datafile is: 77 Should be: 7 +Error 1034 Number of rows changed from 1 to 11 +DROP TABLE t1_will_crash; +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; +# 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 +SELECT * FROM t1_will_crash; +a +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +Warnings: +Error 145 Table './test/t1_will_crash#P#p1' is marked as crashed and should be repaired +Error 1194 Table 't1_will_crash' is marked as crashed and should be repaired +Error 1034 1 client is using or hasn't closed the table properly +Error 1034 Size of indexfile is: 1024 Should be: 2048 +Error 1034 Size of datafile is: 28 Should be: 7 +Error 1034 Number of rows changed from 1 to 4 +DROP TABLE t1_will_crash; diff --git a/mysql-test/suite/parts/r/partition_repair_myisam.result b/mysql-test/suite/parts/r/partition_repair_myisam.result index df737ec2853..2d0a26b397c 100644 --- a/mysql-test/suite/parts/r/partition_repair_myisam.result +++ b/mysql-test/suite/parts/r/partition_repair_myisam.result @@ -1,8 +1,20 @@ +# REPAIR USE_FRM is not implemented for partitioned tables. +# 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 @@ -16,23 +28,29 @@ a 9 10 11 -Warnings: -Error 145 Table './test/t1_will_crash' is marked as crashed and should be repaired -Error 1194 Table 't1_will_crash' is marked as crashed and should be repaired -Error 1034 1 client is using or hasn't closed the table properly -Error 1034 Size of indexfile is: 1024 Should be: 2048 -Error 1034 Size of datafile is: 77 Should be: 7 -Error 1034 Number of rows changed from 1 to 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) +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 @@ -46,11 +64,400 @@ a 9 10 11 -Warnings: -Error 145 Table './test/t1_will_crash#P#p1' is marked as crashed and should be repaired -Error 1194 Table 't1_will_crash' is marked as crashed and should be repaired -Error 1034 1 client is using or hasn't closed the table properly -Error 1034 Size of indexfile is: 1024 Should be: 2048 -Error 1034 Size of datafile is: 28 Should be: 7 -Error 1034 Number of rows changed from 1 to 4 +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 FROM t1_will_crash GROUP BY (b % 7); +partition rows +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 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 FROM t1_will_crash GROUP BY (b % 7); +partition rows +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 +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 +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 FROM t1_will_crash GROUP BY (b % 7); +partition rows +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; diff --git a/mysql-test/suite/parts/t/partition_repair_myisam-master.opt b/mysql-test/suite/parts/t/partition_recover_myisam-master.opt index 787371a854a..787371a854a 100644 --- a/mysql-test/suite/parts/t/partition_repair_myisam-master.opt +++ b/mysql-test/suite/parts/t/partition_recover_myisam-master.opt diff --git a/mysql-test/suite/parts/t/partition_recover_myisam.test b/mysql-test/suite/parts/t/partition_recover_myisam.test new file mode 100644 index 00000000000..69e45b3ef3c --- /dev/null +++ b/mysql-test/suite/parts/t/partition_recover_myisam.test @@ -0,0 +1,31 @@ +# test the auto-recover (--myisam-recover) of partitioned myisam tables +--source include/have_partition.inc +--disable_warnings +--disable_query_log +drop table if exists t1_will_crash; +--enable_query_log +--enable_warnings + + +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; +--echo # replacing t1.MYI with a corrupt + unclosed one created by doing: +--echo # 'create table t1 (a int key(a))' head -c1024 t1.MYI > corrupt_t1.MYI +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash.MYI +--copy_file std_data/corrupt_t1.MYI $MYSQLTEST_VARDIR/master-data/test/t1_will_crash.MYI +SELECT * FROM t1_will_crash; +DROP TABLE t1_will_crash; +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; +--echo # replacing t1#P#p1.MYI with a corrupt + unclosed one created by doing: +--echo # 'create table t1 (a int key(a)) partition by hash (a) partitions 3' +--echo # head -c1024 t1#P#p1.MYI > corrupt_t1#P#p1.MYI +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1.MYI +--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1.MYI +SELECT * FROM t1_will_crash; +DROP TABLE t1_will_crash; diff --git a/mysql-test/suite/parts/t/partition_repair_myisam.test b/mysql-test/suite/parts/t/partition_repair_myisam.test index 551d344f60e..4c4cb21b848 100644 --- a/mysql-test/suite/parts/t/partition_repair_myisam.test +++ b/mysql-test/suite/parts/t/partition_repair_myisam.test @@ -1,3 +1,4 @@ +# test of check/repair of partitioned myisam tables --source include/have_partition.inc --disable_warnings --disable_query_log @@ -5,7 +6,9 @@ drop table if exists t1_will_crash; --enable_query_log --enable_warnings +--echo # REPAIR USE_FRM is not implemented for partitioned tables. +--echo # 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; @@ -13,18 +16,227 @@ FLUSH TABLES; --echo # 'create table t1 (a int key(a))' head -c1024 t1.MYI > corrupt_t1.MYI --remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash.MYI --copy_file std_data/corrupt_t1.MYI $MYSQLTEST_VARDIR/master-data/test/t1_will_crash.MYI +CHECK TABLE t1_will_crash; +REPAIR TABLE t1_will_crash; SELECT * FROM t1_will_crash; DROP TABLE t1_will_crash; + +--echo # 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) +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; +--echo # test with CHECK/REPAIR TABLE --echo # replacing t1#P#p1.MYI with a corrupt + unclosed one created by doing: --echo # 'create table t1 (a int key(a)) partition by hash (a) partitions 3' --echo # head -c1024 t1#P#p1.MYI > corrupt_t1#P#p1.MYI --remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1.MYI --copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1.MYI +CHECK TABLE t1_will_crash; +REPAIR TABLE t1_will_crash; +SELECT * FROM t1_will_crash; +FLUSH TABLES; +--echo # test with ALTER TABLE ... CHECK/REPAIR PARTITION +--echo # replacing t1_will_crash#P#p1.MYI with a corrupt + unclosed one +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1.MYI +--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1.MYI +ALTER TABLE t1_will_crash CHECK PARTITION p0, p2; +ALTER TABLE t1_will_crash CHECK PARTITION p0, p1; +ALTER TABLE t1_will_crash CHECK PARTITION p1, p2; +ALTER TABLE t1_will_crash REPAIR PARTITION p0, p2; +ALTER TABLE t1_will_crash REPAIR PARTITION p0, p1; +SELECT * FROM t1_will_crash; +DROP TABLE t1_will_crash; + +--echo # 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; +FLUSH TABLES; +--echo # test with CHECK/REPAIR TABLE +--echo # replacing t1_will_crash#P#p1#SP#p1sp0.MYI with a corrupt + unclosed one +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1#SP#p1sp0.MYI +--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1#SP#p1sp0.MYI +CHECK TABLE t1_will_crash; +REPAIR TABLE t1_will_crash; +SELECT * FROM t1_will_crash; +FLUSH TABLES; +--echo # test with ALTER TABLE ... CHECK/REPAIR PARTITION +--echo # replacing t1_will_crash#P#p1#SP#p1sp0.MYI with a corrupt + unclosed one +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1#SP#p1sp0.MYI +--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1#SP#p1sp0.MYI +ALTER TABLE t1_will_crash CHECK PARTITION p0; +ALTER TABLE t1_will_crash CHECK PARTITION all; +ALTER TABLE t1_will_crash CHECK PARTITION p1; +ALTER TABLE t1_will_crash REPAIR PARTITION p0; +ALTER TABLE t1_will_crash REPAIR PARTITION p0, p1; +SELECT * FROM t1_will_crash; +DROP TABLE t1_will_crash; + +--echo # 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; + +# creating a longer string for for filling the records +let $i= 3; +let $lt= longtext; +while ($i>0) +{ + let $lt= $lt$lt; + dec $i; +} + +# Tests (mapped to partition) +# Partition +# 0 - truncated datafile (size = 0 bytes) +# 1 - head -c 1024 of datafile (simulates crashed write) +# 2 - after _mi_mark_file_changed (only marked index as opened) +# 3 - after write_record (updated datafile + not closed/updated index) +# 4 - after flush_cached_blocks (updated index/datafiles, not closed index) +# 5 - (Not used) after mi_state_info_write (fully uppdated/closed index file) +# (this was verified to be a harmless crash, since everything was written) +# 6 - partly updated datafile (insert 6 small records, delete 5,3,1, +# insert one larger record (2.5 X small) and break in gdb before it has +# been completely written (in write_dynamic_record) +# (done with 3 different MYD files, since it also affects +# the delete-linked-list) + +--disable_query_log +eval INSERT INTO t1_will_crash VALUES + ('abc', 1, '$lt'), ('def', 2, '$lt'), ('ghi', 3, '$lt'), ('jkl', 6, '$lt'), + ('mno', 5, '$lt'), ('pqr', 4, '$lt'), ('tuw', 8, '$lt'), ('vxy', 9, '$lt'), + ('z lost', 7, '$lt'), ('aaa', 10, '$lt'), ('bbb', 11, '$lt'), + ('zzzzzZzzzzz', 97, '$lt'), ('a', 89, '$lt'), (' ', 83, '$lt'), + ('ccc', 79, '$lt'), ('ddd', 73, '$lt'), ('eee', 71, '$lt'), + ('fff', 67, '$lt'), ('ooo', 13, '$lt'), ('nnn', 17, '$lt'), + ('mmm', 19, '$lt'), ('lll', 23, '$lt'), ('kkkkkkkkKkk', 29, '$lt'), + (' lost', 0, '$lt'), ('1 broken when head -c1024 on datafile', 71, + '$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt'), + ('3 crashed after write_record', 24, '$lt'); +eval INSERT INTO t1_will_crash VALUES + ('2 crashed after _mi_mark_changed', 30, '$lt'); +# if crashed here, part p5 would need to be repaired before next statement +# but since we use pre fabricated crashed files, we can skip that here. +eval INSERT INTO t1_will_crash VALUES + ('5 still here since crash in next row in multirow insert?', 40, '$lt'), + ('4 crashed after flush_cached_blocks', 18, '$lt'); +# There is no write after mi_state_info_write, so this is not tested. +#eval INSERT INTO t1_will_crash VALUES +# ('5 crashed after mi_state_info_write', 12, '$lt'); +eval INSERT INTO t1_will_crash VALUES + ('6 row 1', 27, '$lt'), ('6 row 2', 34, '$lt'), + ('6 row 3', 41, '$lt'), ('6 row 4', 48, '$lt'), + ('6 row 5', 55, '$lt'), ('6 row 6', 62, '$lt'); +DELETE FROM t1_will_crash WHERE b in (27, 55); +DELETE FROM t1_will_crash WHERE b = 41; +eval INSERT INTO t1_will_crash VALUES + ('6 row 7 (crash before completely written to datafile)', 27, '$lt$lt'); +--enable_query_log +SELECT COUNT(*) FROM t1_will_crash; +SELECT (b % 7) AS partition, COUNT(*) AS rows FROM t1_will_crash GROUP BY (b % 7); +SELECT (b % 7) AS partition, b, a, length(c) FROM t1_will_crash ORDER BY partition, b, a; +FLUSH TABLES; +# testing p0, p1, p3, p6(1) +--echo # truncating p0 to simulate an empty datafile (not recovered!) +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p0.MYD +--write_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p0.MYD +EOF + +--echo # replacing p1 with only the first 1024 bytes (not recovered!) +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1.MYD +--copy_file std_data/parts/t1_will_crash#P#p1_first_1024.MYD $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p1.MYD + +--echo # replacing p3 with a crashed one at the last row in first insert +--echo # (crashed right after *share->write_record()) +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p3.MYI +--copy_file std_data/parts/t1_will_crash#P#p3.MYI $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p3.MYI + +--echo # replacing p6 with a crashed MYD file (1) (splitted dynamic record) +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p6.MYD +--copy_file std_data/parts/t1_will_crash#P#p6.MYD $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p6.MYD +ANALYZE TABLE t1_will_crash; +OPTIMIZE TABLE t1_will_crash; +CHECK TABLE t1_will_crash; +REPAIR TABLE t1_will_crash; +SELECT COUNT(*) FROM t1_will_crash; +SELECT (b % 7) AS partition, COUNT(*) AS rows FROM t1_will_crash GROUP BY (b % 7); +SELECT (b % 7) AS partition, b, a, length(c) FROM t1_will_crash ORDER BY partition, b, a; +FLUSH TABLES; + +# testing p2, p4, p6(2, 3) +--echo # +--echo # replacing p2 with crashed files (after _mi_mark_changed) +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p2.MYI +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p2.MYD +--copy_file std_data/parts/t1_will_crash#P#p2.MYI $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p2.MYI +--copy_file std_data/parts/t1_will_crash#P#p2.MYD $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p2.MYD +ALTER TABLE t1_will_crash CHECK PARTITION p2; +--echo # crash was when index only marked as opened, no real corruption +ALTER TABLE t1_will_crash CHECK PARTITION p2; +FLUSH TABLES; + +--echo # +--echo # replacing p4 with updated but not closed index file +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p4.MYI +--copy_file std_data/parts/t1_will_crash#P#p4.MYI $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p4.MYI +#SHOW TABLE STATUS like 't1_will_crash'; +#ALTER TABLE t1_will_crash ANALYZE PARTITION p4; +#SHOW TABLE STATUS like 't1_will_crash'; +ALTER TABLE t1_will_crash OPTIMIZE PARTITION p4; +#SHOW TABLE STATUS like 't1_will_crash'; +ALTER TABLE t1_will_crash CHECK PARTITION p4; +#SHOW TABLE STATUS like 't1_will_crash'; +ALTER TABLE t1_will_crash REPAIR PARTITION p4; +#SHOW TABLE STATUS like 't1_will_crash'; +FLUSH TABLES; + +--echo # +--echo # replacing p6 with a crashed MYD file (2) (splitted dynamic record) +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p6.MYD +--copy_file std_data/parts/t1_will_crash#P#p6_2.MYD $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p6.MYD +#ALTER TABLE t1_will_crash OPTIMIZE PARTITION p6; +ALTER TABLE t1_will_crash CHECK PARTITION p6; +ALTER TABLE t1_will_crash REPAIR PARTITION p6; +SELECT (b % 7) AS partition, b, a, length(c) FROM t1_will_crash +WHERE (b % 7) = 6 +ORDER BY partition, b, a; +FLUSH TABLES; + +--echo # +--echo # replacing p6 with a crashed MYD file (3) (splitted dynamic record) +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p6.MYD +--copy_file std_data/parts/t1_will_crash#P#p6_3.MYD $MYSQLTEST_VARDIR/master-data/test/t1_will_crash#P#p6.MYD +--echo # Different results from the corrupt table, which can lead to dropping +--echo # of the not completely written rows when using REBUILD on a corrupt +--echo # table, depending if one reads via index or direct on datafile. +--echo # Since crash when reuse of deleted row space, CHECK MEDIUM or EXTENDED +--echo # 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; +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; +# NOTE: REBUILD PARTITION without CHECK before, 2 + (1) records will be lost! +#ALTER TABLE t1_will_crash REBUILD PARTITION p6; +ALTER TABLE t1_will_crash CHECK PARTITION p6; +ALTER TABLE t1_will_crash REPAIR PARTITION p6; +SELECT COUNT(*) FROM t1_will_crash; +SELECT (b % 7) AS partition, COUNT(*) AS rows FROM t1_will_crash GROUP BY (b % 7); +SELECT (b % 7) AS partition, b, a, length(c) FROM t1_will_crash ORDER BY partition, b, a; +ALTER TABLE t1_will_crash CHECK PARTITION all EXTENDED; DROP TABLE t1_will_crash; |