On 2018-07-10 21:17, Dan Kennedy wrote:
On 07/11/2018 02:56 AM, Nick wrote:
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?
Try running the FTS5 integrity-check command with the 3.24.0 command
line to ensure it really is corrupt:
https://www.sqlite.org/fts5.html#the_integrity_check_command
The index can be rebuilt using the rebuild command:
https://www.sqlite.org/fts5.html#the_rebuild_command
3.13.0 was about 2 years ago. There have been a couple of fixes for
fts5 corruption bugs since then. This one, for example:
https://www.sqlite.org/src/info/9a2de4f05fabf7e7
So you may have hit a known issue. Hard to say.
Dan.
Part II
With the help from Dan the FTS5 table was fixed and then subsequently
worked as expected. For belt and braces, using the 3.24 sqlite cli
client, I created a new db with the below PRAGMA statements and then ran
".dump"' to copy over the records from the previous db.
PRAGMA legacy_file_format = off;
PRAGMA page_size = 4096;
PRAGMA auto_vacuum = 2;
PRAGMA foreign_keys = on;
PRAGMA journal_mode = wal;
PRAGMA application_id = 19;
Both PRAGMA and FTS integrity returned ok and manual testing showed the
new db worked as expected. At the same time I've upgrade apsw to the
latest version (I saw it downloaded 3.24 file during compiling).
A number of days later I've gone back and ran the INSERT INTO
[i_epg]([i_epg]) VALUES('integrity-check') cmd and disappointingly it
returned Error: database disk image is malformed.
However unlike my first report above the same FTS5 queries are all
working and returning results as expected.
I'm at a loss.
Regards
Nick
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
