[sqlite] fts5

[Dan Kennedy]

On 04/08/2015 04:49 AM, Scott Hess wrote:
> On Thu, Sep 11, 2014 at 8:58 AM, Dan Kennedy  wrote:
>> Fts5 is still in the experimental stage at the moment.
>>
>> If anybody has any ideas for useful features, or knows of problems with FTS4
>> that could be fixed in FTS5, don't keep them to yourself!
> Apologies for not noticing this thread earlier!
>
> After fts2 was released, someone engaged me on a discussion about
> whether I had considered an alternate storage strategy.  The current
> system of {term,doclist} where doclist is something like
> [{docid,[pos]}] means that the index b-tree is very lumpy because
> doclists are (extremely) variably-sized.  The suggestion was to store
> things as an ordered set of {term,doc,pos} tuples, then use some sort
> of delta encoding between them.  This would quite naturally balance
> the interior of the index versus the leaves, and would also work well
> with incremental merging since you only needed to worry about the head
> block for each segment being scanned.  I believe the current fts5 code
> gets similar results by keeping an index for large doclists to allow
> quickly scanning to the right point, so this might not add much.
>
> Something that bugged me a lot was that I had used deletion markers to
> cancel out hits, but did not provide a way for deletion markers to
> cancel out.  The main problem with this was that a large delete would
> stay in the system until it reached the final segment, even if it had
> already overtaken all of the original inserts.  I wished that I had
> either maintained a separate structure tracking _document_ deletion
> (which would make merges somewhat more complicated because they
> wouldn't be term-centric), or code updates as "delete+insert".  In the
> latter case deletes could drop out at the point where they reached the
> original insert.


Thanks for this. The "delete+insert" idea sounds like quite an 
interesting one.

So instead of just "delete" and "insert" keys, the merge tree now also 
contains "delete+insert" keys (call them "update" keys). Then maintain 
the tree so that

   (a) for each "insert", the next youngest duplicate key must either 
not exist or be a "delete",
   (b) for each "update", the next youngest duplicate key must exist and 
must be an "insert" or "update", and
   (c) for each "delete", the next youngest duplicate key must exist and 
must be an "insert" or "update".

And as a result, when a "delete" catches up with an "insert" while 
merging they can both be discarded. Instead of the current situation, 
where we retain the "delete" unless the output segment is the oldest in 
the database. Cool.

I guess they don't generally do this in merge-trees because the cost of 
figuring out whether to use "update" or "insert" keys when writing a new 
segments is prohibitively high. But FTS doesn't have that problem, as it 
never does a true "blind write". When it clobbers a key it always knows 
it at time of writing.


Dan.









>
> I seem to recall being upset by the amount of compression gzip could
> manage against index blocks, even though they mostly aren't very
> large.  I think things got around 1/4 or 1/3 smaller.  To me that
> implied that there were probably some gains to be had in encoding.
> [This is distinct from compression of content data, which fts3/4
> already support.]
>
> I'm 100% convinced that merging could be improved :-).  Clearly there
> is a lot of benefit to merging together the low-order segments, but I
> never figured out a good way to model whether merging the larger
> segments actually improved anything, since at some point you no longer
> can really enforce locality anyhow.  But I'm guessing that your
> experiments with the sqlite4 key/value store probably involve lots of
> exploration along these lines.
>
> -scott
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting a crash on 32-bit Linux

[Dan Kennedy]

On 04/14/2015 10:00 PM, Ron Aaron wrote:
> Just updated with the version from sqlite.org and have the same problem:

The line numbers still don't match the 3.8.9 amalgamation on the 
website. Are you running [make sqlite3.c] yourself or downloading 
sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?

Are you able to share the test app with us so that we can reproduce the 
crash?

What is output if you run [where full 7] in gdb?

Thanks,
Dan.





>
>
> #0  findInodeInfo (pFile=0x9434118, ppInode=0x9434120) at sqlite3.c:26091
> #1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0 
> , h=5, pId=0x9434118, zFilename=0x9434208 
> "/home/ron/proj/8th/test.db", ctrlFlags=0)
> at sqlite3.c:30092
> #2  0x084fa69e in unixOpen (pVfs=0x89422b0 , 
> zPath=0x9434208 "/home/ron/proj/8th/test.db", pFile=0x9434118, flags=262,
> pOutFlags=0xd074) at sqlite3.c:30697
> #3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0 
> , zPath=0x9434208 "/home/ron/proj/8th/test.db", 
> pFile=0x9434118, flags=262,
> pFlagsOut=0xd074) at sqlite3.c:16093
> #4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0 
> , ppPager=0x91c6af0, zFilename=0x92a3cf0 
> "test.db", nExtra=76, flags=0,
> vfsFlags=262, xReinit=0x8516110 ) at sqlite3.c:45520
> #5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0 
> , zFilename=0x92a3cf0 "test.db", db=0x9192fd0, 
> ppBtree=0x919318c, flags=0,
> vfsFlags=262) at sqlite3.c:54119
> #6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db", 
> ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
> #7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db", 
> ppDb=0x91a2028) at sqlite3.c:127601
>
>
> On 4/14/15 17:31, Ron Aaron wrote:
>> I'm using the one from fossil...
>>
>>
>> On 4/14/15 17:12, Richard Hipp wrote:
>>> On 4/14/15, Ron Aaron  wrote:
 I just updated to the 3.8.9 version of SQLite for my project, and am
 compiling for 32-bit linux (on a 64-bit box).


 I'm getting a consistent crash in findInodeInfo, with this backtrace:


  #0  findInodeInfo (pFile=0x9438118, ppInode=0x9438120) at
  sqlite3.c:26091
>>> Corresponds to this source code line:
>>> https://www.sqlite.org/src/info/25b80a3d?ln=710
>>>
  #1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0
  , h=5, pId=0x9438118, zFilename=0x9438208
  "/home/ron/proj/8th/test.db", ctrlFlags=0)
   at sqlite3.c:30092
>>> Corresponds to this source code line:
>>> https://www.sqlite.org/src/info/25b80a3d?ln=4711
>>>
>>> So I think you must be using a non-standard build of the sqlite3.c
>>> amalgamation file, huh?
>>>
  #2  0x084fa69e in unixOpen (pVfs=0x89422b0 
 ,
  zPath=0x9438208 "/home/ron/proj/8th/test.db", pFile=0x9438118,
  flags=262, pOutFlags=0xd064)
   at sqlite3.c:30697
  #3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0
  , zPath=0x9438208
  "/home/ron/proj/8th/test.db", pFile=0x9438118, flags=262,
  pFlagsOut=0xd064)
   at sqlite3.c:16093
  #4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0
  , ppPager=0x91c6af0, zFilename=0x92a7cf0
  "test.db", nExtra=76, flags=0, vfsFlags=262,
   xReinit=0x8516110 ) at sqlite3.c:45520
  #5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0
  , zFilename=0x92a7cf0 "test.db", 
 db=0x9192fd0,
  ppBtree=0x919318c, flags=0, vfsFlags=262)
   at sqlite3.c:54119
  #6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db",
  ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
  #7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db",
  ppDb=0x91a2028) at sqlite3.c:127601


 Nothing has changed in my test code, and I haven't got a similar 
 problem
 on 32-bit OS X;  however, it's certainly conceivable I'm not using the
 right flags when compiling SQLite:

  gcc   -I.  -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_AUTHORIZATION
  -DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_LOCKING_STYLE=0
  -DSQLITE_SYSTEM_MALLOC=1 -DSQLITE_OMIT_COMPILEOPTION_DIAGS
  -DSQLITE_OMIT_TRACE -O0 -c -o sqlite3.o sqlite3.c


 The crash occurs whether I work in a chroot lin 32 environment or a
 virtual machine running 'native'.  The test application is statically
 linked.


 Any clues would be appreciated!


 Thanks,

 Ron



 -- 
 Ron Aaron, CTO
 Aaron High-Tech, Ltd.
 +972.52.652.5543
 
 ___
 sqlite-users mailing list
 sqlite-users at mailinglists.sqlite.org
 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

>>
>

[sqlite] Changing WAL mode for a transaction

[Dan Kennedy]

On 04/26/2015 07:01 PM, Navaneeth K N wrote:
> Hello,
>
> My application runs the following right after opening the connection to the 
> database.
>
>pragma journal_mode=wal;
>pragma page_size=4096

Did executing the "PRAGMA journal_mode=wal" statement succeed? Or did it 
return SQLITE_BUSY or some other error code?







>
> When the application wants to perform a bulk data load (loading around 21Gb 
> of data), it runs the following before starting the transaction.
>
>  pragma journal_mode=delete
>
> This is done because I am under the assumption that WAL journal mode is not 
> suited for long running, big transactions (please correct me if I am wrong).
>
> But the journal mode change seems to be not affecting. Documentation for WAL 
> states the following:
>
> "Unlike the other journaling modes, PRAGMA journal_mode=WAL is persistent. If 
> a process sets WAL mode, then closes and reopens the database, the database 
> will come back in WAL mode. In contrast, if a process sets (for example) 
> PRAGMA journal_mode=TRUNCATE and then closes and reopens the database will 
> come back up in the default rollback mode of DELETE rather than the previous 
> TRUNCATE setting."
>
> Does this mean, in my case the journal mode change won't have any effect? 
> When I tested, I still see -wal & -shm files present. Will it use "DELETE" 
> journal mode for my huge transaction?
>
> Any help would be great!
> ?
> Navaneeth
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Changing WAL mode for a transaction

[Dan Kennedy]

On 04/27/2015 12:55 PM, Navaneeth K N wrote:
> Hi Dan,
>
>
>> On 27-Apr-2015, at 10:52 am, Dan Kennedy  wrote:
>>
>> On 04/26/2015 07:01 PM, Navaneeth K N wrote:
>>> Hello,
>>>
>>> My application runs the following right after opening the connection to the 
>>> database.
>>>
>>>pragma journal_mode=wal;
>>>pragma page_size=4096
>> Did executing the "PRAGMA journal_mode=wal" statement succeed? Or did it 
>> return SQLITE_BUSY or some other error code?
> Yes. It succeeded. I can see -wal files after this.

Sorry, it's early here. I meant the "PRAGMA journal_mode=delete" - did 
it succeed?







>
>
>
>>
>>
>>
>>
>>
>>
>>> When the application wants to perform a bulk data load (loading around 21Gb 
>>> of data), it runs the following before starting the transaction.
>>>
>>>  pragma journal_mode=delete
>>>
>>> This is done because I am under the assumption that WAL journal mode is not 
>>> suited for long running, big transactions (please correct me if I am wrong).
>>>
>>> But the journal mode change seems to be not affecting. Documentation for 
>>> WAL states the following:
>>>
>>> "Unlike the other journaling modes, PRAGMA journal_mode=WAL is persistent. 
>>> If a process sets WAL mode, then closes and reopens the database, the 
>>> database will come back in WAL mode. In contrast, if a process sets (for 
>>> example) PRAGMA journal_mode=TRUNCATE and then closes and reopens the 
>>> database will come back up in the default rollback mode of DELETE rather 
>>> than the previous TRUNCATE setting."
>>>
>>> Does this mean, in my case the journal mode change won't have any effect? 
>>> When I tested, I still see -wal & -shm files present. Will it use "DELETE" 
>>> journal mode for my huge transaction?
>>>
>>> Any help would be great!
>>> ?
>>> Navaneeth
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Segfault during FTS index creation from huge data

[Dan Kennedy]

On 04/29/2015 01:55 AM, Scott Robison wrote:
> On Tue, Apr 28, 2015 at 7:08 AM, Hick Gunter  wrote:
>
>> Getting "NoMem" sounds very much like a memory leak somewhere, with the
>> most likely place being your own application, followed by the wrapper you
>> are using, the FTS code and lastly the SQLite core. Lastly because the
>> SQLite core is extensively tested with an explicit emphasis on not leaking
>> memory (or other resources) in the first place and secondly recovering
>> gracefully from memory allocation failures.
>>
> I've seen the same thing from the plain old amalgamation (not sqlite.net).
> It only happens on *HUGE* (multiples of gigabytes) data sets. At least in
> my case, it was not a memory leak.
>
> It's been a couple of years since I encountered it, and I worked around it
> from the presumption that the data set used to stress test FTS was atypical
> and wouldn't be encountered in the wild. Here are the details as best as I
> can remember them:
>
> While inserting records into the FTS table, multiple FTS b-tree structures
> are created. These are not the same b-trees used in plain vanilla SQLite.
> Periodically as multiple b-trees are created and grow to some size, the
> multiple b-trees are merged into a single b-tree.
>
> This merge operation allocates chunks of memory proportionate to the size
> of the b-trees being merged. Using a contrived example that is not exact,
> just illustrative:
>
> Set of inserts until two b-trees of one megabyte each are present. Merge
> them into a two megabyte b-tree.
>
> Merge 2 2MiB trees into 1 4MiB tree.
>
> 2 x 4 MiB = 8 MiB.
>
> lather rinse repeat.
>
> 2 x 1 GiB = 2 GiB but probably fails due to overhead; if not...
>
> 2 x 2 GiB = 4 GiB but almost certainly fails due to overhead; if not...
>
> 2 x 4 GiB = 8 GiB definitely fails on a 32 bit system.
>
> In reality I never got to the point of allocating chunks of memory that
> large. The failure happened well under 2 GiB (somewhere within a few
> hundred MiB of the 1 GiB limit) due to other allocations and OS overhead.
>
> I just took a quick glance at the FTS code. As I said, it has been a couple
> years, but this looks like the malloc that was failing for me at the time:
> http://www.sqlite.org/cgi/src/artifact/81f9ed55ad586148?ln=2473

That one is allocating enough space for the doclist associated with a 
single term. Doclists are between say 2 and 12 bytes in size for each 
instance of a term in the document set. So they can get quite large for 
common terms ("a", "the" etc.). And the OP does have over a billion 
documents. So I guess if there is a fairly common term in there, that 
allocation could be too large for the OS to satisfy.










>
> Note: The data set I was using to stress test had been created by other
> team members and consisted of completely random text. Not random words, but
> random collections of letters up to dozens or maybe hundreds of letters in
> length (though there were certainly many shorter "words" in the data set).
> This resulted in a "worst case scenario" for FTS because there were
> millions of terms that were only used one or at most very few times. Very
> little doclist delta compression was possible, so the trees grew more
> quickly than they otherwise would have. Even so, it took hours of
> processing (like overnight test runs) to generate the NOMEM error. Given
> the nonsensical nature of the fake data set, I didn't report it as a
> problem at the time (I don't think; if I did, I didn't dwell on it long).
>
> What I wound up doing to support even that huge random data set was to
> split my FTS index into 53 FTS index partitions. The worst case scenario
> for me was that I might have to do up to 53 queries to gather my data and
> combine it after the fact. FTS is fast enough that I was able to do this
> without appreciable overhead. Splitting it into 53 "buckets" (a nice prime
> number that kept things relatively balanced) kept the largest FTS b-trees
> to a reasonable size so that merging wouldn't need such large allocations.
> This might not be an acceptable solution for everyone, but it worked for me.
>
> Given how merging works in FTS 3 & 4, I don't think a "simple" solution is
> available. The only one that comes to mind might be to stop merging once
> data structures grow to a certain size. Otherwise a more complicated merge
> algorithm would be necessary.
>
> If you want or need more info, I can probably reproduce a data set that
> would result in the failure. Feel free to email me, on or off list, if I
> can be of assistance.
>
> OH! One final point. I don't think I ever had a segfault because of this.
> Just a failure that prevented FTS creation progress.
>
> SDR
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Segfault during FTS index creation from huge data

[Dan Kennedy]

On 04/29/2015 02:03 AM, Dan Kennedy wrote:
> On 04/29/2015 01:55 AM, Scott Robison wrote:
>> On Tue, Apr 28, 2015 at 7:08 AM, Hick Gunter  wrote:
>>
>>> Getting "NoMem" sounds very much like a memory leak somewhere, with the
>>> most likely place being your own application, followed by the 
>>> wrapper you
>>> are using, the FTS code and lastly the SQLite core. Lastly because the
>>> SQLite core is extensively tested with an explicit emphasis on not 
>>> leaking
>>> memory (or other resources) in the first place and secondly recovering
>>> gracefully from memory allocation failures.
>>>
>> I've seen the same thing from the plain old amalgamation (not 
>> sqlite.net).
>> It only happens on *HUGE* (multiples of gigabytes) data sets. At 
>> least in
>> my case, it was not a memory leak.
>>
>> It's been a couple of years since I encountered it, and I worked 
>> around it
>> from the presumption that the data set used to stress test FTS was 
>> atypical
>> and wouldn't be encountered in the wild. Here are the details as best 
>> as I
>> can remember them:
>>
>> While inserting records into the FTS table, multiple FTS b-tree 
>> structures
>> are created. These are not the same b-trees used in plain vanilla 
>> SQLite.
>> Periodically as multiple b-trees are created and grow to some size, the
>> multiple b-trees are merged into a single b-tree.
>>
>> This merge operation allocates chunks of memory proportionate to the 
>> size
>> of the b-trees being merged. Using a contrived example that is not 
>> exact,
>> just illustrative:
>>
>> Set of inserts until two b-trees of one megabyte each are present. Merge
>> them into a two megabyte b-tree.
>>
>> Merge 2 2MiB trees into 1 4MiB tree.
>>
>> 2 x 4 MiB = 8 MiB.
>>
>> lather rinse repeat.
>>
>> 2 x 1 GiB = 2 GiB but probably fails due to overhead; if not...
>>
>> 2 x 2 GiB = 4 GiB but almost certainly fails due to overhead; if not...
>>
>> 2 x 4 GiB = 8 GiB definitely fails on a 32 bit system.
>>
>> In reality I never got to the point of allocating chunks of memory that
>> large. The failure happened well under 2 GiB (somewhere within a few
>> hundred MiB of the 1 GiB limit) due to other allocations and OS 
>> overhead.
>>
>> I just took a quick glance at the FTS code. As I said, it has been a 
>> couple
>> years, but this looks like the malloc that was failing for me at the 
>> time:
>> http://www.sqlite.org/cgi/src/artifact/81f9ed55ad586148?ln=2473
>
> That one is allocating enough space for the doclist associated with a 
> single term. Doclists are between say 2 and 12 bytes in size for each 
> instance of a term in the document set. So they can get quite large 
> for common terms ("a", "the" etc.). And the OP does have over a 
> billion documents. So I guess if there is a fairly common term in 
> there, that allocation could be too large for the OS to satisfy.

Or, really, 32-bit overflow resulting in a negative value being passed 
to sqlite3_malloc() causing the OOM report. Huh.

[sqlite] Segfault during FTS index creation from huge data

[Dan Kennedy]

On 04/29/2015 05:27 AM, Artem wrote:
> , Tim.
>
> ?? ?? 29 ?? 2015 ?., 1:21:00:
>
>> On 28 Apr 2015 at 23:14, Artem  wrote:
 How about trying the sqlite3.exe command line utility. put your
 sql for that operation in a text file, launch the program, open
 the database, then read in the sql file with the .read command.
 If the error occurs, then possibly sqlite3. if not then it is
 probably something else.
>>> I tried it and failed.
>>>
>>> Console log:
>>>
>>> f:\Suggests\test>sqlite3.exe single.db
>>> SQLite version 3.8.9 2015-04-08 12:16:33
>>> Enter ".help" for usage hints.
>>> sqlite> .read test.sql
>>> Error: near line 1: out of memory
>> That's not a segfault, though, is it.
> When I did the same in linux version of SQLite - I saw
> the "Segmentation Fault" error.

Maybe something to do with the optimistic allocation strategy Linux 
uses. Perhaps malloc() returned non-NULL but then a segfault occurred 
when it first tried to access the pages. From the man-page:

By default, Linux follows an optimistic memory allocation
strategy. This means that when malloc() returns non-NULL
there is no guarantee that the memory really is available.

Or maybe the OOM killer took out the process. Or something.

Dan.

[sqlite] Segfault during FTS index creation from huge data

[Dan Kennedy]

On 04/29/2015 03:39 PM, Scott Robison wrote:
> On windows, malloc returns null if the allocation fails. Sqlite detects
> this and returns an error.
>
> On linux, malloc may return a non null yet invalid pointer and only fail
> when the memory is accessed because it wasn't really available.

That we're getting a segfault instead of SQLITE_NOMEM is not an SQLite bug.

But that SQLite is requesting a ridiculously large allocation (assuming 
that is what is happening) is less than ideal as well though. Even if 
it's not technically a "bug".

It's just that working around the large allocation in question (if we're 
right about which one it is) is tricky to do. And tricky to test too.

Dan.




>
> If Sqlite is not at fault when posix APIs lie about file locking, I don't
> think Sqlite is responsible when malloc lies about what should be
> considered a failed memory allocation which should return null.
> On Apr 29, 2015 2:28 AM, "Simon Slavin"  wrote:
>
>> On 29 Apr 2015, at 9:21am, Scott Robison  wrote:
>>
>>> Personally I don't see it as a bug. A limitation, yes. A different
>>> algorithm that requires less ram would remove / change the limit.
>> Anything inside the SQLite code which causes a Segfault is a bug.
>> Assuming that you're not hacking your OS or messing with SQLite 'black box'
>> variables like connections and statements.
>>
>> Just my humble opinion.  I'm not on the development team.
>>
>> A minimal (either small, or simple to reproduce) repeatable example would
>> be welcome.  It can even include huge data as long as you can describe a
>> way to create crashing data like "Generate a trillion identical rows in
>> this table.".
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Changing WAL mode for a transaction

[Dan Kennedy]

On 04/29/2015 04:28 PM, Navaneeth K N wrote:
> Hi Dan,
>
>
>> On 27-Apr-2015, at 11:34 am, Dan Kennedy  wrote:
>>
>> On 04/27/2015 12:55 PM, Navaneeth K N wrote:
>>> Hi Dan,
>>>
>>>
>>>> On 27-Apr-2015, at 10:52 am, Dan Kennedy  wrote:
>>>>
>>>> On 04/26/2015 07:01 PM, Navaneeth K N wrote:
>>>>> Hello,
>>>>>
>>>>> My application runs the following right after opening the connection to 
>>>>> the database.
>>>>>
>>>>>pragma journal_mode=wal;
>>>>>pragma page_size=4096
>>>> Did executing the "PRAGMA journal_mode=wal" statement succeed? Or did it 
>>>> return SQLITE_BUSY or some other error code?
>>> Yes. It succeeded. I can see -wal files after this.
>> Sorry, it's early here. I meant the "PRAGMA journal_mode=delete" - did it 
>> succeed?
> Yes. It succeeded. Running `pragma journal_mode;` again shows delete for that 
> connection. So does that mean, all the other connections uses WAL mode and 
> just this connection will use DELETE mode for all the transactions?

If it succeeded, it should mean that the connection was able to gain 
exclusive access to the database file and switch the database back to 
rollback (non-WAL) mode. The *-wal and *-shm files should have been 
deleted when the command returns.

Dan.

[sqlite] Segfault during FTS index creation from huge data

[Dan Kennedy]

On 04/30/2015 07:41 AM, Scott Robison wrote:
>
> I wrote up some test code today that definitely forces a SQLITE_NOMEM
> condition, and there are no leaks (though there are a couple really large
> outstanding allocations to force an error without needing to insert
> gigabytes of data).
>
> In thinking about what Dan wrote last night (many short word matches) I
> decided that maybe my supposition was wrong and it wasn't the number of
> relatively unique words in my old data set. Using the FTS4AUX virtual
> table, I recall looking at the info in the DB at the time and seeing a huge
> number of words with 1 or very few matches, and assumed that was the
> problem in that data set. However, given the random nature of that data
> set, and the fact that there are only 26 single letter "words" and 676
> double letter "words" (and so on), I could have easily missed the
> relatively few rows of data that had very large numbers of docids /
> occurrences in the aux table output.
>
> My test app goes to the other extreme. It inserts as many rows as possible
> consisting of the single letter word "a" 256 times, and in my case, it
> fails after 1,052,641 rows were inserted (1,048,576 rows were committed).
>
> In any case, my memory of the "merging" of things was correct, though the
> precise location may not have been.
>
> Notes:
>
> 1. Depending on how much memory pressure I put on the system by
> pre-allocating even more big chunks of memory, the NOMEM error moves around
> a bit. I've seen it happen in a malloc as well.
>
> 2. The reality is that FTS was designed around certain assumptions, and
> these extra large data sets don't fit into those cases. In my case, the
> only time I've seen errors was due to synthetic / unrealistic test data.
> The exponential growth related to segment directory merges seems to dictate
> that eventually, after a lot of inserts / updates, the data structures are
> going to get quite large.

Thanks for doing this. I'll look at it properly later on today to see if 
the results suggest anything we can do.

I'm thinking there's another problem though. At some point soon, we run 
into this:

   https://www.sqlite.org/limits.html#max_length

The default limit is 10^9 bytes.

The fully-merged doclist generated for the term "a" in the above is 
probably around 256MiB in size. So if we could successfully merge it in 
memory, it could be inserted into the database. However, once you get up 
over a billion records there might be doclists for common terms that 
exceed this limit.

Dan.








>
> 3. One possible change that might have an impact for Artem: right now the
> FTS_MERGE_COUNT is set to 16. 16 also seems to be hard coded in FTS in a
> few places, though I could be mistaken and the instances of "16" as a magic
> number could be coincidence. Regardless, I wonder if a different value of
> FTS_MERGE_COUNT might tweak the system so that it takes a lot longer for
> Artem to encounter the problem.
>
> Finally, the details:
>
> In this exact case, the call stack looks as follows at the time of the
> SQLITE_NOMEM error code:
>
> sqlite3MemRealloc, line 17109, sqlite3-1.c
> sqlite3Realloc, line 20996, sqlite3-1.c
> sqlite3_realloc, line 21022, sqlite3-1.c
> sqlite3Fts3SegReaderStep, line 2946, sqlite3-6.c (attempting to realloc to
> 135,265,788 bytes)
> fts3SegmentMerge, line 3214, sqlite3-6.c
> fts3AllocateSegdirIdx, line 1150, sqlite3-6.c
> fts3SegmentMerge, line 3199, sqlite3-6.c
> fts3AllocateSegdirIdx, line 1150, sqlite3-6.c
> fts3SegmentMerge, line 3199, sqlite3-6.c
> sqlite3Fts3PendingTermsFlush, line 3252, sqlite3-6.c
> fts3PendingTermsDocid, line 878, sqlite3-6.c
> sqlite3Fts3UpdateMethod, line 5619, sqlite3-6.c
> fts3UpdateMethod, line 21701, sqlite3-5.c
> sqlite3VdbeExec, line 24064, sqlite3-3.c
> sqlite3Step, line 16367, sqlite3-3.c
> sqlite3_step, line 16434, sqlite3-3.c
> main, line 68: if (chk(sqlite3_step(stmt)) != SQLITE_DONE)
>
> Here is my test code (C++, compiled with Visual C++ 2010, though I'm quite
> certain that is not the problem):
>
> #include 
> #include 
> #include 
> #include 
>
> #include "sqlite3.h"
>
> sqlite3* db = nullptr;
>
> int chk(int errcode)
> {
> if ((errcode > 0) && (errcode < 100))
> {
> int ext_errcode = sqlite3_extended_errcode(db);
> const char* p = sqlite3_errmsg(db);
> if (!p || !*p)
> p = "{missing errmsg}";
> std::ostringstream oss;
> oss << ext_errcode << '-' << p;
> throw oss.str();
> }
>
> return errcode;
> }
>
> int main()
> {
> unsigned long long ull = 0;
>
> // allocate a bunch of memory to put pressure on malloc
> std::vector allocs;
> allocs.push_back(new char[1024*1024*1024]);
> allocs.push_back(new char[256*1024*1024]);
> //allocs.push_back(new char[128*1024*1024]);
> //allocs.push_back(new char[64*1024*1024]);
>
> try
> {
> chk(sqlite3_open("test.db", &db));
> chk(sqlite3_exec(db, "BEGIN", nullptr, nullptr, nullptr));
> chk(sqlite3_exec(db, "CREATE VIRTUAL TABLE IF NOT EXISTS data USING
> fts4();", nullptr, nullptr, nullptr));
>
> sqlite3_

[sqlite] Segfault during FTS index creation from huge data

[Dan Kennedy]

On 04/30/2015 02:08 PM, Dominique Pell? wrote:
> Dan Kennedy  wrote:
>
>> On 04/30/2015 07:41 AM, Scott Robison wrote:
>>>
>>> I wrote up some test code today that definitely forces a SQLITE_NOMEM
>>> condition, and there are no leaks (though there are a couple really large
>>> outstanding allocations to force an error without needing to insert
>>> gigabytes of data).
>>>
>>> In thinking about what Dan wrote last night (many short word matches) I
>>> decided that maybe my supposition was wrong and it wasn't the number of
>>> relatively unique words in my old data set. Using the FTS4AUX virtual
>>> table, I recall looking at the info in the DB at the time and seeing a
>>> huge
>>> number of words with 1 or very few matches, and assumed that was the
>>> problem in that data set. However, given the random nature of that data
>>> set, and the fact that there are only 26 single letter "words" and 676
>>> double letter "words" (and so on), I could have easily missed the
>>> relatively few rows of data that had very large numbers of docids /
>>> occurrences in the aux table output.
>>>
>>> My test app goes to the other extreme. It inserts as many rows as possible
>>> consisting of the single letter word "a" 256 times, and in my case, it
>>> fails after 1,052,641 rows were inserted (1,048,576 rows were committed).
>>>
>>> In any case, my memory of the "merging" of things was correct, though the
>>> precise location may not have been.
>>>
>>> Notes:
>>>
>>> 1. Depending on how much memory pressure I put on the system by
>>> pre-allocating even more big chunks of memory, the NOMEM error moves
>>> around
>>> a bit. I've seen it happen in a malloc as well.
>>>
>>> 2. The reality is that FTS was designed around certain assumptions, and
>>> these extra large data sets don't fit into those cases. In my case, the
>>> only time I've seen errors was due to synthetic / unrealistic test data.
>>> The exponential growth related to segment directory merges seems to
>>> dictate
>>> that eventually, after a lot of inserts / updates, the data structures are
>>> going to get quite large.
>>
>> Thanks for doing this. I'll look at it properly later on today to see if the
>> results suggest anything we can do.
>>
>> I'm thinking there's another problem though. At some point soon, we run into
>> this:
>>
>>https://www.sqlite.org/limits.html#max_length
>>
>> The default limit is 10^9 bytes.
>>
>> The fully-merged doclist generated for the term "a" in the above is probably
>> around 256MiB in size. So if we could successfully merge it in memory, it
>> could be inserted into the database. However, once you get up over a billion
>> records there might be doclists for common terms that exceed this limit.
>>
>> Dan.
>
> What about FTS5? I understand that it will use less memory than
> FTS3/FTS4. Will it solve this problem?

In theory, yes. But I haven't actually tried it yet.

Dan.

[sqlite] Bug in sqlite3_trace/trigger/delete

[Dan Kennedy]

On 07/31/2015 08:34 PM, sqlite-mail wrote:
> Hello  !
>   
> I'm using sqlite for a project and with this specific database
> https://dev.dadbiz.es/tmp/odoo.db.zip (12MB compressed / 38MB uncompressed)
> this is happening:
>   
> -1 Registering an sqlite3_trace function when trying to delete a record just
> inserted on the table "res_users" the registered sqlite3_trace function is
> called lots of times and sometimes it segfaults (I think stack overflow), I
> think it enters in a unintended loop.

If I build the test program below using the command line provided with 
SQLite 3.8.11.1 and then run it against the oodo.db file from the link 
above, I get a single line of output:

   SQL: DELETE FROM res_users WHERE id=7

No errors under valgrind. Is this the expected result? Does it mean bug 
(1) has been fixed already?

Dan.




>   
> -2 If we do a dump "sqlite3 odoo.db .dump > odoo.db.sql" and try to recreate
> the database with "sqlite3 new-odoo.db < odoo.db.sql" we get errors for
> tables/views declarations out of order (trying to create a view
> https://www.endad.eu/tmp/odoo.db.zipthat refer to other views not yet
> created).
>   
> Attached there is the simple "C" test file with a shell file to make it with
> the flags I use on this project.
>   
> This database uses a lot of foreign keys.
>   
> The trigger on the "res_users" table is very simple:
> -
> BEFORE DELETE ON "res_users"
> BEGIN
>  SELECT RAISE(ABORT, 'Can not remove root/admin user!')
>  WHERE OLD.id = 1;
> END;
> -
>   
> I've also tested with a fresh sqlite3.c/h from
> https://www.sqlite.org/snapshot/sqlite-amalgamation-201507231639.zip.
>   
> When tested with a single table with the above trigger with a fresh database
> the test program behaves as expected.
> -
> CREATE TABLE IF NOT EXISTS tbl(id  INTEGER PRIMARY KEY, name varchar);
> INSERT OR IGNORE INTO tbl(id, name) VALUES
> (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
> CREATE TRIGGER IF NOT EXISTS tbl_delete_trigger BEFORE DELETE ON tbl
> BEGIN
>  SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE OLD.id =
> 1;
> END;
> -
>   
>   
> I found a small test that shows how to create a database that after ".dump"
> will not properly be restored.
>   
> After writing to you about this bug with sqlite3_trace/trigger I start
> thinking what I did with this database (odoo.db) that could possibly make
> it's ".dump" not usable to restore and I found the reason.
>   
> On that database I was constantly droping and recreating tables/views with
> slight different fields and that seems what makes sqlite3 get lost.
>   
> Example that creates a database not correctly restorable:
> 
> begin;
> create table if not exists tbl(id integer primary key, name varchar);
> insert or ignore into tbl(id, name) values (1,'a'), (2, 'b');
> create view if not exists tbl_view as select * from tbl;
> create view if not exists tbl_view_view as select * from tbl_view;
> drop view if exists tbl_view;
> create view if not exists tbl_view as select * from tbl;
> end;
> 
>   
> After creating a database with the above sql we get the following from
> ".dump":
> 
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE tbl(id integer primary key, name varchar);
> INSERT INTO "tbl" VALUES(1,'a');
> INSERT INTO "tbl" VALUES(2,'b');
> CREATE VIEW tbl_view_view as select * from tbl_view;--<< here we
> are trying to create a view on another view not yet created
> CREATE VIEW tbl_view as select * from tbl;
> COMMIT;
> 
>   
> On the ".dump"/".restore" problem it seems that sqlite3 shell rely on
> sqlite3_master rowid order to perform the ".dump" and when we drop/recreate a
> table/view that other tables/views depends sqlite3 do not detect it and
> simply add a new entry at the end of sqlite3_master.
>   
>   
>  shell script to make the bug test program
> MYINC=$HOME/dev/sqlite3
> #MYINC=.
>   
> gcc \
>  -DTHREADSAFE=1 \
>  -DSQLITE_DEFAULT_FILE_FORMAT=4 \
>  -DSQLITE_DEFAULT_AUTOVACUUM=1 \
>  -DSQLITE_DEFAULT_FOREIGN_KEYS=  1 \
>  -DSQLITE_ENABLE_COLUMN_METADATA=1 \
>  -DSQLITE_ENABLE_FTS4=1 \
>  -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \
>  -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \
>  -DSQLITE_ENABLE_RTREE=1 \
>  -DSQLITE_ENABLE_STAT4=1 \
>  -DSQLITE_OMIT_TCL_VARIABLE=1 \
>  -DSQLITE_USE_URI=1 \
>  -DSQLITE_SOUNDEX=1\
>  -o test-sqlite-bug test-sqlite-bug.c -I $MYINC $MYINC/sqlite3.c
> -lpthread -lm -ldl
> -
> - test-sqlite-bug.c
> #include 
> #include "sqlite3.h"
>   
> static const char test_sql[] =
>  "CREATE TABLE IF NOT EXISTS tbl(id  INTEGER PRIMARY KEY, name
> varchar);"
>  "INSERT OR IGNORE INTO tbl(id, name) VALUES "
>  "(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');"
>  "CREATE TRIGGER IF NOT EXISTS tbl_delete_trigger BEFORE DELETE ON tbl
> "
>  "BEGIN"
>  " SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE
> OLD.id = 1;"
>  "END;";
>

[sqlite] Bug in sqlite3_trace/trigger/delete

[Dan Kennedy]

On 08/03/2015 07:24 PM, sqlite-mail wrote:
>
> Hello Dan !
>
> I downloaded again
> http://www.sqlite.org/2015/sqlite-amalgamation-3081101.zip just and
> recompiled it and running I still get the same 1000 lines of the
> trigger call.
>
> My environment is ubuntu 14.04 and I'm using gcc 4.9.2 and now I also
> compiled with clang 3.6 and I've got the same 1000 lines of trigger call.
>
> As you can see from the test program there is not much else than
> sqlite3 and the database.
>
> I also tested before on OS X with the same result, but I can not test
> it again now there, later on I'll do it.
>
> What environment are you testing ? I mean os, compiler, ...
>

64-bit Linux with gcc 4.7.1.

Are we using the same database? Before running the test program, I get:

  $ sha1sum ./odoo.db
  0f5ac7aa291a9c149cf36bbc6ac4c73a90774c97  ./odoo.db

and

  $ sqlite3 ./odoo.db "SELECT count(*) FROM res_users WHERE id = 7;"
  0

Is this as expected?

Dan.



> Cheers !
>
> Mon Aug 03 2015 13:08:08 CEST from "Dan Kennedy"
>  Subject: Re: [sqlite] Bug in
> sqlite3_trace/trigger/delete
> On 07/31/2015 08:34 PM, sqlite-mail wrote:
>
> Hello !
>
> I'm using sqlite for a project and with this specific database
> https://dev.dadbiz.es/tmp/odoo.db.zip (12MB compressed / 38MB
> uncompressed)
> this is happening:
>
> -1 Registering an sqlite3_trace function when trying to delete
> a record just
> inserted on the table "res_users" the registered sqlite3_trace
> function is
> called lots of times and sometimes it segfaults (I think stack
> overflow), I
> think it enters in a unintended loop.
>
> If I build the test program below using the command line provided
> with
> SQLite 3.8.11.1 and then run it against the oodo.db file from the
> link
> above, I get a single line of output:
>
> SQL: DELETE FROM res_users WHERE id=7
>
> No errors under valgrind. Is this the expected result? Does it
> mean bug
> (1) has been fixed already?
>
> Dan.
>
>
>
>
>
> -2 If we do a dump "sqlite3 odoo.db .dump > odoo.db.sql" and
> try to recreate
> the database with "sqlite3 new-odoo.db < odoo.db.sql" we get
> errors for
> tables/views declarations out of order (trying to create a view
> https://www.endad.eu/tmp/odoo.db.zipthat refer to other views
> not yet
> created).
>
> Attached there is the simple "C" test file with a shell file
> to make it with
> the flags I use on this project.
>
> This database uses a lot of foreign keys.
>
> The trigger on the "res_users" table is very simple:
> -
> BEFORE DELETE ON "res_users"
> BEGIN
> SELECT RAISE(ABORT, 'Can not remove root/admin user!')
> WHERE OLD.id = 1;
> END;
> -
>
> I've also tested with a fresh sqlite3.c/h from
> https://www.sqlite.org/snapshot/sqlite-amalgamation-201507231639.zip.
>
> When tested with a single table with the above trigger with a
> fresh database
> the test program behaves as expected.
> -
> CREATE TABLE IF NOT EXISTS tbl(id INTEGER PRIMARY KEY, name
> varchar);
> INSERT OR IGNORE INTO tbl(id, name) VALUES
> (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
> CREATE TRIGGER IF NOT EXISTS tbl_delete_trigger BEFORE DELETE
> ON tbl
> BEGIN
> SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE
> OLD.id =
> 1;
> END;
> -
>
>
> I found a small test that shows how to create a database that
> after ".dump"
> will not properly be restored.
>
> After writing to you about this bug with sqlite3_trace/trigger
> I start
> thinking what I did with this database (odoo.db) that could
> possibly make
> it's ".dump" not usable to restore and I found the reason.
>
> On that database I was constantly droping and recreating
> tables/views with
> slight different fields and that seems what makes sqlite3 get
> lost.
>
> Example that creates a database not correctly restorable:
> 
> begin;
> create table if not exists tbl(id integer primary key, name
> 

[sqlite] MMAP performance with databases over 2GB

[Dan Kennedy]

On 08/06/2015 09:53 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> In my testing I am finding that using SQLite's mmap support with
> larger databases (final db size is ~10GB) to create the database (WAL
> mode) is considerably (about three times) slower than no mmap.
>
> The default max mmap limit is 2GB (sadly).  Has anyone else tested
> mmap with >2GB databases and have results?  I don't know if this is a
> peculiarity of my data set, or because of how SQLite is implemented.

Is it using more CPU cycles in mmap mode or just taking longer? If the 
former, does [time] attribute them to "user" or "system"?

How large are you letting the wal file grow between checkpoints?

Dan.




>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1
>
> iEYEARECAAYFAlXDdQMACgkQmOOfHg372QRt3wCeKQpP9g2OhWS2yJg+iU7Gxvxo
> TPUAn3ikDarecOaKLPIsnS3Xv+IltreU
> =yUxV
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MMAP performance with databases over 2GB

[Dan Kennedy]

On 08/07/2015 12:35 AM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 08/06/2015 09:27 AM, Dan Kennedy wrote:
>>> Is it using more CPU cycles in mmap mode or just taking longer?
>>> If the former, does [time] attribute them to "user" or "system"?
> It is taking longer.  I have 3 XML dumps which I turn into JSON
> (incrementally) and then denormalise and insert into SQLite across
> several tables.  While all this work is going on, the code prints out
> statistics about how long it is running and about how many records per
> second are being processed.  The final database size after commit and
> vacuum is ~8GB.  There are a lot of foreign keys too, although all
> simple (referring to an INTEGER PRIMARY KEY column).
>
> I gave mmap a try - ie the *only* change was to add an extra pragma
> before the beginning of the transaction:
>
> "pragma mmap_size="+str(2*1024*1024*1024)
>
> In hard numbers, without that line I was doing ~1,118 records per
> second and with it it does ~300.  A normal run takes about 1h20m but
> the mmap one was still running 3 hours later when I aborted it.
>
> (BTW this is all on a tmpfs filesystem on 64 bit Linux with swap
> spread across two ssds, and 32GB of ram.  ie the actual storage
> hardware isn't a factor.  Also single threaded because XML.)


When the b-tree layer requests a page reference in mmap mode, SQLite 
first needs to figure out whether it should use regular in-memory page 
(data cached in heap memory) or a mmap page (data is actually a pointer 
into mmap'd address space). If a write-transaction is open, it cannot 
use a mmap page if:

   1) there is an entry for the requested page in the wal file, or
   2) there is an entry (possibly a dirty one) for the requested page in 
the cache.

If the wal file is really large, as in this case, then test (1) can be 
quite slow.

One interesting thing is that the wal-file lookup is done before the 
cache lookup. Which doesn't seem quite right. Source code archeology and 
testing have failed to reveal why it is that way. The branch here swaps 
the two tests around:

   http://www.sqlite.org/src/info/3a82c8e6cb7227fe

Does that improve performance any in your case?

Thanks,
Dan.









>
>>> How large are you letting the wal file grow between checkpoints?
> Pretty much the entire database size.  For the tests I was starting
> with a deleted database directory (ie no pre-existing files), and then
> doing these pragmas:
>
>"pragma page_size=4096",
> # "pragma mmap_size="+str(2*1024*1024*1024),
>"pragma journal_mode=wal",
>"pragma wal_autocheckpoint=1",
>"pragma foreign_keys=on",
>
> Then I start a transaction, and do the importing within that
> transaction.  The database file is 4kb during that process, the wal
> file gets to be about 10GB.
>
> If I use gdb to periodically break into the running process in the
> mmap case, then it was always in sqlite3WalFindFrame.
>
> I don't need any help fixing my importing process (eg don't need a
> journal on an empty database anyway).  But it is frustrating that mmap
> only goes up to a few kb shy of 2GB even for 64 bit, and I have one
> example (ie anecdote not data) showing that mmap hurts for inserts on
>> 2GB databases.  Perhaps it is worth others testing to see if this
>> is
> a systemic problem, or just bad luck for me :-)
>
> It may also be relevant that tables add columns over time.  I
> dynamically add them after encountering previously unseen fields in
> the JSON.  However I'd expect the schema to be final a few thousand
> records in.  Most tables have 3 to 6 million records.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1
>
> iEYEARECAAYFAlXDmukACgkQmOOfHg372QSVtgCbBihGgIuZqS3Yy2JARXZ1+Q59
> GmwAoMG53XxuLNhcMIw1PV46fD/Z/5tT
> =luXx
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite crash

[Dan Kennedy]

On 08/08/2015 04:11 AM, Robert Weiss wrote:
> I observed the sqlite command shell version 3.8.11.1 to crash (exit to the 
> OSwithout an error message) while running in a Cygwin shell under Windows 7 
> when I tried to create anindex.  The source was compiled by gcc 4.9.2.  The 
> same type of crashhappened when I tried the operation from a Lua script 
> linked to the same objectlibrary.
>
>
>   
> Here are the DDL statements entered previous to the crash:
>
>
>   
> CREATE TABLEd200_on_passport(fn,path,size,serial,pid);
>
> CREATE INDEX d200_on_passport_serial ond200_on_passport(serial);
>
> CREATE VIEW d200 as select * fromd200_on_passport;
>
>
>   
> And here is the statement thatcaused the crash:
>
> create index d200_on_passport_fn ond200_on_passport(fn);
>
>
>   
> The crash didn?t happen when Itried the CREATE INDEX statement on a test 
> database with the same ddlstatements but containing no data.
>
>
> The compressed version of the database that illustrates the problem is a 
> little smaller than 2 MB.  It contains some semi-personal information (it's 
> part of an attempt to organize my photo library; pathnames hint at where I've 
> been on vacation and so on, but the database contains no images) and I'd 
> prefer not to post it to a list, but I can send it for use by those tracking 
> down the bug.


Can you send the database to me? Or to drh if that seems more prudent to 
you.

Thanks,
Dan.





>
> Robert Weiss
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite crash

[Dan Kennedy]

On 08/11/2015 12:23 AM, Robert Weiss wrote:
> Dan Kennedy--
> What address, precisely, should I use to send the database to you?  When I 
> "reply" to your message I get the whole SQLite discussion group, which is 
> what I want to avoid (no offense intended, guys and gals).
> BTW, the problem does not seem to occur in version 3.8.10.2.
> Robert Weiss

Thanks for the database. We couldn't reproduce the problem here though.

The crash is repeatable, correct? Are you able to capture a stack trace 
from the command line shell with gdb?

Thanks,
Dan.


>
>
>       On Friday, August 7, 2015 11:31 PM, Dan Kennedy  gmail.com> wrote:
> 
>
>   On 08/08/2015 04:11 AM, Robert Weiss wrote:
>> I observed the sqlite command shell version 3.8.11.1 to crash (exit to the 
>> OSwithout an error message) while running in a Cygwin shell under Windows 7 
>> when I tried to create anindex.  The source was compiled by gcc 4.9.2.  The 
>> same type of crashhappened when I tried the operation from a Lua script 
>> linked to the same objectlibrary.
>>
>>
>>
>> Here are the DDL statements entered previous to the crash:
>>
>>
>>
>> CREATE TABLEd200_on_passport(fn,path,size,serial,pid);
>>
>> CREATE INDEX d200_on_passport_serial ond200_on_passport(serial);
>>
>> CREATE VIEW d200 as select * fromd200_on_passport;
>>
>>
>>
>> And here is the statement thatcaused the crash:
>>
>> create index d200_on_passport_fn ond200_on_passport(fn);
>>
>>
>>
>> The crash didn?t happen when Itried the CREATE INDEX statement on a test 
>> database with the same ddlstatements but containing no data.
>>
>>
>> The compressed version of the database that illustrates the problem is a 
>> little smaller than 2 MB.  It contains some semi-personal information (it's 
>> part of an attempt to organize my photo library; pathnames hint at where 
>> I've been on vacation and so on, but the database contains no images) and 
>> I'd prefer not to post it to a list, but I can send it for use by those 
>> tracking down the bug.
>
> Can you send the database to me? Or to drh if that seems more prudent to
> you.
>
> Thanks,
> Dan.
>
>
>
>
>
>> Robert Weiss
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite crash

[Dan Kennedy]

On 08/12/2015 02:15 AM, Robert Weiss wrote:
> The bug seems to be repeatable.  At least, it happened again today.
> I haven't used gdb in a long time.  Here's my first crack at it; what else 
> should I do?
> $ gdb sqliteGNU gdb (GDB) 7.8Copyright (C) 2014 Free Software Foundation, 
> Inc.License GPLv3+: GNU GPL version 3 or later 
> <http://gnu.org/licenses/gpl.html>This is free software: you are free to 
> change and redistribute it.There is NO WARRANTY, to the extent permitted by 
> law.  Type "show copying"and "show warranty" for details.This GDB was 
> configured as "i686-pc-cygwin".Type "show configuration" for configuration 
> details.For bug reporting instructions, please 
> see:<http://www.gnu.org/software/gdb/bugs/>.Find the GDB manual and other 
> documentation resources online 
> at:<http://www.gnu.org/software/gdb/documentation/>.For help, type 
> "help".Type "apropos word" to search for commands related to "word"...Reading 
> symbols from sqlite...done.(gdb) run /cygdrive/r/errd200.dbStarting program: 
> /usr/local/bin/sqlite /cygdrive/r/errd200.db[New Thread 12300.0x294c][New 
> Thread 12300.0x32f0][New Thread 12300.0x3530][New Thread 12300.0x328c][New 
> Thread 12300.0x389c]SQLite version 3.8.11.1 2015-07-29 20:00:57Enter ".help" 
> for usage hints.sqlite> create index d200_on_passport_fn on 
> d200_on_passport(fn);[New Thread 12300.0xa64]gdb: unknown target exception 
> 0x8001 at 0x74d66d61
> Program received signal ?, Unknown signal.[Switching to Thread 
> 12300.0xa64]0x74d66d61 in sysfer!FirstHookFunc () from 
> /cygdrive/c/Windows/SysWOW64/SYSFER.DLL(gdb) bt#0  0x74d66d61 in 
> sysfer!FirstHookFunc () from /cygdrive/c/Windows/SysWOW64/SYSFER.DLL#1  
> 0x in ?? ()

What does typing this command at the (gdb) prompt after the crash output?

   thread apply all where 15

Or, if that doesn't work, just "where 15".

Thanks,
Dan.



>
>
>   On Tuesday, August 11, 2015 1:43 AM, Dan Kennedy  gmail.com> wrote:
> 
>
>   On 08/11/2015 12:23 AM, Robert Weiss wrote:
>> Dan Kennedy--
>> What address, precisely, should I use to send the database to you?  When I 
>> "reply" to your message I get the whole SQLite discussion group, which is 
>> what I want to avoid (no offense intended, guys and gals).
>> BTW, the problem does not seem to occur in version 3.8.10.2.
>> Robert Weiss
> Thanks for the database. We couldn't reproduce the problem here though.
>
> The crash is repeatable, correct? Are you able to capture a stack trace
> from the command line shell with gdb?
>
> Thanks,
> Dan.
>
>
>>
>>On Friday, August 7, 2015 11:31 PM, Dan Kennedy > gmail.com> wrote:
>>  
>>
>>On 08/08/2015 04:11 AM, Robert Weiss wrote:
>>> I observed the sqlite command shell version 3.8.11.1 to crash (exit to the 
>>> OSwithout an error message) while running in a Cygwin shell under Windows 7 
>>> when I tried to create anindex.  The source was compiled by gcc 4.9.2.  The 
>>> same type of crashhappened when I tried the operation from a Lua script 
>>> linked to the same objectlibrary.
>>>
>>>
>>>  
>>> Here are the DDL statements entered previous to the crash:
>>>
>>>
>>>  
>>> CREATE TABLEd200_on_passport(fn,path,size,serial,pid);
>>>
>>> CREATE INDEX d200_on_passport_serial ond200_on_passport(serial);
>>>
>>> CREATE VIEW d200 as select * fromd200_on_passport;
>>>
>>>
>>>  
>>> And here is the statement thatcaused the crash:
>>>
>>> create index d200_on_passport_fn ond200_on_passport(fn);
>>>
>>>
>>>  
>>> The crash didn?t happen when Itried the CREATE INDEX statement on a test 
>>> database with the same ddlstatements but containing no data.
>>>
>>>
>>> The compressed version of the database that illustrates the problem is a 
>>> little smaller than 2 MB.  It contains some semi-personal information (it's 
>>> part of an attempt to organize my photo library; pathnames hint at where 
>>> I've been on vacation and so on, but the database contains no images) and 
>>> I'd prefer not to post it to a list, but I can send it for use by those 
>>> tracking down the bug.
>> Can you send the database to me? Or to drh if that seems more prudent to
>> you.
>>
>> Thanks,
>> Dan.
>>
>>
>>
>>
>>
>>> Robert Weiss
>>> 

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

[Dan Kennedy]

On 08/20/2015 12:38 PM, David Waters wrote:
> I have a large FTS4 table (around 200 million rows and growing).  A simple
> query (SELECT * FROM main WHERE main MATCH 'data') returns in less than a
> second.  However, if an ORDER BY is added (SELECT * FROM main WHERE main
> MATCH 'data' ORDER BY udate) it will never return (after 20 mins, I
> canceled the query).
>
> EXPLAIN QUERY PLAN on the ORDER BY statement shows 'USE TEMP B-TREE FOR
> ORDER BY'.  Shouldn't it attempt to use the available FTS Index for ORDER
> BY?

I don't see how it could. The FTS index is not a list of rows sorted by 
udate.

> If not, Is there another method (or work around) to get the data back
> in order?

You could use an external content FTS index. Then put a regular index on 
the udate column of your external content table and query it directly 
for non-fulltext queries.

   https://www.sqlite.org/fts3.html#section_6_2_2

Dan.

[sqlite] When sqlite3_close() returns SQL_BUSY

[Dan Kennedy]

On 08/21/2015 12:30 PM, Jeff M wrote:
> Sometimes my iOS app creates an unreasonable number of prepared statements 
> (perhaps 1,000, an app bug that I'm fixing).  These prepared statements are 
> later finalized just prior to doing sqlite3_close(), which sometimes returns 
> SQL_BUSY.  The docs say SQL_BUSY will be returned if I haven't finalized all 
> prepared statements, but I believe I have done so.  My iOS app has only one 
> connection to the DB and I'm doing all this work on the main thread.
>
> 1.  The docs don't say what to do in the case of SQL_BUSY.  Does that mean 
> I've certainly failed to finalize one or more prepared statements, or does 
> SQLite just need more time (in which case can I loop on sqlite3_close() until 
> I get SQLITE_OK)?
>
> 2.  Does SQLite keep a record of prepared statements?  If so, is there a way 
> I can ask SQLite to close them all, or are there any tricks to debugging 
> which statements have not been finalized?

It does. Here:

   https://www.sqlite.org/c3ref/next_stmt.html

If sqlite3_close() returns SQLITE_BUSY, this can be used to find the 
unfinalized statements. Sometimes using sqlite3_sql() on the statement 
handles helps to determine where the leak occurred.

Dan.

[sqlite] Potential for Segmentation Violation/Fault in sqlite 3.8.11.1

[Dan Kennedy]

On 08/25/2015 11:36 PM, Bill Parker wrote:
> Hello All,
>
>  In reviewing source code files in sqlite 3.8.11.1, I found some
> instances of calls to Tcl_Alloc() which are not checked for a return
> value of NULL, indicating failure in directory '/tea/generic', file
> 'tclsqlite3.c'.  Additionally, in the event of failure, there are
> some cases where memset()/memcpy() is called after Tcl_Alloc(), but
> in the event that Tcl_Alloc() returns NULL, memset()/memcpy() will
> generate a segmentation fault/violation if memset()/memcpy() is called
> with a address location pointing to NULL (see test program below
> the patch file).
>
> The patch file below should catch and handle all conditions where
> Tcl_Alloc() is called, but are NOT checked for a return value of NULL:

Does Tcl_Alloc() actually return NULL if a malloc fails? I thought if 
memory can not be allocated it calls Tcl_Panic() to report an error 
message and then aborts the process.

Dan.



>
> ===
>
> --- tclsqlite3.c.orig2015-08-22 18:50:01.65600 -0700
> +++ tclsqlite3.c2015-08-22 19:12:05.71600 -0700
> @@ -380,6 +380,10 @@
> }
>
> p = (IncrblobChannel *)Tcl_Alloc(sizeof(IncrblobChannel));
> +  if( !p ){
> +Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
> +return TCL_ERROR;
> +  }
> p->iSeek = 0;
> p->pBlob = pBlob;
>
> @@ -439,6 +443,10 @@
> SqlFunc *p, *pNew;
> int nName = strlen30(zName);
> pNew = (SqlFunc*)Tcl_Alloc( sizeof(*pNew) + nName + 1 );
> +  if( !pNew ){
> +Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
> +return NULL;  /*  what should be returned here? */
> +  }
> pNew->zName = (char*)&pNew[1];
> memcpy(pNew->zName, zName, nName+1);
> for(p=pDb->pFunc; p; p=p->pNext){
> @@ -1168,6 +1176,10 @@
>   nVar = sqlite3_bind_parameter_count(pStmt);
>   nByte = sizeof(SqlPreparedStmt) + nVar*sizeof(Tcl_Obj *);
>   pPreStmt = (SqlPreparedStmt*)Tcl_Alloc(nByte);
> +if( !pPreStmt ){
> +  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
> +  return TCL_ERROR;
> +}
>   memset(pPreStmt, 0, nByte);
>
>   pPreStmt->pStmt = pStmt;
> @@ -1177,6 +1189,11 @@
>   #ifdef SQLITE_TEST
>   if( pPreStmt->zSql==0 ){
> char *zCopy = Tcl_Alloc(pPreStmt->nSql + 1);
> +  if( !zCopy ) {
> +Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
> +Tcl_Free(pPreStmt);
> +return TCL_ERROR;
> +  }
> memcpy(zCopy, zSql, pPreStmt->nSql);
> zCopy[pPreStmt->nSql] = '\0';
> pPreStmt->zSql = zCopy;
> @@ -1372,6 +1389,10 @@
>   p->nCol = nCol = sqlite3_column_count(pStmt);
>   if( nCol>0 && (papColName || p->pArray) ){
> apColName = (Tcl_Obj**)Tcl_Alloc( sizeof(Tcl_Obj*)*nCol );
> +  if( !apColName ){
> +Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
> +return;
> +  }
> for(i=0; i   apColName[i] = Tcl_NewStringObj(sqlite3_column_name(pStmt,i), -1);
>   Tcl_IncrRefCount(apColName[i]);
> @@ -1715,6 +1736,10 @@
> zAuth = Tcl_GetStringFromObj(objv[2], &len);
> if( zAuth && len>0 ){
>   pDb->zAuth = Tcl_Alloc( len + 1 );
> +if( !pDb->zAuth ){
> +  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
> +  return TCL_ERROR;
> +}
>   memcpy(pDb->zAuth, zAuth, len+1);
> }else{
>   pDb->zAuth = 0;
> @@ -1804,6 +1829,10 @@
> zBusy = Tcl_GetStringFromObj(objv[2], &len);
> if( zBusy && len>0 ){
>   pDb->zBusy = Tcl_Alloc( len + 1 );
> +if( !pDb->zBusy ){
> +  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
> +  return TCL_ERROR;
> +}
>   memcpy(pDb->zBusy, zBusy, len+1);
> }else{
>   pDb->zBusy = 0;
> @@ -1970,6 +1999,10 @@
> zCommit = Tcl_GetStringFromObj(objv[2], &len);
> if( zCommit && len>0 ){
>   pDb->zCommit = Tcl_Alloc( len + 1 );
> +if( !pDb->zCommit ){
> +  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
> +  return TCL_ERROR;
> +}
>   memcpy(pDb->zCommit, zCommit, len+1);
> }else{
>   pDb->zCommit = 0;
> @@ -2315,6 +2348,10 @@
> Tcl_IncrRefCount(pScript);
>
> p = (DbEvalContext *)Tcl_Alloc(sizeof(DbEvalContext));
> +  if( !p ){
> +Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
> +return TCL_ERROR;
> +  }
> dbEvalInit(p, pDb, objv[2], pArray);
>
> cd2[0] = (void *)p;
> @@ -2458,6 +2495,10 @@
> }
> if( zNull && len>0 ){
>   pDb->zNull = Tcl_Alloc( len + 1 );
> +if( !pDb->zNULL ){
> +  Tcl_SetResult(interp, (char *)"malloc failed", TCL_STATIC);
> +  return TCL_ERROR;
> +}
>   memcpy(pDb->zNull, zNull, len);
>   pDb->zNull[len] = '\0';
> }else{
> @@ -2513,6 +2554,10 @@
> zProgress = Tcl_GetStrin

[sqlite] FTS5 Porter extra arguments not passed through

[Dan Kennedy]

On 07/31/2015 03:48 AM, Ralf Junker wrote:
> As per the documentation, extra arguments to the Porter stemmer are 
> handed on to the underlying tokenizer:
>
> http://www.sqlite.org/docsrc/artifact/9eca0ea509ae3e4d?ln=544-546
>
> Example syntax a few lines below:
>
>   http://www.sqlite.org/docsrc/artifact/9eca0ea509ae3e4d?ln=556
>
> However, the code does not follow the documentation and specifies 0 
> instead of the argument parameters:
>
>   http://www.sqlite.org/src/artifact/30f97a8c74683797?ln=540

Thanks for reporting this, and for the other ones earlier. Now fixed here:

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

Dan.

[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules (revisited)

[Dan Kennedy]

On 12/10/2015 05:15 AM, ajm at zator.com wrote:
> Hi list:
>
> In a C++ Windows app that uses SQLite v. 3.9.1 and behaves well, I try change 
> the search engine from FTS3/4 modules to FTS5, by means off:
>
> 1. Define the directive
> #define SQLITE_ENABLE_FTS5 1
>
> 2.-  Create the table:
> CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 
> remove_diacritics 0',columnsize=0)
>
> 3.- Populate the table:
> INSERT INTO ftsm (row,nm) SELECT id,nm FROM atm WHERE(..)
>
> After that, the app and the search engine works as espected.
>
> To update the ftsm table after several inserts and deletes, I try to follow 
> this steps
>
> 1a.- Delete the previous table.
> DROP TABLE IF EXIST ftsm
>
> 2a.- Create table (as above)
>
> 3a.- Populate table (as above).
>
> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an 
> erroro in 1a:  "database disk image is malformed".
>
> Note that in previous attemps I believed that the problem was into try to 
> delete a ftsm table build with the previous modules, but the error happen 
> when trying delete a table build with the FTS5 module.
>
> I managed to drop the ftsm table by means of create a new dbase; create the 
> same tables (except ftsm); populate the tables, and replacing the previous 
> dbase with the new one. But obviously, this method is primitive; time 
> consuming, and has problems when the dbase is in use.
>
> Some clues?

Not really sure why it might fail there. Can you post the entire 
database schema (results of "SELECT * FROM sqlite_master" or the output 
of the .schema shell tool command)?

Thanks,
Dan.

[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules(revisited)

[Dan Kennedy]

> 1a.- Delete the previous table.
> DROP TABLE IF EXIST ftsm
>
> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an 
> erroro in 1a:  "database disk image is malformed".
>
> Note that in previous attemps I believed that the problem was into try to 
> delete a ftsm table build with the previous modules, but the error happen 
> when trying delete a table build with the FTS5 module.
>
> ...
>>> Some clues?
>> Not really sure why it might fail there. Can you post the entire
>> database schema (results of "SELECT * FROM sqlite_master" or the output
>> of the .schema shell tool command)?
>>
> Dan:
>
> Here is the schema:

That all looks Ok.

If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm" 
command does it report an error as well?

Thanks,
Dan.







>
> CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 
> remove_diacri
> tics 0',columnsize=0);
>
> And here the entire database schema as produced by the shell:
>
> sqlite> SELECT * FROM sqlite_master;
> table|usr|usr|2|CREATE TABLE usr (ky INTEGER PRIMARY KEY,id CHARACTER 
> UNIQUE,lev
>   INTEGER,pwd TEXT)
> index|sqlite_autoindex_usr_1|usr|3|
> table|block|block|4|CREATE TABLE block (Stat INTEGER,User INTEGER,Page 
> TEXT,Text
>   INTEGER)
> table|FreqUse|FreqUse|5|CREATE TABLE FreqUse (Stat INTEGER,User INTEGER,Page 
> TEX
> T,Text INTEGER)
> table|blb|blb|6|CREATE TABLE blb (Id INTEGER PRIMARY KEY,Nm INTEGER)
> table|atm|atm|7|CREATE TABLE atm (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl 
> INTEGER,D
> c REAL,Dm REAL,St INTEGER)
> table|coco|coco|8|CREATE TABLE coco (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl 
> INTEGE
> R,Dc REAL,Dm REAL,St INTEGER)
> table|lnk|lnk|9|CREATE TABLE lnk (So INTEGER NOT NULL,Ta INTEGER NOT NULL,Cl 
> INT
> EGER,Tpt INTEGER,UNIQUE 
> (So,Ta,Cl),CHECK(typeof(So)='integer'),CHECK(typeof(Ta)=
> 'integer'),CHECK((typeof(Cl)='integer') OR (typeof(Cl)='null')))
> index|sqlite_autoindex_lnk_1|lnk|10|
> table|prm|prm|11|CREATE TABLE prm(ref INTEGER, val INTEGER, own INTEGER, 
> UNIQUE(
> ref, own))
> index|sqlite_autoindex_prm_1|prm|12|
> table|email|email|13|CREATE TABLE email (Id INTEGER PRIMARY KEY, Tit INTEGER, 
> No
> m INTEGER, Org INTEGER,eHnm INTEGER, ePort INTEGER, eUnm INTEGER, ePsw 
> INTEGER,
> eScon INTEGER, eDel INTEGER,sHnm INTEGER, sPort INTEGER, sUnm INTEGER, sPsw 
> INTE
> GER, sScon INTEGER,Enam INTEGER, Rnam INTEGER, Unam INTEGER, Onam INTEGER, 
> iucs
> INTEGER, sec1 INTEGER, sec2 INTEGER, sec3 INTEGER, sec4 INTEGER,Cl INTEGER, 
> St I
> NTEGER, aux1 INTEGER, aux2 INTEGER, aux3 INTEGER, aux4 INTEGER, aux5 INTEGER, 
> au
> x6 INTEGER, aux7 INTEGER)
> view|AgVtHolder|AgVtHolder|0|CREATE VIEW AgVtHolder AS SELECT id FROM atm 
> WHERE
> id IN(SELECT so FROM lnk L WHERE L.ta=73 AND L.cl=47)
> view|AgVtIDt|AgVtIDt|0|CREATE VIEW AgVtIDt AS SELECT L.ta AS 'Hd', C.nm AS 
> 'Dt'
> FROM atm C, lnk L WHERE C.cl=17 AND C.id IN (SELECT L.so FROM lnk L WHERE 
> L.cl=4
> 8 AND L.ta IN(SELECT id FROM AgVtHolder)) AND L.so=C.id
> view|AgVtPre|AgVtPre|0|CREATE VIEW AgVtPre AS SELECT L.ta AS 'Hd', CAST(Nm AS 
> IN
> T) AS 'Pr' FROM atm C, lnk L WHERE C.cl=17 AND C.id IN(SELECT so FROM lnk L 
> WHER
> E L.cl=49 AND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk 
> L W
> HERE L.ta=73 AND L.cl=47))) AND L.So=C.id
> view|AgVtos|AgVtos|0|CREATE VIEW AgVtos AS SELECT D.Hd AS 'Hd', D.Dt AS 'Dt', 
> P.
> Pr AS 'Pr' FROM AgVtIDt D, AgVtPre P WHERE P.Hd=D.Hd
> view|AgPdHolder|AgPdHolder|0|CREATE VIEW AgPdHolder AS SELECT id FROM atm 
> WHERE
> id IN(SELECT So FROM lnk L WHERE L.ta=75 AND L.cl=53)
> view|AgPdIDt|AgPdIDt|0|CREATE VIEW AgPdIDt AS SELECT L.ta AS 'Hd', C.Nm AS 
> 'Dt'
> FROM atm C, lnk L WHERE C.Cl=18 AND C.id IN (SELECT L.so FROM lnk L WHERE 
> L.cl=5
> 4 AND L.ta IN(SELECT id FROM AgPdHolder)) AND L.so=C.id
> view|AgEfHolder|AgEfHolder|0|CREATE VIEW AgEfHolder AS SELECT id FROM atm 
> WHERE
> id IN(SELECT So FROM lnk L WHERE L.ta=77 AND L.cl=59)
> view|AgEfIDt|AgEfIDt|0|CREATE VIEW AgEfIDt AS SELECT L.ta AS 'Hd', C.Nm AS 
> 'Dt'
> FROM atm C, lnk L WHERE C.Cl=19 AND C.id IN (SELECT L.So FROM lnk L WHERE 
> L.cl=6
> 0 AND L.ta IN(SELECT id FROM AgEfHolder)) AND L.So=C.id
> view|AgEfKlv|AgEfKlv|0|CREATE VIEW AgEfKlv AS SELECT L.ta AS 'Hd', C.Nm AS 
> 'Kl'
> FROM atm C, lnk L WHERE C.cl=19 AND C.id IN(SELECT so FROM lnk L WHERE 
> L.cl=61 A
> ND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk L WHERE 
> L.ta=7
> 7 AND L.cl=59))) AND L.so=C.id
> view|AgEfemer|AgEfemer|0|CREATE VIEW AgEfemer AS SELECT D.Hd AS 'Hd', D.Dt AS 
> 'D
> t', P.Kl AS 'Kl' FROM AgEfIDt D, AgEfKlv P WHERE P.Hd=D.Hd
> table|ftsm|ftsm|0|CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter 
> unico
> de61 remove_diacritics 0',columnsize=0)
> table|ftsm_data|ftsm_data|11332|CREATE TABLE 'ftsm_data'(id INTEGER PRIMARY 
> KEY,
>   block BLOB)
> table|ftsm_idx|ftsm_idx|11333|CREATE TABLE 'ftsm_idx'(segid, term, pgno, 
> PRIMARY
>   KEY(segid, term)) WITHOUT ROWID
> table|ftsm_content|f

[sqlite] Problem when upgrading from FTS3/4 to FTS5modules(revisited)

[Dan Kennedy]

On 12/11/2015 08:22 PM, ajm at zator.com wrote:
>>  Mensaje original 
>> De: Dan Kennedy 
>> Para:  sqlite-users at mailinglists.sqlite.org
>> Fecha:  Fri, 11 Dec 2015 15:28:33 +0700
>> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 to 
>> FTS5modules(revisited)
>>
>>> 1a.- Delete the previous table.
>>> DROP TABLE IF EXIST ftsm
>>>
>>> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives 
>>> an erroro in 1a:  "database disk image is malformed".
>>>
>>> Note that in previous attemps I believed that the problem was into try to 
>>> delete a ftsm table build with the previous modules, but the error happen 
>>> when trying delete a table build with the FTS5 module.
>>>
>>> ...
>>>>> Some clues?
>>>> Not really sure why it might fail there. Can you post the entire
>>>> database schema (results of "SELECT * FROM sqlite_master" or the output
>>>> of the .schema shell tool command)?
>>>>
>>> Dan:
>>>
>>> Here is the schema:
>> That all looks Ok.
>>
>> If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm"
>> command does it report an error as well?
>>
> Dan:
>
> Surprisingly, the shell does not complain when using the same query, and 
> indeed, drop the table.
>
> Yeah, I also believe that the problem is in my roof, although that code has 
> been behaving Ok from ages with the FTS3/4 modules (only changes some 
> directives in other places of the code).
>
> Any way, when running again the code when the table has been previously 
> erased (with the shell), the routine works without complaint (executes the 
> query correctly), and despite the warning, the rest of the app behaves 
> correctly and the queries related with the search works fine.
>
> When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, 
> but sqlite3_step() ends with error 11.

Are you able to compile a debugging build of SQLite and set a breakpoint 
in sqlite3CorruptError()?

Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS 
statement (pretty good chance), the stack trace will tell us more about 
the form of corruption SQLite thinks it has found.

Thanks,
Dan.




>
> When the table is already deleted, sqlite3_prepare_v2() ends Ok, and 
> sqlite3_step() ends with 101.
>
> Frankly, because the routine is the same in both times, and there are not 
> variables to bind, really I don't know where to look. Anyway thanks for 
> your's attention.
>
> Cheers!
>
> --
> Adolfo J. Millan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)

[Dan Kennedy]

On 12/13/2015 08:05 PM, ajm at zator.com wrote:
>>  Mensaje original 
>> De: Dan Kennedy 
>> Para:  sqlite-users at mailinglists.sqlite.org
>> Fecha:  Fri, 11 Dec 2015 22:54:45 +0700
>> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 
>> toFTS5modules(revisited)
>>
>>>>> 1a.- Delete the previous table.
>>>>> DROP TABLE IF EXIST ftsm
>>>>>
>>>>> This scheme work ok with the FST3/4 modules, but compiling with FTS5 
>>>>> gives an erroro in 1a:  "database disk image is malformed".
>>>>>
>>>>> Note that in previous attemps I believed that the problem was into try to 
>>>>> delete a ftsm table build with the previous modules, but the error happen 
>>>>> when trying delete a table build with the FTS5 module.
>>>>>
>>>>> ...
>>>>>>> Some clues?
>>>>>> Not really sure why it might fail there. Can you post the entire
>>>>>> database schema (results of "SELECT * FROM sqlite_master" or the output
>>>>>> of the .schema shell tool command)?
>>>>>>
>>>>> Dan:
>>>>>
>>>>> Here is the schema:
>>>> That all looks Ok.
>>>>
>>>> If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm"
>>>> command does it report an error as well?
>>>>
>>> Dan:
>>>
>>> Surprisingly, the shell does not complain when using the same query, and 
>>> indeed, drop the table.
>>>
>>> Yeah, I also believe that the problem is in my roof, although that code has 
>>> been behaving Ok from ages with the FTS3/4 modules (only changes some 
>>> directives in other places of the code).
>>>
>>> Any way, when running again the code when the table has been previously 
>>> erased (with the shell), the routine works without complaint (executes the 
>>> query correctly), and despite the warning, the rest of the app behaves 
>>> correctly and the queries related with the search works fine.
>>>
>>> When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, 
>>> but sqlite3_step() ends with error 11.
>> Are you able to compile a debugging build of SQLite and set a breakpoint
>> in sqlite3CorruptError()?
>>
>> Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS
>> statement (pretty good chance), the stack trace will tell us more about
>> the form of corruption SQLite thinks it has found.
>>
> Dan:
>
> I managed a breakpoint int the requested function, and can tell that it has 
> been called twice inside the sqlite3_step() function before it returns.
>
> Here you have the call's stack:
>
> First call of sqlite3CorrupError()  lineno == 56209
>
> sqlite3CorruptError(int lineno) Line 133961
> decodeFlags(MemPage * pPage, int flagByte) Line 56209
> btreeInitPage(MemPage * pPage) Line 56251
> getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, 
> BtCursor * pCur, int bReadOnly) Line 56495
> clearDatabasePage(BtShared * pBt, unsigned int pgno, int freePageFlag, int * 
> pnChange) Line 62907
> sqlite3BtreeClearTable(Btree * p, int iTable, int * pnChange) Line 62970
> btreeDropTable(Btree * p, unsigned int iTable, int * piMoved) Line 63028
> sqlite3BtreeDropTable(Btree * p, int iTable, int * piMoved) Line 63111
> sqlite3VdbeExec(Vdbe * p) Line 77954
> sqlite3Step(Vdbe * p) Line 71546
> sqlite3_step(sqlite3_stmt * pStmt) Line 71608
> sqlite3_exec(sqlite3 * db, const char * zSql, int (void *, int, char * *, 
> char * *) * xCallback, void * pArg, char * * pzErrMsg) Line 103955
> fts5ExecPrintf(sqlite3 * db, char * * pzErr, const char * zFormat, ...) Line 
> 180863
> sqlite3Fts5DropAll(Fts5Config * pConfig) Line 180876
> fts5DestroyMethod(sqlite3_vtab * pVtab) Line 178532
> sqlite3VtabCallDestroy(sqlite3 * db, int iDb, const char * zTab) Line 117587
> sqlite3VdbeExec(Vdbe * p) Line 79084
> sqlite3Step(Vdbe * p) Line 71546
> sqlite3_step(sqlite3_stmt * pStmt) Line 71608
>
> Secon call of sqlite3CorrupError() lineno == 56251
>
> sqlite3CorruptError(int lineno) Line 133961
> btreeInitPage(MemPage * pPage) Line 56251
> getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, 
> BtCursor * pCur, int bReadOnly) Line 56495
> ...
> Rest the same...
>
> Note that the line numbers can be slightly greater than the ones in the 
> original file (SQLite 3.9.1) because the inclusion of some comments.
>
> As I can reproduce the problem as needed, In case of interest, I can try to 
> provide any intermediate value inside the stack.

So that looks like database corruption, except we don't think the 
database is actually corrupt as the DROP TABLE statement did not fail 
when run in the shell tool. So perhaps heap-corruption has caused the 
in-memory cache to become corrupt.

Can you run the whole thing under valgrind?

Dan.

[sqlite] about attach database

[Dan Kennedy]

On 12/16/2015 12:51 PM, ??? wrote:
>  After testing the Sqlite3_open and ATTACH DATABASE,
>  I found that the attach database is slower than sqlite3_open.
>  there is attachment after the mail which includ the speed 
> information ( millisecond ).

Hi,

This mailing list strips attachments, so you'll need to include the 
information inline.

One possible explanation: When you run an ATTACH statement, SQLite opens 
the new database file and reads the schema from the sqlite_master table. 
Whereas sqlite3_open() just opens the db file (reading the schema is 
deferred until it is first required in this case).

So an apples/apples comparison might be to open/ATTACH the database and 
then run a simple query that forces SQLite to read the database schema 
if it has not already - say "SELECT * FROM sqlite_master".

Dan.

[sqlite] Index on computed value?

[Dan Kennedy]

On 12/16/2015 03:17 PM, Deon Brewis wrote:
> Is it possible to have an index on a computer value?
>
>
> E.g. I have a 40 byte value in one of my columns. I only want an index over 
> the first 4 bytes of it.
>
>
> However, I don't really want to repeat those 4 bytes inside another column on 
> the main table.
>
>
> Is there any way to accomplish that?

Something like

   CREATE TABLE t1(x BLOB);
   CREATE INDEX i1 ON t1( substr(x, 1, 4) );

   https://www.sqlite.org/expridx.html

Dan.

[sqlite] Malloc getting segmentation fault in sqlite

[Dan Kennedy]

On 12/24/2015 05:02 PM, santosh dasimanth wrote:
> Hi All,
> I am working on Sqlite in multi threaded environment on ARM v7 platform.
>
> I am facing problems with malloc() function returning segmentation fault.
> The problem is not frequent but out of 100 times am getting this once.
>
> The backtrace is pasted below.
>
> (gdb) bt
> #0  0x4038eb18 in malloc () from /lib/libc.so.0
> #1  0x401e0758 in sqlite3MallocRaw () from /pfrm2.0/lib/libsqlite3.so.0
> #2  0x401e08a8 in sqlite3Malloc () from /pfrm2.0/lib/libsqlite3.so.0
> #3  0x401e6254 in sqlite3VdbeCreate () from /pfrm2.0/lib/libsqlite3.so.0
> #4  0x401d99cc in sqlite3GetVdbe () from /pfrm2.0/lib/libsqlite3.so.0
> #5  0x401bd780 in sqlite3FinishCoding () from /pfrm2.0/lib/libsqlite3.so.0
> #6  0x401d2464 in sqlite3Parser () from /pfrm2.0/lib/libsqlite3.so.0
> #7  0x401dd664 in sqlite3RunParser () from /pfrm2.0/lib/libsqlite3.so.0
> #8  0x401d650c in sqlite3Prepare () from /pfrm2.0/lib/libsqlite3.so.0
> #9  0x401d69a4 in sqlite3_prepare () from /pfrm2.0/lib/libsqlite3.so.0
> #10 0x401ed5c0 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
> #11 0x401d5dbc in sqlite3InitCallback () from /pfrm2.0/lib/libsqlite3.so.0
> #12 0x401ed6f8 in sqlite3_exec () from /pfrm2.0/lib/libsqlite3.so.0
> #13 0x401d6184 in ?? () from /pfrm2.0/lib/libsqlite3.so.0
>
> The traces are pointing to different functions when I hit the issue at
> times.
> Please let me know if anyone of people faced this problem before with
> sqlite.

You have a corrupted heap in your application. Usually this is caused by 
calling free() or similar on a pointer that you should not have, but can 
also be due to large buffer overwrites and so on.

If possible, run your application under valgrind - either on the ARM 
platform or on a workstation. It will very likely tell you what is going 
wrong.

   http://valgrind.org/

Dan.

[sqlite] The Lock-Byte Page

[Dan Kennedy]

On 12/28/2015 05:08 AM, Olivier Mascia wrote:
> Hello,
>
> I'm referring to paragraph 1.3 of https://www.sqlite.org/fileformat2.html 
> about the Lock-Byte page.
>
>  From what I read, I understand those 2^9 bytes at offset 2^30, should they 
> exist, are set aside, untouched by SQLite nor the built-in unix and win32 
> VFS, but third-party VFS implementations might.
>
> What I don't really get straight is what file-locking related mechanism would 
> have a use for those bytes, knowing they wouldn't even exists unless the 
> database size is 2^30 bytes or more?  Or should I understand that for 
> whatever purpose they could be used for, only their offset is useful (even 
> when those bytes do not exists in the file)?

It's because (at least historically - may have changed?) win32 does not 
support advisory locks. So if you take an EXCLUSIVE lock on a range of 
bytes no other process can read them. This is different from Unix, where 
all locks are advisory - one process locking a range of bytes does not 
prevent another from reading them, only from taking a conflicting lock.

For this reason we need a range of bytes that are never read by SQLite 
to take locks on (otherwise we couldn't have readers running 
concurrently with the writer). The reason the selected range is out at 
2^30 instead of, say, at the start of the file, is to avoid forcing 
really small databases to be larger than they would otherwise have to be.

It doesn't matter that database files are usually less than 2^30 bytes 
in size. All the (main?) platforms support locking regions of a file 
that have not yet been populated.

Dan.

[sqlite] Documentation Typo in FTS Example

[Dan Kennedy]

On 12/30/2015 03:57 PM, Casey Rodarmor wrote:
>  From https://sqlite.org/fts3.html:
>
> CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c, d);
> CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c);
>
> INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
> INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
>
>
> The two inserts will fail, since t2 has 5 columns but 4 values were
> supplied.

Thanks for this. Now fixed in source control.

Dan.

[sqlite] Waiting on RESERVED locks

[Dan Kennedy]

On 02/18/2015 06:53 AM, Tim Starling wrote:
> We (Wikimedia) are observing SQLITE_BUSY errors in our integration
> testing. The integration test consists of having a single browser
> instance view a MediaWiki site which uses SQLite 3.8 for its backend.
> The browser sends several parallel requests for CSS, JS, etc., and
> MediaWiki writes to the SQLite database while servicing each of these
> requests. Thus there is some lock contention.
>
> In strace we see SQLite sleeping when it fails to acquire a SHARED
> lock, but when it tries to acquire a RESERVED lock, no sleep is done,
> and an error is immediately reported to the application.

If you already have a busy-handler or busy-timeout configured but it is 
not being used when upgrading to a RESERVED lock, it is to avoid 
deadlock. Is the transaction something like:

   BEGIN;
 SELECT  /* SHARED lock on database */
 INSERT ...  /* RESERVED lock on database */

In this case, blocking on the RESERVED lock is no good. The process 
currently holding it is a writer that will need to obtain the EXCLUSIVE 
lock in order to commit its transaction. And obtaining the EXCLUSIVE 
lock will of course require it to wait until all readers release their 
SHARED locks - including the reader waiting on RESERVED. So deadlock.

One fix is to make sure that an active read-transaction is never 
upgraded to a write-transaction. Do this by making all transactions that 
may write to the database write-transactions from the start. Either by 
opening them with "BEGIN IMMEDIATE" or making sure that the first 
statement in the transaction is a writer.

   BEGIN IMMEDIATE;/* Blocks for RESERVED lock */
 SELECT 
 INSERT ...


Dan.




>
> https://phabricator.wikimedia.org/T89180
>
> The relevant code has a comment indicating that this is expected
> behaviour:
>
> /* Obtain a RESERVED lock on the database file. If the exFlag parameter
> ** is true, then immediately upgrade this to an EXCLUSIVE lock. The
> ** busy-handler callback can be used when upgrading to the EXCLUSIVE
> ** lock, but not when obtaining the RESERVED lock.
> */
> rc = pagerLockDb(pPager, RESERVED_LOCK);
> if( rc==SQLITE_OK && exFlag ){
>rc = pager_wait_on_lock(pPager, EXCLUSIVE_LOCK);
> }
>
>
> Is it possible to open a bug or feature request for making SQLite wait
> on RESERVED locks?
>
> Also, do you have any suggestions for a workaround?
>
> -- Tim Starling
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite vfs and wal mode

[Dan Kennedy]

On 02/23/2015 12:42 PM, J Decker wrote:
> I see; the first part...
>
> " result of the pragma or the error message if the pragma fails"  I missed
> the or part... or rather missed the part before the or.
>
> On Sun, Feb 22, 2015 at 8:13 PM, J Decker  wrote:
>
>> So now that I have the added complexity of my own vfs in which to make a
>> sqlite vfs... I'm having some issues.
>>
>> Hmm...
>> One of the first things I do is 'pragma journal_mode=WAL'
>> I return sqlite_ok; but the result has 0 columns, whereas my command line
>> tool returns a column 'journal_mode' and a row 'WAL'..
>>
>> is WAL somehow dependant on the VFS?

Only in that sqlite3_io_methods.iVersion needs to be 2 or greater. i.e. 
you need to implement the xShmXXX() methods.

>>
>> I was just going to note, that although I'm setting wall, and it does 3
>> probes to see if the wal file exists... and probes a lot actually - though
>> I guess it's assuming other processes are going to be manipulating the
>> file? (is there a compile option to disable that, and assume it's the only
>> one with access to the database?)

If you set "PRAGMA locking_mode=EXCLUSIVE" it won't do that. Of course, 
then you're limited to at most one sqlite3* handle.

Or, it will stop probing if it is actually in wal mode.

Dan.

[sqlite] Is readline ubiquitous on 32-bit x86 Linux?

[Dan Kennedy]

The pre-built sqlite3 shell tool for x86 Linux available for download here:

   http://www.sqlite.org/download.html

does not include readline support. Which makes it painful to use.

Does anyone think that many systems would be affected if it dynamically 
linked against the system readline? This means that the binary would not 
work on systems without libreadline.so installed. Or is readline 
considered ubiquitous by now?

Dan.

[sqlite] Weird FTS bugs (wrong, missing and false positive results)

[Dan Kennedy]

On 07/02/2015 09:24 PM, Kevin Benson wrote:
> FTFY, you're welcome ;-)
> Now *MAYBE* someone can read it and possibly reply.
>
> --
> --
>--
>   --???--
>  K e V i N
>
> On Thu, Jul 2, 2015 at 9:11 AM,  wrote:
>
>> We use SQLite for indexing and searching the text contents of our app
>> using FTS4
>> (about 27k unique words, about 1 million words for the whole contents). In
>> particular,
>> we use the offsets function. Currently, after some testing, we?re
>> experiencing a plenty
>> of problems with finding the results needed.
>>
>> For the forms of the words searching we use the ?all-to-all? way, which
>> means we have
>> some standard English endings and words forms and modify the initial
>> request so that
>> all possible combinations are included and separated by OR from each
>> other.
>>
>> I. e. if we have two forms and two words in the request (for simplicity),
>> that would look
>> like (the MATCH part):
>>
>> ?(word1_form1 NEAR/10 word2_form1)
>> OR (word1_form1 NEAR/10 word2_form2)
>> OR (word1_form2 NEAR/10 word2_form1)
>> OR (word1_form2 NEAR/10 word2_form2)?.
>>
>> Initially, the problem appeared that the query returned offsets for
>> absolutely wrong words.
>> While searching for ?honest fair?, we got words like ?good?, ?sport?,
>> ?natural? offsets.
>> We moved from the system provided (iOS, OS X) SQLite (version 3.8.5,
>> further called
>> ?old SQLite?) to the latest one available on the official site (version
>> 3.8.10.2,
>> further called ?new SQLite? or just ?SQLite?), which solved the issue, but
>> returned offsets
>> for words which were not near (according to NEAR/10). I supposed it?s
>> because the request
>> was too big because of lots of the words forms. But, for example, this
>> request
>>
>> for ?offense is rank?:
>> select documents.name, contents.rowid, part, offsets(contents)
>> from contents left join documents on contents.document_id = documents.id
>> where text match '(\"offense\" NEAR/10 \"is\" NEAR/10 \"rank\")'
>> order by document_id
>>
>> returned the correct results, while this one returned false positive
>> results (0 stands for
>> apostrophe so it?s not interpreted as a separate token for the simple
>> tokenizer we use):
>>
>> select documents.name, contents.rowid, part, offsets(contents)
>> from contents left join documents on contents.document_id = documents.id
>> where text match '(\"offense\" NEAR/10 \"is\" NEAR/10 \"rank\")
>> OR (\"offense\" NEAR/10 \"is\" NEAR/10 \"rank0s\")'
>> order by document_id
>>
>> The experiments revealed that ?rank0s? can be whatever: ranks",
>> "rankqwerty" and so on,
>> even "rankqwertyvrfbgbrevwkefovmwpsrvrm" or "yugbuoiipkipnuo?. If it's
>> removed, nothing
>> found. If after that the first word is modified, the one correct result
>> found. Also, a
>> search for ?speak again? didn?t find the results at all, though there
>> should be quite a
>> lot of them.
>>
>> The ?database disk image is malformed? error was reported, but integrity
>> check completed
>> with no errors, the database was recreated and that didn?t help, meanwhile
>> the old SQLite
>> did find the results for this request in exactly that file with no error
>> reported.
>>
>> Also, the new SQLite worked well for the same request if the offsets
>> function was replaced
>> with the snippet function (that also solved the false positive results
>> problem described
>> above). The search for ?father? returned not all results. For example, the
>> first result
>> which can manually be found in the contents table using SQLite Database
>> Browser for this
>> request:
>>
>> select documents.name, contents.rowid, part, offsets(contents)
>> from contents left join documents on contents.document_id = documents.id
>> where text match '(\"father\")
>> OR (\"fathere\") OR (\"fathering\")
>> OR (\"fatherish\") OR (\"fathers\")
>> OR (\"fatheres\") OR (\"fatherian\")
>> OR (\"fatheral\") OR (\"father0s\")'
>> and document_id in (25)
>> order by document_id
>>
>> missed when actually searched, the snippet function doesn?t help here,
>> neither helps
>> searching for a single word form. Error logging with
>>
>> sqlite3_config(SQLITE_CONFIG_LOG, errorLogCallback, NULL)
>>
>> was never called.
>>   Any ideas?


It's difficult to say. The level of detail you have provided is a little 
overwhelming.

If you are using a custom tokenizer and it behaves inconsistently you 
could get these kind of problems. Or it could also be a bug in the 
combination of NEAR, OR and an auxiliary FTS function.

Are you able to post a database online along with a query that returns 
an incorrect result? And the tokenizer implementation too if possible, 
although it might be possible to figure it out without that.

Does "neither helps searching for a single word form" mean that a simple 
query like "text MATCH 'father'" is also failing? If so, that's the one 
to post.

You are compiling with SQLITE_ENABLE_FTS3_PARENTHESIS defined, correct?

Dan.

[sqlite] Weird FTS bugs (wrong, missing and false positive results)

[Dan Kennedy]

On 07/06/2015 07:23 PM, shuricksoft at ukr.net wrote:
>
>   ---  ? ---
> ?? ????: "Dan Kennedy" 
> : 2  2015, 14:26:05
>
>   
>> On 07/02/2015 09:24 PM, Kevin Benson wrote:
>>> FTFY, you're welcome ;-)
>> Now *MAYBE* someone can read it and possibly reply.
>>> --
>>>  --
>>> --
>>>--???--
>>>   K e V i N
>>>
>>
>> It's difficult to say. The level of detail you have provided is a little
>> overwhelming.
>>
>> If you are using a custom tokenizer and it behaves inconsistently you
>> could get these kind of problems. Or it could also be a bug in the
>> combination of NEAR, OR and an auxiliary FTS function.
>>
>> Are you able to post a database online along with a query that returns
>> an incorrect result? And the tokenizer implementation too if possible,
>> although it might be possible to figure it out without that.
>>
>> Does "neither helps searching for a single word form" mean that a simple
>> query like "text MATCH 'father'" is also failing? If so, that's the one
>> to post.
>>
>> You are compiling with SQLITE_ENABLE_FTS3_PARENTHESIS defined, correct?
>>
>> Dan.
> Sorry for the delayed reply, I was traveling these days.
>
> Thanks, Kevin! What was the problem with the line breaks? When I sent it, it 
> looked fine.
>
> Dan, I'm not using a custom tokenizer, just the standard one (called simple).
>
> I can send you the database, but we don't like it to be publicly available 
> online, so, please, tell me a comfortable way for you I can make it available 
> only to you (about 6 MB).

6MB? Just zip it and mail it to me.

Thanks,
Dan.




>
> Yes, "neither helps searching for a single word form" means just the same you 
> wrote.
>
> Yes, sure, I'm compiling with SQLITE_ENABLE_FTS3_PARENTHESIS.
>   
>   
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to use fts5 ?

[Dan Kennedy]

On 07/16/2015 02:32 PM, Laurent Dami wrote:
> Hi there,
>
> I'm interested in testing the fts5 extension, already documented 
> (https://www.sqlite.org/fts5.html) , but I didn't find any 
> instructions on how to compile it. I found the sources in ext/fts5, 
> but apparently there is a piece of yacc grammar that doesn't compile 
> under yacc/bison.
>
> Did I miss some documentation ? Or is it not ready yet for use ?
>
> Thanks in advance,

Hi,

Slightly more up to date docs, including how to compile, here:

   http://sqlite.org/draft/fts5.html#section_2

Just noticed there is an HTML bug in the code for that section. Where it 
says:

   $ ls fts5.& 91;ch]

it should of course be:

   $ ls fts5.[ch]

FTS5 status: I think FTS5 is more than ready to test now. The current 
focus here is on running lots of performance tests to see if there are 
any tweaks we can make to the way data is stored in the database (i.e. 
the schema of the SQL tables used to store the underlying data) that 
will speed things up. So it's possible the file-format might change a 
little bit before it's actually released.

We're very interested in bugs, performance regressions (relative to 
FTS4) and other performance problems right now.

Thanks,
Dan.







>
> Laurent Dami
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fts5 module does not build from the 3.8.11 release's source tarball

[Dan Kennedy]

On 07/28/2015 02:55 AM, Hinrichsen, John wrote:
> Hi,
>
> I was not able to get the fts5 module to build from the versioned source
> tarball for this release (http://www.sqlite.org/2015/sqlite-src-3081100.zip
> ).

Which step failed?



>
> I was able to 'make fts5.c' following the instructions that reference the
> "trunk" tarball.
>
> Regards,
> John Hinrichsen
>

[sqlite] fts5 module does not build from the 3.8.11 release's source tarball

[Dan Kennedy]

On 07/31/2015 09:51 PM, Hinrichsen, John wrote:
> Update:
>
> I don't have a problem compiling under centos 7 (gcc 4.8.3), but with
> centos 6 (gcc 4.4.7) I do get this error.
>
> fts5_main.c:30: error: redefinition of typedef 'Fts5Global'
> fts5Int.h:83: note: previous declaration of 'Fts5Global' was here
>
> Unfortunately, I still have to support centos 6.

Thanks for this. Now fixed here:

   http://www.sqlite.org/src/info/54a771fe2c2b3c1c

Regards,
Dan.




>
>
> On Mon, Jul 27, 2015 at 4:16 PM, Hinrichsen, John 
> wrote:
>
>> This was the error I got:
>>
>> fts5_main.c:30: error: redefinition of typedef 'Fts5Global'
>>
>>
>> On Mon, Jul 27, 2015 at 4:00 PM, Dan Kennedy 
>> wrote:
>>
>>> On 07/28/2015 02:55 AM, Hinrichsen, John wrote:
>>>
>>>> Hi,
>>>>
>>>> I was not able to get the fts5 module to build from the versioned source
>>>> tarball for this release (
>>>> http://www.sqlite.org/2015/sqlite-src-3081100.zip
>>>> ).
>>>>
>>> Which step failed?
>>>
>>>
>>>
>>>
>>>> I was able to 'make fts5.c' following the instructions that reference the
>>>> "trunk" tarball.
>>>>
>>>> Regards,
>>>> John Hinrichsen
>>>>
>>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>

[sqlite] Statistical SQLITE_BUSY_SNAPSHOT in WAL mode

[Dan Kennedy]

On 05/31/2015 04:11 AM, Simon Slavin wrote:
> On 30 May 2015, at 9:59pm, Samuel Tebeka  wrote:
>
>> I'm doing a BEGIN EXCLUSIVE before every write statement already, should I
>> do it for read steatements as well?
> It's worth a try.  I'm not sure what's wrong here, I'm just hoping we find a 
> way to change what's happening enough to figure out a good solution.

SQLITE_BUSY_SNAPSHOT indicates that a connection is trying to upgrade a 
read-transaction to read/write, but that the database has been modified 
since the read transaction was opened and it is not possible to do so.

   https://www.sqlite.org/rescode.html#busy_snapshot

[sqlite] FTS5

[Dan Kennedy]

On 05/15/2015 02:37 PM, Milan K??? wrote:
> Hello,
> I've found that SQLite is preparing new FTS5 extension, which could be 
> better than current FTS3/4.
> If it is still in development, I would like to propose one more 
> change. In our project we would need possibility to specify which columns
> should be matched by the match operator. We use 'standardized' DB in 
> which we cannot change tables and we have several queries
> which operates only on several columns (each query needs different set 
> of columns). To achieve the required functionality we have to use
> matchinfo() structure with custom function checking whether the 
> required columns matched.
>
> For example, lets assume the following table
>
> FtsTableA | A B C D E F G H
>
> It would be nice to allow specification of 'required' columns to 
> match. I think that the following 'extended' syntax could be quite 
> consistent:
>
> select docId from FtsTableA where FtsTableA(B,C,D) match 'a* b* c*'
>
> The other solution could be to ORify the match clause, but I think it 
> would quite ugly solution and I believe
> ... match '(B:a* OR C:a* OR D:a*)(B:b* OR C:b* OR D:b*)(B:c* OR C:c* 
> OR D:c*)'

Now: ... MATCH '{B C D}:a* AND {B C D}:b* AND {B C D}:c*'

https://sqlite.org/draft/fts5.html#section_2

So some progress.

Dan.

[sqlite] Newbie issue - Linux error malloc.c:2372: sysmalloc: Assertion `(old_top == (((mbinptr) (((char *)

[Dan Kennedy]

On 06/06/2015 03:19 AM, George wrote:
> Hello everyone,
>
> I am new to the list. I am working on an application in which I will be
> embedding SQLite as the database engine. The application is written in
> C.
>
> I am currently having an issue which I am not able to resolve at the
> moment so I thought I would ask here since I am just starting out with
> SQLite.
>
> My problem is, from my point of view, that I am not able to perform an
> action to the same database file in the following manner:
>
> 1) I open a database via:
>   sqlite3_initialize()
>   sqlite3_open_v2
> 2) I do some work on getting metadata from the database like table
> names and their fields and then
> 3) I close the connection via:
>   sqlite3_close_v2
>   sqlite3_shutdown
> 4) After all of this is done I wish to process an import file so I need
> to open another connection to the same database file and run some
> statements but when I try to do that I get this on the open call in
> step 1 (above):

I guess that assert() failing means the heap is corrupted. Which might 
be SQLite related or might not.

Running the app under [valgrind] might tell you more. Post its complete 
output here if there are errors but it's not obvious what the problem is.

Dan.




>
> malloc.c:2372: sysmalloc: Assertion `(old_top == (((mbinptr) (((char *)
> &((av)->bins[((1) - 1) * 2])) - __builtin_offsetof (struct
> malloc_chunk, fd && old_size == 0) || ((unsigned long) (old_size)
>> = (unsigned long)__builtin_offsetof (struct malloc_chunk,
>> fd_nextsize))+((2 *(sizeof(size_t)) < __alignof__ (long double) ?
>> __alignof__ (long double) : 2 *(sizeof(size_t))) - 1)) & ~((2
>> *(sizeof(size_t)) < __alignof__ (long double) ? __alignof__ (long
>> double) : 2 *(sizeof(size_t))) - 1))) && ((old_top)->size & 0x1) &&
>> ((unsigned long) old_end & pagemask) == 0)' failed. Aborted (core
>> dumped)
> This happens in on line 17149 when calling:
>
> p = SQLITE_MALLOC( nByte+8 )
>
> nByte is 64000
>
> in sqlite3.c (amalgamation latest version
> sqlite-amalgamation-3081002.zip)
>
> I am compiling and running the code on:
> Linux x140e 3.13.0-53-generic #89-Ubuntu SMP Wed May 20 10:34:28 UTC
> 2015 i686 athlon i686 GNU/Linux
>
> NAME="Ubuntu"
> VERSION="14.04.2 LTS, Trusty Tahr"
> ID=ubuntu
> ID_LIKE=debian
> PRETTY_NAME="Ubuntu 14.04.2 LTS"
> VERSION_ID="14.04"
> HOME_URL="http://www.ubuntu.com/";
> SUPPORT_URL="http://help.ubuntu.com/";
> BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/";
>
> I have compiled sqlite.o with the following:
>
> gcc -c -Wall -O0 -g -std=c99 -Dlinux -I/usr/local/include sqlite3.c \
>  -DSQLITE_THREADSAFE=1 -DSQLITE_OMIT_LOAD_EXTENSION -o obj/sqlite3.o
>
> Any suggestions or directions greatly appreciated.
> TIA,
> George
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DROP statement on Virtual Tables

[Dan Kennedy]

On 06/09/2015 04:46 PM, Samuel Debionne wrote:
> Hello,
>
> AFAIU, when executing a drop statement on a virtual table, the XConnect
> callback is executed first, then XDestroy.
>
> Now I have the following scenario where my virtual table is related to a
> file (say a CSV file):
>
> 1. Create the virtual table
> 2. Delete the CSV file from disk
> 3. Try to drop the table (fails)
>
> When creating the vtable, xCreate / xConnect should fail (returns
> SQLITE_ERROR) if the underlying file does not exists.
>
> But when dropping the vtable, this very same xConnect should continue to
> let the user drop a table on a deleted file.
>
> Is there a way to know the "context", e.g. the SQL command, that has
> triggered a call to xConnect ? Or is there a better way to tackle this
> issue ?

I don't think there is a way to detect the current statement.

You probably have to allow the xConnect() to succeed and set a flag 
within the sqlite3_vtab object to indicate that the underlying file is 
not actually present. Then return SQLITE_IOERR or similar on all 
operations that actually require the CSV file.

Dan.



>
> Thank you,
> Samuel
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DROP statement on Virtual Tables

[Dan Kennedy]

On 06/10/2015 03:28 PM, Samuel Debionne wrote:
> Thank you for your thoroughful answers !
>
> Following your advices, I have split XCreate and xConnect
> implementations, the first enforces the existence of the resource while
> the later returns SQLITE_OK even if the resource is missing.
>
>> The proper place to implement handling a missing backing storage file
>> is xOpen (for SELECT) and xUpdate (for INSERT/UPDATE/DELETE). You
>> choose to either return an error there, or silently provide no rows
>> on SELECT and ignore INSERTs.
> Well, it seems that xBestIndex is called first (for SELECT). If I return
> SQLITE_IOERR from xBestIndex, SQLite crashes.

Do you have a stack trace for the crash?

Did the xBestIndex() implementation set sqlite3_vtab.zErrMsg before 
returning? Setting this to point to a buffer that was not allocated 
using sqlite3_malloc() or sqlite3_mprintf() can cause a crash.

Dan.





>   xConnect requires that
> ppVTab is allocated,  initialized and a dummy vtab schema should be
> declared :
>
> sqlite3_declare_vtab(db, "CREATE TABLE missing_vt(uid INTEGER)");
>
> Something similar should probably be done for xBestIndex and the
> sqlite3_index_info structure. But this is really confusing the
> implementation...
>
>> 3) pragma writeable_schema; delete from sqlite3_master where
>> name='mycsv';
> This may be the best option actually ! I think I will go for it and add
> a ".drop VTABLE" command to my shell...
>
> It would be great to have better support for this scenario: if the
> statement is a DROP TABLE on a virtual table, allows xConnect to fail
> and remove the table from sqlite3_master.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual tables/xBestIndex: Is this a bug?

[Dan Kennedy]

On 06/11/2015 03:49 AM, Eric Hill wrote:
> Is it a bug that SQLite changes the order of the constraints passed to 
> xBestIndex based merely on the order of the ON clause, when SQL (AFAIK) says 
> nothing about what the order of the ON clause should be?

No. The order is undefined.

However, it is less than perfect that the constraints on "film_id=" are 
marked usable when they are really not. That's the root of your problem 
I think - SQLite is asking for the wrong thing. When it eventually 
figures out that it can't actually use the plan it requested from 
xBestIndex (because the film_id= constraint is not actually usable) it 
falls back to a linear scan.

There is now a change on the trunk that should fix this:

   http://www.sqlite.org/src/info/7b446771cadedafb

Dan.

[sqlite] Contstant WHERE terms still require table scan?

[Dan Kennedy]

On 06/12/2015 05:45 PM, nomad at null.net wrote:
> On Fri Jun 12, 2015 at 09:49:29AM +, Hick Gunter wrote:
>> Seems the correct code is already generated...
> Thanks Hick, that shows a bit more detail I didn't think to look for.
> It seems that this only works for bind values, as the comparison and goto
> statements aren't present when the term is defined at prepare time:
>
>  .width 4 10 4 4 4 10 2 10
>  EXPLAIN SELECT
>  x.id
>  FROM
>  x
>  WHERE
>  1=0
>  ;
>
>  addr  opcode  p1p2p3p4  p5  comment
>    --        --  --  --
>  0 Init0 9 0 00  NULL
>  1 Ne  2 7 1 51  NULL
>  2 OpenRead0 2 0 0   00  NULL
>  3 Rewind  0 7 0 00  NULL
>  4 Rowid   0 3 0 00  NULL
>  5 ResultRow   3 1 0 00  NULL
>  6 Next0 4 0 01  NULL
>  7 Close   0 0 0 00  NULL
>  8 Halt0 0 0 00  NULL
>  9 Transactio  0 0 1 0   01  NULL
>  10TableLock   0 2 0 x   00  NULL
>  11Integer 1 1 0 00  NULL
>  12Integer 0 2 0 00  NULL
>  13Goto0 1 0 00  NULL
>
> That makes me think that for the 1=0 case the scan occurs anyway?

I think the "Ne" at address 1 is the test in this case.

Dan.

[sqlite] Possible error in FTS5 docs

[Dan Kennedy]

On 06/19/2015 08:34 PM, Philip Bennefall wrote:
> Hi all,
>
> While reading through the draft documentation for FTS5, I noticed the 
> following in section 4.1.1:
>
> "The built-in auxiliary function bm25() returns a real value 
> indicating how well the current row matches the full-text query. The 
> better the match, the
> larger the value returned."
>
> Then, a little further down it says:
>
> "In order to avoid this pitfall, the FTS5 implementation of BM25 
> multiplies the result by -1 before returning it, ensuring that better 
> matches are assigned
> numerically lower scores."
>
> Is this a mistake, or did I misunderstand something?

It's a mistake. Should be "The better the match, the numerically lower 
the value returned" or similar.

Thanks for pointing this out.

Dan.

[sqlite] SQLite uclinux

[Dan Kennedy]

On 06/19/2015 04:48 PM, Thomas Mager wrote:
> Hi,
>
> I have to develop an application under uclinux which should use a 
> SQLite database. Since it is running finally on an embedded system it 
> should not use much memory. So, I wanted to build SQLite with my cross 
> compiler and omit some options to reduce the library size and get a 
> very small footprint. My problem is that when I run configure and put 
> some -DSQLITE_OMIT_xxx entries to CFLAGS I get an error when I want to 
> build the SQLite library. See the following what I configure and what 
> I get as result when linking. The switch -DSQLITE_OMIT_TRIGGER 
> produces the error in this case, all other -DSQLITE_OMIT_xxx switches 
> you see in the configure command below are running fine.
>
> This is my configure command:
>
> CC="/home/lantronix/linuxsdk/toolchains/freescale-coldfire-4.4/bin/m68k-uclinux-gcc"
>  
> CFLAGS="-Os -fpic -Wall -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_WAL 
> -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_PROGRESS_CALLBACK 
> -DSQLITE_OMIT_TEMPDB -DSQLITE_OMIT_UTF16 
> -DSQLITE_OMIT_XFER_OPT-DSQLITE_OMIT_TRIGGER" ./configure 
> --host=m68k-uclinux --disable-threadsafe --disable-shared 
> --disable-libtool-lock
>
> Here is the output when building the library:
>
> /bin/bash ./libtool --tag=CC   --mode=link 
> /home/lantronix/linuxsdk/toolchains/freescale-coldfire-4.4/bin/m68k-uclinux-gcc
>  
> -DSQLITE_THREADSAFE=0  -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -Os 
> -fpic -Wall -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_WAL 
> -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_PROGRESS_CALLBACK 
> -DSQLITE_OMIT_TEMPDB -DSQLITE_OMIT_UTF16 -DSQLITE_OMIT_XFER_OPT 
> -DSQLITE_OMIT_TRIGGER   -o sqlite3 shell.o sqlite3.o
> libtool: link: 
> /home/lantronix/linuxsdk/toolchains/freescale-coldfire-4.4/bin/m68k-uclinux-gcc
>  
> -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -Os 
> -fpic -Wall -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_WAL 
> -DSQLITE_OMIT_AUTHORIZATION -DSQLITE_OMIT_PROGRESS_CALLBACK 
> -DSQLITE_OMIT_TEMPDB -DSQLITE_OMIT_UTF16 -DSQLITE_OMIT_XFER_OPT 
> -DSQLITE_OMIT_TRIGGER -o sqlite3 shell.o sqlite3.o
> sqlite3.o: In function `dotlockLock':
> sqlite3.c:(.text+0x378ae): warning: the use of LEGACY `utimes' is 
> discouraged, use `utime'
> sqlite3.o: In function `yy_destructor':
> sqlite3.c:(.text+0x35afa): undefined reference to 
> `sqlite3DeleteTriggerStep'
> sqlite3.o: In function `sqlite3Parser':
> sqlite3.c:(.text+0x48ce0): undefined reference to `sqlite3FinishTrigger'
> sqlite3.c:(.text+0x48d2c): undefined reference to `sqlite3BeginTrigger'
> sqlite3.c:(.text+0x48e62): undefined reference to 
> `sqlite3TriggerUpdateStep'
> sqlite3.c:(.text+0x48e8e): undefined reference to 
> `sqlite3TriggerInsertStep'
> sqlite3.c:(.text+0x48eb2): undefined reference to 
> `sqlite3TriggerDeleteStep'
> sqlite3.c:(.text+0x48ed2): undefined reference to 
> `sqlite3TriggerSelectStep'
> sqlite3.c:(.text+0x48f84): undefined reference to `sqlite3DropTrigger'
> collect2: ld returned 1 exit status
> make: *** [sqlite3] Error 1
>
>
> It would be nice if you have some hints for me?

In order to use some of the SQLITE_OMIT_* options, including 
SQLITE_OMIT_TRIGGER, you need to build SQLite from the canonical source 
files, not the amalgamation package. The first zip file under 
"Alternative Source Code Formats" here:

   http://www.sqlite.org/download.html

Dan.

[sqlite] sqlite error code 1034

[Dan Kennedy]

On 06/23/2015 12:01 PM, Mayank Kumar (mayankum) wrote:
> Thanks Simon. My application has synchronous set to FULL. It seems sqlite 
> tries to do fsync for read operations as well since I also got this error 
> when executing "select * from db".
> My application currently treat this error 1034(disk io error) as catastrophic 
> error and dies.
>
> Do you think if sqlite3_step or sqlite3_open fail with this error, we should 
> treat this as transaction committed and ignore the fsync error and it will be 
> retried with the next commit or read and eventually will sync unless I see 
> this error continuously in which case we can treat this as catastrophic.

The fsync() within the SELECT statement is likely happening as part of 
rolling back a hot-journal. i.e. it is attempting to restore the 
database to a sane state following a transaction that failed due to an 
earlier IO error.

On the platform in question, is there any reason to think the system 
will recover once fsync() starts failing? Treating this as a 
catastrophic error would usually be the right way to go.

Dan




>
> Also I wanted to know if fsync requires space on the filesystem since in our 
> case nvram has little space(200mb), do you think fsync could fail because of 
> that as well?
>
> Thanks
> mayank
>
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon 
> Slavin
> Sent: Monday, June 22, 2015 6:11 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite error code 1034
>
>
> On 23 Jun 2015, at 1:33am, Mayank Kumar (mayankum)  
> wrote:
>
>> We once in a while see the following errors returns by sqlite for a database 
>> created on nvram. I am trying to understand, how are other users handling 
>> this error ? if fsync fails and sqlite api fails because of this issue, does 
>> sqlite internally retry before declaring it cannot commit a transaction 
>> because of this and should the application retry or this is a non 
>> recoverable error ?
>> I know if the os fsync fails, we cannot guarantee the db remains sane and 
>> uncorrupted, in case of os crash or shutdown, but just trying to see what 
>> others are doing for their applications in this scenario.
> There's no easy way to retry the operation which failed, but you probably 
> don't have to.
>
> I have not encountered fsync() failure as part of SQLite, but to a unix user 
> it is the same as 'IO Error'.  There is no point in retrying it since it will 
> effectively be retried on the next operation of the file, including when the 
> file handle is closed.  So there is a good chance that the file will 
> eventually be synchronised anyway.  Problems may, however, occur if you have 
> multiple apps/users trying to access the file at once.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite PENDING lock before SHARED lock with WAL enabled

[Dan Kennedy]

On 06/23/2015 03:49 PM, Harmen de Jong - CoachR Group B.V. wrote:
> Is there anyone who can confirm that the first read transaction with WAL 
> enabled locks the database because it triggers the SchemaLoaded?

When a database in wal mode is first queried, SQLite takes a SHARED lock 
on the file. This is the same SHARED lock that is used when reading in 
rollback mode - except in wal mode it is held until the connection is 
closed.

And acquiring a SHARED lock requires temporarily obtaining a PENDING lock:

   http://www.sqlite.org/src/artifact/27cc135e2d0b8?ln=3030-3032

So yes, I think your analysis is correct.

Dan.




>
> Does anybody have a better suggestion to circumvent this locking issue than 
> serializing the first read transaction?
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Harmen 
> de Jong - CoachR Group B.V.
> Sent: donderdag 18 juni 2015 15:02
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite PENDING lock before SHARED lock with WAL enabled
>
> Thanks for your feedback. We can confirm that the database is in WAL mode. 
> After some further digging, we have found out It only happens with the very 
> first query. SQLite calls sqlite3InitOne when DbHasProperty(db, 1, 
> DB_SchemaLoaded) is false. Apparently, the schema is loaded into memory via 
> the common SHARED locking mechanism. So when multiple threads perform their 
> first query at the very same moment, they lock each other out.
>
> We assume that the locking of the PENDING byte during initialization does not 
> lock out a writer on another already initialized connection. Can your confirm 
> this?
>
> We can solve the issue by using our own locking mechanism around the first 
> query, so that these queries never occur simultaneously. However, it would be 
> great if there would be a more neat solution, because some of our databases 
> have a lot of tables and therefore loading the database schema can cost a lot 
> of time (relatively). Therefore, we would like to be able to open multiple 
> parallel connections and initialize them at the same time. Do you have any 
> suggestions for this?
>
> Best regards,
>
> Harmen de Jong
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard 
> Hipp
> Sent: donderdag 18 juni 2015 13:00
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite PENDING lock before SHARED lock with WAL enabled
>
> On 6/18/15, Harmen de Jong - CoachR Group B.V.  wrote:
>> A SELECT query obtains a SHARED lock on the database file. Doing so,
>> there can be many simultaneous readers according to the documentation
>> at https://www.sqlite.org/lockingv3.html. However, before the SHARED
>> lock is obtained in winLock(sqlite3_file *id, int locktype), a
>> temporary PENDING lock is requested.
>>
>> So when multiple threads start a SELECT query on different sqlite3*
>> objects at the exact same time, some of them will result in a
>> SQLITE_BUSY error, because they all request the exclusive PENDING
>> lock. Is there a way to work around this, so that I can execute SELECT 
>> queries really simultaneously?
>>
>> We have WAL enabled,
> Please double-check that you really have WAL mode enabled.  From the 
> sqlite3.exe command-line shell run commands like:
>
>   PRAGMA journal_mode;
>   .dbinfo
>
> In the second command, the "write format" and "read format" should both be 
> "2".
>
> I do not think you are really in WAL mode because a SHARED lock should never 
> happen in WAL mode.
>
>
>
>> so we were under the assumption that it should be possible to have
>> multiple readers at the same time, however the UNLOCKED -> PENDING ->
>> SHARED transition seems to intervene with the principle of having
>> multiple readers at the same time.
>>
>> This is the callstack of one of the threads:
>>
>>>  SQLService.exe!winLock(sqlite3_file * id=0x0e42aab0, int
>>> locktype=1) Line 35956
>> SQLService.exe!sqlite3OsLock(sqlite3_file *
>> id=0x0e42aab0, int lockType=1) Line 16294
>> SQLService.exe!pagerLockDb(Pager * pPager=0x0e42a998,
>> int
>> eLock=1) Line 42356
>> SQLService.exe!pager_wait_on_lock(Pager *
>> pPager=0x0e42a998, int locktype=1) Line 45027
>> SQLService.exe!sqlite3PagerSharedLock(Pager *
>> pPager=0x0e42a998) Line 46249
>> SQLService.exe!lockBtree(BtShared * pBt=0x0e429ea0)
>> Line
>> 55204
>> SQLService.exe!sqlite3BtreeBeginTrans(Btree *
>> p=0x0e428250, int wrflag=0) Line 55562
>> SQLService.exe!sqlite3InitOne(sqlite3 * db=0x0e427e38,
>> int iDb=0, char * * pzErrMsg=0x0d861654) Line 104635
>> SQLService.exe!sqlite3Init(sqlite3 * db=0x0e427e38,
>> char * *
>> pzErrMsg=0x0d861654) Line 104814
>> SQLServ

[sqlite] No diagnostic given for missing column (since 3.7.10 up to 3.8.8.3)

[Dan Kennedy]

On 03/03/2015 03:57 PM, Andy Gibbs wrote:
> Hi,
>
> The following is a reduced test-case of a problem I have observed:
>
> CREATE TABLE a(tid,pid);
> CREATE TABLE b(tid,pname);
> CREATE TEMP TABLE pidmap(newpid INTEGER,oldpid INTEGER);
> CREATE TEMP TABLE pnamemap(pid INTEGER,pname TEXT COLLATE BINARY);
>
> (Please note that the tables are usually populated with data.)
>
> SELECT a.ROWID,b.ROWID FROM a
>  INNER JOIN b ON a.tid=b.tid
>  AND a.pid=(SELECT pid FROM pidmap WHERE pname=b.pname);
>
> The same problem occurs with a similar query:
>
> SELECT a.ROWID,b.ROWID FROM a
>  INNER JOIN b ON a.tid=b.tid
>  WHERE a.pid=(SELECT pid FROM pidmap WHERE pname=b.pname);
>
> The problem comes from an unintentional mistake: using the table
> pidmap in the sub-query, rather than pnamemap.
>
> Running the sub-query on its own gives (as expected):
>
> SELECT pid FROM pidmap;
> Error: no such column: pid
>
> This also results in an error:
>
> SELECT a.ROWID,b.ROWID FROM a
>  INNER JOIN b ON a.tid=b.tid
>  AND a.pid=(SELECT xyz FROM pidmap WHERE pname=b.pname);
> Error: no such column: xyz
>
> And, of course, correcting the original query works as expected:
>
> SELECT a.ROWID,b.ROWID FROM a
>  INNER JOIN b ON a.tid=b.tid
>  AND a.pid=(SELECT pid FROM pnamemap WHERE pname=b.pname);
>
> Seems to me that sqlite is resolving the column name from the incorrect
> scope in the sub-query?
>
> I have observed this in v3.7.10, but have also tested it in v3.8.8.3.

I think it's correct, no? Since there is no column "pid" to match 
against in the sub-query, the "pid" within the sub-query refers to 
"a.pid" from the outer query.

   http://en.wikipedia.org/wiki/Correlated_subquery

[sqlite] Multi-table index ersatz?

[Dan Kennedy]

On 03/03/2015 06:10 PM, Eric Grange wrote:
> Hi,
>
> I have problem where I need a "multi-table index" ersatz, or maybe a better
> data structure :-)
>
> The problem is as follow:
>
> - Table A : some fields plus fields A1 & A2
> - Table B : some fields plus fields B1 & B2
>
> Both tables have several dozen millions of rows, and both are accessed
> independently of each others by some queries, their current structure has
> no performance issues for those queries.
>
> However I have a new query which is like
>
> select ...some fields of A & B...
> from A join B on A.A2 = B.B2
> where A.A1 = ?1
> order by B.B1
> limit 100
>
>
> Without the limit, there can be tens of thousandths resulting rows, without
> the A1 condition, there can be millions of resulting rows.
>
> With indexes on A & B, the performance of the above is not very good, as
> indexing A1 is not enough, and indexing B1 is not enough either, so no
> query plan is satisfying.
>
> I can make the query instantaneous by duplicating the A1 & B1 fields in a
> dedicated C table (along with the primary keys of A & B), index that table,
> and then join back the A & B table to get the other fields.
>
> However this results in a fairly large table of duplicated data, whose sole
> purpose is to allow the creation of a fairly large index, which gets me the
> performance.

You might be able to make the new table a WITHOUT ROWID table and set 
its PRIMARY KEY up with the same (or a superset of the) fields of your 
"fairly large index" in order to save a bit of space.




>
> Note that if the fields A1 & B1 are removed from their tables and kept only
> in C, this has massive performance implication on other queries running
> only against A & B, as those fields are leveraged in other composite
> indexes.
>
> Is there a better way that would not involve duplicating the data?
>
> Eric
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Howto set lower pager page size when using zipvfs extension

[Dan Kennedy]

On 03/13/2015 09:30 PM, Alexandre Mainville wrote:
> Hi,
>
> When using the zipvfs extension,  one can change the size for the upper
> pager using pragma page_size but how does one change the page size for the
> lower level pager. The zipvfs_block_size pragma does not seem to support
> setting the value.

Enable URI filenames:

   https://www.sqlite.org/uri.html

and specify an option of the form "block_size=N". The lower level pager 
should use a page-size of N bytes to access the database file. e.g.

   file:test.db?block_size=4096

The setting is not persistent - each client should specify the 
block_size=N option separately.

Dan.





>
> Thanks
>
> Alex
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

[Dan Kennedy]

On 03/18/2015 02:39 AM, Bart Smissaert wrote:
> OK, let me try to explain:
>
> Simple table with primary integer key, call the field ID
>
> strSQL = "Insert into table1 values(?)"
>
> lReturnValue = sqlite3_prepare16_v2(lDBHandle,
>StrPtr(strSQL),
>Len(strSQL) * 2,
>lReturnStatementHandle,
>0)
>
> strSQL = "BEGIN TRANSACTION"
>
> lReturnValue = sqlite3_prepare16_v2(lDBHandle,
>StrPtr(strSQL),
>Len(strSQL) * 2,
>lReturnStatementHandle,
>0)
> lReturnValue = sqlite3_step(lStatementHandle)
> lReturnValue = sqlite3_finalize(lStatementHandle)
>
> In a loop, looping through rows of a VBA variant array:
>
> sqlite3_bind_int lStatementHandle, 1, vArray(r)
> sqlite3_step lStatementHandle
> sqlite3_reset lStatementHandle
>
> After this loop:
>
> lReturnValue = sqlite3_finalize(lStatementHandle)   < unexpected
> lReturnValue here
>
> strSQL = "COMMIT TRANSACTION"
>
> lReturnValue = sqlite3_prepare16_v2(lDBHandle,
>StrPtr(strSQL),
>Len(strSQL) * 2,
>lReturnStatementHandle,
>0)
> lReturnValue = sqlite3_step(lStatementHandle)
> lReturnValue = sqlite3_finalize(lStatementHandle)
>
>
>
> That is it.
> The result is fine and no duplicates are inserted, only thing wrong is the
> result the one from last sqlite3_finalize.
> Note that I don't check the return values in the loop, maybe I should, but
> it saves some time.

The last or second last sqlite3_finalize()?

If an error occurs in sqlite3_step(), then the following sqlite3_reset() 
or sqlite3_finalize() also returns an error. So you can write code like 
this:

   while( SQLITE_ROW==sqlite3_step(pStmt) ){
 // process row
   }
   rc = sqlite3_reset(pStmt);

and you are sure to catch any error. Replacing the sqlite3_reset() with 
finalize() would also catch any error code.

However, it is only the first sqlite3_reset() or sqlite3_finalize() 
following the error that also returns an error code. After that call has 
returned, the statement is back in "initial" state, so reset() or 
finalize() returns SQLITE_OK. So, if I do this:

   sqlite3_step(pStmt);
   rc = sqlite3_reset(pStmt);
   rc2 = sqlite3_finalize(pStmt);

and an error occurs in sqlite3_step(), then "rc" will be set to an error 
code, but "rc2" to SQLITE_OK. As by the time sqlite3_finalize() is 
called the statement is already back in "initial - no error has 
occurred" state.

Does that explain what you're seeing?

Dan.





>
>
> RBS
>
>
> On Tue, Mar 17, 2015 at 6:33 PM, Richard Hipp  wrote:
>
>> On 3/17/15, Bart Smissaert  wrote:
>>> Have a simple table with a primary integer key.
>>> Doing an insert (with no insert or ignore or insert or replace) with
>>> duplicate values
>>> for this primary integer key field produces zero on sqlite3_finalize, but
>>> 19 from sqlite3_errorcode.
>> I wrote a test program for this and I get 19 in both cases.  Maybe you
>> could share your test case with us?
>>
>>> I thought that the result value of sqlite3_finalize also should produce a
>>> non-zero value.
>>> This is with the latest version.
>>> Anything I am overlooking here or misunderstanding?
>>>
>>> RBS
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Source code for 3.8.7.4

[Dan Kennedy]

On 03/20/2015 04:11 PM, Saurav Sarkar wrote:
> Hi All,
>
> i have tried to download amalagamation source code for sqlite for verions
> 3.8.7.4
>
> By changing the url to
> http://www.sqlite.org/2015/sqlite-amalgamation-3080704.zip
>
> But the file could not be found.
>
> Could download for 3.8.8.2
>
> Can anyone please help ?

   http://www.sqlite.org/2014/sqlite-amalgamation-3080704.zip






>
> Thanks and Regards,
> Saurav
>
> On Thu, Mar 19, 2015 at 9:05 PM, Saurav Sarkar 
> wrote:
>
>> Hi Richard,
>>
>> Thanks for the info.
>> I got this link from the site for 3.8.7.4
>> http://www.sqlite.org/cgi/src/info/f66f7a17b78ba617
>>
>> Hope this is fine.
>>
>> I want this version because my project is using this release.
>>
>> Best Regards,
>> Saurav
>>
>> On Thu, Mar 19, 2015 at 8:38 PM, Richard Hipp  wrote:
>>
>>> On 3/19/15, Saurav Sarkar  wrote:
 Hi All,

 Can anyone please tell me how can i get the source code for 3.8.7.4 ?
 In the website of SQLite i could get hold of a amalgamation source code
>>> of
 latest release 3.8.8.3 in a zip file.

 Can i similarly get for 3.8.7.4 ?

>>> Over 14,400 different historical versions of SQLite are available at
>>> https://www.sqlite.org/src/
>>>
>>> Why do you want 3.8.7.4 instead of 3.8.8.3?
>>> --
>>> D. Richard Hipp
>>> drh at sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Assert on pager state when using zipvfs

[Dan Kennedy]

On 03/27/2015 08:19 PM, Alexandre Mainville wrote:
> Hi,
>
> Were are getting the following assert inside the
> sqlite3PagerWalFramesize when using zipvfs:
> assert( pPager->eState==PAGER_READER );
>
> Is this expected?
>
> Here are the steps to reproduce:
> begin transaction;
> create table test(id int, name text);
> insert into test values(, ); (I inserted a 100 rows like this)
> commit;
> delete from test where name like 'whatever';
>
> I attached the resulting file.

Hi Alex,

It's not expected. We will need more details to debug it though.

Can you post a stack trace at the assert() failure?

Which versions of SQLite and zipvfs are you using?

Are you able to reproduce this outside of a large application? Do you 
have example code?

Thanks,
Dan Kennedy.






>
> Thanks
>
> Alex
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] howto shrink files in wal mode

[Dan Kennedy]

On 05/07/2015 04:46 PM, Zaumseil Ren? wrote:
>> How are you measuring the size of the database file ?
>>
>> Simon.
> Using the tcl "file" command.
>
> puts b=[file size $dbfile]
>
> Please see in the script in the original post.

If you do the VACUUM before the "PRAGMA wal_checkpoint" instead of 
after, does that help?

Dan.

[sqlite] Fortify SCA Buffer Overflow in sqlite3.c

[Dan Kennedy]

On 05/14/2015 01:47 PM, Held, Douglas wrote:
> 
> A team of mine wants to use SQLCipher so I scanned it with Fortify SCA. 
> SQLCipher includes sqlite3.c version 3.8.8.3.  The software has reported a 
> Buffer Overflow (off-by-one) in the following C code:
>
> In sqlite3.c, it says the overflow can happen here on line 96492 when 'i' 
> gets down to 1:
>
> 96487: SQLITE_PRIVATE void sqlite3SrcListShiftJoinType(SrcList *p){
> 96488:   if( p ){
> 96489: int i;
> 96490: assert( p->a || p->nSrc==0 );
> 96491: for(i=p->nSrc-1; i>0; i--){
> 96492:   p->a[i].jointype = p->a[i-1].jointype;
> 96493: }
> 96494: p->a[0].jointype = 0;
> 96495:   }
> 96496: }
>
> The declaration of this buffer 'a' is on line 11973:
>
> 11946: struct SrcList {
> 11947:   int nSrc;/* Number of tables or subqueries in the FROM 
> clause */
> 11948:   u32 nAlloc;  /* Number of entries allocated in a[] below */
> 11949:   struct SrcList_item {
> 11950: Schema *pSchema;  /* Schema to which this item is fixed */
> 11951: char *zDatabase;  /* Name of database holding this table */
> 11952: char *zName;  /* Name of the table */
> 11953: char *zAlias; /* The "B" part of a "A AS B" phrase.  zName is 
> the "A" */
> 11954: Table *pTab;  /* An SQL table corresponding to zName */
> 11955: Select *pSelect;  /* A SELECT statement used in place of a table 
> name */
> 11956: int addrFillSub;  /* Address of subroutine to manifest a subquery 
> */
> 11957: int regReturn;/* Register holding return address of 
> addrFillSub */
> 11958: int regResult;/* Registers holding results of a co-routine */
> 11959: u8 jointype;  /* Type of join between this able and the 
> previous */
> 11960: unsigned notIndexed :1;/* True if there is a NOT INDEXED 
> clause */
> 11961: unsigned isCorrelated :1;  /* True if sub-query is correlated */
> 11962: unsigned viaCoroutine :1;  /* Implemented as a co-routine */
> 11963: unsigned isRecursive :1;   /* True for recursive reference in WITH 
> */
> 11964: #ifndef SQLITE_OMIT_EXPLAIN
> 11965: u8 iSelectId; /* If pSelect!=0, the id of the sub-select in 
> EQP */
> 11966: #endif
> 11967: int iCursor;  /* The VDBE cursor number used to access this 
> table */
> 11968: Expr *pOn;/* The ON clause of a join */
> 11969: IdList *pUsing;   /* The USING clause of a join */
> 11970: Bitmask colUsed;  /* Bit N (1< */
> 11971: char *zIndex; /* Identifier from "INDEXED BY " clause 
> */
> 11972: Index *pIndex;/* Index structure corresponding to zIndex, if 
> any */
> 11973:   } a[1]; /* One entry for each identifier on the list */
> 11974: };
>
> The analyzer says that the real length of this thing is 112 bytes long (can 
> someone verify that?) and that up above on line 96492, the write length into 
> the buffer is 224 bytes, at least when 'i' gets down to 1.

Perhaps "is 1 or greater" instead of "down to 1". I don't think it's an 
actual problem. The static analyzer is assuming that all pointers of 
type (SrcList*) point to objects of size sizeof(SrcList). But in SQLite 
this structure is actually allocated using:

   pSrcList = malloc(sizeof(SrcList) + (nSrc-1) * sizeof(struct 
SrcList_item));

where "nSrc" is the value that will be copied to pSrcList->nSrc. Thus, 
although it is declared as an array of size 1, pSrcList->a[] is actually 
pSrcList->nSrc elements in size.

Dan.

[sqlite] Segfault during FTS index creation from huge data

[Dan Kennedy]

On 05/19/2015 03:35 PM, Artem wrote:
> Hi!
>
> And what about result of our conversation?
> Can developers increase this limitations for using all memory that user
> have?

Hi Artem,

The conclusion was that although the first problem encountered is the 
massive allocation that FTS tries to make, fixing that won't actually 
improve things much. SQLite is hard-coded to limit the size of blobs to 
a bit under 1GiB. So even if we could merge these big doclists in 
memory, we're pretty close to the limit of what can be stored in the 
database anyway.

The 1GiB limit can be raised to 2GiB by recompiling SQLite with 
different options. But any further than that would require a redesign of 
the file-format.

So to fix this, we would need to change the format FTS4 uses to store 
the index within the database. Some sort of extension to allow it to 
spread the largest doclists across two or more database records. And 
unfortunately I suspect that will remain a low priority for the 
foreseeable future.

I've tested FTS5 with really large databases, and it seems to work. It's 
not actually released yet though.

Regards,
Dan.





>
>> One, you should remove sqlite-users at sqlite.org from your To list. I keep
>> bouncing email when I reply to you. Not a big deal, just an FYI.
>> Two:
>> On Sun, May 3, 2015 at 2:13 PM, James K. Lowden 
>> wrote:
>>> On Thu, 30 Apr 2015 12:47:57 -0600
>>> Scott Robison  wrote:
>>>
 Perhaps you are correct and "sigsegv" is not the literal signal that
 is triggered in this case. I don't care, really. The fact is that an
 apparently valid pointer was returned from a memory allocation
 function yet can result in an invalid access for whatever reason (out
 of memory, in this case). The Linux OOM killer may kill the offending
 process (which is what one would expect, but one would also expect
 malloc to return null, so we already know not to expect the
 expected). Or it may kill some other process which has done nothing
 wrong! Sure, the OS is protecting the two processes address space
 from one another, but it seems to me that if one process can kill
 another process, there is a problem.
>>> I have no argument with you, Scott.  It's neither the first nor last
>>> thing Linix implemented in the name of efficiency that undid what
>>> previously were guarantees.  My only angels-on-the-head-of-a-pin
>>> argument is that the OOM-killer doesn't invalidate malloc-returned
>>> pointers in particular.  It sweeps with a much broader brush, you might
>>> say.   ;-)
>>>
>> Okay, I think I see what you're saying, though there seem to be a number of
>> anecdotes online about people who do get a sigsegv from some simple memory
>> allocation strategies (designed to allocate all available memory). I would
>> not discount the possibility of a sigsegv, but agree that it is probably
>> not supposed to happen given the way optimistic memory allocation is
>> claimed to work.
>
>>> SIGSEGV *is* significant to the OP because it doesn't signify heap
>>> exhaustion.  If that signal was triggered in the heap, it indicates
>>> heap corruption.  If it was triggered in the stack, it suggests the
>>> stack might been exhausted, perhaps before a pure OOM condition was
>>> reached.
>>>
>> The code I was referring to in earlier posts performed a realloc. I wonder
>> if perhaps there is a corner case in there. Growing a block potentially
>> means moving a block, so if the library was copying from a previously
>> allocated block to a new block, maybe that could result in a segfault?
>> Or maybe another explanation could be that some other library other piece
>> of code replaced the default malloc-family functions.
>
>
>

[sqlite] Segfault during FTS index creation from huge data

[Dan Kennedy]

On 05/19/2015 05:54 PM, Artem wrote:
>> On 05/19/2015 03:35 PM, Artem wrote:
>>> Hi!
>>>
>>> And what about result of our conversation?
>>> Can developers increase this limitations for using all memory that user
>>> have?
>> Hi Artem,
>> The conclusion was that although the first problem encountered is the
>> massive allocation that FTS tries to make, fixing that won't actually
>> improve things much. SQLite is hard-coded to limit the size of blobs to
>> a bit under 1GiB. So even if we could merge these big doclists in
>> memory, we're pretty close to the limit of what can be stored in the
>> database anyway.
>> The 1GiB limit can be raised to 2GiB by recompiling SQLite with
>> different options. But any further than that would require a redesign of
>> the file-format.
>> So to fix this, we would need to change the format FTS4 uses to store
>> the index within the database. Some sort of extension to allow it to
>> spread the largest doclists across two or more database records. And
>> unfortunately I suspect that will remain a low priority for the
>> foreseeable future.
>> I've tested FTS5 with really large databases, and it seems to work. It's
>> not actually released yet though.
>> Regards,
>> Dan.
> Thank you very much, Dan. Now I understand the situation.
> Can  you  please tell me, when FTS5 will be released? Maybe I can test
> it on my data before official release?

Hi Artem,

I'm not sure exactly when fts5 will be released. Testing with a large 
data set would be extremely helpful though. Grab the "fts5" branch from 
fossil and run [make sqlite3.c] to create an amalgamation file that 
includes fts5. Build with -DSQLITE_ENABLE_FTS5.

For large data sets, I think it will help to run a command like:

   INSERT INTO tbl(tbl, rank) VALUES('pgsz', 4040);

after creating the FTS5 table (where "tbl" is the name of the new 
table). More here:

   https://sqlite.org/fts5.html#section_5_7

Thanks,
Dan.

[sqlite] Creating an index seems to break table logic

[Dan Kennedy]

On 05/20/2015 12:39 AM, Adam Podstawczy?ski wrote:
> Hi all,
>
> I have this schema:
>
>
> CREATE TABLE list_of_numbers (
>nn TEXT,
>astart INT,
>aend INT,
>alength INT,
>usesflag TEXT,
>blength INT,
>coolflag NUM,
>alphaid
> );

Thanks for the report. Are you able to cut and paste a shell tool 
session showing the query working without the index, creating the index, 
and then the query failing?

Also run "PRAGMA integrity_check" in the same session if possible.

Posting an unedited cut'n'paste from the shell tool causes bug reports 
to be treated more urgently - as everybody can proceed without worrying 
that the OP may have made a typo.

Thanks,
Dan.




>
> Some example data:
>
> nn  astart  aendalength usesflag  
>   blength coolflagalpaid
> --  --  --  --  
> --  --  --  --
> 1   701700  701700  9   Y 
>   10  0   1b9633407507819ni
> 1   701707  701703  9   Y 
>   10  0   1b6033960773078ni
> 1   701704  701705  9   Y 
>   10  0   1b9633407507819ni
> 1   701706  701706  9   Y 
>   10  0   1b6033960773078ni
> 1   701707  701707  9   Y 
>   10  0   1b6033939751871ni
> 1   701708  701709  9   Y 
>   10  0   1b9633407507819ni
>
> And this query:
>
> sqlite> select * from list_of_numbers where astart < 7169319380 and aend > 
> 7169319380;
>
> Now, the above query is expected to return one record only ? and it does:
>
> 1nn  astart  aendalength usesflag 
>blength coolflagalpaid
> --  --  --  --  
> --  --  --  --
> 1   716978  716983  3   Y 
>   10  0   1b3603393975150ni
>
> But when I add index to the very same table:
>
> CREATE INDEX startingnumber ON list_of_numbers(astart);
> CREATE INDEX endingnumber ON list_of_numbers(aend);
>
> The behavior of the same query becomes unpredictable:
>
> nn  astart  aendalength usesflag  
>   blength coolflagalpaid
> 1   716978  716983  3   Y 
>   10  0   1b3603393975150ni
> 1   701466  701466  3   Y 
>   10  0   1b3603396077307ni
> 1   701547  701547  3   Y 
>   10  0   1b3603393975187ni
> 1   701971  701971  3   Y 
>   10  0   1b3603396077307ni
> 1   703833  703833  3   Y 
>   10  0   1b3963340750704ni
> 1   705793  705793  3   Y 
>   10  0   1b3603393975173ni
> 1   705404  705404  3   Y 
>   10  0   1b3603393975171ni
> 1   705407  705407  3   Y 
>   10  0   1b3603393975171ni
>
>
> ... and a few dozens more results. Only the first one meets the query 
> constraints.
>
> Why is this happening?
>
> Thanks,
>
> Adam
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BestIndex problem

[Dan Kennedy]

On 05/21/2015 10:20 PM, Emmanouil Karvounis wrote:
> Greetings,
>
> We are having an issue with BestIndex in our Virtual Table implementation.
> Allow us to illustrate the problem with a simple example.
>
> We create a virtual table named 'vt' that conceptually has 3 columns
> "value1, value2, value3" and then we want to execute the following queries:
>
> 1) select value1 from vt where value1 = 7;
>
> In this case, BestIndex passes the equal constraint on the first column and
> by setting the output as pdxInfo->aConstraintUsage[0].argvIndex = 1,
> we indicate that we accept this specific constraint. So, at Filter we get
> the value 7 as argument0. Everything behaves normally so far.
>
> However, in case we run any of the following equivalent queries (that
> should pass to Filter more than one value), we get an error message
> "xBestIndex returned an invalid plan":
>
> 2) select value1 from vt where value1 = 7 or value1 = 8;
> 3) select value1 from vt where value1 in (select * from tableA);//
> suppose tableA contains an integer 'id' column and records (7, 8)
> 4) select value1 from vt, tableA where value1 = tableA.id;
>
> Again, in each case we set pdxInfo->aConstraintUsage[0].argvIndex = 1 but
> we get the above error message.
>
> This behavior seems rather weird, so we'd like some expert help on what we
> might be doing wrong.

That happens if you set the argvIndex variable on a constraint for which 
the "usable" flag is not set. For each constraint in the aConstraint[] 
array, you need to check that the "usable" flag is set - and ignore the 
constraint if it is not.

Search for "usable" in this section of the docs:

   https://www.sqlite.org/vtab.html#xbestindex

Dan.

[sqlite] (msync()+) If mmap_size is big enough, read() will never be needed, so what about enabling mmap on platforms that "lack of a unified buffer cache"?

[Dan Kennedy]

On 05/22/2015 02:37 PM, Mikael wrote:
> Hi,
>
> If mmap_size is big enough, read() will never be needed, so what about
> enabling mmap on platforms that "lack of a unified buffer cache"?
>
> I mean, on a 64bit platform, setting mmap_size to say 1TB should be fine -
>
> So what about enabling mmap_size on platforms that "lack of a unified
> buffer cache"?
>
>
> Also, if read() by some reason would be needed, there's msync() which is
> like a flush(), so here the "lack of a unified buffer cache" shouldn't be
> an issue anyhow?

I think part of the concern is two processes accessing the same SQLite 
database - one using mmap() and one using regular read() and write() calls.

Just out of interest, is it a specific platform that you're concerned about?

Dan.

[sqlite] Contentless FTS4 Tables

[Dan Kennedy]

On 05/23/2015 04:33 AM, ShadowMarta at yahoo.de wrote:
> Hi!
>
> I have made a Contentless FTS4 Table like:
>
> "CREATE VIRTUAL TABLE if not exists OCR USING fts4(content="", `FullOCR`
> varchar;"
>
> And managed to insert some data into it.
>
> I have 2 questions:
>
> 1.) How to get the proper COUNT on the table ?
>   The only query seems to work is:
>   "SELECT COUNT(*) FROM OCR_docsize;"  is this the right way to do it ?
>
> 2.) How to perform a "JOIN" operation with it?
>   "SELECT docid FROM OCR WHERE FullOCR MATCH 'framework';" result is "2".
>
>   "SELECT * FROM ART INNER JOIN OCR ON ART.ID = (SELECT docid FROM OCR 
> WHERE FullOCR MATCH 'framework') ORDER BY ID;"
>   Gives "SQL logic error or missing database:"
>
>   "SELECT * FROM ART INNER JOIN OCR_docsize ON ART.ID = (SELECT docid 
> FROM OCR WHERE FullOCR MATCH 'framework') ORDER BY ID;"
>   Gives me 9 results back - should be only 1 -
>
>   "SELECT * FROM ART NATURAL JOIN (SELECT docid FROM OCR WHERE FullOCR 
> MATCH 'framework') ORDER BY ID;"
>  Gives me 9 results back - should be only 1 -
>
> What I am missing here?

Are you able to make the database file available for download somewhere?

Thanks,
Dan.

[sqlite] Replace an open database

[Dan Kennedy]

On 05/27/2015 11:59 PM, Wade, William wrote:
> I have an application file that I'm considering moving to sqlite. Some 
> current behavior is:
>
> 1) Only one writing thread for the lifetime of a logical dataset.
>
> 2) The writing thread opens the file with exclusive write access (open fails 
> if there is another writer, no other writers allowed, but one writer plus 
> multiple readers is a common situation).
>
> 3) The writing thread prefers to delete any existing file. If it can't do 
> that (some readers currently have the file open) it gains an exclusive 
> read/write lock (consistent with no reader has a transaction in progress) and 
> truncates the file to zero length, writes its new header (including his own 
> uuid, indicating that this is logically a new file). When existing readers 
> get around to reading again, they will check that uuid, and handle the change 
> in writers "gracefully."
>
> I'm wondering how to implement that behavior while using sqlite as my 
> application file. I can imagine several solutions, but I'm not sure what 
> pitfalls to look for. In particular, haven't determined how to get sqlite to 
> write-open an existing empty file, as-if it were creating a new database.
>
> Possible solutions:
>
> 1) As part of the initial write transaction (at the sqlite client level), use 
> sqlite to remove all of the existing tables. There are a couple of issues 
> with this. If the old file was 200gb, I'd expect sqlite to "waste a lot of 
> time" updating its free-page structures. Also, if the new file only grows to 
> a few megabytes, I'd want the excess space to be recovered. I also want the 
> write-open to succeed if the existing file is corrupted.

This is by far the best solution. Attempting to manipulate an SQLite 
database file using file-system APIs while other clients have it open is 
at best an accident waiting to happen. Use SQLite APIs instead.

But instead of using a regular SQL transaction to drop all the old 
tables, use the backup API to clobber the existing database with the new 
one.

   https://www.sqlite.org/c3ref/backup_finish.html

Using the backup API, the clobber operation is still done as a regular 
SQLite transaction - so all the locking and notifying of other clients 
gets done right. The amount of IO (and CPU) required should depend on 
the size of the new db only, not the existing db size. And it won't 
matter if the existing db is corrupt or not - as the backup API never 
actually examines the contents of the existing database.

Dan




>
> 2) Implement my own VFS, such that old readers get read (and lock) errors 
> until they open a new connection, while my new writer "sees" this as a new 
> file even if the VFS wasn't able to delete the file at the OS level. Since 
> I'm just getting started with sqlite, I'd rather not have to dive into the 
> low-level guts of implementing a new VFS, if I can help it.
>
> 3) After gaining the exclusive lock, truncate the database file (and the 
> write-ahead log or rollback journal), create a new database file (somewhere 
> else, possibly a virtual database). Perform a raw copy of the new virtual 
> database into the old actual database file, toss the virtual database, and 
> then use sqlite to open the old (now properly formatted, almost empty) 
> database file as a writer.
>
> 4) Change all readers so that they close/reopen the database at all 
> transaction boundaries (so that when there are no current read transactions, 
> nobody else has the file open). I'm a bit worried about the performance 
> implications of this for small read transactions (although I have not tested 
> this). I can do this for all my existing clients, but I can't do this for 
> somebody using some other sqlite3 client (like the command-line client). Also 
> if some non-sqlite code has the file open without a lock (perhaps somebody is 
> currently copying the file using an os-level command), I'd prefer to make 
> that reader fail, rather delay my writer.
>
> Help please?
> Bill
>
> **
> This e-mail and any attachments thereto may contain confidential information 
> and/or information protected by intellectual property rights for the 
> exclusive attention of the intended addressees named above. If you have 
> received this transmission in error, please immediately notify the sender by 
> return e-mail and delete this message and its attachments. Unauthorized use, 
> copying or further full or partial distribution of this e-mail or its 
> contents is prohibited.
> **
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Replace an open database

[Dan Kennedy]

On 05/29/2015 12:59 PM, Zaumseil Ren? wrote:
> On 05/27/2015 11:59 PM, Wade, William wrote:
>
>>> 3) The writing thread prefers to delete any existing file. If it can't do 
>>> that (some readers currently have the file open) it gains an exclusive 
>>> read/write lock (consistent with no reader has a transaction in progress) 
>>> and truncates the file to zero length, writes its new header (including his 
>>> own uuid, indicating that this is logically a new file). When existing 
>>> readers get around to reading again, they will check that uuid, and handle 
>>> the change in writers "gracefully."
>>>
>> But instead of using a regular SQL transaction to drop all the old
>> tables, use the backup API to clobber the existing database with the new
>> one.
>>
>>https://www.sqlite.org/c3ref/backup_finish.html
>>
>> Using the backup API, the clobber operation is still done as a regular
>> SQLite transaction - so all the locking and notifying of other clients
>> gets done right. The amount of IO (and CPU) required should depend on
>> the size of the new db only, not the existing db size. And it won't
>> matter if the existing db is corrupt or not - as the backup API never
>> actually examines the contents of the existing database.
>>
>> Dan
> Interesting idea. Could this also a solution to my problem described in the 
> thread "emptying tables"?

Maybe. Using the backup API to clobber one database with another 
featuring a bunch of empty tables is likely quicker than actually 
deleting all the data from the original.

Dan.




>
>
> Rene
>
> Kernkraftwerk Goesgen-Daeniken AG
> CH-4658 Daeniken, Switzerland
>
> Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder 
> gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist 
> (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese 
> Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese 
> unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend 
> zu benachrichtigen. Besten Dank.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Replace an open database

[Dan Kennedy]

On 05/29/2015 02:35 PM, Dominique Devienne wrote:
> On Fri, May 29, 2015 at 8:48 AM, Dan Kennedy  wrote:
>
>> On 05/29/2015 12:59 PM, Zaumseil Ren? wrote:
>>
>>> On 05/27/2015 11:59 PM, Wade, William wrote:
>>>
>>>> But instead of using a regular SQL transaction to drop all the old
>>>> tables, use the backup API to clobber the existing database with the new
>>>> one.
>>>>
>>>> https://www.sqlite.org/c3ref/backup_finish.html
>>>>
>>>> Using the backup API, the clobber operation is still done as a regular
>>>> SQLite transaction - so all the locking and notifying of other clients
>>>> gets done right. The amount of IO (and CPU) required should depend on
>>>> the size of the new db only, not the existing db size. And it won't
>>>> matter if the existing db is corrupt or not - as the backup API never
>>>> actually examines the contents of the existing database.
>>>>
>>> Interesting idea. Could this also a solution to my problem described in
>>> the thread "emptying tables"?
>>>
>> Maybe. Using the backup API to clobber one database with another featuring
>> a bunch of empty tables is likely quicker than actually deleting all the
>> data from the original.
>
> Dan, can you please explain quickly why? Does the backup API work at the
> page level for example, and thus is more efficient than normal row level
> ops? Thanks, --DD

That's pretty much it. Backup API just blindly copies pages of data from 
the source database into the target. But running "DROP TABLE" or "DELETE 
FROM" has to, at a minimum, iterate through the b-tree structures and 
add the now unused pages to the database free list.

wrt to this diagram:

   https://www.sqlite.org/arch.html

the backup API works at the Pager level, bypassing B-Tree altogether.

Dan.













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

[sqlite] Replace an open database

[Dan Kennedy]

On 05/29/2015 03:27 PM, Zaumseil Ren? wrote:
>> On Fri, May 29, 2015 at 8:48 AM, Dan Kennedy <[hidden 
>> email]<http://sqlite.1065341.n5.nabble.com/user/SendEmail.jtp?type=node&node=82464&i=0>>
>>  wrote:
>>
>>> On 05/29/2015 12:59 PM, Zaumseil Ren? wrote:
>>>
>>>> On 05/27/2015 11:59 PM, Wade, William wrote:
>>>>
>>>>> But instead of using a regular SQL transaction to drop all the old
>>>>> tables, use the backup API to clobber the existing database with the new
>>>>> one.
>>>>>
> I have a database open, statements prepared and data inserted.
> Now I clobber these database using the backup API with a new empty one.
> The new database has the same structure (table,index).
> Can I use the already prepared statements?

If they were compiled with sqlite3_prepare_v2() they will still work. In 
this case SQLite will detect that your db schema has "changed" and 
automatically reprepare each statement object the first time 
sqlite3_step() is called.

If they were compiled with sqlite3_prepare() you will see SQLITE_SCHEMA 
errors.

Dan.







>
>
> Thank you,
> Rene
>
>
> Kernkraftwerk Goesgen-Daeniken AG
> CH-4658 Daeniken, Switzerland
>
> Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder 
> gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist 
> (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese 
> Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese 
> unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend 
> zu benachrichtigen. Besten Dank.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 explicitly set delimiter

[Dan Kennedy]

On 11/01/2015 06:39 PM, chromedout64 at yahoo.com wrote:
>   Is there an easy way to set an FTS5 table so that the only 
> delimiter/separator is a space and everything else, including all 
> punctuation, is a token? Some searching revealed that there is an 
> undocumented feature as a part of FTS3/4 that allows the actual delimiter to 
> be specified as part of table creation, but I'm not sure if this exists for 
> FTS5.

You could use the tokenchars option of the unicode61 or ascii tokenizers 
to specify that all characters except whitespace are token characters:

   https://www.sqlite.org/fts5.html#section_4_3_1

Dan.

[sqlite] FTS5 explicitly set delimiter

[Dan Kennedy]

On 11/02/2015 01:55 AM, chromedout64 at yahoo.com wrote:
> Thanks, I figured that this might be the case. What is the best way to 
> specify all characters except whitespace as part of a CREATE VIRTUAL TABLE 
> statement? Should you simply list each of the literal ascii characters such 
> as tokenchars '!@#$%' and so on.

I think you'll have to do the above, yes.

Dan.

[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database

[Dan Kennedy]

On 11/06/2015 12:36 PM, chromedout64 at yahoo.com wrote:
> I noticed that this update is not included in the official 3.9.2 release. How 
> is it determined when an update such as this one gets included in a release? 
> Thanks for any info.

Most likely as part of 3.10.0.

You can always download the SQLite trunk, either via fossil or via a 
link like:

   http://www.sqlite.org/src/tarball/SQLite-trunk.tgz?uuid=trunk

Then run [./configure && make amalgamation-tarball] to build an autoconf 
package.

Regards,
Dan.






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

[sqlite] FTS5 rank configuration option

[Dan Kennedy]

On 11/06/2015 02:26 PM, chromedout64 at yahoo.com wrote:
> The documentation says that a persistent rank can be set with
> INSERT INTO ft(ft, rank) VALUES('rank', 'bm25(10.0, 5.0)');
> However, I can only get it to work on the same database connection as the 
> query. Subsequent database connections seem to use the standard default 
> ranking. Is this by design?

Thanks for reporting this. Might be fixed here:

   http://sqlite.org/src/info/33e6606f5e497e81

Dan.




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

[sqlite] Database corruption using zipvfs on android 64 bits (x86_64)

[Dan Kennedy]

On 11/06/2015 11:57 PM, Alexandre Mainville wrote:
> Hi,
>
> I am experiencing database corruption using zipvfs on android 64
> bits (x86_64).
> The same program executing on 32 bits runs without problems.
>
> The program creates a new database on every run and does a series of
> inserts and updates (always the same series of operations).
>
> Both programs produce the same SQLITE_DEBUG trace up until the point the 64
> bit version outputs:
> zipvfs database is corrupt. Line 1100 of [9d6c1880fb]
> because of a zero size payload in a slot header.
>
> I am waiting on a device to test behavior with arm64-v8a.
>
> I am using sqlite 3.8.8.3 and zipvfs 3.8.8.
>
> If anyone has experienced the same or has some insight on this, it would be
> greatly appreciated.


Are you able to run this on a workstation? If so, try running the 
program under valgrind ( http://valgrind.org ).

One way to debug this might be to run the zipvfs integrity-check (i.e. 
ZIPVFS_CTRL_INTEGRITY_CHECK) after each transaction. If you can figure 
out which transaction is introducing the corruption and take a copy of 
the database before and after it is executed, we might be able to figure 
out what is going on based on that and the log below.

Another would be to capture the statements executed using an 
sqlite3_trace() callback, then try to use them to reproduce the 
corruption using the shell tool. Even if that doesn't produce the 
corruption, it will be worth running such a script under valgrind to 
search for uninitialized variable problems etc.

I can't see any bugfixes in the changelog since 3.8.8 to do with corrupt 
databases, but it might be worth upgrading to the latest zipvfs and 
sqlite to check if the problem is still present anyway.

Regards,
Dan.

[sqlite] Another unexpected source of SQLITE_BUSY_SNAPSHOT

[Dan Kennedy]

On 11/11/2015 01:19 AM, Florian Weimer wrote:
> * Dan Kennedy:
>
>> On 10/18/2015 10:27 PM, Florian Weimer wrote:
>>> My first surprise was SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE (see
>>> ?SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE?).  I have found another
>>> source of such snapshot failures with WAL-mode databases.
>>>
>>> I like to pre-compile my DML statements before starting transactions,
>>> mainly for the implied syntax check.  (But perhaps there are also
>>> performance benefits, too?I haven't checked.)
>>>
>>> Here is what I did (if my tracing is correct).  Unless mentioned
>>> otherwise, the operations succeed with SQLITE_OK.
>>>
>>> Open the database.
>>> Compile the UPDATE statement.
>>> Compile the ROLLBACK statement (used below).
>>> Start a BEGIN DEFERRED transaction (with a fresh statement).
>>> Reset the UPDATE statement.
>>> Step the UPDATE statement.
>>>  -> this fails with SQLITE_BUSY_SNAPSHOT
>>> Reset/step/reset the pre-compiled ROLLBACK statement.
>>> Start a BEGIN IMMEDIATE transaction (with a fresh statement).
>>>  -> this fails with SQLITE_BUSY_SNAPSHOT
>>> Start a BEGIN IMMEDIATE transaction (with a fresh statement).
>>> Reset the UPDATE statement.
>>>  -> this fails with SQLITE_BUSY_SNAPSHOT
>>>
>>> After the last failure, iterating through the list of stattements does
>>> not show that the UPDATE statement is busy.
>>
>> The SQLITE_BUSY_SNAPSHOT returned by the final call to sqlite3_reset()
>> on the UPDATE is not a new error - it has just saved the error code
>> from the error that occurred during the sqlite3_step() call in the
>> previous transaction.
> D'oh!  I mistakenly assumed using the sqlite3_prepare_v2() function
> would avoid that.  Thanks.
>
>> So technically the error did not occur within
>> the BEGIN IMMEDIATE transaction.
> Right.
>
> I have changed my wrapper for sqlite3_reset() to ignore the error
> value and removed the compensation code from my transaction monitor.
> The tests that previously failed occasionally still succeed.
>
>>> I don't know how risky pre-compiling UPDATE statement is.  For SELECT
>>> statements, it is problematic because they can lead to persistent
>>> transaction failures with SQLITE_BUSY_SNAPSHOT because the
>>> pre-compiled and partitial executed statement is busy and will block
>>> creating a new transaction.  I wonder if this can happen with UPDATE
>>> statements as well.
>> I don't quite understand this. Preparing an SQL statement may read the
>> database (in order to read the schema). But calling sqlite3_prepare()
>> doesn't leave the statement in a "partially executed" state.
> My concern is about sqlite3_step(UPDATE) without a following
> sqlite3_reset(UPDATE).  Perhaps I should change my wrapper to
> unconditionally call sqlite3_reset() after DML-related sqlite3_step(),
> whether the stepping operation succeeded or not.

For a DML statement, I usually just do:

   sqlite3_step(pStmt);
   rc = sqlite3_reset(pStmt);
   if( rc!=SQLITE_OK ){
 /* Handle error */
   }

I don't see why you would want to call step() but not reset() for a DML 
operation. And since reset() always returns either the same or a more 
specific error code, there isn't too much reason to ever catch the 
return value of step(). IMO.

Dan.

[sqlite] Delta Compression in RBU

[Dan Kennedy]

On 11/13/2015 08:06 AM, Philip Bennefall wrote:
> Something I forgot in my prior post; I found the delta creation code 
> in sqldiff.c so my question really concerns the combined delta code 
> found in RBU and sqldiff.c (both creating and applying deltas).


The versions of the delta creation and application code checked in to 
the sqlite source project are public domain. The original authors of the 
code re-licensed it.

Dan.

[sqlite] Information passing between xBestIndex and xFilter

[Dan Kennedy]

On 11/14/2015 12:33 AM, Johnny Wezel wrote:
> I think there is a flaw in information passing between the xBestIndex
> and xFilter methods in virtual tables.
>
> The information about the constraint operation in the aConstraint array
> can't reach xFilter. But how is xFilter to know how to set up the cursor
> when a statement like
>
> SELECT * FROM MyTable WHERE a > 10
>
> is given? I can pass the index covering a and I can make SQLite pass the
> constant 10 to xFilter, but not the > operation.
>
> IMHO xBestIndex should be called after xOpen as opposed to before and
> have a cursor parameter so I can set up the whole filtering information
> on my terms.
>
> Any thoughts on that?

The idea is that your xBestIndex method sets the 
sqlite3_index_info.idxNum and (optionally) the sqlite3_index_info.idxStr 
variables, the values of which are then passed to xFilter. So you have 
to invent some way for xBestIndex to encode the ">" that xFilter 
understands.

   https://www.sqlite.org/vtab.html#xbestindex
   https://www.sqlite.org/vtab.html#xfilter

See the rtree module for an example:

   http://www.sqlite.org/src/artifact/0f9b595bd0deb?ln=1635

I can see your point though. The simple cases would be easier if xFilter 
were passed something similar to the aConstraint[] array passed to 
xBestIndex.

Dan.

[sqlite] SQLITE vs. OSX mmap == inevitable catalog corruption?

[Dan Kennedy]

On 11/18/2015 03:37 PM, T?r?k Edwin wrote:
> On 11/17/2015 12:11 AM, Deon Brewis wrote:
>> We found that using SQLITE memory mapped I/O (mmap_size > 0) in OSX isn't 
>> usable in any way shape or form. It will inevitably lead to catalog 
>> corruption if you hard-reboot OSX, even without the database or application 
>> open.
>>
> I tried to turn on mmap I/O in SQLite 3.9.1 in our application on CentOS 7 
> and Debian Jessie, but after 24-48h of continous writes to the DB I always 
> get a corruption:
> SQLite result 0xb: database corruption at line 76915 of [767c1727fe]
> SQLite result 0xb: statement aborts at 10: [DELETE FROM revision_blocks WHERE 
> revision_id=:revision_id]
> Query "DELETE FROM revision_blocks WHERE revision_id=:revision_id" failed: 
> (code 0xb: database disk image is malformed) database disk image is malformed
>
> After this 'pragma integrity check' says:
> row 90814 missing from index sqlite_autoindex_revision_ops_1
> wrong # of entries in index sqlite_autoindex_revision_ops_1
> row 1046646 missing from index idx_revmap
> row 1046646 missing from index sqlite_autoindex_revision_blocks_1
> wrong # of entries in index idx_revmap
> wrong # of entries in index sqlite_autoindex_revision_blocks_1
>
> There are not reboots involved, just multiple processes accessing a WAL DB. 
> Without mmap I/O I've never seen corrupted DBs in our application.

As of yesterday, SQLite uses a read-only mapping in mmap mode. The db 
file is written using plain old write(), just as in non-mmap mode:

   http://sqlite.org/src/info/67c5d3c646c8198c

It would be interesting to know if this clears the problem in your 
environment.

Dan.

[sqlite] FTS5 prefix index documentation may be incorrect

[Dan Kennedy]

On 11/25/2015 05:11 AM, Charles Leifer wrote:
> The FTS5 prefix index documentation[1] seems to not be working. I've tried
> with SQLite 3.9.0 and 3.10.0 (2015-11-06) and both show the same error
> messages.

Thanks for reporting this. Now fixed here:

   http://sqlite.org/src/info/11eb8e877e2ba859

Dan.


>
> Examples:
>
> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3');
> Error: malformed prefix=... directive
> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix=2, prefix=3);
> Error: multiple prefix=... directives
>
> What does appear to work is using a comma:
>
> sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
> sqlite> .schema ft
> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3');
>
> 1: https://sqlite.org/fts5.html#section_4_2
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_errmsg and wide char

[Dan Kennedy]

On 11/27/2015 12:13 PM, Igor Korot wrote:
> Hi,
> Is there any way to have "sqlite3_errmsg" function return a wide char string?
> Or do a conversion in a portable way?

Will sqlite3_errmsg16() work for you?

   https://www.sqlite.org/c3ref/errcode.html

Dan.

[sqlite] SQLite slows by a factor of 1000 after foreign key violation

[Dan Kennedy]

On 10/01/2015 10:32 PM, Andrew Cunningham wrote:
> I have noticed that when FOREIGN KEY is in DEFERRED mode and a FOREIGN KEY
> violation occurs, SQLite will continue to run, but performance slows down
> by about 1000x. Breaking into the code shows that it seems to be
> continually re-reading the database. When I finally get to COMMIT , an
> exception is thrown, but this can take hours , literally as I have a single
> global transactions.
>
> Of course the solution is to debug the problem by setting the mode to
> IMMEDIATE, detecting the issue at the source, then returning to DEFERRED.
>
> But the '1000x slowdown' behavior is an interesting quirk I don't really
> understand.
>


SQLite uses a counter to enforce deferred foreign keys. Each time an 
operation violates a constraint it increments that counter.

If the counter is greater than 0, then for every operation that might 
potentially correct the violation (which means virtually every operation 
on any table that is the parent or child in an FK constraint) it does 
extra seeks to see if it really does. i.e. each time you insert into the 
parent table, it seeks within the child table to see if the insert fixes 
a foreign key violation. And if it does, the counter is decremented.

So we know there is overhead, but I'm quite surprised that it is 1000x 
slower.

How large is the database? So large that these extra seeks could be 
performing real IO (i.e. having to go all the way to the disk for every 
seek, not just finding the data in the OS cache)?

Thanks,
Dan.

[sqlite] (no subject)

[Dan Kennedy]

On 10/03/2015 03:19 AM, Andrew Cunningham wrote:
>
>
> SQLite uses a counter to enforce deferred foreign keys. Each time an
> operation violates a constraint it increments that counter.
>
> If the counter is greater than 0, then for every operation that might
> potentially correct the violation (which means virtually every
> operation
> on any table that is the parent or child in an FK constraint) it does
> extra seeks to see if it really does. i.e. each time you insert
> into the
> parent table, it seeks within the child table to see if the insert
> fixes
> a foreign key violation. And if it does, the counter is decremented.
>
> So we know there is overhead, but I'm quite surprised that it is 1000x
> slower.
>
> How large is the database? So large that these extra seeks could be
> performing real IO (i.e. having to go all the way to the disk for
> every
> seek, not just finding the data in the OS cache)?
>
>
> OK, that kinds of makes sense in explaining where the problem comes 
> from. I really do see an exponential growth in execution time 
> depending on how many INSERTS are made after the first FK violation 
> occurs.
>
> I have a very simple example that , for example, takes 0.2s with no FK 
> violations and 25s ( about  100x slower) when a single FK violation is 
> forced before I start doing the "mass creation".
>
> This is a small database, creating 500  parent objects, each with 50 
> child objects.
> If I change (double) the number of objects to 1000 the time changes to 
> 0.4s and 100s. It looks like an N^2 problem. So you can see that very 
> quickly my factor of 1000x is very easy to reach..
>
>
> When FK checking is in DEFERRED mode, the implication is that no FK 
> checking is made until the COMMIT.
>
> That's very useful, as often it is impossible to make the 
> modifications done in the correct order to satisfy all the 
> FK=IMMEDIATE constraints, even though the end result is a 
> "referentially correct" database.
>
> So what you are saying above makes DEFERRED mode a lot less useful.
>
> DISCLAIMER: I am use ODB ( the awesome C++/ORM tool) to do these tests.
>
>
>


Hi,

Ok. My first guess is that you are missing the "suggested index" on the 
child table. Without this, if there is a foreign key violation in the 
db, each insert on the parent table will cause a linear scan of the 
entire child table. With the index, it's just a simple seek.

https://www.sqlite.org/foreignkeys.html#fk_indexes

If you're unsure, open your database using the command line tool and 
dump the schema using the ".schema" command. Post that here and someone 
should be able to tell you if you're missing an index or not.

Dan.

[sqlite] incorrect R-tree documentation

[Dan Kennedy]

On 10/03/2015 02:04 AM, Clemens Ladisch wrote:
> Hi,
>
> the R-tree documentation says:
> | Attempts to insert something other than an integer into the first
> | column, or something other than a numeric value into the other
> | columns, will result in an error.
>
> This is not actually true:
>
>> create virtual table t using rtree(id, x1, x2);
>> insert into t values(null, null, null);
>> insert into t values('xxx', 'xxx', x'1234');
>> select * from t;
> 1|0.0|0.0
> 0|0.0|0.0

Thanks for pointing this out. Docs now updated here:

   http://sqlite.org/docsrc/info/56eab0136ce41732

Dan.

[sqlite] sqlite-users Digest, Vol 94, Issue 4

[Dan Kennedy]

On 10/06/2015 02:45 AM, Andrew Cunningham wrote:
>> Ok. My first guess is that you are missing the "suggested index" on the
>> child table. Without this, if there is a foreign key violation in the
>> db, each insert on the parent table will cause a linear scan of the
>> entire child table. With the index, it's just a simple seek.
>>
>> https://www.sqlite.org/foreignkeys.html#fk_indexes
>>
>> If you're unsure, open your database using the command line tool and
>> dump the schema using the ".schema" command. Post that here and someone
>> should be able to tell you if you're missing an index or not.
>>
>
> OK.
>
> D:\ODB\odb-examples-2.4.0\relationship>D:\ODB\sqlite3.exe test.db
> SQLite version 3.8.11.1 2015-07-29 20:00:57
> Enter ".help" for usage hints.
> sqlite> .schema
> CREATE TABLE "relation_employer" (
>"name" TEXT NOT NULL PRIMARY KEY);
> CREATE TABLE "relation_project" (
>"name" TEXT NOT NULL PRIMARY KEY);
> CREATE TABLE "relation_employee" (
>"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>"first" TEXT NOT NULL,
>"last" TEXT NOT NULL,
>"employer" TEXT NOT NULL,
>CONSTRAINT "employer_fk"
>  FOREIGN KEY ("employer")
>  REFERENCES "relation_employer" ("name")
>  DEFERRABLE INITIALLY DEFERRED);
> CREATE TABLE "relation_employee_projects" (
>"object_id" INTEGER NOT NULL,
>"value" TEXT NOT NULL,
>CONSTRAINT "object_id_fk"
>  FOREIGN KEY ("object_id")
>  REFERENCES "relation_employee" ("id")
>  ON DELETE CASCADE,
>CONSTRAINT "value_fk"
>  FOREIGN KEY ("value")
>  REFERENCES "relation_project" ("name")
>  DEFERRABLE INITIALLY DEFERRED);
> CREATE INDEX "relation_employee_projects_object_id_i"
>ON "relation_employee_projects" ("object_id");

I think you need indexes on the child key columns that don't already 
have them. i.e.

   CREATE INDEX relation_employee_projects_value_i ON 
relation_employee_projects_value(value);
   CREATE INDEX relation_employee_employer_i ON relation_employee(employer);

Dan.

[sqlite] Problem with sqlite3_db_filename

[Dan Kennedy]

On 10/08/2015 03:51 AM, Bart Smissaert wrote:
> As I understand it this should produce a filepointer to the filepath of the
> attached database, given the database handle of file the other database was
> attached to and the database name of the attached database. I checked all
> the return values and also did a select involving tables in both
> databases and all goes fine, so I can be sure that the other database is
> attached OK.
> All I get from sqlite3_db_filename is zero, so no valid file pointer. No
> error messages though.
>
> I am accessing sqlite3.dll (Windows 7) via a std_call dll as I am working
> in VBA here.
>
> Any suggestions what could be the problem?
>
> I am running 3.8.11.1
>

The program below works here.

I'm seeing full paths for databases "main" and "aux", and a zero-length 
nul-terminated string for "next" (the in-memory database).

Dan

-



#include 
#include 
#include 

int main(int argc, char **argv){
   int rc;
   sqlite3 *db;

   rc = sqlite3_open("test.db", &db);
   if( rc!=SQLITE_OK ){
 fprintf(stderr, "sqlite3_open: %s\n", sqlite3_errmsg(db));
 exit(1);
   }

   rc = sqlite3_exec(db, "ATTACH 'other.db' AS 'aux'", 0, 0, 0);
   if( rc!=SQLITE_OK ){
 fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
 exit(1);
   }

   rc = sqlite3_exec(db, "ATTACH ':memory:' AS 'next'", 0, 0, 0);
   if( rc!=SQLITE_OK ){
 fprintf(stderr, "sqlite3_exec: %s\n", sqlite3_errmsg(db));
 exit(1);
   }

   printf("main  db is: %s\n", sqlite3_db_filename(db, "main"));
   printf("aux   db is: %s\n", sqlite3_db_filename(db, "aux"));
   printf("next  db is: %s\n", sqlite3_db_filename(db, "next"));

   return 0;
}

[sqlite] compiling sqlite3 with FTS5 support

[Dan Kennedy]

On 10/09/2015 10:18 AM, chromedout64 at yahoo.com wrote:
> I noticed there was talk in the other thread of including FTS5 support in the 
> amalgamation. That would be great and it seems like that might solve my 
> problem. Is that correct?
>
> Also, I noticed what may be a couple typos in the FTS5 documentation at 
> https://www.sqlite.org/fts5.html.
> In the external content table section, the example states:
> CREATE TABLE fts USING fts5(a, c, content=tbl, content_rowid=d);
> I think it should be CREATE VIRTUAL TABLE instead of CREATE TABLE.
>
> Also, in the overview section, the text says:
> The expression to the right of the MATCH operator must be the nameof the FTS5 
> table. The expression on the left must be a text value specifyingthe term to 
> search for.I think the right and left's are backwards.

Thanks for these. Now fixed.

Dan.

[sqlite] fts5aux?

[Dan Kennedy]

On 10/15/2015 03:35 AM, Philip Bennefall wrote:
> Hi all,
>
> Are there any plans in fts5 to add support for something equivalent to 
> fts4aux? This would be of great use to me.

There is, but it's not actually finished or documented yet. It still 
does a linear scan of the entire FTS index for every query. To create 
the table:

   CREATE VIRTUAL TABLE vvv USING fts5vocab(, );

where  may be either "row" or "column".

Code is here: http://www.sqlite.org/src/artifact/85ebf2e93089c

Dan.

[sqlite] some thing wrong under android.

[Dan Kennedy]

On 10/16/2015 03:22 PM, ???_Ren? wrote:
> my code like below:
> --
>
>
> const char* sql =
> "begin;"
> "create table if not exists personal("
> "user_id integer primary key autoincrement,"
> "type int not null,"
> "user_name char(100) unique not null,"
> "password char(200),"
> "nick_name char(100),"
> "image blob(204800),"
> "round int,"
> "win int,"
> "kill int,"
> "death int,"
> "coin int,"
> "diamond int,"
> "vip int,"
> "level int,"
> "experience int,"
> "finished_story int,"
> "last_online_date int"
> ");"
> "create table if not exists fragment("
> "id integer primary key autoincrement,"
> "user_id int not null references personal(user_id),"
> "type int not null,"
> "amount int,"
> "finished_times int"
> ");"
> "create unique index if not exists fragment_index on fragment(user_id asc, 
> type asc);"
> "commit;"
> ;
>
>
> char* errmsg = 0;
> int ret = sqlite3_exec(m_db, sql, 0, 0, &errmsg);
> sqlite3_free(errmsg);
>
>
>
>
> --
> under ios, it work nice.
> but under android it don't work.
>
>
> i have debug this error, and find the rules are:
> 1).when i delete that statement, it work nice:
> "create unique index if not exists fragment_index on fragment(user_id asc, 
> type asc);"
>
>
> 2).when i delete these statements, it work nice too.
> "begin;"
> "commit;"
>
>
> so, i think it don't support create index in a transaction under android.
>
>
> do you have any suggest for me?

You may need to ensure that SQLite stores temp files in memory. Either 
by building with -DSQLITE_TEMP_STORE=3 or by executing "PRAGMA 
temp_store=memory" after opening the db connection.

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

Dan.




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

[sqlite] Compilation fails under Linux (Ubuntu) when FTS5 is enabled

[Dan Kennedy]

On 10/17/2015 06:47 PM, tonyp at acm.org wrote:
> What the subject says and I get this error:
>
> /tmp/cco8QBGJ.o: In function `fts5Bm25GetData':
> sqlite3.c:(.text+0xb521b): undefined reference to `log'
>
> (using the latest 3.9.1 release)

You'll need to add "-lm" to the command line.

Was this using one of the configure scripts? Or a hand-made makefile?

Dan.


>
> Any ideas?
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compilation fails under Linux (Ubuntu) when FTS5 is enabled

[Dan Kennedy]

On 10/18/2015 02:21 AM, tonyp at acm.org wrote:
> I'm trying to compile the shell using the amalgamation (three files 
> involved: shell.c sqlite3.c sqlite.h).
> This is a process I've done a zillion times before without problems.
> The only new thing I did was add the -DSQLITE_ENABLE_FTS5 to command 
> line.
>
> (Adding the -lm switch did not help.)

That's quite surprising. Please post the full command line and the 
resulting output.

Thanks,
Dan.



>
> -----Original Message- From: Dan Kennedy
> Sent: Saturday, October 17, 2015 4:35 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Compilation fails under Linux (Ubuntu) when FTS5 
> is enabled
>
> On 10/17/2015 06:47 PM, tonyp at acm.org wrote:
>> What the subject says and I get this error:
>>
>> /tmp/cco8QBGJ.o: In function `fts5Bm25GetData':
>> sqlite3.c:(.text+0xb521b): undefined reference to `log'
>>
>> (using the latest 3.9.1 release)
>
> You'll need to add "-lm" to the command line.
>
> Was this using one of the configure scripts? Or a hand-made makefile?
>
> Dan.
>
>
>>
>> Any ideas?
>> Thanks.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Another unexpected source of SQLITE_BUSY_SNAPSHOT

[Dan Kennedy]

On 10/18/2015 10:27 PM, Florian Weimer wrote:
> My first surprise was SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE (see
> ?SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE?).  I have found another
> source of such snapshot failures with WAL-mode databases.
>
> I like to pre-compile my DML statements before starting transactions,
> mainly for the implied syntax check.  (But perhaps there are also
> performance benefits, too?I haven't checked.)
>
> Here is what I did (if my tracing is correct).  Unless mentioned
> otherwise, the operations succeed with SQLITE_OK.
>
>Open the database.
>Compile the UPDATE statement.
>Compile the ROLLBACK statement (used below).
>Start a BEGIN DEFERRED transaction (with a fresh statement).
>Reset the UPDATE statement.
>Step the UPDATE statement.
> -> this fails with SQLITE_BUSY_SNAPSHOT
>Reset/step/reset the pre-compiled ROLLBACK statement.
>Start a BEGIN IMMEDIATE transaction (with a fresh statement).
> -> this fails with SQLITE_BUSY_SNAPSHOT
>Start a BEGIN IMMEDIATE transaction (with a fresh statement).
>Reset the UPDATE statement.
> -> this fails with SQLITE_BUSY_SNAPSHOT
>
> After the last failure, iterating through the list of stattements does
> not show that the UPDATE statement is busy.


The SQLITE_BUSY_SNAPSHOT returned by the final call to sqlite3_reset() 
on the UPDATE is not a new error - it has just saved the error code from 
the error that occurred during the sqlite3_step() call in the previous 
transaction. So technically the error did not occur within the BEGIN 
IMMEDIATE transaction.


>
> This seems to contradict this piece of the documentation:
>
> | If X starts a transaction that will initially only read but X knows
> | it will eventually want to write and does not want to be troubled
> | with possible SQLITE_BUSY_SNAPSHOT errors that arise because another
> | connection jumped ahead of it in line, then X can issue BEGIN
> | IMMEDIATE to start its transaction instead of just an ordinary
> | BEGIN. The BEGIN IMMEDIATE command goes ahead and starts a write
> | transaction, and thus blocks all other writers. If the BEGIN
> | IMMEDIATE operation succeeds, then no subsequent operations in that
> | transaction will ever fail with an SQLITE_BUSY error.
>
> 
>
> This happens with version 3.8.11.1 on Fedora 22.
>
> I don't know how risky pre-compiling UPDATE statement is.  For SELECT
> statements, it is problematic because they can lead to persistent
> transaction failures with SQLITE_BUSY_SNAPSHOT because the
> pre-compiled and partitial executed statement is busy and will block
> creating a new transaction.  I wonder if this can happen with UPDATE
> statements as well.

I don't quite understand this. Preparing an SQL statement may read the 
database (in order to read the schema). But calling sqlite3_prepare() 
doesn't leave the statement in a "partially executed" state.

So if you do:

   sqlite3_prepare(UPDATE);
   BEGIN;
 sqlite3_prepare(SELECT);
 sqlite3_step(UPDATE);

then I guess you might get an SQLITE_BUSY_SNAPSHOT error (if the 
prepare() call actually needed to read the database), but if you do:

   sqlite3_prepare(SELECT);
   sqlite3_prepare(UPDATE);
   BEGIN;
 sqlite3_step();

then I don't think you can get an SQLITE_BUSY_SNAPSHOT error.

Dan.

[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database

[Dan Kennedy]

On 10/27/2015 05:06 PM, chromedout64 at yahoo.com wrote:
> Using the example from the documentation at https://www.sqlite.org/fts5.html
> I thought this worked in a previous version.
>
> CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
> CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', 
> content_rowid='a');
> CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
>INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
> END;
> CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
>INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, 
> old.c);
> END;
> CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
>INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, 
> old.c);
>INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
> END;
>
> REPLACE INTO tbl VALUES(1,'foo','bar');

I think this may have worked with an older version of FTS5 that did not 
support REPLACE.

The trouble is that because the top-level statement uses OR REPLACE 
conflict handling, so do the INSERT statements fired by the triggers. 
And OR REPLACE conflict handling was broken for external content tables.

Fts5 is now updated on trunk so that OR REPLACE is ignored for 
contentless and external content tables:

   http://sqlite.org/src/info/a85c2a4758c27e8d

So this sort of thing should work again.

Dan.

[sqlite] SQLite3 trunk error with old database with fts3/4

[Dan Kennedy]

On 09/04/2015 07:35 PM, Domingo Alvarez Duarte wrote:
> Hello !
>
> After fix the index issues using an old sqlite3 executable (the trunk refuse
> to work on indexes created with single quotes on field names) I'm getting
> ocasionaly memory errors when using fts3/4 searches, see error below:
>
> free(): corrupted unsorted chunks: 0x7fa3a01073a0

Is this error on the trunk or with the old version?

If it's on the trunk, is the error reproducible using the sqlite3 shell 
tool?

If not, what does valgrind have to say about the app?

Thanks,
Dan.

[sqlite] SQLite3 trunk error with old database with fts3/4

[Dan Kennedy]

On 09/04/2015 09:18 PM, Domingo Alvarez Duarte wrote:
> Hello !
>
> I'm not sure where the problem is but this code worked without any problem
> with previous sqlite3.
>
> Here is a backtrace of a segfault using gdb (the line numbers will not match
> standard sqlite3.c because I have some custom extensions):

Extensions in the sense that the core SQLite code has been enhanced? Or 
extensions as in code that interacts with SQLite using only APIs that 
begin with "sqlite3_"?

If you run the app under valgrind are there any interesting errors or 
warnings?

>
> enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
>
> Program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread 0x73c70700 (LWP 22336)]
> 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>  pDef=0x)
>  at sqlite3.c:66869
> 66869  if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
> ){
> (gdb) bt
> #0  0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>  pDef=0x)
>  at sqlite3.c:66869
> #1  0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
>  p4type=-1431655766, p4=0x7fffe4181588)
>  at sqlite3.c:66884
> #2  0x00479f14 in vdbeFreeOpArray (db=0x7fffe478,
>  aOp=0x7fffe40df508, nOp=)
>  at sqlite3.c:66933
> #3  0x0047a01c in sqlite3VdbeClearObject (db=0x7fffe478,
>  p=0x7fffe408ac88) at sqlite3.c:68920
> #4  0x0047a0c3 in sqlite3VdbeDelete (p=0x7fffe408ac88)
>  at sqlite3.c:68941
> #5  0x004e6044 in sqlite3VdbeFinalize (p=0x7fffe408ac88)
>  at sqlite3.c:68861
> #6  0x004e60cd in sqlite3_finalize (pStmt=0x7fffe408ac88)
>  at sqlite3.c:70500

It's tricky to interpret this. It seems likely that the pDef pointer in 
the last frame might be incorrect - or that might just be an artifact of 
optimization.

Thanks,
Dan.

[sqlite] SQLite3 trunk error with old database with fts3/4

[Dan Kennedy]

On 09/04/2015 09:29 PM, Domingo Alvarez Duarte wrote:
> Hello again !
>
> On mac os x some time ago I was getting segfaults here and tought that it was
> caused by the way os x manage memory but it doesn't seem correct.
>
> The error happens on this code that is called before call sqlite3_close:
>
>  sqlite3 *db = sdb->db;
>  sqlite3_stmt* statement = NULL;
>  int count = 0;
>  while ((statement = sqlite3_next_stmt(db, statement)))
>  {
>  //do no close statements because garbage collector will
> do it
>  //on MacOSX we get segfaults finalizing statements here
>  printf("sq_sqlite3_close_release:stmt:%s\n",
> sqlite3_sql(statement));
>  sqlite3_finalize(statement);
>  count++;
>  }
>  if (count) return sq_throwerror(v, _SC("closing database with
> %d statements not closed."), count);

Hi,

Two problems:

After you have finalized a statement handle, it may not be passed to 
sqlite3_next_stmt(). Change the while() line to:

   while( (statement = sqlite3_next_stmt(db, NULL)) ){ ...

Another reason not to do this before calling sqlite3_close() is that the 
FTS module may be managing some of these statement handles. So if you 
finalize() them before sqlite3_close() is called, then when the FTS 
module is shut down as part of the eventual sqlite3_close() call, it may 
pass the same statement handle pointers to sqlite3_finalize() - similar 
to a double-free of any other object or memory allocation. SQLite 
includes checks to try to return SQLITE_MISUSE instead of crashing when 
this happens, but they only work some of the time - this scenario can 
still cause crashes or heap corruption.

A workaround is to call sqlite3_close() on the db, then do the above 
only if it returns SQLITE_BUSY. This works because, even though it 
fails, the first sqlite3_close() shuts down the FTS module - 
guaranteeing that it is no longer holding pointers to statement handles.

Even better is not to leak statement handle pointers. The 
sqlite3_next_stmt() API should really only be used to help track down 
leaks, not to do cleanup.

Dan.








> 
>>   Fri Sep 04 2015 4:18:01 pm CEST CEST from "Domingo Alvarez Duarte"
>>   Subject: Re: [sqlite] SQLite3 trunk error 
>> with
>> old database with fts3/4
>>
>>   Hello !
>>
>> I'm not sure where the problem is but this code worked without any problem
>> with previous sqlite3.
>>
>> Here is a backtrace of a segfault using gdb (the line numbers will not
>> match
>> standard sqlite3.c because I have some custom extensions):
>>
>> enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
>>
>> Program received signal SIGSEGV, Segmentation fault.
>> [Switching to Thread 0x73c70700 (LWP 22336)]
>> 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>>  pDef=0x)
>>  at sqlite3.c:66869
>> 66869  if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
>> ){
>> (gdb) bt
>> #0  0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>>  pDef=0x)
>>  at sqlite3.c:66869
>> #1  0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
>>  p4type=-1431655766, p4=0x7fffe4181588)
>>  at sqlite3.c:66884
>> #2  0x00479f14 in vdbeFreeOpArray (db=0x7fffe478,
>>  aOp=0x7fffe40df508, nOp=)
>>  at sqlite3.c:66933
>> #3  0x0047a01c in sqlite3VdbeClearObject (db=0x7fffe478,
>>  p=0x7fffe408ac88) at sqlite3.c:68920
>> #4  0x0047a0c3 in sqlite3VdbeDelete (p=0x7fffe408ac88)
>>  at sqlite3.c:68941
>> #5  0x004e6044 in sqlite3VdbeFinalize (p=0x7fffe408ac88)
>>  at sqlite3.c:68861
>> #6  0x004e60cd in sqlite3_finalize (pStmt=0x7fffe408ac88)
>>  at sqlite3.c:70500
>>
>>
>>   
>>> Fri Sep 04 2015 4:05:12 pm CEST CEST from "Dan Kennedy"
>>>  Subject: Re: [sqlite] SQLite3 trunk error with 
>>> old
>>> database with fts3/4
>>>
>>> On 09/04/2015 07:35 PM, Domingo Alvarez Duarte wrote:
>>>
>>>   
>>>> Hello !
>>>>
>>>> After fix the index issues using an old sqlite3 executable (the trunk
>>>> refuse
>>>> to work on indexes created with single quotes on field names) I'm getting
>>>> ocasionaly memory errors when using fts3/4 searches, see error below:
>>>>
>>>> free(): corrupted unsorted chunks: 0x7fa3a01073a0
>>>>
>>>>
>>>   Is this error on

[sqlite] SQLite3 trunk error with old database with fts3/4

[Dan Kennedy]

On 09/04/2015 10:13 PM, Domingo Alvarez Duarte wrote:
> Hello again !
>
> I looked at the documentaion again and realized that I was alread calling
> sqlite3_close_v2 the I commented out all of the sqlite3_next_stmt cleanup and
> no segfaults so far, I'll see if memory is released on a long running process
> to certify that everything is fine.

The trouble with sqlite3_close_v2() is that after you call it you can't 
safely pass the db handle to sqlite3_next_stmt() - the data structure 
may have already been freed.

Dan.



>
> Thanks a lot for your help !
>
>
>
>>   Fri Sep 04 2015 4:57:57 pm CEST CEST from "Domingo Alvarez Duarte"
>>   Subject: Re: [sqlite] SQLite3 trunk error 
>> with
>> old database with fts3/4
>>
>>   Hello !
>>
>> I did something similar to your sugestion (sqlite3_next_stmt(db, NULL))
>> and
>> it still segfaults.
>>
>> What you mention about fts3/4 having prepared statemtns that and somehow
>> I'm
>> doing a double free a good point.
>>
>> And will be sad to not be able to use sqlite3_next_stmt(db, NULL) to
>> finalize
>> any open preapred statemnt, because it's very handy when using "exceptions"
>> and having a single point to do the cleanup.
>>
>> Can somehow sqlite3_prepare somehow have any extra parameter to indicated
>> that we are using it from an extension and somehow sqlite3_next_stmt detect
>> it and skip it ?
>>
>> Or any way to safely have a central point to do a cleanup ?
>>
>> Cheers !
>>
>>   
>>> Fri Sep 04 2015 4:44:02 pm CEST CEST from "Dan Kennedy"
>>>  Subject: Re: [sqlite] SQLite3 trunk error with 
>>> old
>>> database with fts3/4
>>>
>>> On 09/04/2015 09:29 PM, Domingo Alvarez Duarte wrote:
>>>
>>>   
>>>> Hello again !
>>>>
>>>> On mac os x some time ago I was getting segfaults here and tought that it
>>>> was
>>>> caused by the way os x manage memory but it doesn't seem correct.
>>>>
>>>> The error happens on this code that is called before call sqlite3_close:
>>>>
>>>> sqlite3 *db = sdb->db;
>>>> sqlite3_stmt* statement = NULL;
>>>> int count = 0;
>>>> while ((statement = sqlite3_next_stmt(db, statement)))
>>>> {
>>>> //do no close statements because garbage collector will
>>>> do it
>>>> //on MacOSX we get segfaults finalizing statements here
>>>> printf("sq_sqlite3_close_release:stmt:%s\n",
>>>> sqlite3_sql(statement));
>>>> sqlite3_finalize(statement);
>>>> count++;
>>>> }
>>>> if (count) return sq_throwerror(v, _SC("closing database with
>>>> %d statements not closed."), count);
>>>>
>>>>
>>>   Hi,
>>>
>>> Two problems:
>>>
>>> After you have finalized a statement handle, it may not be passed to
>>> sqlite3_next_stmt(). Change the while() line to:
>>>
>>> while( (statement = sqlite3_next_stmt(db, NULL)) ){ ...
>>>
>>> Another reason not to do this before calling sqlite3_close() is that the
>>> FTS module may be managing some of these statement handles. So if you
>>> finalize() them before sqlite3_close() is called, then when the FTS
>>> module is shut down as part of the eventual sqlite3_close() call, it may
>>> pass the same statement handle pointers to sqlite3_finalize() - similar
>>> to a double-free of any other object or memory allocation. SQLite
>>> includes checks to try to return SQLITE_MISUSE instead of crashing when
>>> this happens, but they only work some of the time - this scenario can
>>> still cause crashes or heap corruption.
>>>
>>> A workaround is to call sqlite3_close() on the db, then do the above
>>> only if it returns SQLITE_BUSY. This works because, even though it
>>> fails, the first sqlite3_close() shuts down the FTS module -
>>> guaranteeing that it is no longer holding pointers to statement handles.
>>>
>>> Even better is not to leak statement handle pointers. The
>>> sqlite3_next_stmt() API should really only be used to help track down
>>> leaks, not to do cleanup.
>>>
>>> Dan.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>   
>>>>   
>>>>> Fri Sep 04 2015 4:18:01 pm CEST CEST from "Do

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Dan Kennedy]

On 09/06/2015 09:23 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/06/2015 06:16 AM, Markus Weiland wrote:
>> I've discovered a potential bug in handling of SQLite database
>> files on gvfs mounted network shares.
> SQLite doesn't support being stored on the network for several
> reasons, including that network file protocols don't implement fully
> and correctly various access and locking, and that some journaling
> modes like WAL require shared memory and hence can't be networked.
> This is covered in the FAQ:
>
>https://www.sqlite.org/faq.html#q5
>
> Or if you want to be pedantic, it is gvfs and networked filesystems
> that have the bug.

The other problem is that it's 2.8.17. I think the most recent bugfix on 
the 2.* line was in 2007. 2.8.17 was the last release, and that was in 
December 2005.

Dan.

[sqlite] FTS: Escaping MATCH expressions

[Dan Kennedy]

On 09/09/2015 07:56 PM, Lohmann, Niels, Dr. (CQTN) wrote:
> Hi there,
>   
> I have a question regarding the expressions that are valid after MATCH: Is 
> there a way to escape a string str such that I can safely bind it to variable 
> @var in a statement like "SELECT * FROM myFtsTable WHERE myFtsTable MATCH 
> @var;"?
>   
> In particular, I encountered error messages with strings like "TEST.*" or 
> "TEST'*".

I don't think there is a foolproof way to do that with FTS4. Enclosing 
the text in double-quotes might help, but then there is no way to escape 
embedded double quotes.

In FTS5 you can enclose tokens in double quotes and escape embeded quote 
characters in the usual SQL way (by doubling them). i.e.

   ... MATCH '"TEST.*"'

Or for {TEST"*}:

   ... MATCH '"TEST""*"'

Dan.




>   
> All the best
> Niels
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_READONLY_ROLLBACK due to expected race?

[Dan Kennedy]

On 09/09/2015 08:48 PM, Matthew Flatt wrote:
> The documentation for SQLITE_READONLY_ROLLBACK suggests that it will
> only happen as a result of a previous crash or power failure, where a
> hot journal is left behind. I'm seeing that error without those events
> and with a small number of concurrent readers and writers.
>
> In the implementation of SQLite, comments in hasHotJournal() mention
> the possibility of a false positive (referencing ticket #3883, which I
> cannot find) and how it will be handled in the playback mechanism after
> obtaining an exclusive lock. The check for a read-only connection after
> hasHotJournal() is called, however, happens (and must happen?) before
> that exclusive lock is acquired. So, it seems like the race-provoked
> false positive could trigger a false SQLITE_READONLY_ROLLBACK error.
>
> If that's right, then for my application's purposes, it works to detect
> SQLITE_READONLY_ROLLBACK and just try again. I'd like to make sure I'm
> not missing some other problem in my system, though.


I think your analysis is correct.

If you open a database that has a journal file with a read-only connect, 
stop it here using the debugger:

   http://www.sqlite.org/src/artifact/4784012f80b21?ln=4875

delete the journal file and then let the process continue, you do indeed 
get an SQLITE_READONLY_ROLLBACK error.

Dan.



>
> Thanks!
> Matthew
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How stable is FTS5 considered?

[Dan Kennedy]

On 09/14/2015 01:45 AM, Abilio Marques wrote:
> Hi,
>
>
>
> I've been into this mailing list for a month now, and I think I've heard
> FTS5 mentioned a couple of times. Back when I first saw it, I remember it
> to be labeled with something close to beta or preliminary.
>
>
>
> Long story short, I've previously worked with a dedicated search engine
> called Sphinx Search. One of the things people love about it, is it's
> ability to be linked to Snowball (http://snowball.tartarus.org), which is a
> project created by Dr. Martin Porter. This code includes stemmers in
> several other languages (Spanish, French, Portuguese, Italian, German,
> Dutch, Swedish, Norwegian, Danish, Russian, Finnish and even an improved
> English version), which would be an upgrade over the present FTS5 condition:
>
>
>
> "The porter stemmer algorithm is designed for use with English language
> terms only - using it with other languages may or may not improve search
> utility."
>
>
>
> I'm thinking about a possible approach to get Snowball working with SQLite.
> I believe an extension is the way to go, as Snowball is published under the
> BSD license (and so I guess it cannot be mixed with public domain code).
>
>
>
> But I have no experience mixing BSD and public domain, so anyone with more
> information can shed a light on that matter?
>
>
>
> Second, and the most important question for me is, can I consider FTS5
> stable enough to start working on the extension?

I think so.

The custom tokenizer API changed just recently in order to support synonyms:

   http://www.sqlite.org/src/info/0b7e4ab8abde3ae3

but I don't expect it to change again. The updated API is described here:

   http://sqlite.org/draft/fts5.html#section_7_1

For example code, see the built-in tokenizers:

   http://www.sqlite.org/src/artifact/f380f46f341af9c9

Dan.

[sqlite] FTS5 documentation typo

[Dan Kennedy]

On 09/14/2015 06:31 PM, Abilio Marques wrote:
> While reading the documentation draft for FTS5, I spotted this on
> section 4.3.3 .
> I believe the first example is missing quotation marks around the word 
> 'porter':
>
> *-- Two ways to create an FTS5 table that uses the porter tokenizer to
> -- stem the output of the default tokenizer (unicode61). *
> CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = porter);
> CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter unicode61');

Hi,

I think it's Ok as is. An "option value" can be eitehr an FTS5 bareword 
or string literal. See the 4th paragraph here:

   https://www.sqlite.org/fts5.html#section_4

Dan.