Skip to content

Online Updates of Vector Index

This document describes how to create and configure online updates for MyVector indexes—the real-time, automatic synchronization of vector indexes when data changes (INSERT, UPDATE, DELETE) in the base table.

Overview

Online updates mean that when you add, modify, or delete rows in a table with a vector column, the corresponding vector index is updated automatically. Without online updates, you must call myvector_index_build or myvector_index_refresh manually (or via triggers) to keep the index in sync.

MyVector implements online updates by monitoring the MySQL binary log (binlog). A background thread reads row-based binlog events and applies INSERT/UPDATE/DELETE changes to registered vector indexes.

Prerequisites

1. Row-Based Binary Logging

Online updates require row-based binlog format. The plugin parses row events to extract vector and primary key values.

-- Check current format
SHOW VARIABLES LIKE 'binlog_format';

-- Set row format (add to my.cnf or run at runtime)
SET GLOBAL binlog_format = 'ROW';

For MySQL 8.0.20+, you may need to set binlog_row_metadata for full column metadata. See your MySQL version's documentation.

2. Binary Logging Enabled

SHOW VARIABLES LIKE 'log_bin';
-- Should be ON

3. User Privileges for Binlog Connection

The plugin opens a separate MySQL connection to read the binlog. This connection requires:

  • REPLICATION CLIENT (or REPLICATION SLAVE on older MySQL) — to read binlog events
  • SELECT on the base table — to read vector data when building/rebuilding the index

Note: Root access is not mandatory. A dedicated user with these privileges is sufficient (see GitHub issue #81).

Example user setup:

CREATE USER 'myvector_binlog'@'localhost' IDENTIFIED BY 'your_password';

-- For reading binlogs
GRANT REPLICATION CLIENT ON *.* TO 'myvector_binlog'@'localhost';

-- For building/refreshing indexes (read base table)
GRANT SELECT ON your_database.* TO 'myvector_binlog'@'localhost';

FLUSH PRIVILEGES;

4. Configuration File

The binlog thread uses a config file for connection parameters. Set the path via the myvector_config_file system variable (default: myvector.cnf).

Security: The config file contains credentials. On Unix, it must be mode 0600 or 0400 and owned by the MySQL process user. The plugin refuses to load files with insecure permissions. See CONFIGURATION.md for full details.

Create a config file (e.g. /etc/myvector.cnf or myvector.cnf in the data directory) with connection options in key=value format:

myvector_user_id=myvector_binlog
myvector_user_password=your_password
myvector_host=127.0.0.1
myvector_port=3306
myvector_socket=/tmp/mysql.sock

For local socket connection, you can use:

myvector_user_id=myvector_binlog
myvector_user_password=your_password
myvector_socket=/tmp/mysql.sock

Then point the plugin to it:

SET GLOBAL myvector_config_file = '/path/to/myvector.cnf';

Or in my.cnf:

[mysqld]
myvector_config_file=/path/to/myvector.cnf

5. Feature Level

The binlog thread is disabled when myvector_feature_level is 1. Ensure it is 0 or 2+ (default is 2):

SHOW VARIABLES LIKE 'myvector_feature_level';
-- Default: 2 (binlog enabled)

Creating an Online-Updated Index

Step 1: Define the Column with online=Y and idcol

Add online=Y and idcol=<primary_key_column> to the MYVECTOR column options. The idcol tells the binlog processor which column is the primary key for mapping rows to index entries.

Example: VARBINARY column (MySQL 8.0)

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  embedding VARBINARY(3072) COMMENT 'MYVECTOR(type=HNSW,dim=768,size=100000,online=Y,idcol=id,dist=L2)'
);

Example: VECTOR column (MySQL 8.4+)

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  embedding VECTOR(768) COMMENT 'MYVECTOR(type=HNSW,dim=768,size=100000,online=Y,idcol=id,dist=L2)'
);

Step 2: Build the Initial Index

Build the index once before relying on online updates:

CALL mysql.myvector_index_build('your_database.products.embedding', 'id');

Step 3: Verify Online Registration

On plugin startup, MyVector discovers all columns with online=Y from the myvector_columns view and registers them for binlog updates. The view is created by the plugin installation script (sql/myvectorplugin.sql) in the mysql database. Ensure the installation script has been run so that mysql.myvector_columns exists.

How It Works

  1. Plugin init: A background thread starts and connects to MySQL using the config file credentials.
  2. Discovery: The thread queries myvector_columns (or equivalent) for columns with online=Y and idcol set.
  3. Binlog stream: The thread opens a binlog stream and processes row events.

  4. Index updates: For each event affecting a registered table, the listener adds, updates, or removes the corresponding vector entry in memory. A row whose vector is set to NULL is removed.

    Requirements

    Plugin and component builds apply INSERT, UPDATE and DELETE (#188, #194). Releases before these fixes applied only INSERTs: on them, rebuild the index after an UPDATE or DELETE. Online updates expect the default binlog_row_image=FULL and an integer key column (TINYINT to BIGINT, signed or unsigned; before #204 only INT). Row events the listener cannot apply (another key type, a row image that is not FULL, a column type it cannot read) are skipped. Each table and reason is logged as a warning once, and again after the index is rebuilt or reloaded, and myvector_index_status counts them, see Troubleshooting.

  5. Checkpointing: Progress is tracked via binlog file and position so the index can be recovered after restart.

Complete Example

-- 1. Ensure row-based binlog
SET GLOBAL binlog_format = 'ROW';

-- 2. Create table with online=Y
CREATE TABLE embeddings (
  id INT PRIMARY KEY,
  text VARCHAR(512),
  vec VARBINARY(3072) COMMENT 'MYVECTOR(type=HNSW,dim=768,size=100000,online=Y,idcol=id,dist=L2)'
);

-- 3. Build initial index
CALL mysql.myvector_index_build('your_db.embeddings.vec', 'id');

-- 4. Insert data — index updates automatically
INSERT INTO embeddings (id, text, vec) VALUES (1, 'hello', myvector_construct('[0.1, 0.2, ...]'));

-- 5. Update — index updates automatically
UPDATE embeddings SET vec = myvector_construct('[0.2, 0.3, ...]') WHERE id = 1;

-- 6. Delete — index updates automatically
DELETE FROM embeddings WHERE id = 1;

-- 7. Search (works immediately after DML)
SELECT id, text FROM embeddings
WHERE MYVECTOR_IS_ANN('your_db.embeddings.vec', 'id', myvector_construct('[0.1, 0.2, ...]'), 10);

Without Online Updates

If you omit online=Y:

  • The index is not updated automatically when you INSERT/UPDATE/DELETE.
  • You must call myvector_index_build to rebuild, or use myvector_index_refresh with a track= column for incremental refresh.
  • No binlog connection or REPLICATION privileges are required.

Troubleshooting

Symptom Possible cause
Index not updating after DML online=Y or idcol missing in column options; binlog format not ROW; myvector_feature_level=1
myvector_index_status shows Online events skipped : N (...); the server log has Online updates for db.t: skipping row events (...) The listener received row events for the table that it cannot apply, and the reason says why: binlog_row_image is not FULL (set it back to FULL, the default), key column is not an integer (idcol must be an integer column), or a column type it cannot read. The skipped changes are not in the index: fix the cause, then rebuild the index with myvector_index_build. The count resets when the server restarts; the warning is logged once per reason, and again after the index is rebuilt or reloaded
"Binlog thread failed to connect" Wrong credentials in config file; user lacks REPLICATION CLIENT
Plugin: the index stops updating soon after install; the server log shows Binlog fetch failed: with no error text, then Exiting binlog func Plugin builds before the #166 fix: the binlog listener exited when the server was idle for a second. Upgrade the plugin, then rebuild the index with myvector_index_build so it includes the rows changed while the listener was stopped
Index not found after restart Index not saved; ensure myvector_index_save or automatic save runs; check myvector_index_dir
Wrong database for myvector_columns The myvector_columns view is created in mysql by the installation script; ensure it exists and the plugin can query it
Index not loaded at startup; the server log shows Online index db.t.v not loaded: ERROR: unknown index type 'hnws' ... (or missing index type) The column comment's type is misspelled or missing. The column is not registered for online updates and searches fail with "not open". Correct the comment and rebuild (example below)

A misspelled or missing type used to build a brute-force KNN index silently. It is now an error. To fix a column that shows the log line above:

ALTER TABLE products MODIFY embedding VARBINARY(3100)
  COMMENT 'MYVECTOR COLUMN type=HNSW,dim=768,size=100000,online=Y,idcol=id,dist=L2';
CALL mysql.myvector_index_build('db.products.embedding', 'id');
CALL mysql.myvector_index_status('db.products.embedding');   -- Type : HNSW

The rebuild registers the column for online updates again, so rows changed after it finishes are applied to the index. To confirm, insert a row and check that Current Rows in myvector_index_status goes up. If it does not, the binlog listener is probably not running. It connects using the configuration file (see Configuration File), and it reads that file when the plugin or component starts. If the file was added after that, restart the server, or reinstall the component.

Testing with Docker

A test script validates the online updates flow:

./scripts/test-online-updates.sh                    # Use prebuilt ghcr.io image
./scripts/test-online-updates.sh myvector:local     # Use locally built image

To build the Docker image locally (required if the prebuilt image has config issues):

./scripts/build-docker-local.sh 8.4   # ~15–30 min first run
./scripts/test-online-updates.sh myvector:mysql8.4-local

Index build shows "socket ''" with prebuilt image? The published image may not yet include the config parsing fix. The test still validates binlog/online updates. Use a locally built image for full validation: ./scripts/build-docker-local.sh 8.4 then ./scripts/test-online-updates.sh myvector:mysql8.4-local.

CMake "No mysys timer support detected"? Docker on macOS can restrict timer syscalls. The script uses --privileged to work around this. If it still fails, try Colima or a Linux host.

Build fails? On arm64 (Apple Silicon), the script uses Clang and -j1. If it still fails: - Debug: Run with MYVECTOR_VERBOSE=1 ./scripts/build-docker-local.sh 8.4 to see the failing command. - Fallback: Force amd64 (slower, runs under QEMU): MYVECTOR_ARCH=amd64 ./scripts/build-docker-local.sh 8.4 - Prebuilt: Use ./scripts/test-online-updates.sh (no args) with the prebuilt image. - Native builds: See BUILDING_MACOS.md for MySQL/LLVM compatibility on macOS.

For Docker, ensure myvector.cnf has myvector_host=127.0.0.1 and myvector_port=3306 so the index build connection works. See DOCKER_IMAGES.md.