Dear Jay,
In the course of execution of Sqlite3RunVacuum() API, total 4 new files will
be created. 3 temporary files and 1 journal file.
Since my main DB file name is "MyDb.db", the journal file which is created
is "MyDb.db-journal"
I observed that there was no failure in sqlite3_io_methods API set.
Please check the below source code from Amalgamation file and suggest me
which section of the code is critical and what happens during execution of
each of the APis called within Sqlire3RunVacuum().
This is the source code of function Sqlite3RunVacuum() APi
===================================================================
SQLITE_PRIVATE int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){
int rc = SQLITE_OK; /* Return code from service routines */
Btree *pMain; /* The database being vacuumed */
Btree *pTemp; /* The temporary database we vacuum into */
char *zSql = 0; /* SQL statements */
int saved_flags; /* Saved value of the db->flags */
int saved_nChange; /* Saved value of db->nChange */
int saved_nTotalChange; /* Saved value of db->nTotalChange */
void (*saved_xTrace)(void*,const char*); /* Saved db->xTrace */
Db *pDb = 0; /* Database to detach at end of vacuum */
int isMemDb; /* True if vacuuming a :memory: database */
int nRes; /* Bytes of reserved space at the end of each page
*/
int nDb; /* Number of attached databases */
if( !db->autoCommit ){
sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a
transaction");
return SQLITE_ERROR;
}
if( db->activeVdbeCnt>1 ){
sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in
progress");
return SQLITE_ERROR;
}
/* Save the current value of the database flags so that it can be
** restored before returning. Then set the writable-schema flag, and
** disable CHECK and foreign key constraints. */
saved_flags = db->flags;
saved_nChange = db->nChange;
saved_nTotalChange = db->nTotalChange;
saved_xTrace = db->xTrace;
db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks |
SQLITE_PreferBuiltin;
db->flags &= ~(SQLITE_ForeignKeys | SQLITE_ReverseOrder);
db->xTrace = 0;
pMain = db->aDb[0].pBt;
isMemDb = sqlite3PagerIsMemdb(sqlite3BtreePager(pMain));
/* Attach the temporary database as 'vacuum_db'. The synchronous pragma
** can be set to 'off' for this file, as it is not recovered if a crash
** occurs anyway. The integrity of the database is maintained by a
** (possibly synchronous) transaction opened on the main database before
** sqlite3BtreeCopyFile() is called.
**
** An optimisation would be to use a non-journaled pager.
** (Later:) I tried setting "PRAGMA vacuum_db.journal_mode=OFF" but
** that actually made the VACUUM run slower. Very little journalling
** actually occurs when doing a vacuum since the vacuum_db is initially
** empty. Only the journal header is written. Apparently it takes more
** time to parse and run the PRAGMA to turn journalling off than it does
** to write the journal header file.
*/
nDb = db->nDb;
if( sqlite3TempInMemory(db) ){
zSql = "ATTACH ':memory:' AS vacuum_db;";
}else{
zSql = "ATTACH '' AS vacuum_db;";
}
rc = execSql(db, pzErrMsg, zSql);
if( db->nDb>nDb ){
pDb = &db->aDb[db->nDb-1];
assert( strcmp(pDb->zName,"vacuum_db")==0 );
}
if( rc!=SQLITE_OK ) goto end_of_vacuum;
pTemp = db->aDb[db->nDb-1].pBt;
/* The call to execSql() to attach the temp database has left the file
** locked (as there was more than one active statement when the
transaction
** to read the schema was concluded. Unlock it here so that this doesn't
** cause problems for the call to BtreeSetPageSize() below. */
sqlite3BtreeCommit(pTemp);
nRes = sqlite3BtreeGetReserve(pMain);
/* A VACUUM cannot change the pagesize of an encrypted database. */
#ifdef SQLITE_HAS_CODEC
if( db->nextPagesize ){
extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*);
int nKey;
char *zKey;
sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey);
if( nKey ) db->nextPagesize = 0;
}
#endif
/* Do not attempt to change the page size for a WAL database */
if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
==PAGER_JOURNALMODE_WAL
){
db->nextPagesize = 0;
}
if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes,
0)
|| (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes,
0))
|| NEVER(db->mallocFailed)
){
rc = SQLITE_NOMEM;
goto end_of_vacuum;
}
rc = execSql(db, pzErrMsg, "PRAGMA vacuum_db.synchronous=OFF");
if( rc!=SQLITE_OK ){
goto end_of_vacuum;
}
#ifndef SQLITE_OMIT_AUTOVACUUM
sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
sqlite3BtreeGetAutoVacuum(pMain));
#endif
/* Begin a transaction */
rc = execSql(db, pzErrMsg, "BEGIN EXCLUSIVE;");
if( rc!=SQLITE_OK ) goto end_of_vacuum;
/* Query the schema of the main database. Create a mirror schema
** in the temporary database.
*/
rc = execExecSql(db, pzErrMsg,
"SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14) "
" FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"
" AND rootpage>0"
);
if( rc!=SQLITE_OK ) goto end_of_vacuum;
rc = execExecSql(db, pzErrMsg,
"SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14)"
" FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");
if( rc!=SQLITE_OK ) goto end_of_vacuum;
rc = execExecSql(db, pzErrMsg,
"SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21) "
" FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");
if( rc!=SQLITE_OK ) goto end_of_vacuum;
/* Loop through the tables in the main database. For each, do
** an "INSERT INTO vacuum_db.xxx SELECT * FROM main.xxx;" to copy
** the contents to the temporary database.
*/
rc = execExecSql(db, pzErrMsg,
"SELECT 'INSERT INTO vacuum_db.' || quote(name) "
"|| ' SELECT * FROM main.' || quote(name) || ';'"
"FROM main.sqlite_master "
"WHERE type = 'table' AND name!='sqlite_sequence' "
" AND rootpage>0"
);
if( rc!=SQLITE_OK ) goto end_of_vacuum;
/* Copy over the sequence table
*/
rc = execExecSql(db, pzErrMsg,
"SELECT 'DELETE FROM vacuum_db.' || quote(name) || ';' "
"FROM vacuum_db.sqlite_master WHERE name='sqlite_sequence' "
);
if( rc!=SQLITE_OK ) goto end_of_vacuum;
rc = execExecSql(db, pzErrMsg,
"SELECT 'INSERT INTO vacuum_db.' || quote(name) "
"|| ' SELECT * FROM main.' || quote(name) || ';' "
"FROM vacuum_db.sqlite_master WHERE name=='sqlite_sequence';"
);
if( rc!=SQLITE_OK ) goto end_of_vacuum;
/* Copy the triggers, views, and virtual tables from the main database
** over to the temporary database. None of these objects has any
** associated storage, so all we have to do is copy their entries
** from the SQLITE_MASTER table.
*/
rc = execSql(db, pzErrMsg,
"INSERT INTO vacuum_db.sqlite_master "
" SELECT type, name, tbl_name, rootpage, sql"
" FROM main.sqlite_master"
" WHERE type='view' OR type='trigger'"
" OR (type='table' AND rootpage=0)"
);
if( rc ) goto end_of_vacuum;
/* At this point, unless the main db was completely empty, there is now a
** transaction open on the vacuum database, but not on the main database.
** Open a btree level transaction on the main database. This allows a
** call to sqlite3BtreeCopyFile(). The main database btree level
** transaction is then committed, so the SQL level never knows it was
** opened for writing. This way, the SQL transaction used to create the
** temporary database never needs to be committed.
*/
{
u32 meta;
int i;
/* This array determines which meta meta values are preserved in the
** vacuum. Even entries are the meta value number and odd entries
** are an increment to apply to the meta value after the vacuum.
** The increment is used to increase the schema cookie so that other
** connections to the same database will know to reread the schema.
*/
static const unsigned char aCopy[] = {
BTREE_SCHEMA_VERSION, 1, /* Add one to the old schema cookie */
BTREE_DEFAULT_CACHE_SIZE, 0, /* Preserve the default page cache size
*/
BTREE_TEXT_ENCODING, 0, /* Preserve the text encoding */
BTREE_USER_VERSION, 0, /* Preserve the user version */
};
assert( 1==sqlite3BtreeIsInTrans(pTemp) );
assert( 1==sqlite3BtreeIsInTrans(pMain) );
/* Copy Btree meta values */
for(i=0; i<ArraySize(aCopy); i+=2){
/* GetMeta() and UpdateMeta() cannot fail in this context because
** we already have page 1 loaded into cache and marked dirty. */
sqlite3BtreeGetMeta(pMain, aCopy[i], &meta);
rc = sqlite3BtreeUpdateMeta(pTemp, aCopy[i], meta+aCopy[i+1]);
if( NEVER(rc!=SQLITE_OK) ) goto end_of_vacuum;
}
rc = sqlite3BtreeCopyFile(pMain, pTemp);
if( rc!=SQLITE_OK ) goto end_of_vacuum;
rc = sqlite3BtreeCommit(pTemp);
if( rc!=SQLITE_OK ) goto end_of_vacuum;
#ifndef SQLITE_OMIT_AUTOVACUUM
sqlite3BtreeSetAutoVacuum(pMain, sqlite3BtreeGetAutoVacuum(pTemp));
#endif
}
assert( rc==SQLITE_OK );
rc = sqlite3BtreeSetPageSize(pMain, sqlite3BtreeGetPageSize(pTemp),
nRes,1);
end_of_vacuum:
/* Restore the original value of db->flags */
db->flags = saved_flags;
db->nChange = saved_nChange;
db->nTotalChange = saved_nTotalChange;
db->xTrace = saved_xTrace;
sqlite3BtreeSetPageSize(pMain, -1, -1, 1);
/* Currently there is an SQL level transaction open on the vacuum
** database. No locks are held on any other files (since the main file
** was committed at the btree level). So it safe to end the transaction
** by manually setting the autoCommit flag to true and detaching the
** vacuum database. The vacuum_db journal file is deleted when the pager
** is closed by the DETACH.
*/
db->autoCommit = 1;
if( pDb ){
sqlite3BtreeClose(pDb->pBt);
pDb->pBt = 0;
pDb->pSchema = 0;
}
sqlite3ResetInternalSchema(db, 0);
return rc;
}
#endif /* SQLITE_OMIT_VACUUM && SQLITE_OMIT_ATTACH */
===============================================================================
On Wed, Feb 23, 2011 at 9:44 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:
> On Wed, Feb 23, 2011 at 11:50:05AM +0530, Sudha Venkatareddy scratched on
> the wall:
> > Hi All,
> >
> > Input:MyDb.db with size 23KB (has lot of empty pages caused due to delete
> > operation)
> >
> > *Expected OutPut: after applying Vacuum command, should be MyDb.db with
> > reduced file size of 13KB.*
> >
> > *Actual output: MyDb.db remains size 23KB(size not changes from original)
> > and creates temporary file etilqs_Hm4RUi6JPXcMZ17 whose data is same as
> > MyDb.db but the size is reduced to 13KB*
>
> VACUUM is a two step process. First, the data is copied from the
> original file to a temp file. This is a high-level copy, where the
> data is compacted and reordered, and free pages are eliminated.
>
> The second step copies the data from the temp file back to the
> primary file. This is done as a low-level page-by-page copy. It is
> *not* an OS file copy. By using the page update system already built
> into SQLite, the copy-back will create a rollback journal and remain
> transaction-safe for the whole VACUUM process.
>
> From the sound of things, the first step is working, but the second
> step is failing for some reason. My first guess would be that there
> are permissions issues with creating the rollback file, so the second
> copy process fails. That's just a guess, however, as there could be
> a number of other issues. If you can figure out if a rollback file
> is ever being created, that would help determine if the copy-back is
> starting, but fails for some reason, or if the copy-back step is
> failing right from the start. Given the small database size, it
> might be somewhat hard to figure that out, however-- any rollback is
> going to be there and gone (or not there at all) very quickly.
>
> -j
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
