1# 2001 October 12 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 for correct handling of I/O errors 13# such as writes failing because the disk is full. 14# 15# The tests in this file use special facilities that are only 16# available in the SQLite test fixture. 17# 18# $Id: ioerr.test,v 1.43 2009/04/06 17:50:03 danielk1977 Exp $ 19 20set testdir [file dirname $argv0] 21source $testdir/tester.tcl 22 23# If SQLITE_DEFAULT_AUTOVACUUM is set to true, then a simulated IO error 24# on the 8th IO operation in the SQL script below doesn't report an error. 25# 26# This is because the 8th IO call attempts to read page 2 of the database 27# file when the file on disk is only 1 page. The pager layer detects that 28# this has happened and suppresses the error returned by the OS layer. 29# 30do_ioerr_test ioerr-1 -erc 1 -ckrefcount 1 -sqlprep { 31 SELECT * FROM sqlite_master; 32} -sqlbody { 33 CREATE TABLE t1(a,b,c); 34 SELECT * FROM sqlite_master; 35 BEGIN TRANSACTION; 36 INSERT INTO t1 VALUES(1,2,3); 37 INSERT INTO t1 VALUES(4,5,6); 38 ROLLBACK; 39 SELECT * FROM t1; 40 BEGIN TRANSACTION; 41 INSERT INTO t1 VALUES(1,2,3); 42 INSERT INTO t1 VALUES(4,5,6); 43 COMMIT; 44 SELECT * FROM t1; 45 DELETE FROM t1 WHERE a<100; 46} -exclude [expr [string match [execsql {pragma auto_vacuum}] 1] ? 4 : 0] 47 48# Test for IO errors during a VACUUM. 49# 50# The first IO call is excluded from the test. This call attempts to read 51# the file-header of the temporary database used by VACUUM. Since the 52# database doesn't exist at that point, the IO error is not detected. 53# 54# Additionally, if auto-vacuum is enabled, the 12th IO error is not 55# detected. Same reason as the 8th in the test case above. 56# 57ifcapable vacuum { 58 do_ioerr_test ioerr-2 -cksum true -ckrefcount true -sqlprep { 59 BEGIN; 60 CREATE TABLE t1(a, b, c); 61 INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50)); 62 INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1; 63 INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1; 64 INSERT INTO t1 SELECT a+8, b||'-'||rowid, c||'-'||rowid FROM t1; 65 INSERT INTO t1 SELECT a+16, b||'-'||rowid, c||'-'||rowid FROM t1; 66 INSERT INTO t1 SELECT a+32, b||'-'||rowid, c||'-'||rowid FROM t1; 67 INSERT INTO t1 SELECT a+64, b||'-'||rowid, c||'-'||rowid FROM t1; 68 INSERT INTO t1 SELECT a+128, b||'-'||rowid, c||'-'||rowid FROM t1; 69 INSERT INTO t1 VALUES(1, randstr(600,600), randstr(600,600)); 70 CREATE TABLE t2 AS SELECT * FROM t1; 71 CREATE TABLE t3 AS SELECT * FROM t1; 72 COMMIT; 73 DROP TABLE t2; 74 } -sqlbody { 75 VACUUM; 76 } -exclude [list \ 77 1 [expr [string match [execsql {pragma auto_vacuum}] 1]?9:-1]] 78} 79 80do_ioerr_test ioerr-3 -ckrefcount true -tclprep { 81 execsql { 82 PRAGMA cache_size = 10; 83 BEGIN; 84 CREATE TABLE abc(a); 85 INSERT INTO abc VALUES(randstr(1500,1500)); -- Page 4 is overflow 86 } 87 for {set i 0} {$i<150} {incr i} { 88 execsql { 89 INSERT INTO abc VALUES(randstr(100,100)); 90 } 91 } 92 execsql COMMIT 93} -sqlbody { 94 CREATE TABLE abc2(a); 95 BEGIN; 96 DELETE FROM abc WHERE length(a)>100; 97 UPDATE abc SET a = randstr(90,90); 98 COMMIT; 99 CREATE TABLE abc3(a); 100} 101 102# Test IO errors that can occur retrieving a record header that flows over 103# onto an overflow page. 104do_ioerr_test ioerr-4 -ckrefcount true -tclprep { 105 set sql "CREATE TABLE abc(a1" 106 for {set i 2} {$i<1300} {incr i} { 107 append sql ", a$i" 108 } 109 append sql ");" 110 execsql $sql 111 execsql {INSERT INTO abc (a1) VALUES(NULL)} 112} -sqlbody { 113 SELECT * FROM abc; 114} 115 116 117# Test IO errors that may occur during a multi-file commit. 118# 119# Tests 8 and 17 are excluded when auto-vacuum is enabled for the same 120# reason as in test cases ioerr-1.XXX 121ifcapable attach { 122 set ex "" 123 if {[string match [execsql {pragma auto_vacuum}] 1]} { 124 set ex [list 4 17] 125 } 126 do_ioerr_test ioerr-5 -restoreprng 0 -ckrefcount true -sqlprep { 127 ATTACH 'test2.db' AS test2; 128 } -sqlbody { 129 BEGIN; 130 CREATE TABLE t1(a,b,c); 131 CREATE TABLE test2.t2(a,b,c); 132 COMMIT; 133 } -exclude $ex 134} 135 136# Test IO errors when replaying two hot journals from a 2-file 137# transaction. This test only runs on UNIX. 138# 139# It cannot be run under the "exclusive" permutation. In that case, the 140# locks held by the connection in the local (this) process prevent a 141# second connection from attempting the multi-file transaction. 142# 143ifcapable crashtest&&attach { 144 if {![catch {sqlite3 -has-codec} r] && !$r && [permutation]!="exclusive"} { 145 do_ioerr_test ioerr-6 -ckrefcount true -tclprep { 146 execsql { 147 ATTACH 'test2.db' as aux; 148 CREATE TABLE tx(a, b); 149 CREATE TABLE aux.ty(a, b); 150 } 151 set rc [crashsql -delay 2 -file test2.db-journal { 152 ATTACH 'test2.db' as aux; 153 PRAGMA cache_size = 10; 154 BEGIN; 155 CREATE TABLE aux.t2(a, b, c); 156 CREATE TABLE t1(a, b, c); 157 COMMIT; 158 }] 159 if {$rc!="1 {child process exited abnormally}"} { 160 error "Wrong error message: $rc" 161 } 162 } -sqlbody { 163 SELECT * FROM sqlite_master; 164 SELECT * FROM aux.sqlite_master; 165 } 166 } 167} 168 169# Test handling of IO errors that occur while rolling back hot journal 170# files. 171# 172# These tests can't be run on windows because the windows version of 173# SQLite holds a mandatory exclusive lock on journal files it has open. 174# 175if {$tcl_platform(platform)!="windows"} { 176 do_ioerr_test ioerr-7 -tclprep { 177 db close 178 sqlite3 db2 test2.db 179 db2 eval { 180 PRAGMA synchronous = 0; 181 CREATE TABLE t1(a, b); 182 INSERT INTO t1 VALUES(1, 2); 183 BEGIN; 184 INSERT INTO t1 VALUES(3, 4); 185 } 186 forcecopy test2.db test.db 187 forcecopy test2.db-journal test.db-journal 188 db2 close 189 } -tclbody { 190 sqlite3 db test.db 191 db eval { 192 SELECT * FROM t1; 193 } 194 } -exclude 1 195} 196 197# For test coverage: Cause an I/O failure while trying to read a 198# short field (one that fits into a Mem buffer without mallocing 199# for space). 200# 201do_ioerr_test ioerr-8 -ckrefcount true -tclprep { 202 execsql { 203 CREATE TABLE t1(a,b,c); 204 INSERT INTO t1 VALUES(randstr(200,200), randstr(1000,1000), 2); 205 } 206 db close 207 sqlite3 db test.db 208} -sqlbody { 209 SELECT c FROM t1; 210} 211 212# For test coverage: Cause an IO error whilst reading the master-journal 213# name from a journal file. 214if {$tcl_platform(platform)=="unix"} { 215 do_ioerr_test ioerr-9 -ckrefcount true -tclprep { 216 execsql { 217 CREATE TABLE t1(a,b,c); 218 INSERT INTO t1 VALUES(randstr(200,200), randstr(1000,1000), 2); 219 BEGIN; 220 INSERT INTO t1 VALUES(randstr(200,200), randstr(1000,1000), 2); 221 } 222 forcecopy test.db-journal test2.db-journal 223 execsql { 224 COMMIT; 225 } 226 forcecopy test2.db-journal test.db-journal 227 set f [open test.db-journal a] 228 fconfigure $f -encoding binary 229 puts -nonewline $f "hello" 230 puts -nonewline $f "\x00\x00\x00\x05\x01\x02\x03\x04" 231 puts -nonewline $f "\xd9\xd5\x05\xf9\x20\xa1\x63\xd7" 232 close $f 233 } -sqlbody { 234 SELECT a FROM t1; 235 } 236} 237 238# For test coverage: Cause an IO error during statement playback (i.e. 239# a constraint). 240do_ioerr_test ioerr-10 -ckrefcount true -tclprep { 241 execsql { 242 BEGIN; 243 CREATE TABLE t1(a PRIMARY KEY, b); 244 } 245 for {set i 0} {$i < 500} {incr i} { 246 execsql {INSERT INTO t1 VALUES(:i, 'hello world');} 247 } 248 execsql { 249 COMMIT; 250 } 251} -tclbody { 252 253 catch {execsql { 254 BEGIN; 255 INSERT INTO t1 VALUES('abc', 123); 256 INSERT INTO t1 VALUES('def', 123); 257 INSERT INTO t1 VALUES('ghi', 123); 258 INSERT INTO t1 SELECT (a+500)%900, 'good string' FROM t1; 259 }} msg 260 261 if {$msg != "column a is not unique"} { 262 error $msg 263 } 264} 265 266# Assertion fault bug reported by alex dimitrov. 267# 268do_ioerr_test ioerr-11 -ckrefcount true -erc 1 -sqlprep { 269 CREATE TABLE A(Id INTEGER, Name TEXT); 270 INSERT INTO A(Id, Name) VALUES(1, 'Name'); 271} -sqlbody { 272 UPDATE A SET Id = 2, Name = 'Name2' WHERE Id = 1; 273} 274 275# Test that an io error encountered in a sync() caused by a call to 276# sqlite3_release_memory() is handled Ok. Only try this if 277# memory-management is enabled. 278# 279ifcapable memorymanage { 280 do_ioerr_test memmanage-ioerr1 -ckrefcount true -sqlprep { 281 BEGIN; 282 CREATE TABLE t1(a, b, c); 283 INSERT INTO t1 VALUES(randstr(50,50), randstr(100,100), randstr(10,10)); 284 INSERT INTO t1 SELECT randstr(50,50), randstr(9,9), randstr(90,90) FROM t1; 285 INSERT INTO t1 SELECT randstr(50,50), randstr(9,9), randstr(90,90) FROM t1; 286 INSERT INTO t1 SELECT randstr(50,50), randstr(9,9), randstr(90,90) FROM t1; 287 INSERT INTO t1 SELECT randstr(50,50), randstr(9,9), randstr(90,90) FROM t1; 288 INSERT INTO t1 SELECT randstr(50,50), randstr(9,9), randstr(90,90) FROM t1; 289 } -tclbody { 290 sqlite3_release_memory 291 } -sqlbody { 292 COMMIT; 293 } 294} 295 296ifcapable pager_pragmas&&autovacuum { 297 do_ioerr_test ioerr-12 -ckrefcount true -erc 1 -sqlprep { 298 PRAGMA page_size = 512; 299 PRAGMA auto_vacuum = incremental; 300 CREATE TABLE t1(x); 301 INSERT INTO t1 VALUES( randomblob(1 * (512-4)) ); 302 INSERT INTO t1 VALUES( randomblob(110 * (512-4)) ); 303 INSERT INTO t1 VALUES( randomblob(2 * (512-4)) ); 304 INSERT INTO t1 VALUES( randomblob(110 * (512-4)) ); 305 INSERT INTO t1 VALUES( randomblob(3 * (512-4)) ); 306 DELETE FROM t1 WHERE rowid = 3; 307 PRAGMA incremental_vacuum = 2; 308 DELETE FROM t1 WHERE rowid = 1; 309 } -sqlbody { 310 PRAGMA incremental_vacuum = 1; 311 } 312} 313 314# Usually, after a new page is allocated from the end of the file, it does 315# not need to be written to the journal. The exception is when the new page 316# shares its sector with an existing page that does need to be journalled. 317# This test case provokes this condition to test for the sake of coverage 318# that an IO error while journalling the coresident page is handled correctly. 319# 320sqlite3_simulate_device -char {} -sectorsize 2048 321do_ioerr_test ioerr-12 -ckrefcount true -erc 1 -tclprep { 322 db close 323 sqlite3 db test.db -vfs devsym 324 325 # Create a test database. Page 2 is the root page of table t1. The only 326 # row inserted into t1 has an overflow page - page 3. Page 3 will be 327 # coresident on the 2048 byte sector with the next page to be allocated. 328 # 329 db eval { PRAGMA page_size = 1024 } 330 db eval { CREATE TABLE t1(x) } 331 db eval { INSERT INTO t1 VALUES(randomblob(1100)); } 332} -tclbody { 333 db eval { INSERT INTO t1 VALUES(randomblob(2000)); } 334} 335sqlite3_simulate_device -char {} -sectorsize 0 336catch {db close} 337 338do_ioerr_test ioerr-13 -ckrefcount true -erc 1 -sqlprep { 339 PRAGMA auto_vacuum = incremental; 340 CREATE TABLE t1(x); 341 CREATE TABLE t2(x); 342 INSERT INTO t2 VALUES(randomblob(1500)); 343 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 344 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 345 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 346 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 347 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 348 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 349 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 350 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 351 INSERT INTO t1 VALUES(randomblob(20)); 352 INSERT INTO t1 SELECT x FROM t1; 353 INSERT INTO t1 SELECT x FROM t1; 354 INSERT INTO t1 SELECT x FROM t1; 355 INSERT INTO t1 SELECT x FROM t1; 356 INSERT INTO t1 SELECT x FROM t1; 357 INSERT INTO t1 SELECT x FROM t1; /* 64 entries in t1 */ 358 INSERT INTO t1 SELECT x FROM t1 LIMIT 14; /* 78 entries in t1 */ 359 DELETE FROM t2 WHERE rowid = 3; 360} -sqlbody { 361 -- This statement uses the balance_quick() optimization. The new page 362 -- is appended to the database file. But the overflow page used by 363 -- the new record will be positioned near the start of the database 364 -- file, in the gap left by the "DELETE FROM t2 WHERE rowid=3" statement 365 -- above. 366 -- 367 -- The point of this is that the statement wil need to update two pointer 368 -- map pages. Which introduces another opportunity for an IO error. 369 -- 370 INSERT INTO t1 VALUES(randomblob(2000)); 371} 372 373do_ioerr_test ioerr-14 -ckrefcount true -erc 1 -sqlprep { 374 PRAGMA auto_vacuum = incremental; 375 CREATE TABLE t1(x); 376 CREATE TABLE t2(x); 377 INSERT INTO t2 VALUES(randomblob(1500)); 378 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 379 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 380 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 381 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 382 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 383 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 384 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 385 INSERT INTO t2 SELECT randomblob(1500) FROM t2; 386 387 -- This statement inserts a row into t1 with an overflow page at the 388 -- end of the file. A long way from its parent (the root of t1). 389 INSERT INTO t1 VALUES(randomblob(1500)); 390 DELETE FROM t2 WHERE rowid<10; 391} -sqlbody { 392 -- This transaction will cause the root-page of table t1 to divide 393 -- (by calling balance_deeper()). When it does, the "parent" page of the 394 -- overflow page inserted in the -sqlprep block above will change and 395 -- the corresponding pointer map page be updated. This test case attempts 396 -- to cause an IO error during the pointer map page update. 397 -- 398 BEGIN; 399 INSERT INTO t1 VALUES(randomblob(100)); 400 INSERT INTO t1 VALUES(randomblob(100)); 401 INSERT INTO t1 VALUES(randomblob(100)); 402 INSERT INTO t1 VALUES(randomblob(100)); 403 INSERT INTO t1 VALUES(randomblob(100)); 404 INSERT INTO t1 VALUES(randomblob(100)); 405 INSERT INTO t1 VALUES(randomblob(100)); 406 INSERT INTO t1 VALUES(randomblob(100)); 407 INSERT INTO t1 VALUES(randomblob(100)); 408 INSERT INTO t1 VALUES(randomblob(100)); 409 COMMIT; 410} 411 412do_ioerr_test ioerr-15 -tclprep { 413 db eval { 414 BEGIN; 415 PRAGMA cache_size = 10; 416 CREATE TABLE t1(a); 417 CREATE INDEX i1 ON t1(a); 418 CREATE TABLE t2(a); 419 } 420 for {set ii 1} {$ii < 100} {incr ii} { 421 set v [string range [string repeat [format %.3d $ii] 200] 0 220] 422 db eval {INSERT INTO t1 VALUES($v)} 423 } 424 db eval { 425 DELETE FROM t1 WHERE oid > 85; 426 COMMIT; 427 } 428} -sqlbody { 429 BEGIN; 430 INSERT INTO t2 VALUES(randstr(22000,22000)); 431 DELETE FROM t1 WHERE oid = 83; 432 COMMIT; 433} 434 435# This test verifies that IO errors that occur within the obscure branch 436# of code executed by tkt3762.test are correctly reported. 437# 438ifcapable vacuum&&autovacuum&&pragma { 439 do_ioerr_test ioerr-16 -erc 1 -ckrefcount 1 -sqlprep { 440 PRAGMA auto_vacuum=INCREMENTAL; 441 PRAGMA page_size=1024; 442 BEGIN; 443 CREATE TABLE t1(x); 444 INSERT INTO t1 VALUES(zeroblob(900)); 445 INSERT INTO t1 VALUES(zeroblob(900)); 446 INSERT INTO t1 SELECT x FROM t1; 447 INSERT INTO t1 SELECT x FROM t1; 448 INSERT INTO t1 SELECT x FROM t1; 449 INSERT INTO t1 SELECT x FROM t1; 450 INSERT INTO t1 SELECT x FROM t1; 451 INSERT INTO t1 SELECT x FROM t1; 452 INSERT INTO t1 SELECT x FROM t1; 453 DELETE FROM t1 WHERE rowid>202; 454 COMMIT; 455 VACUUM; 456 PRAGMA cache_size = 10; 457 BEGIN; 458 DELETE FROM t1 WHERE rowid IN (10,11,12) ; 459 } -sqlbody { 460 PRAGMA incremental_vacuum(10); 461 COMMIT; 462 } 463} 464 465finish_test 466