Concurrent MCP server instances quarantine each other's healthy databases

Version: codebase-memory-mcp 0.9.0 (official release, Linux x86_64)

Impact: A healthy index database is renamed to .corrupt and rebuilt from
scratch. The replacement is often left sparse (a few dozen nodes instead of
thousands) and stays that way, because index_repository --mode full cannot
repair it — only delete_project + reindex can.

Patch included below; verified against the reproduction.

Summary

When two or more MCP server instances have the same project as their session
root, a file change makes all of them re-index at once. Whichever instance loses
the race treats a transient SQLite failure as corruption, renames the database to
<project>.db.corrupt, and starts a fresh one — even though the database it
discarded was perfectly healthy.

This is not disk corruption. PRAGMA integrity_check returns ok on the
quarantined files.

Reproduction

watcher-repro.sh (attached) — bash watcher-repro.sh <N>

Creates a throwaway git repo with 20 PHP files, indexes it, starts N MCP server
instances with that repo as cwd (each completing an initialize handshake so the
watcher binds), then rewrites the 20 files three times.

Reproduced reliably. The only variable is the number of instances.

Evidence

1. The quarantined file in the reproduction is an empty database.

$ stat -c%s tmp-wtest.db.corrupt
4096                                   # one SQLite header page
$ sqlite3 tmp-wtest.db.corrupt "SELECT count(*) FROM sqlite_master;"
0                                      # no tables at all
$ sqlite3 tmp-wtest.db.corrupt "PRAGMA integrity_check;"
ok

A database another instance was still creating got opened, found to have no
projects table, and quarantined.

2. In production, the quarantined file was a fully populated database.

A different manifestation on a real project (~2900 nodes, 7 instances):

$ sqlite3 var-www-lk.db.corrupt "PRAGMA integrity_check;"
ok
$ sqlite3 var-www-lk.db.corrupt \
    "SELECT (SELECT count(*) FROM nodes), (SELECT count(*) FROM projects), root_path FROM projects;"
2956|1|/var/www/lk

Exactly one projects row, correct root_path, intact data — quarantined
anyway. The replacement rebuilt to 2961 nodes, so nothing was wrong with the
discarded one.

3. Every file quarantined in production had a valid projects table.

Instrumented the daily maintenance job to dump the table before deleting the
backups. Three quarantines on one machine in a single day:

var-www-callhub      projects: 1 row  root_path=/var/www/callhub
var-www-html-salary  projects: 1 row  root_path=/var/www/html/salary
var-www-lk           projects: 1 row  root_path=/var/www/lk

One row, correct path, in all three. None of them was corrupt.

4. Real-world correlation is exact.

Across 42 indexed projects on this host, mapping each running instance's cwd:

No project with a single instance has ever been quarantined; every project with
more than one has.

Why multiple instances are easy to end up with

Every editor/agent session that mounts the server spawns its own instance, and
each one starts a watcher on its session root:

level=info msg=watcher.start interval_ms=multi-sec
level=info msg=watcher.watch project=var-www-lk path=/var/www/lk

Two terminals open on the same repository is enough. On this host the count
reached 7 for one project across concurrent sessions.

Root cause

cbm_store_check_integrity() in src/store/store.c treats any SQLite error
as corruption:

int rc = sqlite3_prepare_v2(s->db, "SELECT count(*) FROM projects;",
                            CBM_NOT_FOUND, &stmt, NULL);
if (rc != SQLITE_OK) {
    return false;          /* caller quarantines the DB */
}

resolve_store() in src/mcp/mcp.c responds to false by renaming the file to
.corrupt and rebuilding. Two very common failures here are not corruption:

• SQLITE_ERROR — "no such table: projects". Another instance is still
  creating this database and has not committed the schema yet. Quarantining here
  destroys the database that instance is building. This produces the 4 KB,
  zero-table .corrupt in Evidence 1.
• SQLITE_BUSY / SQLITE_LOCKED. Another instance holds a write lock. The
  database is fine; it just could not be read at that moment. This produces the
  healthy 2956-node .corrupt in Evidence 2.

Both become likely as soon as more than one instance watches a project, because a
single file change makes all of them re-index simultaneously.

cbm_store_check_integrity_deep() has the same problem on its quick_check path.

Fix

Report corruption only for result codes that actually mean the file is
unreadable, and let transient failures pass:

/* Compare the primary result code so extended codes (SQLITE_CORRUPT_VTAB, …)
 * still match. */
static bool cbm_store_rc_is_corrupt(int rc) {
    int primary = rc & 0xff;
    return primary == SQLITE_CORRUPT || primary == SQLITE_NOTADB;
}

then use it at every rc != SQLITE_OK / non-SQLITE_ROW branch in both
integrity functions instead of returning false unconditionally.

Patch attached: fix-transient-not-corrupt.patch (42 insertions, 6 deletions,
src/store/store.c only).

Running the patched build here since 2026-07-22. The three projects that were
quarantining daily have stopped.

Two related issues worth considering separately

1. A quarantined project is silently left degraded. After the rename, a fresh
   database is created and only changed files get indexed into it, so the
   project can sit at a few dozen nodes while still appearing normal in
   list_projects. Observed: 13 nodes instead of ~2700 for several hours.
   index_repository --mode full does not fix it — change detection sees no
   modified files and does nothing:

   # after emptying the nodes table
   $ codebase-memory-mcp cli index_repository --repo-path $T --mode full
   "nodes":0                              # unchanged
   $ codebase-memory-mcp cli delete_project --project $P
   $ codebase-memory-mcp cli index_repository --repo-path $T --mode full
   "nodes":14                             # only delete_project first recovers it
   

   Forcing a full reindex after a quarantine would close this.

2. Re-indexing is not serialised across instances. The patch stops the
   destructive misclassification, but N instances still redo the same work on
   every file change. An advisory lock on the database file would remove the
   duplicated effort as well as the contention.

diff --git a/src/store/store.c b/src/store/store.c
index 7b96605..ed00f3f 100644
--- a/src/store/store.c
+++ b/src/store/store.c
@@ -861,6 +861,17 @@ cbm_store_t *cbm_store_open_path_query(const char *db_path) {
 
 /* ── Integrity check ───────────────────────────────────────────── */
 
+/* Does this SQLite result code mean the file itself is unreadable?
+ *
+ * Anything else (SQLITE_BUSY, SQLITE_LOCKED, "no such table" while another
+ * process is still creating the schema) is transient and must NOT be reported
+ * as corruption — the caller renames the DB away on a false positive. Compare
+ * the primary result code so extended codes (SQLITE_CORRUPT_VTAB etc.) match. */
+static bool cbm_store_rc_is_corrupt(int rc) {
+    int primary = rc & 0xff;
+    return primary == SQLITE_CORRUPT || primary == SQLITE_NOTADB;
+}
+
 /* Deep integrity: the shallow check above only sanity-checks the projects
  * table, so page-level corruption (torn artifacts, #895) sails through.
  * quick_check(1) walks the btrees and stops at the first error. Used on
@@ -870,17 +881,21 @@ bool cbm_store_check_integrity_deep(cbm_store_t *s) {
         return false;
     }
     sqlite3_stmt *stmt = NULL;
-    if (sqlite3_prepare_v2(s->db, "PRAGMA quick_check(1);", CBM_NOT_FOUND, &stmt, NULL) !=
-        SQLITE_OK) {
-        return false;
+    int rc = sqlite3_prepare_v2(s->db, "PRAGMA quick_check(1);", CBM_NOT_FOUND, &stmt, NULL);
+    if (rc != SQLITE_OK) {
+        /* Same reasoning as the shallow check: a lock we lost is not corruption. */
+        return cbm_store_rc_is_corrupt(rc) ? false : true;
     }
     bool ok = false;
-    if (sqlite3_step(stmt) == SQLITE_ROW) {
+    int step_rc = sqlite3_step(stmt);
+    if (step_rc == SQLITE_ROW) {
         const char *res = (const char *)sqlite3_column_text(stmt, 0);
         ok = res && strcmp(res, "ok") == 0;
         if (!ok) {
             (void)fprintf(stderr, "ERROR store.corrupt quick_check=%s\n", res ? res : "(null)");
         }
+    } else {
+        ok = !cbm_store_rc_is_corrupt(step_rc);
     }
     sqlite3_finalize(stmt);
     return ok;
@@ -898,17 +913,38 @@ bool cbm_store_check_integrity(cbm_store_t *s) {
     int rc =
         sqlite3_prepare_v2(s->db, "SELECT count(*) FROM projects;", CBM_NOT_FOUND, &stmt, NULL);
     if (rc != SQLITE_OK) {
-        return false;
+        /* Only report corruption for errors that actually mean "this file is
+         * unreadable". The caller's response is destructive (rename the DB to
+         * .corrupt and re-index), so a transient failure must not land here.
+         *
+         * Two transient cases show up constantly once more than one server
+         * instance watches the same project — a file change makes them all
+         * re-index at once:
+         *   - "no such table: projects" (SQLITE_ERROR): another instance is
+         *     still creating this DB and hasn't committed the schema yet.
+         *     Quarantining here destroys the DB that instance is building.
+         *   - SQLITE_BUSY / SQLITE_LOCKED: another instance holds a write lock.
+         *     The DB is perfectly healthy; we just could not read it right now.
+         * Both were observed in the wild quarantining good databases. */
+        if (cbm_store_rc_is_corrupt(rc)) {
+            (void)fprintf(stderr, "ERROR store.corrupt table=projects prepare_rc=%d\n", rc);
+            return false;
+        }
+        return true;
     }
 
     bool ok = true;
-    if (sqlite3_step(stmt) == SQLITE_ROW) {
+    int step_rc = sqlite3_step(stmt);
+    if (step_rc == SQLITE_ROW) {
         int row_count = sqlite3_column_int(stmt, 0);
         if (row_count > ST_MAX_ROW_CHECK) {
             (void)fprintf(stderr, "ERROR store.corrupt table=projects rows=%d (expected 1)\n",
                           row_count);
             ok = false;
         }
+    } else if (cbm_store_rc_is_corrupt(step_rc)) {
+        (void)fprintf(stderr, "ERROR store.corrupt table=projects step_rc=%d\n", step_rc);
+        ok = false;
     }
     sqlite3_finalize(stmt);
 

#!/usr/bin/env bash
# 復現假設：多個 MCP server 同時監看同一專案，檔案變動時各自觸發重索引 → projects 表出現重複列
set -u
CM=/home/ying/.local/bin/codebase-memory-mcp
T=/tmp/wtest
DB=~/.cache/codebase-memory-mcp/tmp-wtest.db
N=${1:-5}
RUN=/tmp/wrepro; rm -rf $RUN; mkdir -p $RUN

INIT='{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"t","version":"1"}}}'

cd "$T" || exit 1
for i in $(seq 1 "$N"); do
  mkfifo $RUN/in$i
  $CM < $RUN/in$i > $RUN/out$i 2> $RUN/err$i &
  echo $! >> $RUN/pids
  exec {fd}> $RUN/in$i        # 保持寫入端開著，server 才不會看到 EOF 退出
  eval "FD$i=$fd"
  echo "$INIT" >&$fd
done

sleep 6
echo "--- 啟動 $N 個 server，watcher 綁定狀況 ---"
grep -h "watcher.watch\|session.root.cwd" $RUN/err* 2>/dev/null | sort | uniq -c | sed 's/^/  /'

echo "--- 檢查 DB（變更前）---"
sqlite3 "$DB" "SELECT '  projects='||count(*)||' nodes='||(SELECT count(*) FROM nodes) FROM projects;" 2>&1

echo "--- 連續改檔觸發 watcher（3 輪）---"
for round in 1 2 3; do
  for i in $(seq 1 20); do
    echo "<?php class W$i { function a(){return $i;} function b(){return $((i*round));} function r$round(){return 1;} }" > "$T/f$i.php"
  done
  echo "  第 $round 輪已改 20 檔"
  sleep 12
  sqlite3 "$DB" "SELECT '    projects='||count(*)||' root='||coalesce(group_concat(root_path,'|'),'?') FROM projects;" 2>&1
  ls ~/.cache/codebase-memory-mcp/tmp-wtest.db.corrupt 2>/dev/null && echo "    *** 出現 .corrupt ***"
done

echo "--- 收尾：關閉 server ---"
for i in $(seq 1 "$N"); do eval "exec {FD$i}>&-"; done
sleep 2
for p in $(cat $RUN/pids 2>/dev/null); do kill -TERM "$p" 2>/dev/null; done
sleep 2
echo "--- 最終狀態 ---"
sqlite3 "$DB" "SELECT '  projects='||count(*)||' nodes='||(SELECT count(*) FROM nodes) FROM projects;" 2>&1
ls ~/.cache/codebase-memory-mcp/tmp-wtest* 2>/dev/null | sed 's/^/  /'
echo "--- server stderr 中的異常 ---"
grep -hiE "corrupt|error|malformed|quarantin" $RUN/err* 2>/dev/null | sort -u | head -10
echo "  (空=無異常)"