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