SQLite Forum

readonly access to WAL database
Login

readonly access to WAL database

(1) By dubiousjim on 2020-05-26 12:39:24 [link]

Hi I'm finding myself unable to open a WAL database readonly. I've read [the WAL documentation](https://www.sqlite.org/wal.html) and [this thread](https://sqlite.org/forum/forumpost/0667868158167ccbcde06f15f26a7278cde447abc7827773e82914c85acb0fdf).

I observe this problem with the sqlite3 shell as well as with my C code, so let's set the latter aside. The only thing relevant about the C code is that it first creates the database, sets `journal_mode = WAL`, and calls `sqlite3_file_control(...SQLITE_FCNTL_PERSIST_WAL, &1)`. In my testing, the -wal file is always at 0 bytes.

I can then open the database fine in r/w mode using the sqlite3 shell. (Though doing so then removes the -wal file when I close the database, as the SQLITE_FCNTL_PERSIST_WAL apparently needs to be set every time the file is opened. Don't know how to do that from the shell.)

If I try to open the database `-readonly` using the shell (whether the -wal file has been removed or not), this is what happens:


    $ sqlite3 -readonly test.db
    SQLite version 3.31.1 2020-01-27 19:55:54
    Enter ".help" for usage hints.
    sqlite> .databases
    Error: disk I/O error
    sqlite> .schema
    Error: disk I/O error
    sqlite> select count(*) from sqlite_master;
    Error: disk I/O error
    sqlite> .q

The sqlite3 shell does have Unix r/w permissions to the database file, the -wal file, and the directory that contains them.

My environment is a Mac 10.10.5, using the macports toolchain. But I compiled the sqlite3 shell by hand.

There is no -shm file because I compiled the shell (and also the libraries my C code linked against) using `-DSQLITE_DEFAULT_LOCKING_MODE=1`.

Here are the shell's config options:

    sqlite> pragma compile_options;
    COMPILER=clang-9.0.1
    DEFAULT_FOREIGN_KEYS
    DEFAULT_LOCKING_MODE=1
    DEFAULT_WAL_SYNCHRONOUS=1
    ENABLE_DBSTAT_VTAB
    ENABLE_FTS3
    ENABLE_FTS3_PARENTHESIS
    ENABLE_FTS5
    ENABLE_GEOPOLY
    ENABLE_JSON1
    ENABLE_LOAD_EXTENSION
    ENABLE_PREUPDATE_HOOK
    ENABLE_RTREE
    ENABLE_SESSION
    ENABLE_STAT4
    ENABLE_STMTVTAB
    ENABLE_UNKNOWN_SQL_FUNCTION
    HAVE_ISNAN
    LIKE_DOESNT_MATCH_BLOBS
    MAX_EXPR_DEPTH=0
    MAX_VARIABLE_NUMBER=512
    OMIT_AUTOINIT
    OMIT_DECLTYPE
    OMIT_DEPRECATED
    OMIT_PROGRESS_CALLBACK
    OMIT_SHARED_CACHE
    SECURE_DELETE
    SOUNDEX
    TEMP_STORE=2
    THREADSAFE=0
    USE_ALLOCA
    USE_URI

If I open the database in r/w mode, set the `journal_mode = DELETE`, and close it, then I can subsequently open and use the database fine in `-readonly` mode. But I'd like to understand what I'm doing that producing the inability to access the database with WAL and readonly together, which the documentation seems to say should work in my situation. (If the -wal file still exists, it's supposed to work, or if that file doesn't exist, but the shell has write-access to the directory and so can create it, it's also supposed to work.)

(2) By dubiousjim on 2020-05-26 14:01:52 in reply to 1 [link]

Looks like the problem is that I was defaulting to `locking_mode = EXCLUSIVE`. If I open the database `-readonly` but then do `pragma locking_mode=normal;`, it works as expected.

(But then a -shm file is created, so I expect this wouldn't work if that file didn't already exist and the sqlite process didn't have permissions to create it.)

(3) By Warren Young (wyoung) on 2020-05-26 17:16:31 in reply to 2

I think you simply shouldn't use the stock SQLite shell in this case. The [`SQLITE_FCNTL_PERSIST_WAL` mode][1] you found is cooperative: all processes requiring that behavior have to apply that mode for it to work as expected.

If you're just using the shell to test things, the solution ends there. If instead you really do need a SQL command shell for your application, I suggest that you hack the stock shell to have the behavior you need.

I'd then install it under a different name (e.g. `myappsqlshell`) to differentiate it from a stock `sqlite3` app.

[1]: https://www.sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpersistwal

(4) By dubiousjim on 2020-05-26 18:02:42 in reply to 3 [link]

Thanks. I was just using the shell for testing. And I was only using the `SQLITE_FCNTL_PERSIST_WAL` mode in order to make it possible to open the database `-readonly` even when no r/w processes were using it. (Didn't want to rely on the r/o process having write access to the directory containing the database.)

What I've learned from all this is:

* Though it's possible in recent versions of sqlite to open -wal databases r/o, it still is awkward in a couple of ways, and opening rollback-journal databases r/o is easier.

* If you do want to open a -wal database r/o, it can't be in `locking_mode = EXCLUSIVE`. That's ok with rollback-journal databases though.

* If you do open a rollback-journal database in `locking_mode = EXCLUSIVE`, while lacking write permissions to the database file or the directory that contains it, you are able to successfully use the database. From my experiments, it looks like the lock is in those cases simply not acquired.