AryaWu/sqlite
0
1# 2007 April 262#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing the incremental vacuum feature.13#14# Note: There are also some tests for incremental vacuum and IO 15# errors in incrvacuum_ioerr.test.16#17# $Id: incrvacuum.test,v 1.23 2009/02/18 20:31:18 drh Exp $18 19set testdir [file dirname $argv0]20source $testdir/tester.tcl21 22# If this build of the library does not support auto-vacuum, omit this23# whole file.24ifcapable {!autovacuum || !pragma} {25 finish_test26 return27}28 29#---------------------------------------------------------------------30# Test the pragma on an empty database.31#32do_test incrvacuum-1.1 {33 execsql {34 pragma auto_vacuum;35 }36} $sqlite_options(default_autovacuum)37do_test incrvacuum-1.2.0 {38 # File size is sometimes 1 instead of 0 due to the hack we put in39 # to work around ticket #3260. Search for comments on #3260 in40 # os_unix.c.41 expr {[file size test.db] > 1}42} {0}43do_test incrvacuum-1.2 {44 # This command will create the database.45 execsql {46 pragma auto_vacuum = 'full';47 pragma auto_vacuum;48 }49} {1}50do_test incrvacuum-1.2.1 {51 expr {[file size test.db] > 0}52} {1}53do_test incrvacuum-1.3 {54 execsql {55 pragma auto_vacuum = 'incremental';56 pragma auto_vacuum;57 }58} {2}59do_test incrvacuum-1.4 {60 # In this case the invalid value is ignored and the auto_vacuum61 # setting remains unchanged.62 execsql {63 pragma auto_vacuum = 'invalid';64 pragma auto_vacuum;65 }66} {2}67do_test incrvacuum-1.5 {68 execsql {69 pragma auto_vacuum = 1;70 pragma auto_vacuum;71 }72} {1}73do_test incrvacuum-1.6 {74 execsql {75 pragma auto_vacuum = '2';76 pragma auto_vacuum;77 }78} {2}79do_test incrvacuum-1.7 {80 # Invalid value. auto_vacuum setting remains unchanged.81 execsql {82 pragma auto_vacuum = 5;83 pragma auto_vacuum;84 }85} {2}86 87#---------------------------------------------------------------------88# Test the pragma on a non-empty database. It is possible to toggle89# the connection between "full" and "incremental" mode, but not to90# change from either of these to "none", or from "none" to "full" or91# "incremental".92#93do_test incrvacuum-2.1 {94 execsql {95 pragma auto_vacuum = 1;96 CREATE TABLE abc(a, b, c);97 }98} {}99do_test incrvacuum-2.2 {100 execsql {101 pragma auto_vacuum = 'none';102 pragma auto_vacuum;103 }104} {1}105do_test incrvacuum-2.2.1 {106 db close107 sqlite3 db test.db108 execsql {109 pragma auto_vacuum;110 }111} {1}112do_test incrvacuum-2.3 {113 execsql {114 pragma auto_vacuum = 'incremental';115 pragma auto_vacuum;116 }117} {2}118do_test incrvacuum-2.4 {119 execsql {120 pragma auto_vacuum = 'full';121 pragma auto_vacuum;122 }123} {1}124 125#---------------------------------------------------------------------126# Test that when the auto_vacuum mode is "incremental", the database127# does not shrink when pages are removed from it. But it does if128# the mode is set to "full".129#130do_test incrvacuum-3.1 {131 execsql {132 pragma auto_vacuum;133 }134} {1}135do_test incrvacuum-3.2 {136 set ::str [string repeat 1234567890 110]137 execsql {138 PRAGMA auto_vacuum = 2;139 BEGIN;140 CREATE TABLE tbl2(str);141 INSERT INTO tbl2 VALUES($::str);142 COMMIT;143 }144 # 5 pages:145 #146 # 1 -> database header147 # 2 -> first back-pointer page148 # 3 -> table abc149 # 4 -> table tbl2150 # 5 -> table tbl2 overflow page.151 #152 expr {[file size test.db] / 1024}153} {5}154do_test incrvacuum-3.3 {155 execsql {156 DROP TABLE abc;157 DELETE FROM tbl2;158 }159 expr {[file size test.db] / 1024}160} {5}161do_test incrvacuum-3.4 {162 execsql {163 PRAGMA auto_vacuum = 1;164 INSERT INTO tbl2 VALUES('hello world');165 }166 expr {[file size test.db] / 1024}167} {3}168 169#---------------------------------------------------------------------170# Try to run a very simple incremental vacuum. Also verify that 171# PRAGMA incremental_vacuum is a harmless no-op against a database that172# does not support auto-vacuum.173#174do_test incrvacuum-4.1 {175 set ::str [string repeat 1234567890 110]176 execsql {177 PRAGMA auto_vacuum = 2;178 INSERT INTO tbl2 VALUES($::str);179 CREATE TABLE tbl1(a, b, c);180 }181 expr {[file size test.db] / 1024}182} {5}183do_test incrvacuum-4.2 {184 execsql {185 DELETE FROM tbl2;186 DROP TABLE tbl1;187 }188 expr {[file size test.db] / 1024}189} {5}190do_test incrvacuum-4.3 {191 set ::nStep 0192 db eval {pragma incremental_vacuum(10)} {193 incr ::nStep194 }195 list [expr {[file size test.db] / 1024}] $::nStep196} {3 2}197 198#---------------------------------------------------------------------199# The following tests - incrvacuum-5.* - test incremental vacuum200# from within a transaction.201#202do_test incrvacuum-5.1.1 {203 expr {[file size test.db] / 1024}204} {3}205do_test incrvacuum-5.1.2 {206 execsql {207 BEGIN;208 DROP TABLE tbl2;209 PRAGMA incremental_vacuum;210 COMMIT;211 }212 expr {[file size test.db] / 1024}213} {1}214 215do_test incrvacuum-5.2.1 {216 set ::str [string repeat abcdefghij 110]217 execsql {218 BEGIN;219 CREATE TABLE tbl1(a);220 INSERT INTO tbl1 VALUES($::str);221 PRAGMA incremental_vacuum; -- this is a no-op.222 COMMIT;223 }224 expr {[file size test.db] / 1024}225} {4}226do_test incrvacuum-5.2.2 {227 set ::str [string repeat abcdefghij 110]228 execsql {229 BEGIN;230 INSERT INTO tbl1 VALUES($::str);231 INSERT INTO tbl1 SELECT * FROM tbl1;232 DELETE FROM tbl1 WHERE oid%2; -- Put 2 overflow pages on free-list.233 COMMIT;234 }235 expr {[file size test.db] / 1024}236} {7}237do_test incrvacuum-5.2.3 {238 execsql {239 BEGIN;240 PRAGMA incremental_vacuum; -- Vacuum up the two pages.241 CREATE TABLE tbl2(b); -- Use one free page as a table root.242 INSERT INTO tbl2 VALUES('a nice string');243 COMMIT;244 }245 expr {[file size test.db] / 1024}246} {6}247do_test incrvacuum-5.2.4 {248 execsql {249 SELECT * FROM tbl2;250 }251} {{a nice string}}252do_test incrvacuum-5.2.5 {253 execsql {254 DROP TABLE tbl1;255 DROP TABLE tbl2;256 PRAGMA incremental_vacuum;257 }258 expr {[file size test.db] / 1024}259} {1}260 261 262# Test cases incrvacuum-5.3.* use the following list as input data.263# Two new databases are opened, one with incremental vacuum enabled,264# the other with no auto-vacuum completely disabled. After executing265# each element of the following list on both databases, test that266# the integrity-check passes and the contents of each are identical.267# 268set TestScriptList [list {269 BEGIN;270 CREATE TABLE t1(a, b);271 CREATE TABLE t2(a, b);272 CREATE INDEX t1_i ON t1(a);273 CREATE INDEX t2_i ON t2(a);274} {275 INSERT INTO t1 VALUES($::str1, $::str2);276 INSERT INTO t1 VALUES($::str1||$::str2, $::str2||$::str1);277 INSERT INTO t2 SELECT b, a FROM t1;278 INSERT INTO t2 SELECT a, b FROM t1;279 INSERT INTO t1 SELECT b, a FROM t2;280 UPDATE t2 SET b = '';281 PRAGMA incremental_vacuum;282} {283 UPDATE t2 SET b = (SELECT b FROM t1 WHERE t1.oid = t2.oid);284 PRAGMA incremental_vacuum;285} {286 CREATE TABLE t3(a, b);287 INSERT INTO t3 SELECT * FROM t2;288 DROP TABLE t2;289 PRAGMA incremental_vacuum;290} {291 CREATE INDEX t3_i ON t3(a);292 COMMIT;293} {294 BEGIN;295 DROP INDEX t3_i;296 PRAGMA incremental_vacuum;297 INSERT INTO t3 VALUES('hello', 'world');298 ROLLBACK;299} {300 INSERT INTO t3 VALUES('hello', 'world');301}302]303 304# If this build omits subqueries, step 2 in the above list will not305# work. Replace it with "" in this case. 306#307ifcapable !subquery { lset TestScriptList 2 "" }308 309# Compare the contents of databases $A and $B.310#311proc compare_dbs {A B tname} {312 set tbl_list [execsql {313 SELECT tbl_name FROM sqlite_master WHERE type = 'table'314 } $A]315 316 do_test ${tname}.1 [subst {317 execsql {318 SELECT tbl_name FROM sqlite_master WHERE type = 'table'319 } $B320 }] $tbl_list321 322 set tn 1323 foreach tbl $tbl_list {324 set control [execsql "SELECT * FROM $tbl" $A]325 do_test ${tname}.[incr tn] [subst {326 execsql "SELECT * FROM $tbl" $B327 }] $control328 }329}330 331set ::str1 [string repeat abcdefghij 130]332set ::str2 [string repeat 1234567890 105]333 334forcedelete test1.db test1.db-journal test2.db test2.db-journal335sqlite3 db1 test1.db336sqlite3 db2 test2.db337execsql { PRAGMA auto_vacuum = 'none' } db1338execsql { PRAGMA auto_vacuum = 'incremental' } db2339 340set tn 1341foreach sql $::TestScriptList {342 execsql $sql db1343 execsql $sql db2344 345 compare_dbs db1 db2 incrvacuum-5.3.${tn}346 do_test incrvacuum-5.3.${tn}.integrity1 {347 execsql { PRAGMA integrity_check; } db1348 } {ok}349 do_test incrvacuum-5.3.${tn}.integrity2 {350 execsql { PRAGMA integrity_check; } db2351 } {ok}352 incr tn353}354db1 close355db2 close356#357# End of test cases 5.3.*358 359#---------------------------------------------------------------------360# The following tests - incrvacuum-6.* - test running incremental 361# vacuum while another statement (a read) is being executed.362#363for {set jj 0} {$jj < 10} {incr jj} {364 # Build some test data. Two tables are created in an empty365 # database. tbl1 data is a contiguous block starting at page 5 (pages366 # 3 and 4 are the table roots). tbl2 is a contiguous block starting 367 # right after tbl1.368 #369 # Then drop tbl1 so that when an incr vacuum is run the pages370 # of tbl2 have to be moved to fill the gap.371 #372 do_test incrvacuum-6.${jj}.1 {373 execsql {374 DROP TABLE IF EXISTS tbl1;375 DROP TABLE IF EXISTS tbl2;376 PRAGMA incremental_vacuum;377 CREATE TABLE tbl1(a, b);378 CREATE TABLE tbl2(a, b);379 BEGIN;380 }381 for {set ii 0} {$ii < 1000} {incr ii} {382 db eval {INSERT INTO tbl1 VALUES($ii, $ii || $ii)}383 }384 execsql {385 INSERT INTO tbl2 SELECT * FROM tbl1;386 COMMIT;387 DROP TABLE tbl1;388 }389 expr {[file size test.db] / 1024}390 } {36}391 392 # Run a linear scan query on tbl2. After reading ($jj*100) rows, 393 # run the incremental vacuum to shrink the database.394 #395 do_test incrvacuum-6.${jj}.2 {396 set ::nRow 0397 db eval {SELECT a FROM tbl2} {} {398 if {$a == [expr $jj*100]} {399 db eval {PRAGMA incremental_vacuum}400 }401 incr ::nRow402 }403 list [expr {[file size test.db] / 1024}] $nRow404 } {19 1000}405}406 407#---------------------------------------------------------------------408# This test - incrvacuum-7.* - is to check that the database can be409# written in the middle of an incremental vacuum.410#411set ::iWrite 1412while 1 {413 do_test incrvacuum-7.${::iWrite}.1 {414 execsql {415 DROP TABLE IF EXISTS tbl1;416 DROP TABLE IF EXISTS tbl2;417 PRAGMA incremental_vacuum;418 CREATE TABLE tbl1(a, b);419 CREATE TABLE tbl2(a, b);420 BEGIN;421 }422 for {set ii 0} {$ii < 1000} {incr ii} {423 db eval {INSERT INTO tbl1 VALUES($ii, $ii || $ii)}424 }425 execsql {426 INSERT INTO tbl2 SELECT * FROM tbl1;427 COMMIT;428 DROP TABLE tbl1;429 }430 expr {[file size test.db] / 1024}431 } {36}432 433 do_test incrvacuum-7.${::iWrite}.2 {434 set ::nRow 0435 db eval {PRAGMA incremental_vacuum} {436 incr ::nRow437 if {$::nRow == $::iWrite} {438 db eval {439 CREATE TABLE tbl1(a, b);440 INSERT INTO tbl1 VALUES('hello', 'world');441 }442 }443 }444 list [expr {[file size test.db] / 1024}]445 } {20}446 447 do_test incrvacuum-7.${::iWrite}.3 {448 execsql {449 SELECT * FROM tbl1;450 }451 } {hello world}452 453 if {$::nRow == $::iWrite} break454 incr ::iWrite455}456 457#---------------------------------------------------------------------458# This test - incrvacuum-8.* - is to check that nothing goes wrong459# with an incremental-vacuum if it is the first statement executed460# after an existing database is opened.461#462# At one point, this would always return SQLITE_SCHEMA (which 463# causes an infinite loop in tclsqlite.c if using the Tcl interface).464#465do_test incrvacuum-8.1 {466 db close467 sqlite3 db test.db468 execsql {469 PRAGMA incremental_vacuum(50);470 }471} {}472 473#---------------------------------------------------------------------474# At one point this test case was causing an assert() to fail.475#476do_test incrvacuum-9.1 {477 db close478 forcedelete test.db test.db-journal479 sqlite3 db test.db480 481 execsql {482 PRAGMA auto_vacuum = 'incremental';483 CREATE TABLE t1(a, b, c);484 CREATE TABLE t2(a, b, c);485 INSERT INTO t2 VALUES(randstr(500,500),randstr(500,500),randstr(500,500));486 INSERT INTO t1 VALUES(1, 2, 3);487 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;488 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;489 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;490 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;491 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;492 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;493 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;494 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;495 }496} {}497 498do_test incrvacuum-9.2 {499 execsql {500 PRAGMA synchronous = 'OFF';501 BEGIN;502 UPDATE t1 SET a = a, b = b, c = c;503 DROP TABLE t2;504 PRAGMA incremental_vacuum(10);505 ROLLBACK;506 }507} {}508 509do_test incrvacuum-9.3 {510 execsql {511 PRAGMA cache_size = 10;512 BEGIN;513 UPDATE t1 SET a = a, b = b, c = c;514 DROP TABLE t2;515 PRAGMA incremental_vacuum(10);516 ROLLBACK;517 }518} {}519 520#---------------------------------------------------------------------521# Test that the parameter to the incremental_vacuum pragma works. That522# is, if the user executes "PRAGMA incremental_vacuum(N)", at most523# N pages are vacuumed.524#525do_test incrvacuum-10.1 {526 execsql {527 DROP TABLE t1;528 DROP TABLE t2;529 }530 expr [file size test.db] / 1024531} {29}532 533do_test incrvacuum-10.2 {534 execsql {535 PRAGMA incremental_vacuum(1);536 }537 expr [file size test.db] / 1024538} {28}539 540do_test incrvacuum-10.3 {541 execsql {542 PRAGMA incremental_vacuum(5);543 }544 expr [file size test.db] / 1024545} {23}546 547do_test incrvacuum-10.4 {548 execsql {549 PRAGMA incremental_vacuum('1');550 }551 expr [file size test.db] / 1024552} {22}553 554do_test incrvacuum-10.5 {555 execsql {556 PRAGMA incremental_vacuum("+3");557 }558 expr [file size test.db] / 1024559} {19}560 561do_test incrvacuum-10.6 {562 execsql {563 PRAGMA incremental_vacuum = 1;564 }565 expr [file size test.db] / 1024566} {18}567 568do_test incrvacuum-10.7 {569 # Use a really big number as an argument to incremetal_vacuum. Should570 # be interpreted as "free all possible space".571 execsql {572 PRAGMA incremental_vacuum(2147483649);573 }574 expr [file size test.db] / 1024575} {1}576 577do_test incrvacuum-10.8 {578 execsql {579 CREATE TABLE t1(x);580 INSERT INTO t1 VALUES(hex(randomblob(1000)));581 DROP TABLE t1;582 }583 # A negative number means free all possible space.584 execsql {585 PRAGMA incremental_vacuum=-1;586 }587 expr [file size test.db] / 1024588} {1}589 590#----------------------------------------------------------------591# Test that if we set the auto_vacuum mode to 'incremental', then592# create a database, thereafter that database defaults to incremental 593# vacuum mode.594#595db close596forcedelete test.db test.db-journal597sqlite3 db test.db598 599ifcapable default_autovacuum {600 do_test incrvacuum-11.1-av-dflt-on {601 execsql {602 PRAGMA auto_vacuum;603 }604 } $AUTOVACUUM605} else {606 do_test incrvacuum-11.1-av-dflt-off {607 execsql {608 PRAGMA auto_vacuum;609 }610 } {0}611}612do_test incrvacuum-11.2 {613 execsql {614 PRAGMA auto_vacuum = incremental;615 }616} {}617do_test incrvacuum-11.3 {618 execsql {619 PRAGMA auto_vacuum;620 }621} {2}622do_test incrvacuum-11.4 {623 # The database has now been created.624 expr {[file size test.db]>0}625} {1}626do_test incrvacuum-11.5 {627 # Close and reopen the connection.628 db close629 sqlite3 db test.db630 631 # Test we are still in incremental vacuum mode.632 execsql { PRAGMA auto_vacuum; }633} {2}634do_test incrvacuum-11.6 {635 execsql {636 PRAGMA auto_vacuum = 'full';637 PRAGMA auto_vacuum;638 }639} {1}640do_test incrvacuum-11.7 {641 # Close and reopen the connection.642 db close643 sqlite3 db test.db644 645 # Test we are still in "full" auto-vacuum mode.646 execsql { PRAGMA auto_vacuum; }647} {1}648 649#----------------------------------------------------------------------650# Special case: What happens if the database is locked when a "PRAGMA651# auto_vacuum = XXX" statement is executed.652#653db close654forcedelete test.db test.db-journal655sqlite3 db test.db656 657do_test incrvacuum-12.1 {658 execsql {659 PRAGMA auto_vacuum = 1;660 }661 expr {[file size test.db]>0}662} {1}663 664# Try to change the auto-vacuum from "full" to "incremental" while the665# database is locked. Nothing should change.666#667do_test incrvacuum-12.2 {668 sqlite3 db2 test.db669 execsql { BEGIN EXCLUSIVE; } db2670 catchsql { PRAGMA auto_vacuum = 2; }671} {1 {database is locked}}672 673do_test incrvacuum-12.3 {674 execsql { ROLLBACK; } db2675 execsql { PRAGMA auto_vacuum }676} {2} ;# Still 2 because PRAGMA auto_vacuum setting held in case of vacuum677do_test incrvacuum-12.4 {678 db close679 sqlite3 db test.db680 execsql { PRAGMA auto_vacuum }681} {1} ;# Revert to 1 because the database file did not change682 683do_test incrvacuum-12.5 {684 execsql { SELECT * FROM sqlite_master }685 execsql { PRAGMA auto_vacuum }686} {1}687 688#----------------------------------------------------------------------689# Special case #2: What if one process prepares a "PRAGMA auto_vacuum = XXX"690# statement when the database is empty, but doesn't execute it until691# after some other process has created the database.692#693db2 close694db close695forcedelete test.db test.db-journal696sqlite3 db test.db ; set ::DB [sqlite3_connection_pointer db]697sqlite3 db2 test.db698 699do_test incrvacuum-13.1 {700 # File size is sometimes 1 instead of 0 due to the hack we put in701 # to work around ticket #3260. Search for comments on #3260 in702 # os_unix.c.703 expr {[file size test.db]>1}704} {0}705do_test incrvacuum-13.2 {706 set ::STMT [sqlite3_prepare $::DB {PRAGMA auto_vacuum = 2} -1 DUMMY]707 execsql {708 PRAGMA auto_vacuum = none;709 PRAGMA default_cache_size = 1024;710 PRAGMA auto_vacuum;711 } db2712} {0}713do_test incrvacuum-13.3 {714 expr {[file size test.db]>0}715} {1}716do_test incrvacuum-13.4 {717 set rc [sqlite3_step $::STMT]718 list $rc [sqlite3_finalize $::STMT]719} {SQLITE_DONE SQLITE_OK}720do_test incrvacuum-13.5 {721 execsql {722 PRAGMA auto_vacuum;723 }724} {0}725 726 727# Verify that the incremental_vacuum pragma fails gracefully if it728# is used against an invalid database file.729#730if {[permutation] == ""} {731 do_test incrvacuum-14.1 {732 set out [open invalid.db w]733 puts $out "This is not an SQLite database file"734 close $out735 sqlite3 db3 invalid.db736 catchsql {737 PRAGMA incremental_vacuum(10);738 } db3739 } {1 {file is not a database}}740 db3 close741}742 743do_test incrvacuum-15.1 {744 db close745 db2 close746 forcedelete test.db747 sqlite3 db test.db748 749 set str [string repeat "abcdefghij" 500]750 751 execsql {752 PRAGMA cache_size = 10;753 PRAGMA auto_vacuum = incremental;754 CREATE TABLE t1(x, y);755 INSERT INTO t1 VALUES('a', $str);756 INSERT INTO t1 VALUES('b', $str);757 INSERT INTO t1 VALUES('c', $str);758 INSERT INTO t1 VALUES('d', $str);759 INSERT INTO t1 VALUES('e', $str);760 INSERT INTO t1 VALUES('f', $str);761 INSERT INTO t1 VALUES('g', $str);762 INSERT INTO t1 VALUES('h', $str);763 INSERT INTO t1 VALUES('i', $str);764 INSERT INTO t1 VALUES('j', $str);765 INSERT INTO t1 VALUES('j', $str);766 767 CREATE TABLE t2(x PRIMARY KEY, y);768 INSERT INTO t2 VALUES('a', $str);769 INSERT INTO t2 VALUES('b', $str);770 INSERT INTO t2 VALUES('c', $str);771 INSERT INTO t2 VALUES('d', $str);772 773 BEGIN;774 DELETE FROM t2;775 PRAGMA incremental_vacuum;776 }777 778 catchsql {INSERT INTO t2 SELECT * FROM t1}779 780 execsql { 781 COMMIT;782 PRAGMA integrity_check;783 }784} {ok}785 786#-------------------------------------------------------------------------787# At one point it was unsafe to truncate a db file on windows while there788# were outstanding xFetch() references. This test case attempts to hit789# that case.790#791ifcapable mmap {792 reset_db793 do_execsql_test incrvacuum-16.0 {794 PRAGMA auto_vacuum = 2;795 CREATE TABLE t3(a);796 INSERT INTO t3 VALUES(1), (2), (3), (4);797 798 CREATE TABLE t2(x);799 INSERT INTO t2 VALUES( randomblob(1000) );800 INSERT INTO t2 VALUES( randomblob(1000) );801 INSERT INTO t2 VALUES( randomblob(1000) );802 INSERT INTO t2 VALUES( randomblob(1000) );803 INSERT INTO t2 VALUES( randomblob(1000) );804 INSERT INTO t2 VALUES( randomblob(1000) );805 } {}806 807 # Reopen db to ensure the page-cache is empty.808 #809 db close810 sqlite3 db test.db811 812 # Open db in mmap-mode. Open a transaction, delete some data, then run813 # incremental-vacuum. Do not commit the transaction. 814 #815 do_execsql_test incrvacuum-16.1 {816 PRAGMA mmap_size = 1000000;817 BEGIN;818 DELETE FROM t2;819 PRAGMA incremental_vacuum = 1000;820 } {1000000}821 822 # Scan through table t3 (which is all clean pages - so mmap is used). Then,823 # midway through, commit the transaction. This causes the db to be truncated824 # while there are outstanding xFetch pages.825 #826 do_test incrvacuum-16.2 {827 set res [list]828 db eval { SELECT a FROM t3 } {829 if {$a==3} { db eval COMMIT }830 lappend res $a831 }832 set res833 } {1 2 3 4}834}835 836# 2021-04-05 dbsqlfuzz cced0668cfd4da4eb2382cb9dd26c17c64aaff76837#838# This is an incremental vacuum database that has one free page that839# needs to be filled. After removing the last page from the end of840# the database file to fill the free page slot, the last page that841# is left is the tail of an overflow chain.842#843# But the size of the database file is shorter than the actual data844# so that after incremental vacuum runs, the file is actually too845# small to hold the last page of the overflow chain.846#847# At one point this caused an assertion fault in 848# sqlite3PagerTruncateImage().849#850do_test incrvacuum-17.0 {851 sqlite3 db {}852 database_may_be_corrupt853 db deserialize [decode_hexdb {854| size 20480 pagesize 4096 filename x2.db855| page 1 offset 0856| 0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3.857| 16: 10 00 01 01 00 40 20 20 00 00 00 05 00 00 00 07 .....@ ........858| 32: 00 00 00 04 00 00 00 01 00 00 00 03 00 00 00 04 ................859| 48: 00 00 00 00 00 00 00 03 00 00 00 01 00 00 00 00 ................860| 64: 00 00 00 01 00 00 00 00 00 00 00 00 00 00 00 00 ................861| 80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 05 ................862| 96: 00 2e 53 60 0d 0f dc 00 01 0f b8 00 0f b8 0f b8 ..S`............863| 4016: 00 00 00 00 00 00 00 00 22 02 06 17 11 11 01 31 ...............1864| 4032: 74 61 62 6c 65 74 32 74 32 03 43 52 45 41 54 45 tablet2t2.CREATE865| 4048: 20 54 41 42 4c 45 20 74 32 28 79 29 00 00 00 24 TABLE t2(y)...$866| 4064: 11 11 01 31 74 61 62 6c 65 74 31 74 31 03 43 52 ...1tablet1t1.CR867| 4080: 45 41 54 45 20 54 41 42 4c 45 20 74 31 28 78 29 EATE TABLE t1(x)868| page 2 offset 4096869| 0: 01 00 00 00 00 02 00 00 00 00 03 00 00 00 03 04 ................870| 16: 00 00 00 05 03 00 00 00 03 00 00 00 00 00 00 00 ................871| page 3 offset 8192872| 0: 0d 00 00 00 02 05 47 00 08 dd 05 47 00 00 00 00 ......G....G....873| 1344: 00 00 00 00 00 00 00 a7 0b 02 03 ce 1c 00 00 00 ................874| 2256: 00 00 00 00 00 00 00 00 00 00 00 00 07 ce 14 01 ................875| 2272: 04 81 9c 2c 00 00 00 00 00 00 00 00 00 00 00 00 ...,............876| 4080: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 05 ................877| page 4 offset 12288878| 0: 00 00 00 00 00 00 00 00 08 dd 05 47 00 00 00 00 ...........G....879| 1344: 00 00 00 00 00 00 00 a7 0b 02 03 ce 1c 00 00 00 ................880| 2256: 00 00 00 00 00 00 00 00 00 00 00 00 07 ce 14 01 ................881| 2272: 04 81 9c 2c 00 00 00 00 00 00 00 00 00 00 00 00 ...,............882| 4080: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 05 ................883| page 5 offset 16384884| 0: 00 00 00 06 00 00 00 00 00 00 00 00 00 00 00 00 ................885| end x2.db886}]} {}887do_catchsql_test incrvacuum-17.1 {888 PRAGMA writable_schema=ON;889 PRAGMA incremental_vacuum(10);890} {0 {}}891 892finish_test893