diesel-rs/diesel

Closing a sqlite connection does not clear the -wal and -shm file

hillin opened this issue · 4 comments

hillin commented

Setup

Versions

  • Rust: 1.73.0
  • Diesel: 2.1.4
  • Database: sqlite
  • Operating System: Windows 10

Feature Flags

  • diesel: "sqlite"

Problem Description

What are you trying to accomplish?

Closing (dropping) a connection, while made sure that there is no other process accessing the db file.

What is the expected output?

The -wal (which is empty) and -shm files should be deleted.

What is the actual output?

Those files are not deleted.

Are you seeing any additional errors?

No.

Steps to reproduce

// demo.db is created with `PRAGMA journal_mode = WAL;`
let path_str = "file:demo.db?mode=ro";  
let mut connection = SqliteConnection::establish(path_str).unwrap();

// do any kind of reading here, -wal and -shm files will be generated

drop(connection);  // does not remove the -wal and -shm files

More information

  • As per the sqlite documentation, usually, the WAL file is deleted automatically when the last connection to the database closes. This is not the case I'm experiencing as I've made sure that my program is the only process that connects to this db file.
  • Opening the db file with DataGrip and disconnecting it will delete these files.
  • Running SELECT count(*) FROM sqlite_master; (as suggested at https://sqlite.org/forum/forumpost/2a8c51e0b8?t=h) before dropping the connection does not delete these files.
  • These files can be deleted if I run PRAGMA schema.journal_mode = DELETE before dropping the connection, but only if the database is writable (i.e. no ?mode=ro).

Checklist

  • This issue can be reproduced on Rust's stable channel. (Your issue will be
    closed if this is not the case)
  • This issue can be reproduced without requiring a third party crate
weiznich commented

Thanks for opening this bug report. Can you clarify why you believe this is a bug in diesel, especially as the sqlite documentation says "usually", which implies that there are valid cases where this is not the case and given that diesel explicitly calls sqlite3_close for each opened database object:

diesel/diesel/src/sqlite/connection/raw.rs

Line 248 in 6ef23bd

let close_result = unsafe { ffi::sqlite3_close(self.internal_connection.as_ptr()) };

hillin commented

Hi @weiznich , I don't have all the clue that it's a diesel bug, it's just an abnormality that I observed using diesel.

Per the sqlite documentation, the wal files are not deleted if:

  • the last process to have the database open exits without cleanly shutting down the database connection - which should not be the case, as my program is the only process that opens the database, and I guess calling sqlite3_close is sufficient to "cleanly shut down the database connection"?
  • or if the SQLITE_FCNTL_PERSIST_WAL file control is used - this one I'm not sure, could there be a place that implicitly sets this flag?

The documentation did not list a third reason, but since the docs are as comprehensive as they can be, I believe it has covered most of the cases. Also for me it's tolerable if the wal files are not deleted occassionally, but apparently in our use cases they are never deleted, which I think should be addressed as a bug.

weiznich commented

This seems to be related to the fact that you use a read only connection.

https://stackoverflow.com/questions/33177784/sqlite3-wal-shm-files-not-deleted#33184948

hillin commented

@weiznich Thanks! That indeed seems to be the problem. A weird behavior in the sqlite implementation but now kind of make sense to me: the -wal file is required to open a db in WAL mode. If sqlite failed to generate the file, it can't even open the db.

(per sqlite docs:)

If the WAL and WAL-index files are missing, then a process that lacks permission to create and initialize those files will not be able to open the database.