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

classic Classic list List threaded Threaded
12 messages Options
ajm
Reply | Threaded
Open this post in threaded view
|

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

ajm
Hi list:

In a C++ Windows app that uses SQLite v. 3.9.1 and behaves well, I try change the search engine from FTS3/4 modules to FTS5, by means off:

1. Define the directive
#define SQLITE_ENABLE_FTS5 1

2.-  Create the table:
CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 remove_diacritics 0',columnsize=0)

3.- Populate the table:
INSERT INTO ftsm (row,nm) SELECT id,nm FROM atm WHERE(..)

After that, the app and the search engine works as espected.

To update the ftsm table after several inserts and deletes, I try to follow this steps

1a.- Delete the previous table.
DROP TABLE IF EXIST ftsm

2a.- Create table (as above)

3a.- Populate table (as above).

This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an erroro in 1a:  "database disk image is malformed".

Note that in previous attemps I believed that the problem was into try to delete a ftsm table build with the previous modules, but the error happen when trying delete a table build with the FTS5 module.

I managed to drop the ftsm table by means of create a new dbase; create the same tables (except ftsm); populate the tables, and replacing the previous dbase with the new one. But obviously, this method is primitive; time consuming, and has problems when the dbase is in use.

Some clues?

Thanks in advance.

--
Adolfo J. Millan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4 to FTS5 modules (revisited)

Dan Kennedy-4
On 12/10/2015 05:15 AM, [hidden email] wrote:

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

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

Thanks,
Dan.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
ajm
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4 to FTS5 modules(revisited)

ajm

>
> ---- Mensaje original ----
> De: Dan Kennedy <[hidden email]>
> Para:  [hidden email]
> Fecha:  Fri, 11 Dec 2015 02:41:43 +0700
> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 to FTS5 modules(revisited)
>
>>
...
>> 1a.- Delete the previous table.
>> DROP TABLE IF EXIST ftsm
>>
>> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an erroro in 1a:  "database disk image is malformed".
>>
>> Note that in previous attemps I believed that the problem was into try to delete a ftsm table build with the previous modules, but the error happen when trying delete a table build with the FTS5 module.
>>
...
>>
>> Some clues?
>
>Not really sure why it might fail there. Can you post the entire
>database schema (results of "SELECT * FROM sqlite_master" or the output
>of the .schema shell tool command)?
>

Dan:

Here is the schema:

CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 remove_diacri
tics 0',columnsize=0);

And here the entire database schema as produced by the shell:

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

HTH to diagnose the problem.

Thanks a lot.

--
Adolfo J Millan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4 to FTS5 modules(revisited)

Dan Kennedy-4

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

That all looks Ok.

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

Thanks,
Dan.







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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
ajm
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4 to FTS5modules(revisited)

ajm

>
> ---- Mensaje original ----
> De: Dan Kennedy <[hidden email]>
> Para:  [hidden email]
> Fecha:  Fri, 11 Dec 2015 15:28:33 +0700
> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 to FTS5modules(revisited)
>
>> 1a.- Delete the previous table.
>> DROP TABLE IF EXIST ftsm
>>
>> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an erroro in 1a:  "database disk image is malformed".
>>
>> Note that in previous attemps I believed that the problem was into try to delete a ftsm table build with the previous modules, but the error happen when trying delete a table build with the FTS5 module.
>>
>> ...
>>>> Some clues?
>>> Not really sure why it might fail there. Can you post the entire
>>> database schema (results of "SELECT * FROM sqlite_master" or the output
>>> of the .schema shell tool command)?
>>>
>> Dan:
>>
>> Here is the schema:
>
>That all looks Ok.
>
>If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm"
>command does it report an error as well?
>

Dan:

Surprisingly, the shell does not complain when using the same query, and indeed, drop the table.

Yeah, I also believe that the problem is in my roof, although that code has been behaving Ok from ages with the FTS3/4 modules (only changes some directives in other places of the code).

Any way, when running again the code when the table has been previously erased (with the shell), the routine works without complaint (executes the query correctly), and despite the warning, the rest of the app behaves correctly and the queries related with the search works fine.

When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, but sqlite3_step() ends with error 11.

When the table is already deleted, sqlite3_prepare_v2() ends Ok, and sqlite3_step() ends with 101.

Frankly, because the routine is the same in both times, and there are not variables to bind, really I don't know where to look. Anyway thanks for your's attention.

Cheers!

--
Adolfo J. Millan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4 to FTS5modules(revisited)

Dan Kennedy-4
On 12/11/2015 08:22 PM, [hidden email] wrote:

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

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

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

Thanks,
Dan.




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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
ajm
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4 toFTS5modules(revisited)

ajm
>
> ---- Mensaje original ----
> De: Dan Kennedy <[hidden email]>
> Para:  [hidden email]
> Fecha:  Fri, 11 Dec 2015 22:54:45 +0700
> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)
>
>>>
>>>> 1a.- Delete the previous table.
>>>> DROP TABLE IF EXIST ftsm
>>>>
>>>> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an erroro in 1a:  "database disk image is malformed".
>>>>
>>>> Note that in previous attemps I believed that the problem was into try to delete a ftsm table build with the previous modules, but the error happen when trying delete a table build with the FTS5 module.
>>>>
>>>> ...
>>>>>> Some clues?
>>>>> Not really sure why it might fail there. Can you post the entire
>>>>> database schema (results of "SELECT * FROM sqlite_master" or the output
>>>>> of the .schema shell tool command)?
>>>>>
>>>> Dan:
>>>>
>>>> Here is the schema:
>>> That all looks Ok.
>>>
>>> If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm"
>>> command does it report an error as well?
>>>
>> Dan:
>>
>> Surprisingly, the shell does not complain when using the same query, and indeed, drop the table.
>>
>> Yeah, I also believe that the problem is in my roof, although that code has been behaving Ok from ages with the FTS3/4 modules (only changes some directives in other places of the code).
>>
>> Any way, when running again the code when the table has been previously erased (with the shell), the routine works without complaint (executes the query correctly), and despite the warning, the rest of the app behaves correctly and the queries related with the search works fine.
>>
>> When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, but sqlite3_step() ends with error 11.
>
>Are you able to compile a debugging build of SQLite and set a breakpoint
>in sqlite3CorruptError()?
>
>Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS
>statement (pretty good chance), the stack trace will tell us more about
>the form of corruption SQLite thinks it has found.
>

Dan:

I managed a breakpoint int the requested function, and can tell that it has been called twice inside the sqlite3_step() function before it returns.

Here you have the call's stack:

First call of sqlite3CorrupError()  lineno == 56209

sqlite3CorruptError(int lineno) Line 133961
decodeFlags(MemPage * pPage, int flagByte) Line 56209
btreeInitPage(MemPage * pPage) Line 56251
getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, BtCursor * pCur, int bReadOnly) Line 56495
clearDatabasePage(BtShared * pBt, unsigned int pgno, int freePageFlag, int * pnChange) Line 62907
sqlite3BtreeClearTable(Btree * p, int iTable, int * pnChange) Line 62970
btreeDropTable(Btree * p, unsigned int iTable, int * piMoved) Line 63028
sqlite3BtreeDropTable(Btree * p, int iTable, int * piMoved) Line 63111
sqlite3VdbeExec(Vdbe * p) Line 77954
sqlite3Step(Vdbe * p) Line 71546
sqlite3_step(sqlite3_stmt * pStmt) Line 71608
sqlite3_exec(sqlite3 * db, const char * zSql, int (void *, int, char * *, char * *) * xCallback, void * pArg, char * * pzErrMsg) Line 103955
fts5ExecPrintf(sqlite3 * db, char * * pzErr, const char * zFormat, ...) Line 180863
sqlite3Fts5DropAll(Fts5Config * pConfig) Line 180876
fts5DestroyMethod(sqlite3_vtab * pVtab) Line 178532
sqlite3VtabCallDestroy(sqlite3 * db, int iDb, const char * zTab) Line 117587
sqlite3VdbeExec(Vdbe * p) Line 79084
sqlite3Step(Vdbe * p) Line 71546
sqlite3_step(sqlite3_stmt * pStmt) Line 71608

Secon call of sqlite3CorrupError() lineno == 56251

sqlite3CorruptError(int lineno) Line 133961
btreeInitPage(MemPage * pPage) Line 56251
getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, BtCursor * pCur, int bReadOnly) Line 56495
...
Rest the same...

Note that the line numbers can be slightly greater than the ones in the original file (SQLite 3.9.1) because the inclusion of some comments.

As I can reproduce the problem as needed, In case of interest, I can try to provide any intermediate value inside the stack.

Thanks again for yours attention.

--
Adolfo J. Millan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
ajm
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4 toFTS5modules(revisited)

ajm
In reply to this post by Dan Kennedy-4

>
> ---- Mensaje original ----
> De: Dan Kennedy <[hidden email]>
> Para:  [hidden email]
> Fecha:  Fri, 11 Dec 2015 22:54:45 +0700
> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)
>
>
>
>Are you able to compile a debugging build of SQLite and set a breakpoint
>in sqlite3CorruptError()?
>
>Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS
>statement (pretty good chance), the stack trace will tell us more about
>the form of corruption SQLite thinks it has found.
>
>

By the way, diggin in Google for solutions, I gave a try to the complete syntax for the DROP TABLE query, and has found that the form "DROP TABLE IF EXIST table_name.ftsm" goes flawlessly.

So:

- I've a workaround to the problem.

- I'm pretty sure that the problem isn't in my code.

HTH.

--
Adolfo J. Millan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
ajm
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4 toFTS5modules(revisited)

ajm
In reply to this post by Dan Kennedy-4

Oops:

Sorry for the typo. I mean

"DROP TABLE IF EXISTS database_name.table_name"

--
Adolfo J. Millan



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
ajm
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4 toFTS5modules(revisited)

ajm
In reply to this post by Dan Kennedy-4
I apologize for the mistake, but you must forget my last two posts (probably due the tiredness or the excitement of the moment).

In fact, the query "DELETE TABLE IF EXIST database_name.table_name" goes flawlessly, but indeed does not drop the table.

Sorry for the confusion.

--
Adolfo J. Millan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4 toFTS5modules(revisited)

Dan Kennedy-4
In reply to this post by ajm
On 12/13/2015 08:05 PM, [hidden email] wrote:

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

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

Can you run the whole thing under valgrind?

Dan.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
ajm
Reply | Threaded
Open this post in threaded view
|

Re: Problem when upgrading from FTS3/4toFTS5modules(revisited)

ajm

>
> ---- Mensaje original ----
> De: Dan Kennedy <[hidden email]>
> Para:  [hidden email]
> Fecha:  Mon, 14 Dec 2015 19:15:23 +0700
> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4toFTS5modules(revisited)
>
>
>
>So that looks like database corruption, except we don't think the
>database is actually corrupt as the DROP TABLE statement did not fail
>when run in the shell tool. So perhaps heap-corruption has caused the
>in-memory cache to become corrupt.
>
>Can you run the whole thing under valgrind?
>

Dan:

This is a Windows app build with MS Visual Studio C++, so can't use Valgrind.

Perhaps the cause is a heap corruption, but I doubt because the problem behaves consistently in the following scenarios:

a.- The problem appears only when the table is build with the FTS5 modules and not when using FTS3/4.
b.- The problem appears when calling the query in different points in the code.
c.- The problem appears in both: debug mode and release mode.
d.- The code behaves correctly changing the name of the table (deletes successfully any other table).

I'm pending to tests the same whith other System version in the same equipe (actually using Windows 10 with AMD 64 bits, but can boot the same with Window 7).

Any way I managed a workaround with a fakeDelete function with this logic:

Detect if the table ftsm exist:

Not exist:
   -> Create and populate the table.

Exists
   -> "DELETE FROM ftsm WHERE nm IS NOT null" (hope this erase all files)
   -> "VACUUM ftsm"
   -> Populate the table

Cheers.

--
Adolfo J. Millan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users