On 7 Feb 2016, at 1:51pm, Bernard McNeill wrote:
> Is it correct to say that, under Linux, if SQLITE_EXTRA_DURABLE is set (and
> all other settings are left as default values), and with the further
> assumption that the hardware
> reports write status accurately to the OS, then SQLITE_OK will on
===
https://www.sqlite.org/src/timeline?y=ci&c=af92401826f5cf49e62c
===
To clarify:
Is it correct to say that, under Linux, if SQLITE_EXTRA_DURABLE is set (and
all other settings are left as default values), and with the further
assumption that the hardware
reports write status accurately to t
Please can I formally propose that, for Linux:
1. A Pragma or other compile-time option is created such that SQLITE_OK is
not issued on file writes/modifications/deletes until the hardware
indicates that all associated Directory syncs, etc., are done.
2. Since the absence of 1. appears to break the
On 2/6/16, Bernard McNeill wrote:
> Please can I formally propose that, for Linux:
> 1. A Pragma or other compile-time option is created such that SQLITE_OK is
> not issued on file writes/modifications/deletes until the hardware
> indicates that all associated Directory syncs, etc., are done.
htt
On Wed, 03 Feb 2016 06:30:13 -0700
"Keith Medcalf" wrote:
>
> Is this on windows? Any errors in the Eventlogs to the tune "Oooopsie --
> accidentally threw away your data instead of writing it to disk"? Windows
> does this quite commonly under some circumstances. MicroSoft created the bug
> Is this on windows?
The original problem was on QNX 6.5 employing a QNX6 filesystem on mNAND
flash.
S.M.
--
Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer
Am Wolfsmantel 46, 91058 Tennenlohe, Germany
Tel: +49-8458-3332-531 stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3
ruary, 2016 14:58
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Bug: Successfully committed transaction rolled back
> after power failure
>
> On Thu, 28 Jan 2016 14:55:28 +
> Simon Slavin wrote:
>
> >
> > On 28 Jan 2016, at 1:38pm, Be
On Thu, 28 Jan 2016 14:55:28 +
Simon Slavin wrote:
>
> On 28 Jan 2016, at 1:38pm, Bernard McNeill wrote:
>
> > ===
> > Like the user reading ?saving OK? and throwing away the
> > Post-It with the original information
> > ===
> >
> > This is exactly my concern.
> > The user throwing away t
On Sun, 31 Jan 2016 20:27:56 -0700
Scott Robison wrote:
> On Sun, Jan 31, 2016 at 7:35 PM, Rowan Worth wrote:
>
> > 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?
On 2 February 2016 at 08:22, Stephen Chrzanowski
wrote:
> On Mon, Feb 1, 2016 at 11:20 AM, Rowan Worth wrote:
> 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, n
> Le 2 f?vr. 2016 ? 03:25, Rowan Worth a ?crit :
>
> To reiterate, I don't have a problem with sqlite's behaviour at all. I
> think it's perfectly acceptable. But I think it deserves to be acknowledged
> and understood for what it is; a pragmatic decision by sqlite to improve
> performance in the
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 responsi
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
On 1 February 2016 at 18:58, Simon Slavin wrote:
>
> On 1 Feb 2016, at 9:23am, bm.email01 at gmail.com wrote:
>
> > ---
> > 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
On Mon, Feb 1, 2016 at 11:20 AM, Rowan Worth wrote:
>
> > 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
This is steering off topic, but, here's my deal with computers as a whole.
Bluntly, I love them, I can't be without them, and I wouldn't change my
experiences with them for anything. That doesn't mean that I implicitly
trust them.
My expectations are "low" because experience has taught me to keep
At 17:55 01/02/2016, you wrote:
>The above about implementation of RAID is good. There were battery
>backed up caching controllers 20 years ago. In the event of a power
>loss, the cached writes could be completed later.
I run such one RAID Areca controller with 8 server disks in RAID6. Even
after
Stephen Chrzanowski wrote:
> @Rowan;
>
> First off, whether the OS or SQLite is ACID or not, if you pull the plug on
> your hardware, all bets are off on whether it'll BOOT, let alone recover a
> single transaction. I get that this could be a useful tool when doing
> disaster proofing, but, at tha
At the risk of repeating something mentioned last week on this thread.
One tactic to reduce/avoid the no-directory sync problem is to use WAL
mode. The commit in WAL is write to the WAL file, so the the directory
sync problem goes away.
If you want to be in paranoid mode, don't trust others. Why n
On 1 Feb 2016, at 11:24am, Rowan Worth wrote:
>
> I take your point, but as Florian pointed out it's not just one file
> system; its a somewhat well known quirk of POSIX fsync.
> http://blog.httrack.com/blog/2013/11/15/everything-you-always-wanted-to-know-about-fsync/
>
> It's a bit disingenuou
On 01/25/2016 04:47 PM, Richard Hipp wrote:
> On 1/25/16, Matthias-Christian Ott wrote:
>>
>> Does this mean that if I use SQLite SQLITE_EXTRA_DURABLE=0, PRAGMA
>> journal_mode=DELETE and PRAGMA synchronous=FULL, SQLite could loose a
>> transaction that it said to be committed depending on the VFS
On 1 Feb 2016, at 9:23am, bm.email01 at gmail.com wrote:
> ---
> 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 succes
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 de
On Mon, Feb 1, 2016 at 9:55 AM, Adam Devita wrote:
> At the risk of repeating something mentioned last week on this thread.
> One tactic to reduce/avoid the no-directory sync problem is to use WAL
> mode. The commit in WAL is write to the WAL file, so the the directory
> sync problem goes away.
>
ist
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
@Rowan;
First off, whether the OS or SQLite is ACID or not, if you pull the plug on
your hardware, all bets are off on whether it'll BOOT, let alone recover a
single transaction. I get that this could be a useful tool when doing
disaster proofing, but, at that stage in the game of bulletproofing,
On Sun, Jan 31, 2016 at 7:35 PM, Rowan Worth wrote:
> 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
On Sat, 30 Jan 2016 20:00:19 +
Simon Slavin wrote:
> On 30 Jan 2016, at 7:56pm, James K. Lowden
> wrote:
>
> > Given that the fsync has returned successfully, I don't know of any
> > hardware that then will take 1000 ms to complete the write. That's
> > the basis for my "subsecond interval
On 30 Jan 2016, at 7:56pm, James K. Lowden wrote:
> Given that the fsync has returned successfully, I don't know of any
> hardware that then will take 1000 ms to complete the write. That's the
> basis for my "subsecond interval" assumption.
Writing to a RAID which has other write commands que
On Thu, 28 Jan 2016 08:00:08 +
Meinlschmidt Stefan wrote:
> > But I ask you, what action could the application possibly take, in
> > that subsecond interval, that it matters?
>
> Under the QNX OS using a QNX6 filesystem with default configuration,
> that ?subsecond interval? is actually up t
ou pay for.
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Saturday, 30 January, 2016 13:00
> To: SQLite mailing list
> Subject: Re: [sqlite] Bug: Succes
===
...you must fsync the containing directory...
===
Is there an Sqlite option to achieve that?
In fact, to summarise:
Suppose I would like to maximise my chances of avoiding the 'Lost Post-It'
problem described above.
What are _all_ the Sqlite compile-time options, and their values, needed
und
Simon Slavin wrote:
>
> On 28 Jan 2016, at 1:38pm, Bernard McNeill wrote:
>
>> ===
>> Like the user reading ?saving OK? and throwing away the
>> Post-It with the original information
>> ===
>>
>> This is exactly my concern.
>> The user throwing away the Post-It is entirely reasonable if he sees a
On 28 Jan 2016, at 1:38pm, Bernard McNeill wrote:
> ===
> Like the user reading ?saving OK? and throwing away the
> Post-It with the original information
> ===
>
> This is exactly my concern.
> The user throwing away the Post-It is entirely reasonable if he sees a
> message like that.
>
> Do y
> ===
> Like the user reading ?saving OK? and throwing away the
> Post-It with the original information
> ===
>
> This is exactly my concern.
> The user throwing away the Post-It is entirely reasonable if he sees a
> message like that.
>
> Do you happen to know if Linux/Debian (which I think uses
===
Like the user reading ?saving OK? and throwing away the
Post-It with the original information
===
This is exactly my concern.
The user throwing away the Post-It is entirely reasonable if he sees a
message like that.
Do you happen to know if Linux/Debian (which I think uses a journalling
files
>> Using the standard defaults (which avoid WAL), is there any
>> possibility whatsoever of that last SQL transaction being lost?
>
> I have an unusual answer: Yes, and it doesn't matter.
While I agree in principle, your answer depends on some assumptions that
need not hold.
> Let's suppose, a
On Wed, 27 Jan 2016 08:51:16 +
Bernard McNeill wrote:
> Using the standard defaults (which avoid WAL), is there any
> possibility whatsoever of that last SQL transaction being lost?
I have an unusual answer: Yes, and it doesn't matter.
Let's suppose, as you did, that the application got b
On 25 January 2016 at 18:26, Meinlschmidt Stefan <
Stefan.Meinlschmidt at esolutions.de> wrote:
> > In your case it sounds like a controlled shutdown - is there a reason you
> > don't do a full disk sync before that?
>
> Yes, it is a controlled shutdown, so in my case the /* post-commit logic
> */
On 27 Jan 2016, at 8:51am, Bernard McNeill wrote:
> Situation: Under Linux/Debian, Sqlite opens an entirely valid DB, and runs
> an entirely valid SQL transaction against that database.
> Following a Commit, the application gets back a 'Commit Successful' code.
> (Ignore any issues of disks retu
Just to be clear
Situation: Under Linux/Debian, Sqlite opens an entirely valid DB, and runs
an entirely valid SQL transaction against that database.
Following a Commit, the application gets back a 'Commit Successful' code.
(Ignore any issues of disks returning hardware 'write done' flags
premature
Richard Hipp wrote:
> On 1/25/16, Howard Chu wrote:
>>
>> This is actually quite an unusual requirement; on older Unix systems you
>> couldn't even *open* a directory, let alone obtain write access to it or
>> fsync it.
>
> Yeah. When the SQLITE_DISABLE_DIRSYNC compile-time option is present,
> w
On Jan 25, 2016, at 8:47 AM, Richard Hipp wrote:
>
> The feedback I receive is that most users of SQLite would much rather
> avoid the extra directory syncs, even if it means having the last
> transaction rollback following a power loss.
Why not do the directory fsync in sqlite3_close_v2()?
As
On 2016/01/25 5:08 PM, Matthias-Christian Ott wrote:
> On 25/01/16 14:14, Richard Hipp wrote:
>> On 1/19/16, Meinlschmidt Stefan wrote:
>>> Shutting down power right after a successfully committed
>>> transaction rolls back that transaction on next startup.
>> Patches checked in:
>>
>> http
On 2016-01-25 16:47, Richard Hipp wrote:
> On 1/25/16, Matthias-Christian Ott wrote:
>>
>> Does this mean that if I use SQLite SQLITE_EXTRA_DURABLE=0, PRAGMA
>> journal_mode=DELETE and PRAGMA synchronous=FULL, SQLite could loose a
>> transaction that it said to be committed depending on the VFS?
>
Matthias-Christian Ott wrote:
> On 2016-01-25 16:47, Richard Hipp wrote:
>> On 1/25/16, Matthias-Christian Ott wrote:
>>>
>>> Does this mean that if I use SQLite SQLITE_EXTRA_DURABLE=0, PRAGMA
>>> journal_mode=DELETE and PRAGMA synchronous=FULL, SQLite could loose a
>>> transaction that it said to
On Mon, Jan 25, 2016 at 5:08 PM, Richard Hipp wrote:
> On 1/25/16, Stephen Chrzanowski wrote:
> >
> > You also have to look at balance across many millions (or is it
> billions?)
> > of devices out there that use SQLite for their primary operations.
>
> Billions and billions.
>
https://en.wikip
On 1/25/16, Howard Chu wrote:
>
> This is actually quite an unusual requirement; on older Unix systems you
> couldn't even *open* a directory, let alone obtain write access to it or
> fsync it.
Yeah. When the SQLITE_DISABLE_DIRSYNC compile-time option is present,
we disable the directory sync lo
On 25/01/16 14:14, Richard Hipp wrote:
> On 1/19/16, Meinlschmidt Stefan wrote:
>>
>> Shutting down power right after a successfully committed
>> transaction rolls back that transaction on next startup.
>
> Patches checked in:
>
> https://www.sqlite.org/src/info/30671345b1c1ee55
> https:
On 1/25/16, Stephen Chrzanowski wrote:
>
> You also have to look at balance across many millions (or is it billions?)
> of devices out there that use SQLite for their primary operations.
Billions and billions.
> Slapping a serious performance decrease on devices where time and
> performance is e
On Mon, Jan 25, 2016 at 10:08 AM, Matthias-Christian Ott
wrote:
>
> If so, why isn't SQLITE_EXTRA_DURABLE=1 the default? Should correctness
> be more important than performance, except when the constraints are such
> that correctness has to be sacrificed for performance?
>
I wouldn't want that,
On 1/25/16, Matthias-Christian Ott wrote:
>
> Does this mean that if I use SQLite SQLITE_EXTRA_DURABLE=0, PRAGMA
> journal_mode=DELETE and PRAGMA synchronous=FULL, SQLite could loose a
> transaction that it said to be committed depending on the VFS?
Sort of. This appears to be true if you are ru
Hi Rowan!
>> Shutting down power right after a successfully committed
>> transaction rolls back that transaction on next startup.
>
> nitpick: This is sqlite behaving as advertised. See
> https://www.sqlite.org/lockingv3.html section 5.0 step 6, and
> https://www.sqlite.org/atomiccommit.html sect
On 1/19/16, Meinlschmidt Stefan wrote:
>
> Shutting down power right after a successfully committed
> transaction rolls back that transaction on next startup.
Patches checked in:
https://www.sqlite.org/src/info/30671345b1c1ee55
https://www.sqlite.org/draft/compile.html#extra_durable
--
On 1/19/16, Meinlschmidt Stefan wrote:
>
> Shutting down power right after a successfully committed
> transaction rolls back that transaction on next startup.
As you observe, this is a file-system dependent thing, and probably
only happens on the QNX filesystem. I will see if we can add a
compil
> Shutting down power right after a successfully committed
> transaction rolls back that transaction on next startup.
nitpick: This is sqlite behaving as advertised. See
https://www.sqlite.org/lockingv3.html section 5.0 step 6, and
https://www.sqlite.org/atomiccommit.html section 3.11 which explai
Hi everybody!
TL;DR:
Shutting down power right after a successfully committed
transaction rolls back that transaction on next startup. This is a
problem in write-on-shutdown-then-power-off scenarios and violates my
expectation of SQLite's transactions being ACID. This can be fixed by
setting the
57 matches
Mail list logo
