Re: [sqlite] WAL mode readonly errors to SELECT statements

[Peter da Silva]

The sensible permissions in this case would probably be rw-rw-r-- with the
same group as the service and owned by the service group. That is how group
permissions were designed to work.


>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode readonly errors to SELECT statements

[Robert Searle]

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

___

Re: [sqlite] WAL mode readonly errors to SELECT statements

[Simon Slavin]

On 24 Jan 2019, at 2:37am, Robert Searle  wrote:

> occasionally get either
> SQLITE_READONLY_RECOVERY or SQLITE_READONLY_CANTINIT responses to select
> statements

No programmer should be seeing these.  They indicate low-level errors that 
cannot be handled in a systematic manner.  Rather than dealing with them, 
figure out why they're happening in the first place.  You do not want to deal 
with a database which is constantly being corrupted, since recovery of it may 
lead to lost data/changes.

As temporary diagnostic measures, try one or both of

(A) Moving the database out of tmpfs to a normal drive
(B) Opening the database as read/write

See whether either/both of those make the fault go away.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode readonly errors to SELECT statements

[Clemens Ladisch]

Robert Searle wrote:
> 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

 says:
| The SQLITE_READONLY_RECOVERY error code indicates that a WAL mode
| database cannot be opened because the database file needs to be
| recovered and recovery requires write access but only read access is
| available.

| The SQLITE_READONLY_CANTINIT result code originates in the xShmMap
| method of a VFS to indicate that the shared memory region used by WAL
| mode exists buts its content is unreliable and unusable by the current
| process since the current process does not have write permission on
| the shared memory region.

> 1) Should we treat these responses as an invitation to retry later rather
> than asserts?

Waiting might work if some other process opens the database and actually
does the recovery.

> 2) Do these responses indicate that the variable(s) requested in the select
> have not been returned?

Error codes indicate that the call failed.  The query did not even begin
to execute.

> 3) Are there any configuration settings on the database that might reduce
> the probability of occurrence?

Open the database with write access (so that recovery can be done), but set
PRAGMA query_only.

> 4) If there aren't any configuration settings, are there any usage patterns
> to avoid or to embrace?

Don't corrupt the database in the first place.  ;-)
You aren't using WAL over a network, or across a VM boundary, are you?

Normally, recovery is needed if some writer crashes.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WAL mode readonly errors to SELECT statements

[Robert Searle]

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

-- 
This Communication is Confidential. We only send and receive email on the

basis of the terms set out at www.taitradio.com/email_disclaimer 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users