Skip to content

[BUG] Duplicate rows in search_index on the SQLite backend — no uniqueness guard, non-atomic reindex, and the stale-row purge cannot remove duplicates #1771

Closed

Description

@rirkeyou-maker

On the SQLite backend, search_index is an FTS5 virtual table. Unlike the Postgres backend — which has uix_search_index_permalink_project (a UNIQUE index on (permalink, project_id)) — the SQLite table has no unique constraint and no secondary indexes at all, so nothing at the database level prevents duplicate rows.

The index update path is also non-atomic across transactions: SearchService.index_entity() (background task on every note write) first calls repository.delete_by_entity_id() in one committed session, then bulk_index_items() (plain INSERTs) in another. Multiple independent processes write the same database concurrently — every MCP server instance does on-demand indexing, plus the file watcher. When two writers interleave (A deletes → B deletes → A inserts → B inserts), both inserts survive as duplicate rows.

Observed in practice: the same (project_id, 'entity', id) appearing 2–3 times, byte-identical content, adjacent rowids, timestamps within 1 ms; the visible count for a single note fluctuating between 1/2/3 across consecutive writes. Once observed, 34 entities across all projects were affected simultaneously.

Additionally, purge_stale_search_index_rows() cannot fix this: its three DELETE statements only remove rows whose backing entity/observation/relation row no longer exists. Duplicate rows have a live backing row, so they survive — a reindex reports 0 stale search rows purged while duplicates demonstrably exist. There is also no dedupe-only maintenance mode, and reindex (the documented remedy for index issues) does not dedupe either.

Impact

  • recent_activity / search results list the same note N times; counts are inflated.
  • The 0 stale search rows purged output actively misleads diagnosis while duplicates exist.
  • Users who try the documented remedy (reindex --full) cannot clean the duplicates and, per the separate created_at issue, lose creation timestamps as a side effect.

Steps to reproduce

Intermittent (timing-dependent), so reproducible only probabilistically:

  1. Run several MCP server instances against the same basic-memory home (one per client session, which is the normal multi-client setup).
  2. Write/update the same note from two clients near-simultaneously (or write while the file watcher processes the same file).
  3. Inspect: SELECT project_id, type, id, count(*) FROM search_index WHERE type='entity' GROUP BY project_id, type, id HAVING count(*) > 1;

Actual

Duplicate, byte-identical rows accumulate silently; no maintenance command removes them.

Expected

A re-index of an entity replaces its previous index rows exactly ("last write wins"), or at minimum a maintenance command exists that deduplicates.

Versions

basic-memory 0.23.2, Windows 11, SQLite backend.

Activity

  1. rirkeyou-maker commented on Oct 17, 2026

    @rirkeyou-maker
    Author

    Cross-referencing two reports from the same installation: #1772 and #1773.

    They are separate root causes (different code paths, separate fixes), but together they form a user-facing chain:

    1. this issue produces duplicate search rows →
    2. the documented remedy for index problems, reindex --full, is what triggers the created_at overwrite in [BUG] reindex --full overwrites entity.created_at with the reindex timestamp for notes without a frontmatter created:` — irreversible #1772 — so users hit that bug while trying to repair the symptoms of this one →
    3. [BUG] bm status and bm doctor` don't surface index inconsistencies (duplicate rows, orphans, inverted timestamps) #1773 explains why neither problem is visible through bm status / bm doctor, so both go unnoticed until the duplicated search hits become obvious.
      Noting the chain here since it affects triage: on its own, this issue can look like a minor display-level annoyance.
  2. dolly-saas-studio commented on Oct 20, 2026

    @dolly-saas-studio

    Reproduced SQLite search-index duplication and positive atomic-refresh comparison

    Using normal v2 API publications, we reproduced duplicate FTS rows for identical (project_id, type, id) with Basic Memory 0.23.2 on SQLite. No duplicate rows were inserted by the test and no runtime source was modified.

    The test uses only two synthetic notes. The target has one observation and seven outgoing relations: one resolvable reference and six unresolved references. The neighbor links back to the target. The real API application lifespan, watcher and relation follow-ups run normally, in dev mode.

    Semantic search stays enabled with local FastEmbed sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2, 384 dimensions, two embedding threads and existing model weights used offline. Both runs use Python 3.14.7, SQLAlchemy 2.1.5, aiosqlite 0.22.1 and the same publication workload.

    Workload and observations

    Per runtime: two initial POSTs, two sequential mutations, three simultaneous PATCH/PUT pairs and one projects GET. SQL statement/commit events and independent read-only snapshots are recorded. Instrumentation is passive; there are no timing barriers or injected SQL failures.

    Observation Released 0.23.2 Official snapshot at PR #1623 merge commit
    HTTP requests 11 11
    Accepted writes, HTTP 202 10 9
    Explicit concurrency conflict, HTTP 409 0 1
    HTTP 5xx 0 0
    Maximum extra same-identity FTS projections observed 9 0
    Extra projections after native shutdown/drain 0 0
    Multirow FTS inserts sharing their owner's DELETE connection with no intervening commit 0 / 79 76 / 76

    Both runs finish with the latest successfully accepted API response matching note_content and the materialized file exactly, including version and SHA-256. Entity, observation and relation identities match the final FTS projection. The seven target relations, including six pending references, remain present. Real finite, nonzero 384-dimensional embeddings are persisted in both runs. Embedding layouts/counts differ between snapshots, so this is not evidence of semantic-result equivalence.

    The released runtime also shows duplication during a sequential PUT with internally scheduled relation refreshes. Multiple external clients are therefore not required. A final-only count would miss the reproduced transient duplicates.

    Candidate provenance and limits

    The tested official snapshot is commit 9027203e4feb39f0b003af1d438ba6e15868fc02, the merge commit of #1623.[3] Its archive SHA-256 is 5f16a20dcf8a903c8d1524471755cee9f57ffbc8a771c836a8604c0ced6870fc. All 448 installed Python source files that correspond to the extracted official package source match it byte-for-byte.

    The source module still reports __version__ == 0.23.2; the archive build's distribution metadata reports 0.0.0. We identify the tested code by commit and source hashes, not those version strings. This snapshot requires FastMCP/FastMCP-slim 4.0.3 instead of 4.0.0b1. No dependencies were forced with --no-deps.

    This is a bounded, positive comparative result for the full official snapshot. It is not an isolated cherry-pick experiment, universal concurrency proof, migration qualification, MCP-client qualification or production deployment. The candidate had one explicit HTTP 409, and only successfully accepted generations were treated as committed content.

    Request

    Could you provide or recommend a maintained backport of the atomic search-row replacement for the 0.23.x line, or an officially supported artifact that avoids the broader schema/index migration?

    The tested snapshot's migration path includes d2e3f4a5b6c7, which rebuilds the entire SQLite FTS table using INSERT ... SELECT ... FROM search_index.[4] A full-index migration has a wider operational scope than the targeted refresh fix. No such migration has been run against production.

    We can supply the synthetic HTTP workload and transaction/snapshot evidence for a regression test. No private corpus or real-note content is needed.

    Sources

    [3] #1623 — fix(core): replace an entity's search rows in one transaction
    [4] https://raw.githubusercontent.com/basicmachines-co/basic-memory/9027203e4feb39f0b003af1d438ba6e15868fc02/src/basic_memory/alembic/versions/d2e3f4a5b6c7_add_script_ngrams_to_full_text_search.py — Official SQLite FTS migration at the tested immutable commit

  3. Tarso-Vitorino commented on Nov 7, 2026

    @Tarso-Vitorino

    Same root cause, opposite symptom: entities left with no search row when a write hits database is locked

    Adding data from another Windows 11 / SQLite / 0.23.2 install, single project of ~11.9k notes, one long-running mcp --transport streamable-http server plus occasional CLI reindex runs.

    Where this issue shows duplicate rows from interleaved writers, we see the other face of the same non-atomic, multi-writer path: 210 entities (~1.8%) ended up with an entity row but no search_index row at all (no entity, observation or relation rows).

    • 113 of them were (re)created during a reindex --full --search that ran while notes were still being written through the server.
    • 96 of 369 notes created during a one-hour window of heavy writes, with a CLI command running alongside the server, were left the same way.
    • The server logs for those windows contain dozens of sqlite3.OperationalError: database is locked tracebacks (busy_timeout is 10 s). We infer, but did not trace note by note, that these are the failed index writes.

    Why it sticks: in BatchIndexer the per-path failure is caught, logged ("Batch indexing failed") and skipped, but the entity row and its checksum are already committed. An incremental reindex --search therefore sees the file as unchanged and never revisits it. Only a full search rebuild (~4 h here) brings these notes back, and they never get vectors in the meantime.

    #1954 (1.0.0) repairs entities missing search rows after the fact, which addresses the visibility part (#1826). The prevention side still seems open, which is why I'm adding it here:

    1. Retry with backoff on SQLITE_BUSY before giving up on a path, or keep the entity marked as pending until its search rows are committed, so the next incremental pass picks it up.
    2. Single-writer coordination: let a CLI reindex hand the work to the running server, or wait for it, instead of opening the database as a second writer.

    Not yet re-tested on 1.0.x.

  4. phernandez commented on Nov 12, 2026

    @phernandez
    Member

    Fixed, in two parts.

    • No new duplicates: fix(core): replace an entity's search rows in one transaction #1623 (v0.24.0) writes an entity's search rows in one transaction: the delete and the inserts commit together. Two writers can no longer interleave their deletes and inserts, so a refresh can't leave a second copy.
    • Existing duplicates: bm doctor (v1.0.0) reports them as duplicated indexed items and suggests bm reindex --search --full --project <name>. A full reindex rewrites every note's search rows, which removes the extra copies. Indexing a note again also replaces all of its rows, so a duplicate clears the next time its note is written.

    The missing-row case from the comments is also covered: an index pass now finds entities with no search row and rebuilds them (#1826), and doctor reports them.

    Reopen if you still see duplicates on 1.0 after a full reindex.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions