为何使用SQLite FTS5搜索时出现‘database disk image is malformed’错误？

错误信息 #

Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 11: 'database disk image is malformed'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync()
   at DatabaseManager.Search(String query) in E:\Documents\projects\sqlite-in-csharp\SqliteExample\DatabaseManager.cs:line 106
   at Program.Program.Main(String[] args) in E:\Documents\projects\sqlite-in-csharp\SqliteExample\Program.cs:line 42
   at Program.Program.<Main>(String[] args)

执行完整性检查后，meta表显示正常，但即使重建search虚拟表，问题仍然存在。

相关代码 #

Program.cs #

using Microsoft.Data.Sqlite;
using static DatabaseManager;

namespace Program
{
    public struct Track
    {
        public string Path;
        public string Artist;
        public string Title;

        public Track(string path, string artist, string title)
        {
            Path = path;
            Artist = artist;
            Title = title;
        }
    }

    class Program
    {
        static async Task Main(string[] args)
        {
            // Get database connection
            DatabaseManager manager = await DatabaseManager.Build();

            // Insert some data
            Track[] tracks = [
                    new Track("E:/Music/King Gizzard & the Lizard Wizard/Flying Microtonal Banana (2017-02-24)/1.1 - Rattlesnake.flac", "King Gizzard & the Lizard Wizard", "Rattlesnake"),
                    new Track("E:/Music/Tame Impala/Lonerism (2012-10-08)/1.8 - Keep On Lying.flac", "Tame Impala", "Keep On Lying"),
                    new Track("E:/Music/Tame Impala/Lonerism (2012-10-08)/1.4 - Mind Mischief.flac", "Tame Impala", "Mind Mischief"),
                    new Track("E:/Music/Tame Impala/Lonerism (2012-10-08)/1.3 - Apocalypse Dreams.flac", "Tame Impala", "Apocalypse Dreams"),
                    new Track("E:/Music/LCD Soundsystem/This Is Happening (2010-12-03)/1.1 - Dance Yrself Clean.flac", "LCD Soundsystem", "Dance Yrself Clean"),
                    new Track("E:/Music/LCD Soundsystem/LCD Soundsystem (2005-01-24)/1.1 - Daft Punk Is Playing at My House.flac", "LCD Soundsystem", "Daft Punk Is Playing at My House"),
                    new Track("E:/Music/The Silents/Things to Learn (2008-03-28)/1.2 - Ophelia.flac", "The Silents", "Ophelia"),
                    new Track("E:/Music/The Silents/Things to Learn (2008-03-28)/1.4 - Tune for a Nymph.flac", "The Silents", "Tune for a Nymph"),
                    new Track("E:/Music/The Silents/Things to Learn (2008-03-28)/1.6 - Nightcrawl.flac", "The Silents", "Nightcrawl"),
                    new Track("E:/Music/The Silents/Things to Learn (2008-03-28)/1.9 - See the Future.flac", "The Silents", "See the Future"),
            ];
            await manager.InsertData(tracks);

            await manager.Search("\"Tame\"");
        }
    }
}

DatabaseManager.cs #

using Microsoft.Data.Sqlite;
using Program;

public class DatabaseManager
{
    private SqliteConnection Connection { get; set; }

    public static async Task<DatabaseManager> Build()
    {
        SqliteConnection connection = new SqliteConnection("Data Source=meta.db");
        await connection.OpenAsync();

        DatabaseManager manager = new DatabaseManager(connection);

        await manager.InitTables();

        return manager;
    }

    private DatabaseManager(SqliteConnection connection)
    {
        Connection = connection;
    }

    private async Task InitTables()
    {
        using (SqliteCommand command = this.Connection.CreateCommand())
        {
            command.CommandText = """
                CREATE TABLE IF NOT EXISTS meta (
                    path TEXT PRIMARY KEY NOT NULL,
                    artist TEXT,
                    title TEXT
                ) WITHOUT ROWID;
                """;
            await command.ExecuteNonQueryAsync();

            command.CommandText = """
                CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
                    path UNINDEXED,
                    artist,
                    title,
                    content=meta,
                    content_rowid=path
                );
                """;
            await command.ExecuteNonQueryAsync();
            command.CommandText = """
                CREATE TRIGGER IF NOT EXISTS meta_ai AFTER INSERT ON meta BEGIN
                    INSERT INTO search(path, artist, title) VALUES (new.path, new.artist, new.title);
                END;
                CREATE TRIGGER IF NOT EXISTS meta_ad AFTER DELETE ON meta BEGIN
                    INSERT INTO search(search, path, artist, title) VALUES ('delete', old.path, old.artist, old.title);
                END;
                CREATE TRIGGER IF NOT EXISTS meta_au AFTER UPDATE ON meta BEGIN
                    INSERT INTO search(search, path, artist, title) VALUES ('delete', old.path, old.artist, old.title);
                    INSERT INTO search(path, artist, title) VALUES (new.path, new.artist, new.title);
                END;
                """;
            await command.ExecuteNonQueryAsync();
        }
    }

    public async Task InsertData(Track data)
    {
        using (SqliteCommand command = this.Connection.CreateCommand())
        {
            command.CommandText = "INSERT INTO meta VALUES (?1, ?2, ?3);";

            command.Parameters.AddWithValue("?1", data.Path);
            command.Parameters.AddWithValue("?2", data.Artist);
            command.Parameters.AddWithValue("?3", data.Title);

            await command.ExecuteNonQueryAsync();
        }
    }

    public async Task InsertData(Track[] data)
    {
        using (SqliteCommand command = this.Connection.CreateCommand())
        {
            command.CommandText = "INSERT INTO meta VALUES (?1, ?2, ?3);";

            var pathParameter = command.Parameters.Add("?1", SqliteType.Text);
            var artistParameter = command.Parameters.Add("?2", SqliteType.Text);
            var titleParameter = command.Parameters.Add("?3", SqliteType.Text);

            foreach (Track track in data)
            {
                pathParameter.Value = track.Path;
                artistParameter.Value = track.Artist;
                titleParameter.Value = track.Title;

                await command.ExecuteNonQueryAsync();
            }
        }
    }

    public async Task Search(string query)
    {
        using (SqliteCommand command = this.Connection.CreateCommand())
        {
            command.CommandText = "SELECT * FROM search WHERE search MATCH ?1;";
            command.Parameters.AddWithValue("?1", query);

            var data = await command.ExecuteReaderAsync();
            while (await data.ReadAsync())
            {
                Console.WriteLine("Test 1");
                Console.WriteLine($"{data.GetValue(0)}, {data.GetValue(1)}, {data.GetValue(2)}");
            }

            Console.WriteLine("Test 2");
        }
    }

    public async Task ResetSanity()
    {
        using (SqliteCommand command = this.Connection.CreateCommand())
        {
            command.CommandText = """
                INSERT INTO search(search) VALUES ('integrity-check');
                INSERT INTO search(search) VALUES ('rebuild');
                INSERT INTO search(search) VALUES ('integrity-check');
                """;

            await command.ExecuteNonQueryAsync();
        }
    }
}

用户疑问 #

请问是我的语法有误？还是在匹配前需要执行某些操作，或是虚拟表需要特定配置？

错误原因 #

FTS5的content_rowid参数有严格要求：它必须指向内容表中的整数类型ROWID列（要么是SQLite自动生成的隐式ROWID，要么是显式定义的整数主键）。但你的代码中：

1. meta表使用了WITHOUT ROWID，这意味着表没有整数ROWID；
2. 你把content_rowid设置为TEXT类型的path列，违反了FTS5的规则，导致虚拟表内部数据结构异常，最终触发“database disk image is malformed”错误。

修正步骤 #

1. 修改meta表结构 #

去掉WITHOUT ROWID，并添加一个整数类型的自增主键：

CREATE TABLE IF NOT EXISTS meta (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT UNIQUE NOT NULL,
    artist TEXT,
    title TEXT
);

2. 调整FTS5虚拟表配置 #

将content_rowid指向新的整数主键id，保持path为UNINDEXED：

CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
    path UNINDEXED,
    artist,
    title,
    content=meta,
    content_rowid=id
);

3. 修正触发器逻辑 #

FTS5的delete命令需要传入content_rowid对应的整数值，而非原path。更新后的触发器如下：

CREATE TRIGGER IF NOT EXISTS meta_ai AFTER INSERT ON meta BEGIN
    INSERT INTO search(path, artist, title) VALUES (new.path, new.artist, new.title);
END;
CREATE TRIGGER IF NOT EXISTS meta_ad AFTER DELETE ON meta BEGIN
    INSERT INTO search(search, content_rowid) VALUES ('delete', old.id);
END;
CREATE TRIGGER IF NOT EXISTS meta_au AFTER UPDATE ON meta BEGIN
    INSERT INTO search(search, content_rowid) VALUES ('delete', old.id);
    INSERT INTO search(path, artist, title) VALUES (new.path, new.artist, new.title);
END;

4. 重置数据库 #

原数据库已损坏，需删除现有的meta.db文件，重新运行程序生成新数据库。

修正后的完整InitTables方法 #

private async Task InitTables()
{
    using (SqliteCommand command = this.Connection.CreateCommand())
    {
        // 修改meta表结构
        command.CommandText = """
            CREATE TABLE IF NOT EXISTS meta (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                path TEXT UNIQUE NOT NULL,
                artist TEXT,
                title TEXT
            );
            """;
        await command.ExecuteNonQueryAsync();

        // 调整FTS5虚拟表配置
        command.CommandText = """
            CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
                path UNINDEXED,
                artist,
                title,
                content=meta,
                content_rowid=id
            );
            """;
        await command.ExecuteNonQueryAsync();

        // 修正触发器
        command.CommandText = """
            CREATE TRIGGER IF NOT EXISTS meta_ai AFTER INSERT ON meta BEGIN
                INSERT INTO search(path, artist, title) VALUES (new.path, new.artist, new.title);
            END;
            CREATE TRIGGER IF NOT EXISTS meta_ad AFTER DELETE ON meta BEGIN
                INSERT INTO search(search, content_rowid) VALUES ('delete', old.id);
            END;
            CREATE TRIGGER IF NOT EXISTS meta_au AFTER UPDATE ON meta BEGIN
                INSERT INTO search(search, content_rowid) VALUES ('delete', old.id);
                INSERT INTO search(path, artist, title) VALUES (new.path, new.artist, new.title);
            END;
            """;
        await command.ExecuteNonQueryAsync();
    }
}

额外优化建议 #

• InsertData方法无需修改，id为自动递增，插入时无需传入；
• 搜索时可关联meta表获取完整数据，示例SQL：

SELECT m.* FROM search s JOIN meta m ON s.content_rowid = m.id WHERE s MATCH ?1;

内容的提问来源于stack exchange，提问作者ThreeRoundedSquares