FTS5 Error database disk image is malformed after 'delete'
(1) By Kelvin H. (khammond) on 2020-09-11 02:11:56 [source]
I get this error when running the below script.
database disk image is malformed
The cause appears to happen when:
- Deleting from an external content table values that do not exists or after creation.
- Accessing the rank column or using bm25 on said table.
This script reproduces the error:
CREATE TABLE test (
id INTEGER PRIMARY KEY,
name TEXT,
value TEXT
);
INSERT INTO test (name, value) VALUES
('hello', 'world'),
('quick', 'the quick text'),
('brown fox', 'just a brown fox roaming');
CREATE VIRTUAL TABLE test_idx USING fts5(name, value, content=test, content_rowid=id);
INSERT INTO test_idx (test_idx, rowid, name, value)
SELECT 'delete', id, name, value FROM test;
INSERT INTO test_idx (rowid, name, value)
SELECT id, name, value FROM test;
SELECT name FROM test_idx WHERE test_idx MATCH 'quick' ORDER BY rank;
Error: near line 22: database disk image is malformed
(2) By Dan Kennedy (dan) on 2020-09-11 15:05:21 in reply to 1 [link] [source]
Thanks for the report. There are a couple of cases now caught in the 'delete' command instead of later on:
https://sqlite.org/src/info/b79f19edfd33c2a7
Really though, this sort of thing corrupts the index. The docs say:
If the values "inserted" into the text columns as part of a 'delete' command are not the same as those currently stored within the table, the results may be unpredictable.
The extended error code should be SQLITE_CORRUPT_VTAB.
Dan.