Re: [sqlite] Problem with VACUUM feature

[Jay A. Kreibich]

On Mon, Mar 28, 2011 at 08:55:28PM +0530, Sudha Venkatareddy scratched on the 
wall:
> Dear Jay,

> 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().

  I'm not on the development team.  I don't know the internal code all
  that well.  Somebody else will have to help you out.

   -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

Re: [sqlite] Problem with VACUUM feature

[Sudha Venkatareddy]

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 = >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**), );
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;
  

Re: [sqlite] Problem with VACUUM feature

[Sudha Venkatareddy]

Hi,

As per the link http://www.sqlite.org/lang_vacuum.html , SQLite claims that
"*The VACUUM command works by copying the contents of the database into a
temporary database file and then overwriting the original with the contents
of the temporary file*."  In this phrase "*The VACUUM command works by
copying the contents of the database into a temporary database file*" seem
to be working fine where as " *then overwriting the original with the
contents of the temporary file*" is not.

As i tested, sqlite creates temp file but it does not overwrite the original
file with temp file content.

I am using sqlite-amalgamation-3_7_3.zip with WIN32 configuration.

Please help me to find the solution for this problem.

Thanks,
Sudha

On Thu, Feb 24, 2011 at 3:04 PM, Sudha Venkatareddy wrote:

> Hi,
>
> I fixed the code in such a way that the  temp files which are created with
> below flags are deleted during close.
>
> dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY
>| FILE_ATTRIBUTE_HIDDEN
>| FILE_FLAG_DELETE_ON_CLOSE;
>
>
> so..
>
>  * Problem 1. *Running VACUUM leaves 3 temporary files in the temp
> directory
>  which are not deleted when main DB is closed.* - Fixed*
>
>  *Problem 2.* Upon applying VACUUM command on say main DB file MyDb.db ,
> and
>  closing the main DB connection, the size of the main DB file MyDb.db does
>  not change where as one of the temp file(etilqs_*) will actually contain
> the
>  reduced size of the same data as of main DB file. - *Not Fixed
>
> *Here i am attaching the MyDb.db as well as temp file "etilqs_*" which
> gets created while running VACUUM command. Its content is same as MainDb.db
> except the file size.
> Expected output: content and file size of Mydb.db should be same as
> etilqs_Hm4RUi6JPXcMZ17 and file name should be remained as MyDb.db.
>
> Please help me in fixing this problem.
>
> Thanks,
> Sudha
>
>
> On Thu, Feb 24, 2011 at 11:50 AM, Sudha Venkatareddy 
> wrote:
>
>> Hi,
>>
>> The porting is done in little tricky way due to limited support from
>> underlying platform.
>>
>> Ported code does not completely follow WINDOWS. it is WINDOWS + WINCE
>> configuration.
>>
>> Porting is done as below.
>>
>> *Step 1.*  Main macros defnined in the source include:
>>
>> #define SQLITE_DEBUG 0
>> #define SQLITE_OS_OTHER 1
>> #define SQLITE_MUTEX_OTHER_OS 1
>> #define SQLITE_CORE 1
>> #define SQLITE_AMALGAMATION 1
>> SQLITE_OS_OTHER  is same as SQLITE_OS_WIN
>>
>> *Step 2.* SQLITE_OS_WINCE is not defined but isNT() is defined as 1.
>> #define isNT() 1
>>
>> Here is the code snippet of otherOsClose() API for your reference.
>> 
>> #define MX_CLOSE_ATTEMPT 3
>> static int otherOsClose(sqlite3_file *id){
>>   int rc, cnt = 0;
>>   otherOsFile *pFile = (otherOsFile*)id;
>>   assert( id!=0 );
>>   assert( pFile->pShm==0 );
>>   OSTRACE2("CLOSE %d\n", pFile->h);
>>   do{
>> rc = CloseHandle(pFile->h);
>> /* SimulateIOError( rc=0; cnt=MX_CLOSE_ATTEMPT; ); */
>>   }while( rc==0 && ++cnt < MX_CLOSE_ATTEMPT && (Sleep(100), 1) );
>> #if SQLITE_OS_WINCE
>> #define WINCE_DELETION_ATTEMPTS 3
>>   winceDestroyLock(pFile);
>>   if( pFile->zDeleteOnClose ){
>> int cnt = 0;
>> while(
>>DeleteFileW(pFile->zDeleteOnClose)==0
>> && GetFileAttributesW(pFile->zDeleteOnClose)!=0x
>> && cnt++ < WINCE_DELETION_ATTEMPTS
>> ){
>>Sleep(100);  /* Wait a little before trying again */
>> }
>> free(pFile->zDeleteOnClose);
>>   }
>> #endif
>>   OSTRACE3("CLOSE %d %s\n", pFile->h, rc ? "ok" : "failed");
>>   OpenCounter(-1);
>>   return rc ? SQLITE_OK : SQLITE_IOERR;
>> }
>> 
>>
>> With configuration steps 1 and 2 as mentioned above, will there be any
>> problems?
>>
>> Since SQLITE_OS_WINCE is not defined as 1, the file will not be deleted on
>> close. I can make some hack to enable pFile->zDeleteOnClose and modify the
>> ported code to delete the file if pFile->zDeleteOnClose is true without
>> actually defining macro SQLITE_OS_WINCE.
>>
>> SQLITE_OS_WINCE  can not be enabled since it requires winceLocks to be
>> implemented which can not be supported at the moment.
>>
>>
>> Thanks,
>> Sudha
>>
>> On Wed, Feb 23, 2011 at 8:14 PM, Shane Harrelson wrote:
>>
>>> Hi-
>>>
>>> On Windows, SQLite uses the FILE_FLAG_DELETE_ON_CLOSE flag to have
>>> temporary files automatically deleted after they are closed.  WINCE
>>> doesn't support this flag, so you will see special logic in os_win.c,
>>> wrapped in #ifdef SQLITE_OS_WINCE, for handling the deletion of these
>>> files.  You mentioned in an earlier post that you had ported to your
>>> platform based on this code.   Could you check that your ported code
>>> includes this logic?
>>>
>>> -Shane
>>>
>>> On Wed, Feb 23, 2011 at 9:00 AM, Sudha Venkatareddy 

Re: [sqlite] Problem with VACUUM feature

[Sudha Venkatareddy]

Hi,

I fixed the code in such a way that the  temp files which are created with
below flags are deleted during close.

dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY
   | FILE_ATTRIBUTE_HIDDEN
   | FILE_FLAG_DELETE_ON_CLOSE;


so..

* Problem 1. *Running VACUUM leaves 3 temporary files in the temp directory
 which are not deleted when main DB is closed.* - Fixed*

 *Problem 2.* Upon applying VACUUM command on say main DB file MyDb.db , and
 closing the main DB connection, the size of the main DB file MyDb.db does
 not change where as one of the temp file(etilqs_*) will actually contain
the
 reduced size of the same data as of main DB file. - *Not Fixed

*Here i am attaching the MyDb.db as well as temp file "etilqs_*" which gets
created while running VACUUM command. Its content is same as MainDb.db
except the file size.
Expected output: content and file size of Mydb.db should be same as
etilqs_Hm4RUi6JPXcMZ17 and file name should be remained as MyDb.db.

Please help me in fixing this problem.

Thanks,
Sudha


On Thu, Feb 24, 2011 at 11:50 AM, Sudha Venkatareddy wrote:

> Hi,
>
> The porting is done in little tricky way due to limited support from
> underlying platform.
>
> Ported code does not completely follow WINDOWS. it is WINDOWS + WINCE
> configuration.
>
> Porting is done as below.
>
> *Step 1.*  Main macros defnined in the source include:
>
> #define SQLITE_DEBUG 0
> #define SQLITE_OS_OTHER 1
> #define SQLITE_MUTEX_OTHER_OS 1
> #define SQLITE_CORE 1
> #define SQLITE_AMALGAMATION 1
> SQLITE_OS_OTHER  is same as SQLITE_OS_WIN
>
> *Step 2.* SQLITE_OS_WINCE is not defined but isNT() is defined as 1.
> #define isNT() 1
>
> Here is the code snippet of otherOsClose() API for your reference.
> 
> #define MX_CLOSE_ATTEMPT 3
> static int otherOsClose(sqlite3_file *id){
>   int rc, cnt = 0;
>   otherOsFile *pFile = (otherOsFile*)id;
>   assert( id!=0 );
>   assert( pFile->pShm==0 );
>   OSTRACE2("CLOSE %d\n", pFile->h);
>   do{
> rc = CloseHandle(pFile->h);
> /* SimulateIOError( rc=0; cnt=MX_CLOSE_ATTEMPT; ); */
>   }while( rc==0 && ++cnt < MX_CLOSE_ATTEMPT && (Sleep(100), 1) );
> #if SQLITE_OS_WINCE
> #define WINCE_DELETION_ATTEMPTS 3
>   winceDestroyLock(pFile);
>   if( pFile->zDeleteOnClose ){
> int cnt = 0;
> while(
>DeleteFileW(pFile->zDeleteOnClose)==0
> && GetFileAttributesW(pFile->zDeleteOnClose)!=0x
> && cnt++ < WINCE_DELETION_ATTEMPTS
> ){
>Sleep(100);  /* Wait a little before trying again */
> }
> free(pFile->zDeleteOnClose);
>   }
> #endif
>   OSTRACE3("CLOSE %d %s\n", pFile->h, rc ? "ok" : "failed");
>   OpenCounter(-1);
>   return rc ? SQLITE_OK : SQLITE_IOERR;
> }
> 
>
> With configuration steps 1 and 2 as mentioned above, will there be any
> problems?
>
> Since SQLITE_OS_WINCE is not defined as 1, the file will not be deleted on
> close. I can make some hack to enable pFile->zDeleteOnClose and modify the
> ported code to delete the file if pFile->zDeleteOnClose is true without
> actually defining macro SQLITE_OS_WINCE.
>
> SQLITE_OS_WINCE  can not be enabled since it requires winceLocks to be
> implemented which can not be supported at the moment.
>
>
> Thanks,
> Sudha
>
> On Wed, Feb 23, 2011 at 8:14 PM, Shane Harrelson  wrote:
>
>> Hi-
>>
>> On Windows, SQLite uses the FILE_FLAG_DELETE_ON_CLOSE flag to have
>> temporary files automatically deleted after they are closed.  WINCE
>> doesn't support this flag, so you will see special logic in os_win.c,
>> wrapped in #ifdef SQLITE_OS_WINCE, for handling the deletion of these
>> files.  You mentioned in an earlier post that you had ported to your
>> platform based on this code.   Could you check that your ported code
>> includes this logic?
>>
>> -Shane
>>
>> On Wed, Feb 23, 2011 at 9:00 AM, Sudha Venkatareddy 
>> wrote:
>> > Hi,
>> >
>> > I referred the link http://www.sqlite.org/cvstrac/tktview?tn=2829
>> > it is slightly related to it but the temporary files are created while
>> > running VACUUM command.
>> > ---
>> > Ticket 2829:
>> >
>> > This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):
>> >
>> >   if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
>> > -| SQLITE_OPEN_SUBJOURNAL) ){
>> > +| SQLITE_OPEN_SUBJOURNAL |
>> SQLITE_OPEN_DELETEONCLOSE) ){
>> >
>> >
>> --
>> >
>> > The temp files were created in the below call sequence:
>> >
>> > -
>> >  62 otherOsOpen() sqlite3.c:123900 0x3afe25bd
>> >  61 sqlite3OsOpen() sqlite3.c:15280 0x3af550d0
>> >  60 pagerOpentemp() sqlite3.c:39431 

Re: [sqlite] Problem with VACUUM feature

[Sudha Venkatareddy]

Hi,

The porting is done in little tricky way due to limited support from
underlying platform.

Ported code does not completely follow WINDOWS. it is WINDOWS + WINCE
configuration.

Porting is done as below.

*Step 1.*  Main macros defnined in the source include:

#define SQLITE_DEBUG 0
#define SQLITE_OS_OTHER 1
#define SQLITE_MUTEX_OTHER_OS 1
#define SQLITE_CORE 1
#define SQLITE_AMALGAMATION 1
SQLITE_OS_OTHER  is same as SQLITE_OS_WIN

*Step 2.* SQLITE_OS_WINCE is not defined but isNT() is defined as 1.
#define isNT() 1

Here is the code snippet of otherOsClose() API for your reference.

#define MX_CLOSE_ATTEMPT 3
static int otherOsClose(sqlite3_file *id){
  int rc, cnt = 0;
  otherOsFile *pFile = (otherOsFile*)id;
  assert( id!=0 );
  assert( pFile->pShm==0 );
  OSTRACE2("CLOSE %d\n", pFile->h);
  do{
rc = CloseHandle(pFile->h);
/* SimulateIOError( rc=0; cnt=MX_CLOSE_ATTEMPT; ); */
  }while( rc==0 && ++cnt < MX_CLOSE_ATTEMPT && (Sleep(100), 1) );
#if SQLITE_OS_WINCE
#define WINCE_DELETION_ATTEMPTS 3
  winceDestroyLock(pFile);
  if( pFile->zDeleteOnClose ){
int cnt = 0;
while(
   DeleteFileW(pFile->zDeleteOnClose)==0
&& GetFileAttributesW(pFile->zDeleteOnClose)!=0x
&& cnt++ < WINCE_DELETION_ATTEMPTS
){
   Sleep(100);  /* Wait a little before trying again */
}
free(pFile->zDeleteOnClose);
  }
#endif
  OSTRACE3("CLOSE %d %s\n", pFile->h, rc ? "ok" : "failed");
  OpenCounter(-1);
  return rc ? SQLITE_OK : SQLITE_IOERR;
}


With configuration steps 1 and 2 as mentioned above, will there be any
problems?

Since SQLITE_OS_WINCE is not defined as 1, the file will not be deleted on
close. I can make some hack to enable pFile->zDeleteOnClose and modify the
ported code to delete the file if pFile->zDeleteOnClose is true without
actually defining macro SQLITE_OS_WINCE.

SQLITE_OS_WINCE  can not be enabled since it requires winceLocks to be
implemented which can not be supported at the moment.


Thanks,
Sudha

On Wed, Feb 23, 2011 at 8:14 PM, Shane Harrelson  wrote:

> Hi-
>
> On Windows, SQLite uses the FILE_FLAG_DELETE_ON_CLOSE flag to have
> temporary files automatically deleted after they are closed.  WINCE
> doesn't support this flag, so you will see special logic in os_win.c,
> wrapped in #ifdef SQLITE_OS_WINCE, for handling the deletion of these
> files.  You mentioned in an earlier post that you had ported to your
> platform based on this code.   Could you check that your ported code
> includes this logic?
>
> -Shane
>
> On Wed, Feb 23, 2011 at 9:00 AM, Sudha Venkatareddy 
> wrote:
> > Hi,
> >
> > I referred the link http://www.sqlite.org/cvstrac/tktview?tn=2829
> > it is slightly related to it but the temporary files are created while
> > running VACUUM command.
> > ---
> > Ticket 2829:
> >
> > This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):
> >
> >   if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
> > -| SQLITE_OPEN_SUBJOURNAL) ){
> > +| SQLITE_OPEN_SUBJOURNAL |
> SQLITE_OPEN_DELETEONCLOSE) ){
> >
> >
> --
> >
> > The temp files were created in the below call sequence:
> >
> > -
> >  62 otherOsOpen() sqlite3.c:123900 0x3afe25bd
> >  61 sqlite3OsOpen() sqlite3.c:15280 0x3af550d0
> >  60 pagerOpentemp() sqlite3.c:39431 0x3af62e70
> >  59 pager_write_pagelist() sqlite3.c:40030 0x3af63a68
> >  58 sqlite3PagerCommitPhaseOne() sqlite3.c:41884 0x3af669ff
> >  57 sqlite3BtreeCommitPhaseOne() sqlite3.c:48993 0x3af72665
> >  56 sqlite3BtreeCommit() sqlite3.c:49083 0x3af728e6
> >  55 sqlite3RunVacuum() sqlite3.c:94735 0x3afcce29
> >  54 sqlite3VdbeExec() sqlite3.c:66384 0x3af961e4
> >  53 sqlite3Step() sqlite3.c:59380 0x3af87b34
> >  52 sqlite3_step() sqlite3.c:59444 0x3af87d6e
> >  51 sqlite3_exec() sqlite3.c:84701 0x3afb86b9
> > --
> >
> >
> >
> > Basically there 2 problems associated when i run VACUUM command.
> > Problem 1. Running VACUUM leaves 3 temporary files in the temp directory
> > which are not deleted when main DB is closed.
> > Problem 2. Upon applying VACUUM command on say main DB file MyDb.db , and
> > closing the main DB connection, the size of the main DB file MyDb.db does
> > not change where as one of the temp file(etilqs_*) will actually contain
> the
> > reduced size of the same data as of main DB file.
> >
> > I am not sure if this is the expected behaviour or there is some bug in
> the
> > flow.
> >
> > Please let me know if there is a solution to resolve this issue.
> >
> > Thanks,
> > Sudha
> >
> > On Wed, Feb 23, 2011 at 5:52 PM, Simon Slavin 

Re: [sqlite] Problem with VACUUM feature

[Sven L]

Make sure your antivirus is turned off when you run your vacuum test.
It's a long-shot, but I've seen some AVs lock files etc...

 
> Date: Wed, 23 Feb 2011 10:14:15 -0600
> From: j...@kreibi.ch
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problem with VACUUM feature
> 
> 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

Re: [sqlite] Problem with VACUUM feature

[Jay A. Kreibich]

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

Re: [sqlite] Problem with VACUUM feature

[Shane Harrelson]

Hi-

On Windows, SQLite uses the FILE_FLAG_DELETE_ON_CLOSE flag to have
temporary files automatically deleted after they are closed.  WINCE
doesn't support this flag, so you will see special logic in os_win.c,
wrapped in #ifdef SQLITE_OS_WINCE, for handling the deletion of these
files.  You mentioned in an earlier post that you had ported to your
platform based on this code.   Could you check that your ported code
includes this logic?

-Shane

On Wed, Feb 23, 2011 at 9:00 AM, Sudha Venkatareddy  wrote:
> Hi,
>
> I referred the link http://www.sqlite.org/cvstrac/tktview?tn=2829
> it is slightly related to it but the temporary files are created while
> running VACUUM command.
> ---
> Ticket 2829:
>
> This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):
>
>   if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
> -                    | SQLITE_OPEN_SUBJOURNAL) ){
> +                    | SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_DELETEONCLOSE) ){
>
> --
>
> The temp files were created in the below call sequence:
>
> -
>  62 otherOsOpen() sqlite3.c:123900 0x3afe25bd
>  61 sqlite3OsOpen() sqlite3.c:15280 0x3af550d0
>  60 pagerOpentemp() sqlite3.c:39431 0x3af62e70
>  59 pager_write_pagelist() sqlite3.c:40030 0x3af63a68
>  58 sqlite3PagerCommitPhaseOne() sqlite3.c:41884 0x3af669ff
>  57 sqlite3BtreeCommitPhaseOne() sqlite3.c:48993 0x3af72665
>  56 sqlite3BtreeCommit() sqlite3.c:49083 0x3af728e6
>  55 sqlite3RunVacuum() sqlite3.c:94735 0x3afcce29
>  54 sqlite3VdbeExec() sqlite3.c:66384 0x3af961e4
>  53 sqlite3Step() sqlite3.c:59380 0x3af87b34
>  52 sqlite3_step() sqlite3.c:59444 0x3af87d6e
>  51 sqlite3_exec() sqlite3.c:84701 0x3afb86b9
> --
>
>
>
> Basically there 2 problems associated when i run VACUUM command.
> Problem 1. Running VACUUM leaves 3 temporary files in the temp directory
> which are not deleted when main DB is closed.
> Problem 2. Upon applying VACUUM command on say main DB file MyDb.db , and
> closing the main DB connection, the size of the main DB file MyDb.db does
> not change where as one of the temp file(etilqs_*) will actually contain the
> reduced size of the same data as of main DB file.
>
> I am not sure if this is the expected behaviour or there is some bug in the
> flow.
>
> Please let me know if there is a solution to resolve this issue.
>
> Thanks,
> Sudha
>
> On Wed, Feb 23, 2011 at 5:52 PM, Simon Slavin  wrote:
>
>>
>> On 23 Feb 2011, at 6:11am, Sudha Venkatareddy wrote:
>>
>> > *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*
>>
>> Your problem is probably related to
>>
>> http://www.sqlite.org/cvstrac/tktview?tn=2829
>>
>> .  It's quite legitimate for your symptoms to occur while the database
>> handle is still open but you should not be seeing those files after you have
>> closed the connection to the database.  Either you are not closing the
>> database connection properly, or some part of the API you're using is not
>> closing the database connection properly.
>>
>> I'm not familiar with how this problem manifests because I don't use
>> Windows, so I'll leave it up to an expert to tell you if it needs fixing
>> somehow.
>>
>> Simon.
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with VACUUM feature

[Sudha Venkatareddy]

Hi,

I referred the link http://www.sqlite.org/cvstrac/tktview?tn=2829
it is slightly related to it but the temporary files are created while
running VACUUM command.
---
Ticket 2829:

This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE):

   if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL
-| SQLITE_OPEN_SUBJOURNAL) ){
+| SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_DELETEONCLOSE) ){

--

The temp files were created in the below call sequence:

-
 62 otherOsOpen() sqlite3.c:123900 0x3afe25bd
 61 sqlite3OsOpen() sqlite3.c:15280 0x3af550d0
 60 pagerOpentemp() sqlite3.c:39431 0x3af62e70
 59 pager_write_pagelist() sqlite3.c:40030 0x3af63a68
 58 sqlite3PagerCommitPhaseOne() sqlite3.c:41884 0x3af669ff
 57 sqlite3BtreeCommitPhaseOne() sqlite3.c:48993 0x3af72665
 56 sqlite3BtreeCommit() sqlite3.c:49083 0x3af728e6
 55 sqlite3RunVacuum() sqlite3.c:94735 0x3afcce29
 54 sqlite3VdbeExec() sqlite3.c:66384 0x3af961e4
 53 sqlite3Step() sqlite3.c:59380 0x3af87b34
 52 sqlite3_step() sqlite3.c:59444 0x3af87d6e
 51 sqlite3_exec() sqlite3.c:84701 0x3afb86b9
--



Basically there 2 problems associated when i run VACUUM command.
Problem 1. Running VACUUM leaves 3 temporary files in the temp directory
which are not deleted when main DB is closed.
Problem 2. Upon applying VACUUM command on say main DB file MyDb.db , and
closing the main DB connection, the size of the main DB file MyDb.db does
not change where as one of the temp file(etilqs_*) will actually contain the
reduced size of the same data as of main DB file.

I am not sure if this is the expected behaviour or there is some bug in the
flow.

Please let me know if there is a solution to resolve this issue.

Thanks,
Sudha

On Wed, Feb 23, 2011 at 5:52 PM, Simon Slavin  wrote:

>
> On 23 Feb 2011, at 6:11am, Sudha Venkatareddy wrote:
>
> > *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*
>
> Your problem is probably related to
>
> http://www.sqlite.org/cvstrac/tktview?tn=2829
>
> .  It's quite legitimate for your symptoms to occur while the database
> handle is still open but you should not be seeing those files after you have
> closed the connection to the database.  Either you are not closing the
> database connection properly, or some part of the API you're using is not
> closing the database connection properly.
>
> I'm not familiar with how this problem manifests because I don't use
> Windows, so I'll leave it up to an expert to tell you if it needs fixing
> somehow.
>
> Simon.
> ___
> 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

Re: [sqlite] Problem with VACUUM feature

[Simon Slavin]

On 23 Feb 2011, at 6:11am, Sudha Venkatareddy wrote:

> *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*

Your problem is probably related to

http://www.sqlite.org/cvstrac/tktview?tn=2829

.  It's quite legitimate for your symptoms to occur while the database handle 
is still open but you should not be seeing those files after you have closed 
the connection to the database.  Either you are not closing the database 
connection properly, or some part of the API you're using is not closing the 
database connection properly.

I'm not familiar with how this problem manifests because I don't use Windows, 
so I'll leave it up to an expert to tell you if it needs fixing somehow.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users