Issue with database in WAL mode: the changes are not committed to the database file, WAL file persists

[bohwaz]

Steps to reproduce

When opening a SQLite DB that is using the WAL journaling mode, then doing some changes, commiting them, and exiting SQLite Studio, the database is not modified, all the changes are only saved in the WAL journal.

As an example I'm using a database that is 2932736 bytes. I open it in SQLite Studio, then add "test" string to one of the rows, then commit, disconnect the database and exit. I now have 3 files:

-rw-r--r-- 1 bohwaz bohwaz 2932736 déc.   1 10:27 association.sqlite
-rw-r--r-- 1 bohwaz bohwaz   32768 déc.   1 10:29 association.sqlite-shm
-rw-r--r-- 1 bohwaz bohwaz    4152 déc.   1 10:29 association.sqlite-wal

The WAL and SHM files should be truncated to zero. This is shown if I open the database with the sqlite3 command line utility and do another write operation:

% sqlite3 association.sqlite 
-- Loading resources from /home/bohwaz/.sqliterc
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> CREATE TABLE test (a);
sqlite> 
% ls -la association.sqlite*
-rw-r--r-- 1 bohwaz bohwaz 2932736 déc.   1 10:31 association.sqlite

Operating system

Debian Linux.

SQLiteStudio version

3.2.1

[piula]

I can confirm that issue.

My workaround is to manually run "PRAGMA wal_checkpoint(full);" right before closing the database file. I would suggest SQLiteStudio to automatically do that internally when closing the connection.

[sbf-]

Issue confirmed with SQLiteStudio 3.2.1 on Windows 10

As a test I reinstalled V3.1.0 and the issue was gone.
I think this problem is caused by sqlite3.dll

SQLiteStudio V3.1.0 uses sqlite3.dll V3.13.0
SQLiteStudio V3.2.1 uses sqlite3.dll V3.24.0

I replaced sqlite.dll V3.24 with V3.13 and issue was gone.

I didn't test yet with sqlite3 V3.27 like @bohwaz did

EDIT: Don't know how to get sqlite3.dll V3.27 but I tested with V3.28 and the latest V3.34 and the problem persists.
So for now, I can only make it work with V3.13

EDIT: Found a very old version 3.8.4.3 which solves the issue too. I'll stick to V3.13 for now.

[gabecook]

This is relevant. #3853

[maxima120]

same issue here..