Reload WAL when modified by outside process
(1) By Ben Johnson (benbjohnson) on 2021-01-20 22:21:48 [link] [source]
I tried to search the forum for this topic but couldn't quite find what I'm looking for. Is it possible to reload the WAL/SHM for an SQLite connection without reconnecting?
I have a
db file and I want to copy the
db-wal file next to it and have it be visible to already connected SQLite connections. The existing connections are not performing writes. I tried starting & rolling back a write transaction and using
PRAGMA wal_checkpoint but no luck.
Is this possible?
(2) By Richard Hipp (drh) on 2021-01-20 23:17:25 in reply to 1 [link] [source]
I think you have to reopen the connection. At least, I can't think of any other way.
(3) By Larry Brasfield (LarryBrasfield) on 2021-01-20 23:22:12 in reply to 1 [link] [source]
You are treading on extremely thin ice if you do what you claim to want.
I would also advise you to consider yourself lucky that your doomed-to-fail (eventually) initial copying attempt failed early. And, going even further, I hope you appreciate that "try it and see what happens" is a perilous approach to writing software. I hope you learn not to do that before ever working on flight software, banking software, any software I use, or nuclear armed missile launch software.
(4) By Richard Hipp (drh) on 2021-01-20 23:35:06 in reply to 3 [link] [source]
I don't think VACUUMM INTO will work for him. My guess is that Ben is asking in relation to his experimentation outlined here: https://twitter.com/benbjohnson/status/1351590920664313856
(5) By Ben Johnson (benbjohnson) on 2021-01-21 01:45:19 in reply to 3 [link] [source]
Hi Larry. Thanks for the feedback. To give some context, I have a real-time SQLite replication tool that Dr. Hipp referenced that uses the regular SQLite API for locking & checkpointing. That's working pretty well and it works based on the invariants described in the SQLite documentation.
Someone asked if I could make the replication fan-out to live replicas. I didn't think it was possible but I thought I'd try it as an experiment. I tried a couple ideas (unsuccessfully) and then posted to this forum. Sounds like it's not possible.
I regret to inform you that I wrote BoltDB which is used by etcd which is in every Kubernetes deployment so I fear that you likely have, in fact, used software that I have worked on.
(6) By Ben Johnson (benbjohnson) on 2021-01-21 01:46:35 in reply to 2 [link] [source]
Thanks for feedback, Dr. Hipp.
(7) By Larry Brasfield (LarryBrasfield) on 2021-01-21 02:53:24 in reply to 5 [link] [source]
It appears that you do recognize the value of relying only on the documented API behavior, so my worry over any fly-by-wire-(driven by a computer) airplane I might ride is considerably alleviated. I hope you appreciate that, from your original post, it appeared that you intended to go outside of such droll constraints, and had already tried. And I imagine now that you appreciate that some inexperienced developers may need to think about how try-it-and-see (and hope for the best) approaches are likely to play out. Please accept my apology for hastily putting you into that category.
Richard and the other members of the SQLite dev team are good about establishing, via documentation, what SQLite behavior can be relied upon. At the same time, they make substantial implementation changes to improve performance while holding to the documented API behavior and continuing to pass the extensive tests which help them catch inadvertent deviations from that behavior. It is for that latter reason that I considered your early failure to be fortunate, and thought you might also.
Good luck with that replication project. I have seen how challenging it can be to get such to work well and reliably. If your tool does that, I would be very tempted to use it (for a personal project) if there was sufficient reason to believe it was reliable. Getting replication to work across live instances would be great, if it was reliable. Maybe a connection proxy, able to deal with the need for closing and reopening "real" connections, could be made to work.
(8) By Ben Johnson (benbjohnson) on 2021-01-21 05:57:00 in reply to 7 [link] [source]
I can only imagine the unspeakable things developers attempt to do to SQLite and what you hear about on the forum. I should have prefaced my question by saying that it was for an experiment.
I'd love some feedback on the replication tool. I'm open-sourcing it in the next few days. It's still beta software but it seems to be running well so far. Since it's doing physical replication, I'm able to periodically validate the replication by checksumming the primary database & restoring and checksumming the replica. It seems to be a good check for correctness.
It's certainly not hardened enough to be production-ready but it's probably ok for a personal project—so long as you're not building nuclear-armed missile launch software in your spare time.
(9) By Dan Kennedy (dan) on 2021-01-21 11:07:23 in reply to 1 [link] [source]
There is no supported way to do this.
But if that does not deter you, one way is to put the db in "PRAGMA journal_mode = delete" (so there is no wal file). Copy the *-wal next to the db. The next time any existing connection reads from the db file it will automatically switch to wal mode and read from the db and the new wal file. Everything will proceed as normal.
Thing is, the db won't switch back to rollback journal mode until the last connection closes the db and deletes the wal file. So you could only do this once without closing all connections.
If the db is already in wal mode, you would have to: (a) make sure there is no content in the current wal file (b) copy the contents of your wal file over the top of the current wal file and (c) zero the first few bytes of the *-shm file. Step (c) will force the next reader to run recovery and thereby pick up on the new wal file content.
(10) By ddevienne on 2021-01-21 11:20:31 in reply to 9 [link] [source]
WAL replication is something many people would like to do.
SQLite already has great extension support in many areas,
but it still does lack a few I wish for, like pager hooks
to do page compression and/or checksuming w/o going to the
complexity of a VFS, or more in point to this thread access
to the WAL subset of a commit to be able to copy and ship
it elsewhere, for replication, which of course requires being
able to apply a WAL-commit to a connection, in an official way.
It's very nice of you to describe something that would work Dan
but the unsupported nature of it is what it is :).
(11) By doug (doug9forester) on 2021-01-22 06:37:51 in reply to 8 [link] [source]
Please point to a reference to "what is a replication tool". Thanks.
(12) By Ben Johnson (benbjohnson) on 2021-01-22 14:13:01 in reply to 11 [source]
I just open-sourced the replication tool here:
It lets you stream SQLite WAL changes to S3 so you can recover later in the event of a node failure.