3

I have a C program that opens a sqlite3 db via sqlite3_open_v2(), and then periodically runs some SELECT queries. Will anything bad happen if I replace that file while the program is running? Is there some proper way to do that to avoid problems? I want to avoid restarting it.

Andrey
  • 808
  • 2
  • 6
  • 12

2 Answers2

6

On UNIX-like systems, as far as I can tell, it will keep using the original file, provided you delete (rather than truncate) it. Such systems do not truly delete a file until all file handles are closed (see this question), and I think that is done by sqlite3_close.

You can test with the command-line client:

echo "create table foo(a); insert into foo values(1);" | sqlite3 test1.db
echo "create table foo(a); insert into foo values(2);" | sqlite3 test2.db
sqlite3 test1.db
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from foo;
1

Now (in another tab or window):

rm test1.db; cp test2.db test1.db

Back in sqlite:

sqlite> select * from foo;
1

strace on the client confirms that it closes the database file right before exit:

open("/tmp/test1.db", O_RDWR|O_CREAT|O_LARGEFILE, 0644) = 3
...
close(3)                                = 0
exit_group(0)                           = ?

Also, I checked and there are no intervening close(3) calls (i.e. the file handle number is not reused).

Community
  • 1
  • 1
Matthew Flaschen
  • 278,309
  • 50
  • 514
  • 539
  • Im surprised that works since test1.db is truncated. Likely the values in test1.db are kept in a memory buffer, and sqlite doesn't know (yet) that test1.db is erased. `rm test1.db && cp test2.db test1.db` would be fine though - as the original test1.db is left untouched and still exists until the sqlite process closes that file. – nos Dec 28 '10 at 23:36
  • I believe this is only true for small db sizes, as it caches a limited number of pages. See [this answer](http://stackoverflow.com/questions/2182774/can-sqlite-load-10m-data-into-the-memory/2185699#2185699). (Referring to Matt's answer, not @nos' comment which came in 13 secs before mine!) – moinudin Dec 28 '10 at 23:36
  • @marcog: This answer doesn't depend on sqlite's caching, it is a feature of the way unix filesystems work. An open file descriptor is a link to a inone just like a directory entry. So when sqlite opens the file there are two links. Delete on the command line means there are *no* named files connected to that inode, but there is still a link and the inode does not get entered into the free list. Recreate the file and that data ends on a different inode with, but the open file descriptor is still valid and still points to the original data. Or does sqlite close the file after caching? – dmckee --- ex-moderator kitten Dec 28 '10 at 23:44
  • @dmckee A quick test suggests that yes, it does close the file. – moinudin Dec 28 '10 at 23:50
  • @marcog: Ah, thanks...Another possibility toccurs to me: my manpage does not specify the underling implementation of `cp` so it might overwrite on the existing inode. – dmckee --- ex-moderator kitten Dec 28 '10 at 23:52
  • @marcog, that contradicts the `strace` I did. I don't think it's plausible that `cp` would overwrite the existing inode if you do `rm` first (like my corrected example). That would be some dark magic. – Matthew Flaschen Dec 29 '10 at 00:05
  • Would you please look into http://stackoverflow.com/q/19448046/89771? My issue is almost exactly the same, but I also need to worry about journal and shared lock files. – Alix Axel Oct 18 '13 at 13:14
0

I think due to in memory connection state like cache there can by unpredictable errors. Consider also that Sqlite block file on read/write and AFAIK swapping file atomicaly is not easy so there can be errors too in that point.

  • 1
    Actually, atomic [rename](http://en.wikipedia.org/wiki/Rename_%28C%29) is easy on UNIX-like systems. Programs with previously opened handles continue to use the old file. There are no errors due to half-renamed files, though. – Matthew Flaschen Dec 28 '10 at 23:51
  • so in this case connection will use old file and application still need to reconnect, second thing is when rename old file operation is done new file need rename also after so by some small time period defined file practically don't exists –  Dec 29 '10 at 00:21
  • no, atomic rename guarantees that there's always some file at that name. – Matthew Flaschen Dec 29 '10 at 01:04
  • @matthew I'm considering file replace/swapping procedure (witch use two atomic rename invocations) If you see my view point –  Dec 29 '10 at 02:14