| blob | e59647fe5013af5bb594ef2120b0b666dae92cf1 |
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 $
20 set testdir [file dirname $argv0]
21 source $testdir/tester.tcl
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 #
30 do_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]
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 #
57 ifcapable 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 }
80 do_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 }
102 # Test IO errors that can occur retrieving a record header that flows over
103 # onto an overflow page.
104 do_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 }
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
121 ifcapable 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 }
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 #
143 ifcapable 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 }
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 #
175 if {$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 }
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 #
201 do_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 }
212 # For test coverage: Cause an IO error whilst reading the master-journal
213 # name from a journal file.
214 if {$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 }
238 # For test coverage: Cause an IO error during statement playback (i.e.
239 # a constraint).
240 do_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 {
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
261 if {$msg != "UNIQUE constraint failed: t1.a"} {
262 error $msg
263 }
264 }
266 # Assertion fault bug reported by alex dimitrov.
267 #
268 do_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 }
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 #
279 ifcapable 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 }
296 ifcapable 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 }
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 #
320 sqlite3_simulate_device -char {} -sectorsize 2048
321 do_ioerr_test ioerr-12 -ckrefcount true -erc 1 -tclprep {
322 db close
323 sqlite3 db test.db -vfs devsym
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 }
335 sqlite3_simulate_device -char {} -sectorsize 0
336 catch {db close}
338 do_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 }
373 do_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;
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 }
412 do_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 }
435 # This test verifies that IO errors that occur within the obscure branch
436 # of code executed by tkt3762.test are correctly reported.
437 #
438 ifcapable 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 }
465 finish_test