[sqlite] Data view mismatch on first OpenWRT startup

[Ward WIllats]

Hi. I've got kind of a weird situation and I'd value your thoughts.

Our OpenWRT-based Linux embedded system, on very first boot, will startup with 
a root filesystem on a RAM (ramFS) mount while the JFFS partition receives an 
initial format. Once the JFFS is formatted and most of the init scripts have 
run, the system copies all the files from the temporary RAM filesystem to the 
JFFS and uses the pivot() system call to make the JFFS the new root. (Future 
boots use the now-formatted JFFS mount as root directly at startup.)

We have a sqlite DB that various init scripts use, then close. Then the copy 
and pivot happens and our main application opens the DB on the JFFS volume. 
(That is, we don't keep any connections to the DB open during the switch from 
the temp ramFS root to the final JFFS root.) 

Our main app is multi-threaded with one connection per thread. It runs the DB 
in WAL mode. Because sqlite can't memmap the .shm file on JFFS (or something), 
we move it onto /tmp (another ramFS) with the semi-obscure SQLITE_SHM_DIRECTORY 
define.

What I am seeing, and ONLY in this first boot situation (where a ramFS -> copy 
-> pivot -> JFFS) sequence occurred, is that some info written on one 
connection in one thread is not present when  read back on another connection 
in another thread. Since we start the app AFTER the transition to JFFS, I 
believe all connection handles the app holds should be to the same/right DB 
file on JFFS. If that is so, the problem should NOT be because one thread is 
writing into "the wrong" DB on ramFS and one is reading from "the right" DB on 
JFFS.

However, the .shm file has lived happily on /tmp this whole time, whether the 
DB was on ramFS or JFFS. My impression was this file only held file offsets for 
housekeeping -- but is there anything else in there that could break if the DB 
file it was tracking was secretly and transparently moved from one volume to 
another?

Or do you have any other conjecture?

Thanks

-- Ward

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

Re: [sqlite] SQLite version 3.21.0 in approximately two weeks

[Ward WIllats]

> On Oct 11, 2017, at 4:26 AM, Richard Hipp  wrote:
> 
> A summary of changes for the 3.21.0 release can be seen at
> 
>https://sqlite.org/draft/releaselog/3_21_0.html


Item #5:  "A forger can subverted" ==> "A forger can subvert"


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

Re: [sqlite] Corrupt index == disk full?

[Ward Willats]

> On May 10, 2017, at 10:49 AM, Simon Slavin <slav...@bigfraud.org> wrote:
> 
> On 10 May 2017, at 6:31pm, Ward WIllats <sqlite-us...@wardco.com> wrote:
> 
>> I guess with corruption, all bets are off
> 
> I see your results from "pragma integrity_check".  As you write, "all bets 
> are off".  

Thanks Simon. I'm not saying there is any kind of bug in SQLite, and, we've 
never been able to fully run down this corruption (not for lack of trying!) -- 
right now we just blow the DB away and start over when it is detected. (* hangs 
head in shame *)

But, we didn't do this in this case because a "disk full" error is not the 
CORRUPT or NOTADB error we expected to see to trigger the panic. So I was 
surprised, but...

Undefined behavior is indeed undefined, so anything can happen and that's OK. 
We'll deal.

Thanks again.

-- Ward


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

Re: [sqlite] Corrupt index == disk full?

[Ward WIllats]

> On May 10, 2017, at 2:20 PM, Simon Slavin  wrote:

> I’m concerned that this sort of corruption is happening repeatedly.  Whether 
> or not there’s anything in howtocorrupt.html that rings any bells, you can 
> rely on us to help figure out what’s wrong.
> 

Thanks. That's kind. It could well be memory corruption. We've had a hard time 
getting Valgrind up on this platform to chase. When we turn all the debug check 
stuff on in sqlite it doesn't seem to happen. (In fact, it doesn't happen often 
in any case.)

This use case on this particular DB is dead simple too -- one writer and one 
reader in different posix threads in the same process, fully serialized.

The unusual parts of our setup are:

1. The DB in questions is on a volatile filesystem and ATTACHED to another, 
smaller one residing on a flash (JFFS) filesystem, and
2. We use the shady SQLITE_SHM_DIRECTORY compile switch to move the .shm file 
off JFFS and into RAM so we can WAL, and
3. We're linking against an older version of the MUSL c library which has had 
bugs in the past on our platform and may have more. (We are moving to a new 
platform soon so will get to upgrade and isolate this variable.)

#2 is the one I always worry about, but generally it seems to work well.

-- Ward

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

Re: [sqlite] Corrupt index == disk full?

[Ward WIllats]

> On May 10, 2017, at 10:49 AM, Simon Slavin <slav...@bigfraud.org> wrote:
> 
> On 10 May 2017, at 6:31pm, Ward WIllats <sqlite-us...@wardco.com> wrote:
> 
>> I guess with corruption, all bets are off
> 
> I see your results from "pragma integrity_check".  As you write, "all bets 
> are off".  

Thanks Simon. I'm not saying there is any kind of bug in SQLite, and, we've 
never been able to fully run down this corruption (not for lack of trying!) -- 
right now we just blow the DB away and start over when it is detected. (* hangs 
head in shame *)

But, we didn't do this in this case because a "disk full" error is not the 
CORRUPT or NOTADB error we expected to see to trigger the panic. So I was 
surprised, but...

Undefined behavior is indeed undefined, so anything can happen and that's OK. 
We'll deal.

Thanks again.

-- Ward


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

[sqlite] Corrupt index == disk full?

[Ward WIllats]

Hello.

We have a DB with a corrupted index (see below). The database has a max_pages 
limit that works out to a 10 MB database size (we're embedded). 

We can insert into the table the index is on OK.

When we do a SELECT out of the table that uses the index, we get a "disk or 
database full" error (and not a "db corrupted or not a db" error). Neither the 
DB nor the disk are actually full, however, max_page_count has been corrupted 
(should be 9756 with page size of 1024).

Does this seem...wellnormal? I mean, I guess with corruption, all bets are 
off, but I'm curious as to what the mechanism might be that made a full error 
and not a corrupted error pop out.

Thanks

-- Ward

sqlite> pragma integrity_check;
*** in database main ***
On tree page 6053 cell 29: Failed to read ptrmap key=67699289
On tree page 6053 cell 29: invalid page number 67699289
On tree page 6053 cell 30: Failed to read ptrmap key=151148817
On tree page 6053 cell 30: invalid page number 151148817
Corruption detected in cell 29 on page 6053
Corruption detected in cell 30 on page 6053
Corruption detected in cell 31 on page 6053
Fragmentation of 33 bytes reported as 0 on page 6053
row 1283 missing from index iRareData
row 1284 missing from index iRareData
row 1285 missing from index iRareData
row 1286 missing from index iRareData
row 1287 missing from index iRareData


sqlite> pragma max_page_count;
1073741823
sqlite> pragma page_count;
8683
sqlite> pragma freelist_count;
1393
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Magic self-healing database!

[Ward WIllats]

Hello List.

Well, here's something I haven't seen before.

I've got a DB in WAL mode on an embedded Linux system. My app (which has 
several threads, each with a connection, with Sqlite running fully serialized) 
was reporting database corruption in its logs.

Sun Feb 19 07:52:45 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 07:52:45 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 07:58:46 2017 user.debug NTPd: ntpd event: periodic
Sun Feb 19 08:06:24 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:06:24 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:11:05 2017 user.debug NTPd: ntpd event: periodic
Sun Feb 19 08:11:05 2017 user.debug NTPd: ntpd event: stratum
Sun Feb 19 08:20:03 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:20:03 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:30:00 2017 user.debug NTPd: ntpd event: periodic
Sun Feb 19 08:36:43 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:36:43 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:48:10 2017 user.debug NTPd: ntpd event: periodic
Sun Feb 19 08:51:50 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 08:51:50 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
Sun Feb 19 09:04:46 2017 user.warn FSP: [01636] [W:S3DB]:Sqlite3 Callback: 
database corruption at line 76226 of [254419c367] (11)
...and so on...

I shelled into the device with the app still running and ran the sqlite shell 
and did a "pragma integrity_check;" which reported:

sqlite> pragma integrity_check;
*** in database main ***
On tree page 598 cell 2: Offset 65026 out of range 178..1020
On tree page 598 cell 1: Offset 21743 out of range 178..1020

Then I SIGKILL'd my application and restarted it, and it recovered 8 frames 
from the WAL file, and the app seemed to be working again. So I used the shell 
again while the app was running and lo and behold! The database was fixed!

sqlite> pragma integrity_check;
ok

Sodoes this sorcery seem possible? Or have I just fooled myself somehow? 
What conclusions (if any) can I draw from this?

Thanks,

-- Ward

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

[sqlite] Deprecated OSX functioon

[Ward WIllats]

This has been coming across my console when building Sqlite since upgrading to 
Mac OSX Sierra a few months ago. FWIW.


Building sqlite3 shell...
sqlite3.c:20839:17: warning: 'OSAtomicCompareAndSwapPtrBarrier' is deprecated: 
first deprecated in macOS 10.12 - Use atomic_compare_exchange_strong() from 
 instead [-Wdeprecated-declarations]
  success = OSAtomicCompareAndSwapPtrBarrier(NULL, newzone, 
^
/usr/include/libkern/OSAtomicDeprecated.h:547:6: note: 
'OSAtomicCompareAndSwapPtrBarrier' has been explicitly marked deprecated here
boolOSAtomicCompareAndSwapPtrBarrier( void *__oldValue, void *__newValue, 
void * volatile *__theValue );
^
1 warning generated.

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

Re: [sqlite] sqlite3_errmsg() after sqlitr3_exec() on ATTACHed DB

[Ward WIllats]

> On Feb 1, 2017, at 1:18 PM, Igor Tandetnik <i...@tandetnik.org> wrote:
> 
> On 2/1/2017 10:32 AM, Ward WIllats wrote:
>> When I perform an sqlite3_exec() to DELETE too many rows in the secondary 
>> ATTACHed database and a disk or database full error occurs, I properly get a 
>> code 13 returned from the API.
>> 
>> But if I then turn around and call sqlite3_errmsg() I get "not an error" 
>> returned.
> 
> With sqlite3_exec(), the error message is returned via its last parameter, 
> not via sqlite3_errmsg(). sqlite3_exec is a wrapper that calls other SQLite 
> APIs internally, and the one that failed may not be the last call, so the 
> error may already have been reset by the time you get around to 
> sqlite3_errmsg(). Which is why sqlite3_exec() makes the effort to preserve 
> the message and forward it to you, should you choose to accept it.

Thanks Igor. Makes perfect sense.

-- Ward


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

[sqlite] sqlite3_errmsg() after sqlitr3_exec() on ATTACHed DB

[Ward WIllats]

Hello.

In our embedded system we have two databases ATTACHed to each other and size 
constrained with max_page pragmas directed at each. (Different filesystems: one 
is on a tmpfs in RAM, the other on JFFS).

When I perform an sqlite3_exec() to DELETE too many rows in the secondary 
ATTACHed database and a disk or database full error occurs, I properly get a 
code 13 returned from the API.

But if I then turn around and call sqlite3_errmsg() I get "not an error" 
returned. (Our too-complicated custom sqlite wrapper does this.)

Guesses:

The error string returned by sqlite3_errmsg() comes from the connection 
structure, and it says "not an error" because, indeed, there as no error on the 
primary DB.

There is a hidden handle/structure for the ATTACHed DB that has the correct 
error string, but sqlite3_errmsg() does not look there.

If I passed in an error string pointer to the exec() call itself, I would 
properly get a "disk or database full" message. In fact, that may be one of the 
reasons such a parameter exists on this API.

The rule is, if it is an exec(), get your error string from the exec() call, 
otherwise, you can get it from sqlite3_errmsg().

(This is no big deal as it is just for our internal logging, and, obviously I 
am being lazy by not just running a test in the shell to see, and, also, our 
wrapper code is likely buggy and is also obscuring what I am really doing even 
from myself (!), but thought it worth throwing this out for others and to be 
sure my conceptual model was not too far off the mark before I start hacking 
away.)

Thanks again.

-- Ward





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

Re: [sqlite] DELETE when DB is full

[Ward WIllats]

> On Jan 31, 2017, at 3:54 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> 
> On 31 Jan 2017, at 10:40pm, Warren Young <war...@etr-usa.com> wrote:
> 
>> On Jan 31, 2017, at 2:03 PM, Ward WIllats <sqlite-us...@wardco.com> wrote:
>> 
>>> the delete sometimes (very rarely) fails with a 13 "disk or database full" 
>>> error. I assume because the purger is late to the party and it needs pages 
>>> in the WAL to be able to rollback if necessary.
>> 
>> If you get this error, shrink the date range or pages-to-free value by half 
>> and try again.  Repeat until it works, then repeat at that size until you’ve 
>> deleted as much as you need to.
> 
> It’s possible to do a DELETE in chunks, like you would use LIMIT on a SELECT. 
>  

Thanks for taking the time to provide the great tips and technical explanation 
by DRH. Our architecture is well suited to some kind of chunked and/or 
recursing delete so I will do some experiments today to find some reasonable 
bounding parameters and push ahead.

(I have another simple question related to this, but I'll start a new thread so 
it is not buried here.)

-- Ward



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

[sqlite] DELETE when DB is full

[Ward WIllats]

Hello.

We have an embedded system out in the wild with a DB in WALL mode that we set a 
max_pages value on to keep its size constrained. The system is more or less a 
data logger. We run a "purger" thread at intervals to DELETE records when it 
discovers free space is running below a threshold. 

The purger starts a transaction, does a query to figure out the time range to 
delete, and then does a delete with that time range. (I realize now I could 
combine the DELETE and SELECT into one statement) and then ends the transaction.

Thing is, the delete sometimes (very rarely) fails with a 13 "disk or database 
full" error. I assume because the purger is late to the party and it needs 
pages in the WAL to be able to rollback if necessary.

I figure I can run the purger more often, or raise my "must keep free" 
threshold, but it is hard to know the rate at which data will be generated and 
it can be really "bursty" -- so such fixes are a bit hand-wavy and unsatisfying.

So, I thought I'd check with you folks and see if: 

1. The operational assumption (delete need pages) I've asserted here is 
correct, and
2. There is some trick I can use to force a delete of records when the DB pages 
are already maxed out -- esp. if I don't need to roll this back.

This is sqlite 3.10.1.

Thanks

-- Ward

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

Re: [sqlite] page_size on ATTACH-ed databases

[Ward WIllats]

Good thought. But no. In fact, I should have included the line where I deleted 
it before taking the trace below.

~# rm /tmp/RareData.db
~# /usr/local/bin/sqlite3 /opt/foundation/core_db.db
SQLite version 3.10.1 2016-01-13 21:41:56


> On Aug 16, 2016, at 11:17 AM, Scott Hess <sh...@google.com> wrote:
> 
> Is there any possibility that the attached db already existed before
> you ran this?  Because once a db exists (contains pages) the page size
> is fixed until you run vacuum.
> 
> On Tue, Aug 16, 2016 at 10:53 AM, Ward WIllats <sqlite-us...@wardco.com> 
> wrote:
>> 
>>>> On Aug 12, 2016, at 11:44 PM, Dan Kennedy <danielk1...@gmail.com> wrote:
>>>> 
>>>> On 08/13/2016 01:14 AM, Ward WIllats wrote:
>>>> 
>>>> Can't reproduce this problem here. Are you able to reproduce it with the 
>>>> shell tool?
>>>> 
>>> 
>>> 
>>> Yes, if I use the shell on our embedded system (OpenWRT/Linux). I should 
>>> have added that:
>>> 
>>> 1. The main DB in on a JFFS filesystem and we've moved the -shm file onto 
>>> /tmp with a marginally-supported sqlite #define.
>>> 2. The secondary DB is on /tmp in a RAM FS (which is 
>>> larger/faster/volatile).
>>> 
>>> We actually issue quite a few pragmas on open. I'll post the whole sequence 
>>> when I can get back to our box.
>>> 
>> 
>> Here we go:
>> 
>> ~# /usr/local/bin/sqlite3 /opt/foundation/core_db.db
>> SQLite version 3.10.1 2016-01-13 21:41:56
>> 
>> Enter ".help" for usage hints.
>> sqlite> attach database '/tmp/RareData.db' as rd;   < ATTACH SECOND DB
>> sqlite>
>> sqlite> pragma page_size=512;   <- SET MAIN DB PAGE SIZE
>> sqlite> pragma cache_size=200;
>> sqlite> pragma mmap_size=0;
>> 0
>> sqlite> pragma busy_timeout=57000;
>> 57000
>> sqlite> pragma foreign_keys=ON;
>> sqlite> pragma synchronous=normal;
>> sqlite> pragma journal_mode=WAL;
>> wal
>> sqlite> pragma temp_store=2;
>> sqlite> pragma waL_autocheckpoint=10;
>> 10
>> sqlite> pragma journal_size_limit=15360;
>> 15360
>> sqlite> pragma auto_vacuum=2;
>> sqlite> pragma max_page_count=16384;
>> 16384
>> sqlite>
>> sqlite> pragma page_size;
>> 512   < MAIN DB PAGE SIZE OK
>> sqlite>
>> sqlite> pragma rd.page_size=4096; <- SET ATTACHED DB PAGE SIZE 
>> TO 4K
>> sqlite> pragma rd.cache_size=32;
>> sqlite> pragma rd.mmap_size=0;
>> 0
>> sqlite> pragma rd.busy_timeout=57000;
>> 57000
>> sqlite> pragma rd.foreign_keys=ON;
>> sqlite> pragma rd.synchronous=normal;
>> sqlite> pragma rd.journal_mode=WAL;
>> wal
>> sqlite> pragma rd.temp_store=2;
>> sqlite> pragma rd.waL_autocheckpoint=2;
>> 2
>> sqlite> pragma rd.journal_size_limit=16384;
>> 16384
>> sqlite> pragma rd.auto_vacuum=2;
>> sqlite> pragma rd.max_page_count=5000;
>> 5000
>> sqlite>
>> sqlite> pragma rd.page_size;
>> 1024   <-- GET 1K DEFAULT PAGE SIZE BACK 
>> ON ATTACHED DB, *NOT* 4K SET ABOVE
>> sqlite>
>> 
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size on ATTACH-ed databases

[Ward WIllats]

>> On Aug 12, 2016, at 11:44 PM, Dan Kennedy <danielk1...@gmail.com> wrote:
>> 
>> On 08/13/2016 01:14 AM, Ward WIllats wrote:
>> 
>> Can't reproduce this problem here. Are you able to reproduce it with the 
>> shell tool?
>> 
> 
> 
> Yes, if I use the shell on our embedded system (OpenWRT/Linux). I should have 
> added that:
> 
> 1. The main DB in on a JFFS filesystem and we've moved the -shm file onto 
> /tmp with a marginally-supported sqlite #define.
> 2. The secondary DB is on /tmp in a RAM FS (which is larger/faster/volatile).
> 
> We actually issue quite a few pragmas on open. I'll post the whole sequence 
> when I can get back to our box.
> 

Here we go:

~# /usr/local/bin/sqlite3 /opt/foundation/core_db.db
SQLite version 3.10.1 2016-01-13 21:41:56

Enter ".help" for usage hints.
sqlite> attach database '/tmp/RareData.db' as rd;   < ATTACH SECOND DB
sqlite> 
sqlite> pragma page_size=512;   <- SET MAIN DB PAGE SIZE
sqlite> pragma cache_size=200;
sqlite> pragma mmap_size=0;
0
sqlite> pragma busy_timeout=57000;
57000
sqlite> pragma foreign_keys=ON;
sqlite> pragma synchronous=normal;
sqlite> pragma journal_mode=WAL;
wal
sqlite> pragma temp_store=2;
sqlite> pragma waL_autocheckpoint=10;
10
sqlite> pragma journal_size_limit=15360;
15360
sqlite> pragma auto_vacuum=2;
sqlite> pragma max_page_count=16384;
16384
sqlite> 
sqlite> pragma page_size;
512   < MAIN DB PAGE SIZE OK
sqlite> 
sqlite> pragma rd.page_size=4096; <- SET ATTACHED DB PAGE SIZE TO 4K
sqlite> pragma rd.cache_size=32;
sqlite> pragma rd.mmap_size=0;
0
sqlite> pragma rd.busy_timeout=57000;
57000
sqlite> pragma rd.foreign_keys=ON;
sqlite> pragma rd.synchronous=normal;
sqlite> pragma rd.journal_mode=WAL;
wal
sqlite> pragma rd.temp_store=2;
sqlite> pragma rd.waL_autocheckpoint=2;
2
sqlite> pragma rd.journal_size_limit=16384;
16384
sqlite> pragma rd.auto_vacuum=2;
sqlite> pragma rd.max_page_count=5000;
5000
sqlite> 
sqlite> pragma rd.page_size;
1024   <-- GET 1K DEFAULT PAGE SIZE BACK ON 
ATTACHED DB, *NOT* 4K SET ABOVE
sqlite> 

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

Re: [sqlite] page_size on ATTACH-ed databases

[Ward WIllats]

> On Aug 12, 2016, at 11:44 PM, Dan Kennedy <danielk1...@gmail.com> wrote:
> 
> On 08/13/2016 01:14 AM, Ward WIllats wrote:
> 
> Can't reproduce this problem here. Are you able to reproduce it with the 
> shell tool?
> 


Yes, if I use the shell on our embedded system (OpenWRT/Linux). I should have 
added that:

1. The main DB in on a JFFS filesystem and we've moved the -shm file onto /tmp 
with a marginally-supported sqlite #define.
2. The secondary DB is on /tmp in a RAM FS (which is larger/faster/volatile).

We actually issue quite a few pragmas on open. I'll post the whole sequence 
when I can get back to our box.

Thanks for taking the the time with this.

-- Ward

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

[sqlite] page_size on ATTACH-ed databases

[Ward WIllats]

Consider:

1. Create a new database, set the pragma page_size=512

2. Create a new database on the connection with ATTACH DATABASE 
'/tmp/number_two.db' AS second;

3. Issue pragma second.page_size=4096  to try and set the page size on the 
attached DB to 4096.

4. Read back with pragma second.page_size and get the default page size of 
1024. (We are still on 3.10.1)

Is it expected that the 4096 did not "stick?" Is there some relationship 
between page sizes in a main and attached DB?

Thanks

-- Ward

(In real life, we also set journal_mode=WAL on both databases AFTER issuing the 
page_size pragma, in case it makes any difference.)





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

Re: [sqlite] max_page_count wont round-trip between shell and program

[Ward WIllats]

> On Jul 5, 2016, at 3:18 PM, David Empson <demp...@emptech.co.nz> wrote:
> 
> 
>> On 6/07/2016, at 8:55 AM, Ward WIllats <sqlite-us...@wardco.com> wrote:
>> 
>>> I have noticed that when I set max_page_count programatically to 16384 and 
>>> read it back with the shell I get 1073741823.
>>> If I set max_page_count with the shell to 16384 and read it back 
>>> programmatically, the program gets back 1073741823.
>>> Both the program and the shell can round-trip their own set/get cycle OK.
>>> 
>> 
>> Oh wait, you're going to tell me the value is transient to the connection, 
>> aren't you? And 1073741823 is some kind of max sentinel?
> 
> Looks like it

Yep. Thanks. Might be nice to have the non-persistence mentioned in the docs 
for the pragma

/*
** Maximum number of pages in one database file.
**
** This is really just the default value for the max_page_count pragma.
** This value can be lowered (or raised) at run-time using that the
** max_page_count macro.
*/
#ifndef SQLITE_MAX_PAGE_COUNT
# define SQLITE_MAX_PAGE_COUNT 1073741823
#endif

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

Re: [sqlite] max_page_count wont round-trip between shell and program

[Ward WIllats]

> On 2016/07/05 10:50 PM, R Smith wrote:
> The page size of the DB doesn't actually change until you repack it (unless 
> it is new) - I think.
> 
> Try the test running "VACUUM;" after setting the page size to repack the DB 
> in that page size and so make it stick outside of the current connection.

It is max_page_count, not page_size.


> On Jul 5, 2016, at 1:56 PM, R Smith  wrote:
> 
> Actually - this looks more like a big-endian problem...
> 
> 1073741823 is exactly 4-bytes left shifted from 16384. So you are 
> setting/reading the high order bytes of a 64-bit field in stead of the 
> low-order bytes.

Yeah, I expect the default max_page_count is a power of 2, which would be the 
1073741823. I just expected the value I set to be persistent and it doesn't 
look like it is. (Which means every program that opens the DB *must* set this 
on the new connection.)

Thanks,

-- Ward

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

[sqlite] max_page_count wont round-trip between shell and program

[Ward WIllats]

> I have noticed that when I set max_page_count programatically to 16384 and 
> read it back with the shell I get 1073741823.
> If I set max_page_count with the shell to 16384 and read it back 
> programmatically, the program gets back 1073741823.
> Both the program and the shell can round-trip their own set/get cycle OK.
> 

Oh wait, you're going to tell me the value is transient to the connection, 
aren't you? And 1073741823 is some kind of max sentinel?

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

[sqlite] max_page_count wont round-trip between shell and program

[Ward WIllats]

I have noticed that when I set max_page_count programatically to 16384 and read 
it back with the shell I get 1073741823.
If I set max_page_count with the shell to 16384 and read it back 
programmatically, the program gets back 1073741823.
Both the program and the shell can round-trip their own set/get cycle OK.

This is on a 64 bit Mac with clang, but gcc seemed to exhibit the same behavior 
(if Apple didn't alias gcc to clang behind my back).

Following are 2 examples. The first: 

1. Shows my configuration
2. Builds a shell
3. Uses the shell to make a test db, set the max_page_count and read it back 
correctly
4. Builds a test program (source shown) to open the DB and read back the 
max_page count incorrectly.

[/tmp/sqlite-amalgamation-313] -> clang --version
Apple LLVM version 7.3.0 (clang-703.0.29)
Target: x86_64-apple-darwin15.6.0
Thread model: posix
InstalledDir: 
/Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin

[/tmp/sqlite-amalgamation-313] -> cat /Users/wardw/.sqliterc 
.mode column
.width 19
.headers on
.prompt 'sql>' '...'
.nullvalue NULL


[/tmp/sqlite-amalgamation-313] -> ls -l
total 14880
-rw-r--r--  1 wardw  wheel  810 Jul  5 12:54 page_test.c
-rw-r--r--  1 wardw  wheel   175474 May 18 04:07 shell.c
-rw-r--r--  1 wardw  wheel  6915433 May 18 04:07 sqlite3.c
-rw-r--r--  1 wardw  wheel   486805 May 18 04:07 sqlite3.h
-rw-r--r--  1 wardw  wheel29370 May 18 04:07 sqlite3ext.h

[/tmp/sqlite-amalgamation-313] -> clang -D SQLITE_DEBUG  -D HAVE_READLINE 
-l readline -o sqlite3 sqlite3.c shell.c

[/tmp/sqlite-amalgamation-313] -> ls -l
total 17648
-rw-r--r--  1 wardw  wheel  810 Jul  5 12:54 page_test.c
-rw-r--r--  1 wardw  wheel   175474 May 18 04:07 shell.c
-rwxr-xr-x  1 wardw  wheel  1414652 Jul  5 12:55 sqlite3
-rw-r--r--  1 wardw  wheel  6915433 May 18 04:07 sqlite3.c
-rw-r--r--  1 wardw  wheel   486805 May 18 04:07 sqlite3.h
-rw-r--r--  1 wardw  wheel29370 May 18 04:07 sqlite3ext.h
[/tmp/sqlite-amalgamation-313] -> ./sqlite3 page_test.db
-- Loading resources from /Users/wardw/.sqliterc

SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sql>pragma max_page_count=16384;
max_page_count 
---
16384  
sql>pragma max_page_count;
max_page_count 
---
16384  
sql>.quit

[/tmp/sqlite-amalgamation-313] -> ls -l
total 17648
-rw-r--r--  1 wardw  wheel  810 Jul  5 12:54 page_test.c
-rw-r--r--  1 wardw  wheel0 Jul  5 12:56 page_test.db
-rw-r--r--  1 wardw  wheel   175474 May 18 04:07 shell.c
-rwxr-xr-x  1 wardw  wheel  1414652 Jul  5 12:55 sqlite3
-rw-r--r--  1 wardw  wheel  6915433 May 18 04:07 sqlite3.c
-rw-r--r--  1 wardw  wheel   486805 May 18 04:07 sqlite3.h
-rw-r--r--  1 wardw  wheel29370 May 18 04:07 sqlite3ext.h

[/tmp/sqlite-amalgamation-313] -> cat page_test.c
/* clang -D SQLITE_DEBUG -o page_test page_test.c sqlite3.c */

#include 
#include "sqlite3.h"

int main()
{

sqlite3* hDb = NULL;

if ( sqlite3_open_v2( "page_test.db", , SQLITE_OPEN_READWRITE, NULL ) 
!= 0 )
return -1;

/*
if ( sqlite3_exec( hDb, "pragma max_page_count=16384", NULL, NULL, NULL ) 
!= 0 )
return -2;
*/

sqlite3_stmt* hStmt = NULL;

if ( sqlite3_prepare_v2( hDb, "pragma max_page_count", -1, , NULL ) 
!= 0 )
return -3;

if ( sqlite3_step( hStmt ) != SQLITE_ROW )
return -4;

printf( "Max page size set to %d\n", sqlite3_column_int( hStmt, 0 ) );

if ( sqlite3_finalize( hStmt ) != 0 )
return -5;

hStmt = NULL;

if ( sqlite3_close_v2( hDb ) != 0 )
return -5;

hDb = NULL;

return 0;
}

[/tmp/sqlite-amalgamation-313] -> clang -D SQLITE_DEBUG -o page_test 
page_test.c sqlite3.c

[/tmp/sqlite-amalgamation-313] -> ls -l
total 20240
-rwxr-xr-x  1 wardw  wheel  1323224 Jul  5 12:57 page_test
-rw-r--r--  1 wardw  wheel  810 Jul  5 12:54 page_test.c
-rw-r--r--  1 wardw  wheel0 Jul  5 12:56 page_test.db
-rw-r--r--  1 wardw  wheel   175474 May 18 04:07 shell.c
-rwxr-xr-x  1 wardw  wheel  1414652 Jul  5 12:55 sqlite3
-rw-r--r--  1 wardw  wheel  6915433 May 18 04:07 sqlite3.c
-rw-r--r--  1 wardw  wheel   486805 May 18 04:07 sqlite3.h
-rw-r--r--  1 wardw  wheel29370 May 18 04:07 sqlite3ext.h

[/tmp/sqlite-amalgamation-313] -> ./page_test
Max page size set to 1073741823

[/tmp/sqlite-amalgamation-313] -> echo $?
0

===
EXAMPLE TWO
===

This second example does the reverse:

1. Change the test program to WRITE and read back max_page_count to 16384 
(correctly)
2. Read the max_page_count with the shell and get incorrect 1073741823 back.

[/tmp/sqlite-amalgamation-313] -> ls -l
total 14880
-rw-r--r--  1 wardw  wheel  832 Jul  5 13:30 page_test.c
-rw-r--r--  1 wardw  wheel   175474 May 18 04:07 shell.c
-rw-r--r--  1 wardw  

[sqlite] pragma foreign_keys attribute of connection?

[Ward WIllats]

> On Mar 2, 2016, at 6:53 PM, Ward WIllats  wrote:
> 
> Now, this diagnosis may or may not be correct,


Indeed, it is not. Never mind!

Thanks!

-- Ward

[sqlite] pragma foreign_keys attribute of connection?

[Ward WIllats]

Hello.

We have multiple processes trying to access an sqlite DB while one of them is 
inside a transaction trying to migrate the schema. Before the migrate 
transaction is started, pragma foreign_keys=0 is executed. Yet, in the middle 
of the migration, we get a constraint violation as though another process did 
pragma foreign_keys=1 and turned them back on.

Now, this diagnosis may or may not be correct, we are running more tests, but I 
thought I'd just ask here if foreign key enforcement is an attribute of the 
connection, or if it an aspect of the database file itself?

Thanks

-- Ward

[sqlite] Caveats using Sqlite on JFFS2 ?

[Ward WIllats]

Happy New Year folks.

Subject says it all. Any things to look out for when using Sqlite on a JFFS2 
filesystem? I see some old stuff from 2011 about WAL mode and MMAP_SHARED, but 
suspect that is no longer germane? Anything else to watch out for? (This would 
be for an Open-WRT style embedded Linux on MIPS.)

Thanks

-- Ward

[sqlite] Slow real world performance - Any suggestions please (warning long)

[Ward Willats]

> On Jul 2, 2015, at 3:16 PM, Rob Willett  
> wrote:
> 
> We?re trying to understand whether or not we have a performance problem with 
> our Sqlite database.

It may or may not apply to your situation, but after doing lots of inserts, 
running ANALYZE can sometimes work wonders.

-- Ward

[sqlite] Dumb statement question...

[Ward Willats]

Can a prepared statement have more than 1 statement in it (and bind parameters 
across the whole thing)?

Something like:

prepare_v2( h, "one statement ? ; two statement ?", -1, , NULL )

bind_int( s, 1,  )
bind_int( s, 2,  )

(I ask because I am getting a SQLITE_RANGE (25) error just after a ";" in a 
compound statement I've compiled with a param I've bound earlier...)

Thanks,

-- Ward

[sqlite] Corrupting pointers to the lookaside smallacator

[Ward Willats]

We are compiling the 3.8.7.1 using clang arm64 for iOS. Following set:

#define SQLITE_ENABLE_COLUMN_METADATA 1
#define HAVE_INTTYPES_H 1
#define HAVE_STDINT_H 1
#define HAVE_USLEEP 1

#define SQLITE_DEBUG 1
#define SQLITE_MEMDEBUG 1

WAL mode.

In MallowRaw(), very rarely, seeing the lookaside buffer pBuf or pBuf->next 
getting corrupted with ASCII from our CREATE TABLE statements. ('INTEGER' or 
part of one of our field names). Thing is, we are not running the schema create 
code on these runs (the DB already exists), so these strings must be coming 
from sqlite_master, AFAIKT.

None of the SQLITE_DEBUG or SQLITE_MEMDEBUG asserts fire. When it happens, it 
happens early in the application run.

Hard to set a hardware write breakpoint on such a mutable thing.

I fully believe the problem is of my own creation, but interested in any 
thoughts or if anyone has seen anything like this.

Thanks, as always,

-- Ward

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

Re: [sqlite] SQLite where clause tree

[Ward Willats]

> On Nov 10, 2014, at 9:31 AM, Richard Hipp <d...@sqlite.org> wrote:
> 
> On Mon, Nov 10, 2014 at 12:28 PM, Ward Willats <sqlite-us...@wardco.com>
> wrote:
> 
>> 
>>> On Nov 10, 2014, at 3:11 AM, Richard Hipp <d...@sqlite.org> wrote:
>>> 
>>> If you recompile the SQLite command-line shell (sqlite3.exe) using the
>>> -DSQLITE_ENABLE_SELECTTRACE option, then you can enter:
>>> 
>> 
>> If I do that,
>> 
>>   gcc -D SQLITE_ENABLE_SELECTTRACE -D HAVE_READLINE -l readline -o
>> sqlite3 sqlite3.c shell.c
>> 
>> sqlite3DebugPrintf and sqlite3TreeViewSelect are undefined at link time.
>> 
>> Must other switches be thrown?
>> 
> 
> Try adding -DSQLITE_DEBUG
> 

gcc -D SQLITE_DEBUG -D SQLITE_ENABLE_SELECTTRACE -D HAVE_READLINE -l readline 
-o sqlite3 sqlite3.c shell.c

Yes, that does it. Thanks.

-- Ward

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

Re: [sqlite] SQLite where clause tree

[Ward Willats]

> On Nov 10, 2014, at 3:11 AM, Richard Hipp  wrote:
> 
> If you recompile the SQLite command-line shell (sqlite3.exe) using the
> -DSQLITE_ENABLE_SELECTTRACE option, then you can enter:
> 

If I do that, 

   gcc -D SQLITE_ENABLE_SELECTTRACE -D HAVE_READLINE -l readline -o sqlite3 
sqlite3.c shell.c

sqlite3DebugPrintf and sqlite3TreeViewSelect are undefined at link time.

Must other switches be thrown?

-- Ward



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

Re: [sqlite] sqlite3.c in a library - api rename

[Ward Willats]

> On Oct 28, 2014, at 8:23 PM, Richard Hipp <d...@sqlite.org> wrote:
> 
> On Tue, Oct 28, 2014 at 11:18 PM, Ward Willats <sqlite-us...@wardco.com>
> wrote:
> 
>> Hello.
>> 
>> I am using the amalgamation in a C++ library statically linked into other
>> people's applications.
>> 
>> Is there a way to namespace and/or macro and/or let C++ do its
>> name-mangling thing to all the identifiers (by running the CPP compiler and
>> turning __cplusplus off) so only my library translation units can use this
>> "secret" version? (Or, more like, so the host app doesn't accidentally use
>> my version.)
>> 
>> 
> sed 's/sqlite3/wards_db/g' sqlite3.c >wards_db.c
> sed 's/sqlite3/wards_db/g' sqlite3.h >wards_db.h
> 

Heh! Okay! :)

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

[sqlite] sqlite3.c in a library - api rename

[Ward Willats]

Hello.

I am using the amalgamation in a C++ library statically linked into other 
people's applications.

Is there a way to namespace and/or macro and/or let C++ do its name-mangling 
thing to all the identifiers (by running the CPP compiler and turning 
__cplusplus off) so only my library translation units can use this "secret" 
version? (Or, more like, so the host app doesn't accidentally use my version.)

Thanks,

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

[sqlite] CONFIG_SERIALIED superset of CONFIG_MULITHREAD?

[Ward Willats]

Hello.

I'm wondering if CONFIG_SERIALIZED is a superset of CONFIG_MULITHREAD, 
recursive mutex wise.

I imagine MULTITHREAD is turning on mutexes to protect the pager and other 
"low-level" execution stuff, and SERIALIZED is turning on more mutexes to 
protect stuff hanging off the connection, like the parser and vdbe code 
generator.

The reason I ask is that I had this great system where I was running 
MULTITHREAD and was giving each thread its own handle via a "handle cache" that 
examined characteristics of the current thread to decide which handle to hand 
out when a open was requested. However, with the rise of Grand Central Dispatch 
and anonymous block execution on iOS and OS X, the threading model has become a 
complete circus and also, certain calls I have used in the past to do my cache 
magic have been deprecated -- the result is that the cache is getting fooled 
and passing the same connection handle to more than one thread (on rare 
occasions) and BOOM!

So...the easiest fix is to just switch to SERIALIZED. But I want to be sure I 
can still (mostly) have separate connections for each thread, but 
*occasionally* have more than one thread on the same connection. That is, I am 
assuming is not a requirement of SERIALIZED that ALL threads come banging in on 
a same global connection. (I mean, I've already thrown the switch and it seems 
to work, but thought I'd also ask to see if I'm missing anything.)

Someday, of course, I'll rework the handle cache"when I have time" (yeah, 
right).

-- Ward



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

Re: [sqlite] use sqlite3 in ios

[Ward Willats]

On Aug 13, 2014, at 2:31 AM, YAN HONG YE  wrote:

> When I  add sqlite3.c and sqlite3.h to xcode ios cocoa project, and compiled, 
> the error msg is:
> Sqlite3 class is not a objective class,  who have any cocoa sqlite source, I 
> don't know how to do.

If you compile in a .mm file instead of a .m file, you will fire up the 
ObjectiveC++ clang and it should compile the sqlite.h and sqlite.c file without 
trouble -- well, there will be some warnings.

We do this so we can trace into it, use common table subexpressions, and have a 
known version we are running against. We also provide our code as a library to 
other developers, so this eliminates one more dependency (we put sqlite.h in a 
namespace in case the client app links with Apple's version too).

Be sure to #define HAVE_USLEEP.

It's fun to watch xcode groan as it tries to load up the amalgamation source in 
the debugger!

-- Ward

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

Re: [sqlite] Support for concurrent transactions

[Ward Willats]

On Jun 21, 2014, at 1:36 PM, Simon Slavin  wrote:
>   But the programming style I've been using recently does the equivalent of 
> allowing any number of concurrent "BEGIN"s and handing back a handle for each 
> one.  You can execute any number of commands (SELECT or write) for each 
> BEGIN, and then close the transaction with an "END" or "ROLLBACK".
> 
> SQLite, of course, does not hand back a handle for each "BEGIN".  A BEGIN 
> inside a transaction is, correctly, an error.  But I find myself wanting to 
> handle my transactions more ... erm ... modularly.  So if I was to change my 
> programming style accordingly ...

Do you mean nested transactions?

In my home-grown C++ wrapper for sqlite I do this with a "transaction tracker" 
object that tracks the nesting level on a connection and executes a BEGIN 
(IMMEDIATE) if the nesting level == 0, and a "SAVEPOINT X" if the nesting level 
!= 0, and if successful, a COMMIT if the level is 0, and a "RELEASE X" if the 
nesting level != 0.

If the transaction is not successful and the nesting level is 0, it does a 
ROLLBACK, otherwise a "ROLLBACK TO X; RELEASE X"

(Yes, the name of the savepoint is always X.)

Works well.

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

[sqlite] Benign SQLITE_SCHEMA error?

[Ward Willats]

Hello.

Running Sqlite 3.8.4.3 in an iOS application in WAL mode. One writer. Many 
readers. Each thread on own connection.

After a particular big bulk insert of data, during which, BTW, indices are 
dropped and recreated and an ANALYZE done, sqlite is reporting on the 
SQLITE_CONFIG_LOG callback:

 Message: statement aborts at NN: [SELECT x 
 Error code: 17

17 is a SQLITE_SCHEMA error. The schema has not actually changed -- at least 
not intentionally!

The occurrence is pretty deterministic.

I traced through the source and Sqlite quietly retries the error and the second 
time it succeeds. No error is returned to the originating API call.

So..is this benign? Can/should I fix? How worried should I be?

Thanks

-- Ward

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

Re: [sqlite] Simple data conversion in SQLite - please help

[Ward Willats]

On Jan 22, 2014, at 8:21 AM, Richard Hipp  wrote:

> I seem to recall seeing some SMS databases off of an iPhone that used unix
> timestamps for the date/time.  That would be seconds since 1970.  You can
> use the 'unixepoch' modifier on the date functions within SQLite to do
> the conversion, if you want.


Yeah, that's what we do in our iOS apps. Try  "select datetime( field, 
'unixepoch' ) from table"

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

Re: [sqlite] fsync on iOS

[Ward Willats]

On Jan 16, 2014, at 1:50 PM, Richard Hipp  wrote:

> FYI:
> 
> If you use "PRAGMA journal_mode=WAL;" with "PRAGMA synchronous=NORMAL;",
> then fsync()s will only occur during a checkpoint operation.  And, you can
> turn off automatic checkpointing and run checkpoints from a separate thread
> or process, and let that separate thread or process take the fsync()
> delays, if you want.
> 

Thanks. WAL mode would also be a more natural fit for our many readers/few
writers architecture.

It's just that we always seem to be on the verge of releasing something and
so I never have the guts to throw that switch without being able to give Q/A
enough time to crawl all over it!

After this next release, FOR SURE! :-)

-- Ward

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

Re: [sqlite] fsync on iOS

[Ward Willats]

On Jan 16, 2014, at 1:50 PM, Richard Hipp  wrote:

> FYI:
> 
> If you use "PRAGMA journal_mode=WAL;" with "PRAGMA synchronous=NORMAL;",
> then fsync()s will only occur during a checkpoint operation.  And, you can
> turn off automatic checkpointing and run checkpoints from a separate thread
> or process, and let that separate thread or process take the fsync()
> delays, if you want.
> 

Thanks. WAL mode would also be a more natural fit for our many readers/few
writers architecture.

It's just that we always seem to be on the verge of releasing something and
so I never have the guts to throw that switch without being able to give Q/A
enough time to crawl all over it!

After this next release, FOR SURE! :-)

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

Re: [sqlite] fsync on iOS

[Ward Willats]

On Jan 16, 2014, at 12:02 PM, Richard Hipp <d...@sqlite.org> wrote:

> Do not compile with SQLITE_NO_SYNC.

Okay. Thanks.

> On Jan 16, 2014, at 1:29 PM, Roger Binns <rog...@rogerbinns.com> wrote:

> 
>> On 16/01/14 11:43, Ward Willats wrote:
>> So it looks like fsync() is taking more than the 5 second timeout I've
>> set.
> 
> This is not uncommon on mobile devices using flash based storage.  There
> is a lot of volatility in read and write performance.

I knew 5 seconds was a lot, but I've also seen multi-second delays working with 
(erasing) flash in other contexts, so it didn't seem impossible.
Also, this is very rare, we test A LOT and this has only happened once.

I am assuming the "BEGIN IMMEDIATE" the other thread that gets the busy error 
is doing *does* go through the default busy handler usleep() machinery if it 
can't get its lock right away though, yes?

If so, I'll just crank up the timeout. If not, I'll have to put in some retry 
logic of my own.

Thanks both,

-- Ward

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

[sqlite] fsync on iOS

[Ward Willats]

Hello Experts:

We are compiling our own amalgamation into our multi-threaded iOS app. 

Just saw a busy error where one thread is in sqlite doing an fsync() 
(unix_sync(), full_fsync()) and the the thread that gets the error is trying to 
start a transaction.

So it looks like fsync() is taking more than the 5 second timeout I've set.

What do you think? Should I make the timeout longer, or #define SQLITE_NO_SYNC, 
or?

Thanks, as always,

-- Ward

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

Re: [sqlite] Saw something interesting in the debugger...

[Ward Willats]

On Jan 9, 2014, at 10:10 AM, Roger Binns  wrote:
> The default busy handler (see sqliteDefaultBusyCallback in source) sleeps
> for these amount of milliseconds:
> 
>  { 1, 2, 5, 10, 15, 20, 25, 25,  25,  50,  50, 100 };
> 
> However on non-Windows if you do not have HAVE_USLEEP defined then it
> sleeps with a granularity of one second.  The blocking thread could have
> finished after 10ms, but you'll still be stuck in the busy handlers for
> another 990ms.
> 
> Simply ensure HAVE_USLEEP is defined when building sqlite3.c.  Or add your
> own busy handler that sleeps for sub-second amounts of time.


I was more interested in the scenario than the solution -- but I probably would 
have missed this simple fix and done something elaborate and stupid, so thank 
you very much!

-- Ward

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

[sqlite] Saw something interesting in the debugger...

[Ward Willats]

I've got a multi-threaded iOS app. Each thread has its own long-lived DB 
connection.

I was debugging a "stuttering" in the UI thread and broke into the debugger 
during one of the pauses.

I found the UI thread and a worker thread, both in the DB, both in the default 
busy handler, both taking a 1 second sleep.

I expected to see a third thread in the DB doing some work while the other two 
waited -- but no such thing.

Now, I could have missed it. A third thread could have gotten in and out by the 
time I broke in.

BUT, I was wondering if there are scenarios where only two threads can bounce 
each other into busy sleep like two bocci balls colliding?

(one thread wanted a read lock, the other a BEGIN EXCLUSIVE write lock)

-- Ward

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

Re: [sqlite] Connection philosophy

[Ward Willats]

On Jan 22, 2013, at 8:18 PM, Keith Medcalf  wrote:

> I presume that you are using some kind of input-driven or event driven 
> application which may get a request to process a query "in the middle" of 
> your update transaction.  

That is correct.

> One of the advantages of WAL and using a separate reader and writer 
> connection is that the reader connection will not see "uncommitted" data from 
> the "writer".

This is a good point and one I had not considered because I haven't been bit. 
In our application, the schema itself has kept us out of trouble, since the 
readers generally (always?) deal with a part of the schema uninvolved with the 
data collection. That is, they are looking up data to support the collection, 
but are not interested in the specific data being collected.

Kind of. (It is a complicated program and I would not be surprised if there are 
some corner-cases to this statement.)

It may be worth pursuing this approach anyway to make the most robust wrapper 
possible (and remove these concerns from the working set of things I have to 
worry about!).

> One way you might do this is to modify your wrappers 
...
>  keep a "usage" count 

Turns out the wrappers already have smart, reference counted objects for 
statement handles and the DB objects themselves, so hooking in the logic you 
suggest would be straightforward.


>  it may be better to think about such possibilities before they notice it and 
> claim your application is "broken" ... or even worse is acting "mysteriously" 
> in their eyes.
> 

So, you've worked here too, eh?  :-)

Anyway, I think this is a powerful model and, given the wrappers, I think I can 
experiment with it in a low-risk way. I look forward to doing so. In other 
news, last night I coded the the "secret singleton handle" -- actually a cache 
keyed by DB filename -- works well, and it may be just me, but I feel like it 
sped up certain operations quite a bit. We'll see what the QA folks tell me 
later on today!

Thanks again for all this. Very productive.

-- Ward


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

Re: [sqlite] Connection philosophy

[Ward Willats]

On Jan 22, 2013, at 5:54 PM, Keith Medcalf  wrote:

> I prefer the long-lived approach.  Continuously re-initialization of the 
> connection on open, the need to re-read pages into the page cache 
> repetitively, and the subsequent discard of a nicely loaded page-cache on 
> connection close usually incurs non-trivial overhead.

Thanks for this thoughtful response (you too, Simon). The page-cache argument 
is compelling.

In my case, I only have one writer (I think!) during the big transaction, so a 
long-lived, singleton connection or WAL should work for me. I guess I would 
lean toward the former (KISS).

-- Ward

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

Re: [sqlite] Connection philosophy

[Ward Willats]

On Jan 22, 2013, at 10:07 AM, Simon Slavin  wrote:

> Change the code used in your one big thread so that it counts the number if 
> INSERT/UPDATEs it does and changes transactions and does a little pause after 
> every thousand ops.  Or hundred.  Whatever.
> 

Cool idea, except the folks in Marketing want all the data or none of the data 
each time we collect it. (That is, in my case only, I can't partially commit 
along the way.)

-- Ward

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

[sqlite] Connection philosophy

[Ward Willats]

Hello.

Just wondering what the group opinion is on something.

I have a bunch of home-grown C++ wrappers for Sqlite and when my app needs to 
use the DB, most routines just instance one of these DB wrapper objects on the 
stack and go for it. The constructor creates a new DB connection, which is 
closed at destructor time. Very convenient.

All is well, EXCEPT, I have ONE big, long transaction that collects and stores 
a lot of data from some sensors. If this data is big enough, it will eventually 
cause that connection to obtain an exclusive lock on the DB. Now if the data 
collection code subsequently calls any subroutine that instances up a quick and 
dirty DB object on the stack and tries to use it, a "database is locked" (or 
"database busy" on write) error will be returned.

My app is single threaded.

I am have a couple of options:

1. Pass the DB object containing the connection with the open data collection 
transaction to the subroutines that need a DB. (Which is what I have been 
doing.)

2. Secretly keep a single, global connection open and let all the instances of 
the wrapper object use it.

3. Switch from rollback to WAL (or something) to keep the connections out of 
each other's way. That seems kind of a big deal since I then have to arrange 
the COMMITs.

Generally, I've had a "get in, get out" philosophy, but I can see the 
advantages of a single DB connection that exists for the life of the app. 
(Still, that seems like it might be more fragile than actually closing the DB 
file, dumping caches, and updating the directory.)

So what do all you hot-shots think is best practice? In/out, long-lived or 
something else?

Thanks

-- Ward



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

Re: [sqlite] Is there a way to create a foreign key in existing table?

[Ward Willats]

On Dec 31, 2012, at 12:57 AM, Simon Slavin  wrote:

> 
> On 31 Dec 2012, at 8:54am, Igor Korot  wrote:
> 
>> I simply forgot to do it on the table creation. And now the table has
>> many rows...
> 
> You can easily modify a TABLE definition or even an entire database by using 
> the SQLite shell tool to dump the database as text file of SQL commands, then 
> edit the text file using a text editor, then use the same shell tool to read 
> the text file back in again.


Or, if you want to do it "live:" use ALTER TABLE to rename the existing table, 
CREATE TABLE to make the table with the FK you want, INSERT SELECT to bring the 
records from the renamed table to the new table, and DROP TABLE to get rid of 
the renamed original.


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