Hello, I have expected at least some reply. Oh well, new the corruption has happened again (on another different machine) and I have saved the database files. One of the corrupted files is available at http://www.emclient.com/temp/folders.zip. I'd be glad for any help or at least confirmation that it could be related to the issues in the tickets listed below.
Thanks, Filip Navara On Tue, Mar 31, 2009 at 11:05 AM, Filip Navara <[email protected]> wrote: > 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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
