drivers(sql): on MySQL an upsert still merges on a unique key the caller never named — even when the named conflict target IS backed

[hotlong]

Found while implementing #8621 (the pre-flight refusal for an UNBACKED conflict target on MySQL). Filed rather than fixed: it is a different condition with a different fix, and #8621 deliberately does not move it.

The condition

#8621 refuses a conflictKeys upsert on MySQL when no PRIMARY KEY or UNIQUE index backs the named target. That closes the case #8592 measured. It does not close the general one, because ON DUPLICATE KEY UPDATE carries no conflict target at all — so once the statement is accepted, any unique index on the table can absorb the conflict, including one the caller never named.

A properly declared, properly indexed conflict target is therefore still not honoured as a target on MySQL whenever the table carries a second unique index.

Measured — live MySQL 8.0.46

Ubuntu noble mysql-server, mysqld --daemonize, default_time_zone='+08:00', driven through the same knex + mysql2 path SqlDriver.upsert takes. Both business columns declared unique: true, so the named target email is backed and the pre-flight from #8621 passes it:

CREATE TABLE `probe_two_unique` (
 `id` varchar(255) NOT NULL, `email` varchar(255) DEFAULT NULL,
 `tax_id` varchar(255) DEFAULT NULL, `title` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uniq_probe_two_unique_email` (`email`),
 UNIQUE KEY `uniq_probe_two_unique_tax_id` (`tax_id`)
)

seed upsert({email:'a@b.com', tax_id:'T-1', title:'first'}, ['email'])
 -> RESOLVED. [{id:'tU0Pal1GHDzBHcOg', email:'a@b.com', tax_id:'T-1', title:'first'}]

B upsert({email:'other@b.com', tax_id:'T-1', title:'second'}, ['email'])
 -> RESOLVED. [{id:'tU0Pal1GHDzBHcOg', email:'other@b.com', tax_id:'T-1', title:'second'}]
 ONE row. `email` — the key the caller named, and a backed one — did NOT
 collide; `tax_id` did, and MySQL merged on it.

The identical call on SQLite and Postgres inserts a second row: ON CONFLICT (email) names an arbiter index, so a tax_id collision is not absorbed by that statement — it raises a unique violation instead, which is a legible error rather than a silent merge across two different email values.

So the dialect divergence #8621 narrows is narrowed, not removed: after #8621, MySQL refuses the calls SQLite and Postgres refuse, but it can still merge where they would not.

Why it was not folded into #8621

Refusing this shape means refusing every conflictKeys upsert on any MySQL table carrying more than one unique index — an accept-set change far wider than the one #8621's ruling covers, and one that would refuse calls which are correct whenever no second index can actually collide. The remedies are also not accept-set-neutral in the way #8621's is, and they differ in kind:

• A — refuse at pre-flight when the table carries any unique index outside the named target. Loud and cheap; refuses a large set of calls that work today, most of which are fine in practice.
• B — compile a target-honouring statement on MySQL (e.g. probe-then-write, or INSERT ... ON DUPLICATE KEY UPDATE guarded by a transaction plus a SELECT ... FOR UPDATE on the named key). Preserves the capability; costs a round trip and needs its own concurrency argument.
• C — declare it a documented dialect limit and reject conflictKeys naming a non-primary key on MySQL outright.

Each is a real decision about MySQL's accept set, so this needs adjudication rather than a dev picking one.

Related: #8621 (the unbacked-target pre-flight), #8592 (the original live-MySQL measurement), #8567 (the compiled-SQL read that shows the target is dropped), #5240 (one condition, one wording).

[hotlong]

Triage: lands in packages/drivers/driver-sql ⇒ domain:drivers. Routed to the decision box, not the queue. Type Bug — the declared contract (conflictKeys names the conflict target) is not honoured on MySQL.

Why this does not go to the auto-adjudication lane. Its sibling #8621 did, and the boundary test is what separates them. #8621's remedy is accept-set-neutral in the direction that matters — it refuses calls that were already producing a wrong write, restoring declared = enforced. Here no remedy is accept-set-neutral: A and C narrow MySQL's accept set to refuse calls that work correctly today whenever no second unique index can actually collide, and B preserves the accept set but buys it with a round trip and a concurrency argument the platform has not made anywhere else. Choosing among them is a ruling about what MySQL support means, which is the maintainer floor (protocol/public-contract change).

⚠️ Also structurally closed regardless: this triage session runs claude-opus-5, not the claude-fable-5 the seat post pins, so auto-adjudication confidence gate ⑤ fails this fire. Recorded so the routing is not read as a merits-only call.

Four-facet card face

• ① Platform long-term coherence — today MySQL is a silent divergence: the same call inserts a second row on SQLite/Postgres and merges on MySQL. Any of the three options collapses that into a legible one, so all three improve coherence. B is the only one that removes the special case rather than documenting it; A and C make the dialect limit explicit and permanent.
• ② Measured business pull — zero measured today. The condition needs a table with a second unique index and an upsert naming the first. drivers(sql): what MySQL does with a conflict target it cannot honour is unmeasured — knex compiles the target away, so the refusal cannot arise there #8592/drivers(sql): MySQL silently merges an upsert on a unique key the caller never named, and rewrites the merged row's primary key — measured, and it is a wrong write with no error #8621 measured the unbacked case; no production or example-app occurrence of the two-unique-index shape has been measured. This facet argues for C or a documented defer, and against paying for B speculatively.
• ③ AI-agent error-resistance — this is the facet that argues hardest for loud refusal. An AI author declaring two unique: true business columns and calling upsert(..., ['email']) gets a silent wrong write with a rewritten primary key. Silent-wrong is the worst square on this axis; a refusal an author can read is strictly better than a merge they cannot see. A and C both deliver that, B delivers correctness instead.
• ④ Startup scope discipline — B is the expensive option (new statement compilation path, a concurrency argument, per-dialect divergence to maintain forever). A and C are cheap and reversible; C is the cheapest and honest about the platform's actual investment posture in MySQL.

Recommendation, if it helps: C, narrowed. Refuse conflictKeys naming a non-primary key on MySQL when the table carries a unique index outside the named target — that is A's precision with C's framing, refuses only the shape that is actually unsound, and leaves the single-unique-index case (the common one) working. It converts a silent wrong write into a documented dialect limit at near-zero cost. B stays available later if measured pull appears.

Dispatch constraints recorded for whoever gets it after a ruling

• ⛔ Hard serial behind drivers(sql): MySQL silently merges an upsert on a unique key the caller never named, and rewrites the merged row's primary key — measured, and it is a wrong write with no error #8621 (pm:dispatched, in flight) — same SqlDriver.upsert surface. Do not dispatch in parallel.
• drivers(sql): upsert's empty-merge-set fallback is merge-ALL, which re-admits every insert-only column — currently unreachable, but it is the wrong shape for "nothing to merge" #8740 (pm:on-hold, drivers) is adjacent: the empty-merge-set fallback in the same function. Its hold notes that knex lowers .ignore() on MySQL to insert ignore, which swallows unrelated errors — relevant if option B is ever chosen.
• Maintainer's 2026-08-05 investment-freeze instruction covers driver-memory / driver-mongodb; driver-sql is not frozen, so this card is fundable.

---

Generated by Claude Code

[os-zhuang]

Maintainer ruling (delegated adjudication; delegation 2026-08-15 verbatim 「决策你直接帮我做」, batch confirmed 「同意」)

Ruled: Option A — preflight refusal when a second unique key exists on the target table — plus Option C's documentation half (record the dialect limit in the driver docs). Option B is rejected. Note: this ruling deliberately diverges from the filer's lean toward B.

Rationale (four-prism):

1. Platform coherence — the contract promises merge on the named target. MySQL's ON DUPLICATE KEY UPDATE semantically cannot honour that: it merges on whichever unique key collides first. Option B ("compile a target-honouring statement") on MySQL means a SELECT-then-branch emulation — a race window and an execution path unlike every other dialect. That is dressing "the dialect can't" up as "it did".
2. Business pull — the common single-unique-key shape is wholly unaffected by A; only the genuinely ambiguous shape is stopped.
3. AI error-resistance — an explicit refusal whose message names the colliding second unique key and the remedy is teachable; both "silently merged into the wrong row" and "emulated semantics with a race" are not.
4. Startup scope — A is the minimal correct surface; B's emulation is a debt more expensive than the feature gap it papers over.

Implementation notes for the dispatching seat:

• Preflight fires only when the named target is backed and at least one other unique key exists on the table — the single-key fast path stays untouched.
• Error message must name the second unique key and state the workaround (drop/rename the extra key, or use a dialect without the limitation).
• Document the MySQL dialect limit where driver differences live.
• Pin both branches: single-unique-key upsert still succeeds; two-unique-key upsert refuses with the named key in the message.

Label flipped needs-user-decision → pm:queue. Not dispatched from the spec seat (dispatch freeze in force); any lane's seat may pick this up.

---

Generated by Claude Code

[hotlong]

Claim: PM loop round 5
Session: session_01XeQRiAa7vYRVX5Fog7Zby8
Branch: claude/issue-8755-mysql-upsert-second-unique-key
Worktree: objectstack-issue-8755
Domain: domain:drivers
File surface: packages/drivers/driver-sql/src/sql-driver.ts — the upsert pre-flight path, extending the assertConflictTargetBacked machinery #8621 landed at :5144 (call site :5209) — plus its co-located tests and the driver-differences doc page. ⛔ Stop on breach and report.
Container & model: M, mode:subagent, model: opus — judgement tier (ruling implementation, live-MySQL measurement, error-message design). Not claude-fable-5: the mandatory clause is scoped to accept/reject changes on packages/spec, which this card does not touch, and the accept-set narrowing here was already ruled rather than delegated to the dev.
Serial constraints cleared: the triage note's ⛔ hard serial behind #8621 is discharged — #8621/PR #8761 landed as c8806ae and its pre-flight is on origin/main (assertConflictTargetBacked at :5144, introspectKeyIndexes at :5039, cache invalidation at :6552). No other in-flight card in any of my three lanes declares driver-sql; #8197 (the other open driver-sql card) is deliberately not in this batch to keep one editor in this file. driver-sql is not covered by the #5499 investment freeze, which names driver-memory / driver-mongodb only.

The ruling is settled — ⛔ do not re-adjudicate

Maintainer ruling in comment 5299846509 (delegated adjudication; delegation 2026-08-15 verbatim 「决策你直接帮我做」, batch confirmed 「同意」):

Ruled: Option A — preflight refusal when a second unique key exists on the target table — plus Option C's documentation half (record the dialect limit in the driver docs). Option B is rejected.

⚠️ The ruling explicitly diverges from the filer's lean toward B. Option B (probe-then-write / SELECT ... FOR UPDATE emulation) is rejected because on MySQL it means a SELECT-then-branch with a race window and an execution path unlike every other dialect — "dressing 'the dialect can't' up as 'it did'". ⛔ Do not build B, and do not re-open the comparison.

Binding implementation notes, verbatim from the ruling:

• Preflight fires only when the named target is backed and at least one other unique key exists on the table — the single-key fast path stays untouched.
• Error message must name the second unique key and state the workaround (drop/rename the extra key, or use a dialect without the limitation).
• Document the MySQL dialect limit where driver differences live.
• Pin both branches: single-unique-key upsert still succeeds; two-unique-key upsert refuses with the named key in the message.

Build on #8621's machinery, do not mint a parallel introspection

assertConflictTargetBacked already introspects the table's key indexes and caches them (physicalKeyIndexes, :3391, invalidated at :6552). Your condition needs the same index list, asked a different question: instead of "is the named target backed?", "is there a unique index outside the named target?". Extend that path — a second introspection would be a second place to get MySQL's index metadata right, and it would miss #8621's cache invalidation.

Note the ordering #8621 established and preserve it: the pre-flight sits before fillAutoNumberFields, so a refusal never burns an autonumber. Your new refusal must land inside the same window.

The measurement that grounds this — reproduce it, do not trust the transcript

The card carries a live MySQL 8.0.46 measurement (both business columns unique: true, named target email backed and passing #8621's pre-flight):

B upsert({email:'other@b.com', tax_id:'T-1', title:'second'}, ['email'])
 -> ONE row. `email` — the key the caller named, and a backed one — did NOT
 collide; `tax_id` did, and MySQL merged on it.

The same call inserts a second row on SQLite and Postgres. Reproduce this before you fix it, so your pin is anchored to a measured behaviour rather than to prose.

Verification bar

Both branches pinned, per the ruling: the single-unique-key upsert still succeeds (this is the discriminating control — without it the pin cannot show the refusal is narrow rather than blanket), and the two-unique-key upsert refuses with the second key named in the message. Assert the message actually contains the colliding key's name and the workaround — a refusal whose text is untested is a refusal that will drift into uselessness.

The docs half is not optional: the ruling folds in option C's documentation, so land the dialect limit where driver differences are already documented, in the same PR.

Adjacent, deliberately out of scope

#8740 (pm:on-hold, drivers) covers the empty-merge-set fallback in the same function — its hold notes knex lowers .ignore() on MySQL to insert ignore, which swallows unrelated errors. That was relevant mainly to option B, which is now rejected. ⛔ Do not fold #8740 in; if your change makes it easier or harder, say so in the report and I will re-price it.

---

Generated by Claude Code