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

[pawelsalawa]

Similar problem in #4367. Solution:
pragma journal_mode = delete

[bohwaz]

In case of your example with SQLite maybe you have the journal mode set by default in your /home/bohwaz/.sqliterc which is sourced at the startup?

No. I think SQLiteStudio has a bug where the transaction is not commited, as there is no issue with SQLite itself.

[bohwaz]

SQLiteStudio should be able to handle WAL mode without having to change the journal mode of the database, like any other SQLite client :)

[pawelsalawa]

After further lecture I admit you are right, let me register short bug so I can quickly remember it once I sit down to it.

[pawelsalawa]

Created #4369

[bohwaz]

Great, thank you :)