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.




Regards
Nick


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to