gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/crash8.test (about) 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 21 set testdir [file dirname $argv0] 22 source $testdir/tester.tcl 23 24 ifcapable !crashtest { 25 finish_test 26 return 27 } 28 do_not_use_codec 29 30 do_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 # 65 do_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}} 72 do_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 # 92 do_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 112 do_test crash8-2.3 { 113 execsql {PRAGMA integrity_check} 114 } {ok} 115 116 proc 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 } 123 proc 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 # 145 if {[atomic_batch_write test.db]==0} { 146 do_test crash8-3.1 { 147 list [file exists test.db-joural] [file exists test.db] 148 } {0 1} 149 do_test crash8-3.2 { 150 execsql { 151 PRAGMA synchronous = off; 152 BEGIN; 153 DELETE FROM t1; 154 SELECT count(*) FROM t1; 155 } 156 } {0} 157 do_test crash8-3.3 { 158 set zJournal [read_file test.db-journal] 159 execsql { 160 COMMIT; 161 SELECT count(*) FROM t1; 162 } 163 } {0} 164 do_test crash8-3.4 { 165 binary scan [string range $zJournal 20 23] I nSector 166 set nSector 167 } {512} 168 169 do_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} 178 do_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} 186 do_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 195 do_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} 204 do_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} 213 do_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 222 do_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 # 247 ifcapable 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 # 359 if {$::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 419 finish_test