| 1 |
# 2010 May 24 |
| 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 |
# |
| 12 |
|
| 13 |
set testdir [file dirname $argv0] |
| 14 |
source $testdir/tester.tcl |
| 15 |
source $testdir/lock_common.tcl |
| 16 |
source $testdir/wal_common.tcl |
| 17 |
|
| 18 |
ifcapable !wal {finish_test ; return } |
| 19 |
set testprefix walcksum |
| 20 |
|
| 21 |
# Read and return the contents of file $filename. Treat the content as |
| 22 |
# binary data. |
| 23 |
# |
| 24 |
proc readfile {filename} { |
| 25 |
set fd [open $filename] |
| 26 |
fconfigure $fd -translation binary |
| 27 |
set data [read $fd] |
| 28 |
close $fd |
| 29 |
return $data |
| 30 |
} |
| 31 |
|
| 32 |
# |
| 33 |
# File $filename must be a WAL file on disk. Check that the checksum of frame |
| 34 |
# $iFrame in the file is correct when interpreting data as $endian-endian |
| 35 |
# integers ($endian must be either "big" or "little"). If the checksum looks |
| 36 |
# correct, return 1. Otherwise 0. |
| 37 |
# |
| 38 |
proc log_checksum_verify {filename iFrame endian} { |
| 39 |
set data [readfile $filename] |
| 40 |
|
| 41 |
foreach {offset c1 c2} [log_checksum_calc $data $iFrame $endian] {} |
| 42 |
|
| 43 |
binary scan [string range $data $offset [expr $offset+7]] II expect1 expect2 |
| 44 |
set expect1 [expr $expect1&0xFFFFFFFF] |
| 45 |
set expect2 [expr $expect2&0xFFFFFFFF] |
| 46 |
|
| 47 |
expr {$c1==$expect1 && $c2==$expect2} |
| 48 |
} |
| 49 |
|
| 50 |
# File $filename must be a WAL file on disk. Compute the checksum for frame |
| 51 |
# $iFrame in the file by interpreting data as $endian-endian integers |
| 52 |
# ($endian must be either "big" or "little"). Then write the computed |
| 53 |
# checksum into the file. |
| 54 |
# |
| 55 |
proc log_checksum_write {filename iFrame endian} { |
| 56 |
set data [readfile $filename] |
| 57 |
|
| 58 |
foreach {offset c1 c2} [log_checksum_calc $data $iFrame $endian] {} |
| 59 |
|
| 60 |
set bin [binary format II $c1 $c2] |
| 61 |
set fd [open $filename r+] |
| 62 |
fconfigure $fd -translation binary |
| 63 |
seek $fd $offset |
| 64 |
puts -nonewline $fd $bin |
| 65 |
close $fd |
| 66 |
} |
| 67 |
|
| 68 |
# Calculate and return the checksum for a particular frame in a WAL. |
| 69 |
# |
| 70 |
# Arguments are: |
| 71 |
# |
| 72 |
# $data Blob containing the entire contents of a WAL. |
| 73 |
# |
| 74 |
# $iFrame Frame number within the $data WAL. Frames are numbered |
| 75 |
# starting at 1. |
| 76 |
# |
| 77 |
# $endian One of "big" or "little". |
| 78 |
# |
| 79 |
# Returns a list of three elements, as follows: |
| 80 |
# |
| 81 |
# * The byte offset of the checksum belonging to frame $iFrame in the WAL. |
| 82 |
# * The first integer in the calculated version of the checksum. |
| 83 |
# * The second integer in the calculated version of the checksum. |
| 84 |
# |
| 85 |
proc log_checksum_calc {data iFrame endian} { |
| 86 |
|
| 87 |
binary scan [string range $data 8 11] I pgsz |
| 88 |
if {$iFrame > 1} { |
| 89 |
set n [wal_file_size [expr $iFrame-2] $pgsz] |
| 90 |
binary scan [string range $data [expr $n+16] [expr $n+23]] II c1 c2 |
| 91 |
} else { |
| 92 |
set c1 0 |
| 93 |
set c2 0 |
| 94 |
wal_cksum $endian c1 c2 [string range $data 0 23] |
| 95 |
} |
| 96 |
|
| 97 |
set n [wal_file_size [expr $iFrame-1] $pgsz] |
| 98 |
wal_cksum $endian c1 c2 [string range $data $n [expr $n+7]] |
| 99 |
wal_cksum $endian c1 c2 [string range $data [expr $n+24] [expr $n+24+$pgsz-1]] |
| 100 |
|
| 101 |
list [expr $n+16] $c1 $c2 |
| 102 |
} |
| 103 |
|
| 104 |
# |
| 105 |
# File $filename must be a WAL file on disk. Set the 'magic' field of the |
| 106 |
# WAL header to indicate that checksums are $endian-endian ($endian must be |
| 107 |
# either "big" or "little"). |
| 108 |
# |
| 109 |
# Also update the wal header checksum (since the wal header contents may |
| 110 |
# have changed). |
| 111 |
# |
| 112 |
proc log_checksum_writemagic {filename endian} { |
| 113 |
set val [expr {0x377f0682 | ($endian == "big" ? 1 : 0)}] |
| 114 |
set bin [binary format I $val] |
| 115 |
set fd [open $filename r+] |
| 116 |
fconfigure $fd -translation binary |
| 117 |
puts -nonewline $fd $bin |
| 118 |
|
| 119 |
seek $fd 0 |
| 120 |
set blob [read $fd 24] |
| 121 |
set c1 0 |
| 122 |
set c2 0 |
| 123 |
wal_cksum $endian c1 c2 $blob |
| 124 |
seek $fd 24 |
| 125 |
puts -nonewline $fd [binary format II $c1 $c2] |
| 126 |
|
| 127 |
close $fd |
| 128 |
} |
| 129 |
|
| 130 |
#------------------------------------------------------------------------- |
| 131 |
# Test cases walcksum-1.* attempt to verify the following: |
| 132 |
# |
| 133 |
# * That both native and non-native order checksum log files can |
| 134 |
# be recovered. |
| 135 |
# |
| 136 |
# * That when appending to native or non-native checksum log files |
| 137 |
# SQLite continues to use the right kind of checksums. |
| 138 |
# |
| 139 |
# * Test point 2 when the appending process is not one that recovered |
| 140 |
# the log file. |
| 141 |
# |
| 142 |
# * Test that both native and non-native checksum log files can be |
| 143 |
# checkpointed. And that after doing so the next write to the log |
| 144 |
# file occurs using native byte-order checksums. |
| 145 |
# |
| 146 |
set native "big" |
| 147 |
if {$::tcl_platform(byteOrder) == "littleEndian"} { set native "little" } |
| 148 |
foreach endian {big little} { |
| 149 |
|
| 150 |
# Create a database. Leave some data in the log file. |
| 151 |
# |
| 152 |
do_test walcksum-1.$endian.1 { |
| 153 |
catch { db close } |
| 154 |
forcedelete test.db test.db-wal test.db-journal |
| 155 |
sqlite3 db test.db |
| 156 |
execsql { |
| 157 |
PRAGMA page_size = 1024; |
| 158 |
PRAGMA auto_vacuum = 0; |
| 159 |
PRAGMA synchronous = NORMAL; |
| 160 |
|
| 161 |
CREATE TABLE t1(a PRIMARY KEY, b); |
| 162 |
INSERT INTO t1 VALUES(1, 'one'); |
| 163 |
INSERT INTO t1 VALUES(2, 'two'); |
| 164 |
INSERT INTO t1 VALUES(3, 'three'); |
| 165 |
INSERT INTO t1 VALUES(5, 'five'); |
| 166 |
|
| 167 |
PRAGMA journal_mode = WAL; |
| 168 |
INSERT INTO t1 VALUES(8, 'eight'); |
| 169 |
INSERT INTO t1 VALUES(13, 'thirteen'); |
| 170 |
INSERT INTO t1 VALUES(21, 'twentyone'); |
| 171 |
} |
| 172 |
|
| 173 |
forcecopy test.db test2.db |
| 174 |
forcecopy test.db-wal test2.db-wal |
| 175 |
db close |
| 176 |
|
| 177 |
list [file size test2.db] [file size test2.db-wal] |
| 178 |
} [list [expr 1024*3] [wal_file_size 6 1024]] |
| 179 |
|
| 180 |
# Verify that the checksums are valid for all frames and that they |
| 181 |
# are calculated by interpreting data in native byte-order. |
| 182 |
# |
| 183 |
for {set f 1} {$f <= 6} {incr f} { |
| 184 |
do_test walcksum-1.$endian.2.$f { |
| 185 |
log_checksum_verify test2.db-wal $f $native |
| 186 |
} 1 |
| 187 |
} |
| 188 |
|
| 189 |
# Replace all checksums in the current WAL file with $endian versions. |
| 190 |
# Then check that it is still possible to recover and read the database. |
| 191 |
# |
| 192 |
log_checksum_writemagic test2.db-wal $endian |
| 193 |
for {set f 1} {$f <= 6} {incr f} { |
| 194 |
do_test walcksum-1.$endian.3.$f { |
| 195 |
log_checksum_write test2.db-wal $f $endian |
| 196 |
log_checksum_verify test2.db-wal $f $endian |
| 197 |
} {1} |
| 198 |
} |
| 199 |
do_test walcksum-1.$endian.4.1 { |
| 200 |
forcecopy test2.db test.db |
| 201 |
forcecopy test2.db-wal test.db-wal |
| 202 |
sqlite3 db test.db |
| 203 |
execsql { SELECT a FROM t1 } |
| 204 |
} {1 2 3 5 8 13 21} |
| 205 |
|
| 206 |
# Following recovery, any frames written to the log should use the same |
| 207 |
# endianness as the existing frames. Check that this is the case. |
| 208 |
# |
| 209 |
do_test walcksum-1.$endian.5.0 { |
| 210 |
execsql { |
| 211 |
PRAGMA synchronous = NORMAL; |
| 212 |
INSERT INTO t1 VALUES(34, 'thirtyfour'); |
| 213 |
} |
| 214 |
list [file size test.db] [file size test.db-wal] |
| 215 |
} [list [expr 1024*3] [wal_file_size 8 1024]] |
| 216 |
for {set f 1} {$f <= 8} {incr f} { |
| 217 |
do_test walcksum-1.$endian.5.$f { |
| 218 |
log_checksum_verify test.db-wal $f $endian |
| 219 |
} {1} |
| 220 |
} |
| 221 |
|
| 222 |
# Now connect a second connection to the database. Check that this one |
| 223 |
# (not the one that did recovery) also appends frames to the log using |
| 224 |
# the same endianness for checksums as the existing frames. |
| 225 |
# |
| 226 |
do_test walcksum-1.$endian.6 { |
| 227 |
sqlite3 db2 test.db |
| 228 |
execsql { |
| 229 |
PRAGMA integrity_check; |
| 230 |
SELECT a FROM t1; |
| 231 |
} db2 |
| 232 |
} {ok 1 2 3 5 8 13 21 34} |
| 233 |
do_test walcksum-1.$endian.7.0 { |
| 234 |
execsql { |
| 235 |
PRAGMA synchronous = NORMAL; |
| 236 |
INSERT INTO t1 VALUES(55, 'fiftyfive'); |
| 237 |
} db2 |
| 238 |
list [file size test.db] [file size test.db-wal] |
| 239 |
} [list [expr 1024*3] [wal_file_size 10 1024]] |
| 240 |
for {set f 1} {$f <= 10} {incr f} { |
| 241 |
do_test walcksum-1.$endian.7.$f { |
| 242 |
log_checksum_verify test.db-wal $f $endian |
| 243 |
} {1} |
| 244 |
} |
| 245 |
|
| 246 |
# Now that both the recoverer and non-recoverer have added frames to the |
| 247 |
# log file, check that it can still be recovered. |
| 248 |
# |
| 249 |
forcecopy test.db test2.db |
| 250 |
forcecopy test.db-wal test2.db-wal |
| 251 |
do_test walcksum-1.$endian.7.11 { |
| 252 |
sqlite3 db3 test2.db |
| 253 |
execsql { |
| 254 |
PRAGMA integrity_check; |
| 255 |
SELECT a FROM t1; |
| 256 |
} db3 |
| 257 |
} {ok 1 2 3 5 8 13 21 34 55} |
| 258 |
db3 close |
| 259 |
|
| 260 |
# Run a checkpoint on the database file. Then, check that any frames written |
| 261 |
# to the start of the log use native byte-order checksums. |
| 262 |
# |
| 263 |
do_test walcksum-1.$endian.8.1 { |
| 264 |
execsql { |
| 265 |
PRAGMA wal_checkpoint; |
| 266 |
INSERT INTO t1 VALUES(89, 'eightynine'); |
| 267 |
} |
| 268 |
log_checksum_verify test.db-wal 1 $native |
| 269 |
} {1} |
| 270 |
do_test walcksum-1.$endian.8.2 { |
| 271 |
log_checksum_verify test.db-wal 2 $native |
| 272 |
} {1} |
| 273 |
do_test walcksum-1.$endian.8.3 { |
| 274 |
log_checksum_verify test.db-wal 3 $native |
| 275 |
} {0} |
| 276 |
|
| 277 |
do_test walcksum-1.$endian.9 { |
| 278 |
execsql { |
| 279 |
PRAGMA integrity_check; |
| 280 |
SELECT a FROM t1; |
| 281 |
} db2 |
| 282 |
} {ok 1 2 3 5 8 13 21 34 55 89} |
| 283 |
|
| 284 |
catch { db close } |
| 285 |
catch { db2 close } |
| 286 |
} |
| 287 |
|
| 288 |
#------------------------------------------------------------------------- |
| 289 |
# Test case walcksum-2.* tests that if a statement transaction is rolled |
| 290 |
# back after frames are written to the WAL, and then (after writing some |
| 291 |
# more) the outer transaction is committed, the WAL file is still correctly |
| 292 |
# formatted (and can be recovered by a second process if required). |
| 293 |
# |
| 294 |
do_test walcksum-2.1 { |
| 295 |
forcedelete test.db test.db-wal test.db-journal |
| 296 |
sqlite3 db test.db |
| 297 |
execsql { |
| 298 |
PRAGMA synchronous = NORMAL; |
| 299 |
PRAGMA page_size = 1024; |
| 300 |
PRAGMA journal_mode = WAL; |
| 301 |
PRAGMA cache_size = 10; |
| 302 |
CREATE TABLE t1(x PRIMARY KEY); |
| 303 |
PRAGMA wal_checkpoint; |
| 304 |
INSERT INTO t1 VALUES(randomblob(800)); |
| 305 |
BEGIN; |
| 306 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 2 */ |
| 307 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 4 */ |
| 308 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 8 */ |
| 309 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 16 */ |
| 310 |
SAVEPOINT one; |
| 311 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 32 */ |
| 312 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 64 */ |
| 313 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 128 */ |
| 314 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 256 */ |
| 315 |
ROLLBACK TO one; |
| 316 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 32 */ |
| 317 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 64 */ |
| 318 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 128 */ |
| 319 |
INSERT INTO t1 SELECT randomblob(800) FROM t1; /* 256 */ |
| 320 |
COMMIT; |
| 321 |
} |
| 322 |
|
| 323 |
forcecopy test.db test2.db |
| 324 |
forcecopy test.db-wal test2.db-wal |
| 325 |
|
| 326 |
sqlite3 db2 test2.db |
| 327 |
execsql { |
| 328 |
PRAGMA integrity_check; |
| 329 |
SELECT count(*) FROM t1; |
| 330 |
} db2 |
| 331 |
} {ok 256} |
| 332 |
catch { db close } |
| 333 |
catch { db2 close } |
| 334 |
|
| 335 |
#------------------------------------------------------------------------- |
| 336 |
# Test cases based on the bug reported at: |
| 337 |
# |
| 338 |
# <https://sqlite.org/forum/forumpost/b490f726db> |
| 339 |
# |
| 340 |
reset_db |
| 341 |
|
| 342 |
do_execsql_test 3.0 { |
| 343 |
PRAGMA auto_vacuum = 0; |
| 344 |
PRAGMA synchronous = NORMAL; |
| 345 |
PRAGMA journal_mode = WAL; |
| 346 |
PRAGMA cache_size = 1; |
| 347 |
|
| 348 |
CREATE TABLE t1 (i INTEGER PRIMARY KEY, b BLOB, t TEXT); |
| 349 |
PRAGMA wal_checkpoint; |
| 350 |
INSERT INTO t1 VALUES(1, randomblob(2048), 'one'); |
| 351 |
} {wal 0 2 2} |
| 352 |
|
| 353 |
do_execsql_test 3.1 { |
| 354 |
BEGIN; |
| 355 |
INSERT INTO t1 VALUES(2, randomblob(2048), 'two'); |
| 356 |
SAVEPOINT one; |
| 357 |
INSERT INTO t1 VALUES(3, randomblob(2048), 'three'); |
| 358 |
INSERT INTO t1 VALUES(4, randomblob(2048), 'four'); |
| 359 |
INSERT INTO t1 VALUES(5, randomblob(2048), 'five'); |
| 360 |
INSERT INTO t1 VALUES(6, randomblob(2048), 'six'); |
| 361 |
INSERT INTO t1 VALUES(7, randomblob(2048), 'seven'); |
| 362 |
|
| 363 |
UPDATE t1 SET b=randomblob(2048) WHERE i=5; |
| 364 |
UPDATE t1 SET b=randomblob(2048) WHERE i=6; |
| 365 |
UPDATE t1 SET b=randomblob(2048) WHERE i=7; |
| 366 |
ROLLBACK TO one; |
| 367 |
INSERT INTO t1 VALUES(8, NULL, 'eight'); |
| 368 |
COMMIT; |
| 369 |
} {} |
| 370 |
|
| 371 |
do_execsql_test 3.2 { |
| 372 |
SELECT i, t FROM t1 |
| 373 |
} {1 one 2 two 8 eight} |
| 374 |
|
| 375 |
forcecopy test.db test2.db |
| 376 |
forcecopy test.db-wal test2.db-wal |
| 377 |
|
| 378 |
sqlite3 db2 test2.db |
| 379 |
do_test 1.3 { |
| 380 |
execsql { |
| 381 |
SELECT i, t FROM t1 |
| 382 |
} db2 |
| 383 |
} {1 one 2 two 8 eight} |
| 384 |
|
| 385 |
catch { db2 close } |
| 386 |
|
| 387 |
#------------------------------------------------------------------------- |
| 388 |
reset_db |
| 389 |
|
| 390 |
do_execsql_test 4.0 { |
| 391 |
PRAGMA auto_vacuum = 0; |
| 392 |
PRAGMA synchronous = NORMAL; |
| 393 |
PRAGMA journal_mode = WAL; |
| 394 |
PRAGMA cache_size = 1; |
| 395 |
|
| 396 |
CREATE TABLE t1 (i INTEGER PRIMARY KEY, b BLOB, t TEXT); |
| 397 |
PRAGMA wal_checkpoint; |
| 398 |
INSERT INTO t1 VALUES(1, randomblob(2048), 'one'); |
| 399 |
} {wal 0 2 2} |
| 400 |
|
| 401 |
do_execsql_test 4.1.1 { |
| 402 |
SAVEPOINT one; |
| 403 |
INSERT INTO t1 VALUES(2, randomblob(2048), 'two'); |
| 404 |
INSERT INTO t1 VALUES(3, randomblob(2048), 'three'); |
| 405 |
INSERT INTO t1 VALUES(4, randomblob(2048), 'four'); |
| 406 |
INSERT INTO t1 VALUES(5, randomblob(2048), 'five'); |
| 407 |
INSERT INTO t1 VALUES(6, randomblob(2048), 'six'); |
| 408 |
INSERT INTO t1 VALUES(7, randomblob(2048), 'seven'); |
| 409 |
|
| 410 |
UPDATE t1 SET b=randomblob(2048) WHERE i=5; |
| 411 |
UPDATE t1 SET b=randomblob(2048) WHERE i=6; |
| 412 |
UPDATE t1 SET b=randomblob(2048) WHERE i=7; |
| 413 |
} |
| 414 |
|
| 415 |
do_execsql_test 4.1.2 { |
| 416 |
ROLLBACK TO one; |
| 417 |
INSERT INTO t1 VALUES(8, NULL, 'eight'); |
| 418 |
RELEASE one; |
| 419 |
} {} |
| 420 |
|
| 421 |
do_execsql_test 4.2 { |
| 422 |
SELECT i, t FROM t1 |
| 423 |
} {1 one 8 eight} |
| 424 |
|
| 425 |
forcecopy test.db test2.db |
| 426 |
forcecopy test.db-wal test2.db-wal |
| 427 |
|
| 428 |
sqlite3 db2 test2.db |
| 429 |
do_test 4.3 { |
| 430 |
execsql { |
| 431 |
SELECT i, t FROM t1 |
| 432 |
} db2 |
| 433 |
} {1 one 8 eight} |
| 434 |
|
| 435 |
catch { db2 close } |
| 436 |
|
| 437 |
#------------------------------------------------------------------------- |
| 438 |
reset_db |
| 439 |
|
| 440 |
do_execsql_test 5.0 { |
| 441 |
PRAGMA auto_vacuum = 0; |
| 442 |
PRAGMA synchronous = NORMAL; |
| 443 |
PRAGMA journal_mode = WAL; |
| 444 |
PRAGMA cache_size = 1; |
| 445 |
|
| 446 |
CREATE TABLE t1 (i INTEGER PRIMARY KEY, b BLOB, t TEXT); |
| 447 |
INSERT INTO t1 VALUES(1, randomblob(2048), 'one'); |
| 448 |
INSERT INTO t1 VALUES(2, randomblob(2048), 'two'); |
| 449 |
INSERT INTO t1 VALUES(3, randomblob(2048), 'three'); |
| 450 |
PRAGMA wal_checkpoint; |
| 451 |
} {wal 0 14 14} |
| 452 |
|
| 453 |
do_execsql_test 5.1 { |
| 454 |
BEGIN; |
| 455 |
SELECT count(*) FROM t1; |
| 456 |
SAVEPOINT one; |
| 457 |
INSERT INTO t1 VALUES(4, randomblob(2048), 'four'); |
| 458 |
INSERT INTO t1 VALUES(5, randomblob(2048), 'five'); |
| 459 |
INSERT INTO t1 VALUES(6, randomblob(2048), 'six'); |
| 460 |
INSERT INTO t1 VALUES(7, randomblob(2048), 'seven'); |
| 461 |
ROLLBACK TO one; |
| 462 |
INSERT INTO t1 VALUES(8, randomblob(2048), 'eight'); |
| 463 |
INSERT INTO t1 VALUES(9, randomblob(2048), 'nine'); |
| 464 |
COMMIT; |
| 465 |
} {3} |
| 466 |
|
| 467 |
forcecopy test.db test2.db |
| 468 |
forcecopy test.db-wal test2.db-wal |
| 469 |
|
| 470 |
sqlite3 db2 test2.db |
| 471 |
do_test 5.2 { |
| 472 |
execsql { |
| 473 |
SELECT i, t FROM t1 |
| 474 |
} db2 |
| 475 |
} {1 one 2 two 3 three 8 eight 9 nine} |
| 476 |
db2 close |
| 477 |
|
| 478 |
do_execsql_test 5.3 { |
| 479 |
SELECT i, t FROM t1 |
| 480 |
} {1 one 2 two 3 three 8 eight 9 nine} |
| 481 |
|
| 482 |
|
| 483 |
finish_test |