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 checkpoint_fullfsync = 0" 224 execsql { PRAGMA journal_mode = WAL } 225 execsql { CREATE TABLE filler(a,b,c); } 226 227 set ::syncs [list] 228 T filter xSync 229 execsql { 230 CREATE TABLE x(y); 231 INSERT INTO x VALUES('z'); 232 PRAGMA wal_checkpoint; 233 } 234 T filter {} 235 set ::syncs 236 } $synccount 237 238 db close 239 T delete 240} 241 242 243#------------------------------------------------------------------------- 244# Only one client may run recovery at a time. Test this mechanism. 245# 246# When client-2 tries to open a read transaction while client-1 is 247# running recovery, it fails to obtain a lock on an aReadMark[] slot 248# (because they are all locked by recovery). It then tries to obtain 249# a shared lock on the RECOVER lock to see if there really is a 250# recovery running or not. 251# 252# This block of tests checks the effect of an SQLITE_BUSY or SQLITE_IOERR 253# being returned when client-2 attempts a shared lock on the RECOVER byte. 254# 255# An SQLITE_BUSY should be converted to an SQLITE_BUSY_RECOVERY. An 256# SQLITE_IOERR should be returned to the caller. 257# 258do_test wal3-5.1 { 259 faultsim_delete_and_reopen 260 execsql { 261 PRAGMA journal_mode = WAL; 262 CREATE TABLE t1(a, b); 263 INSERT INTO t1 VALUES(1, 2); 264 INSERT INTO t1 VALUES(3, 4); 265 } 266 faultsim_save_and_close 267} {} 268 269testvfs T -default 1 270T script method_callback 271 272proc method_callback {method args} { 273 if {$method == "xShmBarrier"} { 274 incr ::barrier_count 275 if {$::barrier_count == 2} { 276 # This code is executed within the xShmBarrier() callback invoked 277 # by the client running recovery as part of writing the recovered 278 # wal-index header. If a second client attempts to access the 279 # database now, it reads a corrupt (partially written) wal-index 280 # header. But it cannot even get that far, as the first client 281 # is still holding all the locks (recovery takes an exclusive lock 282 # on *all* db locks, preventing access by any other client). 283 # 284 # If global variable ::wal3_do_lockfailure is non-zero, then set 285 # things up so that an IO error occurs within an xShmLock() callback 286 # made by the second client (aka [db2]). 287 # 288 sqlite3 db2 test.db 289 if { $::wal3_do_lockfailure } { T filter xShmLock } 290 set ::testrc [ catch { db2 eval "SELECT * FROM t1" } ::testmsg ] 291 T filter {} 292 db2 close 293 } 294 } 295 296 if {$method == "xShmLock"} { 297 foreach {file handle spec} $args break 298 if { $spec == "2 1 lock shared" } { 299 return SQLITE_IOERR 300 } 301 } 302 303 return SQLITE_OK 304} 305 306# Test a normal SQLITE_BUSY return. 307# 308T filter xShmBarrier 309set testrc "" 310set testmsg "" 311set barrier_count 0 312set wal3_do_lockfailure 0 313do_test wal3-5.2 { 314 faultsim_restore_and_reopen 315 execsql { SELECT * FROM t1 } 316} {1 2 3 4} 317do_test wal3-5.3 { 318 list $::testrc $::testmsg 319} {1 {database is locked}} 320db close 321 322# Test an SQLITE_IOERR return. 323# 324T filter xShmBarrier 325set barrier_count 0 326set wal3_do_lockfailure 1 327set testrc "" 328set testmsg "" 329do_test wal3-5.4 { 330 faultsim_restore_and_reopen 331 execsql { SELECT * FROM t1 } 332} {1 2 3 4} 333do_test wal3-5.5 { 334 list $::testrc $::testmsg 335} {1 {disk I/O error}} 336 337db close 338T delete 339 340#------------------------------------------------------------------------- 341# When opening a read-transaction on a database, if the entire log has 342# already been copied to the database file, the reader grabs a special 343# kind of read lock (on aReadMark[0]). This set of test cases tests the 344# outcome of the following: 345# 346# + The reader discovering that between the time when it determined 347# that the log had been completely backfilled and the lock is obtained 348# that a writer has written to the log. In this case the reader should 349# acquire a different read-lock (not aReadMark[0]) and read the new 350# snapshot. 351# 352# + The attempt to obtain the lock on aReadMark[0] fails with SQLITE_BUSY. 353# This can happen if a checkpoint is ongoing. In this case also simply 354# obtain a different read-lock. 355# 356catch {db close} 357testvfs T -default 1 358do_test wal3-6.1.1 { 359 forcedelete test.db test.db-journal test.db wal 360 sqlite3 db test.db 361 execsql { PRAGMA auto_vacuum = off } 362 execsql { PRAGMA journal_mode = WAL } 363 execsql { 364 CREATE TABLE t1(a, b); 365 INSERT INTO t1 VALUES('o', 't'); 366 INSERT INTO t1 VALUES('t', 'f'); 367 } 368} {} 369do_test wal3-6.1.2 { 370 sqlite3 db2 test.db 371 sqlite3 db3 test.db 372 execsql { BEGIN ; SELECT * FROM t1 } db3 373} {o t t f} 374do_test wal3-6.1.3 { 375 execsql { PRAGMA wal_checkpoint } db2 376} {0 4 4} 377 378# At this point the log file has been fully checkpointed. However, 379# connection [db3] holds a lock that prevents the log from being wrapped. 380# Test case 3.6.1.4 has [db] attempt a read-lock on aReadMark[0]. But 381# as it is obtaining the lock, [db2] appends to the log file. 382# 383T filter xShmLock 384T script lock_callback 385proc lock_callback {method file handle spec} { 386 if {$spec == "3 1 lock shared"} { 387 # This is the callback for [db] to obtain the read lock on aReadMark[0]. 388 # Disable future callbacks using [T filter {}] and write to the log 389 # file using [db2]. [db3] is preventing [db2] from wrapping the log 390 # here, so this is an append. 391 T filter {} 392 db2 eval { INSERT INTO t1 VALUES('f', 's') } 393 } 394 return SQLITE_OK 395} 396do_test wal3-6.1.4 { 397 execsql { 398 BEGIN; 399 SELECT * FROM t1; 400 } 401} {o t t f f s} 402 403# [db] should be left holding a read-lock on some slot other than 404# aReadMark[0]. Test this by demonstrating that the read-lock is preventing 405# the log from being wrapped. 406# 407do_test wal3-6.1.5 { 408 db3 eval COMMIT 409 db2 eval { PRAGMA wal_checkpoint } 410 set sz1 [file size test.db-wal] 411 db2 eval { INSERT INTO t1 VALUES('s', 'e') } 412 set sz2 [file size test.db-wal] 413 expr {$sz2>$sz1} 414} {1} 415 416# Test that if [db2] had not interfered when [db] was trying to grab 417# aReadMark[0], it would have been possible to wrap the log in 3.6.1.5. 418# 419do_test wal3-6.1.6 { 420 execsql { COMMIT } 421 execsql { PRAGMA wal_checkpoint } db2 422 execsql { 423 BEGIN; 424 SELECT * FROM t1; 425 } 426} {o t t f f s s e} 427do_test wal3-6.1.7 { 428 db2 eval { PRAGMA wal_checkpoint } 429 set sz1 [file size test.db-wal] 430 db2 eval { INSERT INTO t1 VALUES('n', 't') } 431 set sz2 [file size test.db-wal] 432 expr {$sz2==$sz1} 433} {1} 434 435db3 close 436db2 close 437db close 438 439do_test wal3-6.2.1 { 440 forcedelete test.db test.db-journal test.db wal 441 sqlite3 db test.db 442 sqlite3 db2 test.db 443 execsql { PRAGMA auto_vacuum = off } 444 execsql { PRAGMA journal_mode = WAL } 445 execsql { 446 CREATE TABLE t1(a, b); 447 INSERT INTO t1 VALUES('h', 'h'); 448 INSERT INTO t1 VALUES('l', 'b'); 449 } 450} {} 451 452T filter xShmLock 453T script lock_callback 454proc lock_callback {method file handle spec} { 455 if {$spec == "3 1 unlock exclusive"} { 456 T filter {} 457 set ::R [db2 eval { 458 BEGIN; 459 SELECT * FROM t1; 460 }] 461 } 462} 463do_test wal3-6.2.2 { 464 execsql { PRAGMA wal_checkpoint } 465} {0 4 4} 466do_test wal3-6.2.3 { 467 set ::R 468} {h h l b} 469do_test wal3-6.2.4 { 470 set sz1 [file size test.db-wal] 471 execsql { INSERT INTO t1 VALUES('b', 'c'); } 472 set sz2 [file size test.db-wal] 473 expr {$sz2 > $sz1} 474} {1} 475do_test wal3-6.2.5 { 476 db2 eval { COMMIT } 477 execsql { PRAGMA wal_checkpoint } 478 set sz1 [file size test.db-wal] 479 execsql { INSERT INTO t1 VALUES('n', 'o'); } 480 set sz2 [file size test.db-wal] 481 expr {$sz2 == $sz1} 482} {1} 483 484db2 close 485db close 486T delete 487 488#------------------------------------------------------------------------- 489# When opening a read-transaction on a database, if the entire log has 490# not yet been copied to the database file, the reader grabs a read 491# lock on aReadMark[x], where x>0. The following test cases experiment 492# with the outcome of the following: 493# 494# + The reader discovering that between the time when it read the 495# wal-index header and the lock was obtained that a writer has 496# written to the log. In this case the reader should re-read the 497# wal-index header and lock a snapshot corresponding to the new 498# header. 499# 500# + The value in the aReadMark[x] slot has been modified since it was 501# read. 502# 503catch {db close} 504testvfs T -default 1 505do_test wal3-7.1.1 { 506 forcedelete test.db test.db-journal test.db wal 507 sqlite3 db test.db 508 execsql { 509 PRAGMA journal_mode = WAL; 510 CREATE TABLE blue(red PRIMARY KEY, green); 511 } 512} {wal} 513 514T script method_callback 515T filter xOpen 516proc method_callback {method args} { 517 if {$method == "xOpen"} { return "reader" } 518} 519do_test wal3-7.1.2 { 520 sqlite3 db2 test.db 521 execsql { SELECT * FROM blue } db2 522} {} 523 524T filter xShmLock 525set ::locks [list] 526proc method_callback {method file handle spec} { 527 if {$handle != "reader" } { return } 528 if {$method == "xShmLock"} { 529 catch { execsql { INSERT INTO blue VALUES(1, 2) } } 530 catch { execsql { INSERT INTO blue VALUES(3, 4) } } 531 } 532 lappend ::locks $spec 533} 534do_test wal3-7.1.3 { 535 execsql { SELECT * FROM blue } db2 536} {1 2 3 4} 537do_test wal3-7.1.4 { 538 set ::locks 539} {{4 1 lock shared} {4 1 unlock shared} {5 1 lock shared} {5 1 unlock shared}} 540 541set ::locks [list] 542proc method_callback {method file handle spec} { 543 if {$handle != "reader" } { return } 544 if {$method == "xShmLock"} { 545 catch { execsql { INSERT INTO blue VALUES(5, 6) } } 546 } 547 lappend ::locks $spec 548} 549do_test wal3-7.2.1 { 550 execsql { SELECT * FROM blue } db2 551} {1 2 3 4 5 6} 552do_test wal3-7.2.2 { 553 set ::locks 554} {{5 1 lock shared} {5 1 unlock shared} {4 1 lock shared} {4 1 unlock shared}} 555 556db close 557db2 close 558T delete 559 560 561#------------------------------------------------------------------------- 562# When a connection opens a read-lock on the database, it searches for 563# an aReadMark[] slot that is already set to the mxFrame value for the 564# new transaction. If it cannot find one, it attempts to obtain an 565# exclusive lock on an aReadMark[] slot for the purposes of modifying 566# the value, then drops back to a shared-lock for the duration of the 567# transaction. 568# 569# This test case verifies that if an exclusive lock cannot be obtained 570# on any aReadMark[] slot (because there are already several readers), 571# the client takes a shared-lock on a slot without modifying the value 572# and continues. 573# 574set nConn 50 575if { [string match *BSD $tcl_platform(os)] } { set nConn 25 } 576do_test wal3-9.0 { 577 forcedelete test.db test.db-journal test.db wal 578 sqlite3 db test.db 579 execsql { 580 PRAGMA page_size = 1024; 581 PRAGMA journal_mode = WAL; 582 CREATE TABLE whoami(x); 583 INSERT INTO whoami VALUES('nobody'); 584 } 585} {wal} 586for {set i 0} {$i < $nConn} {incr i} { 587 set c db$i 588 do_test wal3-9.1.$i { 589 sqlite3 $c test.db 590 execsql { UPDATE whoami SET x = $c } 591 execsql { 592 BEGIN; 593 SELECT * FROM whoami 594 } $c 595 } $c 596} 597for {set i 0} {$i < $nConn} {incr i} { 598 set c db$i 599 do_test wal3-9.2.$i { 600 execsql { SELECT * FROM whoami } $c 601 } $c 602} 603 604set sz [expr 1024 * (2+$AUTOVACUUM)] 605do_test wal3-9.3 { 606 for {set i 0} {$i < ($nConn-1)} {incr i} { db$i close } 607 execsql { PRAGMA wal_checkpoint } 608 byte_is_zero test.db [expr $sz-1024] 609} {1} 610do_test wal3-9.4 { 611 db[expr $nConn-1] close 612 execsql { PRAGMA wal_checkpoint } 613 set sz2 [file size test.db] 614 byte_is_zero test.db [expr $sz-1024] 615} {0} 616 617do_multiclient_test tn { 618 do_test wal3-10.$tn.1 { 619 sql1 { 620 PRAGMA page_size = 1024; 621 CREATE TABLE t1(x); 622 PRAGMA journal_mode = WAL; 623 PRAGMA wal_autocheckpoint = 100000; 624 BEGIN; 625 INSERT INTO t1 VALUES(randomblob(800)); 626 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 2 627 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 4 628 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 8 629 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 16 630 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 32 631 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 64 632 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 128 633 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 256 634 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 512 635 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 1024 636 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 2048 637 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 4096 638 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 8192 639 COMMIT; 640 CREATE INDEX i1 ON t1(x); 641 } 642 643 expr {[file size test.db-wal] > [expr 1032*9000]} 644 } 1 645 646 do_test wal3-10.$tn.2 { 647 sql2 {PRAGMA integrity_check} 648 } {ok} 649} 650 651finish_test 652