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.1.1.1 2012/02/21 17:04:16 misho Exp $
21:
22: set testdir [file dirname $argv0]
23: source $testdir/tester.tcl
24:
25: ifcapable !crashtest {
26: finish_test
27: return
28: }
29:
30: set repeats 100
31: #set repeats 10
32:
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: }
41:
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}}
99:
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 {}}
120:
121: #--------------------------------------------------------------------------
122: # The following tests test recovery when both the database file and the 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
164:
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: #
175:
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: }
204:
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}
227:
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: }
313:
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
371:
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
386:
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: }
399:
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
410:
411: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>