Yes! The journal file was indeed there. I deleted it before openning the database, and read 50 records. I repeated the process; it's consistent.
The way I see it that even though the synchronous setting is FULL, there is no API to tell the OS to physically delete a file from disk, just like there is no API to physically force a file to truncate. Well, maybe there is a way (or an API), but sqlite doesn't presently do it. The only way sqlite forces physical writes to disk is via FlushFileBuffers(). So with: journal_mode=DELETE: no buffers written, the OS delays in deleting the file, and during a hard reset, the journal file will remain -- and in my case, that's causing 1 record to get lost. journal_mode=TRUNCATE: no buffers written, the OS delays in truncating the file; same as above. journal_mode=PERSIST: header information is physically written to disk. journal_mode=WAL (synchronous=FULL): all data is physically written to disk, no journal files are truncated or deleted. I think your asking the question indicates you suspected what I would find, no? If I don't opt for WAL, I will use the PERSIST mode instead of the default DELETE mode. On Mon, Sep 10, 2012 at 6:47 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 09/10/2012 03:15 AM, Daniel Frimerman wrote: > >> My apologies about the attachment; should have known better. >> This should be better: >> https://dl.dropbox.com/u/**50838941/SQLite3_Test.zip<https://dl.dropbox.com/u/50838941/SQLite3_Test.zip> >> >> I only get the problem with DELETE and TRUNCATE journal_mode (synchronous >> set to NORMAL or FULL), but not with PERSIST (synchronous set to NORMAL or >> FULL) or WAL (synchronous set to FULL). >> > > When using "PRAGMA journal_mode=DELETE", after you reboot the > system is there a *-journal file present in the directory > next to your database file? > > If so and you rename it before opening the database, are all > 50 records present? > > > > >> The reason I think there has to be 50 rows is because on FULL mode for >> example, the I/O buffers are flushed, and it's consistently missing 1 >> record as opposed to any other number of records. I insert 50 records, the >> sqlite3 command line utility executes the script, and I get to a stage >> where I can write commands to the console. What I mean is that as far as >> sqlite is concerned, it has written the data to disk and also instructed >> the OS to flush the buffers. Perhaps it finalises something from the last >> insert only when the next insert comes in? >> It could be a coincidence of some sort, by sheer difference of >> implementation of different journals that the "problem" doesn't show >> itself >> with PERSIST or WAL journals. >> >> I turned off host I/O cache in VirtualBox, so any writes by the guest OS >> have to be physically written to the virtual disk on the host. The guest >> has "standard" I/O caching on disk, but FlushFileBuffers() should have >> done >> its job. There is no reason why any link in the chain should report data >> written to disk without actually doing it, unless there is a problem. >> >> I suppose I should just go ahead and test it on the physical PC. If there >> is a problem with that also, then I suppose I could blame the OS for not >> flushing stuff to disk properly. >> >> Now I gotta find me a machine............ >> >> Regards, >> >> Dan >> >> >> >> On Sun, Sep 9, 2012 at 8:14 PM, Pavel Ivanov<paiva...@gmail.com> wrote: >> >> Note: attachments are stripped out of this list. So if you want for >>> anybody else to see your zip file you need to put it on some website >>> and post link here. >>> >>> About the problem you have: I wonder how are you sure that there >>> should be 50 rows in the database and not 49? If you are resetting the >>> OS before it has a chance to properly commit everything then it's okay >>> for last transaction to be missing. But if you are sure that you are >>> resetting the OS after everything is settled then maybe you are not >>> committing your last transaction properly? Or maybe there's some bug >>> in your virtualization layer and you need to try the same thing on a >>> real hardware? >>> >>> >>> Pavel >>> >>> >>> On Sun, Sep 9, 2012 at 8:50 AM, Daniel Frimerman >>> <danielfrimer...@gmail.com> wrote: >>> >>>> I am fairly new to sqlite and as a result of not reading the manual and >>>> >>> not >>> >>>> doing some performance testing, I got punished somewhat. I did not >>>> anticipate that on journal_mode=DELETE and synchronous=FULL, I would get >>>> >>> no >>> >>>> more than 5 inserts (in auto-commit mode) per second. It crippled a >>>> certain batch operation on a live system. That's water under the >>>> bridge; >>>> it's the testing afterwards and a potential minor problem that I found >>>> is >>>> what I am now interested in. >>>> >>>> I tested all journal mode settings for sqlite, as well as the >>>> synchronous >>>> setting. Some things that I discovered were not so obvious from reading >>>> the docs, such as the WAL journal mode combined with NORMAL synchronous >>>> setting, which is nowhere near as "durable" as NORMAL setting for other >>>> journal modes. I.e. NORMAL mode for DELETE journal in 99% of cases saves >>>> all inserted data - reproducing the slightest of chances that >>>> consistency >>>> is compromised was rather hard. This is reflected in performance >>>> >>> testing: >>> >>>> NORMAL is only slightly faster than FULL mode for non-WAL journal >>>> >>> settings >>> >>>> (btw, journal_mode=OFF was never tested in any of my tests). But, I >>>> understood, that in WAL+NORMAL mode is equivalent to FULL+non-WAL mode >>>> where consistency/corruption is concerned. That is, the database cannot >>>> get corrupted in WAL+NORMAL. The gain in speed for WAL+NORMAL trades >>>> off >>>> durability and in my tests I easily reproduced that. >>>> >>>> Okay, that was not really related to the possible bug I found. I've >>>> attached a ZIP file containing some batch files that create a table, >>>> >>> insert >>> >>>> some rows, at which point you hard-reset the OS, log back in and check >>>> if >>>> the number of rows in the DB matches what you inserted. Although the >>>> non-WAL journal modes are somewhat similar, the little problem that I've >>>> come to find only happens on DELETE/TRUNCATE, but not on PERSIST or WAL. >>>> The problem is basically as follows: in DELETE and TRUNCATE journal mode >>>> combined with NORMAL/FULL synchronous mode, there is always 1 row >>>> missing >>>> during my simulated power-cut. >>>> >>>> I used VirtualBox 4.1.22 and Windows XP Pro (SP3) and sqlite3 3.7.14 >>>> (command line as well as through my testing application). In VirtualBox, >>>> under storage settings for the VM, I used IDE Controller (afaik it's >>>> >>> single >>> >>>> threaded), turned off host I/O cache. Inside the guest, write-cache >>>> >>> should >>> >>>> be enabled in device manager under policies for the default disk >>>> >>> controller. >>> >>>> >>>> To test this, set your VM as above, copy the files from the attached ZIP >>>> file, also download the latest sqlite3 command line shell. Restart the >>>> guest once to ensure your files are flushed out before you start >>>> >>> resetting >>> >>>> the guest :) >>>> Execute the following batch file: EXEC_DATA__DELETE_FULL.cmd, wait 2-3 >>>> seconds (or less) then hit HOST+R to hard reset the OS. When you >>>> reboot, >>>> run READ_DATA.cmd, you'll see 49 rows, but there should be 50. >>>> You can try the same with EXEC_DATA__DELETE_NORMAL.cmd, >>>> EXEC_DATA__TRUNCATE_FULL.cmd, EXEC_DATA__TRUNCATE_NORMAL.cmd >>>> 50 rows if you try with EXEC_DATA__PERSIST_FULL.cmd and >>>> EXEC_DATA__PERSIST_NORMAL.cmd and EXEC_DATA__WAL_FULL.cmd >>>> >>>> What's with that? >>>> >>>> Kind regards, Dan >>>> >>>> ______________________________**_________________ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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<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<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<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
