1# 2010 May 03 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# This file implements regression tests for SQLite library. The 12# focus of this file is testing the operation of the library in 13# "PRAGMA journal_mode=WAL" mode. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18source $testdir/malloc_common.tcl 19source $testdir/lock_common.tcl 20 21ifcapable !wal {finish_test ; return } 22 23#------------------------------------------------------------------------- 24# This test case, walfault-1-*, simulates faults while executing a 25# 26# PRAGMA journal_mode = WAL; 27# 28# statement immediately after creating a new database. 29# 30do_test walfault-1-pre-1 { 31 faultsim_delete_and_reopen 32 faultsim_save_and_close 33} {} 34do_faultsim_test walfault-1 -prep { 35 faultsim_restore_and_reopen 36} -body { 37 db eval { PRAGMA main.journal_mode = WAL } 38} -test { 39 40 faultsim_test_result {0 wal} 41 42 # Test that the connection that encountered an error as part of 43 # "PRAGMA journal_mode = WAL" and a new connection use the same 44 # journal mode when accessing the database. 45 # 46 # If "PRAGMA journal_mode" is executed immediately, connection [db] (the 47 # one that hit the error in journal_mode="WAL") might return "wal" even 48 # if it failed to switch the database to WAL mode. This is not considered 49 # a problem. When it tries to read the database, connection [db] correctly 50 # recognizes that it is a rollback database and switches back to a 51 # rollback compatible journal mode. 52 # 53 if {[permutation] != "inmemory_journal"} { 54 set jm [db one {SELECT * FROM sqlite_master ; PRAGMA main.journal_mode}] 55 sqlite3 db2 test.db 56 set jm2 [db2 one {SELECT * FROM sqlite_master ; PRAGMA main.journal_mode}] 57 db2 close 58 59 if { $jm!=$jm2 } { error "Journal modes do not match: $jm $jm2" } 60 if { $testrc==0 && $jm!="wal" } { error "Journal mode is not WAL" } 61 } 62} 63 64#-------------------------------------------------------------------------- 65# Test case walfault-2-* tests fault injection during recovery of a 66# short WAL file (a dozen frames or thereabouts). 67# 68do_test walfault-2-pre-1 { 69 sqlite3 db test.db 70 execsql { 71 PRAGMA journal_mode = WAL; 72 BEGIN; 73 CREATE TABLE x(y, z, UNIQUE(y, z)); 74 INSERT INTO x VALUES(randomblob(100), randomblob(100)); 75 COMMIT; 76 PRAGMA wal_checkpoint; 77 78 INSERT INTO x SELECT randomblob(100), randomblob(100) FROM x; 79 INSERT INTO x SELECT randomblob(100), randomblob(100) FROM x; 80 INSERT INTO x SELECT randomblob(100), randomblob(100) FROM x; 81 } 82 execsql { 83 SELECT count(*) FROM x 84 } 85} {8} 86do_test walfault-2-pre-2 { 87 faultsim_save_and_close 88 faultsim_restore_and_reopen 89 execsql { SELECT count(*) FROM x } 90} {8} 91do_faultsim_test walfault-2 -prep { 92 faultsim_restore_and_reopen 93} -body { 94 execsql { SELECT count(*) FROM x } 95} -test { 96 faultsim_test_result {0 8} 97 faultsim_integrity_check 98} 99 100#-------------------------------------------------------------------------- 101# Test fault injection while writing and checkpointing a small WAL file. 102# 103do_test walfault-3-pre-1 { 104 sqlite3 db test.db 105 execsql { 106 PRAGMA auto_vacuum = 1; 107 PRAGMA journal_mode = WAL; 108 CREATE TABLE abc(a PRIMARY KEY); 109 INSERT INTO abc VALUES(randomblob(1500)); 110 } 111 db close 112 faultsim_save_and_close 113} {} 114do_faultsim_test walfault-3 -prep { 115 faultsim_restore_and_reopen 116} -body { 117 db eval { 118 DELETE FROM abc; 119 PRAGMA wal_checkpoint; 120 } 121 set {} {} 122} -test { 123 faultsim_test_result {0 {}} 124} 125 126 127#-------------------------------------------------------------------------- 128# 129if {[permutation] != "inmemory_journal"} { 130 faultsim_delete_and_reopen 131 faultsim_save_and_close 132 do_faultsim_test walfault-4 -prep { 133 faultsim_restore_and_reopen 134 } -body { 135 execsql { 136 PRAGMA auto_vacuum = 0; 137 PRAGMA journal_mode = WAL; 138 CREATE TABLE t1(a PRIMARY KEY, b); 139 INSERT INTO t1 VALUES('a', 'b'); 140 PRAGMA wal_checkpoint; 141 SELECT * FROM t1; 142 } 143 } -test { 144 faultsim_test_result {0 {wal 0 7 7 a b}} 145 faultsim_integrity_check 146 } 147} 148 149#-------------------------------------------------------------------------- 150# 151do_test walfault-5-pre-1 { 152 faultsim_delete_and_reopen 153 execsql { 154 PRAGMA page_size = 512; 155 PRAGMA journal_mode = WAL; 156 } 157 faultsim_save_and_close 158} {} 159do_faultsim_test walfault-5 -faults shmerr* -prep { 160 faultsim_restore_and_reopen 161 execsql { PRAGMA wal_autocheckpoint = 0 } 162 shmfault filter xShmMap 163} -body { 164 execsql { 165 CREATE TABLE t1(x); 166 BEGIN; 167 INSERT INTO t1 VALUES(randomblob(400)); /* 1 */ 168 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 2 */ 169 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 4 */ 170 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 8 */ 171 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 16 */ 172 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 32 */ 173 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 64 */ 174 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 128 */ 175 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 256 */ 176 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 512 */ 177 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 1024 */ 178 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 2048 */ 179 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 4096 */ 180 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 8192 */ 181 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 16384 */ 182 COMMIT; 183 SELECT count(*) FROM t1; 184 } 185} -test { 186 faultsim_test_result {0 16384} 187 faultsim_integrity_check 188} 189 190#-------------------------------------------------------------------------- 191# 192do_test walfault-6-pre-1 { 193 faultsim_delete_and_reopen 194 execsql { 195 PRAGMA page_size = 512; 196 PRAGMA journal_mode = WAL; 197 PRAGMA wal_autocheckpoint = 0; 198 CREATE TABLE t1(x); 199 BEGIN; 200 INSERT INTO t1 VALUES(randomblob(400)); /* 1 */ 201 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 2 */ 202 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 4 */ 203 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 8 */ 204 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 16 */ 205 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 32 */ 206 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 64 */ 207 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 128 */ 208 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 256 */ 209 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 512 */ 210 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 1024 */ 211 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 2048 */ 212 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 4096 */ 213 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 8192 */ 214 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 16384 */ 215 COMMIT; 216 } 217 faultsim_save_and_close 218} {} 219do_faultsim_test walfault-6 -faults shmerr* -prep { 220 faultsim_restore_and_reopen 221 shmfault filter xShmMap 222} -body { 223 execsql { SELECT count(*) FROM t1 } 224} -test { 225 faultsim_test_result {0 16384} 226 faultsim_integrity_check 227 set n [db one {SELECT count(*) FROM t1}] 228 if {$n != 16384 && $n != 0} { error "Incorrect number of rows: $n" } 229} 230 231#-------------------------------------------------------------------------- 232# 233do_test walfault-7-pre-1 { 234 faultsim_delete_and_reopen 235 execsql { 236 PRAGMA page_size = 512; 237 PRAGMA journal_mode = WAL; 238 PRAGMA wal_autocheckpoint = 0; 239 CREATE TABLE t1(x); 240 BEGIN; 241 INSERT INTO t1 VALUES(randomblob(400)); /* 1 */ 242 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 2 */ 243 INSERT INTO t1 SELECT randomblob(400) FROM t1; /* 4 */ 244 COMMIT; 245 } 246 faultsim_save_and_close 247} {} 248do_faultsim_test walfault-7 -prep { 249 faultsim_restore_and_reopen 250} -body { 251 execsql { SELECT count(*) FROM t1 } 252} -test { 253 faultsim_test_result {0 4} 254 set n [db one {SELECT count(*) FROM t1}] 255 if {$n != 4 && $n != 0} { error "Incorrect number of rows: $n" } 256} 257 258#-------------------------------------------------------------------------- 259# 260do_test walfault-8-pre-1 { 261 faultsim_delete_and_reopen 262 execsql { 263 PRAGMA journal_mode = WAL; 264 CREATE TABLE abc(a PRIMARY KEY); 265 INSERT INTO abc VALUES(randomblob(900)); 266 } 267 faultsim_save_and_close 268} {} 269do_faultsim_test walfault-8 -prep { 270 faultsim_restore_and_reopen 271 execsql { PRAGMA cache_size = 10 } 272} -body { 273 execsql { 274 BEGIN; 275 INSERT INTO abc SELECT randomblob(900) FROM abc; /* 1 */ 276 --INSERT INTO abc SELECT randomblob(900) FROM abc; /* 2 */ 277 --INSERT INTO abc SELECT randomblob(900) FROM abc; /* 4 */ 278 --INSERT INTO abc SELECT randomblob(900) FROM abc; /* 8 */ 279 ROLLBACK; 280 SELECT count(*) FROM abc; 281 } 282} -test { 283 faultsim_test_result {0 1} 284 285 faultsim_integrity_check 286 catch { db eval ROLLBACK } 287 faultsim_integrity_check 288 289 set n [db one {SELECT count(*) FROM abc}] 290 if {$n != 1} { error "Incorrect number of rows: $n" } 291} 292 293#-------------------------------------------------------------------------- 294# 295do_test walfault-9-pre-1 { 296 faultsim_delete_and_reopen 297 execsql { 298 PRAGMA journal_mode = WAL; 299 CREATE TABLE abc(a PRIMARY KEY); 300 INSERT INTO abc VALUES(randomblob(900)); 301 } 302 faultsim_save_and_close 303} {} 304do_faultsim_test walfault-9 -prep { 305 #if {$iFail<73} { set iFail 73 } 306 #if {$iFail>73} { exit } 307 308 faultsim_restore_and_reopen 309 execsql { PRAGMA cache_size = 10 } 310} -body { 311 execsql { 312 BEGIN; 313 INSERT INTO abc SELECT randomblob(900) FROM abc; /* 1 */ 314 SAVEPOINT spoint; 315 INSERT INTO abc SELECT randomblob(900) FROM abc; /* 2 */ 316 INSERT INTO abc SELECT randomblob(900) FROM abc; /* 4 */ 317 INSERT INTO abc SELECT randomblob(900) FROM abc; /* 8 */ 318 ROLLBACK TO spoint; 319 COMMIT; 320 SELECT count(*) FROM abc; 321 } 322} -test { 323 faultsim_test_result {0 2} 324 faultsim_integrity_check 325 326 catch { db eval { ROLLBACK TO spoint } } 327 catch { db eval { COMMIT } } 328 set n [db one {SELECT count(*) FROM abc}] 329 if {$n != 1 && $n != 2} { error "Incorrect number of rows: $n" } 330} 331 332do_test walfault-10-pre1 { 333 faultsim_delete_and_reopen 334 execsql { 335 PRAGMA journal_mode = WAL; 336 PRAGMA wal_autocheckpoint = 0; 337 CREATE TABLE z(zz INTEGER PRIMARY KEY, zzz BLOB); 338 CREATE INDEX zzzz ON z(zzz); 339 INSERT INTO z VALUES(NULL, randomblob(800)); 340 INSERT INTO z VALUES(NULL, randomblob(800)); 341 INSERT INTO z SELECT NULL, randomblob(800) FROM z; 342 INSERT INTO z SELECT NULL, randomblob(800) FROM z; 343 INSERT INTO z SELECT NULL, randomblob(800) FROM z; 344 INSERT INTO z SELECT NULL, randomblob(800) FROM z; 345 INSERT INTO z SELECT NULL, randomblob(800) FROM z; 346 } 347 faultsim_save_and_close 348} {} 349do_faultsim_test walfault-10 -prep { 350 faultsim_restore_and_reopen 351 execsql { 352 PRAGMA cache_size = 10; 353 BEGIN; 354 UPDATE z SET zzz = randomblob(799); 355 } 356 357 set ::stmt [sqlite3_prepare db "SELECT zzz FROM z WHERE zz IN (1, 2, 3)" -1] 358 sqlite3_step $::stmt 359} -body { 360 execsql { INSERT INTO z VALUES(NULL, NULL) } 361} -test { 362 sqlite3_finalize $::stmt 363 faultsim_integrity_check 364 365 faultsim_test_result {0 {}} 366 catch { db eval { ROLLBACK } } 367 faultsim_integrity_check 368 369 set n [db eval {SELECT count(*), sum(length(zzz)) FROM z}] 370 if {$n != "64 51200"} { error "Incorrect data: $n" } 371} 372 373#-------------------------------------------------------------------------- 374# Test fault injection while checkpointing a large WAL file, if the 375# checkpoint is the first operation run after opening the database. 376# This means that some of the required wal-index pages are mapped as part of 377# the checkpoint process, which means there are a few more opportunities 378# for IO errors. 379# 380# To speed this up, IO errors are only simulated within xShmMap() calls. 381# 382do_test walfault-11-pre-1 { 383 sqlite3 db test.db 384 execsql { 385 PRAGMA journal_mode = WAL; 386 PRAGMA wal_autocheckpoint = 0; 387 BEGIN; 388 CREATE TABLE abc(a PRIMARY KEY); 389 INSERT INTO abc VALUES(randomblob(1500)); 390 INSERT INTO abc VALUES(randomblob(1500)); 391 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 4 392 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 8 393 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 16 394 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 32 395 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 64 396 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 128 397 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 256 398 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 512 399 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 1024 400 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 2048 401 INSERT INTO abc SELECT randomblob(1500) FROM abc; -- 4096 402 COMMIT; 403 } 404 faultsim_save_and_close 405} {} 406do_faultsim_test walfault-11 -faults shmerr* -prep { 407 catch { db2 close } 408 faultsim_restore_and_reopen 409 shmfault filter xShmMap 410} -body { 411 db eval { SELECT count(*) FROM abc } 412 sqlite3 db2 test.db -vfs shmfault 413 db2 eval { PRAGMA wal_checkpoint } 414 set {} {} 415} -test { 416 faultsim_test_result {0 {}} 417} 418 419#------------------------------------------------------------------------- 420# Test the handling of the various IO/OOM/SHM errors that may occur during 421# a log recovery operation undertaken as part of a call to 422# sqlite3_wal_checkpoint(). 423# 424do_test walfault-12-pre-1 { 425 faultsim_delete_and_reopen 426 execsql { 427 PRAGMA journal_mode = WAL; 428 PRAGMA wal_autocheckpoint = 0; 429 BEGIN; 430 CREATE TABLE abc(a PRIMARY KEY); 431 INSERT INTO abc VALUES(randomblob(1500)); 432 INSERT INTO abc VALUES(randomblob(1500)); 433 COMMIT; 434 } 435 faultsim_save_and_close 436} {} 437do_faultsim_test walfault-12 -prep { 438 if {[info commands shmfault] == ""} { 439 testvfs shmfault -default true 440 } 441 faultsim_restore_and_reopen 442 db eval { SELECT * FROM sqlite_master } 443 shmfault shm test.db [string repeat "\000" 40] 444} -body { 445 set rc [sqlite3_wal_checkpoint db] 446 if {$rc != "SQLITE_OK"} { error [sqlite3_errmsg db] } 447} -test { 448 db close 449 faultsim_test_result {0 {}} 450} 451 452#------------------------------------------------------------------------- 453# Test simple recovery, reading and writing a database file using a 454# heap-memory wal-index. 455# 456do_test walfault-13-pre-1 { 457 faultsim_delete_and_reopen 458 execsql { 459 PRAGMA journal_mode = WAL; 460 PRAGMA wal_autocheckpoint = 0; 461 BEGIN; 462 CREATE TABLE abc(a PRIMARY KEY); 463 INSERT INTO abc VALUES(randomblob(1500)); 464 INSERT INTO abc VALUES(randomblob(1500)); 465 COMMIT; 466 } 467 faultsim_save_and_close 468 file delete sv_test.db-shm 469} {} 470 471do_faultsim_test walfault-13.1 -prep { 472 faultsim_restore_and_reopen 473} -body { 474 db eval { PRAGMA locking_mode = exclusive } 475 db eval { SELECT count(*) FROM abc } 476} -test { 477 faultsim_test_result {0 2} 478 if {[file exists test.db-shm]} { error "Not using heap-memory mode" } 479 faultsim_integrity_check 480} 481 482do_faultsim_test walfault-13.2 -prep { 483 faultsim_restore_and_reopen 484 db eval { PRAGMA locking_mode = exclusive } 485} -body { 486 db eval { PRAGMA journal_mode = delete } 487} -test { 488 faultsim_test_result {0 delete} 489 if {[file exists test.db-shm]} { error "Not using heap-memory mode" } 490 faultsim_integrity_check 491} 492 493do_test walfault-13-pre-2 { 494 faultsim_delete_and_reopen 495 execsql { 496 BEGIN; 497 CREATE TABLE abc(a PRIMARY KEY); 498 INSERT INTO abc VALUES(randomblob(1500)); 499 INSERT INTO abc VALUES(randomblob(1500)); 500 COMMIT; 501 } 502 faultsim_save_and_close 503} {} 504 505do_faultsim_test walfault-13.3 -prep { 506 faultsim_restore_and_reopen 507} -body { 508 db eval { 509 PRAGMA locking_mode = exclusive; 510 PRAGMA journal_mode = WAL; 511 INSERT INTO abc VALUES(randomblob(1500)); 512 } 513} -test { 514 faultsim_test_result {0 {exclusive wal}} 515 if {[file exists test.db-shm]} { error "Not using heap-memory mode" } 516 faultsim_integrity_check 517 set nRow [db eval {SELECT count(*) FROM abc}] 518 if {!(($nRow==2 && $testrc) || $nRow==3)} { error "Bad db content" } 519} 520 521#------------------------------------------------------------------------- 522# Test fault-handling when wrapping around to the start of a WAL file. 523# 524do_test walfault-14-pre { 525 faultsim_delete_and_reopen 526 execsql { 527 PRAGMA auto_vacuum = 0; 528 PRAGMA journal_mode = WAL; 529 BEGIN; 530 CREATE TABLE abc(a PRIMARY KEY); 531 INSERT INTO abc VALUES(randomblob(1500)); 532 INSERT INTO abc VALUES(randomblob(1500)); 533 COMMIT; 534 } 535 faultsim_save_and_close 536} {} 537do_faultsim_test walfault-14 -prep { 538 faultsim_restore_and_reopen 539} -body { 540 db eval { 541 PRAGMA wal_checkpoint = full; 542 INSERT INTO abc VALUES(randomblob(1500)); 543 } 544} -test { 545 faultsim_test_result {0 {0 10 10}} 546 faultsim_integrity_check 547 set nRow [db eval {SELECT count(*) FROM abc}] 548 if {!(($nRow==2 && $testrc) || $nRow==3)} { error "Bad db content" } 549} 550 551finish_test 552