SQLite Forum

FTS5 Error database disk image is malformed after 'delete'
Login

FTS5 Error database disk image is malformed after 'delete'

(1) By Kelvin H. (khammond) on 2020-09-11 02:11:56 [link] [source]

I get this error when running the below script.

database disk image is malformed

The cause appears to happen when:

  1. Deleting from an external content table values that do not exists or after creation.
  2. 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 [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.