[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Yannick Duchêne]

On Thu, 28 Jan 2016 14:55:28 +
Simon Slavin  wrote:

> 
> On 28 Jan 2016, at 1:38pm, Bernard McNeill  wrote:
> 
> > ===
> > Like the user reading ?saving OK? and throwing away the
> > Post-It with the original information
> > ===
> > 
> > This is exactly my concern.
> > The user throwing away the Post-It is entirely reasonable if he sees a
> > message like that.
> > 
> > Do you happen to know if Linux/Debian (which I think uses a journalling
> > filesystem) carries this risk?
> 
> The problem is not at the software level.  Various operating systems and file 
> systems are correctly programmed with regard to waiting for write commands to 
> complete.  I don't know specifically about Debian but Linux has a good 
> reputation for such things, and anyone who bothers to write a journalling 
> file system would understand how to do things properly.
> 
> The problem is at the hardware level.  Standard disk drives (including their 
> motherboard if they have one, and their firmware) are designed for speed, not 
> integrity.  The assumption is that you will be using them to play games or 
> write your CV in Word, not to keep vital data.  So they are set up, using 
> their default jumper positions, to lie.  In order to keep their computer 
> running as fast as possible, instead of
> 
> 1) receive write command
> 2) perform write command
> 3) read that bit of disk to confirm the change
> 4) if not, bring SMART system into play and try writing it somewhere else
> 5) if succeed, tell the computer "I wrote that and it worked."
> 6) otherwise tell the computer "I wrote that and it failed."
> 
> they do this
> 
> 1) receive write command
> 2) tell the computer "I wrote that and it worked."
> 3) perform write command
> 4) read that bit of disk to confirm the change
> 5) if not, bring SMART system into play and try writing it somewhere else

Coincidence. I just had a funny incident; may be it's related.

I just modified a program so that it create fours triggers in a database. I ran 
the program, then got an error from APSW (the program uses Python) complaining 
about an I/O or disk error. This frightened me a bit, but I though this may be 
due to SQLiteBrowser opened on the same DB, which I forget to close and I 
closed just after I started the program, which was subject to the I/O issue. I 
open the DB and can see only two of the four triggers, two missing. So I delete 
the DB and regenerate it three times, without error messages, but still two 
triggers missing. That's only at the fourth repetition the four triggers was 
all there.

The SMART data indicates zero reallocated sectors.

In the SMART utility, I noticed there is an hardware cache, which I disabled, 
in case of and thinking about ?lying devices? I remembered from this message.

This is frightening to me, as I got an error message the first time only, but 
not the other times while something wrong seems to have happened too. Also, I 
could notice something was missing in the DB (even when generated without an 
error notification), because it was about something I was precisely looking at, 
at that moment; if it had been about some rows missing in any table, I would 
have not noticed it.

Hardware failure? OS failure? Software failure? Can't tell for sure??



-- 
Yannick Duch?ne

[sqlite] Table entries disappearing for unknown reason.

[Clemens Ladisch]

Vin?cius da Silva wrote:
> Each thread access the SQLiteManager array m_dbs, indexed by its unique
> index provided by openmp. Since SQLiteManager opens a connection in its
> init() method each thread has a private connection. The tests are passing
> when I set the parallel loops for one thread only.

Try executing "BEGIN EXCLUSIVE" through two separate connections to the
same DB file.  Does it work?

If yes, which file system are you using?


Regards,
Clemens

[sqlite] Table entries disappearing for unknown reason.

[Hick Gunter]

Does the error still occur if you run with only one thread? Does each thread 
have ist own connection or are you sharing a single connection across multiple 
threads?

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von 
Vin?cius da Silva
Gesendet: Dienstag, 02. Februar 2016 22:08
An: SQLite mailing list
Betreff: Re: [sqlite] Table entries disappearing for unknown reason.

The error occur after commenting the SQLite setup pragmas.  All threads access 
the same database file and the same table. I'm using an ext4 file system with 
Ubuntu.

2016-02-02 17:58 GMT-02:00 Clemens Ladisch :

> Vin?cius da Silva wrote:
> > after commenting the pragmas,
>
> Which ones?  omp or SQLite?
>
> > SQLiteManager is throwing
> > an exception because stepping the statement in endTransaction() is
> > returning SQLITE_IOERR_DELETE_NOENT
> >  in the parallel
> loop.
>
> Do your threads access the same database file?
>
> What file system are you using?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



--
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ  
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Table entries disappearing for unknown reason.

[Clemens Ladisch]

Vin?cius da Silva wrote:
> after commenting the pragmas,

Which ones?  omp or SQLite?

> SQLiteManager is throwing
> an exception because stepping the statement in endTransaction() is
> returning SQLITE_IOERR_DELETE_NOENT
>  in the parallel loop.

Do your threads access the same database file?

What file system are you using?


Regards,
Clemens

[sqlite] IS a SQLite db of small size as good as reliable cache?

[James K. Lowden]

On Mon, 01 Feb 2016 06:39:05 -0700
"Keith Medcalf"  wrote:

> OS/2 had IBM cache technology in it which worked properly.  

I remember OS/2.  I remember that, like VMS, you could back up the
whole OS to ... well, floppies, I suppose, and later restore them to
brand new drive, with nothing else required.  I couldn't believe my
eyes when the equivalent "system restore" process on NT started with 

1.  Install the OS.

Twenty-five years on, that's still SOP on NT, except that nowadays no
one expects to restore a backup anyway!  

But IIRC, NTFS was a Microsoft invention, not connected to OS/2, and
borrowed quite heaviily from VMS, because David Cutler.  So I'm
inclined toward your low-wattage theory, because hardly anything Cutler
originally provided remains unsmudged.  

--jkl

[sqlite] Is the first column of a composite primary key, special?

[James K. Lowden]

On Tue, 2 Feb 2016 16:19:07 +0100
Yannick Duch?ne  wrote:

> There are also representations. Sometimes there is not really a
> value, just an identity which is the only thing offering
> sense/meaning, and what may be erroneously seen as a value is rather
> a representation. 

Representation is all.  

The database -- the whole thing, front to back -- is just a model, an
approximation of the real world.  As designer, you decide how to model
that world, the "enterprise of interest".  You decide what "entities"
there are, what properties they have, how they are known (identified).

The DBMS knows nothing of what is being modelled.  It's "just" a logic
system. That logic operates on values. It doesn't distinguish between
kinds of values, between natural and surrogate keys.  That sort of thing
contributes to the understandability (and hence utility) of the model,
but they're extralogical: outside the theory.  

> All of this, using ID in place of *some* values???however at the cost
> of more verbose queries

Back in the real world -- the computing world, where there's an actual
system implementing the math -- yes, some things are faster than
others, and some allowances have to be made for the limitations of the
implementation and the computer.  :-)  

--jkl

[sqlite] Customizing the location of the .sqlite_history

[James K. Lowden]

On Sun, 31 Jan 2016 21:46:18 -0800
Jes Slow  wrote:

> Many applications do this by allowing the user to set an environment
> variable to customize the location, altho personally I would prefer
> another way since environment variables are also global. 

Global?  Environment variables are per-process, and changeable by the
process with putenv(3).  

--jkl

[sqlite] Documentation comment: VFS

[James K. Lowden]

On Mon, 1 Feb 2016 14:24:28 +0200
R Smith  wrote:

> > does it say what "VFS" stands for.  Please don't tell me...//
> 
> I'm going to ignore the request and tell you anyway: It clearly
> stands for "Variably F-Syncing Software".

Oh, come now.  In the tradition of RTMF, surely it's 

Very Fine Sofware

--jkl

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

Using BEGIN EXCLUSIVE TRANSACTION in place of BEGIN TRANSACTION does not
change the result: SQLITE_IOERR_DELETE_NOENT
 is being returned when
stepping the END TRANSACTION statement. I'm using ext4 with Ubuntu.

2016-02-02 19:40 GMT-02:00 Clemens Ladisch :

> Vin?cius da Silva wrote:
> > Each thread access the SQLiteManager array m_dbs, indexed by its unique
> > index provided by openmp. Since SQLiteManager opens a connection in its
> > init() method each thread has a private connection. The tests are passing
> > when I set the parallel loops for one thread only.
>
> Try executing "BEGIN EXCLUSIVE" through two separate connections to the
> same DB file.  Does it work?
>
> If yes, which file system are you using?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ 

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

Each thread access the SQLiteManager array m_dbs, indexed by its unique
index provided by openmp. Since SQLiteManager opens a connection in its
init() method each thread has a private connection. The tests are passing
when I set the parallel loops for one thread only.

2016-02-02 19:27 GMT-02:00 Hick Gunter :

> Does the error still occur if you run with only one thread? Does each
> thread have ist own connection or are you sharing a single connection
> across multiple threads?
>
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Vin?cius da
> Silva
> Gesendet: Dienstag, 02. Februar 2016 22:08
> An: SQLite mailing list
> Betreff: Re: [sqlite] Table entries disappearing for unknown reason.
>
> The error occur after commenting the SQLite setup pragmas.  All threads
> access the same database file and the same table. I'm using an ext4 file
> system with Ubuntu.
>
> 2016-02-02 17:58 GMT-02:00 Clemens Ladisch :
>
> > Vin?cius da Silva wrote:
> > > after commenting the pragmas,
> >
> > Which ones?  omp or SQLite?
> >
> > > SQLiteManager is throwing
> > > an exception because stepping the statement in endTransaction() is
> > > returning SQLITE_IOERR_DELETE_NOENT
> > >  in the parallel
> > loop.
> >
> > Do your threads access the same database file?
> >
> > What file system are you using?
> >
> >
> > Regards,
> > Clemens
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Best Regards,
>
> Vin?cius da Silva ,
> Ph. D. Student.
> PESC  - UFRJ 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ 

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[James K. Lowden]

On Sun, 31 Jan 2016 20:27:56 -0700
Scott Robison  wrote:

> On Sun, Jan 31, 2016 at 7:35 PM, Rowan Worth  wrote:
> 
> > On 31 January 2016 at 03:56, James K. Lowden
> >  wrote:
> >
> > > Surely SQLite does both -- fsync on file and directory -- as part
> > > of a commit. That's not in doubt, is it?
> > >
> >
> > No, SQLite does not. On COMMIT it fsyncs the database file and
> > unlinks the journal[1], but does not fsync the directory. This is
> > demonstrated by the logs in Stefan's initial post, and indeed the
> > purpose of his patch was to introduce an fsync on the directory
> > after unlinking the journal.
> >
> 
> Perhaps there could be a "paranoid" journal mode, where it first
> zeros out the header ala persist, then truncates the file, then
> deletes the file.

Well, I would say there are four choices:

1.  Something like you suggest, but just mark the  "committed
fact" in  the log file.  It would be enough to write "we're done here"
at the end of the file, followed by fsync, followed by unlink.  Then if
the log file is found (undeleted) after a crash, the information in the
file suffices.  

2.  Call fsync after unlink.  

3.  Call fsync after unlink if directory's extended attributes don't
include FS_DIRSYNC_FL *or* the filesystem was mounted with MS_DIRSYNC.  

4.  For systems that support it (Linux 2.6+) just go ahead and set
FS_DIRSYNC_FL.  

The 3rd option underscores the pitfalls of directory synchronization
under Linux.  NetBSD for example has neither option; afaik all
directory operations are synchronous.  (Posix doesn't mention it.)  

Personally, I like to keep things simple.  Update & fsync the log file
one last time before deleting it.  Works on every system, and is
impervious to future permutations to directory synchronization.  

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

The error occur after commenting the SQLite setup pragmas.  All threads
access the same database file and the same table. I'm using an ext4 file
system with Ubuntu.

2016-02-02 17:58 GMT-02:00 Clemens Ladisch :

> Vin?cius da Silva wrote:
> > after commenting the pragmas,
>
> Which ones?  omp or SQLite?
>
> > SQLiteManager is throwing
> > an exception because stepping the statement in endTransaction() is
> > returning SQLITE_IOERR_DELETE_NOENT
> >  in the parallel
> loop.
>
> Do your threads access the same database file?
>
> What file system are you using?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ 

[sqlite] Pointer to building SQLITE4

[Simon Slavin]

On 2 Feb 2016, at 11:43am, Tanish Bansal  wrote:

> SQLITE4

Please note that SQLite4 is experimental at this time.  It's a way for the 
developers to test new features or techniques that may one day be used in a 
stable release.  It should not be used for any program you need to work 
correctly.

Simon.

[sqlite] Pointer to building SQLITE4

[Tanish Bansal]

Hi Sir,



  I want to build SQLITE4, but i am facing some errors when i follow your READ 
ME guidance Steps.

  May be i am Missing some steps here .. 



These are the steps i followed to build sqlite4

1. Downloaded sqlite.tar.gz from ( https://www.sqlite.org/src4/tree?ci=trunk )

2. tar xzf sqlite.tar.gz

3. ./autogen.sh

4. configure

5. make



 When i run make that time i got lot of errors there, PFA the errors that I got.



 how do i fix these errors?



 Thank you.











 TANISH

[sqlite] Is the first column of a composite primary key, special?

[Yannick Duchêne]

On Sun, 31 Jan 2016 18:15:48 -0500
"James K. Lowden"  wrote:

> 
> The relational model, as you well know, doesn't describe
> implementation.  It's math.  It says what relations are, and how
> they're manipulated.  One data type, and an algebra closed over that
> domain.  Math doesn't have pointers or duplication or corruption; those
> are computer concepts, and as such are entirely outside the model.  

I feel better to read this, as I had a doubt on the assertion. There is even 
another way to comment, bringing pointer interpretation into the model: an 
identity, which is fine in a relation model, is a value of some type, to which 
equality applies; a pointer is an identity, although an identity is not always 
a pointer, as a pointer is additionally a kind of array index which implicitly 
implies a relation to one. So ?pointing to? is a reasonable interpretation of 
some relations, when it reads from many to one (which may be for both ways, one 
way only, or not at all). An identity may still sometimes be interpreted as a 
pointer, with only the dereferencing method differing: either relation or array 
index.

That said, I tried to have some meditations on whether or not I should use ID 
in place of values or not, while testing it in practice.

Here is how I see it (to not say all)?? identities and general values, are not 
the same things, as while same identity implies same value, same value does not 
always implies same identity. The latter, to be assumed, needs to be asserted. 
There are also representations. Sometimes there is not really a value, just an 
identity which is the only thing offering sense/meaning, and what may be 
erroneously seen as a value is rather a representation. For the latter case, 
this is better to deal with the identity rather than with the representation 
interpreted as a ?value?, because the representation may vary, so it's cleaner 
to use the identity rather than the ?value?, to make senses. To come back to 
values, when the only thing which locally matters with a value is equality, it 
is valid to locally assert the value is the identity; this may be handy with 
composite values (save complexity folding a complex value) or often repeated 
values (*may* save storage). But this comes with an issue, at least one SQL 
construct prevents from referring to the value corresponding to an identity 
(when not just equality is needed): within indexes. Ex. table T has column "a" 
(an ID) and "b" (an associated value), table U has a column "c", where "c" 
references column "a" from T, then there is no (or I know no) way to create an 
index on U(c) ordered by the associated value "b" from T.


That's abstract, but the practical result is there: the request grouping on 
what was a second column, is now *exactly* as fast as the request grouping on 
what was the first column (there is no more technical differences between the 
two queries), and the DB size is two third of its size before this refactoring. 
All of this, using ID in place of *some* values???however at the cost of more 
verbose queries (more verbose, but not untraceable, this is just tedious).


-- 
Yannick Duch?ne

[sqlite] Table entries disappearing for unknown reason.

[Vinícius da Silva]

Using only omp_get_thread_num() in the parallel loop do not change the
results. However, after commenting the pragmas, SQLiteManager is throwing
an exception because stepping the statement in endTransaction() is
returning SQLITE_IOERR_DELETE_NOENT
 in the parallel loop.
This behavior is reproduced in every multithreaded code that uses
SQLiteManager (the automated tests for SQLiteManager that exercise database
connections in multithreaded code are returning the same error).

2016-02-02 8:46 GMT-02:00 Clemens Ladisch :

> Dominique Devienne wrote:
> > On Tue, Feb 2, 2016 at 10:32 AM, Clemens Ladisch 
> wrote:
> >
> >> Vin?cius da Silva wrote:
> >>> #pragma omp parallel for
> >>> for( int i = 0; i < dispatchedThreads; ++i )
> >>
> >> Does OMP guarantee that there is exactly one thread for each
> >> loop iteration?
> >
> > Yes, OpenMP will execute each *iteration* in a single thread.
> > The set of iterations will be spread on 1 or more thread, depending
> > on OMP_NUM_THREADS.
>
> If i and omp_get_thread_num() are not the same, the code breaks.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best Regards,

Vin?cius da Silva ,
Ph. D. Student.
PESC  - UFRJ 

[sqlite] “btreeInitPage() returns error code 11”

[Richard Hipp]

On 2/2/16, Felipe Gasper  wrote:
>   I?ve got a corrupt SQLite database, and I?m trying to figure out what
> produced the corruption.
>
>   A ?pragma integrity_check? on it yielded the following. Does this trip
> anyone?s ?danger sense? of what may have happened?
>

With few exceptions, analysis of a corrupt database does not normally
help to determine what went wrong.  A better approach to avoiding
"danger", we have found, is to read and understand
https://www.sqlite.org/howtocorrupt.html


> *** in database main ***
> Page 10628: btreeInitPage() returns error code 11

This indicates that the page header is so badly corrupted that SQLite
cannot interpret this page at all.  One possible reason:  page 10628
has been zeroed.  Can you look at a hex dump of that page?  (Remember
that SQLite numbers pages beginning with 1, so the start of the page
is pgsz*10627 where pgsz is the page size.)
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] “btreeInitPage() returns error code 11”

[Felipe Gasper]

Hi all,

I?ve got a corrupt SQLite database, and I?m trying to figure out what 
produced the corruption.

A ?pragma integrity_check? on it yielded the following. Does this trip 
anyone?s ?danger sense? of what may have happened?

Thank you!

-FG


SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma integrity_check;
*** in database main ***
Page 10628: btreeInitPage() returns error code 11
On tree page 8410 cell 103: Child page depth differs
Page 10656: btreeInitPage() returns error code 11
Page 10686: btreeInitPage() returns error code 11
Page 10718: btreeInitPage() returns error code 11
Page 10641: btreeInitPage() returns error code 11
On tree page 10487 cell 32: Child page depth differs
Page 10683: btreeInitPage() returns error code 11
On page 10487 at right child: invalid page number 10732
Page 10642: btreeInitPage() returns error code 11
On tree page 10489 cell 32: Child page depth differs
Page 10684: btreeInitPage() returns error code 11
On page 10489 at right child: invalid page number 10733
Page 10643: btreeInitPage() returns error code 11
On tree page 10491 cell 32: Child page depth differs
Page 10685: btreeInitPage() returns error code 11
On page 10491 at right child: invalid page number 10734
Page 10640: btreeInitPage() returns error code 11
On tree page 6039 cell 19: Child page depth differs
Page 10691: btreeInitPage() returns error code 11
On tree page 6039 cell 21: Child page depth differs
Page 10692: btreeInitPage() returns error code 11
On tree page 5411 cell 36: Child page depth differs
On tree page 5411 cell 37: Child page depth differs
Page 10606: btreeInitPage() returns error code 11
On tree page 10314 cell 82: Child page depth differs
Page 10607: btreeInitPage() returns error code 11
Page 10614: btreeInitPage() returns error code 11
Page 10616: btreeInitPage() returns error code 11
Page 10621: btreeInitPage() returns error code 11
Page 10627: btreeInitPage() returns error code 11
Page 10629: btreeInitPage() returns error code 11
Page 10634: btreeInitPage() returns error code 11
Page 10639: btreeInitPage() returns error code 11
Page 10645: btreeInitPage() returns error code 11
Page 10649: btreeInitPage() returns error code 11
Page 10652: btreeInitPage() returns error code 11
Page 10657: btreeInitPage() returns error code 11
Page 10662: btreeInitPage() returns error code 11
Page 10664: btreeInitPage() returns error code 11
Page 10669: btreeInitPage() returns error code 11
Page 10673: btreeInitPage() returns error code 11
Page 10675: btreeInitPage() returns error code 11
Page 10680: btreeInitPage() returns error code 11
Page 10690: btreeInitPage() returns error code 11
Page 10694: btreeInitPage() returns error code 11
Page 10699: btreeInitPage() returns error code 11
Page 10701: btreeInitPage() returns error code 11
Page 10705: btreeInitPage() returns error code 11
Page 10710: btreeInitPage() returns error code 11
Page 10712: btreeInitPage() returns error code 11
Page 10716: btreeInitPage() returns error code 11
Page 10723: btreeInitPage() returns error code 11
On tree page 10314 cell 110: invalid page number 10725
On page 10314 at right child: invalid page number 10730
Page 10611: btreeInitPage() returns error code 11
On tree page 10409 cell 39: Child page depth differs
Page 10617: btreeInitPage() returns error code 11
Page 10623: btreeInitPage() returns error code 11
Page 10630: btreeInitPage() returns error code 11
Page 10636: btreeInitPage() returns error code 11
Page 10646: btreeInitPage() returns error code 11
Page 10653: btreeInitPage() returns error code 11
Page 10658: btreeInitPage() returns error code 11
Page 10665: btreeInitPage() returns error code 11
Page 10670: btreeInitPage() returns error code 11
Page 10677: btreeInitPage() returns error code 11
Page 10687: btreeInitPage() returns error code 11
Page 10695: btreeInitPage() returns error code 11
Page 10702: btreeInitPage() returns error code 11
Page 10707: btreeInitPage() returns error code 11
Page 10713: btreeInitPage() returns error code 11
Page 10720: btreeInitPage() returns error code 11
On page 10409 at right child: invalid page number 10726
Page 10612: btreeInitPage() returns error code 11
On tree page 10411 cell 39: Child page depth differs
Page 10618: btreeInitPage() returns error code 11
Page 10624: btreeInitPage() returns error code 11
Page 10631: btreeInitPage() returns error code 11
Page 10637: btreeInitPage() returns error code 11
Page 10647: btreeInitPage() returns error code 11
Page 10654: btreeInitPage() returns error code 11
Page 10659: btreeInitPage() returns error code 11
Page 10666: btreeInitPage() returns error code 11
Page 10671: btreeInitPage() returns error code 11
Page 10678: btreeInitPage() returns error code 11
Page 10688: btreeInitPage() returns error code 11
Page 10696: btreeInitPage() returns error code 11
Page 10703: btreeInitPage() returns error code 11
Page 10708: btreeInitPage() returns error co

[sqlite] Table entries disappearing for unknown reason.

[Clemens Ladisch]

Dominique Devienne wrote:
> On Tue, Feb 2, 2016 at 10:32 AM, Clemens Ladisch  
> wrote:
>
>> Vin?cius da Silva wrote:
>>> #pragma omp parallel for
>>> for( int i = 0; i < dispatchedThreads; ++i )
>>
>> Does OMP guarantee that there is exactly one thread for each
>> loop iteration?
>
> Yes, OpenMP will execute each *iteration* in a single thread.
> The set of iterations will be spread on 1 or more thread, depending
> on OMP_NUM_THREADS.

If i and omp_get_thread_num() are not the same, the code breaks.


Regards,
Clemens

[sqlite] Table entries disappearing for unknown reason.

[Dominique Devienne]

On Tue, Feb 2, 2016 at 10:32 AM, Clemens Ladisch  wrote:

> Vin?cius da Silva wrote:
> > #pragma omp parallel for
> > for( int i = 0; i < dispatchedThreads; ++i )
>
> Does OMP guarantee that there is exactly one thread for each
> loop iteration?
>

Yes, OpenMP will execute each *iteration* in a single thread.
The set of iterations will be spread on 1 or more thread, depending
on OMP_NUM_THREADS.
Default is to evenly divide the [0, dispatchedThreads) range into
per-thread ranges, and run each iteration-range on a single thread. --DD

[sqlite] Table entries disappearing for unknown reason.

[Clemens Ladisch]

Vin?cius da Silva wrote:
> #pragma omp parallel for
> for( int i = 0; i < dispatchedThreads; ++i )

Does OMP guarantee that there is exactly one thread for each loop
iteration?

> sqlite3_exec( m_db, "PRAGMA synchronous = OFF", NULL, NULL, NULL );
> sqlite3_exec( m_db, "PRAGMA journal_mode = OFF", NULL, NULL, NULL );

Does it work without those?


Regards,
Clemens

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Rowan Worth]

On 2 February 2016 at 08:22, Stephen Chrzanowski 
wrote:

> On Mon, Feb 1, 2016 at 11:20 AM, Rowan Worth  wrote:

> As I indicated in the last paragraph of my mail, I'm not in favour of
> > fsync-directory-on-commit in the general case. But that's because I worry
> > about the performance impact, not because SQLite is doing its best and at
> > the mercy of the OS. I can't remember who in this thread suggested it but
> > fsync-directory-on-close seemed like a great compromise.
>
> *ONLY* if configurable, and *ONLY* if not a default.  Most of my apps are
> setup at highest risk because the data they contain doesn't matter and I
> want performance vs safety.


Performance over safety is exactly the reason we are here in the first
place. If performance wasn't a factor the OS would go straight to the disk
platter in response to a write() call.

But if we accept reality, we know that we have no such guarantee the data
we just wrote to a file is on the platter before we call fsync(). And
secondly, we have no guarantee that the directory structure we just changed
(eg. by unlinking the journal) is on the platter before we call fsync() on
the directory.

The OS/hardware can still lie, sure. But sqlite hasn't given it a chance to
tell the truth since doesn't fsync the directory - this is what I meant
when I said "hasn't even asked the question." SQLite doesn't ask for the
directory update to hit the platter, and then proceeds execution as if it
has.

To reiterate, I don't have a problem with sqlite's behaviour at all. I
think it's perfectly acceptable. But I think it deserves to be acknowledged
and understood for what it is; a pragmatic decision by sqlite to improve
performance in the general case at the "expense" of a
not-strictly-necessary rollback in an obscure failure scenario. It's not
fair to meet it with the double standard of "fsyncing the directory every
COMMIT would hurt general-case performance too much" and "the OS is
responsible for ensuring data gets to disk", because that essentially asks
the OS to fsync directory after every *i/o operation*, whether asked to or
not. Which would hurt general-case performance much more!

I'm right there behind you that it shouldn't be forced on users of sqlite :)

-Rowan

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Olivier Mascia]

> Le 2 f?vr. 2016 ? 03:25, Rowan Worth  a ?crit :
> 
> To reiterate, I don't have a problem with sqlite's behaviour at all. I
> think it's perfectly acceptable. But I think it deserves to be acknowledged
> and understood for what it is; a pragmatic decision by sqlite to improve
> performance in the general case at the "expense" of a
> not-strictly-necessary rollback in an obscure failure scenario. It's not
> fair to meet it with the double standard of "fsyncing the directory every
> COMMIT would hurt general-case performance too much" and "the OS is
> responsible for ensuring data gets to disk", because that essentially asks
> the OS to fsync directory after every *i/o operation*, whether asked to or
> not. Which would hurt general-case performance much more!
> 
> I'm right there behind you that it shouldn't be forced on users of sqlite :)

Indeed, and those users insisting on using journal_mode=DELETE while living 
with a filesystem requiring an fsync of the directory after create/remove 
files, could also fsync() the directory themselves after the SQLite successful 
commit, before considering it "done". Or they could use one of the alternates 
modes which do not delete the journal, but either reset it (TRUNCATE) or clear 
it (PERSIST). Or even consider WAL mode which has very interesting properties, 
in itself.

https://www.sqlite.org/pragma.html#pragma_journal_mode

Compared to the amount of (generally bad) sample code seen on the internet 
(outside of sqlite.org) where people seem to think it is ordinary business to 
do PRAGMA synchronous=OFF or even PRAGMA journal_mode=OFF, I live happily with 
this journal_mode=DELETE (which I happen to not use). :)

--
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160202/2d3d45f0/attachment.pgp>

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Gabor Grothendieck]

> It is a standard feature of pysqlite2 (the sqlite3 library shipped with 
> Python).  I am quite sure you can read the documentation just as well as I 
> can copy and paste it.  In short, you can use anything you like as the data 
> type in sqlite.  You could specify that some column contains:
>
> create table Developers
> (
>   count sillyGooses integer not null
> );
>
> You have now defined this to be of type sillyGooses integer.  It will have 
> affinity integer.  WHen you read the column metadata you can find out that 
> count is of type sillygooses and multiply the returned value count by Pi/e if 
> that is the conversion factor required.
>
> THe person writing the interface can even do this magically for you -- just 
> like pysqlite / sqlite3 python libraries do.
>

It's hard to tell what you are suggesting without the full example I
asked for but given that

1. you are showing a create statement and
2. refering to the "person writing the interface"

I assume  you are suggesting that the user define the structure of the
output table sent from sqlite and do that either on the python or
sqlite side but that was what we were trying to avoid.

Of course it's possible (and easy to do in R too) if the user is
required to specify the output types on the sqlite or R side but in R
you don't have to -- it's all done automatically for you.  In the full
R example I showed there is no create statement or other statements to
define an the "interface" that I did not show yet it works in
databases that support date types but not in sqlite (unless you rely
on heuristics that the sqldf package provides).

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

[Keith Medcalf]

> > There is no impedence mismatch.  Simply inadequate wattage by the
> person(s) solving the problem.  As I said, this problem has been solved
> with SQLite and Python for a decade.  So I would suggest the problem is
> that the wattage was so low, the lights were completely out.

> The impedence in the example is that on the sqlite side the type is
> numeric and on the R side it is of Date class.  It is impossible to
> know which sqlite numeric types are intended to represent dates and
> which are intended to represent numbers so when they are sent back to
> R there is no automatic assignment to Date class.  Either the
> programmer has to specify it (which is tedious) or else one has to
> rely on heuristics such as assuming that any output field having the
> same name as input field must also have the same type (but such
> heuristics do not cover all cases).
> 
> If you believe that python can know that a date is being returned from
> sqlite without the programmer specifying it even though sqlite has no
> date class, I suggest providing a self contained reproducible example
> of python code to illustrate it as I did with R.

It is a standard feature of pysqlite2 (the sqlite3 library shipped with 
Python).  I am quite sure you can read the documentation just as well as I can 
copy and paste it.  In short, you can use anything you like as the data type in 
sqlite.  You could specify that some column contains:

create table Developers
(
  count sillyGooses integer not null
);

You have now defined this to be of type sillyGooses integer.  It will have 
affinity integer.  WHen you read the column metadata you can find out that 
count is of type sillygooses and multiply the returned value count by Pi/e if 
that is the conversion factor required.

THe person writing the interface can even do this magically for you -- just 
like pysqlite / sqlite3 python libraries do.

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Rowan Worth]

Hi Stephen,

On 1 February 2016 at 21:45, Stephen Chrzanowski 
wrote:
>
> SQLite is nothing more than part of a program run by the OS.  It completely
> relies on whatever the OS tells it.  If the OS tells it that things are OK,
> then that is all that can be done.  SQLite can't take on the responsibility
> of making sure that my taxes are done right, it won't ever wash my car (I'd
> be afraid the T would scratch the paint anyways) and it absolutely cannot
> defend against the lies an OS gives it.


Of course! SQLite is a remarkably reliable bit of software and I hold it in
high regard, but as you say software has its limits. I'm not one to expect
more of SQLite then it can reasonably provide.

Going back to the OS, I've seen instance, in both Windows and Linux based
> platform (RH and Debian) where file sizes do not change even though streams
> of data are being pumped into that particular file.


Stands to reason on linux if the directory hasn't been synced to disk yet.
The file's dirent is stored in a completely independent extent.


> I've seen situations
> where files have been deleted in Linux (Not allowed in Windows) and data
> still gets dumped into the now deleted file.


?
This is not an error, this is standard POSIX file semantics - if you have a
file descriptor referring to a file which gets deleted from disk, the file
descriptor is guaranteed to remain valid until it is closed. NFS even goes
to some lengths to make this work.

Is it the programs fault that
> the file is physically deleted but the program isn't aware?  Is it supposed
> to check every single time it writes a byte to see if the file exists, if
> the partition is healthy, and the root file system passes a FSCK at every
> commit?
>

:)


> SQLite doesn't know or care what sector the bytes
> live at on the drive physically.  It doesn't know about fragmentation, it
> doesn't know what size the clusters are, or anything that is important for
> file storage.


But SQLite *does* know how to interface with the file system. It's all very
well to say the OS/hardware sometimes lies, but in this specific instance
SQLite hasn't even asked the question.

As I indicated in the last paragraph of my mail, I'm not in favour of
fsync-directory-on-commit in the general case. But that's because I worry
about the performance impact, not because SQLite is doing its best and at
the mercy of the OS. I can't remember who in this thread suggested it but
fsync-directory-on-close seemed like a great compromise.

-Rowan