Changing the database schema or its data is not commited

[markus-ja]

Details

When I try to modify the database data (structure or data), the changes are done, but the temp WAL files (*.db-wal & *.db-shm) remains open after disconnecting the database or closing the studio.
When deleting the temp WAL files, the changes are not present anymore.

See:
#3853

Steps to reproduce

1. Insert, delete or update any data in a table.
2. Disconnect the database.
3. Go to the physical file location of the database.
   => The WAL files are still present.
4. Delete the temp WAL files (*.db-wal & *.db-shm) and connect to the database again.
   ERROR: The changes are not present anymore.

Operating system

Win10 20H2

SQLiteStudio version

v3.3.3

[pawelsalawa]

Is the database located on a network folder?

[markus-ja]

It is a local database. It seems, only databases with foreign key constrains are having this issues. When opening a database with only simple tables, no WAL files are created at all.

Attached is a sample database, where I have that issue. Just try to make same changes, and delete the WAL files afterwards.

test.zip
.

[pawelsalawa]

This database has journaling mode set to wal and in this mode it is expected what you are observing. Read https://sqlite.org/pragma.html#pragma_journal_mode

What you are probably looking for is to set journaling mode to delete:
pragma journal_mode = delete

[pawelsalawa]

After further lecture, it seems that SQLiteStudio incorrectly handles wal mode. Created bug: #4369

[Boogier]

I think this is expected. In WAL mode you need to checkpoint your database with PRAGMA wal_checkpoint command.
Until checkpoint is done transactions are present only in the log file. If it is removed until checkpointed all transactions will be removed.

[pawelsalawa]

No. SQLite documentation says:

The WAL file exists for as long as any database connection has the database open. Usually, the WAL file is deleted automatically when the last connection to the database closes. However, if the last process to have the database open exits without cleanly shutting down the database connection, or if the SQLITE_FCNTL_PERSIST_WAL file control is used, then the WAL file might be retained on disk after all connections to the database have been closed.