On 2016/11/15 10:34 PM, Jens Alfke wrote:
On Nov 15, 2016, at 10:57 AM, Simon Slavin <slav...@bigfraud.org> wrote:
sqlite> PRAGMA checkpoint_fullfsync;
1
I wasn’t aware of that pragma. Just tried it on my Mac (10.12.1), and its value
is 1 even if I don’t first set pragma fullfsync; i.e. it defaults to 1.
(Contradicting the docs.)
So it appears that on macOS, SQLite does use F_FULLFSYNC when checkpointing,
but not at other times that it fsyncs. What does that mean in actual use,
assuming that I always use WAL mode? Is there still an opportunity for
corruption in the face of power failures?
(Sorry to be frothing at the mouth about this; but my team’s dealing with a few
users/customers whose apps encounter db corruption, on Android as well as
macOS, and we’re getting really frustrated trying to figure out what’s going
on.)
Quite OK to be unsettled by learning that a flaw in the system that you
assumed did not exist, might be the cause of your troubles. I think
however something is missing in the complete understanding, so to be clear:
Calling F_FULLFSYNC when checkpointing or otherwise invokes a contract
between the running software (your system) and the Operating System
whereby the Operating system promises to A - put the current buffer's
worth of written data INTO the BUS feeding the writable media, AND B -
then asking said media to confirm the writing has happened (committed)
BEFORE handing back control (moving your thread pointer along). This is
not exactly the same for all OSes, but more or less similar.
This can slow down things, but sometimes the security is worth the price
paid in latency. However, Hard drive manufacturers tend to sometimes lie
about having committed your data. It is a well known and almost
universally used method in standard desktop / laptop computers for the
harddrive to tell the OS that: "YES indeed, I have committed" when in
fact it is still piping data into the platters. Yes SSD's are better at
this by simple virtue of lower latency from buffer-to-silicone, but they
are not above lying either.
This means that unless you have a SERVER quality drive with typically
its own battery-backup that guarantees ANY buffered writes to reach the
platters, there simply is zero guarantee that all writes WILL go to
disk, and any normal system that guarantees it lies.
This does not mean however that you should be experiencing corruption.
SQLite might not be able to guarantee all writes reaching the disk, but
in most cases, if a final write did not happen, the usual last step in
committing a transaction is deleting / truncating a journal file or
writing a checkpoint marker or such, which, if it did not happen, should
have the entire write roll back (next time you open the DB) and leave
you in a non-corrupt state. IF this does not happen it means a write may
have happened out of order (not very common but can happen) or some
other worse problem occurred - most importantly, FULL_FSYNC isn't the
wild goose to be chasing. Whether or not any write happened is never an
acceptable cause of corruption, so trying to wrestle with the thing that
promises to make writes happen "more" as a causal relation to a
corruption problem, is simply moot. (This is vigorously tested with
every release of SQLite too).
If you can get the DB files (journals and all) from such a system where
a user claims to be able to reproduce the corruption reliably, that
would be an easy thing to check and the Devs here might learn something
from it. You can simply make something that copy all the DB files before
opening them at startup, until you have produced a corrupt DB, then
those last copied files will be the corrupted DB files that can be
investigated.
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
