[sqlite] SQLite3 trunk error with old database with fts3/4
On 09/04/2015 10:13 PM, Domingo Alvarez Duarte wrote:
> Hello again !
>
> I looked at the documentaion again and realized that I was alread calling
> sqlite3_close_v2 the I commented out all of the sqlite3_next_stmt cleanup and
> no segfaults so far, I'll see if memory is released on a long running process
> to certify that everything is fine.
The trouble with sqlite3_close_v2() is that after you call it you can't
safely pass the db handle to sqlite3_next_stmt() - the data structure
may have already been freed.
Dan.
>
> Thanks a lot for your help !
>
>
>
>> Fri Sep 04 2015 4:57:57 pm CEST CEST from "Domingo Alvarez Duarte"
>> Subject: Re: [sqlite] SQLite3 trunk error
>> with
>> old database with fts3/4
>>
>> Hello !
>>
>> I did something similar to your sugestion (sqlite3_next_stmt(db, NULL))
>> and
>> it still segfaults.
>>
>> What you mention about fts3/4 having prepared statemtns that and somehow
>> I'm
>> doing a double free a good point.
>>
>> And will be sad to not be able to use sqlite3_next_stmt(db, NULL) to
>> finalize
>> any open preapred statemnt, because it's very handy when using "exceptions"
>> and having a single point to do the cleanup.
>>
>> Can somehow sqlite3_prepare somehow have any extra parameter to indicated
>> that we are using it from an extension and somehow sqlite3_next_stmt detect
>> it and skip it ?
>>
>> Or any way to safely have a central point to do a cleanup ?
>>
>> Cheers !
>>
>>
>>> Fri Sep 04 2015 4:44:02 pm CEST CEST from "Dan Kennedy"
>>> Subject: Re: [sqlite] SQLite3 trunk error with
>>> old
>>> database with fts3/4
>>>
>>> On 09/04/2015 09:29 PM, Domingo Alvarez Duarte wrote:
>>>
>>>
Hello again !
On mac os x some time ago I was getting segfaults here and tought that it
was
caused by the way os x manage memory but it doesn't seem correct.
The error happens on this code that is called before call sqlite3_close:
sqlite3 *db = sdb->db;
sqlite3_stmt* statement = NULL;
int count = 0;
while ((statement = sqlite3_next_stmt(db, statement)))
{
//do no close statements because garbage collector will
do it
//on MacOSX we get segfaults finalizing statements here
printf("sq_sqlite3_close_release:stmt:%s\n",
sqlite3_sql(statement));
sqlite3_finalize(statement);
count++;
}
if (count) return sq_throwerror(v, _SC("closing database with
%d statements not closed."), count);
>>> Hi,
>>>
>>> Two problems:
>>>
>>> After you have finalized a statement handle, it may not be passed to
>>> sqlite3_next_stmt(). Change the while() line to:
>>>
>>> while( (statement = sqlite3_next_stmt(db, NULL)) ){ ...
>>>
>>> Another reason not to do this before calling sqlite3_close() is that the
>>> FTS module may be managing some of these statement handles. So if you
>>> finalize() them before sqlite3_close() is called, then when the FTS
>>> module is shut down as part of the eventual sqlite3_close() call, it may
>>> pass the same statement handle pointers to sqlite3_finalize() - similar
>>> to a double-free of any other object or memory allocation. SQLite
>>> includes checks to try to return SQLITE_MISUSE instead of crashing when
>>> this happens, but they only work some of the time - this scenario can
>>> still cause crashes or heap corruption.
>>>
>>> A workaround is to call sqlite3_close() on the db, then do the above
>>> only if it returns SQLITE_BUSY. This works because, even though it
>>> fails, the first sqlite3_close() shuts down the FTS module -
>>> guaranteeing that it is no longer holding pointers to statement handles.
>>>
>>> Even better is not to leak statement handle pointers. The
>>> sqlite3_next_stmt() API should really only be used to help track down
>>> leaks, not to do cleanup.
>>>
>>> Dan.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
> Fri Sep 04 2015 4:18:01 pm CEST CEST from "Domingo Alvarez Duarte"
> Subject: Re: [sqlite] SQLite3 trunk error
> with
> old database with fts3/4
>
> Hello !
>
> I'm not sure where the problem is but this code worked without any
> problem
> with previous sqlite3.
>
> Here is a backtrace of a segfault using gdb (the line numbers will not
> match
> standard sqlite3.c because I have some custom extensions):
>
> enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
>
> Program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread 0x73c70700 (LWP 22336)]
> 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
> pDef=0x)
> at sqlite3.c:66869
> 66869 if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
> ){
> (gdb) bt
> #0 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
> pDef=0x)
> at sqlite3.c:66869
> #1 0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
> p4type=-1431655766,
[sqlite] First test of json and index expressions, not so good
Hello again ! Well it's slow partly because of the linear scan but there are not too many records 5000. I also tried with "indexed by" but: ___ sql = "select? json_extract(json, '$.value') AS val? from json_tbl indexed by json_tbl_idx where val = ?;"; showPlan(); ? AN ERROR HAS OCCURED [no query solution] ___ Cheers ! ?
[sqlite] First test of json and index expressions, not so good
Hello !
Here is the database dump with 50 records:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE json_tbl(id integer primary key, json text collate nocase);
INSERT INTO "json_tbl" VALUES(1,'{"id" : 1, "value" : "the_value_1"}');
INSERT INTO "json_tbl" VALUES(2,'{"id" : 2, "value" : "the_value_2"}');
INSERT INTO "json_tbl" VALUES(3,'{"id" : 3, "value" : "the_value_3"}');
INSERT INTO "json_tbl" VALUES(4,'{"id" : 4, "value" : "the_value_4"}');
INSERT INTO "json_tbl" VALUES(5,'{"id" : 5, "value" : "the_value_5"}');
INSERT INTO "json_tbl" VALUES(6,'{"id" : 6, "value" : "the_value_6"}');
INSERT INTO "json_tbl" VALUES(7,'{"id" : 7, "value" : "the_value_7"}');
INSERT INTO "json_tbl" VALUES(8,'{"id" : 8, "value" : "the_value_8"}');
INSERT INTO "json_tbl" VALUES(9,'{"id" : 9, "value" : "the_value_9"}');
INSERT INTO "json_tbl" VALUES(10,'{"id" : 10, "value" : "the_value_10"}');
INSERT INTO "json_tbl" VALUES(11,'{"id" : 11, "value" : "the_value_11"}');
INSERT INTO "json_tbl" VALUES(12,'{"id" : 12, "value" : "the_value_12"}');
INSERT INTO "json_tbl" VALUES(13,'{"id" : 13, "value" : "the_value_13"}');
INSERT INTO "json_tbl" VALUES(14,'{"id" : 14, "value" : "the_value_14"}');
INSERT INTO "json_tbl" VALUES(15,'{"id" : 15, "value" : "the_value_15"}');
INSERT INTO "json_tbl" VALUES(16,'{"id" : 16, "value" : "the_value_16"}');
INSERT INTO "json_tbl" VALUES(17,'{"id" : 17, "value" : "the_value_17"}');
INSERT INTO "json_tbl" VALUES(18,'{"id" : 18, "value" : "the_value_18"}');
INSERT INTO "json_tbl" VALUES(19,'{"id" : 19, "value" : "the_value_19"}');
INSERT INTO "json_tbl" VALUES(20,'{"id" : 20, "value" : "the_value_20"}');
INSERT INTO "json_tbl" VALUES(21,'{"id" : 21, "value" : "the_value_21"}');
INSERT INTO "json_tbl" VALUES(22,'{"id" : 22, "value" : "the_value_22"}');
INSERT INTO "json_tbl" VALUES(23,'{"id" : 23, "value" : "the_value_23"}');
INSERT INTO "json_tbl" VALUES(24,'{"id" : 24, "value" : "the_value_24"}');
INSERT INTO "json_tbl" VALUES(25,'{"id" : 25, "value" : "the_value_25"}');
INSERT INTO "json_tbl" VALUES(26,'{"id" : 26, "value" : "the_value_26"}');
INSERT INTO "json_tbl" VALUES(27,'{"id" : 27, "value" : "the_value_27"}');
INSERT INTO "json_tbl" VALUES(28,'{"id" : 28, "value" : "the_value_28"}');
INSERT INTO "json_tbl" VALUES(29,'{"id" : 29, "value" : "the_value_29"}');
INSERT INTO "json_tbl" VALUES(30,'{"id" : 30, "value" : "the_value_30"}');
INSERT INTO "json_tbl" VALUES(31,'{"id" : 31, "value" : "the_value_31"}');
INSERT INTO "json_tbl" VALUES(32,'{"id" : 32, "value" : "the_value_32"}');
INSERT INTO "json_tbl" VALUES(33,'{"id" : 33, "value" : "the_value_33"}');
INSERT INTO "json_tbl" VALUES(34,'{"id" : 34, "value" : "the_value_34"}');
INSERT INTO "json_tbl" VALUES(35,'{"id" : 35, "value" : "the_value_35"}');
INSERT INTO "json_tbl" VALUES(36,'{"id" : 36, "value" : "the_value_36"}');
INSERT INTO "json_tbl" VALUES(37,'{"id" : 37, "value" : "the_value_37"}');
INSERT INTO "json_tbl" VALUES(38,'{"id" : 38, "value" : "the_value_38"}');
INSERT INTO "json_tbl" VALUES(39,'{"id" : 39, "value" : "the_value_39"}');
INSERT INTO "json_tbl" VALUES(40,'{"id" : 40, "value" : "the_value_40"}');
INSERT INTO "json_tbl" VALUES(41,'{"id" : 41, "value" : "the_value_41"}');
INSERT INTO "json_tbl" VALUES(42,'{"id" : 42, "value" : "the_value_42"}');
INSERT INTO "json_tbl" VALUES(43,'{"id" : 43, "value" : "the_value_43"}');
INSERT INTO "json_tbl" VALUES(44,'{"id" : 44, "value" : "the_value_44"}');
INSERT INTO "json_tbl" VALUES(45,'{"id" : 45, "value" : "the_value_45"}');
INSERT INTO "json_tbl" VALUES(46,'{"id" : 46, "value" : "the_value_46"}');
INSERT INTO "json_tbl" VALUES(47,'{"id" : 47, "value" : "the_value_47"}');
INSERT INTO "json_tbl" VALUES(48,'{"id" : 48, "value" : "the_value_48"}');
INSERT INTO "json_tbl" VALUES(49,'{"id" : 49, "value" : "the_value_49"}');
CREATE INDEX json_tbl_idx on json_tbl(json_extract(json, '$.value'));
COMMIT;
?
[sqlite] Using |DataDirectory| in connection string (.NET with System.Data.SQLite)
Great, thanks! -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joe Mistachkin Sent: Friday, September 04, 2015 2:06 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Using |DataDirectory| in connection string (.NET with System.Data.SQLite) Lee Gray wrote: > > How do they make use of the connection string |DataDirectory| macro? > It is expanded when the connection is opened. It will either be replaced with the per-AppDomain "DataDirectory" datum or the base directory of the AppDomain. > > I've found lots of references online showing that it is indeed used, > but I haven't found how to extract the file path from it at runtime. > Currently, there is no easy way to do that using only publically accessible parts of System.Data.SQLite; however, this limitation will be addressed in the next release. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] First test of json and index expressions, not so good
Hello !
This is my first test with json and index expressions !
How to make usage of the expression index on queries ?
Why is json_extract so slow ?
Cheers !
Output
Time to insert??? 5000??? 0.032227??? records by second = ???
155149
count=??? 4999
json=??? the_value_1
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select raw??? 5000??? 0.00244??? records by second = ???
2.04918e+06
Time to select json_extrat no index??? 5000??? 8.12196??? records by
second = ??? 615.615
Time to create index??? 5000??? 0.00605??? records by second =
??? 826446
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select json_extrat indexed??? 5000??? 7.38144??? records by
second = ??? 677.375
?
Program
local max_count = 5000;
local sql = "CREATE TABLE IF NOT EXISTS json_tbl(id integer primary key, json
text collate nocase);";
local db = SQLite3(":memory:");
db.exec_dml(sql);
local stmt = db.prepare("insert into json_tbl(json) values(?);");
local start = os.clock();
db.exec_dml("begin;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
i));
??? stmt.step();
??? stmt.reset();
}
stmt.finalize();
db.exec_dml("commit;");
local time_spent = os.clock() -start;
print("Time to insert", max_count, time_spent, "records by second = ",
max_count/time_spent);
print("count=", db.exec_get_one("select count(*) from json_tbl"));
print("json=", db.exec_get_one("select json_extract(json, '$.value') AS val
from json_tbl? where val = 'the_value_1';"));
sql = "select? json_extract(json, '$.value') AS val? from json_tbl where
val = ?;";
local showPlan = function()
{
??? stmt = db.prepare("explain query plan " + sql);
??? while(stmt.next_row())
??? {
??? ??? local line = "";
??? ??? for(local i=0, len = stmt.col_count(); i < len; ++i)
??? ??? {
??? ??? ??? line += "\t" + stmt.col(i);
??? ??? }
??? ??? print(line);
??? }
??? stmt.finalize();
}
showPlan();
start = os.clock();
stmt = db.prepare("select * from json_tbl");
while(stmt.next_row())
{
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select raw", max_count, time_spent, "records by second = ",
max_count/time_spent);
start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select json_extrat no index", max_count, time_spent, "records
by second = ", max_count/time_spent);
start = os.clock();
db.exec_dml("create index json_tbl_idx on json_tbl(json_extract(json,
'$.value'));");
time_spent = os.clock() -start;
print("Time to create index", max_count, time_spent, "records by second = ",
max_count/time_spent);
showPlan();
start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select json_extrat indexed", max_count, time_spent, "records
by second = ", max_count/time_spent);
db.close();
[sqlite] SQLite3 trunk error with old database with fts3/4
On 09/04/2015 09:29 PM, Domingo Alvarez Duarte wrote:
> Hello again !
>
> On mac os x some time ago I was getting segfaults here and tought that it was
> caused by the way os x manage memory but it doesn't seem correct.
>
> The error happens on this code that is called before call sqlite3_close:
>
> sqlite3 *db = sdb->db;
> sqlite3_stmt* statement = NULL;
> int count = 0;
> while ((statement = sqlite3_next_stmt(db, statement)))
> {
> //do no close statements because garbage collector will
> do it
> //on MacOSX we get segfaults finalizing statements here
> printf("sq_sqlite3_close_release:stmt:%s\n",
> sqlite3_sql(statement));
> sqlite3_finalize(statement);
> count++;
> }
> if (count) return sq_throwerror(v, _SC("closing database with
> %d statements not closed."), count);
Hi,
Two problems:
After you have finalized a statement handle, it may not be passed to
sqlite3_next_stmt(). Change the while() line to:
while( (statement = sqlite3_next_stmt(db, NULL)) ){ ...
Another reason not to do this before calling sqlite3_close() is that the
FTS module may be managing some of these statement handles. So if you
finalize() them before sqlite3_close() is called, then when the FTS
module is shut down as part of the eventual sqlite3_close() call, it may
pass the same statement handle pointers to sqlite3_finalize() - similar
to a double-free of any other object or memory allocation. SQLite
includes checks to try to return SQLITE_MISUSE instead of crashing when
this happens, but they only work some of the time - this scenario can
still cause crashes or heap corruption.
A workaround is to call sqlite3_close() on the db, then do the above
only if it returns SQLITE_BUSY. This works because, even though it
fails, the first sqlite3_close() shuts down the FTS module -
guaranteeing that it is no longer holding pointers to statement handles.
Even better is not to leak statement handle pointers. The
sqlite3_next_stmt() API should really only be used to help track down
leaks, not to do cleanup.
Dan.
>
>> Fri Sep 04 2015 4:18:01 pm CEST CEST from "Domingo Alvarez Duarte"
>> Subject: Re: [sqlite] SQLite3 trunk error
>> with
>> old database with fts3/4
>>
>> Hello !
>>
>> I'm not sure where the problem is but this code worked without any problem
>> with previous sqlite3.
>>
>> Here is a backtrace of a segfault using gdb (the line numbers will not
>> match
>> standard sqlite3.c because I have some custom extensions):
>>
>> enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
>>
>> Program received signal SIGSEGV, Segmentation fault.
>> [Switching to Thread 0x73c70700 (LWP 22336)]
>> 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>> pDef=0x)
>> at sqlite3.c:66869
>> 66869 if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
>> ){
>> (gdb) bt
>> #0 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>> pDef=0x)
>> at sqlite3.c:66869
>> #1 0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
>> p4type=-1431655766, p4=0x7fffe4181588)
>> at sqlite3.c:66884
>> #2 0x00479f14 in vdbeFreeOpArray (db=0x7fffe478,
>> aOp=0x7fffe40df508, nOp=)
>> at sqlite3.c:66933
>> #3 0x0047a01c in sqlite3VdbeClearObject (db=0x7fffe478,
>> p=0x7fffe408ac88) at sqlite3.c:68920
>> #4 0x0047a0c3 in sqlite3VdbeDelete (p=0x7fffe408ac88)
>> at sqlite3.c:68941
>> #5 0x004e6044 in sqlite3VdbeFinalize (p=0x7fffe408ac88)
>> at sqlite3.c:68861
>> #6 0x004e60cd in sqlite3_finalize (pStmt=0x7fffe408ac88)
>> at sqlite3.c:70500
>>
>>
>>
>>> Fri Sep 04 2015 4:05:12 pm CEST CEST from "Dan Kennedy"
>>> Subject: Re: [sqlite] SQLite3 trunk error with
>>> old
>>> database with fts3/4
>>>
>>> On 09/04/2015 07:35 PM, Domingo Alvarez Duarte wrote:
>>>
>>>
Hello !
After fix the index issues using an old sqlite3 executable (the trunk
refuse
to work on indexes created with single quotes on field names) I'm getting
ocasionaly memory errors when using fts3/4 searches, see error below:
free(): corrupted unsorted chunks: 0x7fa3a01073a0
>>> Is this error on the trunk or with the old version?
>>>
>>> If it's on the trunk, is the error reproducible using the sqlite3 shell
>>> tool?
>>>
>>> If not, what does valgrind have to say about the app?
>>>
>>> Thanks,
>>> Dan.
>>>
>>> ___
>>> sqlite-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
>>
[sqlite] SQLite3 trunk error with old database with fts3/4
On 09/04/2015 09:18 PM, Domingo Alvarez Duarte wrote:
> Hello !
>
> I'm not sure where the problem is but this code worked without any problem
> with previous sqlite3.
>
> Here is a backtrace of a segfault using gdb (the line numbers will not match
> standard sqlite3.c because I have some custom extensions):
Extensions in the sense that the core SQLite code has been enhanced? Or
extensions as in code that interacts with SQLite using only APIs that
begin with "sqlite3_"?
If you run the app under valgrind are there any interesting errors or
warnings?
>
> enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
>
> Program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread 0x73c70700 (LWP 22336)]
> 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
> pDef=0x)
> at sqlite3.c:66869
> 66869 if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
> ){
> (gdb) bt
> #0 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
> pDef=0x)
> at sqlite3.c:66869
> #1 0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
> p4type=-1431655766, p4=0x7fffe4181588)
> at sqlite3.c:66884
> #2 0x00479f14 in vdbeFreeOpArray (db=0x7fffe478,
> aOp=0x7fffe40df508, nOp=)
> at sqlite3.c:66933
> #3 0x0047a01c in sqlite3VdbeClearObject (db=0x7fffe478,
> p=0x7fffe408ac88) at sqlite3.c:68920
> #4 0x0047a0c3 in sqlite3VdbeDelete (p=0x7fffe408ac88)
> at sqlite3.c:68941
> #5 0x004e6044 in sqlite3VdbeFinalize (p=0x7fffe408ac88)
> at sqlite3.c:68861
> #6 0x004e60cd in sqlite3_finalize (pStmt=0x7fffe408ac88)
> at sqlite3.c:70500
It's tricky to interpret this. It seems likely that the pDef pointer in
the last frame might be incorrect - or that might just be an artifact of
optimization.
Thanks,
Dan.
[sqlite] SQLite3 trunk error with old database with fts3/4
On 09/04/2015 07:35 PM, Domingo Alvarez Duarte wrote: > Hello ! > > After fix the index issues using an old sqlite3 executable (the trunk refuse > to work on indexes created with single quotes on field names) I'm getting > ocasionaly memory errors when using fts3/4 searches, see error below: > > free(): corrupted unsorted chunks: 0x7fa3a01073a0 Is this error on the trunk or with the old version? If it's on the trunk, is the error reproducible using the sqlite3 shell tool? If not, what does valgrind have to say about the app? Thanks, Dan.
[sqlite] Changes to create index on trunk is breaking old code
Hello again !
Although this problem is now fixed on trunk maybe would be a good idea to
issue warnings to prevent new code to use this flaws ?
Maybe sqlite3 executable could emit warnings to stderr when it finds bad
usage on a database for the problems that we already know, like this one ?
?
Example:
sqlite3 a_db_that_uses_malformed_sql.db
-- warning
-- use of single quotes on field names are not standard
-- the_index_table_has_the_problem
sqlite3>
?
Cheers !
?
> Fri Sep 04 2015 11:40:11 am CEST CEST from "Domingo Alvarez Duarte"
> Subject: [sqlite] Changes to create index on
>trunk is breaking old code
>
> Hello !
>
> I'm testing sqlite3 trunk on existing code and noticed that the changes to
> "create index" using expressions/functions is breaking on old databases
>
> It seems that because I used the field name single quotes now it's
> considering it an expression.
>
> On an old database I have the following to create an index:
>
> CREATE INDEX companies_number_idx ON companies ('number');
>
> ?
>
> Now if I try to do anything on that database with sqlite3 trunk I get this
> error message:
>
> malformed database schema (companies_number_idx) - indexes on expressions
>not
> yet supported
>
> ?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] sqlite-users Digest, Vol 93, Issue 4
On 2015-09-04 06:09 PM, Peter Haworth wrote: > Thanks for the info. > > Could I get round the single value limitation with something like: > > IN ((SELECT 'abc','def' FROM myTable)) Maybe what you intend is more like this: IN (SELECT 'abc' UNION ALL SELECT 'def' UNION ALL SELECT 'ghi'... etc.) alternate: IN (SELECT A FROM B) - assuming table B contains a column A that already contains all the values you need to include in the IN. > Also, in the LIKE example, looks like I don't need single quote delimiters > around the pattern? The quote delimiters enables passing the value within an SQLite statement, it is not significant to the type of value or in any way significant to the LIKE parsing. You could as easily use an identifier or parameter sans quotes, but if you wish to compare like-ness by giving a string directly in an SQL statement, it has to be contained in single quotes. That is purely because of string-passing semantics in general. This means the following examples all do the same thing: "SELECT A, 'abc%' FROM B WHERE C LIKE 'abc%';" "SELECT A, :1 FROM B WHERE C LIKE :1;" passing the value: abc% "SELECT A, D FROM B WHERE C LIKE D;" assuming this is a sub-select within another query of which there is a column D that contains a value like: abc% (I may have misinterpreted what you meant, if so, apologies - feel free to ask again) Cheers! Ryan
[sqlite] SQLite3 trunk error with old database with fts3/4
Hello !
I was calling sqlite3_close_v2 after sqlite3_next_stmt/sqlite3_finalize the
problem probably was the fts3/4 extension trying to finalize a statement I
already had finalized.
Cheers !
> Fri Sep 04 2015 5:42:03 pm CEST CEST from "Dan Kennedy"
> Subject: Re: [sqlite] SQLite3 trunk error with old
>database with fts3/4
>
> On 09/04/2015 10:13 PM, Domingo Alvarez Duarte wrote:
>
>>Hello again !
>>
>> I looked at the documentaion again and realized that I was alread calling
>> sqlite3_close_v2 the I commented out all of the sqlite3_next_stmt cleanup
>>and
>> no segfaults so far, I'll see if memory is released on a long running
>>process
>> to certify that everything is fine.
>>
> The trouble with sqlite3_close_v2() is that after you call it you can't
> safely pass the db handle to sqlite3_next_stmt() - the data structure
> may have already been freed.
>
> Dan.
>
>
>
>
>
>>Thanks a lot for your help !
>>
>>
>>
>>
>>>Fri Sep 04 2015 4:57:57 pm CEST CEST from "Domingo Alvarez Duarte"
>>> Subject: Re: [sqlite] SQLite3 trunk error
>>>with
>>> old database with fts3/4
>>>
>>> Hello !
>>>
>>> I did something similar to your sugestion (sqlite3_next_stmt(db, NULL))
>>> and
>>> it still segfaults.
>>>
>>> What you mention about fts3/4 having prepared statemtns that and somehow
>>> I'm
>>> doing a double free a good point.
>>>
>>> And will be sad to not be able to use sqlite3_next_stmt(db, NULL) to
>>> finalize
>>> any open preapred statemnt, because it's very handy when using
>>>"exceptions"
>>> and having a single point to do the cleanup.
>>>
>>> Can somehow sqlite3_prepare somehow have any extra parameter to indicated
>>> that we are using it from an extension and somehow sqlite3_next_stmt
>>>detect
>>> it and skip it ?
>>>
>>> Or any way to safely have a central point to do a cleanup ?
>>>
>>> Cheers !
>>>
>>>
>>>
Fri Sep 04 2015 4:44:02 pm CEST CEST from "Dan Kennedy"
Subject: Re: [sqlite] SQLite3 trunk error with
old
database with fts3/4
On 09/04/2015 09:29 PM, Domingo Alvarez Duarte wrote:
>Hello again !
>
> On mac os x some time ago I was getting segfaults here and tought that
>it
> was
> caused by the way os x manage memory but it doesn't seem correct.
>
> The error happens on this code that is called before call
>sqlite3_close:
>
> sqlite3 *db = sdb->db;
> sqlite3_stmt* statement = NULL;
> int count = 0;
> while ((statement = sqlite3_next_stmt(db, statement)))
> {
> //do no close statements because garbage collector will
> do it
> //on MacOSX we get segfaults finalizing statements here
> printf("sq_sqlite3_close_release:stmt:%s\n",
> sqlite3_sql(statement));
> sqlite3_finalize(statement);
> count++;
> }
> if (count) return sq_throwerror(v, _SC("closing database with
> %d statements not closed."), count);
>
>
Hi,
Two problems:
After you have finalized a statement handle, it may not be passed to
sqlite3_next_stmt(). Change the while() line to:
while( (statement = sqlite3_next_stmt(db, NULL)) ){ ...
Another reason not to do this before calling sqlite3_close() is that the
FTS module may be managing some of these statement handles. So if you
finalize() them before sqlite3_close() is called, then when the FTS
module is shut down as part of the eventual sqlite3_close() call, it may
pass the same statement handle pointers to sqlite3_finalize() - similar
to a double-free of any other object or memory allocation. SQLite
includes checks to try to return SQLITE_MISUSE instead of crashing when
this happens, but they only work some of the time - this scenario can
still cause crashes or heap corruption.
A workaround is to call sqlite3_close() on the db, then do the above
only if it returns SQLITE_BUSY. This works because, even though it
fails, the first sqlite3_close() shuts down the FTS module -
guaranteeing that it is no longer holding pointers to statement handles.
Even better is not to leak statement handle pointers. The
sqlite3_next_stmt() API should really only be used to help track down
leaks, not to do cleanup.
Dan.
>
>>Fri Sep 04 2015 4:18:01 pm CEST CEST from "Domingo Alvarez Duarte"
>> Subject: Re: [sqlite] SQLite3 trunk error
>> with
>> old database with fts3/4
>>
>> Hello !
>>
>> I'm not sure where the problem is but this code worked without any
>> problem
>> with previous sqlite3.
>>
>> Here is a backtrace of a segfault using gdb (the line numbers will not
>> match
>> standard sqlite3.c because I have some custom extensions):
>>
>> enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
[sqlite] SQLite3 trunk error with old database with fts3/4
Hello again !
I think that would be interesting to have functions similar to
sqlite3_next_stmt/sqlite3_finalize for extensions something like this:
sqlite3_next_extension / sqlite3_finalize_extension
?
Cheers !
> Fri Sep 04 2015 5:13:23 pm CEST CEST from "Domingo Alvarez Duarte"
> Subject: Re: [sqlite] SQLite3 trunk error with
>old database with fts3/4
>
> Hello again !
>
> I looked at the documentaion again and realized that I was alread calling
> sqlite3_close_v2 the I commented out all of the sqlite3_next_stmt cleanup
>and
> no segfaults so far, I'll see if memory is released on a long running
>process
> to certify that everything is fine.
>
> Thanks a lot for your help !
>
>
>
>
>>Fri Sep 04 2015 4:57:57 pm CEST CEST from "Domingo Alvarez Duarte"
>> Subject: Re: [sqlite] SQLite3 trunk error with
>> old database with fts3/4
>>
>> Hello !
>>
>> I did something similar to your sugestion (sqlite3_next_stmt(db, NULL))?
>> and
>> it still segfaults.
>>
>> What you mention about fts3/4 having prepared statemtns that and somehow
>> I'm
>> doing a double free a good point.
>>
>> And will be sad to not be able to use sqlite3_next_stmt(db, NULL) to
>> finalize
>> any open preapred statemnt, because it's very handy when using
>>"exceptions"
>> and having a single point to do the cleanup.
>>
>> Can somehow sqlite3_prepare somehow have any extra parameter to indicated
>> that we are using it from an extension and somehow sqlite3_next_stmt
>>detect
>> it and skip it ?
>>
>> Or any way to safely have a central point to do a cleanup ?
>>
>> Cheers !
>>
>>
>>
>>>Fri Sep 04 2015 4:44:02 pm CEST CEST from "Dan Kennedy"
>>> Subject: Re: [sqlite] SQLite3 trunk error with
>>>old
>>> database with fts3/4
>>>
>>> On 09/04/2015 09:29 PM, Domingo Alvarez Duarte wrote:
>>>
>>>
>>>
Hello again !
On mac os x some time ago I was getting segfaults here and tought that
it
was
caused by the way os x manage memory but it doesn't seem correct.
The error happens on this code that is called before call sqlite3_close:
sqlite3 *db = sdb->db;
sqlite3_stmt* statement = NULL;
int count = 0;
while ((statement = sqlite3_next_stmt(db, statement)))
{
//do no close statements because garbage collector will
do it
//on MacOSX we get segfaults finalizing statements here
printf("sq_sqlite3_close_release:stmt:%s\n",
sqlite3_sql(statement));
sqlite3_finalize(statement);
count++;
}
if (count) return sq_throwerror(v, _SC("closing database with
%d statements not closed."), count);
>>> Hi,
>>>
>>> Two problems:
>>>
>>> After you have finalized a statement handle, it may not be passed to
>>> sqlite3_next_stmt(). Change the while() line to:
>>>
>>> while( (statement = sqlite3_next_stmt(db, NULL)) ){ ...
>>>
>>> Another reason not to do this before calling sqlite3_close() is that the
>>> FTS module may be managing some of these statement handles. So if you
>>> finalize() them before sqlite3_close() is called, then when the FTS
>>> module is shut down as part of the eventual sqlite3_close() call, it may
>>> pass the same statement handle pointers to sqlite3_finalize() - similar
>>> to a double-free of any other object or memory allocation. SQLite
>>> includes checks to try to return SQLITE_MISUSE instead of crashing when
>>> this happens, but they only work some of the time - this scenario can
>>> still cause crashes or heap corruption.
>>>
>>> A workaround is to call sqlite3_close() on the db, then do the above
>>> only if it returns SQLITE_BUSY. This works because, even though it
>>> fails, the first sqlite3_close() shuts down the FTS module -
>>> guaranteeing that it is no longer holding pointers to statement handles.
>>>
>>> Even better is not to leak statement handle pointers. The
>>> sqlite3_next_stmt() API should really only be used to help track down
>>> leaks, not to do cleanup.
>>>
>>> Dan.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>Fri Sep 04 2015 4:18:01 pm CEST CEST from "Domingo Alvarez Duarte"
> Subject: Re: [sqlite] SQLite3 trunk error
> with
> old database with fts3/4
>
> Hello !
>
> I'm not sure where the problem is but this code worked without any
> problem
> with previous sqlite3.
>
> Here is a backtrace of a segfault using gdb (the line numbers will not
> match
> standard sqlite3.c because I have some custom extensions):
>
> enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
>
> Program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread 0x73c70700 (LWP 22336)]
> 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
> pDef=0x)
> at sqlite3.c:66869
> 66869 if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
> ){
> (gdb) bt
[sqlite] SQLite3 trunk error with old database with fts3/4
Hello again !
I looked at the documentaion again and realized that I was alread calling
sqlite3_close_v2 the I commented out all of the sqlite3_next_stmt cleanup and
no segfaults so far, I'll see if memory is released on a long running process
to certify that everything is fine.
Thanks a lot for your help !
> Fri Sep 04 2015 4:57:57 pm CEST CEST from "Domingo Alvarez Duarte"
> Subject: Re: [sqlite] SQLite3 trunk error with
>old database with fts3/4
>
> Hello !
>
> I did something similar to your sugestion (sqlite3_next_stmt(db, NULL))?
>and
> it still segfaults.
>
> What you mention about fts3/4 having prepared statemtns that and somehow
>I'm
> doing a double free a good point.
>
> And will be sad to not be able to use sqlite3_next_stmt(db, NULL) to
>finalize
> any open preapred statemnt, because it's very handy when using "exceptions"
> and having a single point to do the cleanup.
>
> Can somehow sqlite3_prepare somehow have any extra parameter to indicated
> that we are using it from an extension and somehow sqlite3_next_stmt detect
> it and skip it ?
>
> Or any way to safely have a central point to do a cleanup ?
>
> Cheers !
>
>
>>Fri Sep 04 2015 4:44:02 pm CEST CEST from "Dan Kennedy"
>> Subject: Re: [sqlite] SQLite3 trunk error with old
>> database with fts3/4
>>
>> On 09/04/2015 09:29 PM, Domingo Alvarez Duarte wrote:
>>
>>
>>>Hello again !
>>>
>>> On mac os x some time ago I was getting segfaults here and tought that it
>>> was
>>> caused by the way os x manage memory but it doesn't seem correct.
>>>
>>> The error happens on this code that is called before call sqlite3_close:
>>>
>>> sqlite3 *db = sdb->db;
>>> sqlite3_stmt* statement = NULL;
>>> int count = 0;
>>> while ((statement = sqlite3_next_stmt(db, statement)))
>>> {
>>> //do no close statements because garbage collector will
>>> do it
>>> //on MacOSX we get segfaults finalizing statements here
>>> printf("sq_sqlite3_close_release:stmt:%s\n",
>>> sqlite3_sql(statement));
>>> sqlite3_finalize(statement);
>>> count++;
>>> }
>>> if (count) return sq_throwerror(v, _SC("closing database with
>>> %d statements not closed."), count);
>>>
>>>
>> Hi,
>>
>> Two problems:
>>
>> After you have finalized a statement handle, it may not be passed to
>> sqlite3_next_stmt(). Change the while() line to:
>>
>> while( (statement = sqlite3_next_stmt(db, NULL)) ){ ...
>>
>> Another reason not to do this before calling sqlite3_close() is that the
>> FTS module may be managing some of these statement handles. So if you
>> finalize() them before sqlite3_close() is called, then when the FTS
>> module is shut down as part of the eventual sqlite3_close() call, it may
>> pass the same statement handle pointers to sqlite3_finalize() - similar
>> to a double-free of any other object or memory allocation. SQLite
>> includes checks to try to return SQLITE_MISUSE instead of crashing when
>> this happens, but they only work some of the time - this scenario can
>> still cause crashes or heap corruption.
>>
>> A workaround is to call sqlite3_close() on the db, then do the above
>> only if it returns SQLITE_BUSY. This works because, even though it
>> fails, the first sqlite3_close() shuts down the FTS module -
>> guaranteeing that it is no longer holding pointers to statement handles.
>>
>> Even better is not to leak statement handle pointers. The
>> sqlite3_next_stmt() API should really only be used to help track down
>> leaks, not to do cleanup.
>>
>> Dan.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>>
Fri Sep 04 2015 4:18:01 pm CEST CEST from "Domingo Alvarez Duarte"
Subject: Re: [sqlite] SQLite3 trunk error
with
old database with fts3/4
Hello !
I'm not sure where the problem is but this code worked without any
problem
with previous sqlite3.
Here is a backtrace of a segfault using gdb (the line numbers will not
match
standard sqlite3.c because I have some custom extensions):
enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x73c70700 (LWP 22336)]
0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
pDef=0x)
at sqlite3.c:66869
66869 if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
){
(gdb) bt
#0 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
pDef=0x)
at sqlite3.c:66869
#1 0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
p4type=-1431655766, p4=0x7fffe4181588)
at sqlite3.c:66884
#2 0x00479f14 in vdbeFreeOpArray (db=0x7fffe478,
aOp=0x7fffe40df508, nOp=)
at sqlite3.c:66933
#3 0x0047a01c in sqlite3VdbeClearObject (db=0x7fffe478,
p=0x7fffe408ac88) at sqlite3.c:68920
#4 0x0047a0c3 in sqlite3VdbeDelete (p=0x7fffe408ac88)
[sqlite] SQLite3 trunk error with old database with fts3/4
Hello !
I did something similar to your sugestion (sqlite3_next_stmt(db, NULL))? and
it still segfaults.
What you mention about fts3/4 having prepared statemtns that and somehow I'm
doing a double free a good point.
And will be sad to not be able to use sqlite3_next_stmt(db, NULL) to finalize
any open preapred statemnt, because it's very handy when using "exceptions"
and having a single point to do the cleanup.
Can somehow sqlite3_prepare somehow have any extra parameter to indicated
that we are using it from an extension and somehow sqlite3_next_stmt detect
it and skip it ?
Or any way to safely have a central point to do a cleanup ?
Cheers !
> Fri Sep 04 2015 4:44:02 pm CEST CEST from "Dan Kennedy"
> Subject: Re: [sqlite] SQLite3 trunk error with old
>database with fts3/4
>
> On 09/04/2015 09:29 PM, Domingo Alvarez Duarte wrote:
>
>>Hello again !
>>
>> On mac os x some time ago I was getting segfaults here and tought that it
>>was
>> caused by the way os x manage memory but it doesn't seem correct.
>>
>> The error happens on this code that is called before call sqlite3_close:
>>
>> sqlite3 *db = sdb->db;
>> sqlite3_stmt* statement = NULL;
>> int count = 0;
>> while ((statement = sqlite3_next_stmt(db, statement)))
>> {
>> //do no close statements because garbage collector will
>> do it
>> //on MacOSX we get segfaults finalizing statements here
>> printf("sq_sqlite3_close_release:stmt:%s\n",
>> sqlite3_sql(statement));
>> sqlite3_finalize(statement);
>> count++;
>> }
>> if (count) return sq_throwerror(v, _SC("closing database with
>> %d statements not closed."), count);
>>
> Hi,
>
> Two problems:
>
> After you have finalized a statement handle, it may not be passed to
> sqlite3_next_stmt(). Change the while() line to:
>
> while( (statement = sqlite3_next_stmt(db, NULL)) ){ ...
>
> Another reason not to do this before calling sqlite3_close() is that the
> FTS module may be managing some of these statement handles. So if you
> finalize() them before sqlite3_close() is called, then when the FTS
> module is shut down as part of the eventual sqlite3_close() call, it may
> pass the same statement handle pointers to sqlite3_finalize() - similar
> to a double-free of any other object or memory allocation. SQLite
> includes checks to try to return SQLITE_MISUSE instead of crashing when
> this happens, but they only work some of the time - this scenario can
> still cause crashes or heap corruption.
>
> A workaround is to call sqlite3_close() on the db, then do the above
> only if it returns SQLITE_BUSY. This works because, even though it
> fails, the first sqlite3_close() shuts down the FTS module -
> guaranteeing that it is no longer holding pointers to statement handles.
>
> Even better is not to leak statement handle pointers. The
> sqlite3_next_stmt() API should really only be used to help track down
> leaks, not to do cleanup.
>
> Dan.
>
>
>
>
>
>
>
>
>
>
>>
>>>Fri Sep 04 2015 4:18:01 pm CEST CEST from "Domingo Alvarez Duarte"
>>> Subject: Re: [sqlite] SQLite3 trunk error
>>>with
>>> old database with fts3/4
>>>
>>> Hello !
>>>
>>> I'm not sure where the problem is but this code worked without any
>>>problem
>>> with previous sqlite3.
>>>
>>> Here is a backtrace of a segfault using gdb (the line numbers will not
>>> match
>>> standard sqlite3.c because I have some custom extensions):
>>>
>>> enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
>>>
>>> Program received signal SIGSEGV, Segmentation fault.
>>> [Switching to Thread 0x73c70700 (LWP 22336)]
>>> 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>>> pDef=0x)
>>> at sqlite3.c:66869
>>> 66869 if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
>>> ){
>>> (gdb) bt
>>> #0 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>>> pDef=0x)
>>> at sqlite3.c:66869
>>> #1 0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
>>> p4type=-1431655766, p4=0x7fffe4181588)
>>> at sqlite3.c:66884
>>> #2 0x00479f14 in vdbeFreeOpArray (db=0x7fffe478,
>>> aOp=0x7fffe40df508, nOp=)
>>> at sqlite3.c:66933
>>> #3 0x0047a01c in sqlite3VdbeClearObject (db=0x7fffe478,
>>> p=0x7fffe408ac88) at sqlite3.c:68920
>>> #4 0x0047a0c3 in sqlite3VdbeDelete (p=0x7fffe408ac88)
>>> at sqlite3.c:68941
>>> #5 0x004e6044 in sqlite3VdbeFinalize (p=0x7fffe408ac88)
>>> at sqlite3.c:68861
>>> #6 0x004e60cd in sqlite3_finalize (pStmt=0x7fffe408ac88)
>>> at sqlite3.c:70500
>>>
>>>
>>>
>>>
Fri Sep 04 2015 4:05:12 pm CEST CEST from "Dan Kennedy"
Subject: Re: [sqlite] SQLite3 trunk error with
old
database with fts3/4
On 09/04/2015 07:35 PM, Domingo Alvarez Duarte wrote:
>Hello !
>
> After fix the index issues using an old sqlite3 executable (the trunk
> refuse
> to work on indexes created with single quotes on field names) I'm
[sqlite] SQLite3 trunk error with old database with fts3/4
Hello !
As I said before the problem is in this code (I suspect that finalizing the
statement inside a loop using sqlite3_next_stmt(db, statement) is the
problem):
??? sqlite3 *db = sdb->db;
??? sqlite3_stmt* statement = NULL;
??? int count = 0;
??? while ((statement = sqlite3_next_stmt(db, statement)))
??? {
??? ??? //do no close statements because garbage collector will
do it
??? ??? //on MacOSX we get segfaults finalizing statements here
??? printf("sq_sqlite3_close_release:stmt: %p : %s\n",
statement, sqlite3_sql(statement));
??? sqlite3_finalize(statement);
??? count++;
??? }
?
> Fri Sep 04 2015 4:33:13 pm CEST CEST from "Dan Kennedy"
> Subject: Re: [sqlite] SQLite3 trunk error with old
>database with fts3/4
>
> On 09/04/2015 09:18 PM, Domingo Alvarez Duarte wrote:
>
>>Hello !
>>
>> I'm not sure where the problem is but this code worked without any problem
>> with previous sqlite3.
>>
>> Here is a backtrace of a segfault using gdb (the line numbers will not
>>match
>> standard sqlite3.c because I have some custom extensions):
>>
> Extensions in the sense that the core SQLite code has been enhanced? Or
> extensions as in code that interacts with SQLite using only APIs that
> begin with "sqlite3_"?
>
> If you run the app under valgrind are there any interesting errors or
> warnings?
>
>
>
>>enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
>>
>> Program received signal SIGSEGV, Segmentation fault.
>> [Switching to Thread 0x73c70700 (LWP 22336)]
>> 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>> pDef=0x)
>> at sqlite3.c:66869
>> 66869 if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
>> ){
>> (gdb) bt
>> #0 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
>> pDef=0x)
>> at sqlite3.c:66869
>> #1 0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
>> p4type=-1431655766, p4=0x7fffe4181588)
>> at sqlite3.c:66884
>> #2 0x00479f14 in vdbeFreeOpArray (db=0x7fffe478,
>> aOp=0x7fffe40df508, nOp=)
>> at sqlite3.c:66933
>> #3 0x0047a01c in sqlite3VdbeClearObject (db=0x7fffe478,
>> p=0x7fffe408ac88) at sqlite3.c:68920
>> #4 0x0047a0c3 in sqlite3VdbeDelete (p=0x7fffe408ac88)
>> at sqlite3.c:68941
>> #5 0x004e6044 in sqlite3VdbeFinalize (p=0x7fffe408ac88)
>> at sqlite3.c:68861
>> #6 0x004e60cd in sqlite3_finalize (pStmt=0x7fffe408ac88)
>> at sqlite3.c:70500
>>
> It's tricky to interpret this. It seems likely that the pDef pointer in
> the last frame might be incorrect - or that might just be an artifact of
> optimization.
>
> Thanks,
> Dan.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] SQLite3 trunk error with old database with fts3/4
On 4 Sep 2015, at 4:23pm, Domingo Alvarez Duarte wrote: > I think that would be interesting to have functions similar to > sqlite3_next_stmt/sqlite3_finalize for extensions something like this: > > > sqlite3_next_extension / sqlite3_finalize_extension Almost nobody uses sqlite3_next_stmt(). You're meant to use sqlite3_finalize() instead. And if you can't use sqlite3_finalize() you just close the connection and allow SQLite to do its own cleanup. So there has not been a lot of attention paid to anything using methods like sqlite3_next_stmt(). Is it not possible to use sqlite3_finalize() in your own code ? Simon.
[sqlite] SQLite3 trunk error with old database with fts3/4
Hello again !
On mac os x some time ago I was getting segfaults here and tought that it was
caused by the way os x manage memory but it doesn't seem correct.
The error happens on this code that is called before call sqlite3_close:
??? sqlite3 *db = sdb->db;
??? sqlite3_stmt* statement = NULL;
??? int count = 0;
??? while ((statement = sqlite3_next_stmt(db, statement)))
??? {
??? ??? //do no close statements because garbage collector will
do it
??? ??? //on MacOSX we get segfaults finalizing statements here
??? printf("sq_sqlite3_close_release:stmt:%s\n",
sqlite3_sql(statement));
??? sqlite3_finalize(statement);
??? count++;
??? }
??? if (count) return sq_throwerror(v, _SC("closing database with
%d statements not closed."), count);
?
> Fri Sep 04 2015 4:18:01 pm CEST CEST from "Domingo Alvarez Duarte"
> Subject: Re: [sqlite] SQLite3 trunk error with
>old database with fts3/4
>
> Hello !
>
> I'm not sure where the problem is but this code worked without any problem
> with previous sqlite3.
>
> Here is a backtrace of a segfault using gdb (the line numbers will not
>match
> standard sqlite3.c because I have some custom extensions):
>
> enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
>
> Program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread 0x73c70700 (LWP 22336)]
> 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
> ??? pDef=0x)
> ??? at sqlite3.c:66869
> 66869??? ? if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
> ){
> (gdb) bt
> #0? 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
> ??? pDef=0x)
> ??? at sqlite3.c:66869
> #1? 0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
> ??? p4type=-1431655766, p4=0x7fffe4181588)
> ??? at sqlite3.c:66884
> #2? 0x00479f14 in vdbeFreeOpArray (db=0x7fffe478,
> ??? aOp=0x7fffe40df508, nOp=)
> ??? at sqlite3.c:66933
> #3? 0x0047a01c in sqlite3VdbeClearObject (db=0x7fffe478,
> ??? p=0x7fffe408ac88) at sqlite3.c:68920
> #4? 0x0047a0c3 in sqlite3VdbeDelete (p=0x7fffe408ac88)
> ??? at sqlite3.c:68941
> #5? 0x004e6044 in sqlite3VdbeFinalize (p=0x7fffe408ac88)
> ??? at sqlite3.c:68861
> #6? 0x004e60cd in sqlite3_finalize (pStmt=0x7fffe408ac88)
> ??? at sqlite3.c:70500
>
> ?
>
>>Fri Sep 04 2015 4:05:12 pm CEST CEST from "Dan Kennedy"
>> Subject: Re: [sqlite] SQLite3 trunk error with old
>> database with fts3/4
>>
>> On 09/04/2015 07:35 PM, Domingo Alvarez Duarte wrote:
>>
>>
>>>Hello !
>>>
>>> After fix the index issues using an old sqlite3 executable (the trunk
>>> refuse
>>> to work on indexes created with single quotes on field names) I'm getting
>>> ocasionaly memory errors when using fts3/4 searches, see error below:
>>>
>>> free(): corrupted unsorted chunks: 0x7fa3a01073a0
>>>
>>>
>> Is this error on the trunk or with the old version?
>>
>> If it's on the trunk, is the error reproducible using the sqlite3 shell
>> tool?
>>
>> If not, what does valgrind have to say about the app?
>>
>> Thanks,
>> Dan.
>>
>> ___
>> sqlite-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] Changes to create index on trunk is breaking old code
On Fri, Sep 4, 2015 at 3:50 PM, Domingo Alvarez Duarte < sqlite-mail at dev.dadbiz.es> wrote: > I now know that the single quotes is not supposed to be used to specify > field > names and I pointed this here because previous versions of sqlite permitted > it and if somehow I improperly used it probably other did so too. > >From https://www.sqlite.org/lang_keywords.html: > Programmers are cautioned not to use the two exceptions described in the > previous bullets. We emphasize that they exist only so that old and ill-formed SQL statements > will run correctly. Future versions of SQLite might raise errors instead of accepting the > malformed statements covered by the exceptions above.
[sqlite] Changes to create index on trunk is breaking old code
On Fri, Sep 4, 2015 at 3:49 PM, Igor Tandetnik wrote:
> On 9/4/2015 9:40 AM, Dominique Devienne wrote:
>
>> Could someone please point the exact rules ("naked", double-quotes,
>> square-brakets, etc...) in the doc please?
>>
>
> This is the closest:
>
> https://www.sqlite.org/lang_keywords.html
Thanks Igor. Exactly what I was looking for. Close enough. --DD
PS: I'd suggest linking it to lang_expr or create_table diagrams, when they
refer to column-name in their railroad diagrams. My $0.02.
[sqlite] SQLite3 trunk error with old database with fts3/4
Hello !
I'm not sure where the problem is but this code worked without any problem
with previous sqlite3.
Here is a backtrace of a segfault using gdb (the line numbers will not match
standard sqlite3.c because I have some custom extensions):
enter mutex 0x7fffe405f570 (1213663847) with nRef=1919906927
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x73c70700 (LWP 22336)]
0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
??? pDef=0x)
??? at sqlite3.c:66869
66869??? ? if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0
){
(gdb) bt
#0? 0x00479d85 in freeEphemeralFunction (db=0x7fffe478,
??? pDef=0x)
??? at sqlite3.c:66869
#1? 0x00479e39 in freeP4 (db=db at entry=0x7fffe478,
??? p4type=-1431655766, p4=0x7fffe4181588)
??? at sqlite3.c:66884
#2? 0x00479f14 in vdbeFreeOpArray (db=0x7fffe478,
??? aOp=0x7fffe40df508, nOp=)
??? at sqlite3.c:66933
#3? 0x0047a01c in sqlite3VdbeClearObject (db=0x7fffe478,
??? p=0x7fffe408ac88) at sqlite3.c:68920
#4? 0x0047a0c3 in sqlite3VdbeDelete (p=0x7fffe408ac88)
??? at sqlite3.c:68941
#5? 0x004e6044 in sqlite3VdbeFinalize (p=0x7fffe408ac88)
??? at sqlite3.c:68861
#6? 0x004e60cd in sqlite3_finalize (pStmt=0x7fffe408ac88)
??? at sqlite3.c:70500
?
> Fri Sep 04 2015 4:05:12 pm CEST CEST from "Dan Kennedy"
> Subject: Re: [sqlite] SQLite3 trunk error with old
>database with fts3/4
>
> On 09/04/2015 07:35 PM, Domingo Alvarez Duarte wrote:
>
>>Hello !
>>
>> After fix the index issues using an old sqlite3 executable (the trunk
>>refuse
>> to work on indexes created with single quotes on field names) I'm getting
>> ocasionaly memory errors when using fts3/4 searches, see error below:
>>
>> free(): corrupted unsorted chunks: 0x7fa3a01073a0
>>
> Is this error on the trunk or with the old version?
>
> If it's on the trunk, is the error reproducible using the sqlite3 shell
> tool?
>
> If not, what does valgrind have to say about the app?
>
> Thanks,
> Dan.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] sqlite-users Digest, Vol 93, Issue 4
Thanks for the info. Could I get round the single value limitation with something like: IN ((SELECT 'abc','def' FROM myTable)) Also, in the LIKE example, looks like I don't need single quote delimiters around the pattern? > > Message: 4 > Date: Thu, 3 Sep 2015 12:54:46 -0400 > From: Richard Hipp > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Variables in statements > Message-ID: >
[sqlite] Changes to create index on trunk is breaking old code
Hello !
First of all, thanks for reply !
I now know that the single quotes is not supposed to be used to specify field
names and I pointed this here because previous versions of sqlite permitted
it and if somehow I improperly used it probably other did so too.
It seems that it's now fixed on trunk.
Cheers !
> Fri Sep 04 2015 3:40:18 pm CEST CEST from "Dominique Devienne"
> Subject: Re: [sqlite] Changes to create index on
>trunk
>is breaking old code
>
> On Fri, Sep 4, 2015 at 2:54 PM, Richard Hipp wrote:
>
>
>>On 9/4/15, Domingo Alvarez Duarte wrote:
>>
>>>I'm testing sqlite3 trunk on existing code and noticed that the changes
>> to
>>
>>>"create index" using expressions/functions is breaking on old databases.
>>>
>> Should now be fixed on trunk. Please update and try again.
>>
>>
> Neither [1] and [2] clearly spell out the syntaxes allowed to refer to
> column or table names.
> And I don't recall reading about it in the online doc. Did I miss it (quite
> possible), or is it simply not there?
>
> Could someone please point the exact rules ("naked", double-quotes,
> square-brakets, etc...) in the doc please?
>
> Thanks, --DD
>
> https://www.sqlite.org/lang_createtable.html
> https://www.sqlite.org/lang_expr.html
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
[sqlite] Changes to create index on trunk is breaking old code
On Fri, Sep 4, 2015 at 2:54 PM, Richard Hipp wrote:
> On 9/4/15, Domingo Alvarez Duarte wrote:
> > I'm testing sqlite3 trunk on existing code and noticed that the changes
> to
> > "create index" using expressions/functions is breaking on old databases.
>
> Should now be fixed on trunk. Please update and try again.
>
Neither [1] and [2] clearly spell out the syntaxes allowed to refer to
column or table names.
And I don't recall reading about it in the online doc. Did I miss it (quite
possible), or is it simply not there?
Could someone please point the exact rules ("naked", double-quotes,
square-brakets, etc...) in the doc please?
Thanks, --DD
https://www.sqlite.org/lang_createtable.html
https://www.sqlite.org/lang_expr.html
[sqlite] SQLite3 trunk error with old database with fts3/4
Hello ! After fix the index issues using an old sqlite3 executable (the trunk refuse to work on indexes created with single quotes on field names) I'm getting ocasionaly memory errors when using fts3/4 searches, see error below: free(): corrupted unsorted chunks: 0x7fa3a01073a0 ? Cheers !
[sqlite] Stable way to SELECT from SQLite db using WAL on network share?
On 9/4/15, Todd Biggins wrote: > Thanks Richard! > > So if we remove the remote machine and focus on the same host - running as > a separate process based on System.Data.SQLite > can I safely run a SELECT query without harming the database integrity or > the 3rd party app writing to the database? That's all you need to do. There is one corner case: If the main application is writing extensively and the secondary reader app does a long-running read transaction, the read transaction can prevent the main application from resetting the WAL file. This can cause the WAL file to grow very large. Everything should still work - it will just use more disk space. The WAL will reset automatically once the read transaction completes. -- D. Richard Hipp drh at sqlite.org
[sqlite] Using |DataDirectory| in connection string (.NET with System.Data.SQLite)
Lee Gray wrote: > > How do they make use of the connection string |DataDirectory| macro? > It is expanded when the connection is opened. It will either be replaced with the per-AppDomain "DataDirectory" datum or the base directory of the AppDomain. > > I've found lots of references online showing that it is indeed used, > but I haven't found how to extract the file path from it at runtime. > Currently, there is no easy way to do that using only publically accessible parts of System.Data.SQLite; however, this limitation will be addressed in the next release. -- Joe Mistachkin
[sqlite] Changes to create index on trunk is breaking old code
On Fri, Sep 4, 2015 at 11:49 AM, Dominique Devienne
wrote:
> On Fri, Sep 4, 2015 at 11:40 AM, Domingo Alvarez Duarte <
> sqlite-mail at dev.dadbiz.es> wrote:
>
>> CREATE INDEX companies_number_idx ON companies ('number');
>>
>
> That's a string literal (single-quotes), not a column name. Should be
> either number (no quotes), or "number" (double-quotes). (Maybe also
> [number], a la MS SQL Server.)
>
> It's a change of behavior, sure, by relies on an SQLite
> backward-compatibility "quirk" you should not depend on IMHO. --DD
>
s/dependency/compatibility/g
[sqlite] Changes to create index on trunk is breaking old code
On Fri, Sep 4, 2015 at 11:40 AM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:
> CREATE INDEX companies_number_idx ON companies ('number');
>
That's a string literal (single-quotes), not a column name. Should be
either number (no quotes), or "number" (double-quotes). (Maybe also
[number], a la MS SQL Server.)
It's a change of behavior, sure, by relies on an SQLite backward-dependency
"quirk" you should not depend on IMHO. --DD
[sqlite] Stable way to SELECT from SQLite db using WAL on network share?
Thanks Richard! So if we remove the remote machine and focus on the same host - running as a separate process based on System.Data.SQLite can I safely run a SELECT query without harming the database integrity or the 3rd party app writing to the database? If so, is it a simple matter of executing the select from the same host, or do I need to set readonly on the System.Data.SQLite data connection or ? Again - my goal is not to affect the existing database's internal settings. I truly appreciate your input! On Fri, Sep 4, 2015 at 11:24 AM, Richard Hipp wrote: > WAL mode requires the use of shared memory (in this case implemented > using the mmapped -shm file) in order to coordinate access to the > write-ahead log by various processes doing access. But in order to > share memory, all processes accessing the database have to be on the > same machine, obviously. > > So, no, it is not possible to run a database in WAL mode on one > machine and simultaneously access that database over a network share > from another machine. You'll have to switch to one of the rollback > modes (ex: DELETE) in order to do that. > > On 9/4/15, Todd Biggins wrote: > > Hello all, > > > > I've been digging through SQLite.org and these forums, but I was hoping > for > > a final consensus on my conundrum: > > > > I have a third party app that is writing to a local v3.0 SQLite database > > with WAL. I want to access it outside of the existing app solely for the > > purpose of SELECT queries (using System.Data.SQLite) - these queries > would > > be run by only one other process, either from the same host or a remote > > machine (by sharing the database folder through NTFS/SMB) > > > > Am I able to run SELECT queries without altering the database integrity > for > > the other app? Since the database is using WAL, it looks like I need > write > > access to the folder structure, and even a SELECT query attempts to > > generate the -WAL and -SHM file on the database - files which already > exist > > from the 3rd party app. > > > > To reiterate - is there a "safe" way to use System.Data.SQLite to > passively > > view tables without altering the database or its cache? > > > > Thanks so much for your time! > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Changes to create index on trunk is breaking old code
Hello !
I'm testing sqlite3 trunk on existing code and noticed that the changes to
"create index" using expressions/functions is breaking on old databases.
It seems that because I used the field name single quotes now it's
considering it an expression.
On an old database I have the following to create an index:
CREATE INDEX companies_number_idx ON companies ('number');
?
Now if I try to do anything on that database with sqlite3 trunk I get this
error message:
malformed database schema (companies_number_idx) - indexes on expressions not
yet supported
?
[sqlite] Stable way to SELECT from SQLite db using WAL on network share?
WAL mode requires the use of shared memory (in this case implemented using the mmapped -shm file) in order to coordinate access to the write-ahead log by various processes doing access. But in order to share memory, all processes accessing the database have to be on the same machine, obviously. So, no, it is not possible to run a database in WAL mode on one machine and simultaneously access that database over a network share from another machine. You'll have to switch to one of the rollback modes (ex: DELETE) in order to do that. On 9/4/15, Todd Biggins wrote: > Hello all, > > I've been digging through SQLite.org and these forums, but I was hoping for > a final consensus on my conundrum: > > I have a third party app that is writing to a local v3.0 SQLite database > with WAL. I want to access it outside of the existing app solely for the > purpose of SELECT queries (using System.Data.SQLite) - these queries would > be run by only one other process, either from the same host or a remote > machine (by sharing the database folder through NTFS/SMB) > > Am I able to run SELECT queries without altering the database integrity for > the other app? Since the database is using WAL, it looks like I need write > access to the folder structure, and even a SELECT query attempts to > generate the -WAL and -SHM file on the database - files which already exist > from the 3rd party app. > > To reiterate - is there a "safe" way to use System.Data.SQLite to passively > view tables without altering the database or its cache? > > Thanks so much for your time! > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] Stable way to SELECT from SQLite db using WAL on network share?
Hello all, I've been digging through SQLite.org and these forums, but I was hoping for a final consensus on my conundrum: I have a third party app that is writing to a local v3.0 SQLite database with WAL. I want to access it outside of the existing app solely for the purpose of SELECT queries (using System.Data.SQLite) - these queries would be run by only one other process, either from the same host or a remote machine (by sharing the database folder through NTFS/SMB) Am I able to run SELECT queries without altering the database integrity for the other app? Since the database is using WAL, it looks like I need write access to the folder structure, and even a SELECT query attempts to generate the -WAL and -SHM file on the database - files which already exist from the 3rd party app. To reiterate - is there a "safe" way to use System.Data.SQLite to passively view tables without altering the database or its cache? Thanks so much for your time!
[sqlite] Changes to create index on trunk is breaking old code
On 9/4/2015 9:40 AM, Dominique Devienne wrote:
> Could someone please point the exact rules ("naked", double-quotes,
> square-brakets, etc...) in the doc please?
This is the closest:
https://www.sqlite.org/lang_keywords.html
It talks about keywords, but actually the rules works if you replace
"keyword" with "any sequence of characters", including spaces and
punctuation (except that a "naked", un-delimited sequence must be a
valid identifier).
--
Igor Tandetnik
[sqlite] Changes to create index on trunk is breaking old code
On 9/4/15, Domingo Alvarez Duarte wrote: > > I'm testing sqlite3 trunk on existing code and noticed that the changes to > "create index" using expressions/functions is breaking on old databases. > Should now be fixed on trunk. Please update and try again. -- D. Richard Hipp drh at sqlite.org
