3.17.0 does not read updated DB

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

3.17.0 does not read updated DB

Roman Fleysher
Dear SQLiters,

Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up updated content of a DB file? I updated DB while it was attached in sqlite3 and SELECT did not produce new results. Instead it printed the same output as before DB file was updated.

Update was performed using sqlite3 version 3.8.11 (also command shell). I seem to be able to replicate that.

Roman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 3.17.0 does not read updated DB

Roman Fleysher
My mistake: I do not update DB. I rename (unix mv) the DB.

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of Roman Fleysher [[hidden email]]
Sent: Thursday, February 16, 2017 12:33 AM
To: General Discussion of SQLite Database
Subject: [sqlite] 3.17.0 does not read updated DB

Dear SQLiters,

Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up updated content of a DB file? I updated DB while it was attached in sqlite3 and SELECT did not produce new results. Instead it printed the same output as before DB file was updated.

Update was performed using sqlite3 version 3.8.11 (also command shell). I seem to be able to replicate that.

Roman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 3.17.0 does not read updated DB

Hick Gunter
The unix mv command does not affect currently open file handles, only the directory entry or entries (atomically, unless it is a cross-filesystem mv, which is accomplished by pretrending you said cp and is in no way atomic).

As long as the first command shell is running, it will continue to see (and modify) the original contents. A new command shell would open a different file (the one the directory entry now points to) and therefore operate on a different file than the first command shell.

A "file" on unix consists of an "inode" (the basic file system entity that allows allocation of disk space) and at least one "directory entry" (that assigns a name to an inode). An inode is freed when the last directory entry referring to it is removed and the last file handle referencing it is closed.

e.g.

x) run sqlite on a.db (let's assume this file is inode number 4711)
x) mv a.db b.db (now the directory entry for b.db points to inode 4711)
x) mv x.db a.db (now the directory entry for a.db points to the inode of x.db, let's assume this is 815)
x) run sqlite on a.db (this opens inode 815)

note: to access the former a.db you now have to use the name b.db


e.g.

x) run sqlite on a.db (again assumed inode number 4711)
x) mv b.db a.db (now the directory entry for a.db points to the inode of b.db, again assumed 815)
x) run sqlite on a.db (this opens inode 815)

note: the former contents of a.db (inode 4711) is now no longer referenced by any directory entry. It will continue to stick around until the (last) file handle is closed, after which it will be deleted.

In both cases, the two instances of sqlite are operating on different inodes i.e. file contents and therefore cannot see each others' changes.


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Roman Fleysher
Gesendet: Donnerstag, 16. Februar 2017 06:52
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] 3.17.0 does not read updated DB

My mistake: I do not update DB. I rename (unix mv) the DB.

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of Roman Fleysher [[hidden email]]
Sent: Thursday, February 16, 2017 12:33 AM
To: General Discussion of SQLite Database
Subject: [sqlite] 3.17.0 does not read updated DB

Dear SQLiters,

Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up updated content of a DB file? I updated DB while it was attached in sqlite3 and SELECT did not produce new results. Instead it printed the same output as before DB file was updated.

Update was performed using sqlite3 version 3.8.11 (also command shell). I seem to be able to replicate that.

Roman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 3.17.0 does not read updated DB

Jens Alfke-2
In reply to this post by Roman Fleysher

> On Feb 15, 2017, at 9:51 PM, Roman Fleysher <[hidden email]> wrote:
>
> My mistake: I do not update DB. I rename (unix mv) the DB.

I wouldn’t recommend doing that to an open database!

(a) I don’t think it’s formally specified when SQLite opens or closes the database file. It’s possible it might close and reopen it at some point, in which case it would probably open the old path and suddenly be seeing a different file.
(b) Any side files like the -wal and -shm files used by WAL, or the .journal file, will be left behind. SQLite will probably keep using them at the old location. But if you close the handle and then reopen it on the moved file, those side files will now be missing, which could result in database corruption.
(c) As noted, the semantics of mv differ depending on whether the paths are on the same filesystem, and the effects on an open sqlite database would be completely different.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users