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