sqlite3 CLI on macOS not cleaning up wal and shm files on exit
(1) By anonymous on 2020-03-26 11:42:57 [source]
To wit:
[msa@dandy tmp]$ rm /tmp/test.db*
rm: /tmp/test.db*: No such file or directory
[msa@dandy tmp]$ /usr/bin/sqlite3 /tmp/test.db
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode = WAL;
wal
sqlite> CREATE TABLE t(a);
sqlite> .quit
[msa@dandy tmp]$ ls /tmp/test.db*
/tmp/test.db /tmp/test.db-shm /tmp/test.db-wal
[msa@dandy tmp]$
A simple build of the SQLite 3.28.0 amalgamation (cc *.c *.h) deletes the wal and shm files when it exits. Any idea what's going wrong here? I'm running macOS Catalina, but I've seen this behavior on previous versions as well.
(2) By Richard Hipp (drh) on 2020-03-26 12:07:25 in reply to 1 [link] [source]
When the last connection to a WAL-mode database disconnects, SQLite normally tries to checkpoint the database and delete the -wal and -shm files. However, this can be disabled by enabling the SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE flag.
The version of SQLite shipped on MacOS and iOS contains a lot of custom modifications. Perhaps this flag has been turned on by default for the version of SQLite that Apple is shipping.
Try this: On your built-in "sqlite3", run the command:
.dbconfig
What is the value of the "no_ckpt_on_close" flags?
(3) By anonymous on 2020-03-26 12:15:31 in reply to 2 [link] [source]
This is the whole shebang:
[msa@dandy tmp]$ /usr/bin/sqlite3
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .dbconfig
enable_fkey off
enable_trigger on
fts3_tokenizer off
load_extension off
no_ckpt_on_close off
enable_qpsg off
trigger_eqp off
reset_database off
defensive off
sqlite>
(5) By Richard Hipp (drh) on 2020-03-26 12:24:59 in reply to 3 [link] [source]
So much for my first theory. Working on a new explanation now....
(7.1) Originally by anonymous with edits by Richard Hipp (drh) on 2020-03-26 17:47:35 from 7.0 in reply to 5 [link] [source]
One additional pointer I can offer is that the order in which the open database handles are closed seems to matter. If they are closed in the reverse order in which they were opened (like popping a stack), then SQLite deletes the files. If they are closed in the same order in which they were opened, then SQLite does not delete the wal and shm files.
See https://github.com/groue/GRDB.swift/issues/739 for more context.
(8.1) Originally by anonymous with edits by Richard Hipp (drh) on 2020-03-26 17:46:57 from 8.0 in reply to 7.0 [link] [source]
I can't think of an explanation for that that isn't a bug. But the bug might be in the OS or the language shim, not SQLite.
It's interesting that you obtained the result using your own Swift code, not the CLI which comes with macOS. Can you post the results of these, as called from your Swift code ?
SELECT sqlite_version();
SELECT sqlite_source_id();
(9) By Michael Allman (msa) on 2020-03-26 19:33:56 in reply to 8.1 [link] [source]
This may be an OS build issue, but it's not a language binding issue. I showed how the problem can be reproduced using the sqlite3 CLI in my first post. ;)
(4) By Simon Slavin (slavin) on 2020-03-26 12:16:12 in reply to 1 [link] [source]
I reproduced your result in 3.28.0. But the problem didn't occur with the current version 3.31.1. Please download the current version of 'Precompiled Binaries' for macOS and try again.
(6) By anonymous on 2020-03-26 12:30:00 in reply to 4 [link] [source]
My experience is likewise. Version 3.31.1 behaves as expected. The only outlier I've found is the built-in version from macOS. It would be helpful to nail down the issue there, because I'm using SQLite from a macOS app I'm developing. That uses the built-in version.