WAL files deleted
(1) By Joshua Root (jmroot) on 2021-04-29 15:47:58
SQLite version 3.35.5 seems to be deleting the -wal and -shm files for a database when closing it, under at least some circumstances. This of course makes it impossible to read the database without write permissions for the enclosing directory. This behaviour seems to be a relatively recent change, as I'm unable to reproduce it with 3.28.0. Is this a bug, or a deliberate change? If the latter, is there a way to prevent the files from being deleted?
(2) By ThanksRyan on 2021-04-29 16:38:23 in reply to 1 [link]
Oh, good. There's only 594 files changed since 3.28.0 and 3.35.5: <https://www.sqlite.org/src/vdiff?from=884b4b7e502b4e99&to=1b256d97b553a961&diff=0> To the Fossil hackers: it would be nice to see a count of files changed. Maybe just below the context stuff and above the the diffs.
(3) By Larry Brasfield (larrybr) on 2021-04-29 16:55:26 in reply to 1 [link]
I don't believe the duration of those files, when left to default behavior, was guaranteed. If it changed, it is still within the API specification. However, for better control of this, consider the TRUNCATE journaling mode <u>[described here](https://sqlite.org/pragma.html#pragma_journal_mode)</u>.
(4) By Keith Medcalf (kmedcalf) on 2021-04-29 17:44:20 in reply to 1 [link]
The -wal and -shm files should always be deleted when closing the last connection to a database in WAL mode unless the connection has been configured not to do so by using the `sqlite3_file_control` API specifying to not delete the WAL with the `SQLITE_FCNTL_PERSIST_WAL` option for every connection to the database file. <https://sqlite.org/c3ref/file_control.html> The `SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE` option to the `sqlite3_db_config` will also probably result in the -wal and -shm not being deleted if it is applied to every connection to the database. <https://sqlite.org/c3ref/db_config.html> Are you using one of these options and it is no longer working correctly or are you not using one of these options every time and observing that a previously existing bug may have been fixed?
(5) By Joshua Root (jmroot) on 2021-04-30 04:11:29 in reply to 4 [link]
Thanks. I missed the part in the docs about it being deleted automatically by default, and that didn't ever seem to happen with the older version. I'll start using `SQLITE_FCNTL_PERSIST_WAL`.
(6) By Warren Young (wyoung) on 2021-04-30 16:23:46 in reply to 5 [link]
If it didn’t “ever” happen, there’s a fair chance you weren’t closing SQLite cleanly. In the default configuration, those files only exist while a program is actively using the DB. You might want to check for this in the read/write case in case you’re causing SQLite to drop partially-complete transactions and such.
(7) By aryairani on 2021-06-07 04:15:55 in reply to 4 [link]
We were wondering about this in our project too, we think we're closing all the connections appropriately, but still end up with lingering wal files. Someone suggested this test using the sqlite3 cli; what do you think is going on here? Maybe a bug in the CLI? Or a bug in the docs? ``` arya@jrrr /tmp % rm -rf test* arya@jrrr /tmp % sqlite3 test.db SQLite version 3.32.3 2020-06-18 14:16:19 Enter ".help" for usage hints. sqlite> PRAGMA journal_mode = WAL; wal sqlite> CREATE TABLE t(a); sqlite> .exit arya@jrrr /tmp % ls test* test.db test.db-shm test.db-wal arya@jrrr /tmp % ```
(8) By Keith Medcalf (kmedcalf) on 2021-06-07 05:04:44 in reply to 7 [link]
The CLI is an Application program that uses the SQLite3 library. It is no different from any other application that uses the SQLite3 library except that it happens to be written and supported by the authors of SQLite3. Do you get the same result with the current versions of SQLite3?
(9) By Donald Griggs (dfgriggs) on 2021-06-07 12:42:31 in reply to 7 [link]
Doesn't appear to fail using the Windows version of 3.32.3 CLI (nor with current CLI) <pre> k:\tmp>c:sqlite3 test.db SQLite version 3.32.3 2020-06-18 14:00:33 Enter ".help" for usage hints. sqlite> PRAGMA journal_mode = WAL; wal sqlite> CREATE TABLE t(a); sqlite> .exit k:\tmp>dir Volume in drive K is Windows Volume Serial Number is 3EB4-4EE0 Directory of k:\tmp 06/07/2021 08:34 AM <DIR> . 06/07/2021 08:34 AM <DIR> .. 06/07/2021 08:34 AM 8,192 test.db 1 File(s) 8,192 bytes 2 Dir(s) 107,722,104,832 bytes free </pre>