LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 04-22-2016, 01:50 AM   #1
mfoley
Senior Member
 
Registered: Oct 2008
Location: Columbus, Ohio USA
Distribution: Slackware
Posts: 2,539

Rep: Reputation: 177Reputation: 177
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.
 
Old 04-22-2016, 08:07 AM   #2
weibullguy
ReliaFree Maintainer
 
Registered: Aug 2004
Location: Kalamazoo, Michigan
Distribution: Slackware 14.2
Posts: 2,815
Blog Entries: 1

Rep: Reputation: 261Reputation: 261Reputation: 261
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
 
Old 04-22-2016, 11:28 AM   #3
mfoley
Senior Member
 
Registered: Oct 2008
Location: Columbus, Ohio USA
Distribution: Slackware
Posts: 2,539

Original Poster
Rep: Reputation: 177Reputation: 177
Quote:
Originally Posted by weibullguy View Post
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?
 
Old 04-22-2016, 11:43 AM   #4
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
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.
 
Old 04-23-2016, 12:40 PM   #5
mfoley
Senior Member
 
Registered: Oct 2008
Location: Columbus, Ohio USA
Distribution: Slackware
Posts: 2,539

Original Poster
Rep: Reputation: 177Reputation: 177
Quote:
Originally Posted by Habitual View Post
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.
 
Old 04-23-2016, 01:16 PM   #6
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
Quote:
Originally Posted by mfoley View Post
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.
Ouch.
 
Old 04-25-2016, 10:56 PM   #7
mfoley
Senior Member
 
Registered: Oct 2008
Location: Columbus, Ohio USA
Distribution: Slackware
Posts: 2,539

Original Poster
Rep: Reputation: 177Reputation: 177
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!

Last edited by mfoley; 04-25-2016 at 10:59 PM.
 
  


Reply

Tags
readonly, sqlite


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
how to open places.sqlite... un1x Linux - General 3 01-18-2016 02:38 PM
MS office files open in read only mode from samba share Toushi Linux - Networking 6 05-19-2011 11:39 AM
LXer: Sqlite-Commander - A ncurses based tool to display the records and tables of a sqlite database LXer Syndicated Linux News 0 01-02-2011 08:11 AM
Sample SQLite read, write, select, compare statements for C++ miamagoo Programming 5 06-16-2010 09:05 AM
Open office read only, K-write read/write mode lwtvh Linux - Newbie 1 07-19-2003 11:33 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 06:55 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration