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

What am I missing?

Thanks,

Peter


> On Dec 24, 2014, at 12:03 PM, Peter Truskier <ptrusk...@gmail.com> wrote:
> 
> Thanks so much for the quick AND HELPFUL response!
> 
> I had run the pragma, but was unaware of the command. When I ran the 
> 'integrity-check' command on the virtual table, I got a "database disk image 
> is malformed Error Code 11"
> 
> Running the 'rebuild' command seems to have fixed the problem. I guess one of 
> the triggers for keeping the virtual table up to date must have failed for 
> some reason.
> 
> Thank you again! 
> 
> --
> Peter Truskier
> Berkeley, CA USA
> 1-510-495-6442
> 
> 
> 
>> On Dec 24, 2014, at 11:47 AM, Richard Hipp <d...@sqlite.org> wrote:
>> 
>> Have you run integrity checks on the database file (
>> https://www.sqlite.org/pragma.html#pragma_integrity_check) and on the FTS4
>> tables (https://www.sqlite.org/fts3.html#integcheck)?  Do they all look
>> correct?
>> 
>> On Wed, Dec 24, 2014 at 2:40 PM, Peter Truskier <ptrusk...@gmail.com> wrote:
>> 
>>> I have an sqlite database in which I've created a virtual table using
>>> FTS4. For nearly a year, we've been using it to do full text searching with
>>> no problem.
>>> 
>>> The database contains a table of products (tblProducts) with columns id,
>>> SKU, itemDesc, etc.
>>> 
>>> The virtual table is created like this:
>>> 
>>>  CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts",
>>> SKU, itemDesc)
>>> 
>>> A couple of days ago, full text searches (using "MATCH") suddenly stopped
>>> working - always returning an empty recordset with no error. The data in
>>> the virtual table appears to be correct.
>>> 
>>> If I do a query on the virtual table like this:
>>> 
>>>  SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',
>>> 
>>> I get a valid recordset containing the expected records. But, if I do this:
>>> 
>>>  SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',
>>> 
>>> I get an empty recordset.
>>> 
>>> I've checked the integrity of the database, and it is reported to be good.
>>> I've tried dropping and re-creating the virtual table, and still get the
>>> same behavior.
>>> 
>>> Does anyone have any suggestion for what might suddenly cause this
>>> behavior after working for moths and months?
>>> 
>>> Thanks, and happy holidays!
>>> 
>>> 
>>> 
>>> _______________________________________________
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> 
>> 
>> 
>> 
>> -- 
>> D. Richard Hipp
>> d...@sqlite.org
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

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

Reply via email to