Dear Yongil and Paul, Thanks for your answers.
Yongil, my unittests don't removes at all *-wal files, they do only remove the file with the base name. It is done in setUp() and tearDown() methods, not inside the unittests. https://sourceforge.net/p/mpddb/code/ci/master/tree/Dic/ut/test_chooser.py Paul, what really surprises me, as I already said, the mentioned unittest doesn't fail, being run separately. It fails only as I a part of all unittests in the package. The size of a produced intermediate database file in the unittest is always the same. You are right, nothing from you list happens, but explicit close() on the database. Anyway, I confirm that adding the mentioned explicit checkpoint ("PRAGMA wal_checkpoint;") right after conditional creation of the schema (the block with "CREATE TABLE IF NOT EXISTS..." statements) solved my issue, I can not reproduce it now. I underline that I've added one and only one explicit checkpoint before all INSERT and UPDATE statements. The fact, that I always see INSERT and UPDATE data in the database file and not always the schema data, is quite interesting. I suspect that WAL works differently for schema operations (conditional creates in my case) and for other modifying operations, and it fails for the schema in some rare cases (many opened and closes databases without leaving the application). Otherwise, I mustn't see INSERT and UPDATE data in the database file if the schema isn't checkpointed. Let me show you some examples... (may be you will find some interesting facts) The dump of a "good" database file: 00000000 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 |SQLite format 3.| 00000010 10 00 02 02 00 40 20 20 00 00 00 02 00 00 00 0d |.....@ ........| 00000020 00 00 00 00 00 00 00 00 00 00 00 05 00 00 00 04 |................| 00000030 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 |................| 00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 02 |................| 00000060 00 2d f5 a0 0d 0f f8 00 0c 06 25 00 0d 51 0f cf |.-........%..Q..| 00000070 0f a6 0c ff 0a e6 0c d6 09 62 0a b9 07 d0 09 33 |.........b.....3| 00000080 06 25 07 a3 00 00 00 00 00 00 00 00 00 00 00 00 |.%..............| 00000090 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| {....zeros....} 00000610 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000620 00 00 00 00 00 82 7b 0b 07 17 19 19 01 85 51 74 |......{.......Qt| 00000630 61 62 6c 65 73 65 74 76 61 6c 73 65 74 76 61 6c |ablesetvalsetval| 00000640 0c 43 52 45 41 54 45 20 54 41 42 4c 45 20 73 65 |.CREATE TABLE se| 00000650 74 76 61 6c 20 28 20 2d 2d 20 73 65 74 74 69 6e |tval ( -- settin| 00000660 67 20 76 61 6c 75 65 0a 20 20 20 20 20 20 20 20 |g value. | 00000670 20 20 20 20 20 20 20 20 73 69 64 20 49 4e 54 45 | sid INTE| 00000680 47 45 52 20 4e 4f 54 20 4e 55 4c 4c 2c 20 2d 2d |GER NOT NULL, --| {....cut....} 00007fa0 65 73 09 04 04 00 00 17 43 68 65 63 6b 2c 03 04 |es......Check,..| 00007fb0 00 01 5b 02 4e 75 6d 62 65 72 20 6f 66 20 63 6f |..[.Number of co| 00007fc0 72 72 65 63 74 73 20 74 6f 20 63 6f 72 72 65 63 |rrects to correc| 00007fd0 74 20 61 20 6d 69 73 74 61 6b 65 0e 02 04 00 00 |t a mistake.....| 00007fe0 21 49 6e 63 6f 72 72 65 63 74 73 13 01 04 00 00 |!Incorrects.....| 00007ff0 2b 43 75 72 72 65 6e 74 20 70 72 6f 66 69 6c 65 |+Current profile| 00008000 0a 00 00 00 6c 0a 54 00 0f a3 0f ec 0f dc 0f 5e |....l.T........^| {....cut....} 0000cf30 06 04 01 09 01 22 1e 06 04 01 09 01 21 1d 06 04 |....."......!...| 0000cf40 01 09 01 20 1c 06 04 01 09 01 1f 1b 06 04 01 09 |... ............| 0000cf50 01 1e 1a 06 04 01 09 01 1d 19 06 04 01 09 01 1c |................| 0000cf60 18 06 04 01 09 01 1b 17 06 04 01 09 01 1a 16 06 |................| 0000cf70 04 01 09 01 19 15 06 04 01 09 01 18 14 06 04 01 |................| 0000cf80 09 01 17 13 06 04 01 09 01 16 12 06 04 01 09 01 |................| 0000cf90 15 11 06 04 01 09 01 14 10 06 04 01 09 01 13 0f |................| 0000cfa0 06 04 01 09 01 12 0e 06 04 01 09 01 11 0d 06 04 |................| 0000cfb0 01 09 01 10 0c 06 04 01 09 01 0f 0b 06 04 01 09 |................| 0000cfc0 01 0e 0a 06 04 01 09 01 0d 09 06 04 01 09 01 0c |................| 0000cfd0 08 06 04 01 09 01 0b 07 06 04 01 09 01 0a 06 06 |................| 0000cfe0 04 01 09 01 09 05 06 04 01 09 01 07 04 06 04 01 |................| 0000cff0 09 01 05 03 06 04 01 09 01 03 02 04 04 09 08 09 |................| 0000d000 The dump of a "bad" file for the same parts: 00000000 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 |SQLite format 3.| 00000010 10 00 02 02 00 40 20 20 00 00 00 01 00 00 00 01 |.....@ ........| 00000020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 |................| 00000060 00 2d f5 a0 0d 00 00 00 00 10 00 00 00 00 00 00 |.-..............| 00000070 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000080 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000090 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| {....zeros....} 00000610 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000620 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000630 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000640 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000650 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000660 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000670 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00000680 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| {....cut....} 00007fa0 68 65 63 6b 65 64 20 65 6e 6f 75 67 68 2c 03 04 |hecked enough,..| 00007fb0 00 01 5b 02 4e 75 6d 62 65 72 20 6f 66 20 63 6f |..[.Number of co| 00007fc0 72 72 65 63 74 73 20 74 6f 20 63 6f 72 72 65 63 |rrects to correc| 00007fd0 74 20 61 20 6d 69 73 74 61 6b 65 0e 02 04 00 00 |t a mistake.....| 00007fe0 21 49 6e 63 6f 72 72 65 63 74 73 13 01 04 00 00 |!Incorrects.....| 00007ff0 2b 43 75 72 72 65 6e 74 20 70 72 6f 66 69 6c 65 |+Current profile| 00008000 0a 00 00 00 8e 08 4b 00 0f 17 0d bf 0f ec 0f dc |......K.........| {....cut....} 0000cf30 06 04 01 09 01 22 1e 06 04 01 09 01 21 1d 06 04 |....."......!...| 0000cf40 01 09 01 1f 1c 06 04 01 09 01 1e 1b 06 04 01 09 |................| 0000cf50 01 1d 1a 06 04 01 09 01 1c 19 06 04 01 09 01 1b |................| 0000cf60 18 06 04 01 09 01 1a 17 06 04 01 09 01 19 16 06 |................| 0000cf70 04 01 09 01 18 15 06 04 01 09 01 17 14 06 04 01 |................| 0000cf80 09 01 16 13 06 04 01 09 01 15 12 06 04 01 09 01 |................| 0000cf90 14 11 06 04 01 09 01 13 10 06 04 01 09 01 12 0f |................| 0000cfa0 06 04 01 09 01 11 0e 06 04 01 09 01 10 0d 06 04 |................| 0000cfb0 01 09 01 0f 0c 06 04 01 09 01 0e 0b 06 04 01 09 |................| 0000cfc0 01 0d 0a 06 04 01 09 01 0c 09 06 04 01 09 01 0b |................| 0000cfd0 08 06 04 01 09 01 09 07 06 04 01 09 01 08 06 06 |................| 0000cfe0 04 01 09 01 06 05 06 04 01 09 01 05 04 06 04 01 |................| 0000cff0 09 01 04 03 06 04 01 09 01 03 02 04 04 09 08 09 |................| 0000d000 Regards, Max > From: sandersonforens...@gmail.com > Date: Fri, 3 Jun 2016 13:05:57 +0100 > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] WAL: no schema after close > > As above the WAL wont check point unless > > 1. You tell it too > 2. You commit a transaction which takes the wal above (default) 1000 pages > 3. you exit the applictaion gracefully > > The WAL algorithm is doing exactly what it should do and is taking (or > rather keeping) the DB in it's last know good state. In your case this > is a new blank DB. > > It is not enough to commit a transaction as the wal can and often does > contain the data for multiple transactions. > > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit > -Forensic Toolkit for SQLite > email from a work address for a fully functional demo licence > > > On 3 June 2016 at 11:32, Yongil Jang <yongilj...@gmail.com> wrote: > > In my guess, if you call following command after for each operation and > > schema exits exactly... Your test may removes *-wal file. > > > > conn.execute("PRAGMA wal_checkpoint;") > > > > I hope it is correct answer. > > > > Regards. > > > > 2016년 6월 3일 (금) 19:19, Максим Дементьев <deme...@hotmail.com>님이 작성: > > > >> Hello, > >> > >> I’ve got a set of python 3 unittests which use sqlite (versions 3.12.0 and > >> 3.13.0 under Linux 4.4.6-gentoo x86_64). > >> > >> There is one test which closes the database and opens it again during the > >> test. > >> If I use the WAL (by using conn.execute("PRAGMA journal_mode=WAL;")) and > >> run all unittests, this unittest produces a database file with the empty > >> schema after first close, so it fails. > >> When I run it alone (or without WAL), it passes. > >> > >> I’ve checked “hexdump -C” of the normal and the bad cases after first > >> close, in both cases the size of database file is the same, they contain > >> common data for inserted rows, but there is no “CREATE TABLE ...” > >> statements in the bad database file. > >> > >> There are conn.commit() calls after each modification SQL statement. I > >> even tried to put explicit "BEGIN; ... COMMIT;" around "CREATE TABLE IF NOT > >> EXISTS..." statements, it doesn't help. > >> > >> Is it a well-known problem ? > >> > >> Regards, > >> Max > >> > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
