date:20141127

Re: [sqlite] Recursive SQL query does not use expected index

2014-11-27 Thread Richard Hipp

On Thu, Nov 27, 2014 at 10:33 AM, Scholz Maik (CM-AI/ECB2) <
maik.sch...@de.bosch.com> wrote:

> Hi,
>
> I like to use a recursive query for analyzing a table with a tree like
> data structure.
> In my table t1, each row has a column with the parent rowed/o value.
> Functional, the queries working fine, but because of the expected size of
> t1,
> i like to help the query planer with the parent-child relation by creating
> this index.
> "CREATE INDEX IF NOT EXISTS i_t1 ON t1 (p_o)"
>
> root/o=1
> child/o=2;p_o=1
> child/o=3;p_o=1
> child/o=4;p_o=1
> child/o=5;p_o=4
> child/o=6;p_o=4
> child/o=7;p_o=4
>
> In practice: Q4 is much faster then Q6 because of using the "AUTOMATIC
> COVERING INDEX (id=?)".
>
> My question:
> Why is the index i_t1 not used in Q6?
>

Without doing a detailed analysis of your queries, my first guess would be
that you need to change the type declaration on p_o to be INTEGER instead
of TEXT, since it is referencing an integer.

 p_o INTEGER REFERENCES t1(o),

That one change might be enough of a hint to the SQLite query planner to
help it do a better job.

Secondarily, it seems like the SQL-ish thing to do seems like it would be
to set p_o to NULL (instead of 0) when the row has no parent.  This
probably won't make the query run any faster, though.


>
> Maik
>
> Example:
> Q1:)
> BEGIN TRANSACTION;
> CREATE TABLE "t1" (
> `o` INTEGER,
> `p_o`   TEXT NOT NULL,
> `a` TEXT NOT NULL,
> `id`TEXT NOT NULL,
> `tag`   TEXT,
> PRIMARY KEY(o)
> );
> INSERT INTO `t1` VALUES(103903,0,18,59207,2);
> INSERT INTO `t1` VALUES(103904,103903,18,59207,516);
> INSERT INTO `t1` VALUES(103905,103903,18,59207,5);
> INSERT INTO `t1` VALUES(103906,103903,18,59207,545);
> INSERT INTO `t1` VALUES(103907,103906,18,59207,8576);
> INSERT INTO `t1` VALUES(103908,103906,18,59207,8484);
> INSERT INTO `t1` VALUES(103909,103908,18,59207,9344);
> INSERT INTO `t1` VALUES(103910,103908,18,59207,9345);
> INSERT INTO `t1` VALUES(103911,103908,18,59207,9253);
> INSERT INTO `t1` VALUES(103912,103911,18,59207,9600);
> INSERT INTO `t1` VALUES(103913,103906,18,59207,8476);
> INSERT INTO `t1` VALUES(103914,103913,18,59207,7297);
> INSERT INTO `t1` VALUES(103915,103913,18,59207,7185);
> INSERT INTO `t1` VALUES(103916,103915,18,59207,4353);
> INSERT INTO `t1` VALUES(103917,103913,18,59207,7186);
> INSERT INTO `t1` VALUES(103918,103917,18,59207,4609);
> INSERT INTO `t1` VALUES(103919,103913,18,59207,7193);
> INSERT INTO `t1` VALUES(103920,103919,18,59207,6444);
> INSERT INTO `t1` VALUES(103921,103920,18,59207,11392);
> INSERT INTO `t1` VALUES(103922,103920,18,59207,11393);
> INSERT INTO `t1` VALUES(103923,103906,18,59207,8476);
> COMMIT;
>
> Q2:)
> CREATE INDEX IF NOT EXISTS i_t1 ON t1 (p_o)
>
> sqlite> select * from t1;
> "103903""0" "18""59207" "2"
> "103904""103903""18""59207" "516"
> "103905""103903""18""59207" "5"
> "103906""103903""18""59207" "545"
> "103907""103906""18""59207" "8576"
> "103908""103906""18""59207" "8484"
> "103909""103908""18""59207" "9344"
> "103910""103908""18""59207" "9345"
> "103911""103908""18""59207" "9253"
> "103912""103911""18""59207" "9600"
> "103913""103906""18""59207" "8476"
> "103914""103913""18""59207" "7297"
> "103915""103913""18""59207" "7185"
> "103916""103915""18""59207" "4353"
> "103917""103913""18""59207" "7186"
> "103918""103917""18""59207" "4609"
> "103919""103913""18""59207" "7193"
> "103920""103919""18""59207" "6444"
> "103921""103920""18""59207" "11392"
> "103922""103920""18""59207" "11393"
> "103923""103906""18""59207" "8476"
>
> Q3:)
> EXPLAIN QUERY PLAN WITH RECURSIVE
> tn(o,level,roottag,id,path) AS (
> SELECT o,0,tag,id,printf('%04p',tag&0xff) AS path FROM t1 WHERE
> unlikely(p_o=0) AND (tag=0x02 OR tag=0x03)
> UNION
> SELECT t1.o,tn.level+1,tn.roottag,tn.id
> ,printf('%s.%04p',tn.path,t1.tag&0xff)
> FROM t1,tn
> WHERE tn.id=t1.id AND tn.o=t1.p_o
> )
> SELECT * FROM tn;
>
> =>Remark: The relation "tn.id=t1.id" is optional!
>
> "2" "0" "0" "SEARCH TABLE t1 USING INDEX i_t1 (p_o=?)"
> "3" "0" "1" "SCAN TABLE tn"
> "3" "1" "0" "SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX
> (id=?)"
> "1" "0" "0" "COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE
> (UNION)"
> "0" "0" "0" "SCAN SUBQUERY 1"
>
> Q4:)
> WITH RECURSIVE
> tn(o,level,roottag,id,path) AS (
> SELECT o,0,tag,id,printf('%04p',tag&0xff) AS path FROM t1 WHERE
> unlikely(p_o=0) AND (tag=0x02 OR tag=0x03)
> UNION
> SELECT

[sqlite] Recursive SQL query does not use expected index

2014-11-27 Thread Scholz Maik (CM-AI/ECB2)

Hi,

I like to use a recursive query for analyzing a table with a tree like data 
structure.
In my table t1, each row has a column with the parent rowed/o value.
Functional, the queries working fine, but because of the expected size of t1,
i like to help the query planer with the parent-child relation by creating this 
index.
"CREATE INDEX IF NOT EXISTS i_t1 ON t1 (p_o)"

root/o=1
child/o=2;p_o=1
child/o=3;p_o=1
child/o=4;p_o=1
child/o=5;p_o=4
child/o=6;p_o=4
child/o=7;p_o=4

In practice: Q4 is much faster then Q6 because of using the "AUTOMATIC COVERING 
INDEX (id=?)".

My question:
Why is the index i_t1 not used in Q6?

Maik

Example:
Q1:)
BEGIN TRANSACTION;
CREATE TABLE "t1" (
`o` INTEGER,
`p_o`   TEXT NOT NULL,
`a` TEXT NOT NULL,
`id`TEXT NOT NULL,
`tag`   TEXT,
PRIMARY KEY(o)
);
INSERT INTO `t1` VALUES(103903,0,18,59207,2);
INSERT INTO `t1` VALUES(103904,103903,18,59207,516);
INSERT INTO `t1` VALUES(103905,103903,18,59207,5);
INSERT INTO `t1` VALUES(103906,103903,18,59207,545);
INSERT INTO `t1` VALUES(103907,103906,18,59207,8576);
INSERT INTO `t1` VALUES(103908,103906,18,59207,8484);
INSERT INTO `t1` VALUES(103909,103908,18,59207,9344);
INSERT INTO `t1` VALUES(103910,103908,18,59207,9345);
INSERT INTO `t1` VALUES(103911,103908,18,59207,9253);
INSERT INTO `t1` VALUES(103912,103911,18,59207,9600);
INSERT INTO `t1` VALUES(103913,103906,18,59207,8476);
INSERT INTO `t1` VALUES(103914,103913,18,59207,7297);
INSERT INTO `t1` VALUES(103915,103913,18,59207,7185);
INSERT INTO `t1` VALUES(103916,103915,18,59207,4353);
INSERT INTO `t1` VALUES(103917,103913,18,59207,7186);
INSERT INTO `t1` VALUES(103918,103917,18,59207,4609);
INSERT INTO `t1` VALUES(103919,103913,18,59207,7193);
INSERT INTO `t1` VALUES(103920,103919,18,59207,6444);
INSERT INTO `t1` VALUES(103921,103920,18,59207,11392);
INSERT INTO `t1` VALUES(103922,103920,18,59207,11393);
INSERT INTO `t1` VALUES(103923,103906,18,59207,8476);
COMMIT;

Q2:)
CREATE INDEX IF NOT EXISTS i_t1 ON t1 (p_o)

sqlite> select * from t1;
"103903""0" "18""59207" "2"
"103904""103903""18""59207" "516"
"103905""103903""18""59207" "5"
"103906""103903""18""59207" "545"
"103907""103906""18""59207" "8576"
"103908""103906""18""59207" "8484"
"103909""103908""18""59207" "9344"
"103910""103908""18""59207" "9345"
"103911""103908""18""59207" "9253"
"103912""103911""18""59207" "9600"
"103913""103906""18""59207" "8476"
"103914""103913""18""59207" "7297"
"103915""103913""18""59207" "7185"
"103916""103915""18""59207" "4353"
"103917""103913""18""59207" "7186"
"103918""103917""18""59207" "4609"
"103919""103913""18""59207" "7193"
"103920""103919""18""59207" "6444"
"103921""103920""18""59207" "11392"
"103922""103920""18""59207" "11393"
"103923""103906""18""59207" "8476"

Q3:)
EXPLAIN QUERY PLAN WITH RECURSIVE
tn(o,level,roottag,id,path) AS (
SELECT o,0,tag,id,printf('%04p',tag&0xff) AS path FROM t1 WHERE unlikely(p_o=0) 
AND (tag=0x02 OR tag=0x03)
UNION
SELECT t1.o,tn.level+1,tn.roottag,tn.id,printf('%s.%04p',tn.path,t1.tag&0xff)
FROM t1,tn
WHERE tn.id=t1.id AND tn.o=t1.p_o
)
SELECT * FROM tn;

=>Remark: The relation "tn.id=t1.id" is optional!

"2" "0" "0" "SEARCH TABLE t1 USING INDEX i_t1 (p_o=?)"
"3" "0" "1" "SCAN TABLE tn"
"3" "1" "0" "SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (id=?)"
"1" "0" "0" "COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)"
"0" "0" "0" "SCAN SUBQUERY 1"

Q4:)
WITH RECURSIVE
tn(o,level,roottag,id,path) AS (
SELECT o,0,tag,id,printf('%04p',tag&0xff) AS path FROM t1 WHERE unlikely(p_o=0) 
AND (tag=0x02 OR tag=0x03)
UNION
SELECT t1.o,tn.level+1,tn.roottag,tn.id,printf('%s.%04p',tn.path,t1.tag&0xff)
FROM t1,tn
WHERE tn.id=t1.id AND tn.o=t1.p_o
)
SELECT * FROM tn;

"103903""0" "2" "59207" "0002"
"103905""1" "2" "59207" "0002.0005"
"103904""1" "2" "59207" "0002.0004"
"103906""1" "2" "59207" "0002.0021"
"103913""2" "2" "59207" "0002.0021.001C"
"103923""2" "2" "59207" "0002.0021.001C"
"103908""2" "2" "59207" "0002.0021.0024"
"103907""2" "2" "59207" "0002.0021.0080"
"103915""3" "2" "59207" "0002.0021.001C.0011"
"103917""3" "2" "59207" "0002.0021.001C.0012"
"103919""3" "2" "59207" "0002.0021.001C.0019"
"103914""3" "2" "59207" "0002.0021.001C.0081"
"103911""3" "2"

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

2014-11-27 Thread Richard Hipp

On Thu, Nov 27, 2014 at 7:33 AM, Dominique Devienne 
wrote:

> On Thu, Nov 27, 2014 at 12:57 PM, Dan Kennedy 
> wrote:
>
> > Thanks for tracking this down.Should be fixed here:
> >
> >   http://www.sqlite.org/src/info/f095cde579e7417306
>
>
> Dan, the test reads:
>
> ifcapable fts3 {  ... }
>
> yet both the comments and code use fts4. Should that be fts4, or it somehow
> doesn't matter? --DD
>

fts3 and fts4 are really the same code base.  fts4 merely enables some
extra options that are not backwards compatible, so it had to have a
different name.  The ifcapable command in the test harness only understands
"fts3".  So I believe Dan's test case is correct as written.
-- 
D. Richard Hipp
d...@sqlite.org
___
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 Dominique Devienne

On Thu, Nov 27, 2014 at 12:57 PM, Dan Kennedy  wrote:

> Thanks for tracking this down.Should be fixed here:
>
>   http://www.sqlite.org/src/info/f095cde579e7417306

Dan, the test reads:

ifcapable fts3 {  ... }

yet both the comments and code use fts4. Should that be fts4, or it somehow
doesn't matter? --DD
___
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 Paul

Thank you very much, Dan!


> 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.
> 
>
___
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] Read overflow in CREATE VIRTUAL TABLE query (formerly Corrupting pointers to the lookaside smallacator)

2014-11-27 Thread Paul


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.

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

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] Corrupting pointers to the lookaside smallacator

2014-11-27 Thread Paul


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? 

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

Re: [sqlite] Recursive SQL query does not use expected index

[sqlite] Recursive SQL query does not use expected index

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

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

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

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

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

Re: [sqlite] Corrupting pointers to the lookaside smallacator

Re: [sqlite] Corrupting pointers to the lookaside smallacator

9 matches

Site Navigation

Mail list logo

Footer information