How do I specify a timeout in sqlite3 from the command-line?

Question

I have a couple basic scripts that kick out a little information while my sqlite3 DB is being populated, but about half the time the command instantly fails:

$ sqlite3 outgoing.db "select * from edges where worker is not null;"
Error: database is locked
$ sqlite3 outgoing.db "select * from edges where worker is not null;"
Error: database is locked
$ sqlite3 outgoing.db "select * from edges where worker is not null;"
1014->9000|1014|9000||-1.0|2
1014->9001|1014|9001||-1.0|2
...

If I add .timeout 1; to the beginning of the command I just get a syntax error; how do I pass sqlite the special . parameters non-interactively via the command-line?

score 8 · Answer 1 · answered Mar 09 '18 at 11:49

8

Just use:

$ sqlite3 -cmd ".timeout 1000" outgoing.db "SELECT * FROM edges"

answered Mar 09 '18 at 11:49

luzik

181
1
2

1

This looks very similar to an already provided answer. – Alexei Mar 09 '18 at 11:53
2

No, because I am providing dedicated sqlite3 param '-cmd' – luzik Mar 09 '18 at 20:17
5

This is actually the most elegant answer because it avoids process substitutions and heredocs. – Mark Thomson Dec 29 '18 at 11:21

score 6 · Accepted Answer · answered Aug 11 '13 at 10:25

You can do this by using an init file.

init.sql (note that the timeout value is in milliseconds - 1 is rather short):

.timeout 1000

At the prompt:

$ sqlite3 -init init.sql outgoing.db "select * from edges where worker is not null;"
Loading resources from init.sql
# 1 second pause
Error: database is locked

With some shells (on Linux at least, not very portable), you can avoid the need for a proper file with process substitution if that's a problem:

$ sqlite3 -init <(echo .timeout 1000) your.db "your sql;"

The extra output line ("Loading resources from ...") doesn't get printed if the output is redirected to a file or pipe, or to the .output file if you specified one in your init file.

score 4 · Answer 3 · answered Aug 13 '13 at 07:29

In the sqlite3 command-line tool, dot commands are not SQL commands, so you cannot mix them with queries in the same line (and you should not delimit them with a semicolon).

sqlite3 can read commands from its standard input, so you can simply use a here document to give it multiple lines:

$ sqlite3 outgoing.db <<EOF
.timeout 1000
select * from edges where worker is not null;
EOF

score 0 · Answer 4 · answered Apr 23 '19 at 01:24

0

I made this little bash function for it, so that I wouldn't need to make a file:

function patient_sqlite {
  DB_FILE="$1"
  QUERY="$2"
  echo $'.timeout 1000\n'"$QUERY" | sqlite3 "$DB_FILE"
}
patient_sqlite db.sqlite 'SELECT * FROM users LIMIT 1;'

answered Apr 23 '19 at 01:24

turiyag

161
1
2

How do I specify a timeout in sqlite3 from the command-line?

4 Answers4