[sqlite] sqlite3 command line, read-only

[Mark Foley]

I'm new to the list, so apologies if this has been posted before.

I am using sqlite3 3.7.17 on Linux Slackware64 14.1. I use the sqlite3 command
every 10 minutes to query several Thunderbird calendar databases. Occasionally, I
have a message, "Error: database is locked"; understandable since Thunderbird
is occasionally updating its database.

First, is there a way to open a database in readonly mode from the command line?
I 've searched the man page and googled, but I find nothing. I've tried opening
the database as dbname?mode=ro, but that doesn't work. The C API has such a
mode, but apparently not the command line.

If the answer to that question is "no", I would like to request that a readonly
switch be added to future versions of sqlite. I think this would be generally
useful and couldn't be that difficult to implment.

Thanks, Mark

[Dominique Devienne]

Post by Mark Foley
I'm new to the list, so apologies if this has been posted before.
I am using sqlite3 3.7.17 on Linux Slackware64 14.1. I use the sqlite3 command
every 10 minutes to query several Thunderbird calendar databases. Occasionally, I
have a message, "Error: database is locked"; understandable since Thunderbird
is occasionally updating its database.
First, is there a way to open a database in readonly mode from the command line?
I 've searched the man page and googled, but I find nothing. I've tried opening
the database as dbname?mode=ro, but that doesn't work. The C API has such a
mode, but apparently not the command line.

Perhaps you're missing the leading file: ? I believe it is required. --DD

C:\Users\DDevienne>sqlite3 new.db
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
sqlite> create table t (v);
sqlite> insert into t values (1), (2), (3);
sqlite> .q

C:\Users\DDevienne>sqlite3 file:new.db?mode=ro
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
sqlite> select * from t;
1
2
3
sqlite> update t set v = 4;
Error: attempt to write a readonly database
sqlite>

[Mark Foley]

Post by Dominique Devienne
Perhaps you're missing the leading file: ? I believe it is required. --DD

No, unless I'm putting it in the wrong place. As I wrote I did:

$ sqlite3 "myDbpath?mode=ro"

I also tried escaping the '?' as '\?', no go.

Should this work from the command line? Should the '?' go somewhere else? Can
you give a working commandline example?

THX --Mark

-----Original Message-----

Post by Dominique Devienne
Date: Mon, 25 Apr 2016 14:41:30 +0200
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Mark Foley
I'm new to the list, so apologies if this has been posted before.
I am using sqlite3 3.7.17 on Linux Slackware64 14.1. I use the sqlite3 command
every 10 minutes to query several Thunderbird calendar databases. Occasionally, I
have a message, "Error: database is locked"; understandable since Thunderbird
is occasionally updating its database.
First, is there a way to open a database in readonly mode from the command line?
I 've searched the man page and googled, but I find nothing. I've tried opening
the database as dbname?mode=ro, but that doesn't work. The C API has such a
mode, but apparently not the command line.

Perhaps you're missing the leading file: ? I believe it is required. --DD
C:\Users\DDevienne>sqlite3 new.db
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
sqlite> create table t (v);
sqlite> insert into t values (1), (2), (3);
sqlite> .q
C:\Users\DDevienne>sqlite3 file:new.db?mode=ro
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
sqlite> select * from t;
1
2
3
sqlite> update t set v = 4;
Error: attempt to write a readonly database
sqlite>
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Dominique Devienne]

Post by Dominique Devienne
Perhaps you're missing the leading file: ? I believe it is required. --DD

$ sqlite3 "myDbpath?mode=ro"
Please (re)read the example I included. --DD

C:\Users\DDevienne>sqlite3 *file:*new.db?mode=ro
...
sqlite> update t set v = 4;
*Error: attempt to write a readonly database*
sqlite>

[Mark Foley]

Post by Dominique Devienne
Please (re)read the example I included. --DD
C:\Users\DDevienne>sqlite3 *file:*new.db?mode=ro

Sorry, perhaps I'm a bit obtuse. I don't see how your example differs from mine:

sqlite "mydbpath?mode=ro"

Are you referring to the "*file:*" syntax?

--Mark

-----Original Message-----

Post by Dominique Devienne
Date: Mon, 25 Apr 2016 15:14:56 +0200
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Dominique Devienne
Perhaps you're missing the leading file: ? I believe it is required. --DD

$ sqlite3 "myDbpath?mode=ro"
Please (re)read the example I included. --DD
C:\Users\DDevienne>sqlite3 *file:*new.db?mode=ro
...
sqlite> update t set v = 4;
*Error: attempt to write a readonly database*
sqlite>
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Dominique Devienne]

Post by Mark Foley

Post by Dominique Devienne
Please (re)read the example I included. --DD
C:\Users\DDevienne>sqlite3 *file:*new.db?mode=ro

sqlite "mydbpath?mode=ro"
Are you referring to the "*file:*" syntax?

Precisely: https://www.sqlite.org/uri.html --DD

Since SQLite always interprets any filename that does not begin with "file:"

Post by Mark Foley
as an ordinary filename regardless of the URI setting

[Mark Foley]

I'm running this on Linux. That Windows notation doesn't work.

sqlite3 "*file:*/mnt/tmp/Users/.../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
"*file:*/mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/local.sqlite\?mode=ro":
unable to open database file

--Mark

-----Original Message-----
Date: Mon, 25 Apr 2016 15:47:27 +0200
From: Dominique Devienne <***@gmail.com>
To: SQLite mailing list <sqlite-***@mailinglists.sqlite.org>
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Mark Foley

Post by Dominique Devienne
Please (re)read the example I included. --DD
C:\Users\DDevienne>sqlite3 *file:*new.db?mode=ro

sqlite "mydbpath?mode=ro"
Are you referring to the "*file:*" syntax?

Precisely: https://www.sqlite.org/uri.html --DD

Since SQLite always interprets any filename that does not begin with "file:"

Post by Mark Foley
as an ordinary filename regardless of the URI setting

[Dominique Devienne]

Post by Mark Foley
I'm running this on Linux. That Windows notation doesn't work.
sqlite3
"*file:*/mnt/tmp/Users/.../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
unable to open database file

There's no "Windows notation". Nor is it *file:*, it's just file: (file
followed by colon, just like http: in URL)

The first example didn't have it, and I added some emphasis since you
missed it the first time around.
But if you mail client is ascii-only, the emphasis shows up with the extra
"stars".

On Linux, just use single-quotes to avoid escaping the question mark.
sqlite3 'file:path?mode=ro'

I'd show you if the ancient SQLite3 on my RH6.5 wasn't so old it doesn't
support multi-row inserts or URI filenames. --D

[Mark Foley]

Dominique,

Nope: still error:

sqlite3 \
'*file:*/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
"*file:*/mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/local.sqlite?mode=ro":
unable to open database file

--Mark

-----Original Message-----

Date: Mon, 25 Apr 2016 16:52:24 +0200
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Mark Foley
I'm running this on Linux. That Windows notation doesn't work.
sqlite3
"*file:*/mnt/tmp/Users/.../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
unable to open database file

There's no "Windows notation". Nor is it *file:*, it's just file: (file
followed by colon, just like http: in URL)
The first example didn't have it, and I added some emphasis since you
missed it the first time around.
But if you mail client is ascii-only, the emphasis shows up with the extra
"stars".
On Linux, just use single-quotes to avoid escaping the question mark.
sqlite3 'file:path?mode=ro'
I'd show you if the ancient SQLite3 on my RH6.5 wasn't so old it doesn't
support multi-row inserts or URI filenames. --D
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by Mark Foley
Error: unable to open database
unable to open database file

That path looks like it has two components which change according to your installation: 'hcarr.HPRS.000' and '8sbpxrwj'. Try

ls /mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/

and see what you get. You may have to change it to suit your installation.

Simon.

[Mark Foley]

Try ls /mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/
and see what you get. You may have to change it to suit your installation.

$ ls -l
/mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/
total 5568
-rwxr-xr-x 0 root root 98304 2016-04-21 09:51 deleted.sqlite*
-rwxr-xr-x 0 root root 5603328 2016-04-22 14:18 local.sqlite*

This might be a bit misleading. The volume is mounted using the domain
administrator ID and PW, which it permitted as full access the the C: share on
that worksations:

$ mount //holly/C /mnt/tmp -o username=Administrator,password=adminpw

Neverthless, sometimes certain files can't be read or listed even by
administrator. Never really tried to sort that out as I am generally not
mounting workstation drives with the intention of changing files.

--Mark

-----Original Message-----

Date: Mon, 25 Apr 2016 17:15:20 +0100
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Mark Foley
Error: unable to open database
unable to open database file

That path looks like it has two components which change according to your installation: 'hcarr.HPRS.000' and '8sbpxrwj'. Try
ls /mnt/tmp/Users/hcarr.HPRS.000/AppData/Roaming/Thunderbird/Profiles/8sbpxrwj.default/calendar-data/
and see what you get. You may have to change it to suit your installation.
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Dominique Devienne]

Post by Mark Foley
sqlite3 \
'*file:*/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
unable to open database file

Are you trying to highlight something in the email, or do you really type
the stars/asterisks around file? You shouldn't.
Sorry, can't help you more, I'm afraid you're not reading me correctly, or
I'm not writing correctly for you. --DD

PS: See, no stars below

sqlite3 \
'file:/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'"

[Mark Foley]

Yes, I am typing the stars in. That was my question to you to which you replied
in your message of 15:47 +0200

Post by Dominique Devienne

Post by Mark Foley
Are you referring to the "*file:*" syntax?

Precisely: https://www.sqlite.org/uri.html --DD

I took you litterally, no I didn't look up the URL.

ANYWAY, Yes!!! That works removing the stars:

$ sqlite3 \
'file:/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'"
Orange Category
Green Category
Blue Category
Purple Category
Blue Category,Holidays
Personal

I *told* you I was obtuse!!!

So, I'll use that in combination with the "-cmd timeout=5000" just to be sure a writer
doesn't lock me out anyway!

Thanks a bunch --Mark

-----Original Message-----
Date: Mon, 25 Apr 2016 18:21:33 +0200
From: Dominique Devienne <***@gmail.com>
To: SQLite mailing list <sqlite-***@mailinglists.sqlite.org>
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Dominique Devienne
sqlite3 \
'*file:*/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
unable to open database file

Are you trying to highlight something in the email, or do you really type
the stars/asterisks around file? You shouldn't.
Sorry, can't help you more, I'm afraid you're not reading me correctly, or
I'm not writing correctly for you. --DD

PS: See, no stars below

sqlite3 \
'file:/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'"

[Mark Foley]

I'm back with more read-only issues.

Thanks to postings on this list, I've made progress, but still more issues.

Using the sqlite3 command-line, I'm attempted to open,
read-only, a database located on a Windows 7 workstations, from a Linux server.
I am mounting the Windows volume r/o:

mount //${workstation}/C /mnt/iCal -o ro

I am opening the database read-only, with timeout:

$ sqlite3 -separator '~' -cmd ".timeout 20000" "file:/mnt/tmp/Users.../calendar-date/local.sqlite?mode=ro"

So, why do I occasionally get these Error messages in my logfile?

2016-04-26 14:32 HPRS_Office_Calendar_Calendar.ics updated
Error: attempt to write a readonly database
Error: attempt to write a readonly database
Error: attempt to write a readonly database
Error: attempt to write a readonly database
Error: attempt to write a readonly database
2016-04-26 14:52 HPRS_Office_Calendar_Calendar.ics updated

First of all, why is it attempting to write anything if it's opened read-only?

Secondly, how could it ever succeed if the entire volume is read-only? Note that
there were no errors on the 1st and last examples. And, the "updated" message
refers to the output of the cron script, not an update to the database.

THX --Mark

[Scott Robison]

Post by Mark Foley
I'm back with more read-only issues.
Thanks to postings on this list, I've made progress, but still more issues.
Using the sqlite3 command-line, I'm attempted to open,
read-only, a database located on a Windows 7 workstations, from a Linux server.
mount //${workstation}/C /mnt/iCal -o ro
$ sqlite3 -separator '~' -cmd ".timeout 20000"

"file:/mnt/tmp/Users.../calendar-date/local.sqlite?mode=ro"

Post by Mark Foley
So, why do I occasionally get these Error messages in my logfile?
2016-04-26 14:32 HPRS_Office_Calendar_Calendar.ics updated
Error: attempt to write a readonly database
Error: attempt to write a readonly database
Error: attempt to write a readonly database
Error: attempt to write a readonly database
Error: attempt to write a readonly database
2016-04-26 14:52 HPRS_Office_Calendar_Calendar.ics updated
First of all, why is it attempting to write anything if it's opened read-only?
Secondly, how could it ever succeed if the entire volume is read-only? Note that
there were no errors on the 1st and last examples. And, the "updated" message
refers to the output of the cron script, not an update to the database.

Is there a hot journal file with the database? If so it may need to roll
back a transaction which will require write access anyway.

[Mark Foley]

Post by Scott Robison
Is there a hot journal file with the database? If so it may need to roll
back a transaction which will require write access anyway.

I don't know that answer. Would the journal file be an actual file in the same
folder as the database file? Certaily the database had changed in the 20 minutes
between 14:32 and 14:52.

So ... if the database needs to roll back (or commit) even if the user's access
is read-only, then it would always fail in this case, even if the DB owner on
the Windows box wasn't actually updating the database, right?

Can sqlite handle contention among multiple accessors with read/write if the
accessors are not on the same host? I.e. is locking intrinsic in sqlite and any
accessor from any host is able to determine and set a row/table lock?

--Mark

-----Original Message-----

Post by Scott Robison
Date: Tue, 26 Apr 2016 22:31:11 -0600
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Mark Foley
I'm back with more read-only issues.
Thanks to postings on this list, I've made progress, but still more

issues.

Post by Mark Foley
Using the sqlite3 command-line, I'm attempted to open,
read-only, a database located on a Windows 7 workstations, from a Linux

server.

Post by Mark Foley
mount //${workstation}/C /mnt/iCal -o ro
$ sqlite3 -separator '~' -cmd ".timeout 20000"

"file:/mnt/tmp/Users.../calendar-date/local.sqlite?mode=ro"

Post by Mark Foley
So, why do I occasionally get these Error messages in my logfile?
2016-04-26 14:32 HPRS_Office_Calendar_Calendar.ics updated
Error: attempt to write a readonly database
Error: attempt to write a readonly database
Error: attempt to write a readonly database
Error: attempt to write a readonly database
Error: attempt to write a readonly database
2016-04-26 14:52 HPRS_Office_Calendar_Calendar.ics updated
First of all, why is it attempting to write anything if it's opened

read-only?

Post by Mark Foley
Secondly, how could it ever succeed if the entire volume is read-only?

Note that

Post by Mark Foley
there were no errors on the 1st and last examples. And, the "updated"

message

Post by Mark Foley
refers to the output of the cron script, not an update to the database.

Is there a hot journal file with the database? If so it may need to roll
back a transaction which will require write access anyway.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by Mark Foley
I don't know that answer. Would the journal file be an actual file in the same
folder as the database file?

Yes. Given that your database file is called 'local.sqlite', look for other files in the same folder with names starting with 'local'.

It would also be useful for you to execute

PRAGMA journal_mode

when you have the database open and tell us what the response is. That tells us more about what journal files should exist and why your connection might be trying to write to them.

Simon.

[Rowan Worth]

Post by Mark Foley
Can sqlite handle contention among multiple accessors with read/write if the
accessors are not on the same host? I.e. is locking intrinsic in sqlite and any
accessor from any host is able to determine and set a row/table lock?

That depends on the journal mode, and the file systems used to access the
database.

WAL is not suitable for access via multiple hosts as it relies on shared
memory.

sqlite relies on the locking primitives provided by the file system - for
this to work across hosts the network file system has to plumb the locking
calls through to the host system correctly. For a quick test you can open
the sqlite3 shell on the same database on both the file server and remote
machine - run "BEGIN IMMEDIATE;" in one and then run "BEGIN IMMEDIATE;" in
the other. On the second attempt you should encounter "database is locked".
That demonstrates the network file system at least propagates locks, but
doesn't prove it behaves correctly in all scenarios (there may be race
conditions etc).

-Rowan

[J Decker]

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

"5. 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."

second, I'm pretty sure that WAL mode is written to the database, so
it remembers what journal mode it was using... which if it wasn't WAL
would require writing to the database.

[J Decker]

Post by Richard Hipp
https://www.sqlite.org/wal.html
"5. 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."
second, I'm pretty sure that WAL mode is written to the database, so
it remembers what journal mode it was using... which if it wasn't WAL
would require writing to the database.

more from wal.html

"Persistence of WAL mode

Unlike the other journaling modes, PRAGMA journal_mode=WAL is
persistent. If a process sets WAL mode, then closes and reopens the
database, the database will come back in WAL mode. In contrast, if a
process sets (for example) PRAGMA journal_mode=TRUNCATE and then
closes and reopens the database will come back up in the default
rollback mode of DELETE rather than the previous TRUNCATE setting.

The persistence of WAL mode means that applications can be converted
to using SQLite in WAL mode without making any changes to the
application itself. One has merely to run "PRAGMA journal_mode=WAL;"
on the database file(s) using the command-line shell or other utility,
then restart the application.

The WAL journal mode will be set on all connections to the same
database file if it is set on any one connection."

[Simon Slavin]

I am using sqlite3 3.7.17 on Linux Slackware64 14.1. I use the sqlite3 command every 10 minutes to query several Thunderbird calendar databases. Occasionally, I
have a message, "Error: database is locked"; understandable since Thunderbird
is occasionally updating its database.

You may get a better result by adding a timeout before your SELECT:

PRAGMA busy_timeout=5000;SELECT <whatever>

This allows SQLite to retry the operation for up to 5 seconds before it gives up and returns the above error.

Simon.

[Mark Foley]

Post by Simon Slavin
PRAGMA busy_timeout=5000;SELECT <whatever>

Thanks, I just tried that. It's a good ides, but the problem is that the PRAGMA
statement echo the timeout value to stdout:

sqlite3 "mydbpath" \
"PRAGMA busy_timeout=5000;select distinct value from cal_properties where key = 'CATEGORIES'"
5000
Orange Category
Green Category
Blue Category
Purple Category
Blue Category,Holidays
Personal

I'd have to do some major editing to my queries to reject the 1st row make the
query processing very depending on whether or no I use the PRAGMA. Is there a
way to surpress the PRAGMA output?

THX --Mark

-----Original Message-----

Post by Simon Slavin
Date: Mon, 25 Apr 2016 13:41:36 +0100
Subject: Re: [sqlite] sqlite3 command line, read-only

I am using sqlite3 3.7.17 on Linux Slackware64 14.1. I use the sqlite3 command every 10 minutes to query several Thunderbird calendar databases. Occasionally, I
have a message, "Error: database is locked"; understandable since Thunderbird
is occasionally updating its database.

PRAGMA busy_timeout=5000;SELECT <whatever>
This allows SQLite to retry the operation for up to 5 seconds before it gives up and returns the above error.
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Richard Hipp]

Post by Mark Foley
Thanks, I just tried that. It's a good ides, but the problem is that the PRAGMA

Can you use the ".once /dev/null" command prior to the "PRAGMA" to
suppress the output?

sqlite3 "mydbpath" -cmd ".once /dev/null" -cmd "PRAGMA
busy_timeout=5000" "select distinct value from cal_properties where
key = 'CATEGORIES'"

[Mark Foley]

Post by Richard Hipp
Can you use the ".once /dev/null" command prior to the "PRAGMA" to
suppress the output?

sqlite3 -cmd ".once /dev/null" -cmd "PRAGMA busy_timeout=5000" "mydbpath" \
"select distinct value from cal_properties where key = 'CATEGORIES'"
Error: unknown command or invalid arguments: "once". Enter ".help" for help
5000
Orange Category
Green Category
Blue Category
Purple Category
Blue Category,Holidays
Personal

Perhaps too old a version of sqlite3? Doing .help does not list .once, but it
does list:

.timeout MS Try opening locked tables for MS milliseconds

I tried that, but perhaps I've got the syntax wrong:

sqlite3 "mydbpath" \
".timeout 5000;select distinct value from cal_properties where key = 'CATEGORIES'"
Error: unknown command or invalid arguments: "timeout". Enter ".help" for help

Putting it in a -cmd (following your example):

sqlite3 -cmd ".timeout 5000" "mydbpath" \
"select distinct value from cal_properties where key = 'CATEGORIES'"
Orange Category
Green Category
Blue Category
Purple Category
Blue Category,Holidays
Personal

Didn't give an error and didn't echo the 5000 to stdout. Do you suppose that
will work?

--Mark

-----Original Message-----

Post by Richard Hipp
Date: Mon, 25 Apr 2016 09:10:58 -0400
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Mark Foley
Thanks, I just tried that. It's a good ides, but the problem is that the PRAGMA

Can you use the ".once /dev/null" command prior to the "PRAGMA" to
suppress the output?
sqlite3 "mydbpath" -cmd ".once /dev/null" -cmd "PRAGMA
busy_timeout=5000" "select distinct value from cal_properties where
key = 'CATEGORIES'"
--
D. Richard Hipp
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Richard Hipp]

Post by Mark Foley
Perhaps too old a version of sqlite3? Doing .help does not list .once,

Added to SQLite 3.8.5, circa 2014-06-04.

[Mark Foley]

OK, I'm at 3.7.17, so too old for .once.

--Mark

-----Original Message-----

Date: Mon, 25 Apr 2016 09:32:48 -0400
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Mark Foley
Perhaps too old a version of sqlite3? Doing .help does not list .once,

Added to SQLite 3.8.5, circa 2014-06-04.
--
D. Richard Hipp
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by Mark Foley
problem is that the PRAGMA
sqlite3 "mydbpath" \
"PRAGMA busy_timeout=5000;select distinct value from cal_properties where key = 'CATEGORIES'"
5000
Orange Category
Green Category
Blue Category
Purple Category
Blue Category,Holidays
Personal
I'd have to do some major editing to my queries to reject the 1st row make the
query processing very depending on whether or no I use the PRAGMA. Is there a
way to surpress the PRAGMA output?

Hmm. I don't know whether this might work:

.once dummy.txt;PRAGMA busy_timeout=5000;select distinct value from cal_properties where key = 'CATEGORIES'

This might route the result of the PRAGMA to your dummy.txt file, which you can ignore. You might have to specify a writable directory. Or you could try sending it to /dev/null or whatever your OS supports as "throw this away".

Simon.

[Mark Foley]

Simon,

As I just wrote to Richard Hipp, I must not have the .once command, but see my
reply for a possible solution using .timeout and tell me what you think. I'm
not sure what the -cmd really does. It is not described in my man page.

--Mark

-----Original Message-----

Date: Mon, 25 Apr 2016 14:12:27 +0100
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Mark Foley
problem is that the PRAGMA
sqlite3 "mydbpath" \
"PRAGMA busy_timeout=5000;select distinct value from cal_properties where key = 'CATEGORIES'"
5000
Orange Category
Green Category
Blue Category
Purple Category
Blue Category,Holidays
Personal
I'd have to do some major editing to my queries to reject the 1st row make the
query processing very depending on whether or no I use the PRAGMA. Is there a
way to surpress the PRAGMA output?

.once dummy.txt;PRAGMA busy_timeout=5000;select distinct value from cal_properties where key = 'CATEGORIES'
This might route the result of the PRAGMA to your dummy.txt file, which you can ignore. You might have to specify a writable directory. Or you could try sending it to /dev/null or whatever your OS supports as "throw this away".
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

As I just wrote to Richard Hipp, I must not have the .once command, but see my reply for a possible solution using .timeout and tell me what you think.

Yep. That should work. In fact it's a better solution than mine.

Simon.

[Mark Foley]

OK, I'll go with that and see if I get any more lock errors.

--Mark

-----Original Message-----
From: Simon Slavin <***@bigfraud.org>
Date: Mon, 25 Apr 2016 14:30:26 +0100

As I just wrote to Richard Hipp, I must not have the .once command, but see my reply for a possible solution using .timeout and tell me what you think.

Yep. That should work. In fact it's a better solution than mine.

Simon.

[Richard Hipp]

Post by Mark Foley
I'm new to the list, so apologies if this has been posted before.
I am using sqlite3 3.7.17 on Linux Slackware64 14.1. I use the sqlite3 command
every 10 minutes to query several Thunderbird calendar databases. Occasionally, I
have a message, "Error: database is locked"; understandable since Thunderbird
is occasionally updating its database.

If you put the databases in WAL-mode (https://www.sqlite.org/wal.html)
using "PRAGMA journal_mode=WAL;" then this problem will likely go away
(depending on what it is that Thunderbird is doing.) I suggest trying
that approach first.

Post by Mark Foley
First, is there a way to open a database in readonly mode from the command line?
I 've searched the man page and googled, but I find nothing. I've tried opening
the database as dbname?mode=ro, but that doesn't work. The C API has such a
mode, but apparently not the command line.
If the answer to that question is "no", I would like to request that a readonly
switch be added to future versions of sqlite. I think this would be generally
useful and couldn't be that difficult to implment.
Thanks, Mark
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Mark Foley]

Post by Richard Hipp
If you put the databases in WAL-mode (https://www.sqlite.org/wal.html)
using "PRAGMA journal_mode=WAL;" then this problem will likely go away
(depending on what it is that Thunderbird is doing.) I suggest trying
that approach first.

Rather interesting results:

sqlite3 "mydbpath" \
"PRAGMA journal_mode=WAL;select distinct value from cal_properties where key = 'CATEGORIES'"
Error: attempt to write a readonly database

At first, I thought the problem was that I had the directory mounted ro, but when
I remounted rw I still got this error. Not sure where or why the DB is being set
as read-only. Possibly a Windows permission thing.

Anyway, read-only is good, but that means the WAL idea won't work and maybe Simon
Slavin's timeout idea is a better approach (except for outputting the timeout
value to stdout).

I wonder, then, why I get a lock error if the database is read-only in the first
place?

THX --Mark

-----Original Message-----

Post by Richard Hipp
Date: Mon, 25 Apr 2016 08:42:26 -0400
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Mark Foley
I'm new to the list, so apologies if this has been posted before.
I am using sqlite3 3.7.17 on Linux Slackware64 14.1. I use the sqlite3 command
every 10 minutes to query several Thunderbird calendar databases. Occasionally, I
have a message, "Error: database is locked"; understandable since Thunderbird
is occasionally updating its database.

If you put the databases in WAL-mode (https://www.sqlite.org/wal.html)
using "PRAGMA journal_mode=WAL;" then this problem will likely go away
(depending on what it is that Thunderbird is doing.) I suggest trying
that approach first.

Post by Mark Foley
First, is there a way to open a database in readonly mode from the command line?
I 've searched the man page and googled, but I find nothing. I've tried opening
the database as dbname?mode=ro, but that doesn't work. The C API has such a
mode, but apparently not the command line.
If the answer to that question is "no", I would like to request that a readonly
switch be added to future versions of sqlite. I think this would be generally
useful and couldn't be that difficult to implment.
Thanks, Mark
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
D. Richard Hipp
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Richard Hipp]

Post by Mark Foley
I wonder, then, why I get a lock error if the database is read-only in the first
place?

The reader has to get a read lock in order to prevent a concurrent
writer from changing content out from under it. (Moving the database
to WAL mode allows readers and writers to co-exist.)

[Mark Foley]

This is probably taking us down another path, but how would my query succeed at
all if I need a lock to read and the DB file itself is read-only?

My normal queries do work fine, even if the directory is mounted read-only.

--Mark

-----Original Message-----

Date: Mon, 25 Apr 2016 09:14:14 -0400
Subject: Re: [sqlite] sqlite3 command line, read-only

Post by Mark Foley
I wonder, then, why I get a lock error if the database is read-only in the first
place?

The reader has to get a read lock in order to prevent a concurrent
writer from changing content out from under it. (Moving the database
to WAL mode allows readers and writers to co-exist.)
--
D. Richard Hipp
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Richard Hipp]

Post by Mark Foley
This is probably taking us down another path, but how would my query succeed at
all if I need a lock to read and the DB file itself is read-only?

Most systems allow you to obtain a read-lock on a read-only file.