Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

2014-10-07 Thread Dan Kennedy

On 10/08/2014 01:52 AM, Sohail Somani wrote:
Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*", 
though it did work as expected with the older version. I'd suggest 
keeping the old behaviour unless there is a performance-based reason 
not to.


On 2014-10-07, 2:49 PM, Sohail Somani wrote:

SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';

COMMIT;

BEGIN TRANSACTION;

INSERT INTO t(key,value0,value1) VALUES('$key','l0l','h4x');

COMMIT;

SELECT '--- 2 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';



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


Unfortunately, this was a break with backwards compatibility in response 
to this issue:


https://www.mail-archive.com/sqlite-users@sqlite.org/msg83345.html

Before:

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

(version 3.8.6), it was up to the specific tokenizer being used whether 
or not the special characters *, ", ( and ) were available to the query 
parser. After that commit, they are stripped out first. So with the new 
version, your query is now equivalent to "MATCH '101* h4x*'".


This should only affect FTS tables that use custom tokenizers (not the 
default simple or porter tokenizers).


Dan.







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


Re: [sqlite] 50% faster than 3.7.17

2014-10-09 Thread Dan Kennedy

On 10/09/2014 04:38 PM, David Woodhouse wrote:

On Thu, 2014-09-25 at 11:13 +0100, David Woodhouse wrote:

I suggested a couple of specific optimisations which the query planner
might be able to make, which should hopefully have benefits wider than
just my own use case. Are those not viable?

I'm preparing to commit a workaround to Evolution to avoid this issue,
and then move on with my life and forget about it.

Before I do, is it worth me rephrasing this as a 'suboptimal query plan'
bug report so it gets tracked and might get attention later? Or should I
just forget the idea of getting it fixed in sqlite?



Well, you could always create a patch...

I think I understand the second optimization. You're saying that given this:

  SELECT DISTINCT main.uid
 FROM main LEFT JOIN email_list ON main.uid = email_list.uid
 WHERE email_list.email LIKE 'al%'

SQLite should deduce that since the LIKE implies "email_list.email IS NOT NULL" 
the LEFT JOIN is equivalent to a regular JOIN. Which would allow SQLite to reorder the 
tables and perhaps come up with a more efficient query plan. Correct?

Seems like a reasonable idea.

The first optimization might be trickier. With queries that feature a single 
table:

  SELECT cols FROM tbl WHERE a=? OR b=? OR c=?

the planner may elect to run something very close to this:

  SELECT cols FROM tbl WHERE a=?
UNION ALL
  SELECT cols FROM tbl WHERE b=?
UNION ALL
  SELECT cols FROM tbl WHERE c=?

However, after returning each row, we remember its PRIMARY KEY (either the 
rowid or real PK for WITHOUT ROWID tables). Similar transformations for 
individual loops within join queries are also possible.

However, with a JOIN query, we don't currently attempt this kind of transform. 
I think because we would have to create some kind of composite key to use in 
place of the PRIMARY KEY to avoid returning duplicates. I guess queries that 
have a DISTINCT clause don't have this problem. So it could in theory transform 
your query to:

  SELECT DISTINCT
   main.uid FROM main LEFT JOIN email_list ON main.uid = email_list.uid
   WHERE email_list.email LIKE 'al%'
UNION ALL
  SELECT main.uid FROM main LEFT JOIN email_list ON main.uid = email_list.uid
   WHERE main.first_name like 'al%'
UNION ALL
  SELECT main.uid FROM main LEFT JOIN email_list ON main.uid = email_list.uid
   WHERE main.last_name like 'al%';

The the hypothetical optimization above could figure out that the first LEFT 
JOIN could just as easily be a JOIN.

And that the other two LEFT JOINs are not required at all due to the DISTINCT 
and the fact that the WHERE clauses on the sub-selects do not reference the 
joined table at all.

It seems like there are a few moving parts here. And none of these are trivial 
changes. So, good ideas that might show up in an SQLite release at some point, 
but it's not realistic to expect these optimizations to be implemented quickly. 
Unless, of course, you can propose a patch and they turn out to be simpler than 
they look.

Regards,
Dan.






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


Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

2014-10-09 Thread Dan Kennedy

On 10/09/2014 01:13 AM, Sohail Somani wrote:

On 2014-10-07, 4:04 PM, Dan Kennedy wrote:

On 10/08/2014 01:52 AM, Sohail Somani wrote:

Figured it out: match terms should be "l0l* h4x*" NOT "*l0l* *h4x*",
though it did work as expected with the older version. I'd suggest
keeping the old behaviour unless there is a performance-based reason
not to.

On 2014-10-07, 2:49 PM, Sohail Somani wrote:

SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';

COMMIT;

BEGIN TRANSACTION;

INSERT INTO t(key,value0,value1) VALUES('$key','l0l','h4x');

COMMIT;

SELECT '--- 2 ---';
SELECT COUNT(*) FROM t_fts WHERE t_fts MATCH '*l0l* *h4x*';


Unfortunately, this was a break with backwards compatibility in response
to this issue:

https://www.mail-archive.com/sqlite-users@sqlite.org/msg83345.html


Before:

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

(version 3.8.6), it was up to the specific tokenizer being used whether
or not the special characters *, ", ( and ) were available to the query
parser. After that commit, they are stripped out first. So with the new
version, your query is now equivalent to "MATCH '101* h4x*'".

This should only affect FTS tables that use custom tokenizers (not the
default simple or porter tokenizers).


Got it, thanks for the explanation. Just to make sure that I 
understand you correctly, is the clause MATCH '*l0l* *h4x*' getting 
translated to MATCH 'l0l* h4x*'?


Yes, that's right.

Dan.

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


Re: [sqlite] Full text search: Regression on 3.8.7 (or using incorrectly?)

2014-10-09 Thread Dan Kennedy

On 10/09/2014 07:23 PM, Sohail Somani wrote:

On 2014-10-09, 7:32 AM, Dan Kennedy wrote:

Got it, thanks for the explanation. Just to make sure that I
understand you correctly, is the clause MATCH '*l0l* *h4x*' getting
translated to MATCH 'l0l* h4x*'?


Yes, that's right.

Dan.


In that case, shouldn't the test in the original post have returned 
the same results for both cases?


Fair point. Fixed here: http://www.sqlite.org/src/info/49dfee7cd1c9

Dan.






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


Re: [sqlite] Lets try this again. Sqlite Python libraries throwing exception on unique constraint.

2014-10-13 Thread Dan Kennedy

On 10/13/2014 11:44 PM, Jeffrey Parker wrote:

Hello,

I am working with sqlite3 in python 2.7.8 and I am running into a strange
error where I get the below exception when running an insert into statement
on an empty table. I know this is probably more to do with the python
libraries but I thought that I would ask the question here in case someone
has any recommendations.

sqlite3.IntegrityError: column VersionName is not unique

The insert still works and when I do the next insert into that table there
is no exception.


It sounds like you are attempting to insert a duplicate row.

You could temporarily add a SELECT statement before the INSERT to check 
if a record with the specified "versionName" is already present in the 
table.


Dan.





There are multiple insert statements and multiple tables. The insert
statements look like below.

params = versionName, versionNumber, updateFrom, metafileURL, patchURL, sha1
conn.execute("INSERT INTO UpdateFrom VALUES(?,?,?,?,?,?)", params)

and the table looks like below.

CREATE TABLE `UpdateFrom` (

`VersionName` TEXT NOT NULL UNIQUE,

`VersionNumber` INTEGER NOT NULL,

`UpdateFrom` INTEGER NOT NULL,

`MetafileURL` TEXT NOT NULL,

`PatchURL` TEXT NOT NULL,

`SHA1` TEXT NOT NULL

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


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


Re: [sqlite] Unable to prepare a statement

2014-10-15 Thread Dan Kennedy

On 10/15/2014 07:19 AM, Sam Carleton wrote:

When I use the SQLite Manager, I am able to run this query just fine:

 UPDATE EventNode
SET IsActive = 1
  WHERE EventNodeId IN (SELECT w.EventNodeId
  FROM EventNode as w, EventNode as m on
m.objectId = 'a09f0f8a-a37c-44c2-846f-16a59b1c34c1'
  WHERE w.lft BETWEEN m.lft AND m.rgt )

But when I try to prepare the same statement to be used with my C++ code:

 const char * updateString =
 "UPDATE EventNode "
"SET IsActive = @isActive "
  "WHERE EventNodeId IN (SELECT w.EventNodeId "
  "FROM EventNode AS w, EventNode AS m ON
m.objectId = @objectId "
 "WHERE w.lft BETWEEN m.lft AND m.rgt)";

I get an error where sqlite3_errmsg() returns: no such table: EventNode

Now the code that is opening the DB is in a base class which is used other
places to access the EventNode table, so I am a bit mystified as to what
exactly is going on.  Any thoughts?


Perhaps it's opening a different database file.

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


Re: [sqlite] INSERT OR REPLACE in a virtual table implementation....

2014-10-17 Thread Dan Kennedy

On 10/18/2014 01:07 AM, dave wrote:

I have a virtual table implementation, and I would like to use the INSERT OR
REPLACE syntax to simplify actions for the user.  In my xUpdate method, for
the case where insertion is occuring,

else if ( argc > 1 && SQLITE_NULL == sqlite3_value_type ( argv[0] ) ) {

I do check a uniqueness constraint, and return an error
SQLITE_CONSTRAINT_UNIQUE, which I presume is the right thing to do.
  
So, my question is, is this the right thing to do to get INSERT OR REPLACE

on a virtual table, or something else, or maybe that syntax is not supported
on virtual tables?


I don't think so. See the final paragraph here:

  http://sqlite.org/c3ref/c_vtab_constraint_support.html

Looks like you have to implement the "OR REPLACE" support in the xUpdate 
method.


Dan.



  
Thanks,
  
-dave

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


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


Re: [sqlite] Bug 993556 - SQLite crash in walIndexTryHdr due to Windows EXCEPTION_IN_PAGE_ERROR exception

2014-10-20 Thread Dan Kennedy

On 10/18/2014 05:45 AM, Deon Brewis wrote:

I'm trying to follow Richard's advise to work around this issue, which is:

"Is that database ever used by more than a single process.  (Use by multiple
threads using separate connections does not count - I mean really used by
multiple processes with their own address space.)  If not (and I think the
answer is "no") then FF could set "PRAGMA locking_mode=EXCLUSIVE"
immediately after opening the database and before doing anything else.  If
that is done, then SQLite will use heap memory for the WAL-index, instead of
mmapped shared memory, and this problem will never come up."


However, I'm unable to do so. I'm using multiple threads using separate
connections, like mentioned, but when I try to use PRAGMA
locking_mode=EXCLUSIVE, the next thread that tries to open a connection will
block indefinitely on the open.

So how can I go about using PRAGMA locking_mode=EXCLUSIVE while still using
multiple threads with connections?


I think the quoted paragraph above assumes that the application is using 
shared-cache mode, which is probably not what you want to do.


If you're on unix, using the VFS "unix-excl" causes a similar effect. 
Second section here:


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

Dan.









--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Bug-993556-SQLite-crash-in-walIndexTryHdr-due-to-Windows-EXCEPTION-IN-PAGE-ERROR-exception-tp78695.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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


Re: [sqlite] Installing 3.8.7 gives 3.8.6

2014-10-25 Thread Dan Kennedy

On 10/25/2014 09:36 PM, Arnaud Meuret wrote:

Hi everyone,

On a fresh CentOS 5, compiling SQLite using the [current tarball][1] 
for 3.8.7, the version being installed ends up in `/usr/local/lib` as 
**3.8.6**:


bash-3.2# make install
make[1]: Entering directory `/mongrel2/sqlite-autoconf-3080700'
 /bin/mkdir -p '/usr/local/lib'
 /bin/sh ./libtool   --mode=install /usr/bin/install -c 
libsqlite3.la '/usr/local/lib'
libtool: install: /usr/bin/install -c .libs/libsqlite3.so.0.8.6 
/usr/local/lib/libsqlite3.so.0.8.6
libtool: install: (cd /usr/local/lib && { ln -s -f 
libsqlite3.so.0.8.6 libsqlite3.so.0 || { rm -f libsqlite3.so.0 && ln 
-s libsqlite3.so.0.8.6 libsqlite3.so.0; }; })
libtool: install: (cd /usr/local/lib && { ln -s -f 
libsqlite3.so.0.8.6 libsqlite3.so || { rm -f libsqlite3.so && ln -s 
libsqlite3.so.0.8.6 libsqlite3.so; }; })
libtool: install: /usr/bin/install -c .libs/libsqlite3.lai 
/usr/local/lib/libsqlite3.la
libtool: install: /usr/bin/install -c .libs/libsqlite3.a 
/usr/local/lib/libsqlite3.a


 [1]: https://www.sqlite.org/2014/sqlite-autoconf-3080700.tar.gz


Is it normal ? I expected to find libsqlite3.so.0.8.7.


It's normal. I think that number is only supposed to change if the new 
release is not ABI compatible with the previous. It's a coincidence that 
"8.6" matches the tail of the previous release number. Earlier releases 
also used "libsqlite3.so.0.8.6".


Dan.


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


Re: [sqlite] [RFE bug] Improve error reporting for foreign keys

2014-10-27 Thread Dan Kennedy

On 10/27/2014 01:49 PM, Tristan Van Berkom wrote:

This is just a request-for-enhancement bug report, I've went to the
trouble or reproducing this problem in a simple test case and while
I probably wont be able to immediately benefit from an upstream fix
for this, I hope that this bug report will be perceived as helpful
and entered into your upstream bug tracking system (which seems to
not be public at this time).

This week I ran into a bug in my ~700 line complex schema which was
very hard to find.

After trying many things, including stepping through the
locateFKeyIndex() function issuing the not-so-informative
message "foreign key mismatch", I finally found that the
error was coming from another table with an incorrectly defined
foreign key.


Refering to function locateFKeyIndex() indicates you are looking at 
sources at least 22 months old. That particular error message has been 
improved since then. It's not quite as detailed as your proposal, but it 
is much better than it was.


Dan.

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


Re: [sqlite] Using sqlite3_backup* while the destination connection has "busy" read-only statements.

2014-11-13 Thread Dan Kennedy

On 11/13/2014 05:26 AM, Shaun Seckman (Firaxis) wrote:

Greetings all,
I'm running into a situation in where our application is crashing during a call 
to sqlite_backup_finish inside of btreeParseCellPtr because some of the 
structure is corrupted.

Both the source and destination database are using the same page sizes and I'm 
running everything from within a single-thread.

I'd like to get clarification on one possible issue in order to determine 
whether this is the culprit or at least rule it out.  This destination 
connection (which is an in-memory database) has several prepared statements, 
some of which are marked as busy (from sqlite3_stmt_busy) due to having step 
called but not reset.  All of these statements are read-only (simple select 
statements).

My question is, is it safe to have these busy statements on the destination 
connection while the backup happens (again, this is all single threaded so 
nothing happens DURING the backup process)?


It's not safe.

There are few reasons for this internally - the most obvious is that the 
backup operation might change the database schema.


The docs seem to omit this, but basically leaving the active statements 
around while the backup happens is the same as using the destination 
database while the backup is ongoing. See the "Concurrent Usage of 
Database Handles" section at the end of this page:


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



If not,  then should sqlite3_backup_init check for this (at least in debug) and 
return NULL if any statements are busy?


I think we likely should. In both debug and non-debug builds.

Thanks for reporting this.

Dan.




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


Re: [sqlite] sliteonline or sqlite in js

2014-11-14 Thread Dan Kennedy

On 11/14/2014 03:37 PM, Noel Frankinet wrote:

Impressive !


It is that. Perhaps not as functional as a native app (so far), but 
looking really good! And you can't beat the deployment.


Dan.





On 12 November 2014 12:08, Kirill  wrote:


Good day,

Full line manager to work with sqlite directly
from the browser on any platform:
http://sqliteonline.com/

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






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


Re: [sqlite] Null rowid when using CTEs

2014-11-14 Thread Dan Kennedy

On 11/14/2014 08:33 AM, RP McMurphy wrote:

When using CTEs the rowid is null. The following returns "--NULL--"

.null --NULL--
with c(x) as (select 1)
select rowid from c;


I guess that's expected. The same behaviour as for reading the rowid of 
a view or sub-select. It should probably be documented though.


Dan.





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


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


Re: [sqlite] Infinite loop in sqlite3VdbeSorterWrite when sorting big data

2014-11-25 Thread Dan Kennedy

On 11/25/2014 02:53 AM, Marcin Sobieszczanski wrote:

Hi

I work with sqlite files that have a few gigabytes of simple data.
Almost all of the data sits in one table that has 9 non-null integer
columns (including row_id, and one int64 column) plus 3 additional
string or int columns (additional columns are filled with nulls
mostly). The table has tens of millions of rows. (I need to
cross-correlate this data.)
Recently, I upgraded to 3.8.7.2 and I've notice that CREATE INDEX on a
table's 3 columns hangs in the new version of sqlite (last OK
versions: 3.8.4 and 3.8.6).

I located the code in sqlite3VdbeSorterWrite: there is only one loop there:
   while( nNew < nMin ) nNew = nNew*2;
In my case nNew == 0 (inspected with gdb; but without symbols). I
believe the multiplication in the line above overflown:
   int nNew = pSorter->nMemory * 2;

Last strace lines show series of re-allocations:
mremap(0x7f8241fff000, 33558528, 67112960, MREMAP_MAYMOVE) = 0x7f81b7fff000
...
mremap(0x7f819fffd000, 268439552, 536875008, MREMAP_MAYMOVE) = 0x7f817fffc000
mremap(0x7f817fffc000, 536875008, 1073745920, MREMAP_MAYMOVE) = 0x7f813fffb000

The next mremap would have tried to allocate 2147487744 bytes
(2147487744=2^31+4Ki, extra 4Ki from glibc), I guess, but int is not
capable to hold more than 2Gi.

Is this a known problem?


It is now.

Do you have a large cache-size configured?

Thanks,
Dan.


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


Re: [sqlite] Infinite loop in sqlite3VdbeSorterWrite when sorting big data

2014-11-25 Thread Dan Kennedy

On 11/26/2014 12:41 AM, Marcin Sobieszczanski wrote:

Do you have a large cache-size configured?

Yes:

PRAGMA cache_size = 10
PRAGMA page_size = 16384


Thanks for reporting this. I think it should be fixed here:

  http://www.sqlite.org/src/info/623827192532f08b

Dan.


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


Re: [sqlite] Corrupting pointers to the lookaside smallacator

2014-11-25 Thread Dan Kennedy

On 11/26/2014 06:47 AM, Ward Willats wrote:

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

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

#define SQLITE_DEBUG 1
#define SQLITE_MEMDEBUG 1

WAL mode.

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

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


Maybe try with both of those and SQLITE_OMIT_LOOKASIDE as well.

When SQLITE_DEBUG and MEMDEBUG are defined, fenceposts are used to 
detect buffer overruns for all regular malloc/free allocations. If 
lookaside is omitted these fencepost checks will be done for small 
allocations as well, which may reveal the source of the memory corruption.


An assert() will fail if any of the fencepost checks indicate a buffer 
overwrite.


Dan.







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

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

Thanks, as always,

-- Ward

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


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


Re: [sqlite] Corrupting pointers to the lookaside smallacator

2014-11-27 Thread Dan Kennedy

On 11/27/2014 03:20 PM, Paul wrote:

Here is how it looks with debug symbols are on:

#0  0x28c4113e in memcpy () from /lib/libc.so.7
#1  0x08854c20 in sqlite3StrAccumAppend (p=0xfffe8548, z=0x2c3fffda "vtb_enyqkyxs 
USING vtable_module_343", N=41) at sqlite3.c:21563
#2  0x087edf30 in sqlite3VXPrintf (pAccum=0xfffe8548, bFlags=1, fmt=0x892e543 "T", 
ap=0xfffe8610 "") at sqlite3.c:21439
#3  0x088077d5 in sqlite3VMPrintf (db=0x2c006788, zFormat=0x892e52d "CREATE VIRTUAL TABLE 
%T", ap=0xfffe860c "xx") at sqlite3.c:21638
#4  0x087f815e in sqlite3MPrintf (db=0x2c006788, zFormat=0x892e52d "CREATE VIRTUAL 
TABLE %T") at sqlite3.c:21654
#5  0x088759af in sqlite3VtabFinishParse (pParse=0x2c007688, pEnd=0x0) at 
sqlite3.c:112383
#6  0x0885bb21 in yy_reduce (yypParser=0x2c1d1408, yyruleno=309) at 
sqlite3.c:123403
#7  0x08856180 in sqlite3Parser (yyp=0x2c1d1408, yymajor=1, yyminor=..., 
pParse=0x2c007688) at sqlite3.c:123629
#8  0x087fc289 in sqlite3RunParser (pParse=0x2c007688, zSql=0x2c3fffc0 "CREATE 
VIRTUAL TABLE temp.vtb_enyqkyxs USING vtable_module_343", pzErrMsg=0xfffe8bc4) at 
sqlite3.c:124466
#9  0x088bbc82 in sqlite3Prepare (db=0x2c006788, zSql=0x2c3fffc0 "CREATE VIRTUAL 
TABLE temp.vtb_enyqkyxs USING vtable_module_343", nBytes=-1, saveSqlFlag=1, 
pReprepare=0x0, ppStmt=0xfffe8d0c, pzTail=0xfffe8d10) at sqlite3.c:103750
#10 0x087fb0ce in sqlite3LockAndPrepare (db=0x2c006788, zSql=0x2c3fffc0 "CREATE 
VIRTUAL TABLE temp.vtb_enyqkyxs USING vtable_module_343", nBytes=-1, saveSqlFlag=1, 
pOld=0x0, ppStmt=0xfffe8d0c, pzTail=0xfffe8d10) at sqlite3.c:103842
#11 0x087fa504 in sqlite3_prepare_v2 (db=0x2c006788, zSql=0x2c3fffc0 "CREATE VIRTUAL 
TABLE temp.vtb_enyqkyxs USING vtable_module_343", nBytes=-1, ppStmt=0xfffe8d0c, 
pzTail=0xfffe8d10) at sqlite3.c:103918
#12 0x087fa01d in sqlite3_exec (db=0x2c006788, zSql=0x2c3fffc0 "CREATE VIRTUAL TABLE 
temp.vtb_enyqkyxs USING vtable_module_343", xCallback=0x0, pArg=0x0, pzErrMsg=0x0) 
at sqlite3.c:99345
#13 0x086588e7 in sqlite::StorageBase::exec_query (this=0x2c3ff640, query=0x2c3fffc0 
"CREATE VIRTUAL TABLE temp.vtb_enyqkyxs USING vtable_module_343", quiet=false) 
at SqliteStorageBase.cpp:286
   


Interesting part is in frame #5

#5  0x088759af in sqlite3VtabFinishParse (pParse=0x2c007688, pEnd=0x0) at 
sqlite3.c:112383
112383  zStmt = sqlite3MPrintf(db, "CREATE VIRTUAL TABLE %T", 
>sNameToken);
(gdb) p pParse->sNameToken
$12 = {
   z = 0x2c3fffda "vtb_enyqkyxs USING vtable_module_343",
   n = 41
}
(gdb) p  pParse->sNameToken.z + 40
$13 = 0x2c42 

As you can see, token size is invalid: 41. This causes memcpy() down at #0 to 
access invalid, unmapped address.
Hopefully it is only read overflow so the only consequence is just an 
occasional Segmentation fault when allocated
piece of string is at the specific place: near the end of the page in front of 
unmapped page.

Should I fill a bug report?


It's certainly very suspicious. Which SQLite version are you using?

Dan.







Is there a way to work this around?

Like append many spaces a the end of query?
Or maybe the problem is in absence of ';' at the end of query?
Meantime I'll try both of these cases.


  

We observe very similar problem.

#1 0x087ec9f7 in sqlite3VXPrintf ()
#2 0x087f816d in sqlite3MPrintf ()
#3 0x088781e5 in sqlite3VtabFinishParse ()
#4 0x0885190f in yy_reduce ()
#5 0x0884d4d8 in sqlite3Parser ()
#6 0x087fc0ce in sqlite3RunParser ()
#7 0x088aa396 in sqlite3Prepare ()
#8 0x087fae18 in sqlite3LockAndPrepare ()
#9 0x087f9a88 in sqlite3_exec ()
#10 0x086588a7 in sqlite::StorageBase::exec_query (this=0x2c2a47c0, query=0x2c7fffc0 
"CREATE VIRTUAL TABLE temp.vtb_wayxzmbo USING vtable_module_344", quiet=false) 
at SqliteStorageBase.cpp:286

It always crashes when "CREATE VIRTUAL TABLE ..." is being executed, always 
with the same backtrace.
I spent many days reviewing and testing my code to eliminate possible cause but 
so far I see nothing wrong with it.
Probability of this crash is so very low so that problem can be reproduced only 
on hi loaded production servers.
(Where such virtual tables are created and dropped millions of times during a 
day)

I am going to compile sqlite without optimizations and with debug symbols and 
wait for a crash
to try and track the root of the problem from within sqlite.

Though I doubt very much this is sqlite problem at all and not an incorrect 
vtable implementation on my side.


SQLite version 3.8.6 2014-08-15 11:46:33


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


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


Re: [sqlite] Read overflow in CREATE VIRTUAL TABLE query (formerly Corrupting pointers to the lookaside smallacator)

2014-11-27 Thread Dan Kennedy

On 11/27/2014 05:56 PM, Paul wrote:

Currently we use various versions of SQLite:

SQLite version 3.8.0.1 2013-08-29 17:35:01
SQLite version 3.8.2 2013-12-06 14:53:30
SQLite version 3.8.6 2014-08-15 11:46:33
SQLite version 3.8.7 2014-10-17 11:24:17


All of them are affected so I never considered it to be an sqlite bug.
But analyzing core file it seems like very much an sqlite bug :/

Tell me if you need more info on this.



Thanks for tracking this down.Should be fixed here:

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

As far as I can see this is "just" a buffer overread - there is no 
chance of an overwrite or database corruption. Easiest workaround is to 
append "()" to your CREATE VIRTUAL TABLE statement. i.e.


  CREATE VIRTUAL TABLE temp.vtb_enyqkyxs USING vtable_module_343()

Dan.






Thanks.


On 11/27/2014 03:20 PM, Paul wrote:

Here is how it looks with debug symbols are on:

#0 0x28c4113e in memcpy () from /lib/libc.so.7
#1 0x08854c20 in sqlite3StrAccumAppend (p=0xfffe8548, z=0x2c3fffda "vtb_enyqkyxs 
USING vtable_module_343", N=41) at sqlite3.c:21563
#2 0x087edf30 in sqlite3VXPrintf (pAccum=0xfffe8548, bFlags=1, fmt=0x892e543 "T", 
ap=0xfffe8610 "") at sqlite3.c:21439
#3 0x088077d5 in sqlite3VMPrintf (db=0x2c006788, zFormat=0x892e52d "CREATE VIRTUAL TABLE 
%T", ap=0xfffe860c "xx") at sqlite3.c:21638
#4 0x087f815e in sqlite3MPrintf (db=0x2c006788, zFormat=0x892e52d "CREATE VIRTUAL 
TABLE %T") at sqlite3.c:21654
#5 0x088759af in sqlite3VtabFinishParse (pParse=0x2c007688, pEnd=0x0) at 
sqlite3.c:112383
#6 0x0885bb21 in yy_reduce (yypParser=0x2c1d1408, yyruleno=309) at 
sqlite3.c:123403
#7 0x08856180 in sqlite3Parser (yyp=0x2c1d1408, yymajor=1, yyminor=..., 
pParse=0x2c007688) at sqlite3.c:123629
#8 0x087fc289 in sqlite3RunParser (pParse=0x2c007688, zSql=0x2c3fffc0 "CREATE 
VIRTUAL TABLE temp.vtb_enyqkyxs USING vtable_module_343", pzErrMsg=0xfffe8bc4) at 
sqlite3.c:124466
#9 0x088bbc82 in sqlite3Prepare (db=0x2c006788, zSql=0x2c3fffc0 "CREATE VIRTUAL 
TABLE temp.vtb_enyqkyxs USING vtable_module_343", nBytes=-1, saveSqlFlag=1, 
pReprepare=0x0, ppStmt=0xfffe8d0c, pzTail=0xfffe8d10) at sqlite3.c:103750
#10 0x087fb0ce in sqlite3LockAndPrepare (db=0x2c006788, zSql=0x2c3fffc0 "CREATE 
VIRTUAL TABLE temp.vtb_enyqkyxs USING vtable_module_343", nBytes=-1, saveSqlFlag=1, 
pOld=0x0, ppStmt=0xfffe8d0c, pzTail=0xfffe8d10) at sqlite3.c:103842
#11 0x087fa504 in sqlite3_prepare_v2 (db=0x2c006788, zSql=0x2c3fffc0 "CREATE VIRTUAL 
TABLE temp.vtb_enyqkyxs USING vtable_module_343", nBytes=-1, ppStmt=0xfffe8d0c, 
pzTail=0xfffe8d10) at sqlite3.c:103918
#12 0x087fa01d in sqlite3_exec (db=0x2c006788, zSql=0x2c3fffc0 "CREATE VIRTUAL TABLE 
temp.vtb_enyqkyxs USING vtable_module_343", xCallback=0x0, pArg=0x0, pzErrMsg=0x0) 
at sqlite3.c:99345
#13 0x086588e7 in sqlite::StorageBase::exec_query (this=0x2c3ff640, query=0x2c3fffc0 
"CREATE VIRTUAL TABLE temp.vtb_enyqkyxs USING vtable_module_343", quiet=false) 
at SqliteStorageBase.cpp:286


Interesting part is in frame #5

#5 0x088759af in sqlite3VtabFinishParse (pParse=0x2c007688, pEnd=0x0) at 
sqlite3.c:112383
112383 zStmt = sqlite3MPrintf(db, "CREATE VIRTUAL TABLE %T", 
>sNameToken);
(gdb) p pParse->sNameToken
$12 = {
z = 0x2c3fffda "vtb_enyqkyxs USING vtable_module_343",
n = 41
}
(gdb) p pParse->sNameToken.z + 40
$13 = 0x2c42 

As you can see, token size is invalid: 41. This causes memcpy() down at #0 to 
access invalid, unmapped address.
Hopefully it is only read overflow so the only consequence is just an 
occasional Segmentation fault when allocated
piece of string is at the specific place: near the end of the page in front of 
unmapped page.

Should I fill a bug report?

It's certainly very suspicious. Which SQLite version are you using?

Dan.






Is there a way to work this around?

Like append many spaces a the end of query?
Or maybe the problem is in absence of ';' at the end of query?
Meantime I'll try both of these cases.




We observe very similar problem.

#1 0x087ec9f7 in sqlite3VXPrintf ()
#2 0x087f816d in sqlite3MPrintf ()
#3 0x088781e5 in sqlite3VtabFinishParse ()
#4 0x0885190f in yy_reduce ()
#5 0x0884d4d8 in sqlite3Parser ()
#6 0x087fc0ce in sqlite3RunParser ()
#7 0x088aa396 in sqlite3Prepare ()
#8 0x087fae18 in sqlite3LockAndPrepare ()
#9 0x087f9a88 in sqlite3_exec ()
#10 0x086588a7 in sqlite::StorageBase::exec_query (this=0x2c2a47c0, query=0x2c7fffc0 
"CREATE VIRTUAL TABLE temp.vtb_wayxzmbo USING vtable_module_344", quiet=false) 
at SqliteStorageBase.cpp:286

It always crashes when "CREATE VIRTUAL TABLE ..." is being executed, always 
with the same backtrace.
I spent many days reviewing and testing my code to eliminate possible cause but 
so far I see nothing wrong with it.
Probability of this crash is so very low so that problem can be reproduced only 
on hi loaded production servers.
(Where such virtual tables are created and dropped millions of times during a 
day)

I am going to compile sqlite without optimizations and with debug 

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

2014-12-08 Thread Dan Kennedy

On 12/08/2014 09:55 PM, Nico Williams wrote:

Ideally there would be something like DEFERRED foreign key checking
for uniqueness constraints...


You could hack SQLite to do enforce unique constraints the same way as 
FKs. When adding an entry to a UNIQUE index b-tree, you check for a 
duplicate. If one exists, increment a counter. Do the opposite when 
removing entries - decrement the counter if there are two or more 
duplicates of the entry you are removing. If your counter is greater 
than zero at commit time, a UNIQUE constraint has failed.


I suspect there would be a non-trivial increase in the CPU use of UPDATE 
statements though.







   You can get something like that by
using non-unique indexes (but there would also go your primary keys)
and then check that there are no duplicates before you COMMIT.  (Doing
this reliably would require something like transaction triggers, which
IIRC exists in a "sessions" branch.)

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


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


Re: [sqlite] Online/Hot backup of WAL journalling mode database

2014-12-09 Thread Dan Kennedy

On 12/10/2014 05:06 AM, Simon Slavin wrote:

On 9 Dec 2014, at 8:57pm, Nick  wrote:


Environment is Linux with multiple (c. 4-6) processes accessing a single sqlite database 
named "test.db".

Backup:
- New process started using cronjob to initiate application checkpoint until 
completion.
- rsync diff the file "test.db" to another drive/location (specifically ignoring the 
"-shm" and "-wal" file).
- exit process

Restore:
- rsync the file "test.db" from another drive/location.

Will not be trustworthy if the database is being written to during the rsync 
operations.




Strictly speaking the database file may not be well-formed even if there 
is no ongoing checkpoint. If:


  a) process A opens a read transaction,
  b) process B opens and commits a write transaction to the database,
  c) process C checkpoints the db,

then the db file considered without the *-wal file may be corrupt. The 
problem comes about because process C can only checkpoint frames up 
until the start of B's transaction. And there is an optimization that 
will prevent it from copying any earlier frames for which there exists a 
frame in B's transaction that corresponds to the same database page. So 
it effectively copis only a subset of the modifications made by earlier 
transactions into the db file - not necessarily creating a valid db file.


Dan.









A) Ensure all processes besides the backup process have the database closed 
while it is being copied. Establish some kind of semaphore so they can tell 
when it's safe to open the database again.

B) Use the SQLite Backup API which was invented to do what you want.





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


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


Re: [sqlite] Online/Hot backup of WAL journalling mode database

2014-12-11 Thread Dan Kennedy

On 12/11/2014 05:49 AM, Nick wrote:

On 10 Dec 2014, at 07:35, Dan Kennedy wrote:


Strictly speaking the database file may not be well-formed even if there is no 
ongoing checkpoint. If:

  a) process A opens a read transaction,
  b) process B opens and commits a write transaction to the database,
  c) process C checkpoints the db,

then the db file considered without the *-wal file may be corrupt. The problem 
comes about because process C can only checkpoint frames up until the start of 
B's transaction. And there is an optimization that will prevent it from copying 
any earlier frames for which there exists a frame in B's transaction that 
corresponds to the same database page. So it effectively copis only a subset of 
the modifications made by earlier transactions into the db file - not 
necessarily creating a valid db file.

Can this corruption be detected by running PRAGMA quick_check / 
integrity_check? Having the occasional backup db corrupted would be tolerable.


In many cases, but not generally. There would exist cases where a part 
of a committed transaction was lost, or the values in unindexed columns 
where replaced, that sort of thing.








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


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


Re: [sqlite] Online/Hot backup of WAL journalling mode database

2014-12-12 Thread Dan Kennedy

On 12/12/2014 03:31 AM, Nick wrote:

On 11 Dec 2014, at 10:08, Dan Kennedy wrote:


On 12/11/2014 05:49 AM, Nick wrote:

On 10 Dec 2014, at 07:35, Dan Kennedy wrote:


Strictly speaking the database file may not be well-formed even if there is no 
ongoing checkpoint. If:

  a) process A opens a read transaction,
  b) process B opens and commits a write transaction to the database,
  c) process C checkpoints the db,

then the db file considered without the *-wal file may be corrupt. The problem 
comes about because process C can only checkpoint frames up until the start of 
B's transaction. And there is an optimization that will prevent it from copying 
any earlier frames for which there exists a frame in B's transaction that 
corresponds to the same database page. So it effectively copis only a subset of 
the modifications made by earlier transactions into the db file - not 
necessarily creating a valid db file.

Can this corruption be detected by running PRAGMA quick_check / 
integrity_check? Having the occasional backup db corrupted would be tolerable.

In many cases, but not generally. There would exist cases where a part of a 
committed transaction was lost, or the values in unindexed columns where 
replaced, that sort of thing.

Ok. Presumably a SQLITE_CHECKPOINT_FULL or SQLITE_CHECKPOINT_RESTART 
checkpoint mode would ensure the db file is valid?


That sounds right. A successful FULL or RESTART checkpoint will always 
copy entire transactions into the db.



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


Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

2014-12-15 Thread Dan Kennedy

On 12/12/2014 09:22 PM, Josef Kučera wrote:

Hello,
I am trying to use SQLite's marvellous Virtual Table mechanism as a SQL
layer for querying an in memory storage. This works good, but I have a
problem with more complex queries. When querying a real SQLite database it
correctly moves the constant conditions across joined tables to optimize
the execution plan (I think this was implemented in the 3.7.17 release).
Unfortunately for virtual tables this does not seem to be supported. I can
overcome this limitation by manually tuning the SQL, but it will help if
the query planner can do this automatically.

The major problem I have is with link table evaluation. Imagine a SQL like
"select * from A join B on A.ID=B.ID join C on C.ID=B.LINKID". The current
implementation evaluates cost of B only as B (ID, LINKID) causing the
execution to perform a full scan on either A or C. This seems to be caused
by the implementation of whereLoopAddVirtual() function. I think it should
evaluate cost for terms separated by tables in the right term as well, e.g.
for the mentioned SQL, table B, it should try B(), B(ID), B(LINKID), B(ID,
LINKID) instead of only B() and B(ID, LINKID).

What should I do?


You want this (or the same thing with the roles of "A" and "C" reversed):

  * a full-scan on A,
  * a lookup on B by (b.id=?)
  * a lookup on C by (c.id=?)

correct?

It's tricky. As you say, xBestIndex() will currently be invoked twice - 
once with no constraints usable and once with both "b.id=?" and 
"b.linkid=?" usable. I guess the reason it is not invoked in the other 
ways you suggest is that that strategy might conceivably require a huge 
number of xBestIndex() calls if there were more than a few other tables 
in the join.


You could change the query so that only one of the constraints is 
visible to the virtual table implementation. Say:


  select * from A join B on A.ID=B.ID join C on C.ID=+B.LINKID

Or rework the virtual table code so that it knows only to use one of 
"b.id=?" or "b.linkid=?" at a time. If the xBestIndex only uses one of 
the constraints, the planner should do the right thing.


Dan.


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


Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

2014-12-15 Thread Dan Kennedy

On 12/15/2014 11:11 PM, Paul wrote:

Hello, dear developers

Recently I've stumbled upon a very rare and strange bug.
The result of this is abnormal memory usage, that does not allow us to remove
fair number of rows from a table due to the limit of memory, available for 32bit
process. This is strange, because database size is somewhat small: 79M.
Digging around I finally managed to pinpoint when exactly does this issue occur.
Another stange thing, though is that memory is successfully deallocated,
bacause no matter what, valgrind does not report definitely lost memory.

I want to present you my test case. You have two options to manipulate it.
Two defines:
  - NO_NESTED_TRANSACTION desables nested transaction.
  - NO_CHILD_RECORDS disables population of 'child' table with data.

To compile:
# clang -o test -L/usr/local/lib -lsqlite3 test.c

My results

Without defines:
# clang -o test -L/usr/local/lib -lsqlite3 test.c
# ./test
   Current mem: 0
   Hi mem: 1294136920

Not okay, 1.2GiB peak memory usage.


With NO_CHILD_RECORDS
# clang -o test -DNO_CHILD_RECORDS -L/usr/local/lib -lsqlite3 test.c
# ./test
   Current mem: 0
   Hi mem: 421141176

A bit better, but still not ok.


With NO_NESTED_TRANSACTION:
# clang -o test -DNO_NESTED_TRANSACTION -L/usr/local/lib -lsqlite3 test.c
# ./test
   Current mem: 0
   Hi mem: 15100760

Seems ok.


With NO_NESTED_TRANSACTION and NO_CHILD_RECORDS:
# clang -o test -DNO_NESTED_TRANSACTION -DNO_CHILD_RECORDS -L/usr/local/lib 
-lsqlite3 test.c
# ./test
   Current mem: 0
   Hi mem: 2554168

No doubt it's even better.


The memory is being used by the statement journal, which you have in 
memory. If the app did not set "journal_mode=memory" and 
"temp_store=memory", SQLite would create a really large temp file 
instead of using memory. Which would still be sub-optimal, but might not 
run into the 32-bit limit.


The reason the statement journal is growing so large is that SQLite only 
truncates the statement journal when the outermost sub-transaction is 
closed. Otherwise it just keeps on appending. i.e.


  BEGIN;
SAVEPOINT xxx;
  ...
  SAVEPOINT yyy;
  ...
  RELEASE yyy;   -- does not truncate statement journal
COMMIT xxx;  -- truncates statement journal
  COMMIT;

Your example has one explicit sub-transaction (equivalent to xxx) and 
each DELETE statement is opening a second, implicit, sub-transaction 
(equivalent to yyy).


With the child records included, each DELETE statement is modifying 3 db 
pages - one from table "bar", one from table "foo" and one from the 
PRIMARY KEY index on "foo". 100,000 deletes, 4Kib page size, 3 pages per 
delete == 1.2GiB. Or without the child records, just 1 page modified per 
delete, so closer to 400MiB of memory. Without the sub-transaction, the 
implicit sub-transaction created by each DELETE becomes the outermost 
and so the statement journal doesn't grow much at all. So not much 
memory used in that case.


Dan.














test.c

#include 
#include 
#include 
#include 
#include 

int main(int argc, char ** argv)
{
 const char * database_file = "/tmp/memusage_test_db";

 // Clear old database file is there is one.
 unlink(database_file);

 sqlite3 * db = NULL;
 if (sqlite3_open(database_file, ) != SQLITE_OK)
 return 1;

 // Set busy timeout just in case...
 if (sqlite3_busy_timeout(db, 1) != SQLITE_OK) {
 sqlite3_close(db);
 return 1;
 }

 // Set pragmas.
 if (sqlite3_exec(db,
  " PRAGMA page_size = 4096;"
  " PRAGMA temp_store = MEMORY;"
  " PRAGMA journal_mode = MEMORY;"
  " PRAGMA cache_size = 1;"
  " PRAGMA foreign_keys = ON;"
  " PRAGMA synchronous = OFF;",
  NULL, NULL, NULL) != SQLITE_OK) {
 sqlite3_close(db);
 return 1;
 }

 // Create database structure.
 if (sqlite3_exec(db,
  "CREATE TABLE foo ("
  "  id  INTEGER,"
  "  x   INTEGER,"
  "  PRIMARY KEY(id)"
  ");"
  ""
  "CREATE TABLE bar ("
  "  id  INTEGER,"
  "  y   INTEGER,"
  "  PRIMARY KEY(id, y),"
  "  FOREIGN KEY(id) REFERENCES foo(id) ON DELETE CASCADE"
  ");",
  NULL, NULL, NULL) != SQLITE_OK) {
 sqlite3_close(db);
 return 1;
 }

 // Populate database with data.
 {
 // Open transaction.
 if (sqlite3_exec(db, "BEGIN IMMEDIATE;", NULL, NULL, NULL) != 
SQLITE_OK) {
 sqlite3_close(db);
 return 1;
 }

 char buffer[256];
 for (int i = 0; i < 10; ++i) {

 snprintf(buffer, sizeof(buffer), "INSERT INTO foo(id, x) VALUES(%u, 
%u)", i + 1, 2 * i + 1);

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

2014-12-15 Thread Dan Kennedy

On 12/15/2014 11:59 PM, Dan Kennedy wrote:

On 12/15/2014 11:11 PM, Paul wrote:

Hello, dear developers

Recently I've stumbled upon a very rare and strange bug.
The result of this is abnormal memory usage, that does not allow us 
to remove
fair number of rows from a table due to the limit of memory, 
available for 32bit

process. This is strange, because database size is somewhat small: 79M.
Digging around I finally managed to pinpoint when exactly does this 
issue occur.

Another stange thing, though is that memory is successfully deallocated,
bacause no matter what, valgrind does not report definitely lost memory.

I want to present you my test case. You have two options to 
manipulate it.

Two defines:
  - NO_NESTED_TRANSACTION desables nested transaction.
  - NO_CHILD_RECORDS disables population of 'child' table with data.

To compile:
# clang -o test -L/usr/local/lib -lsqlite3 test.c

My results

Without defines:
# clang -o test -L/usr/local/lib -lsqlite3 test.c
# ./test
   Current mem: 0
   Hi mem: 1294136920

Not okay, 1.2GiB peak memory usage.


With NO_CHILD_RECORDS
# clang -o test -DNO_CHILD_RECORDS -L/usr/local/lib -lsqlite3 test.c
# ./test
   Current mem: 0
   Hi mem: 421141176

A bit better, but still not ok.


With NO_NESTED_TRANSACTION:
# clang -o test -DNO_NESTED_TRANSACTION -L/usr/local/lib -lsqlite3 
test.c

# ./test
   Current mem: 0
   Hi mem: 15100760

Seems ok.


With NO_NESTED_TRANSACTION and NO_CHILD_RECORDS:
# clang -o test -DNO_NESTED_TRANSACTION -DNO_CHILD_RECORDS 
-L/usr/local/lib -lsqlite3 test.c

# ./test
   Current mem: 0
   Hi mem: 2554168

No doubt it's even better.


The memory is being used by the statement journal, which you have in 
memory. If the app did not set "journal_mode=memory" and 
"temp_store=memory", SQLite would create a really large temp file 
instead of using memory. Which would still be sub-optimal, but might 
not run into the 32-bit limit.


The reason the statement journal is growing so large is that SQLite 
only truncates the statement journal when the outermost 
sub-transaction is closed. Otherwise it just keeps on appending. i.e.


  BEGIN;
SAVEPOINT xxx;
  ...
  SAVEPOINT yyy;
  ...
  RELEASE yyy;   -- does not truncate statement journal
COMMIT xxx;  -- truncates statement journal
  COMMIT;

Your example has one explicit sub-transaction (equivalent to xxx) and 
each DELETE statement is opening a second, implicit, sub-transaction 
(equivalent to yyy).


With the child records included, each DELETE statement is modifying 3 
db pages - one from table "bar", one from table "foo" and one from the 
PRIMARY KEY index on "foo". 100,000 deletes, 4Kib page size, 3 pages 
per delete == 1.2GiB. Or without the child records, just 1 page 
modified per delete, so closer to 400MiB of memory. Without the 
sub-transaction, the implicit sub-transaction created by each DELETE 
becomes the outermost and so the statement journal doesn't grow much 
at all. So not much memory used in that case.


Another idea would be to use a deferred foreign key constraint. That way 
the DELETE operations will not need the statement journal at all.




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


Re: [sqlite] SQLITE_FTS3_MAX_EXPR_DEPTH and upgrading sqlite

2014-12-17 Thread Dan Kennedy

On 12/16/2014 10:57 PM, Ed Willis wrote:

Hello all,

Apologies in advance if this question has been asked and answered elsewhere – a 
(brief, admittedly) search did not turn up anything and so I’m posting this.

We’re in the process of upgrading sqlite in our service.  We were on a version 
which did not have the compile option SQLITE_FTS3_MAX_EXPR_DEPTH and are moving 
up to one that does.  As it turns out we have run into a problem with one of 
our clients where they hit this limit now where previously the query just 
worked.  My question is whether or not there’s any guidance on how to set this 
limit at compile time?  Part of my confusion is that I’m not sure what would 
have happened previously with no limit enforced (as was the case on our older 
version of sqlite) - was the risk stack exhaustion and a resulting crash or was 
it something else entirely?

Basically what I’m worried about is that we'll raise the limit to allow this 
one client to do their queries as they used to do, but will not know that we’ve 
raised it enough to allow all our clients to get the same behavior they were 
accustomed to?  How do people choose this limit?


The point of the setting is to prevent stack overflow. I guess to get 
the old behavior, set it to a very large value.


Are you able to post the FTS query that caused a problem with the 
default settings? Just the MATCH expression will be enough, we don't 
need the table schema or contents.


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


Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

2014-12-17 Thread Dan Kennedy

On 12/16/2014 03:08 PM, Paul wrote:

The memory is being used by the statement journal, which you have in
memory. If the app did not set "journal_mode=memory" and
"temp_store=memory", SQLite would create a really large temp file
instead of using memory. Which would still be sub-optimal, but might
not run into the 32-bit limit.

The reason the statement journal is growing so large is that SQLite
only truncates the statement journal when the outermost
sub-transaction is closed. Otherwise it just keeps on appending. i.e.

BEGIN;
SAVEPOINT xxx;
...
SAVEPOINT yyy;
...
RELEASE yyy; -- does not truncate statement journal
COMMIT xxx; -- truncates statement journal
COMMIT;

Your example has one explicit sub-transaction (equivalent to xxx) and
each DELETE statement is opening a second, implicit, sub-transaction
(equivalent to yyy).

With the child records included, each DELETE statement is modifying 3
db pages - one from table "bar", one from table "foo" and one from the
PRIMARY KEY index on "foo". 100,000 deletes, 4Kib page size, 3 pages
per delete == 1.2GiB. Or without the child records, just 1 page
modified per delete, so closer to 400MiB of memory. Without the
sub-transaction, the implicit sub-transaction created by each DELETE
becomes the outermost and so the statement journal doesn't grow much
at all. So not much memory used in that case.

Another idea would be to use a deferred foreign key constraint. That way
the DELETE operations will not need the statement journal at all.


How can I get around implicit savepoint creation?
Why doesn't savepoint commit truncate a journal?
Why does journal grow even when there is nothing to delete in bar?

Currently this limitation renders use of sqlite impossible, unless using ugly 
hacks.
 From the user's perspective, this overhead is unimaginable. This is completely
normal use of SQL yet overhead is above the wildest imagination :(

Also, I don not understand, how does it become outermost? Journal vener grows
if there is only single transaction (or savepoint) aroun 'delete loop'.
Why in case of just single transaction around deletes this does not happen?
Are you saying there is no YYY savepoint? Or the journal can be truncated
when omiting BEGIN oe XXX but not when they are both present?

Please don't mind my last message.

I understand now, what is going on. Yet this limitation is pretty depressing.
Is there no way in the future for things to change?
Can't journal be truncated, or modified pages be merged, after each 
consequential
implicit sub-transaction (YYY) release, while they are still in the cache?

Is there any hope for me, except deferred FKs or DELETE FROM foo WHERE id IN 
(...)?


There is now an update on the fossil trunk that should fix the problem 
with ON DELETE CASCADE:


http://www.sqlite.org/src/info/8c5dd6cc259e0cdaaddaa52ccfa96fee6b166906

Dan.






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


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


Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

2014-12-18 Thread Dan Kennedy

On 12/18/2014 02:41 PM, Paul wrote:

I want to confirm that issue is fixed for me.
Thanks again, Dan!


Please ignore this update, patch fixes this problem as well.


I want to add even more input for this issue.
I understand why there is implicit savepoint, when I remove row from 'parent' 
table.
But why is this also true for a 'child' table when I perform 'INSERT OR 
REPLACE'?
Removing FK reference disables journal growth. I don't understand...




At the end of the day my head was so big that I, having some ten different test 
cases,
have errourneously confirmed that 'INSERT OR REPLACE' is fixed also, by running 
wrong test.
But sadly it isn't. Here, I'll drop my test program again, for clarity.


I don't think there is an easy fix for this one. The statement journal 
is required, as SQLite may need to reinstate rows deleted by the REPLACE 
processing if the FK constraint fails.


To fix this properly, it probably needs to use a more sophisticated data 
structure than the statement journal. Which would complicate things 
some. But at the moment it seems like SAVEPOINT and very large 
transactions don't work well together.


Dan.


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


Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

2014-12-18 Thread Dan Kennedy

On 12/18/2014 04:16 PM, Paul wrote:

Hi, Dan.


On 12/18/2014 02:41 PM, Paul wrote:

I want to confirm that issue is fixed for me.
Thanks again, Dan!


Please ignore this update, patch fixes this problem as well.


I want to add even more input for this issue.
I understand why there is implicit savepoint, when I remove row from 'parent' 
table.
But why is this also true for a 'child' table when I perform 'INSERT OR 
REPLACE'?
Removing FK reference disables journal growth. I don't understand...



At the end of the day my head was so big that I, having some ten different test 
cases,
have errourneously confirmed that 'INSERT OR REPLACE' is fixed also, by running 
wrong test.
But sadly it isn't. Here, I'll drop my test program again, for clarity.

I don't think there is an easy fix for this one. The statement journal
is required, as SQLite may need to reinstate rows deleted by the REPLACE
processing if the FK constraint fails.

To fix this properly, it probably needs to use a more sophisticated data
structure than the statement journal. Which would complicate things
some. But at the moment it seems like SAVEPOINT and very large
transactions don't work well together.


I understand. I guess, I'll have to stick to UPDATE <-> INSERT.
Thank you for taking your time.

Just out of curiosity, I want to ask one more question.
How can FK constraint fail if I am removing (replacing) row from the 'child' 
table?


The FK constraint can fail because a new row is being inserted into the 
child table. The reason statement rollback may be required is because 
any replaced rows will be removed before SQLite has a chance to figure 
out if the INSERT actually does violate the PK constraint.


Dan.








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


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


Re: [sqlite] SQLITE wal file size keeps growing

2014-12-18 Thread Dan Kennedy

On 12/19/2014 11:22 AM, Kushagradhi Bhowmik wrote:

I am writing continuously into a db file which has PRAGMA journal_mode=WAL,
PRAGMA journal_size_limit=0. My C++ program has two threads, one
reader(queries at 15 sec intervals) and one writer(inserts at 5 sec
intervals).

Every 3 min I am pausing insertion to run a sqlite3_wal_checkpoint_v2()
from the writer thread with the mode parameter as
SQLITE_CHECKPOINT_RESTART. To ensure that no active read operations are
going on at this point, I set a flag that checkpointing is about to take
place and wait for reader to complete (the connection is still open) before
running checkpoint. After checkpoint completion I again indicate to readers
it is okay to resume querying.


It shouldn't hurt, but you should not have to manage the readers that 
way. SQLITE_CHECKPOINT_RESTART should wait on readers as required to 
ensure that the next writer can write into the start of the wal file 
instead of appending. If SQLITE_CHECKPOINT_RESTART returns SQLITE_OK, 
the next writer should be able to restart the wal file.


If you register an sqlite3_wal_hook() callback it will be invoked to 
report the size of the wal file after each write transaction. Logging 
this information along with the checkpoint attempts and return codes 
might help to shed light on the problem.


Dan.







sqlite3_wal_checkpoint_v2() returns SQLITE_OK, and pnLog and Ckpt as
equal(around 4000), indicating complete wal file has been synced with main
db file. So next write should start from beginning according to
documentation. However, this does not seem to be happening as the
subsequent writes cause the WAL file to grow indefinitely, eventually up to
some GBs.

I did some searching and found that that readers can cause checkpoint
failure due to open transactions. However, the only reader I'm using is
ending its transaction before the checkpoint starts. What else could be
preventing the WAL file from not growing?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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


Re: [sqlite] FTS4 Problem

2014-12-24 Thread Dan Kennedy

On 12/25/2014 08:04 AM, Peter Truskier wrote:

As I mentioned, I seem to have solved the problem by doing a "rebuild" command 
on the FTS4 table. But, as I thought about it further, I'm still confused as to why 
dropping, and then re-creating the virtual table didn't solve the problem as well.

What am I missing?


Creating an FTS4 table that uses the "content=" option does not 
automatically populate the FTS index. It just creates an empty FTS index 
that SQLite assumes the user will somehow take care of populating.


Dan.









Thanks,

Peter



On Dec 24, 2014, at 12:03 PM, Peter Truskier  wrote:

Thanks so much for the quick AND HELPFUL response!

I had run the pragma, but was unaware of the command. When I ran the 'integrity-check' 
command on the virtual table, I got a "database disk image is malformed Error Code 
11"

Running the 'rebuild' command seems to have fixed the problem. I guess one of 
the triggers for keeping the virtual table up to date must have failed for some 
reason.

Thank you again!

--
Peter Truskier
Berkeley, CA USA
1-510-495-6442




On Dec 24, 2014, at 11:47 AM, Richard Hipp  wrote:

Have you run integrity checks on the database file (
https://www.sqlite.org/pragma.html#pragma_integrity_check) and on the FTS4
tables (https://www.sqlite.org/fts3.html#integcheck)?  Do they all look
correct?

On Wed, Dec 24, 2014 at 2:40 PM, Peter Truskier  wrote:


I have an sqlite database in which I've created a virtual table using
FTS4. For nearly a year, we've been using it to do full text searching with
no problem.

The database contains a table of products (tblProducts) with columns id,
SKU, itemDesc, etc.

The virtual table is created like this:

  CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts",
SKU, itemDesc)

A couple of days ago, full text searches (using "MATCH") suddenly stopped
working - always returning an empty recordset with no error. The data in
the virtual table appears to be correct.

If I do a query on the virtual table like this:

  SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',

I get a valid recordset containing the expected records. But, if I do this:

  SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',

I get an empty recordset.

I've checked the integrity of the database, and it is reported to be good.
I've tried dropping and re-creating the virtual table, and still get the
same behavior.

Does anyone have any suggestion for what might suddenly cause this
behavior after working for moths and months?

Thanks, and happy holidays!



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




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

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


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


Re: [sqlite] journal file is not removed when ATOMIC WRITE is enabled

2014-12-29 Thread Dan Kennedy

On 12/29/2014 07:57 AM, Yongil Jang wrote:

For more information,

In pager_end_transaction() function,

int bDelete = (!pPager->tempFile &&
sqlite3JournalExists(pPager->jfd)); <-- sqlite3JournalExists() returns
0

I think both of pager_end_transaction() and sqlite3JournalExists()
functions work properly in this scenario.
However, could it(two files are exist at the same time) make a problem?


It's a curious situation, but should not cause a problem.

When a new connection reads from the db for the first time, it will open 
and read the journal file in order to determine that it is not a 
hot-journal, then close it and proceed to open the db in wal mode. Once 
the db has been opened in wal mode, the cold journal file will be 
ignored completely.


So the net effect will be a slight overhead when a connection opens its 
first read transaction on the db.


Dan.








2014-12-29 9:40 GMT+09:00 Yongil Jang :

Dear developers,

Please, look at following instructions.

1) Add SQLITE_IOCAP_ATOMIC or SQLITE_IOCAP_ATOMIC4K flags to
unixDeviceCharacteristics() function (or any OS related functions)
2) Add SQLITE_ENABLE_ATOMIC_WRITE to compile option
3) Compile
4) run sqlite3 -  sqlite3 test.db
5) sqlite> pragma journal_mode=persist;
6) sqlite> create table a(id);
7) sqlite> pragma journal_mode=wal;
8) sqlite> insert into a values (1);

With these instructions, 'test.db-journal' and 'test.db-wal' are
exists at same time.

Regards,
Yongil Jang.

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


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


Re: [sqlite] Suggestion for syntax enhancement for virtual tables

2015-01-01 Thread Dan Kennedy

On 01/02/2015 01:58 PM, Hick Gunter wrote:

Temporary virtual tables sounds like an interesting concept. Does the 
xDestroy() function get called on such a beast (as opposed to xDisconnect() 
when the connection is closed)?


Just xDisconnect().

Dan.




  Should that function delete the backing store (even if a non-temporary 
virtual table is still connected)?

-Ursprüngliche Nachricht-
Von: Baruch Burstein [mailto:bmburst...@gmail.com]
Gesendet: Donnerstag, 01. Jänner 2015 08:38
An: General Discussion of SQLite Database
Betreff: [sqlite] Suggestion for syntax enhancement for virtual tables

For creating temporary virtual tables, currently you need to do:

CREATE VIRTUAL TABLE temp.t ...

Can this syntax be made to work too (similar to creating regular tables)?

CREATE VIRTUAL TEMP TABLE t ...
or
CREATE TEMP VIRTUAL TABLE t ...

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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


Re: [sqlite] Getting SQLITE_IOERR_WRITE when running sqlite

2015-01-02 Thread Dan Kennedy

On 01/02/2015 04:44 PM, Waiba, Aswin wrote:

Hi,

I am currently using sqlite version 3.7.14 in our application. We are using it 
via a single thread, however we are getting SQLITE_IOERR (10) when running the 
application. After enabling the extended result code, we found out that we were 
getting SQLITE_IOERR_WRITE (778). After going through the sqlite code for 
version 3.7.14 I could see that the error was being thrown from unixWrite() and 
unixFileControl() (as the application is deployed on Unix). However we are 
unsure why the error is being thrown from those places.
Has anyone got these kind of errors before and if yes, how was it solved. Any 
feedback will be of great help.


It means a call to write(), pwrite(), fallocate() or similar has failed. 
Because it ran out of disk space, or the media was removed or perhaps is 
faulty. Or a bug in SQLite might be causing invalid parameters to be 
passed to one of these system calls.


Running under [strace] or equivalent might help to figure out why the 
system call is failing.


Dan.






Thanks and Regards
Aswin Waiba




===
Please access the attached hyperlink for an important electronic communications 
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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


Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

2015-01-04 Thread Dan Kennedy

On 01/05/2015 01:39 PM, Hick Gunter wrote:

This is completely legal and well defined.

HAVING is applied to the RESULT set of a SELECT.

The select asks to count the "distinct kontrola" in each group of kvadrat and 
datum, the HAVING clause specifies returning only those records with pocet > 1.

If there were no pocet column in table b, this would return only the non-empty 
groups, which is what the OP intended.

As there is a pocet column in table b, the HAVING clause refers to the original b.pocet 
which contains a (from the POV of the programmer) "randomly selected from the 
group" rows' value. This is a documented SQLite feature.

SELECT kvadrat, datum, counted_pocet as pocet from (SELECT kvadrat, datum ,pocet, 
count(distinct kontrola) as counted_pocet from b group by kvadrat, datum HAVING 
pocet > 1);


SQLite prefers regular column references to aliases. For example:

  $ ./sqlite3
  SQLite version 3.8.8 2015-01-03 18:59:17
  sqlite> CREATE TABLE t1(a, b, c);
  sqlite> INSERT INTO t1 VALUES(1, 1, 1);
  sqlite> INSERT INTO t1 VALUES(2, 2, 2);
  sqlite> INSERT INTO t1 VALUES(3, 3, 3);

then:

  sqlite> SELECT a, b+1 AS c FROM t1 WHERE c=2;
  2|3
  sqlite> SELECT a, b+1 AS d FROM t1 WHERE d=2;
  1|2

In the first SELECT, the "c" in the WHERE clause still refers to column 
"c", despite the alias. In the second, "d" is an alias for "b+1". Or:


  sqlite> SELECT a, b*-1 AS c FROM t1 ORDER BY c ASC;
  3|-3
  2|-2
  1|-1

In the above, the "c" in the ORDER BY refers to (b*-1), not the column "c".

MySQL and Postgres do the same thing for the ORDER BY example. Other 
databases do not allow column aliases to be referred to from within 
WHERE clauses.


Is this actually documented anywhere? That original names trump aliases?

So I guess the same thing is happening in the HAVING clause. The column 
"pocet" is taking precedence over the alias "pocet".


MySQL does not support ungrouped columns in a GROUP BY clause. So it 
picks out the "pocet" alias and the query behaves as desired. Postgres 
does not support aliases in the GROUP BY clause, so the situation 
doesn't come up. But MySQL does agree with SQLite for the following:


  $ ./sqlite3
  sqlite> CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
  sqlite> INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 1, 3);
  sqlite> SELECT count(*) AS b FROM t1 GROUP BY b HAVING b=2;
  1
  sqlite> SELECT count(*) AS d FROM t1 GROUP BY b HAVING d=2;
  2

Showing that given a choice, MySQL picks an original column over an 
alias within the HAVING clause as well.


So, I guess, not a bug...

Dan.



















-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Samstag, 03. Jänner 2015 00:45
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] New column in select will not mask column of the same 
name in having clause and sqlite won't warn

On Sun, 28 Dec 2014 17:46:08 +0100
Tomas Telensky  wrote:


select kvadrat, datum, count(distinct kontrola) as pocet from b group
by kvadrat, datum having pocet > 1

The problem was that pocet was actually a column in table b and I
didn't notice, and the having clause was using the table column
instead of the newly derived column specified in select clause.

So far so good, but sqlite should at least issue any warning, right?

I would say it should raise an error.  The HAVING clause should include at 
least one aggregate.  Comparing a column to a constant is the job of WHERE.

The accepted syntax is ambiguous.  Was the HAVING applied before or after the 
aggregation.  IOW, did you get

1.  the count for each {kvadrat, datum} pair for which pocet > 1, or 2.  the count 
of {kvadrat, datum} pairs that have at least one pocet > 1

?

In the first case the counts would be smaller by the number of rows for which pocet 
<= 1.  In the second case results rows would be eliminated for pairs that contain 
only rows for which pocet <= 1.

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


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


Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

2015-01-04 Thread Dan Kennedy

On 01/05/2015 02:52 PM, Dan Kennedy wrote:

On 01/05/2015 01:39 PM, Hick Gunter wrote:

This is completely legal and well defined.

HAVING is applied to the RESULT set of a SELECT.

The select asks to count the "distinct kontrola" in each group of 
kvadrat and datum, the HAVING clause specifies returning only those 
records with pocet > 1.


If there were no pocet column in table b, this would return only the 
non-empty groups, which is what the OP intended.


As there is a pocet column in table b, the HAVING clause refers to 
the original b.pocet which contains a (from the POV of the 
programmer) "randomly selected from the group" rows' value. This is a 
documented SQLite feature.


SELECT kvadrat, datum, counted_pocet as pocet from (SELECT kvadrat, 
datum ,pocet, count(distinct kontrola) as counted_pocet from b group 
by kvadrat, datum HAVING pocet > 1);


SQLite prefers regular column references to aliases. For example:

  $ ./sqlite3
  SQLite version 3.8.8 2015-01-03 18:59:17
  sqlite> CREATE TABLE t1(a, b, c);
  sqlite> INSERT INTO t1 VALUES(1, 1, 1);
  sqlite> INSERT INTO t1 VALUES(2, 2, 2);
  sqlite> INSERT INTO t1 VALUES(3, 3, 3);

then:

  sqlite> SELECT a, b+1 AS c FROM t1 WHERE c=2;
  2|3
  sqlite> SELECT a, b+1 AS d FROM t1 WHERE d=2;
  1|2

In the first SELECT, the "c" in the WHERE clause still refers to 
column "c", despite the alias. In the second, "d" is an alias for 
"b+1". Or:


  sqlite> SELECT a, b*-1 AS c FROM t1 ORDER BY c ASC;
  3|-3
  2|-2
  1|-1

In the above, the "c" in the ORDER BY refers to (b*-1), not the column 
"c".


MySQL and Postgres do the same thing for the ORDER BY example. Other 
databases do not allow column aliases to be referred to from within 
WHERE clauses.


Is this actually documented anywhere? That original names trump aliases?

So I guess the same thing is happening in the HAVING clause. The 
column "pocet" is taking precedence over the alias "pocet".


MySQL does not support ungrouped columns in a GROUP BY clause. So it 
picks out the "pocet" alias and the query behaves as desired. Postgres 
does not support aliases in the GROUP BY clause, so the situation 
doesn't come up. But MySQL does agree with SQLite for the following:



In the above paragraph, read "HAVING" for "GROUP BY"





  $ ./sqlite3
  sqlite> CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
  sqlite> INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 1, 3);
  sqlite> SELECT count(*) AS b FROM t1 GROUP BY b HAVING b=2;
  1
  sqlite> SELECT count(*) AS d FROM t1 GROUP BY b HAVING d=2;
  2

Showing that given a choice, MySQL picks an original column over an 
alias within the HAVING clause as well.


So, I guess, not a bug...

Dan.



















-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Samstag, 03. Jänner 2015 00:45
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] New column in select will not mask column of 
the same name in having clause and sqlite won't warn


On Sun, 28 Dec 2014 17:46:08 +0100
Tomas Telensky <tomas.telen...@gmail.com> wrote:


select kvadrat, datum, count(distinct kontrola) as pocet from b group
by kvadrat, datum having pocet > 1

The problem was that pocet was actually a column in table b and I
didn't notice, and the having clause was using the table column
instead of the newly derived column specified in select clause.

So far so good, but sqlite should at least issue any warning, right?
I would say it should raise an error.  The HAVING clause should 
include at least one aggregate.  Comparing a column to a constant is 
the job of WHERE.


The accepted syntax is ambiguous.  Was the HAVING applied before or 
after the aggregation.  IOW, did you get


1.  the count for each {kvadrat, datum} pair for which pocet > 1, or 
2.  the count of {kvadrat, datum} pairs that have at least one pocet > 1


?

In the first case the counts would be smaller by the number of rows 
for which pocet <= 1.  In the second case results rows would be 
eliminated for pairs that contain only rows for which pocet <= 1.


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




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


Re: [sqlite] VACUUM requires 6.7 times space ?

2015-01-05 Thread Dan Kennedy

On 01/05/2015 06:22 PM, Simon Slavin wrote:

I have a database file which is 120GB in size.  It consists of two huge tables 
and an index.
Its journal_mode is DELETE.

It is on a partition with 803GB of free space.  By my calculations I have 6.7 
times the amount of free space as the database is taking up.

I use the SQLite shell tool version 3.7.9 to run VACUUM on it.  The Shell tool 
bails out reporting

CPU Time: user 2113.596836 sys 437.660032
Error: near line 5 : database or disk full.

My understanding is that VACUUM can't take more than three times the current 
size of the database file.  What does the above error mean under these 
circumstances ?


Probably running out of space wherever temp files are created.

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


Re: [sqlite] How often is xDisconnect called? (Was: Suggestion for syntax enhancement for virtual tables)

2015-01-05 Thread Dan Kennedy

On 01/06/2015 11:59 AM, Peter Aronson wrote:
It's this comment that makes me worry that xDisconnect can be called 
at other times than detach or close:


** When an in-memory Table object is deleted (for example when the
** schema is being reloaded for some reason), the VTable objects are not
** deleted and the sqlite3_vtab* handles are not xDisconnect()ed
** immediately. Instead, they are moved from the Table.pVTable list to
** another linked list headed by the sqlite3.pDisconnect member of the
** corresponding sqlite3 structure. They are then deleted/xDisconnected
** next time a statement is prepared using said sqlite3*. This is done
** to avoid deadlock issues involving multiple sqlite3.mutex mutexes.

I'm not sure exactly what this means, but it implies that xDisconnect 
can be called in the middle of a session.


It can. One scenario is if you ROLLBACK a transaction that includes 
schema modifications to the temp database. i.e. executing:


  BEGIN;
CREATE TEMP TABLE t2(x);
  ROLLBACK;

will cause the xDisconnect() method of all virtual tables in the temp 
database to be invoked. New sqlite3_vtab objects will be requested via 
xConnect() the next time the virtual table is accessed.


Dan.






Peter

On 1/2/2015 3:00 PM, Peter Aronson wrote:
If only the xDisconnect method is called on a virtual table create in 
the temp database at disconnect time, is that the only time 
xDisconnect will be called?  The documentation at sqlite.org doesn't 
seem to say.  Jay Krebich's Using SQLite says xDisconnect is "Called 
when a database containing a virtual table instance is detached or 
closed. Called once for each table instance."  But looking at the 
SQLite code and comments, I'm not sure this is true.  Is it?  If so, 
it would be easy enough when writing a Virtual Table Module to note 
that it is being created in the temp database, and do any required 
cleanup in xDisconnect instead of xDestroy for that instance.  But if 
xDisconnect can be called at other times, cleanup could be premature.


Best,

Peter


On Friday, January 2, 2015 12:56 AM, Dan Kennedy 
<danielk1...@gmail.com> wrote:




On 01/02/2015 01:58 PM, Hick Gunter wrote:
Temporary virtual tables sounds like an interesting concept. Does 
the xDestroy() function get called on such a beast (as opposed to 
xDisconnect() when the connection is closed)?

Just xDisconnect().

Dan.



   Should that function delete the backing store (even if a 
non-temporary virtual table is still connected)?


-Ursprüngliche Nachricht-
Von: Baruch Burstein [mailto:bmburst...@gmail.com]
Gesendet: Donnerstag, 01. Jänner 2015 08:38
An: General Discussion of SQLite Database
Betreff: [sqlite] Suggestion for syntax enhancement for virtual tables

For creating temporary virtual tables, currently you need to do:

CREATE VIRTUAL TABLE temp.t ...

Can this syntax be made to work too (similar to creating regular 
tables)?


CREATE VIRTUAL TEMP TABLE t ...
or
CREATE TEMP VIRTUAL TABLE t ...

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı 
___

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

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




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


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


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


Re: [sqlite] SQLite Android Bindings: how difficult to add LOCALIZED back?

2015-01-08 Thread Dan Kennedy

On 01/08/2015 07:48 AM, Philip Warner wrote:

I just saw the SQLite Android Bindings page at

http://www.sqlite.org/android/doc/trunk/www/index.wiki

but was a little disappointed to read in the details that UNICODE and 
LOCALIZED are not supported. I'd really like the latest SQLite, and 
LOCALIZED.


How difficult would it be to add LOCALIZED collation support? I'm 
guessing that the fact it's not there means it's non-trivial, but I 
was hoping otherwise...


The stumbling block is that the Android implementations use ICU. So to 
use the Android versions I think we would have to build ICU as a static 
library as well as SQLite. And ICU is quite large.


The implementations are in the file "sqlite3_android.cpp" (part of the 
Android source tree - should be possible to google it). They look quite 
self-contained, so if you were willing to build ICU as part of your app 
and hack around with the code you could probably get them to work 
without too much trouble.


Dan.



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


Re: [sqlite] Huge WAL log

2015-01-15 Thread Dan Kennedy

On 01/15/2015 12:28 AM, Jan Slodicka wrote:

Richard Hipp-3 wrote

No other active readers or writers.

Are you sure?

Writers for sure.

As far readers are concerned, the things are too complex to make an absolute
statement. (I shall check once more.)


Some APIs that might be helpful:

  * sqlite3_get_autocommit() returns 0 if you have an explicit 
transaction open.
  * sqlite3_next_stmt() can be used to iterate through all statements 
belonging to a db connection.
  * sqlite3_stmt_busy() can be used to determine if a statement has 
been stepped but not reset (and so may be holding open an implicit 
transaction).


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

Dan.






However, I can add a few observations
I made:

WAL file size was about 70 MB (as reported by the OS) until the critical
table started.

The OS started to report 7GB after the commit of the critical table
finished.

The commit itself took more than 30 min. After the commit the DB size grew
by several 100MB. What else could explain this except moving data from WAL
to DB? (I.e. WAL reset.)

Afterwards several other tables were written (total number of records close
to 1 mil), but the WAL file did not grow anymore.

After the last table finished, the application was responsive nearly
instantly. (No big data movement between WAL and DB.)

Finally, the application closed without any delay while deleting the WAL
file. (Apparently the WAL file did not contain large amount of unsaved
data.)




Because the WAL file should reset automatically after a commit...

To make sure that I understand: You mean moving a file pointer, not
shrinking of the file itself? (AFAIK, WAL file never shrinks.)



P.S. We still use v3.7.15.2. (Would like to upgrade shortly.)

P.P.S. I have a copy of all SQLite files. If it helped I could try to read
WAL file manually.






--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p79993.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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


Re: [sqlite] Huge WAL log

2015-01-16 Thread Dan Kennedy

On 01/17/2015 12:04 AM, Jan Slodicka wrote:

Simon Slavin-3 wrote

Thanks to your post I discovered multiple-row inserts so that I now
understand what you asked.

Just a note that multiple-row inserts were added to SQLite relatively
recently (2012-03-20 (3.7.11)) and, because SQLite does only
database-level locking, its overhead for INSERTs is far less than that of
SQL Server.  It might be faster to use them but I would expect it to be so
much faster than many inserts as part of one transaction.

I made a fast, perhaps oversimplified, TestA:

Create an empty database. (file based)
CREATE TABLE Test (city NVARCHAR(120) NULL COLLATE NOCASE)
BEGIN
Then I inserted N records using commands such as
INSERT INTO Test VALUES('_random_'). (_random was a random 8 character long
string.)
COMMIT
Measure WAL size
Close the DB
Measure DB size

TestB was performed with the same data except the records were grouped
INSERT INTO Test VALUES('_random_'), VALUES('_random1')...

I tested different groupings (1,2,5,10) and different N values (10 -
250).

Results:
- The more records are grouped, the faster.
- Grouping of 10 records was more than 2x faster than no grouping at all.
- WAL size did not depend on the grouping used and was just slightly larger
than the DB size.

Then I modified the test by adding an index on the single column. I run 2
sets of tests - one where the index was created before first insert and the
one with the index created after all inserts finished.

Results:
- Active index: WAL size ~ DB size
- Inactive index: WAL size ~ 50% of the DB size
- Tests with an active index were slower by 15-20%

Conclusion:
Switching off the indexing during a bulk insert brings minor advantages.
Multi-row inserts may bring larger advantages.
The reason of the "huge WAL problem" remains unclear.



Is it correct that you have a single transaction inserting lots of data 
into a table with multiple indexes on it? Something like 1GB?


When an SQL write transaction is performed, SQLite begins by modifying 
database pages within its internal page-cache. If the transaction 
modifies only a few pages, all dirty pages stay in the cache until the 
user executes "COMMIT", at which point they are appended to the *-wal 
file. However, the page-cache is of limited size (by default 2000 
pages), and once it is completely full of dirty pages SQLite begins 
appending them to the *-wal file mid-transaction in order to free up 
space. At this point each time SQLite needs to modify a page that is not 
already in the cache it must select a dirty page to write out to the 
*-wal file so as to free up space to load the new page into the cache 
where it can be modified. This means that a transaction with a large 
working set may append more than one copy of a single page to the *-wal 
file. Maybe many, many copies.


SQLite indexes are b-trees. Each b-tree node is stored on a database 
page. So if you're inserting keys in random order into a large index 
(one too large to fit entirely within the page-cache), then virtually 
all inserts result in an existing dirty page being flushed from the 
cache and appended to the *-wal file.


To avoid this it seems like there are two options - make the page-cache 
large enough to hold all the indexes or insert keys into the indexes in 
sorted order. Inserting keys in sorted order avoids the problem because 
all writes go to the right-most leaf node of the index b-tree, which 
will almost always be present in the page-cache.


To change the size of the page-cache, see the "PRAGMA cache_size" and 
"PRAGMA page_size" commands.


One way to get keys to be inserted in order is to create all indexes 
after populating the table. SQLite sorts the data before creating the 
index b-tree in this case.


The other is to create a temp (or non temp) table with *exactly the 
same* columns and indexes as the table to be populated and insert the 
new rows into it. Then running:


  INSERT INTO target_tbl SELECT * FROM temp_tbl;

In this case, SQLite detects the similar schemas and copies keys in 
sorted order from the indexes on "temp_tbl" to the corresponding index 
on "target_tbl".


Dan.









Note:
The tests were programmed in c# code that called native SQLite.dll and were
run on a W7 desktop. The results may not apply to other environments.





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p80070.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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


Re: [sqlite] regarding sqlite3_prepare_v2() func

2015-01-21 Thread Dan Kennedy

On 01/22/2015 11:53 AM, Sairam Gaddam wrote:

I have one doubt regarding sqlite code.
I have 2 programs-one with sqlite3_exec() included in the code and in other
it is not included.I included those files which are zmain.c and zmain1.c
respectively.
First i created a database and added a table "em" and added some contents
to it,then i executed and prepared the sqlite select query.
I added "printf("result");" in the "case op_resultrow" of the function
sqlite3vdbeexec().
Here when i executed zmain.c,
sqlite3_prepare_v2(db, sql, strlen(sql) + 1, , NULL);
the above statement didn't print anything.

OUTPUT:
   database opened successfully
   result
   result
   result
   result
   Operation done successfully
   Before
   Afterprep
(clearly there is nothing between before and afterprep)

But when i commented the sqlite3_exec() in zmain1.c
due to sqlite_prepare_v2() function,the program somehow entered
sqlite3vdbeexec() and printed what i gave in my printf statement.
OUTPUT:
 database opened successfully
 Operation done successfully
 Before
 result
 result
 Afterprep
(clearly there are some output between before and afterprep)

My doubt is why the function call sqlite3_prepare_v2() called
sqlite3vdbeexec in second case and why not in first program.


This mailing list strips attachments. So you will need to upload code to 
pastebin or similar or inline it in the mail so that we can see it.


The first call to sqlite3_prepare_v2() needed to load the database 
schema into memory. It does this by executing a regular "SELECT ..." 
statement on the sqlite_master table, which involves calling 
sqlite3VdbeExec(). Via sqlite3_exec(), as it happens.


Dan.







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


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


Re: [sqlite] database locked in PHP

2015-01-25 Thread Dan Kennedy

On 01/25/2015 07:35 AM, Lev wrote:

On Sat, 24 Jan 2015 20:59:22 +
Simon Slavin  wrote:


and set it to 6 (60 seconds) or so.

Okay, I try that, but I still don't understand how can a single threaded
application get a locked error.


Was the error message "database is locked" or "database table is locked"?

When you say "single threaded", are you also implying "uses a single 
connection"?


Is the statement that is hitting the SQLITE_LOCKED a DROP TABLE or DROP 
INDEX statement? If so, do you also have active statements associated 
with the same connection?


Dan.


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


Re: [sqlite] small sqlite fts snippet function or Fts Bug!

2015-01-27 Thread Dan Kennedy

On 01/27/2015 06:48 PM, boscowitch wrote:



and the in an sqlite shell (SQLite version 3.8.8.1 2015-01-20 16:51:25)
I get following for a select with snippet:

EXAMPLE OUTPUT:
sqlite> select docid,*,snippet(test) from test where german match "a";
1|[1] a b c|1] a b c
2|[{[_.,:;[1] a b c|1] a b c
3|1[1] a b c|1[1] a b c
4|[1] a b c|1] a b c
5|​[1] a b c|​[1] a b c



-As you can see for id 1 and 2  is at the right position
but all beginning non-alphanumerical [,{, etc. are just left out in the
snippet.


-ID 4 does not help and breaks the offsets so even worse



Thanks for reporting this. The issue with (1) and (2) is now fixed here:

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

I think it is a bug in the input data causing the problem in (4). The 
values inserted into "test" and "testdata" are just slightly different.


Dan.


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


Re: [sqlite] ROWID-based queries slow in FTS db

2015-01-30 Thread Dan Kennedy

On 01/29/2015 02:29 AM, farkas andras wrote:

Hi all, Im using FTS through DBD::SQLite (perl) to query large text databases 
(~10GB, ~10 million records). The regular FTS MATCH searches work fine (they usually 
run under a second), but searches based on ROWID are atrociously slow and hog massive 
amounts of memory. Im trying to retrieve a couple of adjacent rows like so:
my $q_c = $dbh->prepare( "SELECT * FROM ftstable WHERE (ROWID BETWEEN 1000 AND 
1040)" );
# my $q_c = $dbh->prepare( "SELECT * FROM ftstable LIMIT 1040 OFFSET 1000" ); # 
tried this too, it isnt any better
$q_c->execute();
The execute takes several minutes and uses ~600 MB of memory. Now, 
http://www.sqlite.org/changes.html writes that:
3.8.1: FTS4 queries are better able to make use of docid<$limit constraints to 
limit the amount of I/O required
Theres also this thread, indicating that rowid searches on FTS databases 
are optimized: 
http://sqlite.1065341.n5.nabble.com/FTS-full-text-query-vs-query-by-rowid-td77534.html
 I was using 3.7.x so I updated DBD::SQLite and that got me up to SQLite 3.8.7, but 
I see no significant improvement. Explain query gives the same result as the linked 
thread: 0|0|0|SCAN TABLE tmdata VIRTUAL TABLE INDEX 393216.
Maybe there is a better way to write the query? If not, is there any hope that 
this will get fixed?


Looks like range constraints on rowids were only taken into account when 
there was also a MATCH term in the WHERE clause. Now fixed here:


  http://www.sqlite.org/src/info/85dc12625d300f

The fix should be in 3.8.9.

Dan.


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


Re: [sqlite] ROWID-based queries slow in FTS db

2015-01-30 Thread Dan Kennedy

On 01/30/2015 10:49 PM, Dominique Devienne wrote:

On Fri, Jan 30, 2015 at 4:38 PM, Dan Kennedy <danielk1...@gmail.com> wrote:


On 01/29/2015 02:29 AM, farkas andras wrote:


[...] but searches based on ROWID are atrociously slow and hog massive
amounts of memory [...]


Looks like range constraints on rowids were only taken into account when
there was also a MATCH term in the WHERE clause. Now fixed here:

   http://www.sqlite.org/src/info/85dc12625d300f

The fix should be in 3.8.9.


Just curious Dan. The tests added do not seem to check the query plans
despite the report being about a performance issue. I only skimmed them,
and I'm unfamiliar with TCL and the exact specifics of SQLite testing, so I
could well have missed them, but I do recall seen other perf tests checking
execution plans, in addition to checking correctness. Did I miss them?


Fair point. It would be better if there were tests to show that the 
queries were being correctly optimized.


But the change was fairly trivial, and I didn't think there was much 
chance that it would fail to optimize the queries correctly. Also, it's 
a pretty obscure optimization (one complaint in how many years?), so I 
figured it wasn't all that important. Finally it's fiddly to test in 
this case, as the EXPLAIN QUERY PLAN (or even EXPLAIN) output is not 
sufficient to figure out if it's working properly or not. So I just 
checked by hand that the optimization is working.


On the other hand, that the change could contain some bug related to 
integer overflow or some other boundary condition is a real risk. So the 
tests focus on that.


Dan.


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


Re: [sqlite] Multi-thread mode question

2015-02-08 Thread Dan Kennedy

On 02/08/2015 04:30 PM, Neo Anderson wrote:

The doc says:

Multi-thread.
In this mode, SQLite can be safely used by multiple threads provided that
no single database connection is used simultaneously in two or more threads.

I have a scenario that every sqlite3_calls around a single database connection 
is protected by a recursive mutex, but I have very strange runtime error in 
sqlite3.c and each time the error occurs at a different place.

Does this mean the following statement is true:

In muti-thead mode, a single database connection cannot be shared among threads 
even if any activity around the connection is protected by a mutex.


Not true.

The only difference between multi-threaded and serialized mode is that, 
internally, every sqlite3_xxx() API call grabs a recursive mutex to 
prevent two threads from simultaneously accessing the database handle 
structure. i.e. the same thing your code is doing externally.


Note that calls on statement handles (i.e. sqlite3_step(), 
sqlite3_column_text() etc.) count as calls on the database handle that 
created them. So you need to protect them with the same mutex.


Does the application work if you configure SQLite to serialized mode?

Dan.


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


Re: [sqlite] Multi-thread mode question

2015-02-09 Thread Dan Kennedy

On 02/09/2015 02:18 PM, Hick Gunter wrote:

In serialized mode, SQLite will acquire the mutex when it detects you are "starting to 
use" the database handle (somewhere between entering sqlite3_prepare and the first 
sqlite3_step) and then HANG ON TO IT, NOT LETTING GO until the calling thread is 
"finished" (like when sqlite3_step returns SQLITE_DONE or the thread calls sqlite3_reset 
or sqlite3_finalize).


This is quite inaccurate. Here is the implementation of sqlite3_prepare():

  http://www.sqlite.org/src/artifact/173a5a4991384?ln=792

Notice that all it does is call sqlite3LockAndPrepare(). The 
implemenation of which is here:


  http://www.sqlite.org/src/artifact/173a5a4991384?ln=722-730

It grabs the database mutex, does its work, then releases the database 
mutex.


sqlite3_step() does exactly the same. It grabs the db mutex, does its 
work, releases the mutex.


SQLite does not hold the database mutex between non-nested API calls.

Dan.










In multithread mode, you are taking over this responsibility; if you take care, 
you may nest several selects from different threads into a single transaction, 
but need to be aware of the fact that they will all commit or rollback together.

-Ursprüngliche Nachricht-
Von: Neo Anderson [mailto:neo_in_mat...@msn.com]
Gesendet: Montag, 09. Februar 2015 06:34
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Multi-thread mode question


Does the application work if you configure SQLite to serialized mode?

Yes. But I am confused why serialized mode works while multi-thread mode always 
cause crashes because I also wrap calls around statement handle.


even if you wrap the sqlite3_ calls... you'll need to wrap the entire
lifetime of the statment...

Do I need to do this in serialized mode (suppose I use a single connection 
across multiple threads)?



Date: Sun, 8 Feb 2015 03:31:46 -0800
From: d3c...@gmail.com
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multi-thread mode question

it's better to use a connection per thread... the connection resource
isn't very big...
even if you wrap the sqlite3_ calls... you'll need to wrap the entire
lifetime of the statment... if you do a execute and then start
stepping and getting values while another thread starts another
statement... that's 3 individual locks, but it doesn't lock the
context of the statement being used... it will lead to bizarre crashes
in the database; similar to double-releasing memory or delayed
reference of memory that has been released.

On Sun, Feb 8, 2015 at 3:00 AM, Dan Kennedy <danielk1...@gmail.com> wrote:


On 02/08/2015 04:30 PM, Neo Anderson wrote:


The doc says:

Multi-thread.
In this mode, SQLite can be safely used by multiple threads provided
that no single database connection is used simultaneously in two or
more threads.

I have a scenario that every sqlite3_calls around a single database
connection is protected by a recursive mutex, but I have very
strange runtime error in sqlite3.c and each time the error occurs at
a different place.

Does this mean the following statement is true:

In muti-thead mode, a single database connection cannot be shared
among threads even if any activity around the connection is protected by a 
mutex.


Not true.

The only difference between multi-threaded and serialized mode is
that, internally, every sqlite3_xxx() API call grabs a recursive
mutex to prevent two threads from simultaneously accessing the database handle 
structure.
i.e. the same thing your code is doing externally.

Note that calls on statement handles (i.e. sqlite3_step(),
sqlite3_column_text() etc.) count as calls on the database handle
that created them. So you need to protect them with the same mutex.

Does the application work if you configure SQLite to serialized mode?

Dan.



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


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

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


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

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

Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)

2015-02-10 Thread Dan Kennedy

On 02/11/2015 12:31 AM, Simon Slavin wrote:

On 10 Feb 2015, at 5:01pm, Clemens Ladisch  wrote:


Janke, Julian wrote:

In my opinion, this means, we must ""only"" write a VFS implementation for
our target platform.

What file API is there?

It looks like a standard POXIS implementation.  My guess is that the best place 
to start would be



including the examples linked in section 2.3.  The OP should come back if he 
has more specific questions after that.


A simple VFS for systems that support a basic set of posix primitives is 
here:


  http://www.sqlite.org/src/artifact/69b2085076654

The code is quite easy to follow too.

Dan.







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


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


Re: [sqlite] unreached code in sqlite3.c?

2015-02-12 Thread Dan Kennedy

On 02/12/2015 09:02 PM, Jens Miltner wrote:

Hi,

I'm getting the following two warnings when compiling sqlite3.c with the latest 
clang tools:


sqlite3.c:116769:39: warning: code will never be executed [-Wunreachable-code]
 if( pTerm->wtFlags & TERM_VNULL ) continue;
   ^~~~
sqlite3.c:116716:39: warning: code will never be executed [-Wunreachable-code]
 if( pTerm->wtFlags & TERM_VNULL ) continue;
   ^~~~
2 warnings generated.


(This is for SQLite version 3.8.8.2).


 From the code, I don't immediately see why the compiler would think this code 
will never be executed, so I thought I'd bring it up with you guys.



Unless you have defined SQLITE_ENABLE_STAT4 (or STAT3), TERM_VNULL is 
defined as 0:


  http://www.sqlite.org/src/artifact/d3633e9b59210324?ln=273-277

Dan.

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


Re: [sqlite] Sync journal's directory per transaction? or until database closed? in PERSIST mode

2016-05-26 Thread Dan Kennedy

On 05/25/2016 10:54 PM, 박병언 wrote:

First of all, my English skill is not good. So please forgive me if my
sentences are rude.

I saw this is for anonymous user to report bug. I'm not sure If I need to
write down my personal information. So I didn't write my name. If I need
to, I'll send this information.

I am a Computer Science Master student in South Korea. I'm doing a research
to enhance the SQLite performance.



In PERSIST mode, journal is still remain. But the red code is always
generated in each transaction. That is, journal file descriptor is always
closed at the end of transaction and reopen at the start of next
transaction(I think this is a bug).
Do you think sync journal's directory per transaction is correct?
If this opinion is wrong, would you mind if I know why
 1. the directory must sync per transaction?
 2. PERSIST mode must close the journal file descriptor?




On UNIX, it's possible to delete a file from the file-system while 
another process has it open. In this case the other process can continue 
reading and writing its file-descriptor as normal, but the data is 
stored in memory only, not on disk (since the directory entry has been 
deleted). Once the process exits or closes the file-descriptor, the data 
is lost.


This means that if a connection using "PRAGMA journal_mode = PERSIST" 
keeps the journal file open while the database is unlocked (i.e. between 
transactions), some other process using "PRAGMA journal_mode = DELETE" 
might come along and delete the journal file while our "journal_mode = 
PERSIST" process still has it open. In this case if the "journal_mode = 
PERSIST" process then tries to execute another transaction and there is 
a power failure halfway through, there will be no journal file on disk 
following system recovery. Database corruption.


For this reason SQLite always closes the journal file at the end of a 
transaction on UNIX. On windows, where it is not possible to delete a 
file if another process has it open, SQLite holds the journal file open 
between transactions.


Dan.



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


[sqlite] Segfault during FTS index creation from huge data

2015-04-04 Thread Dan Kennedy
On 04/03/2015 10:16 PM, Artem wrote:
> Hi!
>
> The situation is like that. There?s a SQLite database with around 3 billion 
> records. Each record consists of a certain CHAR field and several other 
> additional fields with different types. The file size is approx. 340 gb. The 
> maximum content length in the doc field is 256 symbols, the content is in 
> Russian.
>
> I?m trying to create a full-text index, but it results in a Segmentation 
> Fault error. I?ve been trying to create it in different possible ways, both 
> under Windows (with SQLite Expert and my own .NET software, including one 
> with x64 architecture) and Linux (both Ubuntu and Centos). I?ve even compiled 
> sqlite from the sources, having included necessary flags for FTS3 and FTS4, 
> but every time I get one and the same error.

This does sound like a real problem, but one that might be difficult to 
track down.

Are you able to get us a stack trace of the crash? Ideally one from a 
build with compiler options "-g -DSQLITE_DEBUG" set.

Thanks,
Dan.




>
> I?ve tried two options:
> - creating a contentless FTS4, when content is stored in a regular table, and 
> FTS-table contains only index (create virtual table docs_fts using 
> fts4(content='docs'... )
> - creating a full-fledged FTS table from a regular one (insert into docs_fts 
> select doc... from docs;)
>
> SQLite is functioning for about 4 hours, after which Segmentation Fault error 
> occurs inevitably.
> There?re no NULL fields in the database.
>
> I?ve worked with 3 different SQLite versions, including the latest one, 
> available on the website. I started trying to create the full-text index of 
> the base shortly after it was created and filled; no other activity, apart 
> from filling the base with data, was conveyed. It has only one docs table, 
> that for sure doesn?t contain any NULL values.
> I also had an idea that Reindex and Vacuum might have done something wrong, 
> however Reindex happens instantly and Vacuum works rather slowly, but 
> successfully.
>
> In short, my actions are:
>
> CREATE TABLE docs ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [doc] CHAR... 
> (other fields here)
> (here?s the process of filling the base with the data, which are downloaded 
> by means of my own script from text files.)
>
> CREATE VIRTUAL TABLE docs_fts using fts4 (content='docs', doc... (other 
> fields here)
> (here?s an attempt of INSERT INTO docs_fts(docs_fts) VALUES ('rebuild') to 
> rebuild contentless FTS index)
>
> or
>
> CREATE VIRTUAL TABLE docs_fts using fts4 (doc... (other fields here)
> (here?s an attempt of INSERT INTO docs_fts SELECT doc... from docs;)
>
> or
>
> CREATE VIRTUAL TABLE docs_fts using fts3 (doc... (other fields here)
> (here?s an attempt of INSERT INTO docs_fts SELECT doc... from docs;)
>
> For each attempt I?ve been making a new copy of the source file, because I 
> suspected that the base could have got broken after Segmentation Fault. I 
> even changed the ram-cards, in case if memory was the problem.
>
> But every time I get one and the same result - Segmentation Fault error.
>
> So, can you please pay your attention to this problem and fix it ASAP?
>
> I can send you a file if you need.
>
> Thank you.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



[sqlite] Prevent database file from being overwritten by other processes

2015-04-08 Thread Dan Kennedy
On 04/08/2015 09:51 PM, R.Smith wrote:
>
>
> On 2015-04-08 04:18 PM, Fabian Pr?bstl wrote:
>> Hi there!
>>
>> Currently, we are using SQLite as our application file format for a 
>> Windows 7/C#/System.Data.SQLite based desktop application. We only 
>> allow one instance to open the file by running "set 
>> locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" when connecting to the 
>> database.
>
> BEGIN EXCLUSIVE - Locks the database from other SQLite3 database 
> connections for the time being.
> COMMIT; - Unlocks it again - so calling all this in one go is pointless.

Almost always correct. But the "PRAGMA locking_mode=EXCLUSIVE" thing 
changes the behaviour:

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






>
> That said, database locking serves only to protect from other database 
> changes... There is no way to prevent a user from intentional messing 
> with any file if they have the privileges to do so. Best practice is 
> to keep the file in your program's assigned /programdata folder or the 
> user folders (/AppData/Roaming/yourApp/ is the usual) - the typical 
> user won't go mess there or even know to look there. Other than that, 
> the entire point of an operating system is to serve its user, not your 
> program - as it should, so you cannot unfortunately protect users 
> against themselves.
>
> If this is to do with your own security being a concern (i.e. you are 
> not trying to safeguard the user) then I would strongly suggest an 
> encryption module or using a DB with user-level locking. (Even then 
> you still won't be able to protect against a willful user deleting, 
> moving, overwriting or otherwise accessing a file).
>
> At a tangent:
> How would you feel if your operating system disallowed you those 
> privileges because some program you installed asked it to? I would 
> change operating systems immediately - Viruses are a big enough 
> problem as it is - imagine being unable to get rid of them...
>
> Good luck!
> Ryan
>
>
>>
>> This all works fine, however a user can still open Windows Explorer 
>> and copy paste a file with the same name but different content (e.g. 
>> an empty file) over an existing, exclusively locked database. From 
>> what I found out with the OpenedFilesView tool, SQLite seems to open 
>> the file with SHARED_WRITE, which explains why *any* process can 
>> overwrite the contents.
>>
>> Is there an easy way of configuring / changing this so that 
>> SHARED_WRITE is not acquired? Will SQLite even function? Is it just 
>> easier to create a hidden copy and work on that?
>>
>> Thanks for the advice
>> Fabian
>> ___
>> 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] Prevent database file from being overwritten by other processes

2015-04-09 Thread Dan Kennedy
On 04/08/2015 10:52 PM, R.Smith wrote:
>
>
> On 2015-04-08 05:38 PM, Dan Kennedy wrote:
>> On 04/08/2015 09:51 PM, R.Smith wrote:
>>>
>>>
>>> On 2015-04-08 04:18 PM, Fabian Pr?bstl wrote:
>>>> Hi there!
>>>>
>>>> Currently, we are using SQLite as our application file format for a 
>>>> Windows 7/C#/System.Data.SQLite based desktop application. We only 
>>>> allow one instance to open the file by running "set 
>>>> locking_mode=EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT" when connecting to 
>>>> the database.
>>>
>>> BEGIN EXCLUSIVE - Locks the database from other SQLite3 database 
>>> connections for the time being.
>>> COMMIT; - Unlocks it again - so calling all this in one go is 
>>> pointless.
>>
>> Almost always correct. But the "PRAGMA locking_mode=EXCLUSIVE" thing 
>> changes the behaviour:
>>
>>   https://www.sqlite.org/pragma.html#pragma_locking_mode
>
> But you need an actual SELECT to get a shared lock and an actual write 
> operation to lock it exclusively, just starting the transaction and 
> ending it does nothing to that effect? Or is my understanding wrong?

That's the usual case. But "BEGIN EXCLUSIVE" actually does take an 
exclusive lock:

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




[sqlite] fts5

2015-04-09 Thread 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

2015-04-14 Thread 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

2015-04-27 Thread 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

2015-04-27 Thread 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

2015-04-29 Thread 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

2015-04-29 Thread 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

2015-04-29 Thread 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

2015-04-29 Thread 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

2015-04-29 Thread 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

2015-04-30 Thread 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", ));
> chk(sqlite3_exec(db, "BEGIN", nullptr, nullptr, nullptr));
> chk(sqlite3_exec(db, "CREATE VIRTUAL TABLE IF NOT EXISTS data USING
> fts4();", nullptr, nullptr, nullptr));
>
> 

[sqlite] Segfault during FTS index creation from huge data

2015-04-30 Thread 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

2015-08-03 Thread 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

2015-08-03 Thread 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
> varchar);
> insert or ignore into tbl(id, name) values (1,'a'), (2, 'b'

[sqlite] MMAP performance with databases over 2GB

2015-08-07 Thread 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

2015-08-07 Thread 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

2015-08-08 Thread 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

2015-08-11 Thread 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

2015-08-12 Thread 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
>>> ___
>>> sq

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

2015-08-20 Thread 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

2015-08-21 Thread 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

2015-08-26 Thread 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*)[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], );
> 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], );
> 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], );
> 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 = 

[sqlite] FTS5 Porter extra arguments not passed through

2015-08-01 Thread 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)

2015-12-11 Thread 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)

2015-12-11 Thread 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
> 

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

2015-12-11 Thread 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)

2015-12-14 Thread 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

2015-12-16 Thread 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?

2015-12-16 Thread 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

2015-12-24 Thread 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

2015-12-28 Thread 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

2015-12-30 Thread 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

2015-02-18 Thread 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

2015-02-23 Thread 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?

2015-02-25 Thread 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)

2015-07-03 Thread 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)

2015-07-06 Thread 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 ?

2015-07-16 Thread 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

2015-07-28 Thread 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

2015-08-01 Thread 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

2015-06-01 Thread 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

2015-06-03 Thread 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 *)

2015-06-06 Thread 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

2015-06-09 Thread 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

2015-06-10 Thread 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?

2015-06-11 Thread 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?

2015-06-12 Thread 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.



  1   2   3   4   5   6   7   8   9   10   >