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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 source $testdir/malloc_common.tcl 19 source $testdir/lock_common.tcl 20 21 ifcapable !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 # 30 do_test walfault-1-pre-1 { 31 faultsim_delete_and_reopen 32 faultsim_save_and_close 33 } {} 34 do_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 # 68 do_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} 86 do_test walfault-2-pre-2 { 87 faultsim_save_and_close 88 faultsim_restore_and_reopen 89 execsql { SELECT count(*) FROM x } 90 } {8} 91 do_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 # 103 do_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 } {} 114 do_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 # 129 if {[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 # 151 do_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 } {} 159 do_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 # 192 do_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 } {} 219 do_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 # 233 do_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 } {} 248 do_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 # 260 do_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 } {} 269 do_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 # 295 do_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 } {} 304 do_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 332 do_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 } {} 349 do_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 # 382 do_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 } {} 406 do_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 # 424 do_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 } {} 437 do_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 # 456 do_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 471 do_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 482 do_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 493 do_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 505 do_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 # 524 do_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 } {} 537 do_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 551 finish_test 552
