SQLite User Forum

sqlite3_analyzer not closing WAL-mode db cleanly
Login

sqlite3_analyzer not closing WAL-mode db cleanly

(1) By anonymous on 2024-09-11 23:56:46 [source]

After running sqlite3_analyzer on a WAL mode database, I'm noticing that the -shm and -wal files are still in the directory after the command finishes. They get properly cleaned up the next time the database is opened and closed cleanly.

(I realize the version of sqlite3_analyzer is older than the version of sqlite3, but this environment is managed by Anaconda and that's what was installed.)

% sqlite3 test.db 
SQLite version 3.45.3 2024-04-15 13:34:05
Enter ".help" for usage hints.
sqlite> pragma journal_mode=wal;
wal
sqlite> create table t1 (c1, c2);
sqlite> insert into t1 (c1) values (1);
sqlite> select * from t1;
1|
sqlite> .quit
% ls -lh
total 16
-rw-r--r--  1 xxx  staff   8.0K Sep 11 16:41 test.db
% sqlite3_analyzer --version
3.44.2 2023-11-24 11:41:44 ebead0e7230cd33bcec9f95d2183069565b9e709bf745c9b5db65cc0cbf92c0f
% sqlite3_analyzer test.db
/** Disk-Space Utilization Report For test.db

     <OUTPUT TRIMMED>

*/
BEGIN;
CREATE TABLE space_used(
   name clob,        -- Name of a table or index in the database file
   tblname clob,     -- Name of associated table
   is_index boolean, -- TRUE if it is an index, false for a table
   is_without_rowid boolean, -- TRUE if WITHOUT ROWID table  
   nentry int,       -- Number of entries in the BTree
   leaf_entries int, -- Number of leaf entries
   depth int,        -- Depth of the b-tree
   payload int,      -- Total amount of data stored in this table or index
   ovfl_payload int, -- Total amount of data stored on overflow pages
   ovfl_cnt int,     -- Number of entries that use overflow
   mx_payload int,   -- Maximum payload size
   int_pages int,    -- Number of interior pages used
   leaf_pages int,   -- Number of leaf pages used
   ovfl_pages int,   -- Number of overflow pages used
   int_unused int,   -- Number of unused bytes on interior pages
   leaf_unused int,  -- Number of unused bytes on primary pages
   ovfl_unused int,  -- Number of unused bytes on overflow pages
   gap_cnt int,      -- Number of gaps in the page layout
   compressed_size int  -- Total bytes stored on disk
);
INSERT INTO space_used VALUES('sqlite_schema','sqlite_schema',0,0,1,1,1,40,0,0,40,0,1,0,0,3944,0,0,4096);
INSERT INTO space_used VALUES('t1','t1',0,0,1,1,1,3,0,0,3,0,1,0,0,4081,0,0,4096);
COMMIT;
% ls -lh
total 80
-rw-r--r--  1 xxx  staff   8.0K Sep 11 16:41 test.db
-rw-r--r--  1 xxx  staff    32K Sep 11 16:42 test.db-shm
-rw-r--r--  1 xxx  staff     0B Sep 11 16:42 test.db-wal
% 

(2.1) By Larry Brasfield (larrybr) on 2024-09-13 16:26:29 edited from 2.0 in reply to 1 [link] [source]

FWIW: I get the same result from version 3.46.1 tools. Investigating ...

Now fixed on trunk.