Using sqlite cli version 3.13 I have a simple schema with a virtual FTS5 table
providing full index searching. It is accessed by a python application using
apsw==3.13.0.post1.
I could successfully use the full index functionality during manual testing of
the db at creation time (probably a year ago now) however, recently I've been
getting "Error: database disk image is malformed" messages when running queries
on the FTS5 virtual table.
In an attempt to explore further I downloaded the latest 3.24 version. With
this latest version I used the ".backup" command to create a copy of the file
in the hope of eliminating HDD errors being a culprit.
Running pragma quick_check and integrity_check on the copied db both return ok.
The schema of the FTS5 table is:
CREATE VIRTUAL TABLE IF NOT EXISTS [i_epg] USING fts5 (
[mangled_title],
[mangled_subtitle],
[mangled_summary],
content=[t_epg],
content_rowid=[tid]
);
The table is exclusive kept up to date using triggers:
-- Triggers to keep the FTS index up to date.
CREATE TRIGGER IF NOT EXISTS i_epg_ai AFTER INSERT ON [t_epg] BEGIN
INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES (new.[tid], new.[mangled_title],
new.[mangled_subtitle], new.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_ad AFTER DELETE ON [t_epg] BEGIN
INSERT INTO [i_epg]([i_epg], rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES('delete', old.[tid], old.[mangled_title],
old.[mangled_subtitle], old.[mangled_summary]);
END;
CREATE TRIGGER IF NOT EXISTS i_epg_au AFTER UPDATE ON [t_epg] BEGIN
INSERT INTO [i_epg]([i_epg], rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES('delete', old.[tid], old.[mangled_title],
old.[mangled_subtitle], old.[mangled_summary]);
INSERT INTO [i_epg](rowid, [mangled_title], [mangled_subtitle],
[mangled_summary]) VALUES (new.[tid], new.[mangled_title],
new.[mangled_subtitle], new.[mangled_summary]);
END;
Running SQL queries on the normal tables all work as expected. Digging further
on the FTS5 queries I noticed the following behaviour:
SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + ban*';
- expect results - actually returns "Error: database disk image is malformed"
immediately
SELECT * FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } : big + ban*';
- expect no results - returns no results
SELECT [mangled_title] FROM [i_epg] WHERE [i_epg] MATCH '{ mangled_title } :
black + adder';
- expect results - returns results not matching request
The Black Adder
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Morning Show Exclusives
Deal of the Day
Four in a Bed
The Black Adder
The Black Adder
The Black Adder
The Black Adder
Denim & Co
The Shoe Stylist
Our World: Crisis in Catalonia
The Black Adder
The Black Adder
The Black Adder
I've never come across a disk image malformed error in my years of using
sqlite3 so not sure where to turn to next. Questions are:
1. Is this a known issue with FTS5 tables and if so is there a workaround?
2. It appears the FTS5 virtual table is corrupt. Is there a way to rebuild the
FTS5 (drop table and recreate?) from just the sqlite cli tool?
Regards
Nick
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
