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