Debian Codegraph @

mysql-test/suite/parts/t/partition_repair_myisam.test

raw
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