AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# 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 to14# uses its rollback journal to recover intact (no database corruption)15# from a power failure during the middle of a COMMIT. The OS interface16# 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 $21 22set testdir [file dirname $argv0]23source $testdir/tester.tcl24 25ifcapable !crashtest {26 finish_test27 return28}29 30set repeats 10031#set repeats 1032 33# The following procedure computes a "signature" for table "abc". If34# abc changes in any way, the signature should change. 35proc signature {} {36 return [db eval {SELECT count(*), md5sum(a), md5sum(b), md5sum(c) FROM abc}]37}38proc 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 during47# 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 during50# 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 during53# 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 and57# second database sync. Neither of these is required in such a small test58# case, so these tests are just to verify that the test infrastructure59# operates as expected.60#61do_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 069} {0}70for {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 signature79 } $::sig80}81do_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}}86do_test crash-1.5 {87 signature88} $::sig89do_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}}94do_test crash-1.7 {95 catchsql {96 SELECT * FROM abc;97 }98} {0 {1 2 3 4 5 6}}99 100do_test crash-1.8 {101 crashsql -delay 3 -file test.db-journal {102 DELETE FROM abc WHERE a = 1;103 }104} {0 {}}105do_test crash-1.9 {106 catchsql {107 SELECT * FROM abc;108 }109} {0 {4 5 6}}110do_test crash-1.10 {111 crashsql -delay 2 -file test.db {112 DELETE FROM abc WHERE a = 4;113 }114} {0 {}}115do_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 the123# journal file contain corrupt data. This can happen after pages are124# written to the database file before a transaction is committed due to125# 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 really134# different from the crash-1.* cases.135#136do_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}145do_test crash-2.2 {146 expr ([file size test.db] / 1024)>16147} {1}148do_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}}153do_test crash-2.4 {154 signature155} $sig156do_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}}161do_test crash-2.6 {162 signature163} $sig164 165#--------------------------------------------------------------------------166# The crash-3.* test cases are essentially the same test as test case167# crash-2.*, but with a more complicated data set. 168#169# The test is repeated a few times with different seeds for the random170# number generator in the crashing executable. Because there is no way to171# seed the random number generator directly, some SQL is added to the test172# case to 'use up' a different quantity random numbers before the test SQL173# is executed.174#175 176# Make sure the file is much bigger than the pager-cache (10 pages). This177# ensures that cache-spills happen regularly.178do_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) > 450187} {1}188for {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 signature202 } $sig203} 204 205#--------------------------------------------------------------------------206# The following test cases - crash-4.* - test the correct recovery of the207# 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 master214# journal file. 215#216ifcapable attach {217 do_test crash-4.0 {218 forcedelete test2.db219 forcedelete test2.db-journal220 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) > 450226 } {1}227 228 set fin 0229 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 1246 set c {1 {child process exited abnormally}}247 }248 set c249 } {1 {child process exited abnormally}}250 if {$::fin} break251 do_test crash-4.1.$i.2 {252 signature253 } $sig254 do_test crash-4.1.$i.3 {255 signature2256 } $sig2257 } 258 set i 0259 set fin 0260 while {[incr i]} {261 set seed [expr {int(abs(rand()*10000))}]262 set sig [signature]263 set sig2 [signature2]264 set ::fin 0265 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 1278 set c {1 {child process exited abnormally}}279 }280 set c281 } {1 {child process exited abnormally}}282 if { $::fin } break283 do_test crash-4.2.$i.2 {284 signature285 } $sig286 do_test crash-4.2.$i.3 {287 signature2288 } $sig2289 } 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 signature307 } $sig308 do_test crash-4.3.$i.3 {309 signature2310 } $sig2311 }312}313 314#--------------------------------------------------------------------------315# The following test cases - crash-5.* - exposes a bug that existed in the316# sqlite3pager_movepage() API used by auto-vacuum databases.317# database when a crash occurs during a multi-file transaction. See comments318# in test crash-5.3 for details.319#320db close321forcedelete test.db322sqlite3 db test.db323do_test crash-5.1 {324 execsql {325 CREATE TABLE abc(a, b, c); -- Root page 3326 INSERT INTO abc VALUES(randstr(1500,1500), 0, 0); -- Overflow page 4327 INSERT INTO abc SELECT * FROM abc;328 INSERT INTO abc SELECT * FROM abc;329 INSERT INTO abc SELECT * FROM abc;330 }331} {}332do_test crash-5.2 {333 expr [file size test.db] / 1024334} [expr [string match [execsql {pragma auto_vacuum}] 1] ? 11 : 10]335set sig [signature]336do_test crash-5.3 {337# The SQL below is used to expose a bug that existed in338# sqlite3pager_movepage() during development of the auto-vacuum feature. It339# 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 the345# journal file has not been sync()hronized.346# 4: Create a table, which calls sqlite3pager_movepage() to move page 4347# 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 written349# to the database file to make space in the cache to load a new page. The350# bug was that page 4 was written to the database file before the journal351# 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 the355# database file and the journal file does not contain trustworthy rollback356# data for this page.357#358 crashsql -delay 1 -file test.db-journal {359 BEGIN; -- 1360 DELETE FROM abc WHERE oid = 1; -- 2361 INSERT INTO abc VALUES(randstr(1500,1500), 0, 0); -- 3362 CREATE TABLE abc2(a, b, c); -- 4363 SELECT * FROM abc; -- 5364 COMMIT; -- 6365 }366} {1 {child process exited abnormally}}367integrity_check crash-5.4368do_test crash-5.5 {369 signature370} $sig371 372#--------------------------------------------------------------------------373# The following test cases - crash-6.* - test that a DROP TABLE operation374# is correctly rolled back in the event of a crash while the database file375# is being written. This is mainly to test that all pages are written to the376# journal file before truncation in an auto-vacuum database.377#378do_test crash-6.1 {379 crashsql -delay 1 -file test.db {380 DROP TABLE abc;381 }382} {1 {child process exited abnormally}}383do_test crash-6.2 {384 signature385} $sig386 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 the390# checksum.391do_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 -translation binary403 seek $f [expr [file size test.db-journal] - 12]404 puts -nonewline $f "\00\00\00\00"405 close $f406} {}407do_test crash-7.2 {408 signature409} $sig410 411finish_test412