I found a bug in latest (3.7.14.1) with a very specific #define that can causes
a database corruption after truncation because of missing backup pages.
If you specify this define:
#define SQLITE_OMIT_AUTOVACUUM
And then execute a VACUUM operation that shrinks the database, due to the
#ifndef at sqlite3.c:42830
The code that would otherwise proceed to backup pages before truncation will
not be executed.
Even the comment is wrong, it CAN and WILL happen for a normal vacuum too, not
just in auto vacuum.
/* If this transaction has made the database smaller, then all pages
** being discarded by the truncation must be written to the journal
** file. This can only happen in auto-vacuum mode.
**
** Before reading the pages with page numbers larger than the
** current value of Pager.dbSize, set dbSize back to the value
** that it took at the start of the transaction. Otherwise, the
** calls to sqlite3PagerGet() return zeroed pages instead of
** reading data from the database file.
*/
So here is my fix... (just removed the #ifndef)
/* If this transaction has made the database smaller, then all pages
** being discarded by the truncation must be written to the journal
** file. This can happen in auto-vacuum mode and during a normal
** vacuum operation.
**
** Before reading the pages with page numbers larger than the
** current value of Pager.dbSize, set dbSize back to the value
** that it took at the start of the transaction. Otherwise, the
** calls to sqlite3PagerGet() return zeroed pages instead of
** reading data from the database file.
*/
if( pPager->dbSize<pPager->dbOrigSize
&& pPager->journalMode!=PAGER_JOURNALMODE_OFF
){
Pgno i; /* Iterator variable */
const Pgno iSkip = PAGER_MJ_PGNO(pPager); /* Pending lock page */
const Pgno dbSize = pPager->dbSize; /* Database image size */
pPager->dbSize = pPager->dbOrigSize;
for( i=dbSize+1; i<=pPager->dbOrigSize; i++ ){
if( !sqlite3BitvecTest(pPager->pInJournal, i) && i!=iSkip ){
PgHdr *pPage; /* Page to journal */
rc = sqlite3PagerGet(pPager, i, &pPage);
if( rc!=SQLITE_OK ) goto commit_phase_one_exit;
rc = sqlite3PagerWrite(pPage);
sqlite3PagerUnref(pPage);
if( rc!=SQLITE_OK ) goto commit_phase_one_exit;
}
}
pPager->dbSize = dbSize;
}
You can test it with a really simple application that I included in
attachment...
You just have to put a breakpoint at sqlite3.c:56747 and step over it and then
restart the app right there.
The next integrity_check will fail completely :)
Thanks
Danny Couture
Technical Architect
Ubisoft Montreal
#include "stdafx.h"
//DON'T FORGET TO COMPILE SQLITE WITH #define SQLITE_OMIT_AUTOVACUUM
#include "sqlite3.h"
int callback(void *, int argc, char ** argv, char ** x)
{
for (int i = 0; i < argc; ++i)
printf("%s, ", argv[i]);
printf("\n");
return 0;
}
int _tmain(int argc, _TCHAR* argv[])
{
sqlite3 * db;
sqlite3_open("test.db", &db);
char * errorMsg;
sqlite3_exec(db, "PRAGMA integrity_check", callback, 0, &errorMsg);
sqlite3_exec(db, "CREATE TABLE test (Key INT, Test DOUBLE, Text
VARCHAR(1024))", callback, 0, &errorMsg);
sqlite3_exec(db, "BEGIN", callback, 0, &errorMsg);
//add some stuff
char query[1024];
for (int i = 0; i < 100000; ++i)
{
sprintf_s(query, "INSERT INTO test VALUES(%d, %d.5, \"%d\")", i, i, i);
sqlite3_exec(db, query, callback, 0, &errorMsg);
}
sqlite3_exec(db, "COMMIT", callback, 0, &errorMsg);
//remove some of the stuff so the vacuum shrinks the DB
sqlite3_exec(db, "DELETE FROM test WHERE Key > 5000", callback, 0,
&errorMsg);
//add a breakpoint at sqlite3.c:58390 and restart the application right
there.
//the next integrity_check will fail completely...
sqlite3_exec(db, "VACUUM", callback, 0, &errorMsg);
return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
