Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/01/13 21:11, Larry Brasfield wrote:
> If anybody truly cares enough to make this behave better, it is
> encapsulated in a function named "booleanValue(char *zArg)".  For
> myself, since it takes "0" and "1", which are easy to type, the present
> behavior is entirely unobjectionable.
> 
> As for whether an appended ';' (or any other junk not called for in
> the .help output) ought to produce a diagnostic instead of simply
> following the above logic, I would say that adherence to the GIGO
> principle is perfectly adequate.

Note the change the SQLite team have made:

  http://www.sqlite.org/src/info/b4d94947fc

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlEHZEMACgkQmOOfHg372QTUTgCeOLdm5gpZQDdE6WIWZHZMzJ1M
YwIAn3AuE9RW4STFDzSU9zw0loph+nxR
=l3sT
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

[Larry Brasfield]

David Bicking wrote:

Actually, it looks like anything except ".header on" will turn headers off. That includes ".header 
on;" (with semicolon - it doesn't do nothing, it turns the headers off) or ".header off;" (with or 
without semincolon.) or ".header ;" (with a space and semicolon but no text.)

I could only get it to return error text with .header or .header; (no space 
before semicolon.)

.explain behaves the same way. "on;" is treated the same as "off" or "foo".

(At least with version 3.7.0 which is what I had handy to test with.)


A quick perusal of the code reveals this logic:
Any case variation of "on" or "yes", or anything that starts with a 
non-zero integer counts as "true" with regard to its effect as an option 
setting.  Anything else counts as "false".  If this was the "expected" 
behavior, it would have to be considered bug-free now.


If anybody truly cares enough to make this behave better, it is 
encapsulated in a function named "booleanValue(char *zArg)".  For 
myself, since it takes "0" and "1", which are easy to type, the present 
behavior is entirely unobjectionable.


As for whether an appended ';' (or any other junk not called for in the 
.help output) ought to produce a diagnostic instead of simply following 
the above logic, I would say that adherence to the GIGO principle is 
perfectly adequate.


--
Larry Brasfield

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

Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

[David Bicking]

 From: Ryan Johnson 
To: sqlite-users@sqlite.org 
Sent: Monday, January 28, 2013 12:54 PM
Subject: Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands 
ending in semicolons while rejecting others.
 
On 28/01/2013 12:08 PM, Larry Brasfield wrote:
> Nathan Chung wrote:
>> *Summary:
>> The SQLite3 shell accepts some dot commands ending in semicolons while
>> rejecting others without displaying proper error messages. The
>> behavior of the dot commands could be more consistent. Examples
>> include "header off;" and "header on;". The shell accepts "header
>> off;" while rejecting "header on;" without a proper error message.
>
> 7. There may be other dot commands displaying other inconsistent
>> behaviors. I have only verified the issue with "header on;" and
>> "header off;".
> 
> The code for the shell command decoder is not written to perform as you 
> expect.  It is very simple, (which is also a virtue), and >focused on 
> accepting correctly composed commands. The shell is considered to be a tool 
> for easily performing basic operations >upon a SQLite database, in the hands 
> of more software-savvy people than those who would be lost without detailed 
> and >"consistent" error reporting.  I dare say that said code, if modified to 
> meet your expectations, would be less suited to its purpose, >harder to 
> maintain and understand, and would divert effort from SQLite itself.  I say 
> this as one who has found reason to modify >that code and who is glad that 
> part of the task consumed little time.
>".header on;" -- does nothing
>".header off;" -- disables headers

>I'm with OP on this one. The error message doesn't need to be "helpful" -- in 
>fact I'm fine if it accepts semicolons, given that >most things you type in 
>the shell require them. Turning apparently-successful commands into no-ops is 
>never a good idea, though.

>My guess is that it's trivial to fix, especially if the decision is to allow 
>semicolons and other trailing garbage (though `.header foo' >would still 
>silently "succeed"). Probably a one-liner along the lines of 
>s/strcmp/strncmp/, or some such.

>$0.02
>Ryan

Actually, it looks like anything except ".header on" will turn headers off. 
That includes ".header on;" (with semicolon - it doesn't do nothing, it turns 
the headers off) or ".header off;" (with or without semincolon.) or ".header ;" 
(with a space and semicolon but no text.)

I could only get it to return error text with .header or .header; (no space 
before semicolon.)

.explain behaves the same way. "on;" is treated the same as "off" or "foo".

(At least with version 3.7.0 which is what I had handy to test with.)

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

Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

[Ryan Johnson]

On 28/01/2013 12:08 PM, Larry Brasfield wrote:

Nathan Chung wrote:

SQLite version: 3.6.12
OS: Mac OS X 10.6.8

*Summary:
The SQLite3 shell accepts some dot commands ending in semicolons while
rejecting others without displaying proper error messages. The
behavior of the dot commands could be more consistent. Examples
include "header off;" and "header on;". The shell accepts "header
off;" while rejecting "header on;" without a proper error message.

*Steps to reproduce:
1. Launch SQLite3 shell with a populated database and type in 
".header on".
2. Try a select statement. The retrieved table will show the field 
headers.

3. Type in ".header off;".
4. Try a select statement. The retrieved table will not show the 
field headers.

5. Type in ".header on;".
6. Try a select statement. The retrieved table will still not show the
field headers. Note that the ".header on;" command from the previous
step did not generate any error messages even though the command was
rejected.
7. There may be other dot commands displaying other inconsistent
behaviors. I have only verified the issue with "header on;" and
"header off;".


The code for the shell command decoder is not written to perform as 
you expect.  It is very simple, (which is also a virtue), and focused 
on accepting correctly composed commands. The shell is considered to 
be a tool for easily performing basic operations upon a SQLite 
database, in the hands of more software-savvy people than those who 
would be lost without detailed and "consistent" error reporting.  I 
dare say that said code, if modified to meet your expectations, would 
be less suited to its purpose, harder to maintain and understand, and 
would divert effort from SQLite itself.  I say this as one who has 
found reason to modify that code and who is glad that part of the task 
consumed little time.

".header on;" -- does nothing
".header off;" -- disables headers

I'm with OP on this one. The error message doesn't need to be "helpful" 
-- in fact I'm fine if it accepts semicolons, given that most things you 
type in the shell require them. Turning apparently-successful commands 
into no-ops is never a good idea, though.


My guess is that it's trivial to fix, especially if the decision is to 
allow semicolons and other trailing garbage (though `.header foo' would 
still silently "succeed"). Probably a one-liner along the lines of 
s/strcmp/strncmp/, or some such.


$0.02
Ryan

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

Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

[Larry Brasfield]

Nathan Chung wrote:

SQLite version: 3.6.12
OS: Mac OS X 10.6.8

*Summary:
The SQLite3 shell accepts some dot commands ending in semicolons while
rejecting others without displaying proper error messages. The
behavior of the dot commands could be more consistent. Examples
include "header off;" and "header on;". The shell accepts "header
off;" while rejecting "header on;" without a proper error message.

*Steps to reproduce:
1. Launch SQLite3 shell with a populated database and type in ".header on".
2. Try a select statement. The retrieved table will show the field headers.
3. Type in ".header off;".
4. Try a select statement. The retrieved table will not show the field headers.
5. Type in ".header on;".
6. Try a select statement. The retrieved table will still not show the
field headers. Note that the ".header on;" command from the previous
step did not generate any error messages even though the command was
rejected.
7. There may be other dot commands displaying other inconsistent
behaviors. I have only verified the issue with "header on;" and
"header off;".


The code for the shell command decoder is not written to perform as you 
expect.  It is very simple, (which is also a virtue), and focused on 
accepting correctly composed commands. The shell is considered to be a 
tool for easily performing basic operations upon a SQLite database, in 
the hands of more software-savvy people than those who would be lost 
without detailed and "consistent" error reporting.  I dare say that said 
code, if modified to meet your expectations, would be less suited to its 
purpose, harder to maintain and understand, and would divert effort from 
SQLite itself.  I say this as one who has found reason to modify that 
code and who is glad that part of the task consumed little time.


--
Larry Brasfield

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

Re: [sqlite] system.data.sqlite example code?

[Adam DeVita]

>From this URL
http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/
The basics of how to set your project and include/reference the Dlls
are there. You should be able to adapt to your IDE.  (I currently use
VS2010 for C# sqlite projects.)

and this one
http://www.devart.com/dotconnect/sqlite/docs/Devart.Data.SQLite~Devart.Data.SQLite.SQLiteConnection.html

The examples are shorter and more to the point.


"Good" is subjective. For people with experience in sqlite or
databases but are new to the C# api, I felt this was helpful to get me
started.

There are many ways to import spread sheets in various formats.  If
you have your IDE configured one can do inserts from within Visual
Studio.  You could also do it using the standard command line tool.
You could write a small program to do it.  This list has some lengthy
discussions of the hazards and trials of CSV formal.  (I personally
favour TAB delimited text files for plane text over CSV because my
users would never use a horizontal tab, but will put quotes and commas
in the names of things.)  I think that the discussion of importing
data is a separate discussion from 'give some references to good
examples of using the API'.  Sometimes I just use the spread sheet to
write the insert queries, if it is reasonable to do so. (Such as I can
reasonably review it all to know the data is clean and safe.)

WPF trees, grids etc. you should determine if the thing needs to be
static or dynamic.  Having done it, that type of code has several
examples online and is more of a Windows Forms of WPF code discussion
compared to sqlite. If you have a predefined db where the columns of
your grid are not dynamic then there are lots of examples online.

regards,
Adam


On Sun, Jan 27, 2013 at 9:50 AM, Don Goyette  wrote:
> Hi All,
>
> I was wondering if anyone knows of a good websites or forums that have good
> articles and/or tutorials (with example code) on how to use
> system.data.sqlite with C#, .NET framework, running in Visual Studio 2012
> Pro?  And with a variety of data sources to get the initial data for
> populating the database tables (ie. CSV, Excel, existing related SQL/SQLite
> tables, XML, etc.
>
> I'm open as to data display choices (Windows Forms or WPF) and controls
> (List, Tree, Grid).  I learn best by seeing the code, versus reading long
> papers or books.
>
> Thanks in advance!
>
> -Don
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Use of sqlite as a FIFO buffer

[Clemens Ladisch]

Alexandros Kostopoulos wrote:
> I would like to use sqlite as a FIFO buffer.

In SQLite, tables are stored as a B-tree (indexed by the ROWID, or by
the INTEGER PRIMARY KEY if you have declared one).

When you remove the oldest entry, you get a hole in the first page of
the table.  When you add a new entry, it gets added to the last page of
the table.

Empty pages are reused when SQLite needs to allocate a new page.

(B-tree pages can be split or joined when SQLite thinks that they are
too full or too empty, so getting empty pages, or allocating new ones,
might not happen exactly when you think it should.)

> - How would sqlite scale as a FIFO buffer, for a DB size in the order
> of a few GBs, for both read and write operations?

Not worse than any other DB organization with similar amounts of
additions/deletions.  A FIFO is certainly better than a DB where
random records are deleted, because in the latter case you'd have
unused space (taking up I/O bandwidth) all over the table.

> can I bound the maximum db file size by making sure that I delete as
> much data as needed before inserting new data?

B-tree reorganizations might give you several partially filled pages,
but there is a lower limit of the amount of data in one page, so there
is an upper bound on the DB file size.


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

Re: [sqlite] bug report: out of memory error for most operations on a large database

[Richard Hipp]

On Sun, Jan 27, 2013 at 2:57 PM, ammon_lymphater <
ammon_lympha...@hotmail.com> wrote:

>
> I think I localized the bug - potential query memory use with larger cache
> sizes
>
> Details
> - created a database and started in a loop 10 times { import 1m
> records; run query}
> - for each iteration observe the memory use by SQLITE3 process
> using
> task manager
> - the mimimum use was during the load, the maximum during the query
>
> Below you will find the observations of memory size for three different
> cache sizes (Page size 32768)
>
> Comments:
> - for 2k pages the maximum levels off at d (4m rows) at 220mb, so
> the query mem use is 220-60 = 160mb
> - for 10k pages the maximum grows by 50mb for each million rows,
> query mem is 160 mb initially, but 500mb for 10m rows
> - for 30k pages cache the picture is similar to that of 10k pages
> cache
>
> Summary: it looks like for large cache sizes the query has some sort of
> memory mis-management.
> Run on the full table with 500m records but cache size limited to 2k pages
> the same query was fine.
>

Have you measured SQLite's memory utilization directly using the
sqlite3_memory_used() routine described at

 http://www.sqlite.org/c3ref/memory_highwater.html

Do you have any other evidence to suggest that the problem is in SQLite and
not in your application?



>
> The coding is 'f 66 220' means that during loading of the 6-th million of
> rows (f) the minimum memory reported
> Was 66mb, maximum 220mb)
> cache 2k pages
> a 90  b 64 166 c 64 ...   d 64 219  e 64 220  f 66 220 g 65 220 h 65
> 220
> i 65 220 j 65 220
>
> cache 10k pages
> a 135 187 b 202 372 c 307 469 d 320 521 e 320 571 f 318 622 g 319 672 h 322
> 722 i 318 772 j 323 824
>
> cache 30k pages
>
> a 187 270 b .. 373 c 363 559 d 421 746 e 562 932 f 711 1175 g 820 1302 h
> 974
> 1354 i 960 1403 j 961 1455
>


-- 
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] Use of sqlite as a FIFO buffer

[Alexandros Kostopoulos]

I would like to use sqlite as a FIFO buffer. I know this isn't its intended
use, but I a) need its robust storage backend and b) also need to apply and
queries to it (so, it will not be actually used only as a FIFO).

My questions are the following:

- How would sqlite scale as a FIFO buffer (where entries are 'pushed' in
the last row of the db, and 'popped' from the first raw of the db), for a
DB size in the order of a few GBs, for both read and write operations?

- When entries are 'popped' (i.e. the first entry in the table is removed)
the DB creates a 'hole' in the file. Is it guaranteed that, as long as the
new entries fill into the holes, the DB size won't increase? That is, can I
bound the maximum db file size by making sure that I delete as much data as
needed before inserting new data?

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

[sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

[Nathan Chung]

SQLite version: 3.6.12
OS: Mac OS X 10.6.8

*Summary:
The SQLite3 shell accepts some dot commands ending in semicolons while
rejecting others without displaying proper error messages. The
behavior of the dot commands could be more consistent. Examples
include "header off;" and "header on;". The shell accepts "header
off;" while rejecting "header on;" without a proper error message.

*Steps to reproduce:
1. Launch SQLite3 shell with a populated database and type in ".header on".
2. Try a select statement. The retrieved table will show the field headers.
3. Type in ".header off;".
4. Try a select statement. The retrieved table will not show the field headers.
5. Type in ".header on;".
6. Try a select statement. The retrieved table will still not show the
field headers. Note that the ".header on;" command from the previous
step did not generate any error messages even though the command was
rejected.
7. There may be other dot commands displaying other inconsistent
behaviors. I have only verified the issue with "header on;" and
"header off;".

Thank you.
-Nathan Chung
nchung...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug report: out of memory error for most operations on a large database

[ammon_lymphater]

I think I localized the bug - potential query memory use with larger cache
sizes

Details
- created a database and started in a loop 10 times { import 1m
records; run query}
- for each iteration observe the memory use by SQLITE3 process using
task manager
- the mimimum use was during the load, the maximum during the query

Below you will find the observations of memory size for three different
cache sizes (Page size 32768)

Comments:
- for 2k pages the maximum levels off at d (4m rows) at 220mb, so
the query mem use is 220-60 = 160mb
- for 10k pages the maximum grows by 50mb for each million rows,
query mem is 160 mb initially, but 500mb for 10m rows
- for 30k pages cache the picture is similar to that of 10k pages
cache

Summary: it looks like for large cache sizes the query has some sort of
memory mis-management.
Run on the full table with 500m records but cache size limited to 2k pages
the same query was fine.

The coding is 'f 66 220' means that during loading of the 6-th million of
rows (f) the minimum memory reported
Was 66mb, maximum 220mb)
cache 2k pages  
a 90  b 64 166 c 64 ...   d 64 219  e 64 220  f 66 220 g 65 220 h 65 220
i 65 220 j 65 220

cache 10k pages
a 135 187 b 202 372 c 307 469 d 320 521 e 320 571 f 318 622 g 319 672 h 322
722 i 318 772 j 323 824

cache 30k pages

a 187 270 b .. 373 c 363 559 d 421 746 e 562 932 f 711 1175 g 820 1302 h 974
1354 i 960 1403 j 961 1455


-Original Message-
From: ammon_lymphater [mailto:ammon_lympha...@hotmail.com] 
Sent: Friday, January 25, 2013 1:40 PM
To: 'Michael Black'; 'General Discussion of SQLite Database'
Subject: RE: [sqlite] bug report: out of memory error for most operations on
a large database

To answer your question: 
 - Disk space is not a problem - there is over 500gb free space on that disk
(with database size 80gb). 
 - Memory - 16gb with at least 8 free at all times during testing
 - Platform -  I moved the platform to Windows Server 2012

I did more testing. I have a large csv file (100gb/500m lines)  Originally I
have imported full file into database  (using sqlite3 .import)  - and then
ran the select query which resulted in out-of-memory error;

Over the last 24 hours it tried the following
 - split the csv file into smaller pieces (10m lines each)
 - import  smaller pieces one-by-one  (either in one sqlite3 session or 1
session per piece - same result)
-  after each import run the query to check whether we have out-of-memory
condition

Out of memory condition:
1. does not affect import - I can still load the rows and count shows that
they are loaded 2. once it occurs all subsequent select...group by queries
show out of memory - adding new rows does not change that 3. is _random_ I
got it after three piece import  yesterday, after two piece import  today
(on the same machine, creating new db each time).
 
So this is _not_ deterministic but over larger timescale (like few hours -
not minutes or a couple of hours)
 - It does not depend on the database size - 20m lines database is only 3gb.

 - In in the full database I have tables with 20 and 30gb sizes  - different
schemas - many more columns but many less rows
  - It is not caused by malformatted data - the data is from a bcp out (SQL
Server) (I will test in in the next 24h)

Additional observations:
When looking at task manager and sqlite3 process
- during import the memory growth up to 800 mb (the cache size)
- during select count(*) it goes up to 1.5gb
- when starting select ... group by the memory it goes up to 1.6 gb
for successful imports, up to 2gb for out of memory condition

My current suspicion: this is not just the size, but (mostly?) the query. 





-Original Message-
From: Michael Black [mailto:mdblac...@yahoo.com]
Sent: Friday, January 25, 2013 5:41 AM
To: ammon_lympha...@hotmail.com; 'General Discussion of SQLite Database'
Subject: RE: [sqlite] bug report: out of memory error for most operations on
a large database

How much free disk space do you have?  Your temp tables might be exceeding
capacity.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ammon_lymphater
Sent: Thursday, January 24, 2013 1:41 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] bug report: out of memory error for most operations on a
large database

Summary: except for  select count(*) , all operations on a large table (500m
rows/80gb) result in out-of-memory error

 

Details:

a.   Using SQLite3 ver 3.7.15.2, Windows 7  64 bit AMD(but the error
also in 3.6.xx and on other Windows platforms)

b.  Created a table (schema attached), imported data from a csv file
using sqlite3 (no problems)

c.  "select Name5, count(*) from LargeData group by name5 order by name5
results" in Error: out of memory (the cardinality of Name5 is 12)

d.  "sqlite> select count(*) from StorageHourlyBySIDCL2;" gives 587608348,
as it should

e.   

Re: [sqlite] [sqlite-dev] Can I safely use the pragma synchronization = OFF?

[Simon Slavin]

TL;DR: If you want ACID at the OS and storage firmware level, expect to buy 
expensive server-rated hardware and expect it to be slow.

On 28 Jan 2013, at 12:30pm, Phil Schwan  wrote:

> Arguably more importantly, there's an OS page cache that sits between your
> application (sqlite) and the file system.  Unless you disable the cache --
> the equivalent of doing an fdatasync() after every operation anyway -- or
> you have an exceptionally clever file system, the OS will combine separate
> writes to the same page before they hit the disk.

On 28 Jan 2013, at 12:29pm, Richard Hipp  wrote:

> Furthermore, I'm pretty sure every modern unix-like system will usually
> reorder the sequence of write()s so that data reaches oxide in a different
> order from how data was written by the application.

Worse still, your hard disk subsystem (You noticed your hard disk has a green 
circuit board on, didn't you ?) probably reorders writes too.  Because this 
results in overall faster operation.  And since it's happening at the storage 
module level, the OS doesn't know about it and can't stop it.

You can buy expensive server-rated hard disks which have jumper settings which 
turn this off.  They're slow.  You wouldn't want to boot from one.  But I have 
tested more than one cheap hard disk which does have the jumper settings 
(according to the documentation) but as far as I could see ignored them.  At 
least sending it a lot of intentionally badly-ordered set of writes took no 
longer (statistical average) when I moved the jumpers.

Amusingly, SSD seem to have less optimization of this kind done in storage 
firmware.  Presumably because they're actually faster when you distribute 
writes between the different banks of storage.

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

Re: [sqlite] [sqlite-dev] Can I safely use the pragma synchronization = OFF?

[Phil Schwan]

I'm not even sure why I'm wading into this; glutton for punishment, I guess.


TL;DR: the assumption that a data-journaled file system guarantees the
atomicity of individual write()s is, in my experience, not a valid one.



Unfortunately this isn't really a topic about which one can draw general
conclusions.  In practice, every file system -- especially network file
systems -- provide subtly different semantics.

But if I *had* to make a general statement, I'd say that even with full
data journaling you cannot trust that writes will necessarily be replayed
in full or in order without explicit fdatasync()ing as sqlite does.  There
are many ways in which the ordering may be disrupted.

Most file systems guarantee atomicity of writes only up to a relatively
small block size (4k is popular).  If you write multiple blocks, even in a
single system call, it's usually possible for one to succeed and another to
fail.

Arguably more importantly, there's an OS page cache that sits between your
application (sqlite) and the file system.  Unless you disable the cache --
the equivalent of doing an fdatasync() after every operation anyway -- or
you have an exceptionally clever file system, the OS will combine separate
writes to the same page before they hit the disk.

The purpose of data journaling for most file systems isn't to provide
strict atomicity OR ordering of application writes.  It's to prevent things
like an unallocated data block (containing whatever random rubbish was
already at that point on the disk platter) appearing in your file if a
power loss or system crash occurs shortly after a write().

Consider the case where:

- sqlite transaction A modifies blocks 1 and 2 of the file
- sqlite transaction B modifies blocks 2 and 3 of the file
- you kick out the power cable

Without an intermediate fdatasync(), it's entirely possible with most file
systems that:

- blocks 1 and 2 are written, containing the changes from both
transactions, but not block 3

- blocks 2 and 3 are written, containing the changes from both
transactions, but not block 1

- blocks 1 and 3 are written, but not block 2

- all three blocks are written, but block 2 only is missing the
modification from transaction B

Any of those scenarios result in a corrupted database.

Maybe you're using a file system that protects against these things and
guarantees strict ordering.  Are you sure it does that even in the face of
a power outage?  Of a disk failure?  Of a disk failure that occurs when
restoring from a power failure?  Of the failure of one of the DIMMs in its
cache?

For 6 years I worked with clients who had a nearly-unlimited budget to
throw at hardware, and none of those systems provided those guarantees
unless you disabled the caches (at which point you might as well let sqlite
call fsync).  Are you willing to bet that yours does?

Cheers,

-p

On 28 January 2013 18:57, Shuki Sasson  wrote:

>
> A *physical journal* logs an advance copy of every block that will later be
> written to the main file system. If there is a crash when the main file
> system is being written to, the write can simply be replayed to completion
> when the file system is next mounted. If there is a crash when the write is
> being logged to the journal, the partial write will have a missing or
> mismatched checksum and can be ignored at next mount.
>
> Physical journals impose a significant performance penalty because every
> changed block must be committed *twice* to storage, but may be acceptable
> when *absolute fault protection is
> required.*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Can I safely use the pragma synchronization = OFF?

[Richard Hipp]

On Sun, Jan 27, 2013 at 12:21 PM, Shuki Sasson wrote:

> No confusion here, the atomicity of the FS journal guarantees that the
> fwrite will happen in full or not happen at all...
>

First off, SQLite uses write(), not fwrite().

Secondly, I don't think any modern unix-like system guarantees atomicity of
write() operations.  You might get an atomic write under special
circumstances (such as a write of exactly one sector) but not in general.
Furthermore, I'm pretty sure every modern unix-like system will usually
reorder the sequence of write()s so that data reaches oxide in a different
order from how data was written by the application.  Together, these
observations go a long way to ensuring that your database files will go
corrupt if you are in the middle of a transaction with PRAGMA
synchronous=OFF when the power goes out, even if you are on the latest
trendy journalling file system.

But you are welcomed to try to prove me wrong by running the experiment
yourself.



> The atomicity of SQLITE is guaranteed by SQLITE mechanisms that are
> completely independent of that.
> However, the synchronization pragma got everything to do with the File
> System fwrite mechansim and nothing to do with the SQLITE mechanism for
> atomicity.
>
> I hope that clears things up here too.. :-)
>
> Shuki
>
> On Sun, Jan 27, 2013 at 12:11 PM, Stephan Beal  >wrote:
>
> > On Sun, Jan 27, 2013 at 5:53 PM, Shuki Sasson 
> > wrote:
> >
> > > Answer: The journal is organized in transactions that each of them is
> > > atomic, so all the buffered cache changes for such operation are put
> into
> > > the transaction. Only fully completed transaction are replayed when the
> > > system is recovering from a panic or power loss.
> > >
> >
> > Be careful not to confuse atomic here with atomic in the SQL sense. Any
> > given SQL write operation is made up of many fwrite() (or equivalent)
> > calls, each of which is (in the journaling FS case) atomic in the sense
> of
> > the whole write() call lands stably on disk or does not, but that has
> > absolutely nothing to do with the atomicity of the complete SQL-related
> > write (whether that be a single field of a single record, metadata for a
> > record, or a whole SQL transaction).
> >
> >
> > > well as data it makes all the sense in the world to run with
> > > synchronization = OFF and gain the additional performance benefits.
> > >
> >
> > Good luck with that. :)
> >
> > --
> > - stephan beal
> > http://wanderinghorse.net/home/stephan/
> > http://gplus.to/sgbeal
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> 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] [sqlite-dev] Can I safely use the pragma synchronization = OFF?

[Shuki Sasson]

UFS is not fully journaled FS it jut keeps the metadata.
With fully journaled File System that keeps metadata and data there is
no possibility to loose unsaved data.
Anything that was handed to fwrite and fwrite returned an OK for it is
backed by the journal.
Read the following:
http://en.wikipedia.org/wiki/Journaling_file_system
 Read the following from there:
Physical journals

A *physical journal* logs an advance copy of every block that will later be
written to the main file system. If there is a crash when the main file
system is being written to, the write can simply be replayed to completion
when the file system is next mounted. If there is a crash when the write is
being logged to the journal, the partial write will have a missing or
mismatched checksum and can be ignored at next mount.

Physical journals impose a significant performance penalty because every
changed block must be committed *twice* to storage, but may be acceptable
when *absolute fault protection is
required.*[2]
[edit
]

Hope this clears things up.
Shuki
On Sun, Jan 27, 2013 at 10:14 PM, Pavel Ivanov  wrote:

> OK. I picked this one:
> http://www.freebsd.org/doc/en/articles/gjournal-desktop/article.html.
> It says:
>
> A journaling file system uses a log to record all transactions that
> take place in the file system, and preserves its integrity in the
> event of a system crash or power failure. Although it is still
> possible to lose unsaved changes to files, journaling almost
> completely eliminates the possibility of file system corruption caused
> by an unclean shutdown.
>
> So with UFS you have guarantees that file system won't corrupt. But
> there's absolutely no durability guarantees ("it is possible to lose
> unsaved changes") and I don't see guarantees that SQLite file format
> won't corrupt (FS may be non-corrupt while file data are bogus). While
> I agree the latter is arguable and could be preserved, durability is a
> big reason to use pragma synchronous = normal. Sure, if you don't care
> about it you may not use that, you may as well use WAL journal mode
> (which AFAIR can also lose some of last changed data with pragma
> synchronous = normal). But still your claim that UFS with full
> journaling is a complete replacement for pragma synchronous = normal
> is false.
>
>
> Pavel
>
> On Sun, Jan 27, 2013 at 5:20 PM, Shuki Sasson 
> wrote:
> > Pick up any book about UFS and read about the journal...
> >
> > Shuki
> >
> > On Sun, Jan 27, 2013 at 7:56 PM, Pavel Ivanov 
> wrote:
> >
> >> > So in any file system that supports journaling fwrite is blocked until
> >> all
> >> > metadata and data changes are made to the buffer cache and journal is
> >> > update with the changes.
> >>
> >> Please give us some links where did you get all this info with the
> >> benchmarks please. Because what you try to convince us is that with
> >> journaling FS write() doesn't return until the journal record is
> >> guaranteed to physically make it to disk. First of all I don't see
> >> what's the benefit of that compared to direct writing to disk not
> >> using write-back cache. And second do you realize that in this case
> >> you can't make more than 30-50 journal records per second? Do you
> >> really believe that for good OS performance it's enough to make less
> >> than 30 calls to write() per second (on any file, not on each file)? I
> >> won't believe that until I see data and benchmarks from reliable
> >> sources.
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Sun, Jan 27, 2013 at 8:53 AM, Shuki Sasson 
> >> wrote:
> >> > Hi Pavel, thanks a lot for your answer. Assuming xWrite is using
> fwrite
> >> > here is what is going on the File System:
> >> > In a legacy UNIX File System (UFS) the journaling protects only the
> >> > metadata (inode structure directory block indirect block etc..) but
> not
> >> the
> >> > data itself.
> >> > In more modern File Systems (usually one that are enterprise based
> like
> >> EMC
> >> > OneFS on the Isilon product) both data and meta data are journaled.
> >> >
> >> > How journaling works?
> >> > The File System has a cache of the File System blocks it deals with
> (both
> >> > metadata and data) when changes are made to a buffer cached block it
> is
> >> > made to the memory only and the set of changes is save to the journal
> >> > persistently. When the persistent journal is on disk than saving both
> >> data
> >> > and meta data changes
> >> > takes too long and and only meta data changes are journaled. If the
> >> journal
> >> > is placed on NVRAM then it is fast enough to save both data and
> metadata
> >> > changes to the journal.
> >> > So in any file system that supports journaling fwrite is blocked until
> >> all
> >> > metadata and data changes are made to the buffer cache