SQLite Forum

SQLITE_CANTOPEN a WAL database even though I have set PRAGMA locking_mode = EXCLUSIVE;

SQLITE_CANTOPEN a WAL database even though I have set PRAGMA locking_mode = EXCLUSIVE;

(1) By anonymous on 2020-08-04 15:08:03 [link] [source]

I'm using 3.32.3 and I'm struggling with opening 3 databases in WAL mode without going through a rollback journal. I have a VFS that only implements version 1.

The very first thing I do is PRAGMA locking_mode = EXCLUSIVE; but it seems that it's too late since in order to be able to issue a PRAGMA I need to sqlite_open_v2 a database first.

So my latest attempt is opening :memory:, setting locking_mode=EXCLUSIVE, then ATTACHing everything else that I need. Unfortunately that doesn't work either. I have tried setting WAL mode before an ATTACH but that has no effect so I'm issuing 3 PRAGMAs after the ATTACHes are done (it seems that I must do it per database otherwise it doesn't work either - same deal with page_size, if I change it to 65536 before an ATTACH it becomes 8192).

This only works if the database files don't exist and I'm just creating them, but I can't open them anymore because if SQLite sees that a database file is WAL it unconditionally checks if the VFS is version 2 and supports SHM no matter what - since mine doesn't, it returns SQLITE_CANTOPEN.

I appreciate that what I've described is chaotic, but that's been my last two days - nothing makes sense anymore so it's just random trial-and-error with shuffling PRAGMAs and opening orders around. What's the correct sequence of function calls / pragmas to be able to open multiple attached databases in EXCLUSIVE WAL mode on a v1 VFS?

(2) By anonymous on 2020-08-04 17:17:52 in reply to 1 [link] [source]

What's even more perplexing is that even if I only ever do CREATE TABLE IF NOT EXISTS on existing tables and only SELECTs I'm still seeing a relatively high amount of journal and WAL writes coming in.

Could it be related to this?

/* Open a transaction on the database file. Regardless of the journal
** mode, this transaction always uses a rollback journal.

If every single opening of the database incurred a WAL->rollback->WAL conversion that would at least explain in part what I'm seeing.

(3) By anonymous on 2020-08-04 19:12:27 in reply to 2 [source]

For prosperity, this is what more-or-less worked in the end, still not ideal:

Before ATTACHing databases

PRAGMA locking_mode = EXCLUSIVE;
PRAGMA journal_mode = WAL;

After ATTACHing databases, must do these once per schema

PRAGMA your_db.page_size = 65536;
PRAGMA your_db.journal_mode = WAL; -- Yup, again
PRAGMA your_db.auto_vacuum = INCREMENTAL;
PRAGMA your_db.synchronous = NORMAL;

I'm not claiming that this is the correct way to do this by a long shot, but if it helps one other person then yay.