Re: [sqlite] transaction recovery question
Also note that it's often possible to open a corrupted database and operate on it with no error, nothing goes bad until you touch a corrupted row/table/index. We've found that doing a "PRAGMA integrity_check" is effective for discovering any/all corruption in a database early and avoiding random errors later. Make sure you do this after blowing away your -journal file while you're experimenting. --Bob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dan Sent: Tuesday, June 03, 2008 7:50 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] transaction recovery question On Jun 3, 2008, at 10:03 PM, Darko Filipovic wrote: > I've tried...(not with UFO :D ). Nothing happens, database is not > corrupted and that is what confuses me...I thought it should not be > readable (malformed) ?! When SQLite needs to modify the content of a database page, it does two things: * writes the contents of that page out to the journal (so that it can be rolled back later if necessary), and * makes the change to an in-memory copy of the page. Later on, when the transaction is committed or enough changes have accumulated in memory, all pending changes are flushed through to the file. You probably abandoned the transaction to early for this to happen - so the journal file was in the file-system, but no actual changes had been made to the database file. Try it with a really big transaction and you will see the corruption. Dan. > Greetings, > Darko F. > > > > Federico Granata wrote: >> 2008/6/3 Darko Filipovic <[EMAIL PROTECTED]>: >> >> >>> But, what happen if journal file is deleted before starting B >>> process? >>> >>> >> what if a UFO stole your pc ? :-D >> >> try to delete journal file and see what happens ... >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> __ NOD32 3154 (20080603) Information __ >> >> This message was checked by NOD32 antivirus system. >> http://www.eset.com >> >> >> >> > ___ > 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] transaction recovery question
On Jun 3, 2008, at 10:03 PM, Darko Filipovic wrote: > I've tried...(not with UFO :D ). Nothing happens, database is not > corrupted and that is what confuses me...I thought it should not be > readable (malformed) ?! When SQLite needs to modify the content of a database page, it does two things: * writes the contents of that page out to the journal (so that it can be rolled back later if necessary), and * makes the change to an in-memory copy of the page. Later on, when the transaction is committed or enough changes have accumulated in memory, all pending changes are flushed through to the file. You probably abandoned the transaction to early for this to happen - so the journal file was in the file-system, but no actual changes had been made to the database file. Try it with a really big transaction and you will see the corruption. Dan. > Greetings, > Darko F. > > > > Federico Granata wrote: >> 2008/6/3 Darko Filipovic <[EMAIL PROTECTED]>: >> >> >>> But, what happen if journal file is deleted before starting B >>> process? >>> >>> >> what if a UFO stole your pc ? :-D >> >> try to delete journal file and see what happens ... >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> __ NOD32 3154 (20080603) Information __ >> >> This message was checked by NOD32 antivirus system. >> http://www.eset.com >> >> >> >> > ___ > 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] transaction recovery question
> First - some sample code or queries would be helpful. > Second - start a new topic > (http://en.wikipedia.org/wiki/Thread_hijacking). > Yes, sorry, my fault, i am a lazy bum these days. My apologies. This was also an indirect test if this list is still alive.. Will start a new thread right away, thanks, Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
On 6/3/08, Christophe Leske <[EMAIL PROTECTED]> wrote: > Hi, > > i am new to this list, Welcome. But you have hijacked an existing thread. That will decrease your chances of getting folks to reply to you positively. Tip: Start a new thread for a different query. > can anyone point me to a good FAQ document on how > to improve the speed of a SQLite database? > > I got a city database (a geographical database) that I need to query for > lat/long values, and importance of the city (class value). > > For my smallest query, i am waiting several hundred milliseconds in a > database that is about 40Mb in size and that has indices on latitude and > longitude, as well as the class itself. > > I have indexed the database, analyzed it (in order to get the stats > table), and vacuumed it. > > Any other hint on how one can speed up the queries? I ahve set PRAGME > CACHE as well... You might do well to provide info on your exact schema as well as your exact query. Also, try the EXPLAIN command. Your query might not be using your indexes at all. > > Thanks in advance, > > > -- > Christophe Leske > > www.multimedial.de - [EMAIL PROTECTED] > http://www.linkedin.com/in/multimedial > Lessingstr. 5 - 40227 Duesseldorf - Germany > 0211 261 32 12 - 0177 249 70 31 > > -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
Welcome to the list! First - some sample code or queries would be helpful. Second - start a new topic (http://en.wikipedia.org/wiki/Thread_hijacking). RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske Sent: Tuesday, June 03, 2008 12:58 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] transaction recovery question Hi, i am new to this list, can anyone point me to a good FAQ document on how to improve the speed of a SQLite database? I got a city database (a geographical database) that I need to query for lat/long values, and importance of the city (class value). For my smallest query, i am waiting several hundred milliseconds in a database that is about 40Mb in size and that has indices on latitude and longitude, as well as the class itself. I have indexed the database, analyzed it (in order to get the stats table), and vacuumed it. Any other hint on how one can speed up the queries? I ahve set PRAGME CACHE as well... Thanks in advance, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ 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] transaction recovery question
Hi, i am new to this list, can anyone point me to a good FAQ document on how to improve the speed of a SQLite database? I got a city database (a geographical database) that I need to query for lat/long values, and importance of the city (class value). For my smallest query, i am waiting several hundred milliseconds in a database that is about 40Mb in size and that has indices on latitude and longitude, as well as the class itself. I have indexed the database, analyzed it (in order to get the stats table), and vacuumed it. Any other hint on how one can speed up the queries? I ahve set PRAGME CACHE as well... Thanks in advance, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
That's why I continue to monitor SQLite messages even when I'm not actively developing with SQLite. There is an inherent entertainment value that appears built in. Over time I'm certain I have been guilty of posting some shall we say "entertaining" messages myself. Sometimes I think SQLite is so perfect a solution that many users just sit around bored and come up with some off the wall subject just for the pure potential entertainment value. I find most of the "feature" requests and subsequent whining both for and against most entertaining. Many users must work for US auto makers. When a US auto maker builds a successful small car the first thing they do is start making it bigger, to the point it loses all its original market share and subsequent value. Fred -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P Kishor Sent: Tuesday, June 03, 2008 10:06 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] transaction recovery question On 6/3/08, Federico Granata <[EMAIL PROTECTED]> wrote: > 2008/6/3 Darko Filipovic <[EMAIL PROTECTED]>: > > > > But, what happen if journal file is deleted before starting B process? > > > > what if a UFO stole your pc ? :-D > > try to delete journal file and see what happens ... > my vote for the funniest message in a long time. ___ 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] transaction recovery question
Darko Filipovic <[EMAIL PROTECTED]> wrote: > The thing is that my system sometimes produces malformed database, > but I don't know what cause that. I'm trying to collect possible > cases when database gets malformed. I'm going in this direction > because it is not possible to debug system to reproduce condition. > Because of that question is: can lack of journal file produce > malformed database file? Yes (but not 100% of the time, as you seem to expect). See also http://sqlite.org/atomiccommit.html in particular section 2 "Hardware Assumptions" and 9 "Things That Can Go Wrong" Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
The thing is that my system sometimes produces malformed database, but I don't know what cause that. I'm trying to collect possible cases when database gets malformed. I'm going in this direction because it is not possible to debug system to reproduce condition. Because of that question is: can lack of journal file produce malformed database file? Greetings, Darko F. Igor Tandetnik wrote: > Darko Filipovic > <[EMAIL PROTECTED]> wrote: > >> I've tried...(not with UFO :D ). Nothing happens, database is not >> corrupted and that is what confuses me...I thought it should not be >> readable (malformed) ?! >> > > Not necessarily. Suppose you issued an update statement that was > supposed to update 100 records. Before the process crashed, 50 of them > were successfully updated (e.g. they just happened to sit on the same > page), but the other 50 were not. The database is not physically > corrupted - the table and record structure is intact. But it's logically > corrupted, in that some database invariants important to your > application may have been violated. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ NOD32 3154 (20080603) Information __ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
On 6/3/08, Darko Filipovic <[EMAIL PROTECTED]> wrote: > I've tried...(not with UFO :D ). Nothing happens, database is not > corrupted and that is what confuses me...I thought it should not be > readable (malformed) ?! The journal file is happily delete-able. So is the db itself. Try it. My sense is that if you delete the journal file, you lose the ability to roll back your database. Other than that, life continues to exist. > > Greetings, > Darko F. > > > > > Federico Granata wrote: > > 2008/6/3 Darko Filipovic <[EMAIL PROTECTED]>: > > > > > >> But, what happen if journal file is deleted before starting B process? > >> > >> > > what if a UFO stole your pc ? :-D > > > > try to delete journal file and see what happens ... > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > __ NOD32 3154 (20080603) Information __ > > > > > This message was checked by NOD32 antivirus system. > > http://www.eset.com > > > > > > > > > ___ > > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
Darko Filipovic <[EMAIL PROTECTED]> wrote: > I've tried...(not with UFO :D ). Nothing happens, database is not > corrupted and that is what confuses me...I thought it should not be > readable (malformed) ?! Not necessarily. Suppose you issued an update statement that was supposed to update 100 records. Before the process crashed, 50 of them were successfully updated (e.g. they just happened to sit on the same page), but the other 50 were not. The database is not physically corrupted - the table and record structure is intact. But it's logically corrupted, in that some database invariants important to your application may have been violated. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
On 6/3/08, Federico Granata <[EMAIL PROTECTED]> wrote: > 2008/6/3 Darko Filipovic <[EMAIL PROTECTED]>: > > > > But, what happen if journal file is deleted before starting B process? > > > > what if a UFO stole your pc ? :-D > > try to delete journal file and see what happens ... > my vote for the funniest message in a long time. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
I've tried...(not with UFO :D ). Nothing happens, database is not corrupted and that is what confuses me...I thought it should not be readable (malformed) ?! Greetings, Darko F. Federico Granata wrote: > 2008/6/3 Darko Filipovic <[EMAIL PROTECTED]>: > > >> But, what happen if journal file is deleted before starting B process? >> >> > what if a UFO stole your pc ? :-D > > try to delete journal file and see what happens ... > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ NOD32 3154 (20080603) Information __ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
2008/6/3 Darko Filipovic <[EMAIL PROTECTED]>: > But, what happen if journal file is deleted before starting B process? > what if a UFO stole your pc ? :-D try to delete journal file and see what happens ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
But, what happen if journal file is deleted before starting B process? Pozdrav, Darko F. Igor Tandetnik wrote: > Robert Lehr <[EMAIL PROTECTED]> wrote: > >> I have a question about recovering from a transaction that was not >> completed by a process b/c it terminated abnormally, e.g., careless >> SIGKILL or segfault. The scenario involves multiple processes having >> the database open. >> >> * process A opens the database >> * process B opens the database >> * process A starts a transaction >> * process A terminates abnormally BEFORE completing the >> transaction >> * process B starts a transaction >> >> the database is now in an indeterminate state. what happens in >> process B? >> > > http://sqlite.org/atomiccommit.html > > When B starts a transaction, it notices a "hot" rollback journal left > behind by process A. It then uses this journal to undo (roll back) any > changes process A may have made in the database file but haven't > committed. The database is restored to the state it was in before > process A started its transaction. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ NOD32 3153 (20080602) Information __ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
Robert Lehr <[EMAIL PROTECTED]> wrote: > I have a question about recovering from a transaction that was not > completed by a process b/c it terminated abnormally, e.g., careless > SIGKILL or segfault. The scenario involves multiple processes having > the database open. > > * process A opens the database > * process B opens the database > * process A starts a transaction > * process A terminates abnormally BEFORE completing the > transaction > * process B starts a transaction > > the database is now in an indeterminate state. what happens in > process B? http://sqlite.org/atomiccommit.html When B starts a transaction, it notices a "hot" rollback journal left behind by process A. It then uses this journal to undo (roll back) any changes process A may have made in the database file but haven't committed. The database is restored to the state it was in before process A started its transaction. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
Simple enough to test... just open two sqlite sessions and try it... Process B will recover the database when the transaction begins. Are you having an issue with sqlite doing something different? HTH Robert Lehr <[EMAIL PROTECTED]> wrote: I have a question about recovering from a transaction that was not completed by a process b/c it terminated abnormally, e.g., careless SIGKILL or segfault. The scenario involves multiple processes having the database open. * process A opens the database * process B opens the database * process A starts a transaction * process A terminates abnormally BEFORE completing the transaction * process B starts a transaction the database is now in an indeterminate state. what happens in process B? -rlehr Robert Lehr Cadence Design Systems, Inc ___ 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
