On Sat, Jul 12, 2014 at 4:37 AM, Charles Parnot <charles.par...@gmail.com> wrote:
> Hi all, > > For testing purposes of our application (a Mac app), I am generating what > I thought would be a database with a “hot” journal using this approach (on > an existing database): > > - open the database (and PRAGMA journal_mode = TRUNCATE;) > - open a transaction: BEGIN IMMEDIATE TRANSACTION; > - add some rows: INSERT etc… > - **make a copy of the db and journal files** (while still hot?) > Normally you need to either (1) reduce the page cache size using "PRAGMA cache_size=5" or else (2) do a VERY large transaction in order to get SQLite to spill content to disk in order to get a hot journal using the technique above. > - close the transaction > > Then I open the copied database+journal (naming the files appropriately), > again in TRUNCATE journal mode. As expected, the content of the database > does not include the inserted rows. However, the journal file is not > emptied, even after closing the database. Based on the documentation ( > http://www.sqlite.org/lockingv3.html#hot_journals), I would have expected > the journal file to be emptied because it is “hot”. > > There are 2 options here: > > - the journal file is actually not “hot” and I misunderstood the > conditions that make it hot > - there is a bug in SQLite > > Obviously, I strongly suspect I am misunderstanding things, and don’t > think it is an SQLite bug. Despite intensive Google-ing and more testing, I > am not sure what makes the journal non-hot. > > Thanks for your help! > > Charles > > > NB: You might be wondering why I am doing the above. I realize SQLite has > already much more advanced tests for “hot” db+journals (running custom > versions of filesystems to generate all kind of edge cases). The test case > I am generating is just for a simple edge case of our Dropbox-based syncing > (see: https://github.com/cparnot/PARStore and > http://mjtsai.com/blog/2014/05/21/findings-1-0-and-parstore/). For a > given database file, there is only one device that can write to it, all > other devices being read-only (not in terms of filesystem, but > sqlite-wise). But it is possible that Dropbox will copy a database and > journal files that are not consistent with each other, which can create > problems. For instance, maybe a read-only device could try to open the > (still old) database with a new non-empty journal file and sqlite would > empty that journal file, then Dropbox could in turn empty the journal file > before the writer client had finished the transaction. I am not (yet) going > to test for and try to protect against more complicated (and rarer) edge > cases where the database is in the middle of writing a transaction (which I > suspect will only happen in case of crashes, not because of Dropbox, in > which case the recovery of the database by the read-only client would > actually be beneficial). > > -- > Charles Parnot > charles.par...@gmail.com > http://app.net/cparnot > twitter: @cparnot > > Your Lab Notebook, Reinvented. > http://findingsapp.com > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
