[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    [sqlite] Corruption of incremental_vacuum databases
From:       Filip Navara <xnavara () volny ! cz>
Date:       2009-03-31 9:05:39
Message-ID: 5b31733c0903310205x3d79bfdbl1f47bfe409d9c95f () mail ! gmail ! com
[Download RAW message or body]

Hello,

after seeing the recent change #6413 and ticket #3761 I finally
decided to write about a corruption issue we have.

This is the environment of our application:
- We are using SQLite 3.6.3 (with SQLite.NET 1.0.60.0).
- Several database files. Each file is opened in it's own connection
and never shared across them.
- Some of these connections have another database attached to it
(containing mostly BLOB data).
- In all cases the connections are opened on program start and closed
on program shutdown.
- There's a low-priority thread that executes "pragma
incremental_vacuum" when the application is idle and there is enough
free pages. Code of the thread is listed below.
- "journal_mode=persist" is used on all databases in all connections
(to workaround a bug in the journal deletion logic on Windows, search
for "TortoiseSVN" in the mailing list archive for details)
- "synchronous=off" is used on all databases in all connections. This
setting is likely to change in future, but in no case of the
corruption a system crash was involved.

Since we started using the incremental_vacuum mode we were getting
database corruption errors pretty often (sometimes as often as once a
day in 3 people). Most, if not all, of these corruptions happened
following a ROLLBACK (caused by constraint violation). "pragma
integrity_check;" on the already corrupted databases usually reported
few "Page XXX is never used" error.

Unfortunately I don't have any of the corrupted databases at hand and
I have no deterministic way to create them. My question is if these
could be related to the just fixed problem (in ticket 3761) or if it
could be another issue?

Best regards,
Filip Navara

----

WaitHandle[] handles = new WaitHandle[] { this.stopEvent, this.wakeupEvent };
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
int timeout = -1;
int pagesPerIteration = 32;

// Wait for thread shutdown and wakeup event. The shutdown event
// is used to stop the thread on application exit. The wakeup event is
// fired on startup if there are free pages in the database or if a DELETE
// statement was executed.
while (WaitHandle.WaitAny(handles, timeout, false) != 0)
{
       long totalFreePages = 0, freePages;
       lock (this.repositories)
       {
               stopWatch.Reset();
               stopWatch.Start();
               foreach (IRepositoryBase repository in this.repositories)
               {
                       // wrapper around "pragma freelist_count;"
                       freePages = repository.GetFreePageCount();
                       totalFreePages += freePages;
                       if (freePages > 0)
                               // wrapper around "pragma incremental_vacuum(x)"
                               repository.Compact(pagesPerIteration);
               }
               stopWatch.Stop();
       }

       // We start by freeing 32 pages per one iteration of the loop for
       // each database. After each iteration the number is recalculated
       // based on the time spent on the operation and then it's
       // truncated to the <24;4096> range.
       pagesPerIteration = Math.Min(Math.Max(24, (int)(100.0 *
pagesPerIteration / stopWatch.ElapsedMilliseconds)), 4096);

       // If there are still free pages in the databases then schedule the
       // thread to wake up in 200ms and continue vacuuming them.
       if (totalFreePages > 0)
               timeout = 200;
       else
               timeout = -1;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[prev in list] [next in list] [prev in thread] [next in thread]