Re: [sqlite] Problem with savepoints

[Eduardo Morras]

On Wed, 6 Jul 2016 18:10:34 +0200
Chris Brody  wrote:

> >
> > Just for my information, what is the purpose of this temporary
> > file?  I see
> >> that -journal file is always stored to disk.
> >>
> >
> > It's a statement journal:
> >
> >   https://www.sqlite.org/tempfiles.html#stmtjrnl
> >
> > Recent changes mean that the first 64KiB of a statement journal are
> > always stored in main-memory, and the temp file only created after
> > the file grows larger than 64KiB. Which is probably why you did not
> > see the problem immediately.
> 
> 
> Personally I wish SQLite would fail upon open or perhaps on first
> write if it cannot write the temporary file. I think this would make
> it easier for programmers to detect and deal with this kind of issue.
> Maybe make it an explicit compile-time option to wait until the
> temporary file is larger than 64KiB before opening the temp file.
> Just a thought. 

This is set up in src/global.c line 172 on 3.13.0 version

   171  #ifndef SQLITE_STMTJRNL_SPILL 
   172  # define SQLITE_STMTJRNL_SPILL (64*1024)
   173  #endif

You can predefine it (define before include sqlite3.h) with the value in bytes 
you want, or set it to -1 to always store them in memory.



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

Re: [sqlite] Problem with savepoints

[Dan Kennedy]

On 07/06/2016 11:10 PM, Chris Brody wrote:

Just for my information, what is the purpose of this temporary file?  I see

that -journal file is always stored to disk.


It's a statement journal:

   https://www.sqlite.org/tempfiles.html#stmtjrnl

Recent changes mean that the first 64KiB of a statement journal are always
stored in main-memory, and the temp file only created after the file grows
larger than 64KiB. Which is probably why you did not see the problem
immediately.


Personally I wish SQLite would fail upon open or perhaps on first write if
it cannot write the temporary file. I think this would make it easier for
programmers to detect and deal with this kind of issue. Maybe make it an
explicit compile-time option to wait until the temporary file is larger
than 64KiB before opening the temp file. Just a thought.


Right now it works the other way around - you can explicitly build or 
configure SQLite to create statement/savepoint journals as soon as they 
are required:


  https://www.sqlite.org/compile.html#stmtjrnl_spill

Dan.

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

Re: [sqlite] Problem with savepoints

[Chris Brody]

>
> Just for my information, what is the purpose of this temporary file?  I see
>> that -journal file is always stored to disk.
>>
>
> It's a statement journal:
>
>   https://www.sqlite.org/tempfiles.html#stmtjrnl
>
> Recent changes mean that the first 64KiB of a statement journal are always
> stored in main-memory, and the temp file only created after the file grows
> larger than 64KiB. Which is probably why you did not see the problem
> immediately.


Personally I wish SQLite would fail upon open or perhaps on first write if
it cannot write the temporary file. I think this would make it easier for
programmers to detect and deal with this kind of issue. Maybe make it an
explicit compile-time option to wait until the temporary file is larger
than 64KiB before opening the temp file. Just a thought.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with savepoints

[Dan Kennedy]

On 07/06/2016 10:52 PM, Jeff Archer wrote:

On Wed, Jul 6, 2016 at 10:46 AM, Dan Kennedy  wrote:


On 07/06/2016 09:09 PM, Jeff Archer wrote:


Hi All,
I am a long time SQLite user but have generally used it from C++ in the
past.  In this project however, I am attempting to make a JDBC wrapper and
use from Android a newer version of SQLite (3.12.2) than that provided by
Android 4.2.2 (3.7.11).


Group 8 however can only do 26 successful inserts and on the 27th

sqlite3_step() is returning SQLITE_CANTOPEN (14).



​​
SQLite is failing to create a temporary file, as there is nowhere
convenient to do this on Android. The usual fix is to build SQLite to store
temp files in memory:

   -DSQLITE_TEMP_STORE=3

   https://www.sqlite.org/compile.html#temp_store

Or execute
​​
"PRAGMA temp_store = memory" after opening your db connection.


​Dan, many thanks.  This does indeed solve my problem.

Just for my information, what is the purpose of this temporary file?  I see
that -journal file is always stored to disk.


It's a statement journal:

  https://www.sqlite.org/tempfiles.html#stmtjrnl

Recent changes mean that the first 64KiB of a statement journal are 
always stored in main-memory, and the temp file only created after the 
file grows larger than 64KiB. Which is probably why you did not see the 
problem immediately.


Dan.


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

Re: [sqlite] Problem with savepoints

[Jeff Archer]

On Wed, Jul 6, 2016 at 10:46 AM, Dan Kennedy  wrote:

> On 07/06/2016 09:09 PM, Jeff Archer wrote:
>
>> Hi All,
>> I am a long time SQLite user but have generally used it from C++ in the
>> past.  In this project however, I am attempting to make a JDBC wrapper and
>> use from Android a newer version of SQLite (3.12.2) than that provided by
>> Android 4.2.2 (3.7.11).
>>
>
> Group 8 however can only do 26 successful inserts and on the 27th
>> sqlite3_step() is returning SQLITE_CANTOPEN (14).
>>
>
>
> ​​
> SQLite is failing to create a temporary file, as there is nowhere
> convenient to do this on Android. The usual fix is to build SQLite to store
> temp files in memory:
>
>   -DSQLITE_TEMP_STORE=3
>
>   https://www.sqlite.org/compile.html#temp_store
>
> Or execute
> ​​
> "PRAGMA temp_store = memory" after opening your db connection.
>

>
​Dan, many thanks.  This does indeed solve my problem.

Just for my information, what is the purpose of this temporary file?  I see
that -journal file is always stored to disk.
​
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with savepoints

[Dan Kennedy]

On 07/06/2016 09:09 PM, Jeff Archer wrote:

Hi All,
I am a long time SQLite user but have generally used it from C++ in the
past.  In this project however, I am attempting to make a JDBC wrapper and
use from Android a newer version of SQLite (3.12.2) than that provided by
Android 4.2.2 (3.7.11).



Group 8 however can only do 26 successful inserts and on the 27th
sqlite3_step() is returning SQLITE_CANTOPEN (14).



SQLite is failing to create a temporary file, as there is nowhere 
convenient to do this on Android. The usual fix is to build SQLite to 
store temp files in memory:


  -DSQLITE_TEMP_STORE=3

  https://www.sqlite.org/compile.html#temp_store

Or execute "PRAGMA temp_store = memory" after opening your db connection.

Dan.




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

Re: [sqlite] Problem with savepoints

[Quan Yong Zhai]

It seemed that SQLite can’t create a temporary file,:

PRAGMA TEMP_STORE=MEMORY


From: Quan Yong Zhai<mailto:q...@msn.com>
Sent: 2016年7月6日 22:35
To: Jeff Archer<mailto:jeffarch...@gmail.com>; 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: RE: [sqlite] Problem with savepoints

PRAGMA JOURNAL_MODE=MEMORY

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

From: Jeff Archer<mailto:jeffarch...@gmail.com>
Sent: 2016年7月6日 22:10
To: 
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: [sqlite] Problem with savepoints

Hi All,
I am a long time SQLite user but have generally used it from C++ in the
past.  In this project however, I am attempting to make a JDBC wrapper and
use from Android a newer version of SQLite (3.12.2) than that provided by
Android 4.2.2 (3.7.11).  Basically, what I have is SQLite compiled with the
NDK into a static library and then a Java wrapper to provide the JDBC
interfaces.  My application is then attempting to use the JDBC interfaces
to operate on the database.  This is generally working.  I can create/open
databases, add/retrieve data, etc. including making use of databases that
are originally created from the native android SQlite (3.7.11)

I have run into a strange issue when attempting to use savepoints.
I create first a transaction with "begin",
then create the outer savepoint "SAVEPOINT SQLITE_SAVEPOINT_1".  This
savepoint will be released after 8 groups have been inserted.
Next, for each of the 8 groups of data a group save point is created
"SAVEPOINT SQLITE_SAVEPOINT_2", data is inserted with sqlite3_step()
returning SQLITE_DONE, then savepoint is released "RELEASE SAVEPOINT
SQLITE_SAVEPOINT_2"
Repeat this successfully for groups 2 through 7.
Group 8 however can only do 26 successful inserts and on the 27th
sqlite3_step() is returning SQLITE_CANTOPEN (14).

This description is of having 128 inserts per group.  The exact number of
inserts seems to be dependent upon the length of the text being inserted
but as long as data does not change the failure point is predictable.

CREATE TABLE variables (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT
NULL, value TEXT NOT NULL)

INSERT INTO variables VALUES (null, '', '')

sqlite version 3.12.2

Thanks,

Jeff
___
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] Problem with savepoints

[Quan Yong Zhai]

PRAGMA JOURNAL_MODE=MEMORY

Sent from Mail for Windows 10

From: Jeff Archer
Sent: 2016年7月6日 22:10
To: 
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Problem with savepoints

Hi All,
I am a long time SQLite user but have generally used it from C++ in the
past.  In this project however, I am attempting to make a JDBC wrapper and
use from Android a newer version of SQLite (3.12.2) than that provided by
Android 4.2.2 (3.7.11).  Basically, what I have is SQLite compiled with the
NDK into a static library and then a Java wrapper to provide the JDBC
interfaces.  My application is then attempting to use the JDBC interfaces
to operate on the database.  This is generally working.  I can create/open
databases, add/retrieve data, etc. including making use of databases that
are originally created from the native android SQlite (3.7.11)

I have run into a strange issue when attempting to use savepoints.
I create first a transaction with "begin",
then create the outer savepoint "SAVEPOINT SQLITE_SAVEPOINT_1".  This
savepoint will be released after 8 groups have been inserted.
Next, for each of the 8 groups of data a group save point is created
"SAVEPOINT SQLITE_SAVEPOINT_2", data is inserted with sqlite3_step()
returning SQLITE_DONE, then savepoint is released "RELEASE SAVEPOINT
SQLITE_SAVEPOINT_2"
Repeat this successfully for groups 2 through 7.
Group 8 however can only do 26 successful inserts and on the 27th
sqlite3_step() is returning SQLITE_CANTOPEN (14).

This description is of having 128 inserts per group.  The exact number of
inserts seems to be dependent upon the length of the text being inserted
but as long as data does not change the failure point is predictable.

CREATE TABLE variables (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT
NULL, value TEXT NOT NULL)

INSERT INTO variables VALUES (null, '', '')

sqlite version 3.12.2

Thanks,

Jeff
___
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