drizzle
C++ MySQL-protocol-compatible database server
Loading...
Searching...
No Matches
WiredTiger Storage Engine — Production Quality Plan

Goal: bring plugin/wiredtiger to production quality, at least on par with plugin/innobase. This plan is the result of a full audit of the plugin (2026-07-10) against the innobase feature surface and the kernel's storage-engine contract (drizzled/plugin/storage_engine.h, transactional_storage_engine.h, drizzled/cursor.h).

Current state: solid prototype. Single-statement DML, PK/secondary reads, and single-session transactions work and the test suite passes. But there are silent wrong-results/data-loss bugs, no durability, and fabricated optimizer statistics.

Built against WiredTiger 3.2.1 (Debian trixie, released 2019, EOL).


Tier 0 — Data loss & wrong results

0.1 No durability

wiredtiger_init (wiredtiger_engine.cc) opens the connection with create,statistics=(all),cache_size=N — no log=(enabled), no checkpoint=(wait=…). Data persists only at checkpoints; the only checkpoints are the manual wiredtiger_checkpoint() UDF and clean shutdown. kill -9 loses every commit since then.

Fix: enable journaling (log=(enabled,recover=on)), configure transaction_sync, add a checkpoint-interval option. Add a kill-and-restart recovery test.

0.2 PRIMARY KEY uniqueness not enforced on INSERT

Data cursors are opened "append,raw" (wiredtiger_cursor.cc:56). WT's overwrite defaults to true, so WT_CURSOR::insert never returns WT_DUPLICATE_KEY — the dup-key branch in doInsertRecord (wiredtiger_cursor.cc:930) is dead code and a duplicate-PK insert silently overwrites the existing row. Tests never insert a duplicate, which is why this hasn't surfaced.

Fix: open write cursors with overwrite=false; handle WT_DUPLICATE_KEY; add dup-insert tests.

0.3 UNIQUE secondary indexes not enforced

WT indexes cannot be unique — only the table key is. Any UNIQUE KEY beyond the one chosen as the WT table key is accepted by doCreateTable and silently unenforced.

Fix: explicit existence probe before insert/update on each unique secondary; or reject such tables at CREATE until implemented.

0.4 UPDATE that changes key columns corrupts data

doUpdateRecord (wiredtiger_cursor.cc:949) packs the key from new_data and calls update: a PK-modifying UPDATE writes the row at the new key and never deletes the old row (duplication), and repositioning the cursor mid-scan risks Halloween-problem row revisits.

Fix: compare old/new key (use old_data); if changed do remove+insert with dup detection, like innobase.

0.5 UPDATE/DELETE via secondary-index scan broken

During doStartIndexScan(idx>0), wt_cursor is a WT index cursor (wiredtiger_cursor.cc:1059), which is read-only. doUpdateRecord/ doDeleteRecord then call update/remove on it, and packRecord packs against the index cursor's key format. Any UPDATE/DELETE whose plan reads a secondary index goes through this path.

Fix: keep a separate table cursor for writes, positioned via the primary key extracted from the index row. Add tests.

0.6 SELECT COUNT(*) can be wrong

HTON_HAS_RECORDS is set (wiredtiger_drizzle.h), so bare COUNT(*) folds to Cursor::records() (drizzled/optimizer/sum.cc:106,177), which defaults to stats.records — fabricated by info(): floored at 2 (wiredtiger_cursor.cc:1397), frozen once past 50k rows (line 1375), sourced from non-transactional btree stats. Empty table can report COUNT(*) = 2.

Fix: drop the flag, or implement an exact transaction-aware records().

0.7 Statement atomicity & savepoints silently violated

doRollback(all=false) is a no-op (wiredtiger_engine.cc:704): a failed statement inside BEGIN leaves partial changes. The savepoint methods (wiredtiger_drizzle.h:134-150) return success while doing nothing, so ROLLBACK TO SAVEPOINT restores nothing. The kernel treats savepoints as mandatory for transactional engines.

Fix (WT has no partial rollback): return not-supported from savepoints; on statement rollback, roll back the whole transaction and report that state (NDB approach). Real statement rollback needs newer WT machinery or write buffering.

0.8 index_read ignores the find flag

The switch (exact) after search_near (wiredtiger_cursor.cc:1245) is empty: HA_READ_KEY_OR_NEXT / OR_PREV / AFTER_KEY / BEFORE_KEY all return whatever row search_near found — range scans can start one row off or include out-of-range rows. Also: exact-match failure returns 0 with STATUS_NOT_FOUND instead of HA_ERR_KEY_NOT_FOUND (line 1241), so the kernel sees success with a stale buffer; prefix lookups on multi-part keys can never satisfy the whole-key exact==0 test; error path returns a raw WT code (line 1233).

Fix: implement the find-flag state machine (position adjust with next/prev based on exact and flag); return proper HA_ERR codes; support prefix comparison. Tests for each find flag.

0.9 NULLs in index lookups pack garbage

wt_pack_from_index_buf (wiredtiger_cursor.cc:1111): the is_null case is a literal FIXME — VARCHAR skips the column, every other type packs whatever bytes are in the key buffer. HTON_NULL_IN_KEY is claimed. WHERE indexed_col IS NULL is wrong today.

Fix: pack the NULL marker consistently with packField's encoding; tests.

0.10 INSERT … ON DUPLICATE KEY UPDATE applies wrong values

extra() maps HA_EXTRA_INSERT_WITH_UPDATE to WT_WRITE_CAN_REPLACE (wiredtiger_cursor.cc:1471). The kernel needs HA_ERR_FOUND_DUPP_KEY back so it can evaluate the UPDATE expressions; engine-side replace stomps the row with the INSERT values. IODKU … SET b=b+1 returns wrong results (existing test masks it: update value equals insert value).

Fix: only honor WRITE_CAN_REPLACE for HA_EXTRA_WRITE_CAN_REPLACE; for IODKU return the dup error. Test with differing values.

0.11 Duplicate-key errors name the wrong key

Cursor::errkey is never set; info(HA_STATUS_ERRKEY) just logs (wiredtiger_cursor.cc:1412). print_keydup_error (kernel, storage_engine.cc:632) uses errkey to build ER_DUP_ENTRY.

Fix: record which key failed at detection point; set errkey in info().

0.12 Auto-increment unsafe across restart/ALTER/explicit inserts

(a) Explicit inserts (id=100) never bump share->auto_increment_value → next auto id collides (and with 0.2, silently destroys the row). Innobase equivalent: innobase_set_max_autoinc. (b) getInitialAutoIncrementValue (wiredtiger_cursor.cc:1780) reads the last row in PK order and assumes the autoinc column leads the PK — wrong for composite keys. (c) info(HA_STATUS_AUTO) hardcodes auto_increment_value=1 (wiredtiger_cursor.cc:1409); ALTER's copy path reads it (alter_table.cc:317) → table rebuild resets the counter. (d) reset_auto_increment is a stub → TRUNCATE doesn't reset.

Fix: bump the share counter on every insert of an explicit value; scan max via the right index; report the real counter in info(); implement reset_auto_increment. Tests incl. restart.


Tier 1 — Robustness

1.1 Release builds turn WT failures into UB

assert(r==0) guards open_session (wiredtiger_engine.cc:57), the dictionary insert in store_table_proto (line 389 — failed proto write silently loses the table definition), and cursor opens in doGetTableIdentifiers/doDoesTableExist. NDEBUG removes them. Also: WT session_max defaults to 100; every Drizzle connection opens a WT session; connection #101 hits the assert.

Fix: real error handling everywhere; set session_max from the connection limit + slack.

1.2 packField/unpackField error paths corrupt Field offset

Errors inside the type switches (wiredtiger_cursor.cc:709,716,723,…, 805+) return without move_field_offset(-row_offset) — shared Field objects point into the wrong record buffer for subsequent rows. Open pack streams also leak on these paths.

Fix: RAII guard or single exit path for offset restore + stream close.

1.3 Error-code mapping holes

doInsertRecord/doUpdateRecord return raw WT codes to the kernel (wiredtiger_cursor.cc:946,963). wt_to_drizzle_error returns -1 (not a valid HA error) for WT_ERROR/default; WT_NOTFOUND→HA_ERR_NO_ACTIVE_RECORD is contextually wrong (should be KEY_NOT_FOUND/END_OF_FILE by call site); EBUSY from drop/rename (open cursors) surfaces as generic.

Fix: route every WT return through context-aware mapping; audit call sites.

1.4 Locking semantics are a façade

store_lock is InnoDB copy-pasta with all actions commented out (wiredtiger_cursor.cc:296-409). SELECT…FOR UPDATE / SHARE MODE take no locks; write-write conflicts return instant WT_ROLLBACK (surfaced as deadlock) instead of waiting; SERIALIZABLE maps to snapshot isolation (wiredtiger_engine.cc:656) which is not serializable.

Fix: at minimum enforce/document limits; consider wait-retry on WT_ROLLBACK to approximate innobase lock waits.

1.5 Lifecycle leaks and stubs

close() frees key_fields but not index_key_fields/index_value_fields (leak on aborted scans); destructor empty; doOpen mallocs unchecked; clone() returns NULL (kernel paths that clone will crash); reset() is a no-op so opt_flag leaks across statements on cached cursors.

1.6 Primary-key selection logic disagrees with itself

doCreateTable and setupKeyFields pick the last unique index when there's no PRIMARY (loop doesn't break on is_unique); get_first_unique_index (wiredtiger_cursor.cc:87) picks the first by key_info order; cursor code assumes keynr==0 is the WT table key. No-PK table with 2+ unique keys mis-maps key fields.

Fix: one rule (first unique, matching kernel key order), everywhere.


Tier 2 — Parity gaps vs innobase

  • Type coverage: no DOUBLE, DECIMAL, DATETIME, TIME, BLOB/TEXT, BOOLEAN, UUID, MICROTIME, IPV6 (wiredtiger_cursor.cc:612-648; HTON_NO_BLOBS). Blocks most real schemas. Encode as WT 'u' items with memcmp-ordered encodings (needed for correct key order anyway).
  • ALTER TABLE fully blocked: HTON_ALTER_NOT_SUPPORTED makes the kernel refuse even copy-ALTER (alter_table.cc:968). Once UPDATE paths are solid, drop the flag.
  • Optimizer stats fabricated: records_in_range hardcodes 1 (wiredtiger_cursor.cc:1693); rec_per_key set to total-entries/2 (line 1443, semantically wrong); data/index_file_length are records*40 / records*10 fictions. Innobase does btree dives. Options: bounded cursor walks with cutoff, or sampled stats cached in the share (records_changed/key_stat_version fields exist, unused).
  • No XA/2PC: innobase is XaStorageEngine with doXaRecover etc.; this plugin is TransactionalStorageEngine only, so engine + replication log cannot 2PC (kernel silently downgrades, transaction_services.cc:776). WT 3.2.1 prepared transactions don't survive restart, so true XA recovery is impossible on this library version. Needs an explicit decision.
  • No maintenance commands: ANALYZE (stats refresh), CHECK (WT_SESSION::verify), OPTIMIZE (WT_SESSION::compact).
  • Observability: innobase exposes ~90 status counters + a dozen DATA_DICTIONARY tables; this plugin exposes 3 read-only sysvars. WT statistics: cursors make a stats table function cheap. Also switch connection-level statistics from all to fast for production.
  • Config surface: only cache-size. Need: logging/sync mode, checkpoint interval, session_max, eviction targets, statistics level, per-table block compression (snappy/zlib/lz4) — validateCreateTableOption doesn't accept a compressor and rubber-stamps leaf_page_max/internal_page_max values.
  • Temporary tables unsupported (innobase: session message cache).
  • doStartConsistentSnapshot not implemented (START TRANSACTION WITH CONSISTENT SNAPSHOT doesn't pin a snapshot at the right moment).
  • get_error_message not implemented; WT error/verbose output goes to stderr — hook WT_EVENT_HANDLER into the drizzle log.
  • Dictionary contention: every doGetTableDefinition takes eng_session_mutex and reads WT; checkpoint() holds the same mutex — a slow checkpoint blocks all metadata ops. Add a proto cache (innobase caches).

Tier 3 — Performance polish

  • errmsg_printf(error::INSPECT,…) on hot paths (per-row in doInsertRecord line 913; per-call in index_next, scan_time). Strip.
  • doInsertRecord calls setupKeyFields(0,true) — rebuilds the key-field vector per inserted row.
  • info(HA_STATUS_VARIABLE) opens/aggregates a stats cursor per call; cache per-share with TTL keyed on records_changed.
  • Per-field wiredtiger_pack_start allocation for every nullable field of every row (the two "re-use the null_packer memory" TODOs).

Test suite build-out

Current 21 tests are single-connection happy paths. Add:

  • duplicate-key INSERT/UPDATE errors (catches 0.2)
  • key-changing UPDATEs (0.4)
  • UPDATE/DELETE via secondary index (0.5)
  • NULL index probes (0.9)
  • COUNT(*) on empty/large tables (0.6)
  • IODKU with differing insert/update values (0.10)
  • multi-connection isolation and write-conflict tests
  • autoinc across restart and after explicit inserts (0.12)
  • crash-recovery: kill server between commits, restart, verify (0.1)

WiredTiger 11.3.1 port — DO THIS FIRST

Decision: port to 11.3.1 (clone in ~/src/github.com/wiredtiger/wiredtiger) before working through the tiers above. Rationale below; assessment verified against the 11.3.1 source (commit 05c56015a4).

Port cost: low — the API surface we use is source-compatible

Everything the plugin touches exists unchanged in 11.3.1:

  • Packing API (wiredtiger_pack_start/_close/_item/_int/_uint, unpack equivalents, WT_PACK_STREAM, WT_ITEM) — identical signatures.
  • Cursor CRUD, search_near, raw/append/overwrite configs (overwrite still defaults true — bug 0.2 exists identically on 11.3.1).
  • table:/index: URIs and index projections; index cursors are still read-only (bug 0.5 unchanged); WT indexes still cannot be unique (0.3 unchanged); still no savepoints (0.7 unchanged).
  • Schema ops incl. rename (not removed), truncate(start,stop), verify, compact; statistics: cursors and both stat keys we use (IDs renumbered — fine, we use the macros); error codes incl. the WT_DEADLOCK == WT_ROLLBACK alias; column-store r format; log=/transaction_sync=/checkpoint= durability configs; isolation levels snapshot/read-committed/read-uncommitted; session_max still defaults to 100 (issue 1.1 applies as-is).

The real porting work is packaging, not code:

  • Build system is now CMake (>=3.10, C11; autoconf removed). Debian ships only 3.2.1, so we build WT from source — as an independent, rarely-rebuilt image (it tracks an upstream release tag): plugin/wiredtiger/container/Containerfile, based on the same quay.io/drizzle/libdrizzle base as the server image for binary compat, builder stage git-clones the 11.3.1 tag (no Zuul required-project), CMake out-of-source build with lz4/snappy/zlib/ zstd built in, installed to /usr/local, published as quay.io/drizzle/wiredtiger:{trixie,11.3.1-trixie}. Zuul: build/ upload/promote jobs file-matched to plugin/wiredtiger/container/; the server-image jobs soft-depend on them with requires/provides, so it only rebuilds on WT bumps or base-image updates. [DONE 2026-07-10 — image + CI wiring in place.]
  • Consume it from the drizzle build (this is the actual port patch): drop libwiredtiger-dev/libwiredtiger0 from bindep.txt, COPY –from=quay.io/drizzle/wiredtiger:trixie /usr/local/ /usr/local/ (plus ldconfig) in the drizzle Containerfile's build and drizzled stages, and add the compressor runtime libs to bindep. plugin.ac's AC_CHECK_LIB detection works unchanged once the lib is present.
  • Recompile against the new header (stat IDs/struct layout — no source changes needed since we use macros).
  • New error codes to add to wt_to_drizzle_error: WT_CACHE_FULL, WT_PREPARE_CONFLICT, WT_RUN_RECOVERY, WT_TRY_SALVAGE.
  • Nothing we currently pass to WT was removed (huffman/prefix_search/ flush_tier removals don't affect us).

Why port first

  1. The port and the bug fixes are almost entirely orthogonal — none of the Tier-0 bugs get harder or easier to diagnose on 11.3.1, but several get better fixes there:
    • 0.8 (index_read find flags) and the records_in_range work want the new WT_CURSOR::bound range API (11.2+, works on index cursors) — implementing them on 3.2.1 via manual search_near gymnastics is code we would throw away.
    • The doDropTable stale-index workaround exists specifically for a 3.2.1 crash; likely deletable after the port.
    • The XA/2PC decision (Tier 2) depends on 11.x-only machinery (prepare_transaction + timestamps). Deciding the transaction story against 3.2.1 capabilities would be deciding against the wrong target.
  2. Validation happens once. The expensive part of this plan is the test build-out (crash recovery, concurrency, dup keys). Fix-first means running that suite to green on 3.2.1, then re-validating everything after the port — durability/recovery/eviction behavior is exactly the code that changed most between 2019 and now.
  3. 3.2.1 is EOL (2019). "Production quality" on an unmaintained library is not achievable regardless of how many plugin bugs we fix.
  4. Risk is bounded: if the port stalls, the pure-logic fixes (0.2 overwrite=false, 0.10, 0.11, 1.2, 1.6) are identical on both versions and can proceed in parallel.

Caveats to carry into the port:

  • LSM: removed from the plugin entirely [DONE 2026-07-10 — data_source option rejected, LSM branches deleted, lsm test dropped]. LSM is removed upstream in WT 12.x anyway; LSM workloads will be served by a future myrocks plugin instead.
  • Timestamps are optional; do NOT adopt them initially (the no-timestamp path preserves 3.2-style semantics). Prepared transactions do not survive restart, so true XA recovery remains impossible even on 11.3.1 — document 1PC-only, or buffer at a higher layer.
  • Write ops require snapshot isolation in WT; the read-committed/ read-uncommitted mappings in doStartTransaction stay read-path-only.

Suggested attack order

  1. Port to 11.3.1: consume quay.io/drizzle/wiredtiger in the drizzle Containerfile (bindep swap + COPY –from), recompile, extend wt_to_drizzle_error with the new error codes, drop 3.2.1 workarounds, tests green. (Image + CI wiring and LSM removal already done.)
  2. Durability config (0.1) — small change, biggest payoff; add the kill-restart recovery test on the final substrate.
  3. overwrite=false + dup-key + errkey (0.2, 0.11); unique secondaries (0.3).
  4. DML path bugs (0.4, 0.5, 0.10) + index_read find flags (0.8, using cursor bounds) — all in wiredtiger_cursor.cc, interacting.
  5. Honesty fixes: savepoints/statement rollback (0.7), HTON_HAS_RECORDS (0.6), NULL keys (0.9).
  6. Tier 1 robustness sweep.
  7. Stats (records_in_range via bound cursors), types, ALTER for real parity (Tier 2), then Tier 3.