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
