Re: [sqlite] Effectiveness of PRAGMA integrity_check;
D. Richard Hipp wrote: From what I am told, most IDE drives do signal the OS when the data reaches the platter. I'm also told that the Linux fsync() call does not return until it gets that signal. The Windows FlushFileBuffers(), on the other hand, does not wait for the data to get to platter. So on a windows system, there is a brief moment of vulnerability where a power loss can lose data. But on Linux, that window of vulnerability is zero. The above is how IDE drives are *suppose* to work. There is wide- spread suspicion that many cheap IDE drives do not implement the protocol correctly. If your have one of those broken IDE disks, all bets are off. Keep in mind that I'm simply parroting my interpretation of the discussions over on the mailing lists at freebsd.org... You might want to go straight to the horse's mouth instead of having it filtered (possibly incorrectly) through me. :) I am also told that the Linux IDE driver is broken with respect to media errors. If the disk drive has a media error, Linux does not take appropriate corrective action, nor does it alert the user-space code. I don't know how true this is or if it is really a problem. (How common are media errors?) Not very common, but I don't anything about the Linux ATA driver, so I couldn't begin to guess just how badly broken it might or might not be. Regardless of the situation, though, the window of vulnerability during which a power loss might cause database corruption is small. And Liz is reporting that she can reproduce the corruption consistently. So perhaps her trouble have a different cause. Even a small window could do the job if it's being written to at a high rate of speed. By the time one set of writes actually hits the disk, more may be in flight. Dunno, there could be any number of factors contributing to this. I guess the moral of the story is that reliable power is important. -- http://www.classic-games.com/ http://www.indie-games.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Effectiveness of PRAGMA integrity_check;
Greg Miller wrote: Liz Steel wrote: You say that I shouldn't get a corrupt database when I pull the power, but I am consistently getting this. I am using SQLite version 2.8.9 using the C++ interface running on Windows XP Home. Is there anything I can do to stop this happening? If you have an IDE hard drive that's caching writes, there's not much the OS and database software can do to prevent corruption on power loss. It's possible to avoid this with tagged queueing, but most drives don't support that. The FreeBSD folks tried to solve this by turning off write caching by default. Unfortunately, this hurt performance so much they had to turn it back on and just recommend SCSI drives for important data. I looked into this some and came back with different information. Who can tell me what is right? From what I am told, most IDE drives do signal the OS when the data reaches the platter. I'm also told that the Linux fsync() call does not return until it gets that signal. The Windows FlushFileBuffers(), on the other hand, does not wait for the data to get to platter. So on a windows system, there is a brief moment of vulnerability where a power loss can lose data. But on Linux, that window of vulnerability is zero. The above is how IDE drives are *suppose* to work. There is wide- spread suspicion that many cheap IDE drives do not implement the protocol correctly. If your have one of those broken IDE disks, all bets are off. I am also told that the Linux IDE driver is broken with respect to media errors. If the disk drive has a media error, Linux does not take appropriate corrective action, nor does it alert the user-space code. I don't know how true this is or if it is really a problem. (How common are media errors?) Regardless of the situation, though, the window of vulnerability during which a power loss might cause database corruption is small. And Liz is reporting that she can reproduce the corruption consistently. So perhaps her trouble have a different cause. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Effectiveness of PRAGMA integrity_check;
Andrew Piskorski wrote: On Thu, Apr 15, 2004 at 08:33:14AM -0500, Greg Miller wrote: support that. The FreeBSD folks tried to solve this by turning off write caching by default. Unfortunately, this hurt performance so much they had to turn it back on and just recommend SCSI drives for important data. Why, do SCSI drives all come with battery-backed cache? (So when you power them up again they complete the old cached write.) I didn't think so, but would be pleased to learn otherwise... No, but the OS can ensure that the ordering constraints are honored on any writes that actually make it to the disk. That's the only constraint the OS makes anyway, since it ensures that the only disk corruption that can occur is that some disk space that is currently unused may still appear to be in use. Then when the system boots after a failure, the system snapshots the disk, and fsck runs in the background to free up that unused space in the background. That's how FreeBSD avoids journalling. Of course, with a good UPS *AND* the proper software running to react to signals from the UPS, you get that sort of protection for free, and you certainly want the system UPS anyway. But that's also much more complicated and vulnerable to failures due to misconfigured software, so it'd sure be nice to have the hard-drive-UPS as well. I suspect there's just not enough demand. People that need the safety just go out and by SCSI drives, IDE drives with tagged queueing, or a general purpose UPS. -- http://www.classic-games.com/ http://www.indie-games.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Effectiveness of PRAGMA integrity_check;
On Thu, Apr 15, 2004 at 08:33:14AM -0500, Greg Miller wrote: > support that. The FreeBSD folks tried to solve this by turning off write > caching by default. Unfortunately, this hurt performance so much they > had to turn it back on and just recommend SCSI drives for important data. Why, do SCSI drives all come with battery-backed cache? (So when you power them up again they complete the old cached write.) I didn't think so, but would be pleased to learn otherwise... Is there any device available which basically serves as an add-on UPS for hard drives? Something you'd just plug all your IDE drives into, and it would keep the drives going just long enough after a power fail to finish writing their cached data and spin down cleanly? AFAIK no such device is available, but if reasonably priced it sure would be nice to have. Of course, with a good UPS *AND* the proper software running to react to signals from the UPS, you get that sort of protection for free, and you certainly want the system UPS anyway. But that's also much more complicated and vulnerable to failures due to misconfigured software, so it'd sure be nice to have the hard-drive-UPS as well. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Effectiveness of PRAGMA integrity_check;
Liz Steel wrote: You say that I shouldn't get a corrupt database when I pull the power, but I am consistently getting this. I am using SQLite version 2.8.9 using the C++ interface running on Windows XP Home. Is there anything I can do to stop this happening? If you have an IDE hard drive that's caching writes, there's not much the OS and database software can do to prevent corruption on power loss. It's possible to avoid this with tagged queueing, but most drives don't support that. The FreeBSD folks tried to solve this by turning off write caching by default. Unfortunately, this hurt performance so much they had to turn it back on and just recommend SCSI drives for important data. -- http://www.classic-games.com/ http://www.indie-games.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Effectiveness of PRAGMA integrity_check;
D. Richard Hipp wrote: (1) Change to version 2.8.13. (2) Describe in detail what kind of changes you are making to the database as you pull the power. (3) Send me one of your corrupt databases for analysis. (4) Begin with a database that passes a "PRAGMA integrity_check". Do whatever it is you do to make it go corrupt. But before you open the database file again, make a copy of both the database and the journal. Open the database again to make sure it really did go corrupt. Then send me both the database and the journal. (5) In step (4), also make a copy of the database before it went corrupt - when it passed the "PRAGMA integrity_check" and send me that copy along with the corrupt database and the journal. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Effectiveness of PRAGMA integrity_check;
Liz Steel wrote: Hello again, I'm not sure if you received my last email, so I'm sending it to the list in the hope that someone can help me. You say that I shouldn't get a corrupt database when I pull the power, but I am consistently getting this. I am using SQLite version 2.8.9 using the C++ interface running on Windows XP Home. Is there anything I can do to stop this happening? (1) Change to version 2.8.13. (2) Describe in detail what kind of changes you are making to the database as you pull the power. (3) Send me one of your corrupt databases for analysis. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Effectiveness of PRAGMA integrity_check;
Hello again, I'm not sure if you received my last email, so I'm sending it to the list in the hope that someone can help me. You say that I shouldn't get a corrupt database when I pull the power, but I am consistently getting this. I am using SQLite version 2.8.9 using the C++ interface running on Windows XP Home. Is there anything I can do to stop this happening? Thanks, Liz. Original Message Follows From: "D. Richard Hipp" <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED] Subject: Re: [sqlite] Effectiveness of PRAGMA integrity_check; Date: Wed, 14 Apr 2004 10:50:28 -0400 Liz Steel wrote: I am trying to do a similar sort of thing with my database. The only way I've found to fairly reliably create a corrupt database file is to pull the battery out of my laptop whilst my application is accessing the database I've just tried it, and I get a code 11 (SQLITE_CORRUPT) returned from the PRAGMA integrity_check command. Is this correct behaviour? No, this is not correct. SQLite is suppose to survive an abrupt power loss with no loss of data. (Uncommitted transactions will be rolled back, but committed transactions should persist and be consistent.) I believe that SQLite does survive power loss without problems on Linux. However, I have received reports that the windows API function FlushFileBuffers() sometimes lies and does not really flush contents to the disk surface as it claims it does. This is just hearsay - I have not independently verified those reports. If FlushFileBuffers() does lie and a power loss occurred in the middle of a COMMIT, then database corruption is possible on windows. This is a bug in the OS and there is not anything SQLite (or any other database engine) can do about it. There was a bug in SQLite version 2.8.12 that could cause database corruption if a power loss occurred at a particularly inauspicious moment in the middle of a COMMIT. That problem was fixed with version 2.8.13. If you are seeing database corruption following power loss on Linux with SQLite version 2.8.13, please let us know about it right away. If you are seeing corruption on Windows, let us know there too - the problem might be the FlushFileBuffers() bug or it might be something else - either way we want to investigate. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Effectiveness of PRAGMA integrity_check;
Liz Steel wrote: I am trying to do a similar sort of thing with my database. The only way I've found to fairly reliably create a corrupt database file is to pull the battery out of my laptop whilst my application is accessing the database I've just tried it, and I get a code 11 (SQLITE_CORRUPT) returned from the PRAGMA integrity_check command. Is this correct behaviour? No, this is not correct. SQLite is suppose to survive an abrupt power loss with no loss of data. (Uncommitted transactions will be rolled back, but committed transactions should persist and be consistent.) I believe that SQLite does survive power loss without problems on Linux. However, I have received reports that the windows API function FlushFileBuffers() sometimes lies and does not really flush contents to the disk surface as it claims it does. This is just hearsay - I have not independently verified those reports. If FlushFileBuffers() does lie and a power loss occurred in the middle of a COMMIT, then database corruption is possible on windows. This is a bug in the OS and there is not anything SQLite (or any other database engine) can do about it. There was a bug in SQLite version 2.8.12 that could cause database corruption if a power loss occurred at a particularly inauspicious moment in the middle of a COMMIT. That problem was fixed with version 2.8.13. If you are seeing database corruption following power loss on Linux with SQLite version 2.8.13, please let us know about it right away. If you are seeing corruption on Windows, let us know there too - the problem might be the FlushFileBuffers() bug or it might be something else - either way we want to investigate. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Effectiveness of PRAGMA integrity_check;
Hello!
I am trying to do a similar sort of thing with my database. The only way
I've found to fairly reliably create a corrupt database file is to pull the
battery out of my laptop whilst my application is accessing the database. I
haven't used the "PRAGMA integrity_check;" command, but I will try it now
and see if that detects my corrupt database. At the moment, I am selecting
every row from every table, but I can see this getting a bit slow when my
database gets bigger.
I've just tried it, and I get a code 11 (SQLITE_CORRUPT) returned from the
PRAGMA integrity_check command. Is this correct behaviour? Will I always get
this error returned? The website says something about it returning "ok" but
I didn't check this as I am using sqlite_exec.
I haven't managed to find anything on the website about using the journal
file that is created. Can anyone point me in the right direction to using
this file to re-create my database? Would I need to take a backup of the
database file before every transaction to use this?
Thanks,
Liz.
Original Message Follows
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: [sqlite] Effectiveness of PRAGMA integrity_check;
Date: Wed, 14 Apr 2004 15:48:57 +1000
G'day,
I'm trying to write some defensive code that is able to recover from
database corruption. The idea is that if a disk fails and a database
becomes corrupt it can be detected and synchronised from a backup copy.
To this end, I've just been trying to write a function that returns true
only when it is sure the database is ok. I use PRAGMA integrity check; and
compare the returned string with "ok". When I tried this with a few random
database changes, though, I had a hard time trying to get the corruption
to trigger. I did the following:
CREATE TABLE foo(bar);
INSERT INTO foo VALUES("bar");
I then went in with a text editor and started modifying the bar record. I
changed "bar" to "car", but the change was not detected. I started
modifying characters to the left and right of the "car" string, but still
no corruption. I was able to get corruption to be detected when I
truncated the file.
Can I take it from this behaviour that there isn't any checksum checking
going on apart from headers and the BTrees themselves? Will the
integrity_check at least guarantee me that I won't at some later stage get
an SQLITE_CORRUPT return?
Benjamin.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
_
Tired of 56k? Get a FREE BT Broadband connection
http://www.msn.co.uk/specials/btbroadband
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Effectiveness of PRAGMA integrity_check;
[EMAIL PROTECTED] wrote:
I'm trying to write some defensive code that is able to recover from
database corruption. The idea is that if a disk fails and a database
becomes corrupt it can be detected and synchronised from a backup copy.
To this end, I've just been trying to write a function that returns true
only when it is sure the database is ok. I use PRAGMA integrity check; and
compare the returned string with "ok". When I tried this with a few random
database changes, though, I had a hard time trying to get the corruption
to trigger. I did the following:
CREATE TABLE foo(bar);
INSERT INTO foo VALUES("bar");
I then went in with a text editor and started modifying the bar record. I
changed "bar" to "car", but the change was not detected. I started
modifying characters to the left and right of the "car" string, but still
no corruption. I was able to get corruption to be detected when I
truncated the file.
Can I take it from this behaviour that there isn't any checksum checking
going on apart from headers and the BTrees themselves? Will the
integrity_check at least guarantee me that I won't at some later stage get
an SQLITE_CORRUPT return?
PRAGMA integrity_check does a good job of testing the
integrity of the BTree layer in the file. (See
http://www.sqlite.org/fileformat.html for a definition
of the "btree layer".) The pragma also checks to make
sure that all indices are correct. But other than that,
changes to the database can easily go undetected.
Note that if you had had an index on the foo table,
your change of "bar" to "car" would have been detected
by the index checks.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
