SQLite Forum

sqlite opens a database file in R/W mode, even if the file is read-only
Login

sqlite opens a database file in R/W mode, even if the file is read-only

(1) By odubaj2 on 2020-12-16 07:16:27 [link] [source]

Applications will succeed in opening a database for read/write and later fail when they try to update the database. It is a problem mainly regarding to nss and firefox in the future. The problem occurs in sqlite-3.34.0, in version 3.33.0 everything works fine.
Is this expected change of behaviour ? Many applications rely on the failure while opening read-only database files for R/W.

Thanks.

(2) By odubaj2 on 2020-12-23 06:52:50 in reply to 1 [link] [source]

gentle ping

(3) By Richard Hipp (drh) on 2020-12-23 13:13:45 in reply to 1 [link] [source]

This is the way SQLite has always worked, and it is by design. If you request to open a database read/write but the database file itself is read-only, then SQLite falls back to a read-only open. Most applications prefer it that way.

If it is important to you to know that the database is writable after you open it, then you can start a transaction and ask if the database is writable using sqlite3_db_readonly() interface.

(4) By anonymous on 2020-12-23 16:59:07 in reply to 3 [source]

I prefer the 3.34 behaviour but doesn't this break backward compatibility?

With the readonly attribute set on Windows:

SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open ./db/test2.db
Error: unable to open database "./db/test2.db": unable to open database file
sqlite>
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .open ./db/test2.db
sqlite> .databases
main: D:\SQLite32\db\test2.db r/o
sqlite>

f. The .databases dot-command now shows the status of each database file as determined by sqlite3_db_readonly() and sqlite3_txn_state(). does not make this change in behaviour explicit, that is, that database error handling is now obsolete when the database exists.

(6) By Richard Hipp (drh) on 2020-12-23 17:49:27 in reply to 4 [link] [source]

I'm sorry, but I do not understand what your complaint is. Can you be more specific?

(7) By anonymous on 2020-12-23 18:51:28 in reply to 6 [link] [source]

Not so important; please ignore. As I mentioned, I prefer the 3.34 behaviour.

(8) By Richard Hipp (drh) on 2020-12-23 19:46:24 in reply to 7 [link] [source]

I don't find any difference in behavior for 3.34 versus earlier versions, is my point.

(9) By anonymous on 2020-12-23 20:04:12 in reply to 8 [link] [source]

Using Windows, with the database file (./db/test2.db) having it readonly attribute set:

v3.33 .opening reports

Error: unable to open database "./db/test2.db": unable to open database file

As the file is not .opened, .databases will not show anything.

v3.34 .opening reports no error & .databases shows

main: D:SQLite32dbtest2.db r/o

The difference in behaviour is that v3.33 would NOT open a readonly database file but v3.34 does open a readonly database file.

(10) By Keith Medcalf (kmedcalf) on 2020-12-23 21:26:37 in reply to 9 [link] [source]

To summarize:

it appears that in 3.33 if one called the sqlite3_open* API with the default flags, or SQLITE_OPEN_READWRITE, that the open would fail if READ and WRITE access could not be obtained.

it appears that in 3.34 (and the current trunk) that if one called the sqlite3_open* API with the default flags, or SQLITE_OPEN_READWRITE that the file is opened even if only READ access can be granted, as if SQLITE_OPEN_READONLY had been specified.

(11) By anonymous on 2020-12-23 22:52:52 in reply to 10 [link] [source]

v3.34 inherits the read only flag set at operating system level (Windows File Explorer) on an SQLite3 database without the --readonly flag being specified AND opens the database in readonly mode.

v3.33 simply refuses to open such a file.

(12) By Warren Young (wyoung) on 2020-12-24 09:26:27 in reply to 11 [link] [source]

This behavior change is on purpose, in reaction to threads like this one.

It's a good thing. SQLite can't know what SQL statements or queries you will execute, so why should a read-only attribute on the DB file — or its journal, or its parent directory, or its whole filesystem — be an impediment to merely opening the DB? If the subsequent query is SELECT COUNT(*) FROM foo, we don't need write access to the DB at all! As pointed out in the linked thread, WAL mode still allows proper locking in this case; there is not ACID failure simply because one client has the DB opened read-only.

I expect there would be more people upset if the new behavior was reverted than there are people caught out by the new behavior.

Indeed, this feels like an XKCD Workflow problem to me: https://xkcd.com/1172/

(13) By David Raymond (dvdraymond) on 2020-12-29 18:22:36 in reply to 4 [link] [source]

I'm not seeing this behavior on my own copies of the Windows CLI tools. I get an error if I use a backslash instead of a forward slash, but otherwise opens just fine in old versions.

C:\Data\Programs\SQLite\db>attrib
A    R               C:\Data\Programs\SQLite\db\test2.db

C:\Data\Programs\SQLite\db>cd..

C:\Data\Programs\SQLite>.\3.33.0\sqlite-tools-win32-x86-3330000\sqlite3.exe
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open .\db\test2.db
Error: unable to open database ".db     est2.db": unable to open database file
sqlite> .open ./db/test2.db
sqlite> .tables
foo
sqlite> .databases
main: C:\Data\Programs\SQLite\db\test2.db
sqlite> .exit

C:\Data\Programs\SQLite>.\3.34.0\sqlite-tools-win32-x86-3340000\sqlite3.exe
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open ./db/test2.db
sqlite> .databases
main: C:\Data\Programs\SQLite\db\test2.db r/o
sqlite>

(5) By Keith Medcalf (kmedcalf) on 2020-12-23 17:22:18 in reply to 3 [link] [source]

I do not think you need to start a transaction to use the sqlite3_db_readonly() API. It appears to work correctly without having to start a transaction first.