[sqlite] Potential bug with compile-time option SQLITE_OMIT_AUTOVACUUM

2017-10-10 Thread Bernard Schurdevin

Hi,

Building amalgamation sqlite3.c with option SQLITE_OMIT_AUTOVACUUM=1 
leads to malformed database on following case :


Windows 7 x64, mingw64 5.4 : gcc -s -static -DSQLITE_OMIT_AUTOVACUUM=1 
shell.c sqlite3.c -o sqlite3.exe


using CLI (compiled with SQLITE_OMIT_AUTOVACUUM=1) :

.open base.db
CREATE TABLE foo (num INTEGER PRIMARY KEY,comment TEXT COLLATE NOCASE) 
WITHOUT ROWID;

PRAGMA integrity_check; ===> ok
DROP TABLE foo;
Error: database disk image is malformed

CLI refuses to drop the table and don't corrupt database, but direct 
usage of API (sqlite3_step) corrupt the database :

*** in database main ***
2nd reference to page ...

Best regards

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


Re: [sqlite] potential bug

2017-04-17 Thread Dan Kennedy

On 04/17/2017 10:42 PM, Bernard Schurdevin wrote:

Hi,

I get weird results (false positive) to PRAGMA foreign_key_check on 
WITHOUT ROWID table depending on foreign key field position.


Thanks for reporting this. Should be fixed here:

  http://www.sqlite.org/src/info/690870bd7b2e607b

Dan.






Kind regards.


= 



-- tested with Window CLI, versions 3.8.5, 3.9.2, 3.14.1, 3.18.0

PRAGMA foreign_keys=ON;

-- bad case
CREATE TABLE masters_bad (id INTEGER PRIMARY KEY AUTOINCREMENT, 
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_bad (line INTEGER NOT NULL, 
quantity INTEGER NOT NULL, master INTEGER NOT NULL REFERENCES 
masters_bad(id), PRIMARY KEY(master, line)) WITHOUT ROWID;


BEGIN;
INSERT INTO masters_bad (reference) VALUES ('this is a ref');
INSERT INTO details_bad (master, line, quantity) VALUES 
(last_insert_rowid(), 1, 999);

COMMIT;

-- ok case 1 (fk in front of fields)
CREATE TABLE masters_ok1 (id INTEGER PRIMARY KEY AUTOINCREMENT, 
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok1 (master INTEGER NOT NULL 
REFERENCES masters_ok1(id), line INTEGER NOT NULL, quantity INTEGER 
NOT NULL, PRIMARY KEY(master,line)) WITHOUT ROWID;


BEGIN;
INSERT INTO masters_ok1 (reference) VALUES ('this is a ref');
INSERT INTO details_ok1 (master, line, quantity) VALUES 
(last_insert_rowid(), 1, 999);

COMMIT;

-- ok case 2 (no more quantity field)
CREATE TABLE masters_ok2 (id INTEGER PRIMARY KEY AUTOINCREMENT, 
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok2 (line INTEGER NOT NULL, master 
INTEGER NOT NULL REFERENCES masters_ok2(id), PRIMARY KEY(master, 
line)) WITHOUT ROWID;


BEGIN;
INSERT INTO masters_ok2 (reference) VALUES ('this is a ref');
INSERT INTO details_ok2 (master, line) VALUES (last_insert_rowid(), 1);
COMMIT;

-- ok case 3 fields order changed
CREATE TABLE masters_ok3 (id INTEGER PRIMARY KEY AUTOINCREMENT, 
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok3 (line INTEGER NOT NULL, master 
INTEGER NOT NULL REFERENCES masters_ok3(id), quantity INTEGER NOT 
NULL, PRIMARY KEY(master, line)) WITHOUT ROWID;


BEGIN;
INSERT INTO masters_ok3 (reference) VALUES ('this is a ref');
INSERT INTO details_ok3 (master, line, quantity) VALUES 
(last_insert_rowid(), 1, 999);

COMMIT;

-- checking
PRAGMA foreign_key_check;


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



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


[sqlite] potential bug

2017-04-17 Thread Bernard Schurdevin

Hi,

I get weird results (false positive) to PRAGMA foreign_key_check on 
WITHOUT ROWID table depending on foreign key field position.


Kind regards.


=

-- tested with Window CLI, versions 3.8.5, 3.9.2, 3.14.1, 3.18.0

PRAGMA foreign_keys=ON;

-- bad case
CREATE TABLE masters_bad (id INTEGER PRIMARY KEY AUTOINCREMENT, 
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_bad (line INTEGER NOT NULL, quantity 
INTEGER NOT NULL, master INTEGER NOT NULL REFERENCES masters_bad(id), 
PRIMARY KEY(master, line)) WITHOUT ROWID;


BEGIN;
INSERT INTO masters_bad (reference) VALUES ('this is a ref');
INSERT INTO details_bad (master, line, quantity) VALUES 
(last_insert_rowid(), 1, 999);

COMMIT;

-- ok case 1 (fk in front of fields)
CREATE TABLE masters_ok1 (id INTEGER PRIMARY KEY AUTOINCREMENT, 
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok1 (master INTEGER NOT NULL 
REFERENCES masters_ok1(id), line INTEGER NOT NULL, quantity INTEGER NOT 
NULL, PRIMARY KEY(master,line)) WITHOUT ROWID;


BEGIN;
INSERT INTO masters_ok1 (reference) VALUES ('this is a ref');
INSERT INTO details_ok1 (master, line, quantity) VALUES 
(last_insert_rowid(), 1, 999);

COMMIT;

-- ok case 2 (no more quantity field)
CREATE TABLE masters_ok2 (id INTEGER PRIMARY KEY AUTOINCREMENT, 
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok2 (line INTEGER NOT NULL, master 
INTEGER NOT NULL REFERENCES masters_ok2(id), PRIMARY KEY(master, line)) 
WITHOUT ROWID;


BEGIN;
INSERT INTO masters_ok2 (reference) VALUES ('this is a ref');
INSERT INTO details_ok2 (master, line) VALUES (last_insert_rowid(), 1);
COMMIT;

-- ok case 3 fields order changed
CREATE TABLE masters_ok3 (id INTEGER PRIMARY KEY AUTOINCREMENT, 
reference TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS details_ok3 (line INTEGER NOT NULL, master 
INTEGER NOT NULL REFERENCES masters_ok3(id), quantity INTEGER NOT NULL, 
PRIMARY KEY(master, line)) WITHOUT ROWID;


BEGIN;
INSERT INTO masters_ok3 (reference) VALUES ('this is a ref');
INSERT INTO details_ok3 (master, line, quantity) VALUES 
(last_insert_rowid(), 1, 999);

COMMIT;

-- checking
PRAGMA foreign_key_check;


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


Re: [sqlite] Potential bug: Database on gvfs mount cannot be committed to

2016-09-18 Thread Simon Slavin

On 19 Sep 2016, at 2:52am, Keith Medcalf  wrote:

> That is to say there is no difference between a block device (such as a 
> physical hard disk) attached to the computer via a 1 foot SCSI cable and an 
> iSCSI LUN where the iSCSI block device is located on a different plant, other 
> than the latency of the command/response.  In both cases you are using a 
> "Local" filesystem.
> 
> This is vastly different from mounting a "remote shared filesystem" from 
> another computer, whether that computer is located in the next rack slot or 
> located on another planet.

And it's going to get worse since the next generation is 'cloud storage' which 
looks like what you've called "remote shared filesystem" but has no fixed 
hardware.  It can be addressed using a network file system (SMB2 / CIFS / NFS) 
but the programmer will have no idea whether their data ends up on a rotating 
disk next door or spread over six SSDs on four continents.  So on top of the 
current problems it exhibits unpredictable timing problems.

We just have to pray that the network file system implements locking properly.  
Which SMB2 does allow, but I have no idea whether any drivers actually do it 
correctly.

Hmm.  Earlier this year Apple announced it's working a new file system, 
designed for the modern age with some pretty neat abilities.  It's suited to 
everything from Smart Cards to snapshottable virtual partitions in the cloud.  
Apple said it will release open source drivers and make the whole thing 
unencumbered by patents.  Now if it could just do the same thing with an API 
which implements locking correctly ...

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


Re: [sqlite] Potential bug: Database on gvfs mount cannot be committed to

2016-09-18 Thread Keith Medcalf

No database server product of which I am aware will "work" properly when the 
database resides on a remote filesystem.  There is a *vast* difference between 
a "remote file system" and a "local file system on a remote block device".

There is no difference between a "remote block device" known as a Hard Disk 
that is remotely attached via a SCSI/SATA/PATA/IDE/MDM cable on which the Local 
Computer creates a local filesystem for its own exclusive use, and a "remote 
block device" that is attached via iSCSI or Fiber Channel on which the Local 
Computer creates a local filesystem for its own exclusive use, except for the 
length of the cable connecting the "Local Computer" to the block storage device.

That is to say there is no difference between a block device (such as a 
physical hard disk) attached to the computer via a 1 foot SCSI cable and an 
iSCSI LUN where the iSCSI block device is located on a different plant, other 
than the latency of the command/response.  In both cases you are using a 
"Local" filesystem.

This is vastly different from mounting a "remote shared filesystem" from 
another computer, whether that computer is located in the next rack slot or 
located on another planet.

The confusion stems from the ill-advised usage of the inaccurate term "Network 
Attached Storage" by the ill-educated to refer to both "Remote Block Storage 
with a Local Filesystem" and "Remote Shared Filesystem", and the propensity of 
(some) of those hearing (or reading) the terms to make inappropriate 
assumptions about the meaning of the terms without understanding the 
consequences of those assumptions.

Practically every device connected to the Internet qualifies as "Network 
Attached Storage" and from that perspective, the Internet is just a great big 
"Storage Area Network".

Neither of those terms have anything to do with whether the filesystem is local 
or remote.  And the ability to create a "snapshot" does not require a Remote 
File System, nor does it require a Local File System.  In fact, it is 
completely independent of where the Filesystem is locatedhow it is attached, or 
the physical storage back-end.

> * R. Smith:
 
> > Enterprise DBs have servers on the same machine as the Files they
> > access, they do not actually use the network file-system to access the
> > DB data-files over the network from multiple clients, or even servers
> > (unless the DBs are partitioned so and ONLY accessed by the single
> > process so locking is permanent and moot).
 
> Deployments vary considerably.  A lot of enterprises use snapshotable
> storage for databases as well, just as an option in case upgrades need
> to be rolled back.




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


Re: [sqlite] Potential bug: Database on gvfs mount cannot be committed to

2016-09-18 Thread Stephen Chrzanowski
"snapshotable" or not, DBs are accessed from the local file system, not
from a network where another OS has control of the file system.

On Sun, Sep 18, 2016 at 10:16 AM, Florian Weimer  wrote:

> * R. Smith:
>
> > Enterprise DBs have servers on the same machine as the Files they
> > access, they do not actually use the network file-system to access the
> > DB data-files over the network from multiple clients, or even servers
> > (unless the DBs are partitioned so and ONLY accessed by the single
> > process so locking is permanent and moot).
>
> Deployments vary considerably.  A lot of enterprises use snapshotable
> storage for databases as well, just as an option in case upgrades need
> to be rolled back.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Potential bug: Database on gvfs mount cannot be committed to

2016-09-18 Thread Florian Weimer
* R. Smith:

> Enterprise DBs have servers on the same machine as the Files they
> access, they do not actually use the network file-system to access the
> DB data-files over the network from multiple clients, or even servers
> (unless the DBs are partitioned so and ONLY accessed by the single
> process so locking is permanent and moot).

Deployments vary considerably.  A lot of enterprises use snapshotable
storage for databases as well, just as an option in case upgrades need
to be rolled back.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Potential bug: Database on gvfs mount cannot be committed to

2015-09-12 Thread R.Smith


On 2015-09-12 06:30 PM, Florian Weimer wrote:
>> On 09/06/2015 11:13 AM, Florian Weimer wrote:
>>> Surely that's not true, and NFS and SMB are fine as long as there
>>> is no concurrent access?
>> And no program crashes, no network glitches, no optimisation in the
>> protocols to deal with latency, nothing else futzing with the files,
>> no programs futzing with them (backup agents, virus scanners etc), the
>> protocols are 100% complete compared to local file access, the
>> implementation of client and server for the protocol is 100% complete
>> and bug free, the operating systems don't treat network filesystems
>> sufficiently different to cause problems, you aren't using WAL, and
>> the list goes on.
>>
>> In other words it can superficially appear to work.  But one day
>> you'll eventually notice corruption, post to this list, and be told
>> not to use network filesystems.  The only variable is how long it
>> takes before you make that post.
> Sorry, this all sounds a bit BS to me.  Surely, as an fopen
> replacement, SQLite works with network file systems, be it the home
> NAS, or something in a typical datacenter.  And if the SQLite locking
> doesn't work in practice (which I doubt, remote file systems are
> better at locking than they used to be), should we really fall back
> on lock files with user overrides?  I hope not.

Your assumption and doubt is very wrong. Being able to open a file over 
the network is one thing, being able to lock it is another. Of course 
SQLite can request the lock (and does) like any other file access 
method, but the Network file system lies about the the actual locked 
state (for various reasons that requires more space than I aim to use here).

This is not an SQLite shortcoming, it is a network file-system shortcoming.

Your assertion that "remote file systems are better at locking than they 
used to be" may be superficially true, but that doesn't mean they are 
any more reliable. They simply lie all the time, because they have to in 
order to not make the connected World a very very slow place.


> (A lot of people run enterprise databases on NFS because that gives
> them snapshot-able storage and other goodies.  Not everyone uses iSCSI
> and block devices.)

That's like saying "A lot of people drive off-road vehicles in the bush, 
there is no reason why F1-cars should not also be driven in the bush."

Enterprise DBs have servers on the same machine as the Files they 
access, they do not actually use the network file-system to access the 
DB data-files over the network from multiple clients, or even servers 
(unless the DBs are partitioned so and ONLY accessed by the single 
process so locking is permanent and moot).

You cannot achieve a safe mechanism for opening, altering and locking 
files using fOpen over a network from other machines and multiple 
processes, but I invite you to try, maybe the exercise will be 
educational and remove some of the misconceptions.

You might even post your findings and code here after the fact. Lots of 
people do not realize the workings and will benefit from reading such an 
exercise.


Cheers,
Ryan




[sqlite] Potential bug: Database on gvfs mount cannot be committed to

2015-09-12 Thread Florian Weimer
* Roger Binns:

> On 09/06/2015 11:13 AM, Florian Weimer wrote:
>> Surely that's not true, and NFS and SMB are fine as long as there
>> is no concurrent access?
>
> And no program crashes, no network glitches, no optimisation in the
> protocols to deal with latency, nothing else futzing with the files,
> no programs futzing with them (backup agents, virus scanners etc), the
> protocols are 100% complete compared to local file access, the
> implementation of client and server for the protocol is 100% complete
> and bug free, the operating systems don't treat network filesystems
> sufficiently different to cause problems, you aren't using WAL, and
> the list goes on.
>
> In other words it can superficially appear to work.  But one day
> you'll eventually notice corruption, post to this list, and be told
> not to use network filesystems.  The only variable is how long it
> takes before you make that post.

Sorry, this all sounds a bit BS to me.  Surely, as an fopen
replacement, SQLite works with network file systems, be it the home
NAS, or something in a typical datacenter.  And if the SQLite locking
doesn't work in practice (which I doubt, remote file systems are
better at locking than they used to be), should we really fall back
on lock files with user overrides?  I hope not.

(A lot of people run enterprise databases on NFS because that gives
them snapshot-able storage and other goodies.  Not everyone uses iSCSI
and block devices.)


[sqlite] Potential bug: Database on gvfs mount cannot be committed to

2015-09-06 Thread Dan Kennedy
On 09/06/2015 09:23 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/06/2015 06:16 AM, Markus Weiland wrote:
>> I've discovered a potential bug in handling of SQLite database
>> files on gvfs mounted network shares.
> SQLite doesn't support being stored on the network for several
> reasons, including that network file protocols don't implement fully
> and correctly various access and locking, and that some journaling
> modes like WAL require shared memory and hence can't be networked.
> This is covered in the FAQ:
>
>https://www.sqlite.org/faq.html#q5
>
> Or if you want to be pedantic, it is gvfs and networked filesystems
> that have the bug.

The other problem is that it's 2.8.17. I think the most recent bugfix on 
the 2.* line was in 2007. 2.8.17 was the last release, and that was in 
December 2005.

Dan.



[sqlite] Potential bug: Database on gvfs mount cannot be committed to

2015-09-06 Thread Simon Slavin

On 6 Sep 2015, at 9:16pm, Roger Binns  wrote:

> no programs futzing with them (backup agents, virus scanners etc)

Reminds me of my most annoying SQLite problem.  They were running a virus 
scanner which delayed temp file deletion and SQLite could not manage its 
journal files properly.  Took me a few hours to figure that out.

Simon.


[sqlite] Potential bug: Database on gvfs mount cannot be committed to

2015-09-06 Thread Florian Weimer
* Roger Binns:

> On 09/06/2015 06:16 AM, Markus Weiland wrote:
>> I've discovered a potential bug in handling of SQLite database
>> files on gvfs mounted network shares.
>
> SQLite doesn't support being stored on the network for several
> reasons, including that network file protocols don't implement fully
> and correctly various access and locking, and that some journaling
> modes like WAL require shared memory and hence can't be networked.
> This is covered in the FAQ:
>
>   https://www.sqlite.org/faq.html#q5
>
> Or if you want to be pedantic, it is gvfs and networked filesystems
> that have the bug.

Surely that's not true, and NFS and SMB are fine as long as there is
no concurrent access?


[sqlite] Potential bug: Database on gvfs mount cannot be committed to

2015-09-06 Thread Markus Weiland
I see. Since this was working under Ubuntu 14.04, I assume this is a 
regression with gvfs. I'll check over there.


On 2015-09-06 06:00 PM, sqlite-users-request at mailinglists.sqlite.org wrote:
> On 09/06/2015 06:16 AM, Markus Weiland wrote:
>> >I've discovered a potential bug in handling of SQLite database
>> >files on gvfs mounted network shares.
> SQLite doesn't support being stored on the network for several
> reasons, including that network file protocols don't implement fully
> and correctly various access and locking, and that some journaling
> modes like WAL require shared memory and hence can't be networked.
> This is covered in the FAQ:
>
>https://www.sqlite.org/faq.html#q5
>
> Or if you want to be pedantic, it is gvfs and networked filesystems
> that have the bug.
>
> Roger



[sqlite] Potential bug: Database on gvfs mount cannot be committed to

2015-09-06 Thread Markus Weiland
Hi,

I've discovered a potential bug in handling of SQLite database files on 
gvfs mounted network shares.

Steps to reproduce:
1. Under vanilla Ubuntu 15.04 with latest official patches and SQLite 
version 2.8.17, mount a Windows / SMB network share via Nautilus file 
manager. The share can be via SMBv2 or v3.
2. Assert that the share is accessible on console via 
`/var/run/user/USERID/gvfs/smb-share:server=SERVERADDRESS,share=SHARENAME/`
3. In a terminal `cd` to the share
4. `touch test.txt` to verify the share is writeable
5. Open a new SQLite database in the same folder with `sqlite test.sqlite`
6. Execute `create table tbl1(one varchar(10), two smallint);` to create 
a table
7. Insert data with `insert into tbl1 values('hello!',10);`

Actual result:
* Inserting data fails with `SQL error: database disk image is malformed`

Expected result:
* Data is inserted and correctly persisted to disk

Notes:
* After the above steps, the database file exists and is about 3kb large.
* I originally noticed the issue with Calibre, which is unable to 
maintain its SQLite ebook database file on a network share because of 
this issue.

I'd appreciate if someone on the list could verify the above findings 
and open an official bug report as needed.

Thank you.





[sqlite] Potential bug: Database on gvfs mount cannot be committed to

2015-09-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/06/2015 11:13 AM, Florian Weimer wrote:
> Surely that's not true, and NFS and SMB are fine as long as there
> is no concurrent access?

And no program crashes, no network glitches, no optimisation in the
protocols to deal with latency, nothing else futzing with the files,
no programs futzing with them (backup agents, virus scanners etc), the
protocols are 100% complete compared to local file access, the
implementation of client and server for the protocol is 100% complete
and bug free, the operating systems don't treat network filesystems
sufficiently different to cause problems, you aren't using WAL, and
the list goes on.

In other words it can superficially appear to work.  But one day
you'll eventually notice corruption, post to this list, and be told
not to use network filesystems.  The only variable is how long it
takes before you make that post.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlXsnwUACgkQmOOfHg372QQczQCffHo7JLJtQw4EcJQAVemsTPEN
/CUAoKpoIz3RudoRWM5qc2ac98dTVa18
=3Yx3
-END PGP SIGNATURE-


[sqlite] Potential bug: Database on gvfs mount cannot be committed to

2015-09-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/06/2015 10:20 AM, Markus Weiland wrote:
> I see. Since this was working under Ubuntu 14.04, I assume this is
> a regression with gvfs. I'll check over there.

Nope.  SQLite can not maintain data integrity when used with *any*
network filesystem.  Sometimes it is able to detect problems, and give
an error message.  And other times you will end up with database
corruption.  Then you'll post to this list, and we'll tell you not to
use network filesystems.  The only variable is how long it takes
before you eventually find out about the corruption.

Sometimes people also come up with various schemes that appear to
work, but they won't always. Even something as simple as opening the
database for reading only can be very complex - such as if the
database wasn't cleanly committed (eg program crashed) - because
journals have to be examined and played back/undone as appropriate.
That involves two or more files, various forms of locking etc - just
the things that are problems with networked filesystems.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlXsncIACgkQmOOfHg372QTweACeORy+Jpo6V4LDY3NXvU0iZ7G4
nW4AnA1ugTztFbRyQOHzhMJsQ8b54F7T
=uuQw
-END PGP SIGNATURE-


[sqlite] Potential bug: Database on gvfs mount cannot be committed to

2015-09-06 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/06/2015 06:16 AM, Markus Weiland wrote:
> I've discovered a potential bug in handling of SQLite database
> files on gvfs mounted network shares.

SQLite doesn't support being stored on the network for several
reasons, including that network file protocols don't implement fully
and correctly various access and locking, and that some journaling
modes like WAL require shared memory and hence can't be networked.
This is covered in the FAQ:

  https://www.sqlite.org/faq.html#q5

Or if you want to be pedantic, it is gvfs and networked filesystems
that have the bug.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlXsTFcACgkQmOOfHg372QQyeQCeJW2PjkZmQQ5jGjAhkI464TTg
zEAAn3mG2H9VjACQHRN8lxQ70itB4FcD
=tl3B
-END PGP SIGNATURE-


Re: [sqlite] SQLite - potential bug with multiple leftjoin/groupby/count

2013-09-04 Thread Clemens Ladisch
Harry Beezhold wrote:
> Sqlite  -  What a cool product!

Do you really think buggy products are cool?  ;-)

> The following is a description of an apparent bug in
> the calculation of a row count of a left joined table.

> The leftjoin/count technique seems to work for each join/count, separately.
> However, if done together, the counts appear to have been multiplied

I created a simplified example:

  CREATE TABLE persons(personID);
  INSERT INTO "persons" VALUES('I');
  CREATE TABLE parentschildren(parentID, childID);
  INSERT INTO "parentschildren" VALUES('father','I');
  INSERT INTO "parentschildren" VALUES('mother','I');
  INSERT INTO "parentschildren" VALUES('I','son');
  INSERT INTO "parentschildren" VALUES('I','daughter');

A single join works as expected:

  SELECT personID, parents.parentID
  FROM persons
  LEFT JOIN parentschildren AS parents ON personID = parents.childID;

  personIDparentID
  --  --
  I   father
  I   mother

If you join that with another table, you get more records because _each_
"I" record matches two children records:

  SELECT personID, parents.parentID, children.childID
  FROM persons
  LEFT JOIN parentschildren AS parents ON personID = parents.childID
  LEFT JOIN parentschildren AS children ON personID = children.parentID;

  personIDparentIDchildID
  --  --  --
  I   father  daughter
  I   father  son
  I   mother  daughter
  I   mother  son

This is how SQL joins work.

If you want to get _independent_ parent and children counts, you should
use correlated subqueries instead:

  SELECT personID,
 (SELECT COUNT(*) FROM parentschildren WHERE childID  = personID) AS 
parentCount,
 (SELECT COUNT(*) FROM parentschildren WHERE parentID = personID) AS 
childCount
  FROM persons;

  personIDparentCount  childCount
  --  ---  --
  I   22


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] SQLite - potential bug with multiple leftjoin/groupby/count

2013-09-04 Thread Richard Hipp
On Tue, Sep 3, 2013 at 10:41 AM, Harry Beezhold  wrote:

>
>
> The attached database (view.db) has 3 tables
>

The sqlite-users@sqlite.org mailing list strips off attachments.  Can you
send a link instead?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] SQLite - potential bug with multiple leftjoin/groupby/count

2013-09-04 Thread Harry Beezhold
Hi,

 

Sqlite  -  What a cool product!

 

The following is a description of an apparent bug in 

the calculation of a row count of a left joined table.

 

In the intended application I plan to use this type of query to feed and
filter the 

list on the "choose a person" popup in a genealogy program.  I have a
workaround

so I AM NOT IN A HURRY FOR A RESPONSE.

 

 

The attached database (view.db) has 3 tables

1,  persons (personID)

2.  parentsChildren (parentID, childID)

3.  spouses (spouse1ID, spouseID)

 

The leftjoin/count technique seems to work for each join/count, separately.

However, if done together, the counts appear to have been multiplied;

Example:  person #4 should have 2 parents, 10 children and 2 spouses.

Instead it gets 20,20 for 2 joins and 40,40,40 for 3 joins.

 

I hope the following queries will tell the story.

I can be contacted at:

harry beezhold

ha...@beezhold.com

 

708-459-8600

 

 

 

 

 

 

**   JOIN parents 

**   looks OK 

**   #4 = 2   

 

SELECT persons.personID,

 count(parents.parentID) as parentCount

FROM  persons 

LEFT JOIN parentsChildren AS parents  ON personID = parents.childID

GROUP BY personID

 

10

22

30

42 These numbers look good.

50 The query asks:

61 How many in the parentsChildren table has my personID in their
childID.

71 They would be my parents

82

92

10   0

11   0

12   2

13   2

14   0

15   0

I stopped it

 

 

**   JOIN children

**   looks OK 

**   #4 = 10  

 

SELECT persons.personID,

 count(children.childID) as childCount

FROM  persons 

LEFT JOIN parentsChildren AS children  ON personID = children.parentID

GROUP BY personID

 

11

21

31

410   Yes, he did have 10 children

52

60

70

82

92

10   1

11   1

12   0

13   0

14   1

15   1

 

*   JOIN spouses

**   looks OK 

**   #4 = 2  

 

SELECT persons.personID,

 count(spouses.spouse1ID) as spouseCount

FROM  persons 

LEFT JOIN spouses ON ((personID = spouse1ID) OR (personID = spouse2ID))

GROUP BY personID

 

11

21

31

42he had 2 wives

50

60

70

81

91

10   1

11   1

12   1

13   1

14   1

15   1

 

**   JOIN 2 from same table   

**   the problem shows


**   *
***

 

SELECT persons.personID,

 count(parents.parentID) as parentCount,

 count(children.childID) as childCount

FROM  persons 

LEFT JOIN parentsChildren AS parents  ON personID = parents.childID

LEFT JOIN parentsChildren AS children  ON personID = children.parentID

GROUP BY personID

 

 

101

222   < bug

301

420   20  < bug   

502

610

710

844   < bug

944   < bug

10   01

11   01

12   20

13   20

14   01

15   01

16   20

17   20

18   010

19   20   20  < bug

20   20

 

**   JOIN 2 diff tables/seq   

**   the problem shows


**   *
***

 

SELECT persons.personID,

 count(spouses.spouse1ID) as spouceCount,

 count(children.childID) as childCount

FROM  persons 

LEFT JOIN spouses ON ((personID = spouse1ID) OR (personID = spouse2ID))

LEFT JOIN parentsChildren AS children  ON personID = children.parentID

GROUP BY personID

 

101

222   < bug

301

420   20  < bug

502

610

710

844  < bug

944  < bug

10   01

11   01

12   20

13   20

14   01

15   01

16   20

17   20

18   010

19   20   20  < bug

20   20

 

**   3   JOIN 

**   the problem shows


**   *
***

 

SELECT persons.personID,

 count(parents.parentID) as parentCount,

 count(children.childID) as childCount,

 count(spouses.spouse1ID) as spouseCount

FROM  (((persons 

LEFT JOIN parentsChildren AS parents  ON personID 

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-23 Thread Thanumalayan Sankaranarayana Pillai
Hi Marc,

Thanks for your comments! I just got confused that some SQLite webpages (
http://www.sqlite.org/transactional.html,
http://www.sqlite.org/features.html) mention that transactions are durable
after a power loss (the D in ACID); nowhere has it been mentioned that
"immediate durability after a commit" is true only when the journal_mode is
explicitly set to WAL. The documentation, however, was thorough regarding
stuff like bad disks and bad OSes, and how they affect the
integrity/corruption of the database.

My view had been fully biased: I was looking at some applications that use
MySQL and such, acting as nodes in a network, and they require this
"immediate durability" guarantee. I totally understand now that SQLite is
aimed at an entirely different set of applications that don't require
"immediate durability", but would rather that the database is fast.

Thanks again,
Thanu


On Thu, May 23, 2013 at 8:00 AM, Marc L. Allen
<mlal...@outsitenetworks.com>wrote:

> Just to throw in my $0.02 as a user
>
> Given the SQL stream of...
>
> 
> COMMIT
> 
>
> Vs.
>
> 
> 
> 
>
> Except in cases where, in the first example, I have time to inform someone
> about the COMMIT before the power loss, there's no functional difference
> between the two events.  I would hate to think I would ever demand that
> SQLite guarantee the commit occurs regardless of how quickly the power loss
> happens after the commit statement.
>
> For a huge majority of the applications I've dealt with (and I say huge
> instead of all only in case there's one I've forgotten about), the timing
> difference doesn't matter.  What matters is that when I come back up I have
> an intact database.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of thanumalayan mad
> Sent: Wednesday, May 22, 2013 8:31 AM
> To: Richard Hipp
> Cc: General Discussion of SQLite Database
> Subject: Re: [sqlite] Potential bug in crash-recovery code: unlink() and
> friends are not synchronous
>
> I do not observe any loss in durability in WAL mode: it works totally fine.
>
> As for the documentation, http://www.sqlite.org/transactional.html and
> http://www.sqlite.org/features.html claim that SQLite is durable during
> power failures; and DELETE is the default journal_mode. Also, other pages,
> http://www.sqlite.org/pragma.html#pragma_synchronous,
> http://www.sqlite.org/atomiccommit.html, and
> http://www.sqlite.org/faq.html, made me think that rollback journaling
> ensures durability (given a honest disk and a "honest" OS), although those
> pages do not "legally" say that; I'm not sure if others would understand it
> this way though. The usual opinion in blogosphere (and forums) also seems
> to be that SQLite is, by default, durable across power failures, though
> they might be meaning only about a "5 second eventual durability". Finally,
> (I hope I do not come across here as being authoritative), a quick fix
> might be explicitly mentioning somewhere in the documentation that DELETE
> and TRUNCATE modes do not ensure immediate durability after a power loss;
> this would combat any wrongly-understood claims in the rest of the
> documentation.
>
> Also, not to spam, but it would be great if you could answer these
> questions for my research (you might send me a reply directly without going
> through the mailing list): [a] Was it always understood that unlink() and
> ftruncate() are not synchronous, and that SQLite transactions in DELETE
> mode are not immediately-durable in Linux; or had you initially
> misunderstood the semantics of those calls, or left-off the fsync() because
> of a typo error? [b] While designing the crash-tests, were the semantics of
> the calls in Unix understood? What if ftruncate() not being synchronous did
> lead to a consistency-loss? Was it reasoned-out that the non-synchronous
> ftruncate would not produce corruption? [c] How much of a loss in
> durability (what other than 5 seconds) would be "good enough" in most
> cases? ... Again, sorry for the spam; my research is trying to make
> sense of the flushing-mess in the entire storage stack, and feedback would
> be extremely useful.
>
> PS: @Richard Hipp and other developers - thanks for SQLite! Amazing piece
> of software.
>
> --
> Thanumalayan Sankaranarayana Pillai
> (Graduate student at the University of Wisconsin-Madison)
>
>
> On Wed, May 22, 2013 at 5:49 AM, Richard Hipp <d...@sqlite.org> wrote:
>
> >
> >
> > On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad <madth...@gmail.com
> >wrote:
> >
> >>
> >> Expected result: You always find that the t

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-23 Thread Marc L. Allen
Just to throw in my $0.02 as a user

Given the SQL stream of...


COMMIT


Vs.





Except in cases where, in the first example, I have time to inform someone 
about the COMMIT before the power loss, there's no functional difference 
between the two events.  I would hate to think I would ever demand that SQLite 
guarantee the commit occurs regardless of how quickly the power loss happens 
after the commit statement.

For a huge majority of the applications I've dealt with (and I say huge instead 
of all only in case there's one I've forgotten about), the timing difference 
doesn't matter.  What matters is that when I come back up I have an intact 
database.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of thanumalayan mad
Sent: Wednesday, May 22, 2013 8:31 AM
To: Richard Hipp
Cc: General Discussion of SQLite Database
Subject: Re: [sqlite] Potential bug in crash-recovery code: unlink() and 
friends are not synchronous

I do not observe any loss in durability in WAL mode: it works totally fine.

As for the documentation, http://www.sqlite.org/transactional.html and 
http://www.sqlite.org/features.html claim that SQLite is durable during power 
failures; and DELETE is the default journal_mode. Also, other pages, 
http://www.sqlite.org/pragma.html#pragma_synchronous,
http://www.sqlite.org/atomiccommit.html, and http://www.sqlite.org/faq.html, 
made me think that rollback journaling ensures durability (given a honest disk 
and a "honest" OS), although those pages do not "legally" say that; I'm not 
sure if others would understand it this way though. The usual opinion in 
blogosphere (and forums) also seems to be that SQLite is, by default, durable 
across power failures, though they might be meaning only about a "5 second 
eventual durability". Finally, (I hope I do not come across here as being 
authoritative), a quick fix might be explicitly mentioning somewhere in the 
documentation that DELETE and TRUNCATE modes do not ensure immediate durability 
after a power loss; this would combat any wrongly-understood claims in the rest 
of the documentation.

Also, not to spam, but it would be great if you could answer these questions 
for my research (you might send me a reply directly without going through the 
mailing list): [a] Was it always understood that unlink() and
ftruncate() are not synchronous, and that SQLite transactions in DELETE mode 
are not immediately-durable in Linux; or had you initially misunderstood the 
semantics of those calls, or left-off the fsync() because of a typo error? [b] 
While designing the crash-tests, were the semantics of the calls in Unix 
understood? What if ftruncate() not being synchronous did lead to a 
consistency-loss? Was it reasoned-out that the non-synchronous ftruncate would 
not produce corruption? [c] How much of a loss in durability (what other than 5 
seconds) would be "good enough" in most cases? ... Again, sorry for the 
spam; my research is trying to make sense of the flushing-mess in the entire 
storage stack, and feedback would be extremely useful.

PS: @Richard Hipp and other developers - thanks for SQLite! Amazing piece of 
software.

--
Thanumalayan Sankaranarayana Pillai
(Graduate student at the University of Wisconsin-Madison)


On Wed, May 22, 2013 at 5:49 AM, Richard Hipp <d...@sqlite.org> wrote:

>
>
> On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad <madth...@gmail.com>wrote:
>
>>
>> Expected result: You always find that the transaction had been executed.
>> Observed result: You sometimes find that the transaction did not execute.
>>
>
> The core team has discussed this.  In order to avoid a substantial 
> performance hit against transaction COMMIT, we have chosen to not do 
> fsyncs on the directory when a file is unlinked, and thus to allow 
> loss of durability following a power loss event.  ACI without the D is 
> still guaranteed.  But not the D.  The overwhelming majority of 
> applications care not one wit about durability following power loss.  
> For most applications, it is sufficient that the file is uncorrupted.  
> If recovery gives you a snapshot of the file as it existed 5 seconds 
> prior to the power loss, that's fine.
>
> WAL-mode transactions should be durable across power-loss events.  So 
> if durability is vitally important to you, you can always set PRAGMA 
> journal_mode=WAL.  Are you observing loss of durability following 
> power loss in WAL mode?
>
> Is there any place in the documentation that we have overlooked where 
> SQLite claims to be durable across a power loss in rollback mode?
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqli

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-23 Thread thanumalayan mad
I do not observe any loss in durability in WAL mode: it works totally fine.

As for the documentation, http://www.sqlite.org/transactional.html and
http://www.sqlite.org/features.html claim that SQLite is durable during
power failures; and DELETE is the default journal_mode. Also, other pages,
http://www.sqlite.org/pragma.html#pragma_synchronous,
http://www.sqlite.org/atomiccommit.html, and
http://www.sqlite.org/faq.html, made
me think that rollback journaling ensures durability (given a honest disk
and a "honest" OS), although those pages do not "legally" say that; I'm not
sure if others would understand it this way though. The usual opinion in
blogosphere (and forums) also seems to be that SQLite is, by default,
durable across power failures, though they might be meaning only about a "5
second eventual durability". Finally, (I hope I do not come across here as
being authoritative), a quick fix might be explicitly mentioning somewhere
in the documentation that DELETE and TRUNCATE modes do not ensure immediate
durability after a power loss; this would combat any wrongly-understood
claims in the rest of the documentation.

Also, not to spam, but it would be great if you could answer these
questions for my research (you might send me a reply directly without going
through the mailing list): [a] Was it always understood that unlink() and
ftruncate() are not synchronous, and that SQLite transactions in DELETE
mode are not immediately-durable in Linux; or had you initially
misunderstood the semantics of those calls, or left-off the fsync() because
of a typo error? [b] While designing the crash-tests, were the semantics of
the calls in Unix understood? What if ftruncate() not being synchronous did
lead to a consistency-loss? Was it reasoned-out that the non-synchronous
ftruncate would not produce corruption? [c] How much of a loss in
durability (what other than 5 seconds) would be "good enough" in most
cases? ... Again, sorry for the spam; my research is trying to make
sense of the flushing-mess in the entire storage stack, and feedback would
be extremely useful.

PS: @Richard Hipp and other developers - thanks for SQLite! Amazing piece
of software.

--
Thanumalayan Sankaranarayana Pillai
(Graduate student at the University of Wisconsin-Madison)


On Wed, May 22, 2013 at 5:49 AM, Richard Hipp  wrote:

>
>
> On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad wrote:
>
>>
>> Expected result: You always find that the transaction had been executed.
>> Observed result: You sometimes find that the transaction did not execute.
>>
>
> The core team has discussed this.  In order to avoid a substantial
> performance hit against transaction COMMIT, we have chosen to not do fsyncs
> on the directory when a file is unlinked, and thus to allow loss of
> durability following a power loss event.  ACI without the D is still
> guaranteed.  But not the D.  The overwhelming majority of applications care
> not one wit about durability following power loss.  For most applications,
> it is sufficient that the file is uncorrupted.  If recovery gives you a
> snapshot of the file as it existed 5 seconds prior to the power loss,
> that's fine.
>
> WAL-mode transactions should be durable across power-loss events.  So if
> durability is vitally important to you, you can always set PRAGMA
> journal_mode=WAL.  Are you observing loss of durability following power
> loss in WAL mode?
>
> Is there any place in the documentation that we have overlooked where
> SQLite claims to be durable across a power loss in rollback mode?
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-22 Thread Klaas V
Dear fellow SQLite afficionados,

  Thanumalayan Sankaranarayana Pillai  wrote:
  "I expect it wouldn't be a problem with WAL"
Thé SQLite (not wanting, but cobsidering him at leat  kind of) Force D. Richard 
H.  [who does not know Him don't read this message, you won;t understand enough 
I'm afraid]  answered to   
  There are no configuration options set wrong.  You shouldn't ever have to
set configuration options in order to get SQLite to work right.  I think
instead you are just running up against fundamental limits of modern
consumer-grade hardware.  I don't think there is anything anybody can do
about it" I'd say as a fellow consumer of a wee little bit different hardware 
as well as software
"asking has some kind of answering included sometimes"  I admit since it's not 
my native lingo in imperfect English, I'm brave and arrogant enough to 
encourage mr. Pillai to download and move to a directory within her or his PATH 
the brand new/recommended RVU(P) 3.17.0(±130520) ASAP (i.e. As Soon As Possible 
[just in the very unlikely case y'all did not know yet this geek speak]
To make a long story shorter: Use WAL to open not only yer eyes, but possibly 
you guys's (and gal's) mind as well. Hopefully it's not (yet) banned in your 
part of this wondrtful globe.
 
Just my not extremely humble opinion. Thanks a million for paying eventual 
attention, friends (and 'why not' foes),
 

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas "Z4us" V MetaDBA
kla...@innocentisart.eu

P.S.In bocca al lupo, tutti!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-22 Thread Thanumalayan Sankaranarayana Pillai
No, I have reported everything. The only thing I missed might be that it's
not "5 seconds" always, but rather the configurable commit interval of the
filesystem, which is by default 5 seconds in most desktop Linux distros.

I only read through the source code of test6.c, and misunderstood that
ftruncate() was assumed to be synchronous. Sorry. I still think that
test6.c might assume unlink() to be synchronous, though again, that is just
a "think".

Thanks for the replying to my questions. They are really useful. I am
extremely surprised to learn that unlink() cannot be made durable in some
systems. Do answer [c] too, if that's possible.  I still am examining
SQLite and a bunch of other DBMSs for power-crash scenarios, so might come
back to badger you about "bugs" in the future.

--
Thanumalayan Sankaranarayana Pillai
(Graduate student at the University of Wisconsin-Madison)


On Wed, May 22, 2013 at 8:05 AM, Richard Hipp  wrote:

>
>
> On Wed, May 22, 2013 at 8:31 AM, thanumalayan mad wrote:
>
>>
>> Also, not to spam, but it would be great if you could answer these
>> questions for my research (you might send me a reply directly without going
>> through the mailing list): [a] Was it always understood that unlink() and
>> ftruncate() are not synchronous, and that SQLite transactions in DELETE
>> mode are not immediately-durable in Linux; or had you initially
>> misunderstood the semantics of those calls, or left-off the fsync() because
>> of a typo error?
>>
>
> We are aware of the need to fsync() the directory that contains a file
> after unlink() in order to make the unlink() durable.  We have deliberately
> chosen to avoid that fsync() for performance reasons.
>
> Note that on some systems (ex: AIX and the Chromium sandbox) it is not
> possible to fsync() a directory and therefore it is not possible to make
> unlink() durable.
>
>
>
>
>> [b] While designing the crash-tests, were the semantics of the calls in
>> Unix understood? What if ftruncate() not being synchronous did lead to a
>> consistency-loss? Was it reasoned-out that the non-synchronous ftruncate
>> would not produce corruption?
>>
>
> Over the past 12 years of developing SQLite, we have become acutely aware
> of the semantics of unix, both as published and as commonly implemented,
> which are not necessarily the same thing.
>
> Crash-tests are looking for corruption problems only.  Crash testing does
> not look for durability.
>
> I do not believe that our crash-testing assumes that ftruncate() is
> synchronous.  Have you detected an error that you have not reported?
>
>
>
>>  [c] How much of a loss in durability (what other than 5 seconds) would
>> be "good enough" in most cases?
>>
>
>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-22 Thread Richard Hipp
On Wed, May 22, 2013 at 8:31 AM, thanumalayan mad wrote:

>
> Also, not to spam, but it would be great if you could answer these
> questions for my research (you might send me a reply directly without going
> through the mailing list): [a] Was it always understood that unlink() and
> ftruncate() are not synchronous, and that SQLite transactions in DELETE
> mode are not immediately-durable in Linux; or had you initially
> misunderstood the semantics of those calls, or left-off the fsync() because
> of a typo error?
>

We are aware of the need to fsync() the directory that contains a file
after unlink() in order to make the unlink() durable.  We have deliberately
chosen to avoid that fsync() for performance reasons.

Note that on some systems (ex: AIX and the Chromium sandbox) it is not
possible to fsync() a directory and therefore it is not possible to make
unlink() durable.




> [b] While designing the crash-tests, were the semantics of the calls in
> Unix understood? What if ftruncate() not being synchronous did lead to a
> consistency-loss? Was it reasoned-out that the non-synchronous ftruncate
> would not produce corruption?
>

Over the past 12 years of developing SQLite, we have become acutely aware
of the semantics of unix, both as published and as commonly implemented,
which are not necessarily the same thing.

Crash-tests are looking for corruption problems only.  Crash testing does
not look for durability.

I do not believe that our crash-testing assumes that ftruncate() is
synchronous.  Have you detected an error that you have not reported?



>  [c] How much of a loss in durability (what other than 5 seconds) would be
> "good enough" in most cases?
>





-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-22 Thread Richard Hipp
On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad wrote:

>
> Expected result: You always find that the transaction had been executed.
> Observed result: You sometimes find that the transaction did not execute.
>

The core team has discussed this.  In order to avoid a substantial
performance hit against transaction COMMIT, we have chosen to not do fsyncs
on the directory when a file is unlinked, and thus to allow loss of
durability following a power loss event.  ACI without the D is still
guaranteed.  But not the D.  The overwhelming majority of applications care
not one wit about durability following power loss.  For most applications,
it is sufficient that the file is uncorrupted.  If recovery gives you a
snapshot of the file as it existed 5 seconds prior to the power loss,
that's fine.

WAL-mode transactions should be durable across power-loss events.  So if
durability is vitally important to you, you can always set PRAGMA
journal_mode=WAL.  Are you observing loss of durability following power
loss in WAL mode?

Is there any place in the documentation that we have overlooked where
SQLite claims to be durable across a power loss in rollback mode?


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-22 Thread Thanumalayan Sankaranarayana Pillai
Thank you for your replies! I now fully understand (and appreciate) that
the "ACI" part of transactions is the most important. Also, I didn't notice
any of ACI being broken: SQLite guarantees those conditions really well.

However, just to be clear, my "potential bug" affects out-of-the-box Fedora
and Ubuntu, even with costly SCSI drives, and with the OS installed using
all default options. On the other hand, in these environments, SQLite
transactions will be immediately durable if fsync()-s are issued after the
ftruncate() or unlink() calls while resetting the rollback journal; I
verified this by modifying unixDelete() within SQLite, setting dirSync = 1
always. I'm writing the rest of this email assuming that this is an
interesting piece of news (and not something already known). Also,
confession: I am a grad student researching filesystems, and I haven’t
directly used SQLite much; I looked at SQLite because I observed some funny
disk activity. So if I am totally out-of-place with this rigmarole,
imagining things about SQLite that aren’t actually true, please forgive the
spam.

It is entirely true that desktop SATA drives with disk-caches, some bad
filesystems, and crazy OS configurations, all disrespect the entire concept
of flushing. But, the setup I verified used either Fedora 17 and Ubuntu
12.04, and either Ext4, Ext3, or Btrfs. I did my best to make sure that the
effects are neither due to a lying disk, nor because of configuration stuff
like disabling filesystem barriers. Moreover, *the last SQLite
transaction's durability is almost always delayed by 5 seconds* (it's not
something you observe only once in a thousand power crashes), if there are
no other applications flushing to the disk. If other applications do flush
to the disk, then the delay is only till when the next flush (of another
application) happens. The bug probably does not affect Windows and other
OSes - it deals with the "Unix" VFS exclusively.

Not calling fsync() might be based on the assumption that they are
synchronous. As far as I can tell, both the ftruncate() and unlink() system
calls are not synchronous (not immediately **issued to the disk**) in the
Linux distros I used, independent of the disk. I understand that such
semantics (of whether the system calls are synchronous) are often
non-standard; at other times, the documentation is not clear about what the
standards are. However, some manpages (http://linux.die.net/man/2/fsync for
ftruncate, and http://linux.die.net/man/8/mount for unlink: look for
"dirsync") suggest that most Linux distributions require an fsync() for
unlink and ftruncate to be synchronous. Some filesystems or distros might,
however, automatically flush, though: I haven't yet looked into the
behavior of other distributions such as RHEL, other Unix-like OSes such as
FreeBSD, or file systems such as XFS. But, I'll be looking into them soon,
as part of my research, so do let me know if you are interested in their
behavior.

Thanks for reading all the way through! Again, it might be a better
decision to not issue the fsync()-s after unlink and ftruncate: "D" is
probably less important than the extra overhead. I'm just trying to make
sure this is by design, and not an accident.

--
Thanu


On Tue, May 21, 2013 at 11:52 AM, Richard Hipp  wrote:
>
> On Tue, May 21, 2013 at 12:04 PM, Thanumalayan Sankaranarayana Pillai <
> madth...@cs.wisc.edu> wrote:
>
> > Hi all,
> >
> > Did anyone look into this? I might be setting some config option wrong,
> > so it would be great if you sent me a "you did something wrong" reply if
> > you feel that I might have the wrong config (or might be doing something
> > totally idiotic).
> >
>
> There are no configuration options set wrong.  You shouldn't ever have to
> set configuration options in order to get SQLite to work right.   I think
> instead you are just running up against fundamental limits of modern
> consumer-grade hardware.  I don't think there is anything anybody can do
> about it.
>
>
> >
> > I tested with a few other Linux machines and a few different SQLite
> > versions, and found that there is an upto-5-second delay (the default
> > filesystem commit interval) in the transactions getting durable (with
power
> > crashes, that is).
> >
> > Thanks in advance,
> > Thanu
> >
> > On Sat, May 18, 2013 at 3:41 AM, thanumalayan mad  > >wrote:
> >
> > > Hi All,
> > >
> > > I was testing out SQLite with a framework I developed. I believe,
while
> > > running on Linux, transactions might not be durable when a power crash
> > > happens immediately after a commit. I observed this using "SQLite
version
> > > 3.7.16.2 2013-04-12 11:52:43", and kernel "3.8.4-102.fc17.x86_64".
Steps
> > to
> > > reproduce:
> > >
> > > 1. Use a Linux machine with an Ext4 filesystem (default mount
options).
> > > 2. Create a database file, set journal_mode to DELETE, perform a
> > > transaction using "begin transaction ... commit;".
> > > 3. Pull the power plug 

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-21 Thread Richard Hipp
On Tue, May 21, 2013 at 12:04 PM, Thanumalayan Sankaranarayana Pillai <
madth...@cs.wisc.edu> wrote:

> Hi all,
>
> Did anyone look into this? I might be setting some config option wrong,
> so it would be great if you sent me a "you did something wrong" reply if
> you feel that I might have the wrong config (or might be doing something
> totally idiotic).
>

There are no configuration options set wrong.  You shouldn't ever have to
set configuration options in order to get SQLite to work right.   I think
instead you are just running up against fundamental limits of modern
consumer-grade hardware.  I don't think there is anything anybody can do
about it.


>
> I tested with a few other Linux machines and a few different SQLite
> versions, and found that there is an upto-5-second delay (the default
> filesystem commit interval) in the transactions getting durable (with power
> crashes, that is).
>
> Thanks in advance,
> Thanu
>
> On Sat, May 18, 2013 at 3:41 AM, thanumalayan mad  >wrote:
>
> > Hi All,
> >
> > I was testing out SQLite with a framework I developed. I believe, while
> > running on Linux, transactions might not be durable when a power crash
> > happens immediately after a commit. I observed this using "SQLite version
> > 3.7.16.2 2013-04-12 11:52:43", and kernel "3.8.4-102.fc17.x86_64". Steps
> to
> > reproduce:
> >
> > 1. Use a Linux machine with an Ext4 filesystem (default mount options).
> > 2. Create a database file, set journal_mode to DELETE, perform a
> > transaction using "begin transaction ... commit;".
> > 3. Pull the power plug immediately after "commit;" returns.
> > 4. Put plug back in, power on the machine, open database file with
> SQLite,
> > and examine whether the transaction has been executed.
> >
> > Expected result: You always find that the transaction had been executed.
> > Observed result: You sometimes find that the transaction did not execute.
> > (To increase the chance that you end with the transaction not having
> > executed, create an Ext4 partition in an unmounted hard disk, mount the
> > partition with the "-o commit=30" mount option, and do the experiment
> with
> > the database in that partition).
> >
> > Of course, it's possible that the transactions are durable, and I'm just
> > being totally bonkers, or running SQLite in some really wrong way. If
> not,
> > I believe that the problem lies in SQLite not doing an fsync() on the
> > parent directory after unlink()-ing the rollback journal file. It seems
> > that, in Ext4, unlink() is atomic, but not synchronous: the rollback
> > journal can exist after rebooting if a power crash follows the unlink().
> > Also, ftruncate() doesn't seem to be synchronous either (non-durability
> > even if the journal_mode is set to TRUNCATE).
> >
> > I haven't examined how other filesystems treat unlink(). I haven't also
> > tested with other pragma options and other journal_modes. I expect it
> > wouldn't be a problem with WAL (it should be correct even if the log
> exists
> > after the reboot); however, with things like VACUUM-ing, the database
> size
> > would probably not be reduced if a power crash happened, because of
> > ftruncate() not being synchronous. More generally, I think some of the
> code
> > assumes that all non-write and non-create operations that modify a file
> (in
> > other words, unlink and ftruncate) are synchronous; but that's true in
> > Linux only if the partition is mounted with "-o dirsync". One more random
> > thought: if I'm not wrong, "SQLite crash-tests" currently tests
> > partial-flushing only with write() calls
> >
> > Again, sorry for the spam if I'm just being bonkers.
> >
> > Thanks,
> > Thanu
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-21 Thread Simon Slavin

On 21 May 2013, at 5:04pm, Thanumalayan Sankaranarayana Pillai 
 wrote:

> Did anyone look into this? I might be setting some config option wrong,
> so it would be great if you sent me a "you did something wrong" reply if
> you feel that I might have the wrong config (or might be doing something
> totally idiotic).
> 
> I tested with a few other Linux machines and a few different SQLite
> versions, and found that there is an upto-5-second delay (the default
> filesystem commit interval) in the transactions getting durable (with power
> crashes, that is).

I think it's a reflection of what the operating system thinks 'synchronous' 
should mean.  Flushing changes to disk takes time and slows down your 
application so the operating system doesn't always do it when it says it does.  
With Unix, for example, it's quite common to find that some file systems do 
absolutely nothing for flush API calls, or put a timer on flush() which 
prevents it from running more frequently than every few seconds, which would 
introduce extra lag and thrashing your mass storage subsystem (e.g. wearing out 
the stepper motor on a hard disk).  Also, hard disk subsystems (the metal thing 
with a circuit board you hold in your hand) aimed at desktop computers actively 
lie to the OS about when data is written to disk unless you buy hard disks 
explicitly sold for use in servers (and priced to match).

Tracking down exactly what's going on would probably involve reading the source 
code for ftruncate() in your version of your OS, and then assuming that the 
hard disk unit doesn't lie.  It could be done if you want, but ftruncate() 
differs even for different versions of the same Unix file system, so the result 
may not tell us much.

> One more random
> thought: if I'm not wrong, "SQLite crash-tests" currently tests
> partial-flushing only with write() calls

Immediate and true flushing is not a huge concern of the developer team.  A 
delay of 5 seconds, in conditions relating to a normal running of an 
application, probably isn't going to be a deal-breaker in anything you should 
be using a DBMS for.

The main thing, and one you seem to confirm is working properly, is that 
transactions are always either completely written or not written, never with 
some parts written and others not written.  That's part of the ACID conditions 
SQLite does its best to implement, and any breaking of those conditions would 
get full and eager attention from the developers.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-21 Thread Thanumalayan Sankaranarayana Pillai
Hi all,

Did anyone look into this? I might be setting some config option wrong,
so it would be great if you sent me a "you did something wrong" reply if
you feel that I might have the wrong config (or might be doing something
totally idiotic).

I tested with a few other Linux machines and a few different SQLite
versions, and found that there is an upto-5-second delay (the default
filesystem commit interval) in the transactions getting durable (with power
crashes, that is).

Thanks in advance,
Thanu

On Sat, May 18, 2013 at 3:41 AM, thanumalayan mad wrote:

> Hi All,
>
> I was testing out SQLite with a framework I developed. I believe, while
> running on Linux, transactions might not be durable when a power crash
> happens immediately after a commit. I observed this using "SQLite version
> 3.7.16.2 2013-04-12 11:52:43", and kernel "3.8.4-102.fc17.x86_64". Steps to
> reproduce:
>
> 1. Use a Linux machine with an Ext4 filesystem (default mount options).
> 2. Create a database file, set journal_mode to DELETE, perform a
> transaction using "begin transaction ... commit;".
> 3. Pull the power plug immediately after "commit;" returns.
> 4. Put plug back in, power on the machine, open database file with SQLite,
> and examine whether the transaction has been executed.
>
> Expected result: You always find that the transaction had been executed.
> Observed result: You sometimes find that the transaction did not execute.
> (To increase the chance that you end with the transaction not having
> executed, create an Ext4 partition in an unmounted hard disk, mount the
> partition with the "-o commit=30" mount option, and do the experiment with
> the database in that partition).
>
> Of course, it's possible that the transactions are durable, and I'm just
> being totally bonkers, or running SQLite in some really wrong way. If not,
> I believe that the problem lies in SQLite not doing an fsync() on the
> parent directory after unlink()-ing the rollback journal file. It seems
> that, in Ext4, unlink() is atomic, but not synchronous: the rollback
> journal can exist after rebooting if a power crash follows the unlink().
> Also, ftruncate() doesn't seem to be synchronous either (non-durability
> even if the journal_mode is set to TRUNCATE).
>
> I haven't examined how other filesystems treat unlink(). I haven't also
> tested with other pragma options and other journal_modes. I expect it
> wouldn't be a problem with WAL (it should be correct even if the log exists
> after the reboot); however, with things like VACUUM-ing, the database size
> would probably not be reduced if a power crash happened, because of
> ftruncate() not being synchronous. More generally, I think some of the code
> assumes that all non-write and non-create operations that modify a file (in
> other words, unlink and ftruncate) are synchronous; but that's true in
> Linux only if the partition is mounted with "-o dirsync". One more random
> thought: if I'm not wrong, "SQLite crash-tests" currently tests
> partial-flushing only with write() calls
>
> Again, sorry for the spam if I'm just being bonkers.
>
> Thanks,
> Thanu
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

2013-05-18 Thread thanumalayan mad
Hi All,

I was testing out SQLite with a framework I developed. I believe, while
running on Linux, transactions might not be durable when a power crash
happens immediately after a commit. I observed this using "SQLite version
3.7.16.2 2013-04-12 11:52:43", and kernel "3.8.4-102.fc17.x86_64". Steps to
reproduce:

1. Use a Linux machine with an Ext4 filesystem (default mount options).
2. Create a database file, set journal_mode to DELETE, perform a
transaction using "begin transaction ... commit;".
3. Pull the power plug immediately after "commit;" returns.
4. Put plug back in, power on the machine, open database file with SQLite,
and examine whether the transaction has been executed.

Expected result: You always find that the transaction had been executed.
Observed result: You sometimes find that the transaction did not execute.
(To increase the chance that you end with the transaction not having
executed, create an Ext4 partition in an unmounted hard disk, mount the
partition with the "-o commit=30" mount option, and do the experiment with
the database in that partition).

Of course, it's possible that the transactions are durable, and I'm just
being totally bonkers, or running SQLite in some really wrong way. If not,
I believe that the problem lies in SQLite not doing an fsync() on the
parent directory after unlink()-ing the rollback journal file. It seems
that, in Ext4, unlink() is atomic, but not synchronous: the rollback
journal can exist after rebooting if a power crash follows the unlink().
Also, ftruncate() doesn't seem to be synchronous either (non-durability
even if the journal_mode is set to TRUNCATE).

I haven't examined how other filesystems treat unlink(). I haven't also
tested with other pragma options and other journal_modes. I expect it
wouldn't be a problem with WAL (it should be correct even if the log exists
after the reboot); however, with things like VACUUM-ing, the database size
would probably not be reduced if a power crash happened, because of
ftruncate() not being synchronous. More generally, I think some of the code
assumes that all non-write and non-create operations that modify a file (in
other words, unlink and ftruncate) are synchronous; but that's true in
Linux only if the partition is mounted with "-o dirsync". One more random
thought: if I'm not wrong, "SQLite crash-tests" currently tests
partial-flushing only with write() calls

Again, sorry for the spam if I'm just being bonkers.

Thanks,
Thanu
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

2011-10-25 Thread David

Simon L wrote 2011-10-25 06:20:

To reproduce this problem, enter the following 5 SQL statements at the
SQLite command line.

create table X(id INTEGER primary key ON CONFLICT REPLACE);
create table Y(id INTEGER primary key ON CONFLICT REPLACE);
insert into X values (1);
insert into Y select * from X;
insert into Y select * from X;


When I tried to run the last SQL statement twice,  SQLite produced the
following error message.
Error: PRIMARY KEY must be unique


Is this a bug? Please advise. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This certainly looks like a bug. I got a constraint failure when I tried it in 
sqlite 3.7.8.

But it works fine when you state the column name explicitly in the select 
clause.

Like this:

create table X(id INTEGER primary key ON CONFLICT REPLACE);
create table Y(id INTEGER primary key ON CONFLICT REPLACE);
insert into X values (1);
insert into Y select id from X;
insert into Y select id from X;

I recall the "INSERT INTO ... SELECT * ..." had a bug related to foreign key 
checks,
which was reported on the mailing list earlier this year:

http://www.sqlite.org/src/tktview?name=6284df89de

Hopefully, a member of the sqlite dev team will acknowledge this bug soon.
--
Regards,
David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

2011-10-24 Thread Simon L
To reproduce this problem, enter the following 5 SQL statements at the
SQLite command line.

create table X(id INTEGER primary key ON CONFLICT REPLACE);
create table Y(id INTEGER primary key ON CONFLICT REPLACE);
insert into X values (1);
insert into Y select * from X;
insert into Y select * from X;


When I tried to run the last SQL statement twice,  SQLite produced the
following error message.
Error: PRIMARY KEY must be unique


Is this a bug? Please advise. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Potential Bug, Select appears to hang

2010-09-23 Thread Tilghman, Jack
There are more fields in each table, but for the sake of brevity, I ommited 
them from the snippets below.

With smaller data sets, the second query works just fine as well.

SELECT COUNT(*) FROM link;
960219

SELECT COUNT(*) FROM node;
812193

Pvid's are INTEGERS.

On linux, the following query executes just fine from the interactive shell:

"SELECT COUNT(*) FROM link ld LEFT OUTER JOIN node n ON n.pvid = 
ld.light_node_pvid WHERE ld.light_node_pvid != '-1' AND ld.light_node_pvid != 
'-2' AND n.pvid IS NULL;"

But, this one appears  to hang(note the white space and eols in this one):

"SELECT COUNT(*) FROM
 link ld
LEFT OUTER JOIN
  node n
ON
  n.pvid = ld.light_node_pvid
WHERE
  ld.light_node_pvid != '-1'
AND
  ld.light_node_pvid != '-2'
AND
  n.pvid IS NULL;"

I get the same results with perl::dbi.

Any idea's?


The information contained in this communication may be CONFIDENTIAL and is 
intended only for the use of the recipient(s) named above.  If you are not the 
intended recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication, or any of its contents, is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender and delete/destroy the original message and any copy of it 
from your computer or paper files.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users