Re: [sqlite] Effectiveness of PRAGMA integrity_check;

2004-04-15 Thread Greg Miller
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;

2004-04-15 Thread D. Richard Hipp
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;

2004-04-15 Thread Greg Miller
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;

2004-04-15 Thread Andrew Piskorski
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;

2004-04-15 Thread Greg Miller
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;

2004-04-15 Thread D. Richard Hipp
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;

2004-04-15 Thread D. Richard Hipp
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;

2004-04-15 Thread Liz Steel
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;

2004-04-14 Thread Liz Steel
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;

2004-04-14 Thread D. Richard Hipp
[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]


[sqlite] Effectiveness of PRAGMA integrity_check;

2004-04-13 Thread ben . carlyle
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]