Thanks for the hint about pragma QUERY_ONLY, that might work but we will have to rethink the database file management. The database is currently owned by root and has sensible unix permissions of rw, r, r (ie only root has write permission)
We can't run the intended service as root because that would allow it to trash anything f it got attacked so we might need to do something clever with group membersip and arrange for the database permissions to change to rw, rw, r so that the service has read-write access to the database files. Regarding the other aspects to the responses. 1) There seems to be a misunderstanding about the term recovery. It does not imply that the database is corrupt for sqlite From https://www.sqlite.org/walformat.html#recovery "Recovery is the process of rebuilding the WAL-index so that it is synchronized with the WAL. Recovery is run by the first thread to connect to a WAL-mode database." We have been running the current software using the database for years and I would be very surprised if it is continually being corrupted and then fixed up in the background and this is the first time we have noticed. 2) From sqlite3 source code comments where SQLITE_READONLY_RECOVERY is raised, it also seems that it will be raised if the WAL header indicates that it is currently "unreliable" and that this can happen if there is currently an active writer (ie normal condition) but the reader doesn't have permission to acquire a write-lock to confirm that this is the case. sqlite3.c around line 59960 (this is post amalgamation so not sure where it is in git repository) " /* If the first page of the wal-index has been mapped, try to read the ** wal-index header immediately, without holding any lock. This usually ** works, but may fail if the wal-index header is corrupt *or currently ** being modified by another thread or process.* */ badHdr = (page0 ? walIndexTryHdr(pWal, pChanged) : 1); /* If the first attempt failed, it might have been due to a race ** with a writer. So get a WRITE lock and try again. */ This case results in SQLITE_READONLY_RECOVER response NB. In my testing, I don't have write permission to the db file or the wal file or the shm file but they do exist so I meet at least one of the criteria specified in the documentation for read-only access to a WAL mode database (since Jan 2018) On Thu, 24 Jan 2019 at 15:37, Robert Searle <robert.sea...@taitradio.com> wrote: > Hi, > > I have an sqlite3 database (version 3.25.3) in tmpfs which has many > readers and writers. > > The database is running in WAL mode and seems to work efficiently in that > mode. > Since the database files are in a memory based file-system, we don't care > about the usual corruption on power-cycle issues because we rebuild a clean > database from scratch when we power up again. > > We have recently started trying to provide read-only access to the > database (service run as user with group/other read access permissions > under Linux, service not database owner) and occasionally get either > SQLITE_READONLY_RECOVERY or SQLITE_READONLY_CANTINIT responses to select > statements ie. Not attempting to modify the database - The service > typically runs for a few minutes trying to read the database about 1-2 > times per second before asserting. > The service is 'nicer' than the other reader/writers. > Each writer is responsible for a distinct subset of the values in the > database. ie. Every variable/row in the database has a unique owner that > can change the value. There are multiple readers for each variable/row. > > These response codes are currently treated as cause for assert (accept > only SQLITE_OK or SQLITE_DONE responses, assert on all other response > codes). > The sqlite3 documentation indicates that these errors indicate possible > database corruption, the sqlite3 source code comments seem to indicate that > they can also occur on race conditions with a writer. > > Questions: > 1) Should we treat these responses as an invitation to retry later rather > than asserts? > 2) Do these responses indicate that the variable(s) requested in the > select have not been returned? ie we need to accept the possibility that > the requested values are temporarily unavailable. > 3) Are there any configuration settings on the database that might reduce > the probability of occurrence? > 4) If there aren't any configuration settings, are there any usage > patterns to avoid or to embrace? > > > > -- > Robert Searle > > Lead Design Engineer > > Tait Communications > > DDI: +64 3 3570717 > > Email: robert.sea...@taitradio.com > > > > www.taitradio.com > -- Robert Searle Lead Design Engineer Tait Communications DDI: +64 3 3570717 Email: robert.sea...@taitradio.com www.taitradio.com -- This Communication is Confidential. We only send and receive email on the basis of the terms set out at www.taitradio.com/email_disclaimer <http://www.taitradio.com/email_disclaimer> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
