Skip to content

[BUG] bm_write (MCP) fails with "An illegal WHERE constraint was provided on a vec0 auxiliary column in a KNN query" when semantic search is disabled #2065

Closed

Description

@terry197913

Bug

bm_write (MCP tool) fails with a sqlite-vec KNN query error when semantic search is disabled. The write path appears to hit the vector embeddings virtual table unconditionally, even though semantic_search_enabled is false in the config.

The same write succeeds when the markdown file is written directly to disk and the watcher indexes it, so the note content itself is fine — the failure is specific to the bm_write code path.

Environment

  • basic-memory: 1.0.0 (upgraded from 0.23.2)
  • Python: 3.12.3, Linux (Ubuntu 24.04, x86_64)
  • Transport: MCP streamable-http (basic-memory mcp --project memory-hall --transport streamable-http --host 127.0.0.1 --port 8081)
  • database_backend: sqlite, semantic_search_enabled: false, single-project constrained MCP
  • Project name matches its permalink (memory-hall), three-layer names unified

Reproduction

Call the MCP tool bm_write with project: "memory-hall" and any valid title/content/directory. The call returns:

Error calling tool 'bm_write': (sqlite3.OperationalError) An illegal WHERE constraint was provided on a vec0 auxiliary column in a KNN query.
[SQL: WITH vector_matches AS MATERIALIZED ( SELECT rowid, distance, source_hash FROM search_vector_embeddings  WHERE project_id IN (?)  AND embedding MATCH ? AND k = ? AND rowid IN (SELECT c.id FROM search_vector_chunks c WHERE c.project_id IN (?) AND c.vector_index = 'sqlite-vec' AND c.embedding_status = 'ready' AND c.embedding_model = ? AND c.source_type IN (?) AND (c.project_id, c.source_type, c.source_row_id) IN (SELECT search_index.project_id, search_index.type, search_index.id FROM search_index JOIN entity ON search_index.entity_id = entity.id WHERE search_index.project_id IN (?) AND entity.content_type = ? AND json_extract(entity.entity_metadata, '$.tags') IS NOT NULL ...

(The SQL is truncated at the tool boundary; the full statement continues with more json_extract conditions on entity.entity_metadata.)

Observations

  1. sqlite-vec is not loadable in this environment: bm reindex --embeddings reports the vec0 extension cannot be loaded (related to the closed issues [BUG] Bug: no such module: vec0 when running bm reindex --embeddings #829 / vec0 module not loaded in worker connections (Windows) — embeddings reindex fails with "no such module: vec0" #767 / [BUG] bm reindex --embeddings fails with "no such module: vec0" on Windows #735), and bm reindex prints Semantic search is not enabled.
  2. Despite semantic_search_enabled: false, the bm_write path still executes a KNN MATCH query against search_vector_embeddings (a vec0 virtual table) — which raises the "illegal WHERE constraint" error instead of skipping vector search.
  3. Writing the same markdown directly to the project directory works: the file watcher indexes it within seconds and bm_search finds it. So the failure is isolated to the bm_write MCP tool.

Expected behavior

bm_write should skip vector/embedding lookups when semantic_search_enabled is false (or when the sqlite-vec extension is unavailable), and complete the note write successfully — same as direct-on-disk writes.

Activity

  1. terry197913 commented on Nov 6, 2026

    @terry197913
    Author

    Additional reproduction: ARM64 + streamable-http, and two extra data points

    Reproduced on a second environment — same error, same code path.

    Environment

    • basic-memory: 1.0.0 (upgraded from 0.23.2)
    • Platform: Linux aarch64 (Oracle Cloud Ampere A1, Ubuntu 24.04)
    • Python: 3.12.x (dedicated venv), transport: MCP streamable-http (basic-memory mcp --project memory-hall --transport streamable-http --host 127.0.0.1 --port 8081)
    • database_backend: sqlite, semantic_search_enabled: false, single-project constrained server

    Result

    Calling the MCP tool bm_write fails with the identical error:

    (sqlite3.OperationalError) An illegal WHERE constraint was provided on a vec0 auxiliary column in a KNN query.
    [SQL: WITH vector_matches AS MATERIALIZED ( SELECT rowid, distance, source_hash FROM search_vector_embeddings WHERE project_id IN (?) AND embedding MATCH ? AND k = ? ...)]
    

    The failing statement runs inside bm_write itself (the KNN similarity/dedup query against search_vector_embeddings), confirming the write path enters the vector index unconditionally even with semantic search disabled.

    Two extra observations

    1. The note file is still written to disk when this error fires. Content lands correctly with frontmatter, but the caller gets an MCP error — so the operation is partially successful from the filesystem's perspective and failed from the protocol's perspective. Callers that retry on error can end up with duplicated notes via other paths.
    2. The disk-write + watcher path is a clean workaround. Writing the exact same markdown directly into the project directory gets indexed by the file watcher with no vector error, and the note is immediately searchable via bm_search. Reads (bm_read) and searches work fine throughout — only the MCP bm_write path is affected.

    Happy to test a fix — the environment is live and the reproduction is one tool call away.

  2. added this to the nextup milestone on Nov 12, 2026
  3. phernandez commented on Nov 12, 2026

    @phernandez
    Member

    Thanks for the two detailed reports. Status:

    I can't reproduce the error itself. These all pass on 1.0.0:

    • a fresh install on Ubuntu 24.04, system Python 3.12, SQLite 3.45.1
    • an upgrade from 0.23.2 that already had vectors (1.0.0 converts the storage to the per-project layout)
    • sqlite-vec 0.1.6, 0.1.7, 0.1.8 and 0.1.9

    One detail in your SQL stands out. It reads tags with `json_extract(entity.entity_metadata, '$.tags')`, which Basic Memory only does when the `entity.tags_json` column is missing. That suggests the database schema is behind 1.0.0. Could you run these against the database the MCP server uses (`~/.basic-memory/memory.db` unless you've moved it) and paste the output?

    sqlite3 ~/.basic-memory/memory.db "select sql from sqlite_master where name = 'search_vector_embeddings'"
    sqlite3 ~/.basic-memory/memory.db "select version_num from alembic_version"
    sqlite3 ~/.basic-memory/memory.db "pragma table_info(entity)" | grep -E "tags_json|frontmatter_"
    python -c "import sqlite3, sqlite_vec; print(sqlite3.sqlite_version, sqlite_vec.__version__)"
    

    Run the last line with the same Python the MCP server runs under. You mention `bm reindex --embeddings` couldn't load sqlite-vec, but the MCP server clearly did, so they may be running different installs.

  4. terry197913 commented on Nov 12, 2026

    @terry197913
    Author

    Diagnostics as requested — all four run against the DB the MCP server uses (~/.basic-memory/memory.db, project MemoryHall), with the same interpreter the service runs under (basic-memory-venv, Python 3.12):

    1. search_vector_embeddings schema

    CREATE VIRTUAL TABLE search_vector_embeddings
    USING vec0(
        project_id integer partition key,
        embedding float[384],
        +source_hash text
    )

    2. alembic_version

    0042
    

    3. pragma table_info(entity), filtered for tags_json / frontmatter_

    (no rows — entity.tags_json and frontmatter_* columns are absent)
    

    4. sqlite / sqlite-vec (MCP venv)

    sqlite: 3.37.2 | sqlite_vec: 0.1.9
    

    basic-memory 1.0.0.

    Two findings that probably explain why you can't reproduce:

    1. entity.tags_json is missing on our DB — so the reported SQL is indeed the json_extract(entity.entity_metadata, '$.tags') fallback branch, as you suspected.

    2. Our DB looks stamped past its migrations rather than migrated through them. alembic_version = 0042, which is the package head (no migration in alembic/versions/ declares down_revision = "0042"), and 0042's ancestry includes d7e8f9a0b1c2_add_structured_metadata_indexes — the revision that adds entity.tags_json (guarded by column_exists). Since the column is absent, that revision can't have actually executed here. It looks like our earlier 0.23.x → 1.0.0 upgrade stamped alembic_version to head without applying the intermediate schema changes. The vec0 tables (search_vector_embeddings with the new partition key layout, search_vector_chunks) do exist, which fits a partially-applied / stamped state.

    On the bm reindex --embeddings sqlite-vec import failure: both bm and basic-memory entry points share the same venv interpreter and import sqlite_vec succeeds there (0.1.9), so that earlier failure most likely ran under a different interpreter on our side. We'll re-test and follow up if it still fails.

    Question: what's the supported way to repair a DB in this stamped state — is there a bm command that verifies/completes the schema (e.g. bm db upgrade), or should we re-run d7e8f9a0b1c2 manually / recreate the project? Happy to test a fix build as well.

  5. phernandez commented on Nov 12, 2026

    @phernandez
    Member

    Thanks, that pinned it down. The cause is the SQLite version, not the schema.

    • Your schema is fine. PRAGMA table_info never lists generated columns, and tags_json and frontmatter_* are generated columns. My diagnostic was wrong. pragma table_xinfo(entity) will show them. Basic Memory's own check had the same flaw, which is why your error SQL used the json_extract fallback.
    • The error comes from SQLite 3.37.2. SQLite before 3.38 pushes a join condition on source_hash into the sqlite-vec scan, and sqlite-vec rejects that for an auxiliary column. I reproduced your exact error on SQLite 3.37.2 with sqlite-vec 0.1.9. The same query works on 3.45 and later.

    The fix is in #2107. Until it ships, a Python that bundles a newer SQLite avoids the error. For example, uv's managed Python: uv tool install basic-memory --python 3.12 --managed-python. To check which SQLite a Python uses: python -c "import sqlite3; print(sqlite3.sqlite_version)".

    No repair or stamping is needed. Your database at 0042 is on the correct schema.

  6. added a commit that references this issue on Nov 13, 2026
    1cc3c27
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

    questionFurther information is requested

    Projects

    No projects

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions