Even in rollback journal mode, it is not universally safe to use normal file operations on a SQLite database. See section 1.3 and 1.4 of https://www.sqlite.org/howtocorrupt.html
If you want to use normal file system operations (or any type of manipulation not using the SQLite library) on an SQLite database, the most bombproof method is to take a backup first, then perform whatever file system operations you want on the backup. This way, you can be sure that (a) no process is connected to the backup (because it has a different filename) and (b) there are no hot journals associated with the backup. You can either use the backup API from an application, or the .backup command from the SQLite command line interface (CLI). From what you say, wanting to use scp, it sounds like a manual process or shell script; the CLI would likely be the easiest choice. On Sat, 7 Dec 2019 at 04:39, MM <finjulh...@gmail.com> wrote: > On Fri, 6 Dec 2019 at 19:06, Simon Slavin <slav...@bigfraud.org> wrote: > > > On 6 Dec 2019, at 6:39pm, MM <finjulh...@gmail.com> wrote: > > > > > So it suffices that I run "PRAGMA journal_mode=WAL;" once from say the > > sqlite3 cli, for all future connections from any tool will use WAL mode > for > > this database file? > > > > Correct. > > > > > What happens when 2 processes that have had their connection open for a > > while, attempt a UPDATE or INSERT INTO statement at the same time? > > > > It depends on which connections have a timeout set: > > > > <https://sqlite.org/c3ref/busy_timeout.html> > > > > It is normal to specify a timeout of 10 seconds (or even 10 minutes) for > > every connection you open. This means that a process which finds the > > database locked will enter a delay/retry loop for up to that amount of > time > > before returning SQLITE_BUSY. > > > > However, note that the default timeout is zero. Which means that if you > > don't set a timeout on a connection, and it encounters a locked database, > > it will immediately return SQLITE_BUSY without retrying. > > > > [The above explanation is simplified for clarity.] > > _______________________________________________ > > > > Thank you. > After having set WAL mode, ontop of my db file, there will be 2 extra > files? At all times? > Up until, I used to scp the db file to another host, and sometimes work on > the db there and then copy back the db to the main host when no processes > are running. > Do I know simply copy the 3 files? the db file, and the 2 others? > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
