sluice

MySQL won't match a JSON column by bind parameter

WHERE json_col = ? matches zero rows in MySQL whether you bind the value as a string or as bytes — the server won't cast the parameter to JSON for the comparison. On a CDC UPDATE, replay-idempotency tolerance turns that zero-row match into silent divergence.

Landed in sluice · 2026-05-04 · MySQL & Vitess

Observed — MySQL → MySQL logical replication (CDC apply) touching a JSON column. Internally the applier value-shaping fix, ADR-0013 (v0.2.2).

What happened #

A MySQL-to-MySQL CDC UPDATE on a table with a JSON column silently applied nothing: zero rows affected, stream position advanced, exit 0, no error. The same applier in the other direction — Postgres to MySQL — failed loudly on the identical column, crashing with Cannot create a JSON value from a string with CHARACTER SET 'binary'. One applier, one JSON column, two directions, opposite symptoms — and only the loud one was safe.

Why (the mechanism) #

The applier bound the row's values straight into parameterised SQL. Two MySQL-isms bit, in sequence:

What made the second one invisible is a property of every correct CDC applier: it must tolerate zero rows affected, because logical-replication resume re-applies events idempotently (a re-applied UPDATE legitimately matches zero rows the second time — that tolerance is what makes replay safe). So the applier could not tell "already applied" from "never matched," logged the zero-row result as normal, advanced the position, and diverged the target with no signal.

The repro #

The comparison, in isolation — no replication needed:

CREATE TABLE ledger (id BIGINT PRIMARY KEY, doc JSON);
INSERT INTO ledger VALUES (1, '{"k":"v"}');

-- a JSON column compared against a (string/bytes) parameter:
SELECT * FROM ledger WHERE doc = '{"k":"v"}';              -- 0 rows
-- the same comparison, parameter cast to JSON first:
SELECT * FROM ledger WHERE doc = CAST('{"k":"v"}' AS JSON); -- 1 row

-- so a CDC applier binding: UPDATE ledger SET ... WHERE doc = ?
--   matches nothing, reports 0 rows affected, and (idempotency
--   tolerance) advances the stream position anyway.

What sluice does about it #

The CDC applier now routes every bound value through the same per-type prepareValue shaping the bulk-copy path uses (JSON []byte → string, and the rest), driven by a lazily-populated per-table column-type cache. For the comparison itself, a placeholderFor(type) helper emits CAST(? AS JSON) instead of a bare ? for JSON-typed columns, so the equality is JSON-to-JSON and matches. The Postgres applier needs no cast equivalent — pgx inspects per-column type metadata natively. And a Debug line now fires whenever an UPDATE or DELETE matches zero rows: resume idempotency still depends on tolerating that case, but the silence now leaves a footprint.

The transferable lesson #

A parameterised = against a typed column is not type-agnostic: MySQL will not coerce a bind parameter into JSON, so a comparison that reads correctly matches nothing at runtime. And when the consumer is a CDC applier, its idempotency tolerance — the very thing that makes replay safe — is exactly what hides a never-matched predicate. This is the MySQL sibling of a Postgres story we hit on the same class: REPLICA IDENTITY FULL silently ate our UPDATEs, where a jsonb value failed an equality round-trip. Same shape, two engines, two root causes: when equality quietly stops matching, replay-tolerance swallows the loss.

Primary sources #