Errors opening WAL-based databases with SQLITE_OPEN_READONLY

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

Errors opening WAL-based databases with SQLITE_OPEN_READONLY

Jens Alfke-2
I'm aware that a database in WAL mode can't be opened read-only if its directory isn't writeable[1]. However, I'm unexpectedly getting errors opening a database when the directory _is_ writeable, but the database is opened read-only. Specifically:

* The database file is in WAL mode.
* The -wal and -shm files do not exist (i.e. the database was previously closed cleanly.)
* The directory containing the database is writeable (i.e. the process is allowed to create files in it.)
* The database is opened with sqlite3_open_v2, using the SQLITE_OPEN_READONLY flag.

In this situation, any SQLite call that actually accesses the database will fail with SQLITE_CANTOPEN.

It seems as though SQLite decides that because the handle is read-only, it's not allowed to create the -shm file. There's some logic to this, but I can't find any mention of it in the documentation.

(I'm using SQLite 3.16 on macOS 10.12.4.)

—Jens

[1]: https://www.sqlite.org/wal.html#readonly
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Errors opening WAL-based databases with SQLITE_OPEN_READONLY

J. King-3
On April 10, 2017 5:14:23 PM EDT, Jens Alfke <[hidden email]> wrote:

>I'm aware that a database in WAL mode can't be opened read-only if its
>directory isn't writeable[1]. However, I'm unexpectedly getting errors
>opening a database when the directory _is_ writeable, but the database
>is opened read-only. Specifically:
>
>* The database file is in WAL mode.
>* The -wal and -shm files do not exist (i.e. the database was
>previously closed cleanly.)
>* The directory containing the database is writeable (i.e. the process
>is allowed to create files in it.)
>* The database is opened with sqlite3_open_v2, using the
>SQLITE_OPEN_READONLY flag.
>
>In this situation, any SQLite call that actually accesses the database
>will fail with SQLITE_CANTOPEN.
>
>It seems as though SQLite decides that because the handle is read-only,
>it's not allowed to create the -shm file. There's some logic to this,
>but I can't find any mention of it in the documentation.
>
>(I'm using SQLite 3.16 on macOS 10.12.4.)
>
>—Jens
>
>[1]: https://www.sqlite.org/wal.html#readonly
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

WAL databases must be opened read/write because readers need to write to the shm file. It is in the documention on WAL mode:

It is not possible to open read-only WAL databases. The opening process must have write privileges for "-shm" wal-index shared memory file associated with the database, if that file exists, or else write access on the directory containing the database file if the "-shm" file does not exist.
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Errors opening WAL-based databases with SQLITE_OPEN_READONLY

Jens Alfke-2

> On Apr 10, 2017, at 2:21 PM, J. King <[hidden email]> wrote:
>
> It is not possible to open read-only WAL databases. The opening process must have write privileges for "-shm" wal-index shared memory file associated with the database, if that file exists, or else write access on the directory containing the database file if the "-shm" file does not exist.

Yes, that's the documentation I was referring to. It only states that the process must be able to create/write the -shm file. It doesn't say anything about opening the database with SQLITE_OPEN_READONLY.

IMHO, since the -shm file is only a cross-process temporary index of the WAL file, SQLite should be allowed to create this file even if the database itself is read-only. Otherwise opening a database read-only will sometimes succeed and sometimes fail on a writeable filesystem, depending on whether or not the -shm file already exists. This caused me some confusion this morning — I was running new test code that reads a number of existing sample databases, and some of them would mysteriously fail to open. Even weirder, after I inspected a problem database using the `sqlite3` tool, the problem with it went away! (Of course, the tool was creating the missing -shm and -wal files when it opened the file…)

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

Re: Errors opening WAL-based databases with SQLITE_OPEN_READONLY

Simon Slavin-3

On 11 Apr 2017, at 12:22am, Jens Alfke <[hidden email]> wrote:

> It only states that the process must be able to create/write the -shm file. It doesn't say anything about opening the database with SQLITE_OPEN_READONLY

The problem here is not whether you’re allowing SQLite to make changes to your data, but whether the OS is allowing SQLite to write to its files.  You should regard SQLITE_OPEN_READONLY as telling SQLite whether it’s allowed to change the database.  It doesn’t tell SQLite how it should work internally, just whether it’s allowed to execute commands like CREATE and UPDATE.

Don’t try to open a database which has WAL mode set unless you have write permission to the database file, journal file and shared memory file.  (Or create new files, then write to them).

It may be that the documentation should be updated to reflect this.

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
|  
Report Content as Inappropriate

Re: Errors opening WAL-based databases with SQLITE_OPEN_READONLY

Simon Slavin-3

On 11 Apr 2017, at 12:33am, Simon Slavin <[hidden email]> wrote:

> It may be that the documentation should be updated to reflect this.

After another look I’m going to withdraw everything I wrote in the thread.

<https://sqlite.org/wal.html> in section 1 says:

"It is not possible to open read-only WAL databases. The opening process must have write privileges for "-shm" wal-index shared memory file associated with the database, if that file exists, or else write access on the directory containing the database file if the "-shm" file does not exist."

J. King posted that earlier in the thread, but I didn’t see it before I replied.  I think it’s clear enough.  It’s the permissions granted by the OS that’s important, not the mode you tell SQLite to open the file in.

However, I went back and read your original post and you weren’t asking what I thought you were asking.  You may have found something strange going on.  My only doubt is whether the individual files (database and -shm file) were marked read-only.  If not, then there seems to be something wrong somewhere.

Sorry I wasted time upthread.

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