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