LinuxQuestions.org - [SOLVED] Can I open sqlite datbase in read-only mode?

- Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)

- - Can I open sqlite datbase in read-only mode? (https://www.linuxquestions.org/questions/linux-server-73/can-i-open-sqlite-datbase-in-read-only-mode-4175578075/)

Can I open sqlite datbase in read-only mode?

I'm running sqlite 3.7.17 on Slackware64 14.1, kernel 3.10.17. I'm open a database at the command line as `sqlite3 dbname`. Normally it works, but occasionally I get the message, "Error: database is locked", which is understandable since I'm not the only one accessing the database.

Is there a command line option to access the database in read-only mode? I didn't see anything in the man page, but seems like a obvious option.

Per the sqlite3 documentation, I use the following syntax to open a database (sqlite3_db.db) in read-only mode

Code:

sqlite3 sqlite3_db.db?mode=ro

Quote:

Originally Posted by weibullguy (Post 5534996)

Per the sqlite3 documentation, I use the following syntax to open a database (sqlite3_db.db) in read-only mode

Code:

sqlite3 sqlite3_db.db?mode=ro

Doesn't work for me:

Code:

$ sqlite3 "/mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/local.sqlite?mode=ro" "select distinct value from cal_properties where key = 'CATEGORIES'"



Error: unable to open database "/mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/local.sqlite?mode=ro": unable to open database file

Your documentation link is for a C programming API. Are you sure this would work on the command line?

Try

Code:

cp /mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/local.sqlite $HOME

in terminal.
Then try to open that copy with

Code:

sqlite3 $HOME/local.sqlite

I tried the terminal command

Code:

sqlite3 /home/jj/.thunderbird/asdfqasdfs.default/calendar-data/local.sqlite?mode=ro

and I got an sqlite> prompt but I didn't do any db commands after that (dont' know how)
I did a .tables on the open db and there weren't any I guess. Back to the sqlite3> prompt.

Quote:

Originally Posted by Habitual (Post 5535068)

Try

Code:

cp /mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/local.sqlite $HOME

in terminal.
Then try to open that copy with

Code:

sqlite3 $HOME/local.sqlite

Hmmm, that would probably work, but the DB file is 5.6GB and I have multiple of these to do. The lock conflict doesn't happen often enough to justify copying all these DB every 10 minutes (which is how often I run the job). Rather odd that the command doesn't have a r/o mode.

I'll post something to the sqlite mailing list and see what they say.

Quote:

Originally Posted by mfoley (Post 5535515)

Ouch.

Found the answer on sqlite-users mailing list:

sqlite3 -cmd ".timeout 5000" "file:/pathto/mydb?mode=ro"

weibullguy was close, but apparently, adding the 'mode' parameter only works if the database is specified in URI format:

Quote:

"The advantage of using a URI filename is that query parameters on the URI can be used to control details of the newly created database connection. For example, an alternative VFS can be specified using a "vfs=" query parameter. Or the database can be opened read-only by using "mode=ro" as a query parameter."

https://www.sqlite.org/uri.html

For URI format, the "file:" prefix is required. Specified that way, the read-only mode works.

But, just because the file is opened in read-only, doesn't mean there won't be a conflict with a DB writer which, apparently in the case of Thunderbird, wants to open the database exclusively. Therefore, the -cmd ".timeout <ms>" parameter is useful. This means if the DB is locked, sqlite3 will retry the specified number of milliseconds before giving up.

I think together these takes care of my issue. Only time will tell!