Re: [sqlite] sqlite journal file question
This was my initial reading too Paul, but reading the OP post again it could be either - who knows what is meant by "syncing". I also think this is the main point for the OP - If by syncing he really means "copying the file" then Richard's advice, else if he means "adding via normal DB connection" then the other options. If the DB files are copied, it needs to be copied in perfect version sync, so the program/service filling the DB needs to be informed to wait a moment while copying. I still think for this scenario the backup API would be better. Otherwise, posting SQL to the second DB would require no such trickery, no wait-states and be a lot less IO intensive. On 2/13/2015 10:16 AM, Paul Sanderson wrote: Richard I read that the db on the standby machine is being updated at a record by record level, i.e. not copied in its entirety. In this scenario I can't see the two db files being guaranteed binary compatible. Copying the journal across in this scenario would imo be a mistake. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 13 February 2015 at 02:47, Richard Hipp wrote: On 2/12/15, Mayank Kumar (mayankum) wrote: Hi All We have two systems which are running in active/standby configuration. The active machine, is actively writing sqlite transactions to a file abcd.db. The standby is syncing the abcd.db file from the active machine on a communication channel and writing the delta records to the abcd.db on the standby machine. Now my question is , lets say the active machine crashes in the middle of the transaction and we have a hot journal created on the active machine. Does it make sense to copy the hot journal to the standby machine, so that the sqlite application on the standby will notice this hot journal and use it to recover any lost transactions Not only does it make sense - it is mandatory if you want to preserve the integrity of the database file. While a transaction is underway, the journal (either the *-journal file or the *-wal file) is part of the state of the database. If you fail to copy it over to another machine and then try to open that database on the other machine, the database will be incomplete and potentially corrupt. The journal files have a well-defined and documented format. They are independent of the byte-order and word-size of the machine they were created on. -- D. Richard Hipp d...@sqlite.org ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite journal file question
Richard I read that the db on the standby machine is being updated at a record by record level, i.e. not copied in its entirety. In this scenario I can't see the two db files being guaranteed binary compatible. Copying the journal across in this scenario would imo be a mistake. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 13 February 2015 at 02:47, Richard Hipp wrote: > On 2/12/15, Mayank Kumar (mayankum) wrote: >> Hi All >> >> We have two systems which are running in active/standby configuration. The >> active machine, is actively writing sqlite transactions to a file abcd.db. >> The standby is syncing the abcd.db file from the active machine on a >> communication channel and writing the delta records to the abcd.db on the >> standby machine. >> >> Now my question is , lets say the active machine crashes in the middle of >> the transaction and we have a hot journal created on the active machine. >> Does it make sense to copy the hot journal to the standby machine, so that >> the sqlite application on the standby will notice this hot journal and use >> it to recover any lost transactions > > Not only does it make sense - it is mandatory if you want to preserve > the integrity of the database file. > > While a transaction is underway, the journal (either the *-journal > file or the *-wal file) is part of the state of the database. If you > fail to copy it over to another machine and then try to open that > database on the other machine, the database will be incomplete and > potentially corrupt. > > The journal files have a well-defined and documented format. They are > independent of the byte-order and word-size of the machine they were > created on. > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] sqlite journal file question
On 2/12/15, Mayank Kumar (mayankum) wrote: > Hi All > > We have two systems which are running in active/standby configuration. The > active machine, is actively writing sqlite transactions to a file abcd.db. > The standby is syncing the abcd.db file from the active machine on a > communication channel and writing the delta records to the abcd.db on the > standby machine. > > Now my question is , lets say the active machine crashes in the middle of > the transaction and we have a hot journal created on the active machine. > Does it make sense to copy the hot journal to the standby machine, so that > the sqlite application on the standby will notice this hot journal and use > it to recover any lost transactions Not only does it make sense - it is mandatory if you want to preserve the integrity of the database file. While a transaction is underway, the journal (either the *-journal file or the *-wal file) is part of the state of the database. If you fail to copy it over to another machine and then try to open that database on the other machine, the database will be incomplete and potentially corrupt. The journal files have a well-defined and documented format. They are independent of the byte-order and word-size of the machine they were created on. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite journal file question
I don't think it can be done, and if it could be done, it would not be wise. The journal is owned and specific to a connection. a hot Journal for connection A on DB 1 cannot ever be used to roll back or affect in any way connection B on DB 2. However, when you say the "standby is syncing", I assume from your description it copies the file all the time? having an exact copy of the DB file and accompanying journal might work on a technical level, but how can you be sure you have the exact same moment in time files? The answer is easy: you can't. Especially not when one of the machines went down or hung up. The best way to achieve your scenario (in my opinion, other people might come up with better best ways) is to either have a second connection to the DB which is updating new records to the second DB (which means the second DB will handle its own hot journals and back ups and the like.) with maybe a once-a-day copy of the full DB after possibly a vacuum or other maintenance routines. This all depends on your workload and implementation specifics. Personally I would have System output any and all SQL that it writes to DB 1 to a file or pipe to the sync service/program which will then apply all the exact same SQL to the second DB, that way they are always in sync and they can't both fail. (Ok they /can/ technically, but the odds of that is negligible). This option also prevents re-copying insane amounts of data which are in both DBs already, all the time. Another option is the backup API but if you don't have a relatively good space of time to make copies of the DB (i.e where there are no updates) then it will not work well. On 2/12/2015 11:09 PM, Mayank Kumar (mayankum) wrote: Hi All We have two systems which are running in active/standby configuration. The active machine, is actively writing sqlite transactions to a file abcd.db. The standby is syncing the abcd.db file from the active machine on a communication channel and writing the delta records to the abcd.db on the standby machine. Now my question is , lets say the active machine crashes in the middle of the transaction and we have a hot journal created on the active machine. Does it make sense to copy the hot journal to the standby machine, so that the sqlite application on the standby will notice this hot journal and use it to recover any lost transactions or the hot journal can only be used on the same db on which it was created. Remember , both the db on active and standby are same(maintained in sync and have same names), so can a hot journal from one db on active machine be used on standby machine to recover lost transactions. We are assuming that somehow the sqlite application on active is not able to use the journal file to recover from it, so as a fallback would copying it to standby make sense at all so that it can be used by the backup db ? Hope by question doesn't sound too absurd:) -Mayank ___ 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] sqlite journal file question
I would say no. The journal file stores pages referenced by page no and when replayed will write those pages back to the main DB at the appropriate physical offset. Although the content of your DB's at a logical level may be the same, it is unlikely that they will be exact copies at a binary level so replaying a journal to the wrong DB would almost certaibnly cause corruption. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 12 February 2015 at 21:09, Mayank Kumar (mayankum) wrote: > Hi All > > We have two systems which are running in active/standby configuration. The > active machine, is actively writing sqlite transactions to a file abcd.db. > The standby is syncing the abcd.db file from the active machine on a > communication channel and writing the delta records to the abcd.db on the > standby machine. > > Now my question is , lets say the active machine crashes in the middle of the > transaction and we have a hot journal created on the active machine. Does > it make sense to copy the hot journal to the standby machine, so that the > sqlite application on the standby will notice this hot journal and use it to > recover any lost transactions or the hot journal can only be used on the > same db on which it was created. > > Remember , both the db on active and standby are same(maintained in sync and > have same names), so can a hot journal from one db on active machine be used > on standby machine to recover lost transactions. > > We are assuming that somehow the sqlite application on active is not able to > use the journal file to recover from it, so as a fallback would copying it to > standby make sense at all so that it can be used by the backup db ? > > Hope by question doesn't sound too absurd:) > -Mayank > ___ > 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
