[sqlite] Bug: Successfully committed transaction rolled back after power failure
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 'D' in ACID, the option is set on by default, but the docs make the point that it could produce a substantial performance hit. The docs could also make the point that if the hardware returns incorrect status to the OS, then, even if the option is on, 'D' may still not be true (but not Sqlite's fault). Best regards On Mon, Jan 25, 2016 at 3: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? > > Sort of. This appears to be true if you are running on QNX and you > lose power (or do a hard reset) shortly after the transaction commits. > It might be the case on other OSes/filesystems but it has never before > been observed. > > This is not new behavior. This is apparently what SQLite has been > doing for 15 years, across quadrillions of transactions on many > billions of devices, and it has never before caused any issues, until > just recently when Mr. Meinlschmidt upgraded to a newer version of > QNX. > > > > > 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? > > > > The trade-off that is described in the description of SQLite > > SQLITE_EXTRA_DURABLE reads like an excerpt from the MySQL manual when > > MyISAM was still widely used. Perhaps I'm also too irritated by > > discussions with advocates of MySQL who would argue against the fact > > that proper transactions were necessary because the DBMS would be faster > > without them. That is not to say that the ACID properties and > > transactions solve every concurrency or correctness problem but they > > help significantly. > > > > As you point out, it is an engineering tradeoff. > > 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. Most developers do not > care that much about durability, at least not enough to want to take > the performance hit of syncing the directory after every unlink. > Non-durable commits on power-loss have long been the default in WAL > mode (run-time fixable by setting PRAGMA synchronous=FULL) and nobody > has before ever complained. Most people consider this a feature. In > fact, if I recall correctly, we first made synchronous=NORMAL the > default in WAL mode by popular request. WAL mode used to default to > power-loss durable but people requested the change for performance > reasons. > > Note especially that this is about durability, not consistency. > SQLite guarantees consistency regardless. People care about > consistency. Durability, not so much. I'm not a MySQL expert, but I > think the historical complaints about MyISAM had more to do with > consistency than with durability, did they not? > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Code Cleanup - Variable Definition - malloc.c - sqlite3DbMallocRawNN
Variable Definitions only supported at start of block before code in older
(MSVC) compilers.
@@ -22479,15 +22479,15 @@
p = sqlite3Malloc(n);
sqlite3MemdebugSetType(p, MEMTYPE_HEAP);
return p;
}
SQLITE_PRIVATE void *sqlite3DbMallocRawNN(sqlite3 *db, u64 n){
+ LookasideSlot *pBuf;
assert( db!=0 );
assert( sqlite3_mutex_held(db->mutex) );
assert( db->pnBytesFreed==0 );
#ifndef SQLITE_OMIT_LOOKASIDE
- LookasideSlot *pBuf;
if( db->lookaside.bDisable==0 ){
assert( db->mallocFailed==0 );
if( n>db->lookaside.sz ){
db->lookaside.anStat[1]++;
}else if( (pBuf = db->lookaside.pFree)==0 ){
---
Life should not be a journey to the grave with the intention of arriving safely
in a pretty and well preserved body, but rather to skid in broadside in a cloud
of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow!
What a Ride!"
-- Hunter S. Thompson
[sqlite] json_group_array
I just love this list and the contained discussions. Doffing my cap to Keith and his succinct and very useful summary of the subtle differences regarding aggregate type functions. Especially like the nested stuff, which I must admit had not really occurred to me. Nice stuff Keith and thanks for sharing that info. It also clarifies the underlying fact that these functions are often slightly misunderstood even by experienced SQL hacks. On 6 February 2016 at 07:24, Keith Medcalf wrote: > > count(*) counts the rows of the result set selected > count(column) counts the NOT NULL values in the column of the result set > selected > count(DISTINCT column) counts the number of distinct values (excluding > NULLs) in the column of the result set selected > > count(column IS NULL) is equivalent to count(*) (the expression always > returns true (1) or false(0) > count(column IS NOT NULL) is equivalent to count(*) for every row so > therefore all rows are counted) > > sum(column IS NULL) returns the count of the number of rows in which the > column is null > sum(column IS NOT NULL) returns the count of the number of rows in which > the column is not null > > These can be combined, so for example count(DISTINCT column) + sum(column > IS NULL) returns the number of unique values in the column where NULLs are > distinct. > and count(DISTINCT column) + (sum(column IS NULL) > 0) returns the number > of unique values where NULLs are not distinct. > > and so on and so forth > > > -Original Message- > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > > bounces at mailinglists.sqlite.org] On Behalf Of R Smith > > Sent: Friday, 5 February, 2016 00:55 > > To: sqlite-users at mailinglists.sqlite.org > > Subject: Re: [sqlite] json_group_array > > > > > > > > On 2016/02/05 6:34 AM, TJ O'Donnell wrote: > > > I can't argue for the correctness of including nulls in aggregate > > functions > > > or not. > > > It truly is an arbitrary decision meant for standards-makers. Yet, > most > > > aggregate > > > function do not include nulls. Interestingly, some SQL's do include > > them > > > in count() > > > but sqlite does not. In my example table, select count(a) from x > > returns 3, > > > but select count(*) from x returns 4 even though a is the only column. > > > I haven't tried every sqlite agg, but I think they all exclude null, > > except > > > json_group_array > > > and json_group_object. > > > > I think you are mistaken in your understanding. While JSON has some > > rules and some conventions, when used inside an SQL engine, the rules of > > SQL needs to be adhered to before any "convention" of JSON. > > Not showing Null values in JSON is a convention, not a rule. (Else, why > > else would json even need the 'NULL' construct?) > > > > Further to this, in SQL, how would you know how many elements are > > present in a json array and which of them are null if there is no way to > > output them? Agreed, sometimes it isn't needed to know, but then you are > > welcome to exclude them via the WHERE clause. > > > > You are also mistaken about the SQL convention and SQLite-specific > > operations re. Nulls - If I have a table t with one single column "a" > > with 3 rows (2 text values and one null value) then doing SELECT > > COUNT(a) FROM t; will show 2 and SELECT COUNT(*) FROM t; will show 3, as > > it should - yes, even though a is the only column. The * doesn't mean > > "a", even if the only column is "a". It means "all the DB rows" and so > > include nulls. (The standard might be hazy on this, I didn't check, but > > this is definitely how SQLite works, and not as you suggested). > > > > This is also very important. Sometimes we'd want to know how many rows > > are in the DB, not JUST which non-null rows are in the only column in > > the DB - that is why we can decide to use either COUNT(a) or COUNT(*), > > or more deliberate with an explicit GROUP BY clause. I would never want > > this convention to be altered. > > > > > > > As a side issue here, but important still I think, what should > > json(null) > > > mean? > > > In my table x, select json(a) from x returns valid json integers for > > > non-null rows, > > > but return a sql null (a blank from command-llne sqlite) not a json > null > > > (which would > > > be the string null) when a is null. In other words, json(null) returns > > > null, > > > not 'null'. > > > > Here I'm with you - the null should output 'null' > > (Devs: I'm guessing this might be an oversight in the CLI rather than > > the SQL engine?) > > > > > I know the json stuff is new in sqlite, but I think it's worth getting > > > these issues worked > > > out, considering how useful json has become. > > > > Right you are, but first the issues need discovery - which is what is > > happening in this very thread. :) > > > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqli
[sqlite] Bug: Successfully committed transaction rolled back after power failure
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. https://www.sqlite.org/src/timeline?y=ci&c=af92401826f5cf49e62c > 2. Since the absence of 1. appears to break the 'D' in ACID, the option is > set on by default, No. This would be a serious performance hit for billions and billions of devices that have soldered-in batteries and generally do not care about durability in the event of an OS crash. Remember that SQLite has never synced directories like this before in its 15+ year history and that was never a problem for anyone until a few weeks ago. If handset manufacturers, and/or OS vendors, want to make it the default on their systems, there is a compile-time option for that: https://www.sqlite.org/draft/compile.html#extra_durable -- D. Richard Hipp drh at sqlite.org
[sqlite] Virtual Table Example?
Hello, Does anyone know where to find an example project that creates a SQLite virtual table module using a web service as the data source? Or if anyone has done this type of work, please contact me directly. Thank you, John Stegeman
