[sqlite] Bug: Successfully committed transaction rolled back after power failure

[bm.emai...@gmail.com]

Maybe, for Linux, there could be a  Pragma to choose between 
fsync-directory-on-commit,
fsync-directory-on-close, or no fsync-directory.

Particularly if it is a documented property (not unintended bug) that Linux 
filesystem operates this way.

Best regards

Sent from my BlackBerry? smartphone on O2

-Original Message-
From: Rowan Worth 
Sender: sqlite-users-bounces at mailinglists.sqlite.orgDate: Tue, 2 Feb 2016 
00:20:55 
To: SQLite mailing list
Reply-To: SQLite mailing list 
Subject: Re: [sqlite] Bug: Successfully committed transaction rolled back
 after power failure

Hi Stephen,

On 1 February 2016 at 21:45, Stephen Chrzanowski 
wrote:
>
> SQLite is nothing more than part of a program run by the OS.  It completely
> relies on whatever the OS tells it.  If the OS tells it that things are OK,
> then that is all that can be done.  SQLite can't take on the responsibility
> of making sure that my taxes are done right, it won't ever wash my car (I'd
> be afraid the T would scratch the paint anyways) and it absolutely cannot
> defend against the lies an OS gives it.


Of course! SQLite is a remarkably reliable bit of software and I hold it in
high regard, but as you say software has its limits. I'm not one to expect
more of SQLite then it can reasonably provide.

Going back to the OS, I've seen instance, in both Windows and Linux based
> platform (RH and Debian) where file sizes do not change even though streams
> of data are being pumped into that particular file.


Stands to reason on linux if the directory hasn't been synced to disk yet.
The file's dirent is stored in a completely independent extent.


> I've seen situations
> where files have been deleted in Linux (Not allowed in Windows) and data
> still gets dumped into the now deleted file.


?
This is not an error, this is standard POSIX file semantics - if you have a
file descriptor referring to a file which gets deleted from disk, the file
descriptor is guaranteed to remain valid until it is closed. NFS even goes
to some lengths to make this work.

Is it the programs fault that
> the file is physically deleted but the program isn't aware?  Is it supposed
> to check every single time it writes a byte to see if the file exists, if
> the partition is healthy, and the root file system passes a FSCK at every
> commit?
>

:)


> SQLite doesn't know or care what sector the bytes
> live at on the drive physically.  It doesn't know about fragmentation, it
> doesn't know what size the clusters are, or anything that is important for
> file storage.


But SQLite *does* know how to interface with the file system. It's all very
well to say the OS/hardware sometimes lies, but in this specific instance
SQLite hasn't even asked the question.

As I indicated in the last paragraph of my mail, I'm not in favour of
fsync-directory-on-commit in the general case. But that's because I worry
about the performance impact, not because SQLite is doing its best and at
the mercy of the OS. I can't remember who in this thread suggested it but
fsync-directory-on-close seemed like a great compromise.

-Rowan
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[bm.emai...@gmail.com]

---
No, SQLite does not. On COMMIT it fsyncs the database file and unlinks the
journal[1], but does not fsync the directory.
---

Since that can cause the last transaction to be lost, despite Sqlite returning 
a 'Commit successful' code to the application, doesn't that mean that Sqlite is 
_not_ truly 'Durable' (per ACID)?

Best regards



Sent from my BlackBerry? smartphone on O2

-Original Message-
From: Rowan Worth 
Sender: sqlite-users-bounces at mailinglists.sqlite.orgDate: Mon, 1 Feb 2016 
10:35:34 
To: SQLite mailing list
Reply-To: SQLite mailing list 
Subject: Re: [sqlite] Bug: Successfully committed transaction rolled back
 after power failure

On 31 January 2016 at 03:56, James K. Lowden 
wrote:

> Surely SQLite does both -- fsync on file and directory -- as part of a
> commit. That's not in doubt, is it?
>

No, SQLite does not. On COMMIT it fsyncs the database file and unlinks the
journal[1], but does not fsync the directory. This is demonstrated by the
logs in Stefan's initial post, and indeed the purpose of his patch was to
introduce an fsync on the directory after unlinking the journal.

[1] with the default PRAGMA journal_mode=delete, obviously
-Rowan
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users