1# test of check/repair of partitioned myisam tables 2--source include/have_partition.inc 3--disable_warnings 4--disable_query_log 5drop table if exists t1_will_crash; 6call mtr.add_suppression("Got an error from thread_id=.*ha_myisam.cc:"); 7call mtr.add_suppression("MySQL thread id .*, query id .* localhost.*root Checking table"); 8--enable_query_log 9--enable_warnings 10 11--echo # REPAIR USE_FRM is not implemented for partitioned tables. 12 13let $MYSQLD_DATADIR= `select @@datadir`; 14 15--echo # test of non partitioned myisam for reference 16CREATE TABLE t1_will_crash (a INT, KEY (a)) ENGINE=MyISAM; 17INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); 18FLUSH TABLES; 19--echo # replacing t1.MYI with a corrupt + unclosed one created by doing: 20--echo # 'create table t1 (a int key(a))' head -c1024 t1.MYI > corrupt_t1.MYI 21--remove_file $MYSQLD_DATADIR/test/t1_will_crash.MYI 22--copy_file std_data/corrupt_t1.MYI $MYSQLD_DATADIR/test/t1_will_crash.MYI 23CHECK TABLE t1_will_crash; 24REPAIR TABLE t1_will_crash; 25SELECT * FROM t1_will_crash; 26DROP TABLE t1_will_crash; 27 28--echo # test of check/repair of a damaged partition's MYI-file 29CREATE TABLE t1_will_crash (a INT, KEY (a)) 30ENGINE=MyISAM 31PARTITION BY HASH (a) 32PARTITIONS 3; 33INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); 34FLUSH TABLES; 35--echo # test with CHECK/REPAIR TABLE 36--echo # replacing t1#P#p1.MYI with a corrupt + unclosed one created by doing: 37--echo # 'create table t1 (a int key(a)) partition by hash (a) partitions 3' 38--echo # head -c1024 t1#P#p1.MYI > corrupt_t1#P#p1.MYI 39--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI 40--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI 41CHECK TABLE t1_will_crash; 42REPAIR TABLE t1_will_crash; 43SELECT * FROM t1_will_crash; 44FLUSH TABLES; 45--echo # test with ALTER TABLE ... CHECK/REPAIR PARTITION 46--echo # replacing t1_will_crash#P#p1.MYI with a corrupt + unclosed one 47--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI 48--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI 49ALTER TABLE t1_will_crash CHECK PARTITION p0, p2; 50ALTER TABLE t1_will_crash CHECK PARTITION p0, p1; 51ALTER TABLE t1_will_crash CHECK PARTITION p1, p2; 52ALTER TABLE t1_will_crash REPAIR PARTITION p0, p2; 53ALTER TABLE t1_will_crash REPAIR PARTITION p0, p1; 54SELECT * FROM t1_will_crash; 55DROP TABLE t1_will_crash; 56 57--echo # test of check/repair of a damaged subpartition's MYI-file 58CREATE TABLE t1_will_crash (a INT, KEY (a)) 59ENGINE=MyISAM 60PARTITION BY RANGE (a) 61SUBPARTITION BY HASH (a) 62SUBPARTITIONS 2 63(PARTITION p0 VALUES LESS THAN (7), 64 PARTITION p1 VALUES LESS THAN MAXVALUE); 65INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); 66SELECT * FROM t1_will_crash; 67FLUSH TABLES; 68--echo # test with CHECK/REPAIR TABLE 69--echo # replacing t1_will_crash#P#p1#SP#p1sp0.MYI with a corrupt + unclosed one 70--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1#SP#p1sp0.MYI 71--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p1#SP#p1sp0.MYI 72CHECK TABLE t1_will_crash; 73REPAIR TABLE t1_will_crash; 74SELECT * FROM t1_will_crash; 75FLUSH TABLES; 76--echo # test with ALTER TABLE ... CHECK/REPAIR PARTITION 77--echo # replacing t1_will_crash#P#p1#SP#p1sp0.MYI with a corrupt + unclosed one 78--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1#SP#p1sp0.MYI 79--copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p1#SP#p1sp0.MYI 80ALTER TABLE t1_will_crash CHECK PARTITION p0; 81ALTER TABLE t1_will_crash CHECK PARTITION all; 82ALTER TABLE t1_will_crash CHECK PARTITION p1; 83ALTER TABLE t1_will_crash REPAIR PARTITION p0; 84ALTER TABLE t1_will_crash REPAIR PARTITION p0, p1; 85SELECT * FROM t1_will_crash; 86DROP TABLE t1_will_crash; 87 88--echo # test of check/repair of crashed partitions in variuos states 89CREATE TABLE t1_will_crash ( 90 a VARCHAR(255), 91 b INT, 92 c LONGTEXT, 93 PRIMARY KEY (a, b)) 94ENGINE=MyISAM 95PARTITION BY HASH (b) 96PARTITIONS 7; 97 98# creating a longer string for for filling the records 99let $i= 3; 100let $lt= longtext; 101while ($i) 102{ 103 let $lt= $lt$lt; 104 dec $i; 105} 106 107# Tests (mapped to partition) 108# Partition 109# 0 - truncated datafile (size = 0 bytes) 110# 1 - head -c 1024 of datafile (simulates crashed write) 111# 2 - after _mi_mark_file_changed (only marked index as opened) 112# 3 - after write_record (updated datafile + not closed/updated index) 113# 4 - after flush_cached_blocks (updated index/datafiles, not closed index) 114# 5 - (Not used) after mi_state_info_write (fully uppdated/closed index file) 115# (this was verified to be a harmless crash, since everything was written) 116# 6 - partly updated datafile (insert 6 small records, delete 5,3,1, 117# insert one larger record (2.5 X small) and break in gdb before it has 118# been completely written (in write_dynamic_record) 119# (done with 3 different MYD files, since it also affects 120# the delete-linked-list) 121 122--disable_query_log 123eval INSERT INTO t1_will_crash VALUES 124 ('abc', 1, '$lt'), ('def', 2, '$lt'), ('ghi', 3, '$lt'), ('jkl', 6, '$lt'), 125 ('mno', 5, '$lt'), ('pqr', 4, '$lt'), ('tuw', 8, '$lt'), ('vxy', 9, '$lt'), 126 ('z lost', 7, '$lt'), ('aaa', 10, '$lt'), ('bbb', 11, '$lt'), 127 ('zzzzzZzzzzz', 97, '$lt'), ('a', 89, '$lt'), (' ', 83, '$lt'), 128 ('ccc', 79, '$lt'), ('ddd', 73, '$lt'), ('eee', 71, '$lt'), 129 ('fff', 67, '$lt'), ('ooo', 13, '$lt'), ('nnn', 17, '$lt'), 130 ('mmm', 19, '$lt'), ('lll', 23, '$lt'), ('kkkkkkkkKkk', 29, '$lt'), 131 (' lost', 0, '$lt'), ('1 broken when head -c1024 on datafile', 71, 132 '$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt$lt'), 133 ('3 crashed after write_record', 24, '$lt'); 134eval INSERT INTO t1_will_crash VALUES 135 ('2 crashed after _mi_mark_changed', 30, '$lt'); 136# if crashed here, part p5 would need to be repaired before next statement 137# but since we use pre fabricated crashed files, we can skip that here. 138eval INSERT INTO t1_will_crash VALUES 139 ('5 still here since crash in next row in multirow insert?', 40, '$lt'), 140 ('4 crashed after flush_cached_blocks', 18, '$lt'); 141# There is no write after mi_state_info_write, so this is not tested. 142#eval INSERT INTO t1_will_crash VALUES 143# ('5 crashed after mi_state_info_write', 12, '$lt'); 144eval INSERT INTO t1_will_crash VALUES 145 ('6 row 1', 27, '$lt'), ('6 row 2', 34, '$lt'), 146 ('6 row 3', 41, '$lt'), ('6 row 4', 48, '$lt'), 147 ('6 row 5', 55, '$lt'), ('6 row 6', 62, '$lt'); 148DELETE FROM t1_will_crash WHERE b in (27, 55); 149DELETE FROM t1_will_crash WHERE b = 41; 150eval INSERT INTO t1_will_crash VALUES 151 ('6 row 7 (crash before completely written to datafile)', 27, '$lt$lt'); 152--enable_query_log 153SELECT COUNT(*) FROM t1_will_crash; 154SELECT (b % 7) AS partition, COUNT(*) AS rows FROM t1_will_crash GROUP BY (b % 7); 155SELECT (b % 7) AS partition, b, a, length(c) FROM t1_will_crash ORDER BY partition, b, a; 156FLUSH TABLES; 157# testing p0, p1, p3, p6(1) 158--echo # truncating p0 to simulate an empty datafile (not recovered!) 159--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p0.MYD 160--write_file $MYSQLD_DATADIR/test/t1_will_crash#P#p0.MYD 161EOF 162 163--echo # replacing p1 with only the first 1024 bytes (not recovered!) 164--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYD 165--copy_file std_data/parts/t1_will_crash#P#p1_first_1024.MYD $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYD 166 167--echo # replacing p3 with a crashed one at the last row in first insert 168--echo # (crashed right after *share->write_record()) 169--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p3.MYI 170--copy_file std_data/parts/t1_will_crash#P#p3.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p3.MYI 171 172--echo # replacing p6 with a crashed MYD file (1) (splitted dynamic record) 173--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD 174--copy_file std_data/parts/t1_will_crash#P#p6.MYD $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD 175ANALYZE TABLE t1_will_crash; 176OPTIMIZE TABLE t1_will_crash; 177CHECK TABLE t1_will_crash; 178REPAIR TABLE t1_will_crash; 179SELECT COUNT(*) FROM t1_will_crash; 180SELECT (b % 7) AS partition, COUNT(*) AS rows FROM t1_will_crash GROUP BY (b % 7); 181SELECT (b % 7) AS partition, b, a, length(c) FROM t1_will_crash ORDER BY partition, b, a; 182FLUSH TABLES; 183 184# testing p2, p4, p6(2, 3) 185--echo # 186--echo # replacing p2 with crashed files (after _mi_mark_changed) 187--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p2.MYI 188--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p2.MYD 189--copy_file std_data/parts/t1_will_crash#P#p2.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p2.MYI 190--copy_file std_data/parts/t1_will_crash#P#p2.MYD $MYSQLD_DATADIR/test/t1_will_crash#P#p2.MYD 191ALTER TABLE t1_will_crash CHECK PARTITION p2; 192--echo # crash was when index only marked as opened, no real corruption 193ALTER TABLE t1_will_crash CHECK PARTITION p2; 194FLUSH TABLES; 195 196--echo # 197--echo # replacing p4 with updated but not closed index file 198--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p4.MYI 199--copy_file std_data/parts/t1_will_crash#P#p4.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p4.MYI 200#SHOW TABLE STATUS like 't1_will_crash'; 201#ALTER TABLE t1_will_crash ANALYZE PARTITION p4; 202#SHOW TABLE STATUS like 't1_will_crash'; 203ALTER TABLE t1_will_crash OPTIMIZE PARTITION p4; 204#SHOW TABLE STATUS like 't1_will_crash'; 205ALTER TABLE t1_will_crash CHECK PARTITION p4; 206#SHOW TABLE STATUS like 't1_will_crash'; 207ALTER TABLE t1_will_crash REPAIR PARTITION p4; 208#SHOW TABLE STATUS like 't1_will_crash'; 209FLUSH TABLES; 210 211--echo # 212--echo # replacing p6 with a crashed MYD file (2) (splitted dynamic record) 213--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD 214--copy_file std_data/parts/t1_will_crash#P#p6_2.MYD $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD 215#ALTER TABLE t1_will_crash OPTIMIZE PARTITION p6; 216ALTER TABLE t1_will_crash CHECK PARTITION p6; 217ALTER TABLE t1_will_crash REPAIR PARTITION p6; 218SELECT (b % 7) AS partition, b, a, length(c) FROM t1_will_crash 219WHERE (b % 7) = 6 220ORDER BY partition, b, a; 221FLUSH TABLES; 222 223--echo # 224--echo # replacing p6 with a crashed MYD file (3) (splitted dynamic record) 225--remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD 226--copy_file std_data/parts/t1_will_crash#P#p6_3.MYD $MYSQLD_DATADIR/test/t1_will_crash#P#p6.MYD 227--echo # Different results from the corrupt table, which can lead to dropping 228--echo # of the not completely written rows when using REBUILD on a corrupt 229--echo # table, depending if one reads via index or direct on datafile. 230--echo # Since crash when reuse of deleted row space, CHECK MEDIUM or EXTENDED 231--echo # is required (MEDIUM is default) to verify correct behavior! 232SELECT (b % 7) AS partition, b, a, length(c) FROM t1_will_crash 233WHERE (b % 7) = 6 234ORDER BY partition, b, a; 235SELECT (b % 7) AS partition, b, a FROM (SELECT b,a FROM t1_will_crash) q 236WHERE (b % 7) = 6 237ORDER BY partition, b, a; 238# NOTE: REBUILD PARTITION without CHECK before, 2 + (1) records will be lost! 239#ALTER TABLE t1_will_crash REBUILD PARTITION p6; 240ALTER TABLE t1_will_crash CHECK PARTITION p6; 241ALTER TABLE t1_will_crash REPAIR PARTITION p6; 242SELECT COUNT(*) FROM t1_will_crash; 243SELECT (b % 7) AS partition, COUNT(*) AS rows FROM t1_will_crash GROUP BY (b % 7); 244SELECT (b % 7) AS partition, b, a, length(c) FROM t1_will_crash ORDER BY partition, b, a; 245ALTER TABLE t1_will_crash CHECK PARTITION all EXTENDED; 246DROP TABLE t1_will_crash; 247