SQLITE_IOERR on `pragma journal_mode=wal;`
(1) By aryairani on 2021-05-31 01:05:23 [link] [source]
I just enabled WAL in our application, and one of our beta users is getting this message via a sqlite bindings library*:
SQLite3 returned ErrorIO while attempting to perform prepare "PRAGMA journal_mode=WAL;": disk I/O error
"ErrorIO" corresponds to
SQLITE_IOERR, "prepare" corresponds to
sqlite3_prepare_v2, and I don't know where the message
disk I/O error comes from; maybe the OS? I would love more info.
I switched from
c_sqlite3_extended_errcode to get more info, but it still seems to be returning plain old
SQLITE_ERROR. I also turned on
-DSQLITE_DEBUG but it didn't seem to do anything for me.
I can't reproduce it on my mac, but his system is:
Ubuntu v20.04.2 LTS (Focal Fossa) EXT4 filesystem, x86_64 architecture
Possibly related is the still unsolved https://sqlite.org/forum/forumpost/ab37b8ed32 which I can reproduce on Mac.
I may end up ditching WAL, but I'd rather make it work, because certain operations have been faster with WAL journaling.
Thanks in advance for any ideas about how to figure it out. -Arya
*direct-sqlite, if you're curious
(2) By Scott Robison (casaderobison) on 2021-05-31 01:29:42 in reply to 1 [source]
Is there any chance the beta user is trying to access the file over a network file system? I know the message states it uses EXT4, but if it is a typical system, there are any number of other file systems that might be mounted. Without knowing the fstab and path to the file, EXT4 isn't a guarantee.
If there is a chance of remote file system usage, that could be a problem. I don't know that it would return that exact error message, but WAL especially is intended to be used exclusively by processes on a single host due to shared memory usage.
(3) By Warren Young (wyoung) on 2021-05-31 01:30:33 in reply to 1 [link] [source]
You'll be far more likely to get a fix if you can post a short program that reproduces either symptom.
Beware: 10 lines of user-written code that requires half a gig of some non-C development environment to be installed plus who knows how many library dependencies doesn't count as "short program."
I say this because you speak vaguely of "bindings," which tells us you aren't using the SQLite C interface at all. That's fine, but since the SQLite developers can't fix things that aren't among the code bases they maintain, until you can distill the problem demonstration to code that squarely puts the blame on one of those code bases, you're going to be in a finger-pointing situation.
This isn't to say that you need to write your demonstration program in C, but if you can show the symptom that way, it'd be more likely to result in a fix than a demo in some other language that has to dig through multiple layers of "bindings" to get down to the C layer. One of those other layers could be what's actually at fault.
(4) By Warren Young (wyoung) on 2021-05-31 01:33:01 in reply to 2 [link] [source]
That's not a strong enough warning: attempting to use WAL over a network link will cause failures. Quoting disadvantage point 2 in the WAL docs: "WAL does not work over a network filesystem." No could-be may-be here at all.
(5) By Scott Robison (casaderobison) on 2021-05-31 02:15:12 in reply to 4 [link] [source]
I was hedging because it seems to me that if multiple processes open a remote database in WAL mode from a single host, and no other connections are open, then they'll all be sharing the same memory. That might "work" for sufficiently fuzzy values of "work".
But yes, given that remote file systems are never recommended, your point is well made.
(6) By aryairani on 2021-06-01 04:34:57 in reply to 3 [link] [source]
Hi Warren and thanks;
I get what you're saying. I am embedding the sqlite 3.28.0 C amalgamation in a custom build of db library used by my non-C application, and I've got all of these open for editing for debugging purposes. i.e. I am calling the C api, but I wasn't the author of all of the code involved. So... somewhere in between black-box non-C bindings and actually writing a C/C++ program.
I assume the problem is not a sqlite problem (speaking of finger-pointing) and more of an "I'm not using it right problem", and was hoping it was a common user/developer error other than the nfs issue we were all thoroughly warned about — I'll spare everyone the fstab, but the database in question is 100% on an ext4 volume on an Ubuntu host and not on nfs; :) the database for my optimize+WAL issue is 100% on an apfs volume on a macOS host.
Or hoping that there might be something more I can do to get more details back from sqlite; beyond calling
sqlite3_extended_errcode, which still doesn't seem to giving me anything more than
SQLITE_IOERR when the issue occurs.
So, in summary: I'll try to minimize better, ideally reproducing in C or via the sqlite3 shell, but if someone thinks of anything in the mean time <3
Would you recommend that I reply to this thread with those, or do threads tend to die quickly and I should open a new one? Do people ever submit relevant db files?
(7) By aryairani on 2021-06-01 04:35:52 in reply to 2 [link] [source]
No, we looked at the fstab and path to the file, and it's definitely ext4. 😬
(8) By Ryan Smith (cuz) on 2021-06-01 14:19:37 in reply to 6 [link] [source]
Submitting DB files happen often, though not able to attach it to the forum directly, you will need to host it somewhere or use a file-share service.
They are usually quite helpful in getting directly to the problem.
Sometimes DBs contain sensitive information, please make a version where such info is obfuscated (a few good UPDATE statements will do the trick), while ensuring that the problem still manifests.
Towards your other points: Yes, there are often small things a programmer might not know at the time that would improve efficiency or speed significantly. We are all too happy to point these out, but it is hard to guess at what they might be for someone else. Code-snippets, SQL statements and actual DB content are most helpful in illuminating these.
Threads here have no life-limit, post as much as you like, or start a new thread. If the new content really is on the same theme, or found a solution to the posted problem, it is nice to post it in the same thread. It makes it very easy for someone else who found the thread, perhaps looking for the same solution, to follow. Conversely, it is considered rude to hijack a thread for a different discussion.
Good luck. :)