Re: [sqlite] FTS4 Problem
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
Re: [sqlite] FTS4 Problem
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
[sqlite] FTS4 Problem
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
