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?
