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

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

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

Can you run the whole thing under valgrind?

Dan.




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

2015-12-13 Thread a...@zator.com
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] Problem when upgrading from FTS3/4 toFTS5modules(revisited)

2015-12-13 Thread a...@zator.com

Oops:

Sorry for the typo. I mean

"DROP TABLE IF EXISTS database_name.table_name"

--
Adolfo J. Millan





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

2015-12-13 Thread a...@zator.com

>
>  Mensaje original 
> De: Dan Kennedy 
> Para:  sqlite-users at mailinglists.sqlite.org
> Fecha:  Fri, 11 Dec 2015 22:54:45 +0700
> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 
> toFTS5modules(revisited)
>
> 
>
>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] Problem when upgrading from FTS3/4 toFTS5modules(revisited)

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

Dan:

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

Here you have the call's stack:

First call of sqlite3CorrupError()  lineno == 56209

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

Secon call of sqlite3CorrupError() lineno == 56251

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

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

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

Thanks again for yours attention.

--
Adolfo J. Millan