Re: [sqlite] SQLite with single writer on Windows network share
On May 9, 2019, at 4:56 PM, James K. Lowden wrote: > > file I/O operations act on the kernel’s filebuffer It’s more widely called a buffer cache or page cache: https://en.wikipedia.org/wiki/Page_cache > If this sounds like an indictment of NFS, it's really not. In large part, it’s a restatement of some fundamental results in computer science, particularly distributed computing: https://en.wikipedia.org/wiki/Byzantine_fault https://en.wikipedia.org/wiki/CAP_theorem Reference 2 from the first Wikipedia article is especially worth reading: https://www.microsoft.com/en-us/research/publication/byzantine-generals-problem/ Never mind the current host of that article: that just happens to be where the 2013 Turing Award winner and primary coauthor on this paper, Leslie Lamport, was working when the Web took off. It was originally published in an ACM technical journal while he was doing research at SRI International. In 1982. That’s how fundamental this problem is. As CS research papers go, “The Byzantine Generals Problem” is uncommonly clearly-written. That paper will explain to you why you want at least 3 servers with Bedrock if you’re going to distribute it across multiple machines. It’s fine to start with just 1 server, as long as you’re fine with a single point of failure, but if you’re going to distribute the DB across machines, 2 makes no sense. Intrigued? Read the paper. Bedrock is based in part on the Paxos algorithm, another major advance in distributed computing, and another of Leslie Lamport’s brainchildren: https://en.wikipedia.org/wiki/Paxos_(computer_science) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite with single writer on Windows network share
On Wed, 8 May 2019 21:36:43 +0200 Clemens Ladisch wrote: > Andrew Moss wrote: > > ... an SQLite database hosted on a windows network share (using > > server 2012 R2 or later). We are well aware this is not advisable > > There are three possible sources of network filesystem data > corruption: > > 1) Bad locking implementations. Some Unix-y network filesystems >products allow to disable locking (in a misguided attempt to > increase performance), or do not implement locking at all. Although it doesn't solve anything, it's useful to understand that "bad" locking is not the whole story. File operations under NFS -- by design -- do not agree with the semantics on a local filesystem. The basic problem is that file I/O operations act on the kernel's filebuffer, not what's on the disk. Since all processes on one machine share the same kernel, they share the same filebuffer, and that filebuffer is the very definition an accurate representation of the state of the filesystem. With NFS, that's not true. There are as many filebuffers as there are nodes using the fileserver, plus the fileserver's own. If machine X updates the file, nothing informs machine Y. If the second machine acts on stale information in its filebuffer -- boom! -- welcome to a corrupted file. As a matter of fact, when machine X updates the file, nothing guarantees *any* data reach the server. Data are not necessarily written until the file is closed. Under SQLite, that could be a long time indeed! Even if locks were honored with perfect fidelity, inconsistent filebuffers in different clients sharing the same file provide lots of opportunity for inconsistency. A low-traffic system with a single writer and not much contention might not bump into it very often (or notice when it does!) but on NFS none of the ACID promises SQLite makes are actually in force. You may read that NFSv4 solves locking problems and others. It does not alter the basic consistency guarantee, though, that nothing is assuredly on the disk until the file is closed. It does not inform other clients' kernels of changes to files they're sharing. NFSv4 provides new functions to commit data to the disk and ascertain whether the current in-kernel image is up to date. SQLite does not use those functions and, even if it did, the mishmash of implementations would make any guaratees tenuous at best. If this sounds like an indictment of NFS, it's really not. Ironically, back when NFS was being invented, the cognoscenti already knew that what the filesystem couldn't provide, database servers could, and would, and soon did. Any problems with consistency, concurrency, locking, etc., were evidence that the process required a DBMS, not that the filesystem was insufficient to the purpose. They were right: the inconsistent-information problem is better solved not by disseminating the information across N filebuffers, but by routing the information through a single one, on a shared DBMS server. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Series of statements results in a malformed database disk image
Thanks for the fix! Best, Manuel On Thu, May 9, 2019 at 7:12 PM Richard Hipp wrote: > On 5/9/19, Manuel Rigger wrote: > > > > I discovered a sequence of statements that results in a malformed > database > > disk image: > > > > Should be fixed now. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] feature request -- enhance strftime() implementing %V, %g and %G for week of year according to ISO 8601
On 9-5-2019 18:20, Nißl Reinhard wrote: Hi, it would be nice, if sqlite3's strftime() would support the following formatting codes: %g The last 2 digits of the ISO 8601 week-based year as a decimal number (00 - 99) %G The ISO 8601 week-based year as a decimal number %V ISO 8601 week number as a decimal number (00 - 53) minimum value for %V is 01. The ISO 8601 week and week-based year produced by %V, %g, and %G, uses a week that begins on Monday, where week 1 is the week that contains January 4th, which is the first week that includes at least four days of the year. If the first Monday of the year is the 2nd, 3rd, or 4th, the preceding days are part of the last week of the preceding year. For those days, %V is replaced by the last week of the preceding year, and both %g and %G are replaced by the digits of the preceding year. The above text has originally been taken from this documentation and corrected regarding "%V is replaced by 53": https://docs.microsoft.com/de-de/cpp/c-runtime-library/reference/strftime-wcsftime-strftime-l-wcsftime-l?view=vs-2019 better docs: http://man7.org/linux/man-pages/man3/strftime.3.html ;-) Thanks. Bye. -- Reinhard Nißl reinhard.ni...@fee.de ___ 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] Series of statements results in a malformed database disk image
On 5/9/19, Manuel Rigger wrote: > > I discovered a sequence of statements that results in a malformed database > disk image: > Should be fixed now. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] feature request -- enhance strftime() implementing %V, %g and %G for week of year according to ISO 8601
Hi, it would be nice, if sqlite3's strftime() would support the following formatting codes: %g The last 2 digits of the ISO 8601 week-based year as a decimal number (00 - 99) %G The ISO 8601 week-based year as a decimal number %V ISO 8601 week number as a decimal number (00 - 53) The ISO 8601 week and week-based year produced by %V, %g, and %G, uses a week that begins on Monday, where week 1 is the week that contains January 4th, which is the first week that includes at least four days of the year. If the first Monday of the year is the 2nd, 3rd, or 4th, the preceding days are part of the last week of the preceding year. For those days, %V is replaced by the last week of the preceding year, and both %g and %G are replaced by the digits of the preceding year. The above text has originally been taken from this documentation and corrected regarding "%V is replaced by 53": https://docs.microsoft.com/de-de/cpp/c-runtime-library/reference/strftime-wcsftime-strftime-l-wcsftime-l?view=vs-2019 Thanks. Bye. -- Reinhard Nißl reinhard.ni...@fee.de ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Series of statements results in a malformed database disk image
On 5/9/19, David Raymond wrote: > I'm curious as to what part of the integrity got > broken. There is an index on a REAL value. Maintaining such an index requires doing equality comparisons on floating-point values. The dangers of doing equality comparisons on floating-point values are well known. This is appears to be an instance where SQLite is not handling this inherently risky operation quite correctly. My initial guess is that the problem is somehow related to SQLite's attempts to store floating point values as integers in order to safe disk space, when the floating point value can be represented by an integer. That optimization works well when storing floating point values like 1.0 and 0.0, but might be running into round-off error problems when storing 9223372036854775807.0. Still looking. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Series of statements results in a malformed database disk image
So it happens _before_ the update or replace? That is weird indeed. Using "indexed by" still returns 2 rows from the index, but integrity check reports 1 missing, so I'm curious as to what part of the integrity got broken. SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t1 (c0, c1 real primary key); QUERY PLAN `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) sqlite> insert into t1 (c0, c1) values (0, 9223372036854775807), (0, 0); QUERY PLAN `--SCAN 2 CONSTANT ROWS sqlite> select * from t1; QUERY PLAN `--SCAN TABLE t1 c0|c1 0|9.22337203685478e+18 0|0.0 sqlite> select c1 from t1 indexed by sqlite_autoindex_t1_1; QUERY PLAN `--SCAN TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 c1 0.0 9.22337203685478e+18 sqlite> pragma integrity_check; integrity_check ok sqlite> update t1 set c0 = null; QUERY PLAN `--SCAN TABLE t1 sqlite> select * from t1; QUERY PLAN `--SCAN TABLE t1 c0|c1 |9.22337203685478e+18 |0.0 sqlite> select c1 from t1 indexed by sqlite_autoindex_t1_1; QUERY PLAN `--SCAN TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 c1 0.0 9.22337203685478e+18 sqlite> pragma integrity_check; integrity_check row 1 missing from index sqlite_autoindex_t1_1 sqlite> -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, May 09, 2019 11:08 AM To: SQLite mailing list Subject: Re: [sqlite] Series of statements results in a malformed database disk image On 5/9/19, Manuel Rigger wrote: > > I discovered a sequence of statements that results in a malformed database > disk image: > > CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY); > INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0); > UPDATE t1 SET c0 = NULL; > UPDATE OR REPLACE t1 SET c1 = 1; > SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL); > > The last statement returns the following: > |1.0 > Error: near line 5: database disk image is malformed Ticket here: https://www.sqlite.org/src/tktview/6c1d3febc00b22d457c7 -- D. Richard Hipp d...@sqlite.org ___ 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] Series of statements results in a malformed database disk image
On 5/9/19, Manuel Rigger wrote: > > I discovered a sequence of statements that results in a malformed database > disk image: > > CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY); > INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0); > UPDATE t1 SET c0 = NULL; > UPDATE OR REPLACE t1 SET c1 = 1; > SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL); > > The last statement returns the following: > |1.0 > Error: near line 5: database disk image is malformed Ticket here: https://www.sqlite.org/src/tktview/6c1d3febc00b22d457c7 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Series of statements results in a malformed database disk image
Edit: HOWEVER, just ran an integrity check, and that did fail. "wrong # of entries in index sqlite_autoindex_t1_1" On Thu, May 9, 2019 at 3:52 PM Chris Locke wrote: > Are you using a new database when you create your table, or using an > existing database? > Are you writing your database locally? > What operating system / sqlite version are you using? > > The above test works for me... > > > Execution finished without errors. > > > Result: 1 rows returned in 62ms > > > At line 4: > > > SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL); > > > > > On Thu, May 9, 2019 at 3:47 PM Manuel Rigger > wrote: > >> Hi, >> >> I discovered a sequence of statements that results in a malformed database >> disk image: >> >> CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY); >> INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0); >> UPDATE t1 SET c0 = NULL; >> UPDATE OR REPLACE t1 SET c1 = 1; >> SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL); >> >> The last statement returns the following: >> |1.0 >> Error: near line 5: database disk image is malformed >> >> Unlike some of my previous test cases, this actually looks like something >> that could happen in practice, or what do you think? >> >> Best, >> Manuel >> ___ >> 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] Series of statements results in a malformed database disk image
Are you using a new database when you create your table, or using an existing database? Are you writing your database locally? What operating system / sqlite version are you using? The above test works for me... > Execution finished without errors. > Result: 1 rows returned in 62ms > At line 4: > SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL); On Thu, May 9, 2019 at 3:47 PM Manuel Rigger wrote: > Hi, > > I discovered a sequence of statements that results in a malformed database > disk image: > > CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY); > INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0); > UPDATE t1 SET c0 = NULL; > UPDATE OR REPLACE t1 SET c1 = 1; > SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL); > > The last statement returns the following: > |1.0 > Error: near line 5: database disk image is malformed > > Unlike some of my previous test cases, this actually looks like something > that could happen in practice, or what do you think? > > Best, > Manuel > ___ > 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] Series of statements results in a malformed database disk image
Hi, I discovered a sequence of statements that results in a malformed database disk image: CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY); INSERT INTO t1(c0, c1) VALUES (TRUE, 9223372036854775807), (TRUE, 0); UPDATE t1 SET c0 = NULL; UPDATE OR REPLACE t1 SET c1 = 1; SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL); The last statement returns the following: |1.0 Error: near line 5: database disk image is malformed Unlike some of my previous test cases, this actually looks like something that could happen in practice, or what do you think? Best, Manuel ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Row is not fetched with PRAGMA reverse_unordered_selects=true
Hi E. Pasma, Thanks for your interest in this! The test case failed on trunk, after the previous bug that you are referring to was fixed. It seems that the bug I newly reported has been fixed in https://www.sqlite.org/src/info/ebe4845cd0ffb96b. In order to trigger it, this more complex (and admittedly obscure) test case was necessary. Best, Manuel On Thu, May 9, 2019 at 10:54 AM E.Pasma wrote: > > > Op 9 mei 2019, om 00:07 heeft Manuel Rigger > het volgende geschreven: > > Hi, > > > > I discovered another bug that is triggered when "PRAGMA > > reverse_unordered_selects=true" is used. It's similar to a previous bug > > that I reported [1], but the statement triggering the bug has a compound > > expression that should always be true (for values that are not NULL) in > the > > WHERE clause: > > > > CREATE TABLE t0(c0 INTEGER PRIMARY KEY); > > INSERT INTO t0(c0) VALUES (1); > > PRAGMA reverse_unordered_selects=true; > > SELECT * FROM t0 WHERE ((t0.c0 > 'a') OR (t0.c0 <= 'a')); -- fetches no > row > > SELECT ((t0.c0 > 'a') OR (t0.c0 <= 'a')) FROM t0; -- returns 1 > > > > Best, > > Manuel > > > > > > [1] https://www.sqlite.org/src/tktview?name=9cf6c9bb51 > > Hi Mauel, > > This appears to have been fixed with the previous bug that you referred > to. The example in the referred ticket is even more simple: > CREATE TABLE t14(x INTEGER PRIMARY KEY); > INSERT INTO t14(x) VALUES (100); > SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC; > SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC; > Hope I'm not mistaken, > > E. Pasma > ___ > 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] Row is not fetched with PRAGMA reverse_unordered_selects=true
> Op 9 mei 2019, om 00:07 heeft Manuel Rigger het > volgende geschreven: > Hi, > > I discovered another bug that is triggered when "PRAGMA > reverse_unordered_selects=true" is used. It's similar to a previous bug > that I reported [1], but the statement triggering the bug has a compound > expression that should always be true (for values that are not NULL) in the > WHERE clause: > > CREATE TABLE t0(c0 INTEGER PRIMARY KEY); > INSERT INTO t0(c0) VALUES (1); > PRAGMA reverse_unordered_selects=true; > SELECT * FROM t0 WHERE ((t0.c0 > 'a') OR (t0.c0 <= 'a')); -- fetches no row > SELECT ((t0.c0 > 'a') OR (t0.c0 <= 'a')) FROM t0; -- returns 1 > > Best, > Manuel > > > [1] https://www.sqlite.org/src/tktview?name=9cf6c9bb51 Hi Mauel, This appears to have been fixed with the previous bug that you referred to. The example in the referred ticket is even more simple: CREATE TABLE t14(x INTEGER PRIMARY KEY); INSERT INTO t14(x) VALUES (100); SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC; SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC; Hope I'm not mistaken, E. Pasma ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
