On 17/04/2009 1:39 AM, Filip Navara wrote:
> 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.
Ummmm ... your first message [scroll down to read] is talking about
*incremental* vacuuming; however the database file that you made
available has FULL (not incremental) auto-vacumming set.
dos-prompt>sqlite3 folders.dat
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma auto_vacuum;
1 <<<=== 1 means full, incremental is 2
sqlite> pragma integrity_check;
*** in database main ***
Page 11 is never used
Page 13 is never used
Page 15 is never used
Page 20 is never used
Page 21 is never used
Page 22 is never used
Page 23 is never used
Page 24 is never used
Page 25 is never used
sqlite>
Six orphan pages at the end of the file plus another 3 orphans suggests
that an auto-vacuum (full or incremental) may have been interrupted --
or perhaps later given that you are? were? using synchronous=off.
Did you get any resolution on this?
Cheers,
John
> 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 <xnav...@volny.cz> 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
> 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
