1# 2009 January 8 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# 12# This test verifies a couple of specific potential data corruption 13# scenarios involving crashes or power failures. 14# 15# Later: Also, some other specific scenarios required for coverage 16# testing that do not lead to corruption. 17# 18# $Id: crash8.test,v 1.4 2009/01/11 00:44:48 drh Exp $ 19 20 21set testdir [file dirname $argv0] 22source $testdir/tester.tcl 23 24ifcapable !crashtest { 25 finish_test 26 return 27} 28do_not_use_codec 29 30do_test crash8-1.1 { 31 execsql { 32 PRAGMA auto_vacuum=OFF; 33 CREATE TABLE t1(a, b); 34 CREATE INDEX i1 ON t1(a, b); 35 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 36 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 37 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 38 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 39 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 40 INSERT INTO t1 VALUES(6, randstr(1000,1000)); 41 CREATE TABLE t2(a, b); 42 CREATE TABLE t3(a, b); 43 CREATE TABLE t4(a, b); 44 CREATE TABLE t5(a, b); 45 CREATE TABLE t6(a, b); 46 CREATE TABLE t7(a, b); 47 CREATE TABLE t8(a, b); 48 CREATE TABLE t9(a, b); 49 CREATE TABLE t10(a, b); 50 PRAGMA integrity_check 51 } 52} {ok} 53 54 55# Potential corruption scenario 1. A second process opens the database 56# and modifies a large portion of it. It then opens a second transaction 57# and modifies a small part of the database, but crashes before it commits 58# the transaction. 59# 60# When the first process accessed the database again, it was rolling back 61# the aborted transaction, but was not purging its in-memory cache (which 62# was loaded before the second process made its first, successful, 63# modification). Producing an inconsistent cache. 64# 65do_test crash8-1.2 { 66 crashsql -delay 2 -file test.db { 67 PRAGMA cache_size = 10; 68 UPDATE t1 SET b = randstr(1000,1000); 69 INSERT INTO t9 VALUES(1, 2); 70 } 71} {1 {child process exited abnormally}} 72do_test crash8-1.3 { 73 execsql {PRAGMA integrity_check} 74} {ok} 75 76# Potential corruption scenario 2. The second process, operating in 77# persistent-journal mode, makes a large change to the database file 78# with a small in-memory cache. Such that more than one journal-header 79# was written to the file. It then opens a second transaction and makes 80# a smaller change that requires only a single journal-header to be 81# written to the journal file. The second change is such that the 82# journal content written to the persistent journal file exactly overwrites 83# the first journal-header and set of subsequent records written by the 84# first, successful, change. The second process crashes before it can 85# commit its second change. 86# 87# When the first process accessed the database again, it was rolling back 88# the second aborted transaction, then continuing to rollback the second 89# and subsequent journal-headers written by the first, successful, change. 90# Database corruption. 91# 92do_test crash8.2.1 { 93 crashsql -delay 2 -file test.db { 94 PRAGMA journal_mode = persist; 95 PRAGMA cache_size = 10; 96 UPDATE t1 SET b = randstr(1000,1000); 97 PRAGMA cache_size = 100; 98 BEGIN; 99 INSERT INTO t2 VALUES('a', 'b'); 100 INSERT INTO t3 VALUES('a', 'b'); 101 INSERT INTO t4 VALUES('a', 'b'); 102 INSERT INTO t5 VALUES('a', 'b'); 103 INSERT INTO t6 VALUES('a', 'b'); 104 INSERT INTO t7 VALUES('a', 'b'); 105 INSERT INTO t8 VALUES('a', 'b'); 106 INSERT INTO t9 VALUES('a', 'b'); 107 INSERT INTO t10 VALUES('a', 'b'); 108 COMMIT; 109 } 110} {1 {child process exited abnormally}} 111 112do_test crash8-2.3 { 113 execsql {PRAGMA integrity_check} 114} {ok} 115 116proc read_file {zFile} { 117 set fd [open $zFile] 118 fconfigure $fd -translation binary 119 set zData [read $fd] 120 close $fd 121 return $zData 122} 123proc write_file {zFile zData} { 124 set fd [open $zFile w] 125 fconfigure $fd -translation binary 126 puts -nonewline $fd $zData 127 close $fd 128} 129 130# The following tests check that SQLite will not roll back a hot-journal 131# file if the sector-size field in the first journal file header is 132# suspect. Definition of suspect: 133# 134# a) Not a power of 2, or (crash8-3.5) 135# b) Greater than 0x01000000 (16MB), or (crash8-3.6) 136# c) Less than 512. (crash8-3.7) 137# 138# Also test that SQLite will not rollback a hot-journal file with a 139# suspect page-size. In this case "suspect" means: 140# 141# a) Not a power of 2, or 142# b) Less than 512, or 143# c) Greater than SQLITE_MAX_PAGE_SIZE 144# 145do_test crash8-3.1 { 146 list [file exists test.db-joural] [file exists test.db] 147} {0 1} 148do_test crash8-3.2 { 149 execsql { 150 PRAGMA synchronous = off; 151 BEGIN; 152 DELETE FROM t1; 153 SELECT count(*) FROM t1; 154 } 155} {0} 156do_test crash8-3.3 { 157 set zJournal [read_file test.db-journal] 158 execsql { 159 COMMIT; 160 SELECT count(*) FROM t1; 161 } 162} {0} 163do_test crash8-3.4 { 164 binary scan [string range $zJournal 20 23] I nSector 165 set nSector 166} {512} 167 168do_test crash8-3.5 { 169 set zJournal2 [string replace $zJournal 20 23 [binary format I 513]] 170 write_file test.db-journal $zJournal2 171 172 execsql { 173 SELECT count(*) FROM t1; 174 PRAGMA integrity_check 175 } 176} {0 ok} 177do_test crash8-3.6 { 178 set zJournal2 [string replace $zJournal 20 23 [binary format I 0x2000000]] 179 write_file test.db-journal $zJournal2 180 execsql { 181 SELECT count(*) FROM t1; 182 PRAGMA integrity_check 183 } 184} {0 ok} 185do_test crash8-3.7 { 186 set zJournal2 [string replace $zJournal 20 23 [binary format I 256]] 187 write_file test.db-journal $zJournal2 188 execsql { 189 SELECT count(*) FROM t1; 190 PRAGMA integrity_check 191 } 192} {0 ok} 193 194do_test crash8-3.8 { 195 set zJournal2 [string replace $zJournal 24 27 [binary format I 513]] 196 write_file test.db-journal $zJournal2 197 198 execsql { 199 SELECT count(*) FROM t1; 200 PRAGMA integrity_check 201 } 202} {0 ok} 203do_test crash8-3.9 { 204 set big [expr $SQLITE_MAX_PAGE_SIZE * 2] 205 set zJournal2 [string replace $zJournal 24 27 [binary format I $big]] 206 write_file test.db-journal $zJournal2 207 execsql { 208 SELECT count(*) FROM t1; 209 PRAGMA integrity_check 210 } 211} {0 ok} 212do_test crash8-3.10 { 213 set zJournal2 [string replace $zJournal 24 27 [binary format I 256]] 214 write_file test.db-journal $zJournal2 215 execsql { 216 SELECT count(*) FROM t1; 217 PRAGMA integrity_check 218 } 219} {0 ok} 220 221do_test crash8-3.11 { 222 set fd [open test.db-journal w] 223 fconfigure $fd -translation binary 224 puts -nonewline $fd $zJournal 225 close $fd 226 execsql { 227 SELECT count(*) FROM t1; 228 PRAGMA integrity_check 229 } 230} {6 ok} 231 232 233# If a connection running in persistent-journal mode is part of a 234# multi-file transaction, it must ensure that the master-journal name 235# appended to the journal file contents during the commit is located 236# at the end of the physical journal file. If there was already a 237# large journal file allocated at the start of the transaction, this 238# may mean truncating the file so that the master journal name really 239# is at the physical end of the file. 240# 241# This block of tests test that SQLite correctly truncates such 242# journal files, and that the results behave correctly if a hot-journal 243# rollback occurs. 244# 245ifcapable pragma { 246 reset_db 247 forcedelete test2.db 248 249 do_test crash8-4.1 { 250 execsql { 251 PRAGMA journal_mode = persist; 252 CREATE TABLE ab(a, b); 253 INSERT INTO ab VALUES(0, 'abc'); 254 INSERT INTO ab VALUES(1, NULL); 255 INSERT INTO ab VALUES(2, NULL); 256 INSERT INTO ab VALUES(3, NULL); 257 INSERT INTO ab VALUES(4, NULL); 258 INSERT INTO ab VALUES(5, NULL); 259 INSERT INTO ab VALUES(6, NULL); 260 UPDATE ab SET b = randstr(1000,1000); 261 ATTACH 'test2.db' AS aux; 262 PRAGMA aux.journal_mode = persist; 263 CREATE TABLE aux.ab(a, b); 264 INSERT INTO aux.ab SELECT * FROM main.ab; 265 266 UPDATE aux.ab SET b = randstr(1000,1000) WHERE a>=1; 267 UPDATE ab SET b = randstr(1000,1000) WHERE a>=1; 268 } 269 list [file exists test.db-journal] [file exists test2.db-journal] 270 } {1 1} 271 272 do_test crash8-4.2 { 273 execsql { 274 BEGIN; 275 UPDATE aux.ab SET b = 'def' WHERE a = 0; 276 UPDATE main.ab SET b = 'def' WHERE a = 0; 277 COMMIT; 278 } 279 } {} 280 281 do_test crash8-4.3 { 282 execsql { 283 UPDATE aux.ab SET b = randstr(1000,1000) WHERE a>=1; 284 UPDATE ab SET b = randstr(1000,1000) WHERE a>=1; 285 } 286 } {} 287 288 set contents_main [db eval {SELECT b FROM main.ab WHERE a = 1}] 289 set contents_aux [db eval {SELECT b FROM aux.ab WHERE a = 1}] 290 291 do_test crash8-4.4 { 292 crashsql -file test2.db -delay 1 { 293 ATTACH 'test2.db' AS aux; 294 BEGIN; 295 UPDATE aux.ab SET b = 'ghi' WHERE a = 0; 296 UPDATE main.ab SET b = 'ghi' WHERE a = 0; 297 COMMIT; 298 } 299 } {1 {child process exited abnormally}} 300 301 do_test crash8-4.5 { 302 list [file exists test.db-journal] [file exists test2.db-journal] 303 } {1 1} 304 305 do_test crash8-4.6 { 306 execsql { 307 SELECT b FROM main.ab WHERE a = 0; 308 SELECT b FROM aux.ab WHERE a = 0; 309 } 310 } {def def} 311 312 do_test crash8-4.7 { 313 crashsql -file test2.db -delay 1 { 314 ATTACH 'test2.db' AS aux; 315 BEGIN; 316 UPDATE aux.ab SET b = 'jkl' WHERE a = 0; 317 UPDATE main.ab SET b = 'jkl' WHERE a = 0; 318 COMMIT; 319 } 320 } {1 {child process exited abnormally}} 321 322 do_test crash8-4.8 { 323 set fd [open test.db-journal] 324 fconfigure $fd -translation binary 325 seek $fd -16 end 326 binary scan [read $fd 4] I len 327 328 seek $fd [expr {-1 * ($len + 16)}] end 329 set zMasterJournal [read $fd $len] 330 close $fd 331 332 file exists $zMasterJournal 333 } {1} 334 335 do_test crash8-4.9 { 336 execsql { SELECT b FROM aux.ab WHERE a = 0 } 337 } {def} 338 339 do_test crash8-4.10 { 340 delete_file $zMasterJournal 341 execsql { SELECT b FROM main.ab WHERE a = 0 } 342 } {jkl} 343} 344 345# 346# Since the following tests (crash8-5.*) rely upon being able 347# to copy a file while open, they will not work on Windows. 348# 349if {$::tcl_platform(platform)=="unix"} { 350 for {set i 1} {$i < 10} {incr i} { 351 catch { db close } 352 forcedelete test.db test.db-journal 353 sqlite3 db test.db 354 do_test crash8-5.$i.1 { 355 execsql { 356 CREATE TABLE t1(x PRIMARY KEY); 357 INSERT INTO t1 VALUES(randomblob(900)); 358 INSERT INTO t1 SELECT randomblob(900) FROM t1; 359 INSERT INTO t1 SELECT randomblob(900) FROM t1; 360 INSERT INTO t1 SELECT randomblob(900) FROM t1; 361 INSERT INTO t1 SELECT randomblob(900) FROM t1; 362 INSERT INTO t1 SELECT randomblob(900) FROM t1; 363 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 64 rows */ 364 } 365 crashsql -file test.db -delay [expr ($::i%2) + 1] { 366 PRAGMA cache_size = 10; 367 BEGIN; 368 UPDATE t1 SET x = randomblob(900); 369 ROLLBACK; 370 INSERT INTO t1 VALUES(randomblob(900)); 371 } 372 execsql { PRAGMA integrity_check } 373 } {ok} 374 375 catch { db close } 376 forcedelete test.db test.db-journal 377 sqlite3 db test.db 378 do_test crash8-5.$i.2 { 379 execsql { 380 PRAGMA cache_size = 10; 381 CREATE TABLE t1(x PRIMARY KEY); 382 INSERT INTO t1 VALUES(randomblob(900)); 383 INSERT INTO t1 SELECT randomblob(900) FROM t1; 384 INSERT INTO t1 SELECT randomblob(900) FROM t1; 385 INSERT INTO t1 SELECT randomblob(900) FROM t1; 386 INSERT INTO t1 SELECT randomblob(900) FROM t1; 387 INSERT INTO t1 SELECT randomblob(900) FROM t1; 388 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 64 rows */ 389 BEGIN; 390 UPDATE t1 SET x = randomblob(900); 391 } 392 forcedelete testX.db testX.db-journal testX.db-wal 393 forcecopy test.db testX.db 394 forcecopy test.db-journal testX.db-journal 395 db close 396 397 crashsql -file test.db -delay [expr ($::i%2) + 1] { 398 SELECT * FROM sqlite_master; 399 INSERT INTO t1 VALUES(randomblob(900)); 400 } 401 402 sqlite3 db2 testX.db 403 execsql { PRAGMA integrity_check } db2 404 } {ok} 405 } 406 catch {db2 close} 407} 408 409finish_test 410