Re: [sqlite] Problem with savepoints
On Wed, 6 Jul 2016 18:10:34 +0200 Chris Brodywrote: > > > > 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
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
> > 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
On 07/06/2016 10:52 PM, Jeff Archer wrote: On Wed, Jul 6, 2016 at 10:46 AM, Dan Kennedywrote: 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
On Wed, Jul 6, 2016 at 10:46 AM, Dan Kennedywrote: > 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
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
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
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
[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