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