sqlite 3.22.0 read-only WAL mode clarifications

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite 3.22.0 read-only WAL mode clarifications

Leonard Lausen
sqlite 3.22.0 added the ability to read from WAL mode databases even if
the application lacks write permission on the database and its
containing directory, as long as the -shm and -wal files exist in that
directory.

Even though the wiki page states that WAL does not work over a network
filesystem, I am able to use WAL over a network filesystem when using
sqlite 3.22.0 as long as the respective -shm and -wal files exist. (This
may or may not work with earlier versions.)

Could someone clarify if this is intended or may result in database
corruption? (I am only reading from the database on the remote host)

Is there some way to force the -shm and -wal files to always be present?

Best regards
Leonard

PS: The details of the network filesystem mount: hostname:/home/data on
/home/hostname type nfs4
(ro,relatime,vers=4.1,rsize=1048576,wsize=1048576,namlen=255,hard,proto=tcp,port=0,timeo=600,retrans=2,sec=sys,clientaddr=IPADDR,local_lock=none,addr=IPADDR)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.22.0 read-only WAL mode clarifications

Richard Hipp-3
On 2/9/18, Leonard Lausen <[hidden email]> wrote:

> sqlite 3.22.0 added the ability to read from WAL mode databases even if
> the application lacks write permission on the database and its
> containing directory, as long as the -shm and -wal files exist in that
> directory.
>
> Even though the wiki page states that WAL does not work over a network
> filesystem, I am able to use WAL over a network filesystem when using
> sqlite 3.22.0 as long as the respective -shm and -wal files exist. (This
> may or may not work with earlier versions.)
>
> Could someone clarify if this is intended or may result in database
> corruption? (I am only reading from the database on the remote host)

Wal relies on a small amount of shared memory to coordinate activities
between clients.  On unix, the shared memory is implemented by calling
mmap() on the -shm file.

If two or more clients connect from different hosts, then obviously
they cannot share the same memory.  In that case, WAL will
malfunction.

It may have worked for you in your test.  But that just means you
didn't test it enough.

>
> Is there some way to force the -shm and -wal files to always be present?
>

https://www.sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpersistwal
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.22.0 read-only WAL mode clarifications

Simon Slavin-3
In reply to this post by Leonard Lausen


On 9 Feb 2018, at 10:50am, Leonard Lausen <[hidden email]> wrote:

> sqlite 3.22.0 added the ability to read from WAL mode databases even if
> the application lacks write permission on the database and its
> containing directory, as long as the -shm and -wal files exist in that
> directory.
>
> Even though the wiki page states that WAL does not work over a network
> filesystem, I am able to use WAL over a network filesystem when using
> sqlite 3.22.0 as long as the respective -shm and -wal files exist. (This
> may or may not work with earlier versions.)

Yes, you are getting results for the pattern of access you tried.  That doesn't mean you'll not start getting error messages or corrupted files when you try something else.  The problem is not with reading files, it's with locking.

> Could someone clarify if this is intended or may result in database
> corruption? (I am only reading from the database on the remote host)

You note above unusual circumstances: "even if the application lacks write permission on the database and its containing directory".  

Will any of the processes accessing the database have write permission ?  If not, if they're all just reading the existing database, then there's no opportunity for corruption.  Think of it as

1) Reading a database while it's being written to may yield corrupt results.  SQLite uses locking to avoid this.
2) Two processes writing a database at once will corrupt the database.  SQLite uses locking to avoid this.
3) Locking does not work properly across a network

and you'll figure out the danger scenarios.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.22.0 read-only WAL mode clarifications

Leonard Lausen
Hey Simon,

thanks for your clarification.

> Will any of the processes accessing the database have write permission
> ? If not, if they're all just reading the existing database, then
> there's no opportunity for corruption. Think of it as

All of the processes (on "main" and remote host) have read and write
access, though I am only reading data in the processes accessing the db
on a network filesystem. So it may be safer to open the database with
SQLITE_OPEN_READONLY on the remote host.

> 1) Reading a database while it's being written to may yield corrupt results.  SQLite uses locking to avoid this.
> 2) Two processes writing a database at once will corrupt the database.  SQLite uses locking to avoid this.
> 3) Locking does not work properly across a network

Thanks for making me aware of potential corrupt results on read.

Due to the potential corruption I changed my scripts to ssh into the
main host and create a backup of the database with 'journal_mode delete'
that can then safely be used to read data even when on a network
filesystem.

Best regards
Leonard
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite 3.22.0 read-only WAL mode clarifications

Simon Slavin-3
On 10 Feb 2018, at 7:07am, Leonard Lausen <[hidden email]> wrote:

> thanks for your clarification.

You're welcome.  A couple of things you wrote make me think I phrased things poorly so I'm just taking this opportunity to illustrate what I wrote.

>> Will any of the processes accessing the database have write permission
>> ? If not, if they're all just reading the existing database, then
>> there's no opportunity for corruption. Think of it as
>
> All of the processes (on "main" and remote host) have read and write
> access, though I am only reading data in the processes accessing the db
> on a network filesystem. So it may be safer to open the database with
> SQLITE_OPEN_READONLY on the remote host.

SQLITE_OPEN_READONLY on the remote processes won't change anything important given that the remote processes aren't making changes anyway.  What's important is that nothing is making changes to the database while it's being read.  Even if the remote processes are only reading, not writing, they can still get bad data if a local process is making changes to the database at the same time.

>> 1) Reading a database while it's being written to may yield corrupt results. SQLite uses locking to avoid this.
>> 2) Two processes writing a database at once will corrupt the database.  SQLite uses locking to avoid this.
>> 3) Locking does not work properly across a network
>
> Thanks for making me aware of potential corrupt results on read.
>
> Due to the potential corruption I changed my scripts to ssh into the
> main host and create a backup of the database with 'journal_mode delete'
> that can then safely be used to read data even when on a network
> filesystem.

The journal mode of the database doesn't matter.  It can still provide corrupted data if locking isn't working properly.

Here's an example of what can happen if locking isn't working properly.  This is contrived and probably fake (in real life things might not work like this), but it demonstrates a problem in a simple way.

Table "people":
Anna, 28
Bertie, 40
Edna, 42
Fred, 88
Gillian, 90
An index on table "people" of (age)

Process 1 (accessing the file locally, read-write):
        UPDATE people SET age="38" WHERE name="Fred"
Process 2 (accessing the file remotely, read-only):
        SELECT name,age FROM people ORDER BY age

Suppose the two happen with timing such that the UPDATE is written to the file when the SELECT is between "Bertie" and "Edna"  The "Fred" row will not be returned by the SELECT !

Now carry this through to how in the file pointers are used to point to rows, and you can see similar problems even if you are just inserting a new row or deleting an old one.  Rather than getting incorrect data, you can get a result indicating that the file is corrupt.  But you might not, you might just get incorrect data as in the above example.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users