# Sqlite DB Recovery

> Use when SQLite 库损坏/malformed/写失败/需安全备份运行中库。点查抢救→判重写回→重建 FTS。

- Skill: `yyyyyhhhhh0639/sqlite-db-recovery` (Agent Skill)
- Install (CLI): `npx skillmds@latest add yyyyyhhhhh0639/sqlite-db-recovery`
- Raw SKILL.md: https://api.skillmd.com/api/skills/yyyyyhhhhh0639/sqlite-db-recovery/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: MIT
- Author: yyyyyhhhhh0639 (https://skillmd.com/u/yyyyyhhhhh0639)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/yyyyyhhhhh0639/sqlite-db-recovery

---


# SQLite 数据库损坏恢复 (state.db / 任意运行中库)

## When to Use

- 报错 `database disk image is malformed` / `resume failed` / `No reply: session storage could not be written`
- SQLite 库损坏后需要从损坏备份中抢救数据
- 需要对运行中的 SQLite 库做安全备份或写操作
- quick_check 报 `Rowid out of order` / `btreeInitPage error` / `never used` 页

## 三禁令（绝对不违反——2026-08-04 事故教训）

| 禁令 | 原因 |
|------|------|
| **禁止文件级 `cp` 备份/覆盖运行中的 SQLite 库** | WAL 模式下快照不一致；cp 覆盖主文件会绕过 wal 盐校验 → 主文件与 WAL 不一致 → 后续 checkpoint 损坏（本次事故直接根因） |
| **禁止 `rm` 运行中库的 `-wal`/`-shm` 文件** | gateway 正在使用（Device or resource busy）；删除导致 WAL 数据丢失/索引不一致 |
| **禁止批量脚本逐次全量复制库** | session-move.py 的 `_do_move` 每次 move 都 copy2 整个 state.db（~346MB）——N 个会话 = N 次全量复制与 gateway 并发写，是损坏放大器。批量迁移前先修脚本设计或停 gateway |

## 安全备份（替代 cp）

```python
import sqlite3
cur = sqlite3.connect(r"$HERMES_HOME/state.db", timeout=60)
cur.execute("PRAGMA busy_timeout=60000")
cur.execute(f"VACUUM INTO 'C:/.../state.db.pre-fix-<ts>.vacuum'")  # SQLite 原生备份
```
或用 CLI：`sqlite3 state.db ".backup 'target.db'"`。VACUUM INTO 产物 quick_check 可验证。

## 六步工作流

### 1. 检测与基线
```python
cur.execute("PRAGMA quick_check")  # 'ok' 或错误清单
```
`quick_check` 输出 `Rowid X out of order` / `never used` 页 → 页错位/写中断型损坏（非文件丢失）。

### 2. 定位根因（只读，先查日志再动库）
- Hermes 日志：`$HERMES_HOME\logs\`
  - `errors.log`：写失败/FTS 损坏检测时间窗（`grep -E "2026-08-04 1[67]:" errors.log | grep -iE "sqlite|malform|corrupt"`）
  - `gateway.log` + `gateway-exit-diag.log`：gateway 生命周期（`lifecycle_ledger` 的 `prior_start_time` 可作 **epoch→本地时间换算锚点**）
  - `gateway-restart.log`：重启记录
- 损坏模式判断：`fts5: corruption found reading blob <N> from table "messages_fts_trigram"` → 起点是 FTS 索引 → 传播为整体 malformed

### 3. 从损坏备份抢救数据（核心技巧）
损坏库（如 Pi Agent 的 `malformed-backup-*`）**COUNT 可数但整体 SELECT 报错**——用**逐 rowid 点查**跳过坏页：

```python
mal = sqlite3.connect("file:...malformed?mode=ro", uri=True)
saved = []
for rid in range(94300, 94901):          # 已知目标会话 rowid 区间
    try:
        row = mal.execute("SELECT rowid, <完整列> FROM messages WHERE rowid=?", (rid,)).fetchone()
        if row and row[2] == <session_id>:   # ⚠️ 列索引：rowid,id,session_id,role,content,tool_call_id,...,timestamp(第8)
            saved.append(row)
    except sqlite3.DatabaseError:
        pass                                 # 坏页跳过
```

⚠️ **Hermes messages 表列序**：`id, session_id, role, content, tool_call_id, tool_calls, tool_name, timestamp, ...`——`id` = **rowid 的字符串**（非独立主键语义），会话归属判断必须用 `session_id` 列。`SELECT rowid, <COLS>` 后 `row[2]` 才是 session_id，timestamp 在 `row[8]`。

### 4. 内容判重（决定哪些写回）
**两侧必须用相同截断规则**——一侧 4000 截断、一侧完整内容 → 哈希必然不同 → 全量误判为"缺失"（v4 事故）。正确写法：

```python
def key(role, ts, content):
    return (role, round(ts, 2), hashlib.md5((content or '').encode('utf-8','ignore')).hexdigest())
# c_keys = 当前库 (role, ts, substr(content,1,4000)) 的 key 集合
# m_keys = malformed (role, ts, substr(content,1,4000)) 的 key 集合  ← 截断一致！
to_restore = [r for r in m_rows if key(r[3], r[8], r[4][:4000]) not in c_keys]
```

真重复判定：`(role, ts, content全文 + tool_calls + reasoning)` 哈希相同。**content 为空的 assistant 消息**（只有 tool_calls/reasoning）会因 `role+空content+同毫秒ts` 哈希碰撞误报重复——对比时加 tool_calls/reasoning 长度。

### 5. 写回（rowid 冲突自适应）
```python
max_rowid = cur.execute("SELECT COALESCE(MAX(rowid),0) FROM messages").fetchone()[0]
for row in sorted(to_restore, key=lambda r: r[8]):     # 按 timestamp 保持时序
    max_rowid += 1
    rid = max_rowid                                     # 原 rowid 已被新消息占用时
    cur.execute(f"INSERT INTO messages (rowid, {COLS}) VALUES (?, {placeholders})",
                (rid, str(rid)) + tuple(row[2:]))       # id = str(rid) 与 Hermes 惯例对齐
```
- 原 rowid 空闲可保留原值；被占用（修复后新消息占了区间）→ 新 rowid + id=str(rid)
- 显式 `BEGIN/COMMIT`，失败 ROLLBACK，不重试放大

### 6. 重建索引与验证
- **FTS 由触发器自动维护**：Hermes 有 `messages_fts_insert/delete/update` + `messages_fts_trigram_*` 触发器——写回/删除后 FTS 自动同步，无需手动 rebuild
- 全量重建（幂等安全）：`INSERT INTO messages_fts(messages_fts) VALUES('rebuild')`，trigram 同理
- **行数语义**（新版 schema）：`messages_fts` = 消息总数；`messages_fts_trigram` = **非 tool 消息数**（external content 视图 `messages_fts_trigram_src` 带 `WHERE role <> 'tool'`）——trigram < messages 总数是**正常**，别误判为不完整
- 验证清单：
  ```python
  PRAGMA quick_check                          # ok
  SELECT COUNT(*) FROM messages_fts           # == messages 总数
  SELECT COUNT(*) FROM messages_fts_trigram   # == 非 tool 数
  SELECT COUNT(*) FROM messages WHERE session_id=?  # 恢复后消息数
  # MATCH 搜索实测（写回消息的关键词应能命中）
  ```

## Pitfalls

1. **cp/rm 触碰运行中库** → 见三禁令。备份只用 VACUUM INTO / .backup
2. **判重截断不一致** → 两侧同一截断规则
3. **messages.id 误当会话 id** → id=rowid 字符串；用 session_id 列
4. **COUNT 可用 ≠ 数据可读** → malformed 库必须逐 rowid 点查
5. **trigram 行数少 ≠ 损坏** → 新版 schema 语义（非 tool 行）
6. **清 sessions.git_repo_root 会被 gateway 重写** → gateway 在线时该字段自动维护（common repo root 语义），清字段只在 gateway 停止时必要；live probe 正常时 lane 由 worktree_root 决定
7. **epoch 换算** → 用 gateway-exit-diag.log 的 `prior_start_time` 做锚点，别凭感觉估算
8. **写回后 UI 需刷新**（重新打开会话）才能看到恢复的消息

## Verification Checklist

- [ ] 备份已用 VACUUM INTO（非 cp），产物可 quick_check
- [ ] 根因已从日志定位（有时间窗证据）
- [ ] 判重两侧截断一致；写回无重复（完整哈希断言）
- [ ] quick_check ok；FTS 行数与数据语义一致
- [ ] 搜索 MATCH 实测通过
- [ ] 全程未触碰 SOUL/AGENTS/config