SQLite Forum

WAL files deleted
Login

WAL files deleted

(1) By Joshua Root (jmroot) on 2021-04-29 15:47:58 [source]

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] [source]

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] [source]

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 described here.

(4) By Keith Medcalf (kmedcalf) on 2021-04-29 17:44:20 in reply to 1 [link] [source]

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] [source]

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] [source]

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] [source]

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] [source]

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] [source]

Doesn't appear to fail using the Windows version of 3.32.3 CLI (nor with current CLI)

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