1# 2010 April 13 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/lock_common.tcl 19source $testdir/wal_common.tcl 20source $testdir/malloc_common.tcl 21ifcapable !wal {finish_test ; return } 22 23set a_string_counter 1 24proc a_string {n} { 25 global a_string_counter 26 incr a_string_counter 27 string range [string repeat "${a_string_counter}." $n] 1 $n 28} 29db func a_string a_string 30 31#------------------------------------------------------------------------- 32# When a rollback or savepoint rollback occurs, the client may remove 33# elements from one of the hash tables in the wal-index. This block 34# of test cases tests that nothing appears to go wrong when this is 35# done. 36# 37do_test wal3-1.0 { 38 execsql { 39 PRAGMA cache_size = 2000; 40 PRAGMA page_size = 1024; 41 PRAGMA auto_vacuum = off; 42 PRAGMA synchronous = normal; 43 PRAGMA journal_mode = WAL; 44 PRAGMA wal_autocheckpoint = 0; 45 BEGIN; 46 CREATE TABLE t1(x); 47 INSERT INTO t1 VALUES( a_string(800) ); /* 1 */ 48 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */ 49 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */ 50 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */ 51 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */ 52 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */ 53 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 64 */ 54 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 128*/ 55 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 256 */ 56 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 512 */ 57 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 1024 */ 58 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2048 */ 59 INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 1970; /* 4018 */ 60 COMMIT; 61 PRAGMA cache_size = 10; 62 } 63 set x [wal_frame_count test.db-wal 1024] 64 if {[permutation]=="memsubsys1"} { 65 if {$x==4251 || $x==4290} {set x 4056} 66 } 67 set x 68} 4056 69 70for {set i 1} {$i < 50} {incr i} { 71 72 do_test wal3-1.$i.1 { 73 set str [a_string 800] 74 execsql { UPDATE t1 SET x = $str WHERE rowid = $i } 75 lappend L [wal_frame_count test.db-wal 1024] 76 execsql { 77 BEGIN; 78 INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 100; 79 ROLLBACK; 80 PRAGMA integrity_check; 81 } 82 } {ok} 83 84 # Check that everything looks OK from the point of view of an 85 # external connection. 86 # 87 sqlite3 db2 test.db 88 do_test wal3-1.$i.2 { 89 execsql { SELECT count(*) FROM t1 } db2 90 } 4018 91 do_test wal3-1.$i.3 { 92 execsql { SELECT x FROM t1 WHERE rowid = $i } 93 } $str 94 do_test wal3-1.$i.4 { 95 execsql { PRAGMA integrity_check } db2 96 } {ok} 97 db2 close 98 99 # Check that the file-system in its current state can be recovered. 100 # 101 forcecopy test.db test2.db 102 forcecopy test.db-wal test2.db-wal 103 forcedelete test2.db-journal 104 sqlite3 db2 test2.db 105 do_test wal3-1.$i.5 { 106 execsql { SELECT count(*) FROM t1 } db2 107 } 4018 108 do_test wal3-1.$i.6 { 109 execsql { SELECT x FROM t1 WHERE rowid = $i } 110 } $str 111 do_test wal3-1.$i.7 { 112 execsql { PRAGMA integrity_check } db2 113 } {ok} 114 db2 close 115} 116 117proc byte_is_zero {file offset} { 118 if {[file size test.db] <= $offset} { return 1 } 119 expr { [hexio_read $file $offset 1] == "00" } 120} 121 122do_multiclient_test i { 123 124 set testname(1) multiproc 125 set testname(2) singleproc 126 set tn $testname($i) 127 128 do_test wal3-2.$tn.1 { 129 sql1 { 130 PRAGMA page_size = 1024; 131 PRAGMA journal_mode = WAL; 132 } 133 sql1 { 134 CREATE TABLE t1(a, b); 135 INSERT INTO t1 VALUES(1, 'one'); 136 BEGIN; 137 SELECT * FROM t1; 138 } 139 } {1 one} 140 do_test wal3-2.$tn.2 { 141 sql2 { 142 CREATE TABLE t2(a, b); 143 INSERT INTO t2 VALUES(2, 'two'); 144 BEGIN; 145 SELECT * FROM t2; 146 } 147 } {2 two} 148 do_test wal3-2.$tn.3 { 149 sql3 { 150 CREATE TABLE t3(a, b); 151 INSERT INTO t3 VALUES(3, 'three'); 152 BEGIN; 153 SELECT * FROM t3; 154 } 155 } {3 three} 156 157 # Try to checkpoint the database using [db]. It should be possible to 158 # checkpoint everything except the table added by [db3] (checkpointing 159 # these frames would clobber the snapshot currently being used by [db2]). 160 # 161 # After [db2] has committed, a checkpoint can copy the entire log to the 162 # database file. Checkpointing after [db3] has committed is therefore a 163 # no-op, as the entire log has already been backfilled. 164 # 165 do_test wal3-2.$tn.4 { 166 sql1 { 167 COMMIT; 168 PRAGMA wal_checkpoint; 169 } 170 byte_is_zero test.db [expr $AUTOVACUUM ? 4*1024 : 3*1024] 171 } {1} 172 do_test wal3-2.$tn.5 { 173 sql2 { 174 COMMIT; 175 PRAGMA wal_checkpoint; 176 } 177 list [byte_is_zero test.db [expr $AUTOVACUUM ? 4*1024 : 3*1024]] \ 178 [byte_is_zero test.db [expr $AUTOVACUUM ? 5*1024 : 4*1024]] 179 } {0 1} 180 do_test wal3-2.$tn.6 { 181 sql3 { 182 COMMIT; 183 PRAGMA wal_checkpoint; 184 } 185 list [byte_is_zero test.db [expr $AUTOVACUUM ? 4*1024 : 3*1024]] \ 186 [byte_is_zero test.db [expr $AUTOVACUUM ? 5*1024 : 4*1024]] 187 } {0 1} 188} 189catch {db close} 190 191#------------------------------------------------------------------------- 192# Test that that for the simple test: 193# 194# CREATE TABLE x(y); 195# INSERT INTO x VALUES('z'); 196# PRAGMA wal_checkpoint; 197# 198# in WAL mode the xSync method is invoked as expected for each of 199# synchronous=off, synchronous=normal and synchronous=full. 200# 201foreach {tn syncmode synccount} { 202 1 off 203 {} 204 2 normal 205 {test.db-wal normal test.db normal} 206 3 full 207 {test.db-wal normal test.db-wal normal test.db-wal normal test.db normal} 208} { 209 210 proc sync_counter {args} { 211 foreach {method filename id flags} $args break 212 lappend ::syncs [file tail $filename] $flags 213 } 214 do_test wal3-3.$tn { 215 forcedelete test.db test.db-wal test.db-journal 216 217 testvfs T 218 T filter {} 219 T script sync_counter 220 sqlite3 db test.db -vfs T 221 222 execsql "PRAGMA synchronous = $syncmode" 223 execsql { PRAGMA journal_mode = WAL } 224 execsql { CREATE TABLE filler(a,b,c); } 225 226 set ::syncs [list] 227 T filter xSync 228 execsql { 229 CREATE TABLE x(y); 230 INSERT INTO x VALUES('z'); 231 PRAGMA wal_checkpoint; 232 } 233 T filter {} 234 set ::syncs 235 } $synccount 236 237 db close 238 T delete 239} 240 241 242#------------------------------------------------------------------------- 243# Only one client may run recovery at a time. Test this mechanism. 244# 245# When client-2 tries to open a read transaction while client-1 is 246# running recovery, it fails to obtain a lock on an aReadMark[] slot 247# (because they are all locked by recovery). It then tries to obtain 248# a shared lock on the RECOVER lock to see if there really is a 249# recovery running or not. 250# 251# This block of tests checks the effect of an SQLITE_BUSY or SQLITE_IOERR 252# being returned when client-2 attempts a shared lock on the RECOVER byte. 253# 254# An SQLITE_BUSY should be converted to an SQLITE_BUSY_RECOVERY. An 255# SQLITE_IOERR should be returned to the caller. 256# 257do_test wal3-5.1 { 258 faultsim_delete_and_reopen 259 execsql { 260 PRAGMA journal_mode = WAL; 261 CREATE TABLE t1(a, b); 262 INSERT INTO t1 VALUES(1, 2); 263 INSERT INTO t1 VALUES(3, 4); 264 } 265 faultsim_save_and_close 266} {} 267 268testvfs T -default 1 269T script method_callback 270 271proc method_callback {method args} { 272 if {$method == "xShmBarrier"} { 273 incr ::barrier_count 274 if {$::barrier_count == 2} { 275 # This code is executed within the xShmBarrier() callback invoked 276 # by the client running recovery as part of writing the recovered 277 # wal-index header. If a second client attempts to access the 278 # database now, it reads a corrupt (partially written) wal-index 279 # header. But it cannot even get that far, as the first client 280 # is still holding all the locks (recovery takes an exclusive lock 281 # on *all* db locks, preventing access by any other client). 282 # 283 # If global variable ::wal3_do_lockfailure is non-zero, then set 284 # things up so that an IO error occurs within an xShmLock() callback 285 # made by the second client (aka [db2]). 286 # 287 sqlite3 db2 test.db 288 if { $::wal3_do_lockfailure } { T filter xShmLock } 289 set ::testrc [ catch { db2 eval "SELECT * FROM t1" } ::testmsg ] 290 T filter {} 291 db2 close 292 } 293 } 294 295 if {$method == "xShmLock"} { 296 foreach {file handle spec} $args break 297 if { $spec == "2 1 lock shared" } { 298 return SQLITE_IOERR 299 } 300 } 301 302 return SQLITE_OK 303} 304 305# Test a normal SQLITE_BUSY return. 306# 307T filter xShmBarrier 308set testrc "" 309set testmsg "" 310set barrier_count 0 311set wal3_do_lockfailure 0 312do_test wal3-5.2 { 313 faultsim_restore_and_reopen 314 execsql { SELECT * FROM t1 } 315} {1 2 3 4} 316do_test wal3-5.3 { 317 list $::testrc $::testmsg 318} {1 {database is locked}} 319db close 320 321# Test an SQLITE_IOERR return. 322# 323T filter xShmBarrier 324set barrier_count 0 325set wal3_do_lockfailure 1 326set testrc "" 327set testmsg "" 328do_test wal3-5.4 { 329 faultsim_restore_and_reopen 330 execsql { SELECT * FROM t1 } 331} {1 2 3 4} 332do_test wal3-5.5 { 333 list $::testrc $::testmsg 334} {1 {disk I/O error}} 335 336db close 337T delete 338 339#------------------------------------------------------------------------- 340# When opening a read-transaction on a database, if the entire log has 341# already been copied to the database file, the reader grabs a special 342# kind of read lock (on aReadMark[0]). This set of test cases tests the 343# outcome of the following: 344# 345# + The reader discovering that between the time when it determined 346# that the log had been completely backfilled and the lock is obtained 347# that a writer has written to the log. In this case the reader should 348# acquire a different read-lock (not aReadMark[0]) and read the new 349# snapshot. 350# 351# + The attempt to obtain the lock on aReadMark[0] fails with SQLITE_BUSY. 352# This can happen if a checkpoint is ongoing. In this case also simply 353# obtain a different read-lock. 354# 355catch {db close} 356testvfs T -default 1 357do_test wal3-6.1.1 { 358 forcedelete test.db test.db-journal test.db wal 359 sqlite3 db test.db 360 execsql { PRAGMA auto_vacuum = off } 361 execsql { PRAGMA journal_mode = WAL } 362 execsql { 363 CREATE TABLE t1(a, b); 364 INSERT INTO t1 VALUES('o', 't'); 365 INSERT INTO t1 VALUES('t', 'f'); 366 } 367} {} 368do_test wal3-6.1.2 { 369 sqlite3 db2 test.db 370 sqlite3 db3 test.db 371 execsql { BEGIN ; SELECT * FROM t1 } db3 372} {o t t f} 373do_test wal3-6.1.3 { 374 execsql { PRAGMA wal_checkpoint } db2 375} {0 4 4} 376 377# At this point the log file has been fully checkpointed. However, 378# connection [db3] holds a lock that prevents the log from being wrapped. 379# Test case 3.6.1.4 has [db] attempt a read-lock on aReadMark[0]. But 380# as it is obtaining the lock, [db2] appends to the log file. 381# 382T filter xShmLock 383T script lock_callback 384proc lock_callback {method file handle spec} { 385 if {$spec == "3 1 lock shared"} { 386 # This is the callback for [db] to obtain the read lock on aReadMark[0]. 387 # Disable future callbacks using [T filter {}] and write to the log 388 # file using [db2]. [db3] is preventing [db2] from wrapping the log 389 # here, so this is an append. 390 T filter {} 391 db2 eval { INSERT INTO t1 VALUES('f', 's') } 392 } 393 return SQLITE_OK 394} 395do_test wal3-6.1.4 { 396 execsql { 397 BEGIN; 398 SELECT * FROM t1; 399 } 400} {o t t f f s} 401 402# [db] should be left holding a read-lock on some slot other than 403# aReadMark[0]. Test this by demonstrating that the read-lock is preventing 404# the log from being wrapped. 405# 406do_test wal3-6.1.5 { 407 db3 eval COMMIT 408 db2 eval { PRAGMA wal_checkpoint } 409 set sz1 [file size test.db-wal] 410 db2 eval { INSERT INTO t1 VALUES('s', 'e') } 411 set sz2 [file size test.db-wal] 412 expr {$sz2>$sz1} 413} {1} 414 415# Test that if [db2] had not interfered when [db] was trying to grab 416# aReadMark[0], it would have been possible to wrap the log in 3.6.1.5. 417# 418do_test wal3-6.1.6 { 419 execsql { COMMIT } 420 execsql { PRAGMA wal_checkpoint } db2 421 execsql { 422 BEGIN; 423 SELECT * FROM t1; 424 } 425} {o t t f f s s e} 426do_test wal3-6.1.7 { 427 db2 eval { PRAGMA wal_checkpoint } 428 set sz1 [file size test.db-wal] 429 db2 eval { INSERT INTO t1 VALUES('n', 't') } 430 set sz2 [file size test.db-wal] 431 expr {$sz2==$sz1} 432} {1} 433 434db3 close 435db2 close 436db close 437 438do_test wal3-6.2.1 { 439 forcedelete test.db test.db-journal test.db wal 440 sqlite3 db test.db 441 sqlite3 db2 test.db 442 execsql { PRAGMA auto_vacuum = off } 443 execsql { PRAGMA journal_mode = WAL } 444 execsql { 445 CREATE TABLE t1(a, b); 446 INSERT INTO t1 VALUES('h', 'h'); 447 INSERT INTO t1 VALUES('l', 'b'); 448 } 449} {} 450 451T filter xShmLock 452T script lock_callback 453proc lock_callback {method file handle spec} { 454 if {$spec == "3 1 unlock exclusive"} { 455 T filter {} 456 set ::R [db2 eval { 457 BEGIN; 458 SELECT * FROM t1; 459 }] 460 } 461} 462do_test wal3-6.2.2 { 463 execsql { PRAGMA wal_checkpoint } 464} {0 4 4} 465do_test wal3-6.2.3 { 466 set ::R 467} {h h l b} 468do_test wal3-6.2.4 { 469 set sz1 [file size test.db-wal] 470 execsql { INSERT INTO t1 VALUES('b', 'c'); } 471 set sz2 [file size test.db-wal] 472 expr {$sz2 > $sz1} 473} {1} 474do_test wal3-6.2.5 { 475 db2 eval { COMMIT } 476 execsql { PRAGMA wal_checkpoint } 477 set sz1 [file size test.db-wal] 478 execsql { INSERT INTO t1 VALUES('n', 'o'); } 479 set sz2 [file size test.db-wal] 480 expr {$sz2 == $sz1} 481} {1} 482 483db2 close 484db close 485T delete 486 487#------------------------------------------------------------------------- 488# When opening a read-transaction on a database, if the entire log has 489# not yet been copied to the database file, the reader grabs a read 490# lock on aReadMark[x], where x>0. The following test cases experiment 491# with the outcome of the following: 492# 493# + The reader discovering that between the time when it read the 494# wal-index header and the lock was obtained that a writer has 495# written to the log. In this case the reader should re-read the 496# wal-index header and lock a snapshot corresponding to the new 497# header. 498# 499# + The value in the aReadMark[x] slot has been modified since it was 500# read. 501# 502catch {db close} 503testvfs T -default 1 504do_test wal3-7.1.1 { 505 forcedelete test.db test.db-journal test.db wal 506 sqlite3 db test.db 507 execsql { 508 PRAGMA journal_mode = WAL; 509 CREATE TABLE blue(red PRIMARY KEY, green); 510 } 511} {wal} 512 513T script method_callback 514T filter xOpen 515proc method_callback {method args} { 516 if {$method == "xOpen"} { return "reader" } 517} 518do_test wal3-7.1.2 { 519 sqlite3 db2 test.db 520 execsql { SELECT * FROM blue } db2 521} {} 522 523T filter xShmLock 524set ::locks [list] 525proc method_callback {method file handle spec} { 526 if {$handle != "reader" } { return } 527 if {$method == "xShmLock"} { 528 catch { execsql { INSERT INTO blue VALUES(1, 2) } } 529 catch { execsql { INSERT INTO blue VALUES(3, 4) } } 530 } 531 lappend ::locks $spec 532} 533do_test wal3-7.1.3 { 534 execsql { SELECT * FROM blue } db2 535} {1 2 3 4} 536do_test wal3-7.1.4 { 537 set ::locks 538} {{4 1 lock shared} {4 1 unlock shared} {5 1 lock shared} {5 1 unlock shared}} 539 540set ::locks [list] 541proc method_callback {method file handle spec} { 542 if {$handle != "reader" } { return } 543 if {$method == "xShmLock"} { 544 catch { execsql { INSERT INTO blue VALUES(5, 6) } } 545 } 546 lappend ::locks $spec 547} 548do_test wal3-7.2.1 { 549 execsql { SELECT * FROM blue } db2 550} {1 2 3 4 5 6} 551do_test wal3-7.2.2 { 552 set ::locks 553} {{5 1 lock shared} {5 1 unlock shared} {4 1 lock shared} {4 1 unlock shared}} 554 555db close 556db2 close 557T delete 558 559 560#------------------------------------------------------------------------- 561# When a connection opens a read-lock on the database, it searches for 562# an aReadMark[] slot that is already set to the mxFrame value for the 563# new transaction. If it cannot find one, it attempts to obtain an 564# exclusive lock on an aReadMark[] slot for the purposes of modifying 565# the value, then drops back to a shared-lock for the duration of the 566# transaction. 567# 568# This test case verifies that if an exclusive lock cannot be obtained 569# on any aReadMark[] slot (because there are already several readers), 570# the client takes a shared-lock on a slot without modifying the value 571# and continues. 572# 573set nConn 50 574if { [string match *BSD $tcl_platform(os)] } { set nConn 25 } 575do_test wal3-9.0 { 576 forcedelete test.db test.db-journal test.db wal 577 sqlite3 db test.db 578 execsql { 579 PRAGMA page_size = 1024; 580 PRAGMA journal_mode = WAL; 581 CREATE TABLE whoami(x); 582 INSERT INTO whoami VALUES('nobody'); 583 } 584} {wal} 585for {set i 0} {$i < $nConn} {incr i} { 586 set c db$i 587 do_test wal3-9.1.$i { 588 sqlite3 $c test.db 589 execsql { UPDATE whoami SET x = $c } 590 execsql { 591 BEGIN; 592 SELECT * FROM whoami 593 } $c 594 } $c 595} 596for {set i 0} {$i < $nConn} {incr i} { 597 set c db$i 598 do_test wal3-9.2.$i { 599 execsql { SELECT * FROM whoami } $c 600 } $c 601} 602 603set sz [expr 1024 * (2+$AUTOVACUUM)] 604do_test wal3-9.3 { 605 for {set i 0} {$i < ($nConn-1)} {incr i} { db$i close } 606 execsql { PRAGMA wal_checkpoint } 607 byte_is_zero test.db [expr $sz-1024] 608} {1} 609do_test wal3-9.4 { 610 db[expr $nConn-1] close 611 execsql { PRAGMA wal_checkpoint } 612 set sz2 [file size test.db] 613 byte_is_zero test.db [expr $sz-1024] 614} {0} 615 616do_multiclient_test tn { 617 do_test wal3-10.$tn.1 { 618 sql1 { 619 PRAGMA page_size = 1024; 620 CREATE TABLE t1(x); 621 PRAGMA journal_mode = WAL; 622 PRAGMA wal_autocheckpoint = 100000; 623 BEGIN; 624 INSERT INTO t1 VALUES(randomblob(800)); 625 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 2 626 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 4 627 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 8 628 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 16 629 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 32 630 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 64 631 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 128 632 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 256 633 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 512 634 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 1024 635 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 2048 636 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 4096 637 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 8192 638 COMMIT; 639 CREATE INDEX i1 ON t1(x); 640 } 641 642 expr {[file size test.db-wal] > [expr 1032*9000]} 643 } 1 644 645 do_test wal3-10.$tn.2 { 646 sql2 {PRAGMA integrity_check} 647 } {ok} 648} 649 650finish_test 651