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# 145if {[atomic_batch_write test.db]==0} { 146do_test crash8-3.1 { 147 list [file exists test.db-joural] [file exists test.db] 148} {0 1} 149do_test crash8-3.2 { 150 execsql { 151 PRAGMA synchronous = off; 152 BEGIN; 153 DELETE FROM t1; 154 SELECT count(*) FROM t1; 155 } 156} {0} 157do_test crash8-3.3 { 158 set zJournal [read_file test.db-journal] 159 execsql { 160 COMMIT; 161 SELECT count(*) FROM t1; 162 } 163} {0} 164do_test crash8-3.4 { 165 binary scan [string range $zJournal 20 23] I nSector 166 set nSector 167} {512} 168 169do_test crash8-3.5 { 170 set zJournal2 [string replace $zJournal 20 23 [binary format I 513]] 171 write_file test.db-journal $zJournal2 172 173 execsql { 174 SELECT count(*) FROM t1; 175 PRAGMA integrity_check 176 } 177} {0 ok} 178do_test crash8-3.6 { 179 set zJournal2 [string replace $zJournal 20 23 [binary format I 0x2000000]] 180 write_file test.db-journal $zJournal2 181 execsql { 182 SELECT count(*) FROM t1; 183 PRAGMA integrity_check 184 } 185} {0 ok} 186do_test crash8-3.7 { 187 set zJournal2 [string replace $zJournal 20 23 [binary format I 256]] 188 write_file test.db-journal $zJournal2 189 execsql { 190 SELECT count(*) FROM t1; 191 PRAGMA integrity_check 192 } 193} {0 ok} 194 195do_test crash8-3.8 { 196 set zJournal2 [string replace $zJournal 24 27 [binary format I 513]] 197 write_file test.db-journal $zJournal2 198 199 execsql { 200 SELECT count(*) FROM t1; 201 PRAGMA integrity_check 202 } 203} {0 ok} 204do_test crash8-3.9 { 205 set big [expr $SQLITE_MAX_PAGE_SIZE * 2] 206 set zJournal2 [string replace $zJournal 24 27 [binary format I $big]] 207 write_file test.db-journal $zJournal2 208 execsql { 209 SELECT count(*) FROM t1; 210 PRAGMA integrity_check 211 } 212} {0 ok} 213do_test crash8-3.10 { 214 set zJournal2 [string replace $zJournal 24 27 [binary format I 256]] 215 write_file test.db-journal $zJournal2 216 execsql { 217 SELECT count(*) FROM t1; 218 PRAGMA integrity_check 219 } 220} {0 ok} 221 222do_test crash8-3.11 { 223 set fd [open test.db-journal w] 224 fconfigure $fd -translation binary 225 puts -nonewline $fd $zJournal 226 close $fd 227 execsql { 228 SELECT count(*) FROM t1; 229 PRAGMA integrity_check 230 } 231} {6 ok} 232} 233 234 235# If a connection running in persistent-journal mode is part of a 236# multi-file transaction, it must ensure that the master-journal name 237# appended to the journal file contents during the commit is located 238# at the end of the physical journal file. If there was already a 239# large journal file allocated at the start of the transaction, this 240# may mean truncating the file so that the master journal name really 241# is at the physical end of the file. 242# 243# This block of tests test that SQLite correctly truncates such 244# journal files, and that the results behave correctly if a hot-journal 245# rollback occurs. 246# 247ifcapable pragma { 248 reset_db 249 forcedelete test2.db 250 251 do_test crash8-4.1 { 252 execsql { 253 PRAGMA journal_mode = persist; 254 CREATE TABLE ab(a, b); 255 INSERT INTO ab VALUES(0, 'abc'); 256 INSERT INTO ab VALUES(1, NULL); 257 INSERT INTO ab VALUES(2, NULL); 258 INSERT INTO ab VALUES(3, NULL); 259 INSERT INTO ab VALUES(4, NULL); 260 INSERT INTO ab VALUES(5, NULL); 261 INSERT INTO ab VALUES(6, NULL); 262 UPDATE ab SET b = randstr(1000,1000); 263 ATTACH 'test2.db' AS aux; 264 PRAGMA aux.journal_mode = persist; 265 CREATE TABLE aux.ab(a, b); 266 INSERT INTO aux.ab SELECT * FROM main.ab; 267 268 UPDATE aux.ab SET b = randstr(1000,1000) WHERE a>=1; 269 UPDATE ab SET b = randstr(1000,1000) WHERE a>=1; 270 } 271 } {persist persist} 272 if {[atomic_batch_write test.db]==0} { 273 do_test crash8.4.1.1 { 274 list [file exists test.db-journal] [file exists test2.db-journal] 275 } {1 1} 276 } 277 278 do_test crash8-4.2 { 279 execsql { 280 BEGIN; 281 UPDATE aux.ab SET b = 'def' WHERE a = 0; 282 UPDATE main.ab SET b = 'def' WHERE a = 0; 283 COMMIT; 284 } 285 } {} 286 287 do_test crash8-4.3 { 288 execsql { 289 UPDATE aux.ab SET b = randstr(1000,1000) WHERE a>=1; 290 UPDATE ab SET b = randstr(1000,1000) WHERE a>=1; 291 } 292 } {} 293 294 set contents_main [db eval {SELECT b FROM main.ab WHERE a = 1}] 295 set contents_aux [db eval {SELECT b FROM aux.ab WHERE a = 1}] 296 297 do_test crash8-4.4 { 298 crashsql -file test2.db -delay 1 { 299 ATTACH 'test2.db' AS aux; 300 BEGIN; 301 UPDATE aux.ab SET b = 'ghi' WHERE a = 0; 302 UPDATE main.ab SET b = 'ghi' WHERE a = 0; 303 COMMIT; 304 } 305 } {1 {child process exited abnormally}} 306 307 do_test crash8-4.5 { 308 list [file exists test.db-journal] [file exists test2.db-journal] 309 } {1 1} 310 311 do_test crash8-4.6 { 312 execsql { 313 SELECT b FROM main.ab WHERE a = 0; 314 SELECT b FROM aux.ab WHERE a = 0; 315 } 316 } {def def} 317 318 do_test crash8-4.7 { 319 crashsql -file test2.db -delay 1 { 320 ATTACH 'test2.db' AS aux; 321 BEGIN; 322 UPDATE aux.ab SET b = 'jkl' WHERE a = 0; 323 UPDATE main.ab SET b = 'jkl' WHERE a = 0; 324 COMMIT; 325 } 326 } {1 {child process exited abnormally}} 327 328 do_test crash8-4.8 { 329 set fd [open test.db-journal] 330 fconfigure $fd -translation binary 331 seek $fd -16 end 332 binary scan [read $fd 4] I len 333 334 seek $fd [expr {-1 * ($len + 16)}] end 335 set zMasterJournal [read $fd $len] 336 close $fd 337 338 file exists $zMasterJournal 339 } {1} 340 341 do_test crash8-4.9 { 342 execsql { SELECT b FROM aux.ab WHERE a = 0 } 343 } {def} 344 345 do_test crash8-4.10 { 346 delete_file $zMasterJournal 347 execsql { SELECT b FROM main.ab WHERE a = 0 } 348 } {jkl} 349} 350 351# 352# Since the following tests (crash8-5.*) rely upon being able 353# to copy a file while open, they will not work on Windows. 354# 355# They also depend on being able to copy the journal file, which 356# is not created on F2FS file-systems that support atomic 357# write. So do not run these tests in that case either. 358# 359if {$::tcl_platform(platform)=="unix" && [atomic_batch_write test.db]==0 } { 360 for {set i 1} {$i < 10} {incr i} { 361 catch { db close } 362 forcedelete test.db test.db-journal 363 sqlite3 db test.db 364 do_test crash8-5.$i.1 { 365 execsql { 366 CREATE TABLE t1(x PRIMARY KEY); 367 INSERT INTO t1 VALUES(randomblob(900)); 368 INSERT INTO t1 SELECT randomblob(900) FROM t1; 369 INSERT INTO t1 SELECT randomblob(900) FROM t1; 370 INSERT INTO t1 SELECT randomblob(900) FROM t1; 371 INSERT INTO t1 SELECT randomblob(900) FROM t1; 372 INSERT INTO t1 SELECT randomblob(900) FROM t1; 373 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 64 rows */ 374 } 375 crashsql -file test.db -delay [expr ($::i%2) + 1] { 376 PRAGMA cache_size = 10; 377 BEGIN; 378 UPDATE t1 SET x = randomblob(900); 379 ROLLBACK; 380 INSERT INTO t1 VALUES(randomblob(900)); 381 } 382 execsql { PRAGMA integrity_check } 383 } {ok} 384 385 catch { db close } 386 forcedelete test.db test.db-journal 387 sqlite3 db test.db 388 do_test crash8-5.$i.2 { 389 execsql { 390 PRAGMA cache_size = 10; 391 CREATE TABLE t1(x PRIMARY KEY); 392 INSERT INTO t1 VALUES(randomblob(900)); 393 INSERT INTO t1 SELECT randomblob(900) FROM t1; 394 INSERT INTO t1 SELECT randomblob(900) FROM t1; 395 INSERT INTO t1 SELECT randomblob(900) FROM t1; 396 INSERT INTO t1 SELECT randomblob(900) FROM t1; 397 INSERT INTO t1 SELECT randomblob(900) FROM t1; 398 INSERT INTO t1 SELECT randomblob(900) FROM t1; /* 64 rows */ 399 BEGIN; 400 UPDATE t1 SET x = randomblob(900); 401 } 402 forcedelete testX.db testX.db-journal testX.db-wal 403 forcecopy test.db testX.db 404 forcecopy test.db-journal testX.db-journal 405 db close 406 407 crashsql -file test.db -delay [expr ($::i%2) + 1] { 408 SELECT * FROM sqlite_master; 409 INSERT INTO t1 VALUES(randomblob(900)); 410 } 411 412 sqlite3 db2 testX.db 413 execsql { PRAGMA integrity_check } db2 414 } {ok} 415 } 416 catch {db2 close} 417} 418 419finish_test 420