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

[os-zhuang]

Filed out of #8567, which measured Postgres and generalised the unbacked-conflict-target refusal to it. Unassigned. Observation-class and deliberately unmeasured — the first deliverable of this card is the measurement, not a fix.

What #8567 established, without a server

SqlDriver.upsert passes conflictKeys to knex's onConflict(...).merge(...). On the mysql2 dialect knex compiles that to ON DUPLICATE KEY UPDATE, which takes no conflict target:

mysql2 -> insert into `plain` (`email`, `id`, `title`) values (?, ?, ?)
 on duplicate key update `title` = values(`title`) <- named keys are gone
pg -> insert into "plain" (...) values ($1, $2, $3)
 on conflict ("email") do update set "title" = excluded."title"

The caller's keys never leave the process, so the server is never asked to find an index for them. The unbacked-conflict-target condition cannot arise on MySQL, and isUnbackedConflictTargetError correctly carries no MySQL limb.

The open question this leaves

If the target is compiled away, MySQL does not refuse — it merges on whichever unique key the row happens to collide with. A caller who wrote conflictKeys: ['email'] on a table whose only unique index is on tax_id gets a silent merge against tax_id on MySQL, where SQLite and Postgres both refuse. That is a worse failure than the one #8445 and #8567 fixed: those were legible-versus-illegible errors, this is a wrong write with no error at all.

⚠️ The paragraph above is an inference from the compiled SQL, not an observation. Nobody has watched a MySQL server do it. #8445's dispatch ruled that describing dialect behaviour from memory or inference is not acceptable evidence, and #8567's dev correctly declined to file this card on that basis — the container has no mysqld/mariadbd and its docker daemon is unreachable.

The measurement channel exists — in CI, not on a dev box

That premise is resolvable. CI already provisions a live MySQL for the conformance job:

# .github/workflows/ci.yml:583
image: mysql:8.0
# .github/workflows/ci.yml:673
OS_TEST_MYSQL_URL: mysql://root:root@127.0.0.1:3306/conformance

and packages/drivers/driver-sql/src/live-dialect-matrix.testkit.ts already reads OS_TEST_MYSQL_URL, reporting an un-run cell rather than passing vacuously. sql-driver-upsert-conflict-target-dialects.test.ts (landing with #8567) currently declares its MySQL cell un-run for exactly this reason — the file runs under Test Core, which has no such service.

What this card is, in order

1. Measure. On a runner that has the MySQL service: create an object whose unique index is on a column other than the one named in conflictKeys, upsert with the mismatched key, and record what actually happens — silent merge on the other key, an error, or something else. ⛔ Do not write the answer into a test before observing it.
2. Then decide, and only then. Options range from leaving it (documented, dialect-honest) through refusing pre-flight in driver-sql when the dialect cannot honour a named target — the latter changes the accept set on MySQL, so it is a minor and wants its own argument, not a rider.
3. Either way the MySQL cell should stop being structurally un-runnable: either wire this file's matrix into a job that has the servers, or state why it belongs where it is.

Related: #8567 (where the compiled SQL was read), #8445 / #8413 (the two faces that refuse this condition), #8590 (the other finding out of #8567), #5240 (one condition, one wording).

[hotlong]

Triage: lands in packages/drivers/driver-sql (the live-dialect matrix + sql-driver-upsert-conflict-target-dialects.test.ts, on main since PR #8591); rationale: well-scoped measurement card with a named channel (CI's OS_TEST_MYSQL_URL service) — queued.

• Native type set to Task, not Bug: the silent-merge-on-the-wrong-key hazard is the card's own flagged inference (⚠️ "not an observation"), and the first deliverable is the measurement. If step 1 confirms the wrong write, re-type Bug on the spot.
• Serial constraints: none remaining — drivers(sql): the unbacked-conflict-target refusal is SQLite-only — Postgres and MySQL still answer the raw driver error #8567 closed 01:10Z via merged PR fix(drivers): recognise the unbacked conflict target on Postgres too, from a measured PG 16.13 (#8567) #8591, so the matrix file this card extends is on main. Re-checked at triage time.
• Scope guard for dispatch: step 1 (measure) + step 3 (make the MySQL cell runnable or justify its home) only. Any step-2 change to the MySQL accept set (e.g. a pre-flight refusal) is a minor with its own argument — comes back as its own card, not a rider.
• Size/model suggestion: M, mode:subagent, opus.

---

Generated by Claude Code

[os-zhuang]

Claim: drivers seat, session session_01VoxQqG5FiUHZKCST7KDoZC, branch claude/issue-8592-mysql-conflict-target-behaviour.

File surface: packages/drivers/driver-sql/src/** (the dialects test + live-dialect-matrix.testkit.ts) and .github/workflows/ci.yml if step 3 needs it. ⛔ Not packages/spec, ⛔ not docs/adr/**. Serial constraints: none — triage re-checked, #8567 merged 01:10Z.

⚠️ I have to correct my own card before dispatching it

I wrote "the file runs under Test Core, which has no such service." That is wrong, and the correction makes this card much sharper. Verified on origin/main just now — the conformance job's step is literally:

 - name: Run driver-sql suite against both live servers
 env:
 OS_TEST_POSTGRES_URL: postgres://…
 OS_TEST_MYSQL_URL: mysql://root:root@127.0.0.1:3306/conformance
 OS_EXPECT_LIVE_DIALECT_MATRIX: '1'
 run: … pnpm --filter @objectstack/driver-sql test

So sql-driver-upsert-conflict-target-dialects.test.ts already runs with a live MySQL 8.0 attached, in CI, today. The server is not missing — it is present and unused.

What that exposes, and it is worse than the gap I filed

The file's MySQL cell is guarded one way only (:323-326, the last lines of the file):

const MYSQL_CELL = DIALECT_CELLS.find((c) => c.id === 'mysql')!;
if (!MYSQL_CELL.available) {
 declareUnprovisionedCell(MYSQL_CELL, 'unbacked conflict-target refusal (behaviour never observed)');
}

There is no else. So:

job	OS_TEST_MYSQL_URL	what the MySQL half does
Test Core	absent	declares itself un-run — visible
Temporal Conformance (live PG + MySQL)	present	declares nothing, measures nothing — invisible

The declaration disappears exactly when the capability to measure appears. That is the inverse of the usual vacuous-green: not a cell that passes without running, but a gap that stops announcing itself on the one runner that could close it. OS_EXPECT_LIVE_DIALECT_MATRIX: '1' exists specifically to turn a missing-URL skip into a named red, and this cell slips past it by not being a skip at all.

None of this is a criticism of #8591 — its declaration is correct for the container it was written in, and the dev could not have measured MySQL there. It is the premise of this card that was incomplete, and it was mine.

What the dispatch therefore asks for

Step 1 is now immediately actionable rather than blocked on someone with a server: add the measuring branch and let CI run it. ⚠️ The dev must still not write the expected answer first — the pin records what the server did, and if MySQK merges on the wrong key, that is a wrong write with no error and the card re-types to Bug on the spot (triage's instruction).

Scope guard adopted from triage verbatim: steps 1 and 3 only. Any change to MySQL's accept set — a pre-flight refusal when the dialect cannot honour a named target — is a minor that comes back as its own card, not a rider on a measurement.

Model: opus, matching triage. Not fable tier: measuring and reporting does not move any accept/reject set, and nothing here widens a spec surface.

---

Generated by Claude Code

[os-zhuang]

{
 "issue": 8592,
 "status": "done",
 "branch": "claude/issue-8592-mysql-conflict-target-behaviour",
 "pr": "https://github.com/objectstack-ai/objectstack/pull/8624",
 "premise_still_valid": true,
 "summary": "OBSERVED on a live MySQL 8.0.46 raised in this container (Ubuntu noble mysql-server via apt, mysqld --daemonize, default_time_zone='+08:00'), driven through the same knex + mysql2 path upsert takes — no inference. Table: `email` named in conflictKeys with NO unique index, `tax_id` carrying the only unique index. Verbatim: seed upsert({email:'a@b.com', tax_id:'T-1', title:'first'}, ['email']) -> RESOLVED, rows=[{id:'VBjOQwQp3uTtewte', email:'a@b.com', tax_id:'T-1'}]. Then upsert({email:'other@b.com', tax_id:'T-1', title:'second'}, ['email']) -> RESOLVED, rows=[{id:'RnSaXzGO69OKkP_D', email:'other@b.com', tax_id:'T-1'}] — ONE row, merged on `tax_id` (the key the caller never named) across two DIFFERENT `email` values, and the surviving row's PRIMARY KEY changed. Then, from a clean table, seed + upsert({email:'a@b.com', tax_id:'T-2'}, ['email']) -> RESOLVED, TWO rows both email='a@b.com'. Reproduced across two independent runs. So four facts: (1) no refusal, where SQLite and Postgres both answer VALIDATION_ERROR/400 for the identical call; (2) a silent merge on the wrong key — the card's inference, now confirmed; (3) the merged row's PRIMARY KEY is REPLACED, because `id` sits in the merge set (`id = values(id)`) — the inference did NOT contain this and it is the sharpest edge, since every external reference to the old id dangles with no error; (4) the named key is not honoured at all, yielding duplicates. MySQL fails in both directions at once. The dispatch's premise correction was right and is the second half of the fix: the file's MySQL cell was guarded one way only, so on the one CI job with a live MySQL attached it declared nothing and measured nothing. Fixed structurally — declareDialectCell(cell, matrix, measure) in live-dialect-matrix.testkit.ts is a TOTAL function, so the half-pair form no longer typechecks; the other six consumers were checked and are already two-way. New pins are marked loudly as characterizations of a DEFECT, to be rewritten (not relaxed) when #8621 lands.",
 "tests": "Both live servers raised locally, so verification ran in CI's exact shape rather than a subset — TZ=America/New_York, live PG 16.13 at Asia/Shanghai, live MySQL 8.0.46 at +08:00, OS_EXPECT_LIVE_DIALECT_MATRIX=1. Full package suite: `pnpm --filter @objectstack/driver-sql test` -> 'Test Files 98 passed (98) / Tests 2011 passed (2011)'. Target file across all three cells -> '19 passed (19)' (6 sqlite + 6 live postgres + 2 compile pins + 5 new live mysql). `tsc --noEmit` clean. Un-provisioned paths checked BOTH ways: with no URLs the MySQL cell reports a named skip ('is provisioned — set OS_TEST_MYSQL_URL to run this cell of the D-A3 driver axis'); with OS_EXPECT_LIVE_DIALECT_MATRIX=1 and URLs dropped it is a named red (2 failed | 8 passed). REVERSE VERIFICATION, direction predicted before each leg, fix committed first: leg 1a — dropping the `measure` argument predicted RED AT TYPECHECK not runtime; observed exactly 'error TS2554: Expected 3 arguments, but got 2.' Matched. Leg 1b — restoring the ORIGINAL one-way block with a live MySQL attached predicted the gap returns SILENTLY and green; observed 19 passed -> 14 passed, still green, tsc exit 0, the five MySQL tests vanished with no signal. Matched, and this is the defect reproduced. Honest bound: leg 1b also shows the combinator does NOT catch a wholesale revert to the one-way form (tsc exit 0) — it makes the half-pair unrepresentable, not the deletion. Positive control predicted unchanged and was: SQLite and Postgres cells pass identically, sweep code untouched. Restored byte-identical after both legs (git status clean, git diff --stat HEAD empty). Gates re-derived against actual changed paths via scripts/pm/dispatch-gates.mjs, which surfaced four families the prompt did not name — check:test-source-alias, check:type-source-resolution, check:query-options-erasure, check:type-check-coverage — all run and green, plus check:nul-bytes OK.",
 "open_questions": [],
 "out_of_scope_findings": [
 "filed as #8621: the step-2 card triage carved out — MySQL silently merges on a unique key the caller never named and rewrites the merged row's primary key; carries the measurement as its evidence and recommends the pre-flight refusal, since it moves MySQL's accept set and is a minor with its own argument",
 "filed as #8622: an upsert merging on ANY non-PK conflict key silently REWRITES the existing row's primary key — measured on SQLite AND MySQL with a properly backed unique conflict target, so it is not MySQL-specific and not about conflict-target validity. Cause: `id` is minted per call and is absent from insertOnlyUpsertColumns, so it travels in the merge set; a no-op only on the default id-keyed path, which is why it stayed invisible. Same argument #7011 accepted for auto_number"
 ]
}

Notes for the PM, outside the JSON

• Card re-typed to Bug, per triage's instruction in comment 5288300349 ("If step 1 confirms the wrong write, re-type Bug on the spot"). Step 1 did confirm it. Flagging the nuance: the wrong write itself is now carried by 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, so if you would rather 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 stay a Task (its own deliverable — the measurement — is complete and green), reverting the type is a one-click change and I will not contest it.
• PR is Part of #8592, not Fixes. The card body's step 2 is not done here — triage excluded it from dispatch, and it is 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. Steps 1 and 3 are complete. If the re-scope is authoritative, 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 can be closed on merge; deliberately left as your call rather than closing it from the PR.
• Labels read back (REST, after bots settled): skip-changeset and tests. Changeset decision: skip-changeset, no changeset file — tests plus a test-only helper (live-dialect-matrix.testkit.ts is not exported from index.ts, verified), no runtime path changed.
• CI convergence not waited on, per the standing contract. Gate status at hand-off is in_progress; the local run above was CI's exact command with both live servers attached, which is the strongest signal available before the runner reports.
• Local servers I started (mysqld, postgres) and the worktree have been torn down.

---

Generated by Claude Code