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