| blob | c1901daec6ca24dc0740c220c45d900fd787f145 |
1 # 2001 September 15
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
12 #
13 # The focus of this file is testing the ability of the database to
14 # uses its rollback journal to recover intact (no database corruption)
15 # from a power failure during the middle of a COMMIT. The OS interface
16 # modules are overloaded using the modified I/O routines found in test6.c.
17 # These routines allow us to simulate the kind of file damage that
18 # occurs after a power failure.
19 #
20 # $Id: crash.test,v 1.27 2008/01/08 15:18:52 drh Exp $
22 set testdir [file dirname $argv0]
23 source $testdir/tester.tcl
25 ifcapable !crashtest {
26 finish_test
27 return
28 }
30 set repeats 100
31 #set repeats 10
33 # The following procedure computes a "signature" for table "abc". If
34 # abc changes in any way, the signature should change.
35 proc signature {} {
36 return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}]
37 }
38 proc signature2 {} {
39 return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc2}]
40 }
42 #--------------------------------------------------------------------------
43 # Simple crash test:
44 #
45 # crash-1.1: Create a database with a table with two rows.
46 # crash-1.2: Run a 'DELETE FROM abc WHERE a = 1' that crashes during
47 # the first journal-sync.
48 # crash-1.3: Ensure the database is in the same state as after crash-1.1.
49 # crash-1.4: Run a 'DELETE FROM abc WHERE a = 1' that crashes during
50 # the first database-sync.
51 # crash-1.5: Ensure the database is in the same state as after crash-1.1.
52 # crash-1.6: Run a 'DELETE FROM abc WHERE a = 1' that crashes during
53 # the second journal-sync.
54 # crash-1.7: Ensure the database is in the same state as after crash-1.1.
55 #
56 # Tests 1.8 through 1.11 test for crashes on the third journal sync and
57 # second database sync. Neither of these is required in such a small test
58 # case, so these tests are just to verify that the test infrastructure
59 # operates as expected.
60 #
61 do_test crash-1.1 {
62 execsql {
63 CREATE TABLE abc(a, b, c);
64 INSERT INTO abc VALUES(1, 2, 3);
65 INSERT INTO abc VALUES(4, 5, 6);
66 }
67 set ::sig [signature]
68 expr 0
69 } {0}
70 for {set i 0} {$i<10} {incr i} {
71 set seed [expr {int(abs(rand()*10000))}]
72 do_test crash-1.2.$i {
73 crashsql -delay 1 -file test.db-journal -seed $seed {
74 DELETE FROM abc WHERE a = 1;
75 }
76 } {1 {child process exited abnormally}}
77 do_test crash-1.3.$i {
78 signature
79 } $::sig
80 }
81 do_test crash-1.4 {
82 crashsql -delay 1 -file test.db {
83 DELETE FROM abc WHERE a = 1;
84 }
85 } {1 {child process exited abnormally}}
86 do_test crash-1.5 {
87 signature
88 } $::sig
89 do_test crash-1.6 {
90 crashsql -delay 2 -file test.db-journal {
91 DELETE FROM abc WHERE a = 1;
92 }
93 } {1 {child process exited abnormally}}
94 do_test crash-1.7 {
95 catchsql {
96 SELECT * FROM abc;
97 }
98 } {0 {1 2 3 4 5 6}}
100 do_test crash-1.8 {
101 crashsql -delay 3 -file test.db-journal {
102 DELETE FROM abc WHERE a = 1;
103 }
104 } {0 {}}
105 do_test crash-1.9 {
106 catchsql {
107 SELECT * FROM abc;
108 }
109 } {0 {4 5 6}}
110 do_test crash-1.10 {
111 crashsql -delay 2 -file test.db {
112 DELETE FROM abc WHERE a = 4;
113 }
114 } {0 {}}
115 do_test crash-1.11 {
116 catchsql {
117 SELECT * FROM abc;
118 }
119 } {0 {}}
121 #--------------------------------------------------------------------------
122 # The following tests test recovery when both the database file and the
123 # journal file contain corrupt data. This can happen after pages are
124 # written to the database file before a transaction is committed due to
125 # cache-pressure.
126 #
127 # crash-2.1: Insert 18 pages of data into the database.
128 # crash-2.2: Check the database file size looks ok.
129 # crash-2.3: Delete 15 or so pages (with a 10 page page-cache), then crash.
130 # crash-2.4: Ensure the database is in the same state as after crash-2.1.
131 #
132 # Test cases crash-2.5 and crash-2.6 check that the database is OK if the
133 # crash occurs during the main database file sync. But this isn't really
134 # different from the crash-1.* cases.
135 #
136 do_test crash-2.1 {
137 execsql { BEGIN }
138 for {set n 0} {$n < 1000} {incr n} {
139 execsql "INSERT INTO abc VALUES($n, [expr 2*$n], [expr 3*$n])"
140 }
141 execsql { COMMIT }
142 set ::sig [signature]
143 execsql { SELECT sum(a), sum(b), sum(c) from abc }
144 } {499500 999000 1498500}
145 do_test crash-2.2 {
146 expr ([file size test.db] / 1024)>16
147 } {1}
148 do_test crash-2.3 {
149 crashsql -delay 2 -file test.db-journal {
150 DELETE FROM abc WHERE a < 800;
151 }
152 } {1 {child process exited abnormally}}
153 do_test crash-2.4 {
154 signature
155 } $sig
156 do_test crash-2.5 {
157 crashsql -delay 1 -file test.db {
158 DELETE FROM abc WHERE a<800;
159 }
160 } {1 {child process exited abnormally}}
161 do_test crash-2.6 {
162 signature
163 } $sig
165 #--------------------------------------------------------------------------
166 # The crash-3.* test cases are essentially the same test as test case
167 # crash-2.*, but with a more complicated data set.
168 #
169 # The test is repeated a few times with different seeds for the random
170 # number generator in the crashing executable. Because there is no way to
171 # seed the random number generator directly, some SQL is added to the test
172 # case to 'use up' a different quantity random numbers before the test SQL
173 # is executed.
174 #
176 # Make sure the file is much bigger than the pager-cache (10 pages). This
177 # ensures that cache-spills happen regularly.
178 do_test crash-3.0 {
179 execsql {
180 INSERT INTO abc SELECT * FROM abc;
181 INSERT INTO abc SELECT * FROM abc;
182 INSERT INTO abc SELECT * FROM abc;
183 INSERT INTO abc SELECT * FROM abc;
184 INSERT INTO abc SELECT * FROM abc;
185 }
186 expr ([file size test.db] / 1024) > 450
187 } {1}
188 for {set i 1} {$i < $repeats} {incr i} {
189 set sig [signature]
190 do_test crash-3.$i.1 {
191 set seed [expr {int(abs(rand()*10000))}]
192 crashsql -delay [expr $i%5 + 1] -file test.db-journal -seed $seed "
193 BEGIN;
194 SELECT random() FROM abc LIMIT $i;
195 INSERT INTO abc VALUES(randstr(10,10), 0, 0);
196 DELETE FROM abc WHERE random()%10!=0;
197 COMMIT;
198 "
199 } {1 {child process exited abnormally}}
200 do_test crash-3.$i.2 {
201 signature
202 } $sig
203 }
205 #--------------------------------------------------------------------------
206 # The following test cases - crash-4.* - test the correct recovery of the
207 # database when a crash occurs during a multi-file transaction.
208 #
209 # crash-4.1.*: Test recovery when crash occurs during sync() of the
210 # main database journal file.
211 # crash-4.2.*: Test recovery when crash occurs during sync() of an
212 # attached database journal file.
213 # crash-4.3.*: Test recovery when crash occurs during sync() of the master
214 # journal file.
215 #
216 ifcapable attach {
217 do_test crash-4.0 {
218 forcedelete test2.db
219 forcedelete test2.db-journal
220 execsql {
221 ATTACH 'test2.db' AS aux;
222 PRAGMA aux.default_cache_size = 10;
223 CREATE TABLE aux.abc2 AS SELECT 2*a as a, 2*b as b, 2*c as c FROM abc;
224 }
225 expr ([file size test2.db] / 1024) > 450
226 } {1}
228 set fin 0
229 for {set i 1} {$i<$repeats} {incr i} {
230 set seed [expr {int(abs(rand()*10000))}]
231 set sig [signature]
232 set sig2 [signature2]
233 do_test crash-4.1.$i.1 {
234 set c [crashsql -delay $i -file test.db-journal -seed $::seed "
235 ATTACH 'test2.db' AS aux;
236 BEGIN;
237 SELECT randstr($i,$i) FROM abc LIMIT $i;
238 INSERT INTO abc VALUES(randstr(10,10), 0, 0);
239 DELETE FROM abc WHERE random()%10!=0;
240 INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);
241 DELETE FROM abc2 WHERE random()%10!=0;
242 COMMIT;
243 "]
244 if { $c == {0 {}} } {
245 set ::fin 1
246 set c {1 {child process exited abnormally}}
247 }
248 set c
249 } {1 {child process exited abnormally}}
250 if {$::fin} break
251 do_test crash-4.1.$i.2 {
252 signature
253 } $sig
254 do_test crash-4.1.$i.3 {
255 signature2
256 } $sig2
257 }
258 set i 0
259 set fin 0
260 while {[incr i]} {
261 set seed [expr {int(abs(rand()*10000))}]
262 set sig [signature]
263 set sig2 [signature2]
264 set ::fin 0
265 do_test crash-4.2.$i.1 {
266 set c [crashsql -delay $i -file test2.db-journal -seed $::seed "
267 ATTACH 'test2.db' AS aux;
268 BEGIN;
269 SELECT randstr($i,$i) FROM abc LIMIT $i;
270 INSERT INTO abc VALUES(randstr(10,10), 0, 0);
271 DELETE FROM abc WHERE random()%10!=0;
272 INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);
273 DELETE FROM abc2 WHERE random()%10!=0;
274 COMMIT;
275 "]
276 if { $c == {0 {}} } {
277 set ::fin 1
278 set c {1 {child process exited abnormally}}
279 }
280 set c
281 } {1 {child process exited abnormally}}
282 if { $::fin } break
283 do_test crash-4.2.$i.2 {
284 signature
285 } $sig
286 do_test crash-4.2.$i.3 {
287 signature2
288 } $sig2
289 }
290 for {set i 1} {$i < 5} {incr i} {
291 set sig [signature]
292 set sig2 [signature2]
293 do_test crash-4.3.$i.1 {
294 crashsql -delay 1 -file test.db-mj* "
295 ATTACH 'test2.db' AS aux;
296 BEGIN;
297 SELECT random() FROM abc LIMIT $i;
298 INSERT INTO abc VALUES(randstr(10,10), 0, 0);
299 DELETE FROM abc WHERE random()%10!=0;
300 INSERT INTO abc2 VALUES(randstr(10,10), 0, 0);
301 DELETE FROM abc2 WHERE random()%10!=0;
302 COMMIT;
303 "
304 } {1 {child process exited abnormally}}
305 do_test crash-4.3.$i.2 {
306 signature
307 } $sig
308 do_test crash-4.3.$i.3 {
309 signature2
310 } $sig2
311 }
312 }
314 #--------------------------------------------------------------------------
315 # The following test cases - crash-5.* - exposes a bug that existed in the
316 # sqlite3pager_movepage() API used by auto-vacuum databases.
317 # database when a crash occurs during a multi-file transaction. See comments
318 # in test crash-5.3 for details.
319 #
320 db close
321 forcedelete test.db
322 sqlite3 db test.db
323 do_test crash-5.1 {
324 execsql {
325 CREATE TABLE abc(a, b, c); -- Root page 3
326 INSERT INTO abc VALUES(randstr(1500,1500), 0, 0); -- Overflow page 4
327 INSERT INTO abc SELECT * FROM abc;
328 INSERT INTO abc SELECT * FROM abc;
329 INSERT INTO abc SELECT * FROM abc;
330 }
331 } {}
332 do_test crash-5.2 {
333 expr [file size test.db] / 1024
334 } [expr [string match [execsql {pragma auto_vacuum}] 1] ? 11 : 10]
335 set sig [signature]
336 do_test crash-5.3 {
337 # The SQL below is used to expose a bug that existed in
338 # sqlite3pager_movepage() during development of the auto-vacuum feature. It
339 # functions as follows:
340 #
341 # 1: Begin a transaction.
342 # 2: Put page 4 on the free-list (was the overflow page for the row deleted).
343 # 3: Write data to page 4 (it becomes the overflow page for the row inserted).
344 # The old page 4 data has been written to the journal file, but the
345 # journal file has not been sync()hronized.
346 # 4: Create a table, which calls sqlite3pager_movepage() to move page 4
347 # to the end of the database (page 12) to make room for the new root-page.
348 # 5: Put pressure on the pager-cache. This results in page 4 being written
349 # to the database file to make space in the cache to load a new page. The
350 # bug was that page 4 was written to the database file before the journal
351 # is sync()hronized.
352 # 6: Commit. A crash occurs during the sync of the journal file.
353 #
354 # End result: Before the bug was fixed, data has been written to page 4 of the
355 # database file and the journal file does not contain trustworthy rollback
356 # data for this page.
357 #
358 crashsql -delay 1 -file test.db-journal {
359 BEGIN; -- 1
360 DELETE FROM abc WHERE oid = 1; -- 2
361 INSERT INTO abc VALUES(randstr(1500,1500), 0, 0); -- 3
362 CREATE TABLE abc2(a, b, c); -- 4
363 SELECT * FROM abc; -- 5
364 COMMIT; -- 6
365 }
366 } {1 {child process exited abnormally}}
367 integrity_check crash-5.4
368 do_test crash-5.5 {
369 signature
370 } $sig
372 #--------------------------------------------------------------------------
373 # The following test cases - crash-6.* - test that a DROP TABLE operation
374 # is correctly rolled back in the event of a crash while the database file
375 # is being written. This is mainly to test that all pages are written to the
376 # journal file before truncation in an auto-vacuum database.
377 #
378 do_test crash-6.1 {
379 crashsql -delay 1 -file test.db {
380 DROP TABLE abc;
381 }
382 } {1 {child process exited abnormally}}
383 do_test crash-6.2 {
384 signature
385 } $sig
387 #--------------------------------------------------------------------------
388 # These test cases test the case where the master journal file name is
389 # corrupted slightly so that the corruption has to be detected by the
390 # checksum.
391 do_test crash-7.1 {
392 crashsql -delay 1 -file test.db {
393 ATTACH 'test2.db' AS aux;
394 BEGIN;
395 INSERT INTO abc VALUES(randstr(1500,1500), 0, 0);
396 INSERT INTO abc2 VALUES(randstr(1500,1500), 0, 0);
397 COMMIT;
398 }
400 # Change the checksum value for the master journal name.
401 set f [open test.db-journal a]
402 fconfigure $f -encoding binary
403 seek $f [expr [file size test.db-journal] - 12]
404 puts -nonewline $f "\00\00\00\00"
405 close $f
406 } {}
407 do_test crash-7.2 {
408 signature
409 } $sig
411 finish_test