Re: [sqlite] PERSIST Journal Mode
Some additional information. It appears that setting the journal_mode is not inherited by DBs that are attached after the PRAGMA journal_mode is executed. So, I added one after the ATTACH. It also appears that, as part of the PRAGMA journal_mode execution, the current journal file is deleted. My SQL activity tends to be a lot of open DB, do work, close DB. That is, I don't open the database and leave it open. When I run multiple transactions, the journal is not deleted between each one. Am I right in assuming that upon opening a database (or the first real command thereafter), journals are deleted as a matter of course? I also have no additional information as to why having PERSIST mode on prevents the database from being updated/correct. I did check the sqlite3_close command, and I'm passing it the connection received from sqlite3_open. It returns SQLITE_OK. I'll check to see if something is being rolled back when that happens. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, December 17, 2012 5:46 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] PERSIST Journal Mode On 17 Dec 2012, at 8:35pm, Marc L. Allen mlal...@outsitenetworks.com wrote: Another item.. when having Journal Mode = PERSIST, DBA (in the example below) was not being physically updated. DBB was. I can think of a reason you might not be able to see an update until you have executed _close(). So are you looking to see an update before or after your code has executed _close() ? How are you looking for an update ? The file's modification timestamp ? Also, are you sure you are executing _close() with the right parameter, and can you check the result returned from _close() to make sure it returns SQLITE_OK ? Simply taking out the PRAGMA fixed things. Any ideas where I should look? Clip from your earlier post: open(DBA) PRAGMA journal_mode = PERSIST; ATTACH DBB SQL ... close() If the DBA.journal file exists, it's deleted before the attach (though I haven't identified exactly where. I can if important.) During the close, the DBA.mjx is deleted as is the DBB.journal file. The DBA.journal file is not deleted until the next time I open it. One situation I think might cause this is if the database isn't closed properly. When SQLite reopens the database it realises it is corrupt. It restores the database to a usable condition, then (this is just a guess) it deletes the old journal so it can make a new uncorrupted one. However, I can't answer your basic problem. I see no reason why these things should change just because you're using PERSIST mode. But there are people who understand SQLite internals better than I do. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PERSIST Journal Mode
On 18 Dec 2012, at 3:04pm, Marc L. Allen mlal...@outsitenetworks.com wrote: I also have no additional information as to why having PERSIST mode on prevents the database from being updated/correct. I did check the sqlite3_close command, and I'm passing it the connection received from sqlite3_open. It returns SQLITE_OK. Well, it seems you're doing it right. It's possible that your operating system doesn't show updates until the file is closed. The other thing is that journal mode is meant to be sticky with each database file. It may be that when you issue 'PRAGMA journal_mode', even though you're setting the same mode that's already active, SQLite reinitialises the journal. Now you have the correct journal mode set for your database files you shouldn't need to use that PRAGMA again. Does that fix things ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PERSIST Journal Mode
I believe only WAL mode is sticky. Not using the pragma seems to generate deletions as normal. The missing data was from after the file was closed. I am still looking into rollback, but why would journal mode matter? Marc On Dec 18, 2012, at 12:02 PM, Simon Slavin slav...@bigfraud.org wrote: On 18 Dec 2012, at 3:04pm, Marc L. Allen mlal...@outsitenetworks.com wrote: I also have no additional information as to why having PERSIST mode on prevents the database from being updated/correct. I did check the sqlite3_close command, and I'm passing it the connection received from sqlite3_open. It returns SQLITE_OK. Well, it seems you're doing it right. It's possible that your operating system doesn't show updates until the file is closed. The other thing is that journal mode is meant to be sticky with each database file. It may be that when you issue 'PRAGMA journal_mode', even though you're setting the same mode that's already active, SQLite reinitialises the journal. Now you have the correct journal mode set for your database files you shouldn't need to use that PRAGMA again. Does that fix things ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PERSIST Journal Mode
I'm having some unexpected results using PRAGMA journal_mode = PERSIST; My general flow runs something like: open(DBA) PRAGMA journal_mode = PERSIST; ATTACH DBB SQL ... close() If the DBA.journal file exists, it's deleted before the attach (though I haven't identified exactly where. I can if important.) During the close, the DBA.mjx is deleted as is the DBB.journal file. The DBA.journal file is not deleted until the next time I open it. Am I misunderstanding something? Thanks, Marc -- ** * * * * Marc L. Allen * ... so many things are * * * possible just as long as you* * Outsite Networks, Inc. * don't know they're impossible. * * (757) 853-3000 #215 * * * * * * mlal...@outsitenetworks.commailto:mlal...@outsitenetworks.com * -- The Phantom Tollbooth * * * * ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PERSIST Journal Mode
On 17 Dec 2012, at 8:35pm, Marc L. Allen mlal...@outsitenetworks.com wrote: Another item.. when having Journal Mode = PERSIST, DBA (in the example below) was not being physically updated. DBB was. I can think of a reason you might not be able to see an update until you have executed _close(). So are you looking to see an update before or after your code has executed _close() ? How are you looking for an update ? The file's modification timestamp ? Also, are you sure you are executing _close() with the right parameter, and can you check the result returned from _close() to make sure it returns SQLITE_OK ? Simply taking out the PRAGMA fixed things. Any ideas where I should look? Clip from your earlier post: open(DBA) PRAGMA journal_mode = PERSIST; ATTACH DBB SQL ... close() If the DBA.journal file exists, it's deleted before the attach (though I haven't identified exactly where. I can if important.) During the close, the DBA.mjx is deleted as is the DBB.journal file. The DBA.journal file is not deleted until the next time I open it. One situation I think might cause this is if the database isn't closed properly. When SQLite reopens the database it realises it is corrupt. It restores the database to a usable condition, then (this is just a guess) it deletes the old journal so it can make a new uncorrupted one. However, I can't answer your basic problem. I see no reason why these things should change just because you're using PERSIST mode. But there are people who understand SQLite internals better than I do. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
