metadata-protocol's raw-SQL migrations quote identifiers with `"` on every dialect, but MySQL does not run with ANSI_QUOTES — the #8686 / #8629 / #5839 statements cannot parse there

[os-zhuang]

Noticed while implementing #8928, which needed the same raw-SQL seam and had to decide its own quoting.

The claim

packages/metadata-protocol/src/migrations/seed-tenancy-backfill.ts quotes every identifier with double quotes, for every dialect:

function quoteIdent(name: string): string {
 return `"${name}"`;
}

Its module header explicitly names MySQL as a supported target — normalizeRows exists precisely to flatten "mysql2's [rows, fields]" beside pg's { rows } and better-sqlite3's bare array, and resolveSeedTenancyExec is built around driver.execute(sql, params).

Under MySQL's default sql_mode a double-quoted token is a string literal, not an identifier. ANSI_QUOTES is what would change that, and grepping packages/drivers/driver-sql/src/ finds no sql_mode / ANSI_QUOTES setting anywhere — the only MySQL session tweak withUtcSession applies is SET time_zone = '+00:00'. knex itself emits backticks for the mysql/mysql2 clients, so nothing upstream normalises this either.

So on MySQL these statements should fail to parse. Every call site swallows that into a warning by design (a migration must never fail a boot), which means the visible symptom is a log line saying the migration was skipped — not a wrong answer, but not the repair either.

Affected statements are all three migrations armed from the same assembly: seed-tenancy-backfill.ts (#8686 — split probe, collision probe, stamp, counter merge, global-counter delete), plus partial-index-probe.ts / sys-setting-identity-index.ts if they share the convention (worth checking in the same pass).

Verification status — stated exactly

Measured: the quoting is unconditional in source; no ANSI_QUOTES / sql_mode is set anywhere in driver-sql; the module's own header claims MySQL support.

NOT measured: this was not run against a live MySQL server. The repo has the machinery for that (live-dialect-matrix.testkit.ts, and the MySQL jobs that back sql-driver-datetime-mysql-storage.test.ts), so the confirming run is cheap for whoever picks this up — and it should be the first step, because if some layer does set ANSI_QUOTES this whole report is void.

Related

#8928 took the dialect-aware route for its own probes rather than inheriting this convention: os migrate duplicates reads the live driver.config.client and quotes with backticks for mysql/mysql2, double quotes otherwise (quoteIdent in packages/cli/src/commands/migrate/duplicates.ts, pinned in duplicates.probe-sql.test.ts). That is one small, already-tested implementation of the fix, if a shared helper is the direction taken.

Not fixed under #8928: packages/metadata-protocol/** was explicitly out of that card's file surface.

[os-steve]

Triage: fix lands in packages/metadata-protocol/src/migrations/** (quoteIdent and siblings) ⇒ domain:metadata. Type Bug (module header declares MySQL support; the statements cannot parse there under default sql_mode — declared≠enforced, masked because migration failures are by-design swallowed into a warning). pm:queue.

Premise-first, binding: step 1 of the dispatch is the live-MySQL confirmation the card itself asks for (live-dialect-matrix.testkit.ts / the MySQL CI jobs) — if some layer does set ANSI_QUOTES, report premise_still_valid: false and stop; that is a good run, not a failure. Step 2 checks partial-index-probe.ts / sys-setting-identity-index.ts for the same convention in the same pass. Fix direction: dialect-aware quoting; #8928's quoteIdent in packages/cli/src/commands/migrate/duplicates.ts is a small already-tested reference — producer-side repair in metadata-protocol, ⛔ not a driver-side tolerance.

Coordination note: sequence with #9380 (same three migrations; that card changes when they run, this one changes whether they parse on MySQL). Not Blocked-by: — file surfaces are disjoint (gate predicate vs statement text) — but the two PRs land in the same module family; serial landing preferred. Size/model suggestion: S–M / opus (cross-dialect semantics deserve judgment; the diff itself is small).

---

Generated by Claude Code

[os-zhuang]

Claiming this card.

• Session ID: session_01NTKPDRoynY8i3HmdSFUxFj
• Branch: claude/issue-9381-dialect-aware-quoting

Plan follows the dispatch: step 1 is the live-MySQL premise confirmation (ANSI_QUOTES / sql_mode on a real server) before any fix is written; step 2 checks partial-index-probe.ts and sys-setting-identity-index.ts for the same convention. No changes to migration gating (#9380's surface).

---

Generated by Claude Code

---

Generated by Claude Code

[os-zhuang]

{
 "issue": 9381,
 "status": "done",
 "branch": "claude/issue-9381-dialect-aware-quoting",
 "pr": "https://github.com/objectstack-ai/objectstack/pull/9440",
 "premise_still_valid": true,
 "summary": "Step 1 (binding) ran first on a LIVE MySQL 8.0.46 and CONFIRMED the premise: session sql_mode is IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION — no ANSI_QUOTES anywhere, and all 7 statements the module builds failed with ER_PARSE_ERROR. The live run also found TWO further MySQL-only defects the card could not see from source: `last_value` is a RESERVED word on MySQL 8.0 so unqualified column names are parse errors even with the table spelled correctly (a qualified `g.last_value` is legal, which is why #8928's probe is unaffected), and `UPDATE t ... (SELECT ... FROM t)` is refused with ER_UPDATE_TABLE_USED (1093) — quoting alone would NOT have made the stamp run. Fixed producer-side in metadata-protocol: dialect-aware quoteIdent mirroring #8928's, every identifier quoted, the stamp's exclusion guards routed through a derived table, and structurally the seam now carries the dialect (resolveSeedTenancySeam returns { exec, client }; backfillSeedTenancy takes the pair) so a caller cannot drop it. resolveSeedTenancyExec is kept unchanged for the CLI. Step 2: partial-index-probe.ts is CLEAN (it builds no identifier-quoted SQL of its own); sys-setting-identity-index.ts is CLEAN of this defect and deliberately bare-identifier — but its operator-facing duplicate-probe statement names the MySQL-reserved column `key` and does not parse there (measured), filed as #9434, not fixed here. Migration gating untouched (#9380's surface).",
 "tests": "LIVE MYSQL 8.0.46 (server started in-container; OS_TEST_MYSQL_URL). New suite packages/metadata-protocol/src/migrations/seed-tenancy-backfill.live-mysql.test.ts, 5/5 pass, with a non-vacuity assertion that the server is NOT running ANSI_QUOTES (log line: '[#9381] live MySQL 8.0.46-0ubuntu0.24.04.3 sql_mode=IGNORE_SPACE,ONLY_FULL_GROUP_BY,...'). REVERSE VERIFICATION twice, each half ablated alone from the COMMITTED state: (a) quoting reverted to unconditional ANSI => 4 of 5 RED, ER_PARSE_ERROR on 'SELECT \"tenant_id\" FROM \"_objectstack_sequences\" WHERE 1 = 0'; (b) derived table reverted only, quoting kept => 4 of 5 RED, ER_UPDATE_TABLE_USED 1093 on the backtick-spelled UPDATE. Restored => 5/5 green. The one green in both ablations is the ANSI_QUOTES server assertion, which is about the server, not the fix. LIVE POSTGRESQL 16.13: the ANSI output of the same builders executed statement-by-statement with ON_ERROR_STOP=1 — all 7 run, UPDATE 1 (only the non-colliding row moved). SQLITE: packages/runtime seed-tenancy-autonumber-split.integration.test.ts 8/8. Seam resolution verified against REAL SqlDriver instances, not doubles: sqlite seam.client=\"better-sqlite3\", mysql2 seam.client=\"mysql2\". Suites: metadata-protocol 120 passed | 1 skipped (the live file with no URL — a named skip), 1662 tests; runtime 165 files / 2467 tests; cli src/commands/migrate 7 files / 33 tests. tsc -p packages/metadata-protocol = 63 errors, exactly the frozen ledger count (no ledger edit). GATES re-derived with node scripts/pm/dispatch-gates.mjs and the union re-run AT THE FINAL COMMIT d79b35377c: nul-bytes, cross-package-test-inputs, durability-log-level, node-version, required-contexts, shard-attestation, workflow-status-functions, stack-collection-maps, changeset-gate-self-tests, objectui-changeset, empty-changeset, adr-0087-registration, override-consistency, query-options-erasure, engine-double-contract, where-matcher, type-check-coverage, check-changeset-no-major, check-changeset-fixed, check-osv-exemptions, docs-audit/check-affected-docs — all PASS. Ratchet check:type-check-debt --re-measure at the same d79b35377c over a fully built workspace closure: '33 ledger entries re-measured, 1926 raw tsc errors total, none above its recorded number.' No ablation of a dogfood dist artifact was involved (this fix has no dogfood leg). CI status at report time: in_progress — reported at draft-PR time per the dispatch contract.",
 "open_questions": [
 {
 "question": "quoteIdent now exists twice — this copy and #8928's in packages/cli/src/commands/migrate/duplicates.ts. Should it move to a shared home, and where?",
 "options": [
 "A — leave both copies (status quo of this PR): zero risk to #8928's tested file, but the next raw-SQL producer copies it a third time and one copy can drift from the other.",
 "B — @objectstack/types: metadata-protocol already depends on it, the CLI reaches it transitively, and it already hosts dialect-shaped helpers (isUniqueViolationError and its cause-chain walkers).",
 "C — a new tiny package, e.g. @objectstack/sql-dialect: cleanest layering, most ceremony for ~6 lines."
 ],
 "recommendation": "B, but as its own card, not as a rider here. Real business need: three producers already hand-build raw SQL for three dialects and two of them got it wrong independently — that is measured pull, not a speculative surface. Long-term soundness: one spelling of 'how this platform quotes an identifier' is the contract-first shape; two copies is exactly the drift ADR-0049 keeps re-litigating. Hard-to-get-wrong: a shared helper plus this PR's seam pattern (the dialect travels WITH the exec) makes the next producer's default correct rather than accidental. The dispatch explicitly ruled the refactor of #8928's copy out of scope for this card, so it needs its own decision and its own round."
 }
 ],
 "out_of_scope_findings": [
 "filed as #9434: sys-setting-identity-index.ts prints a duplicate-probe statement naming the MySQL-reserved column `key`, so the remedy it hands the MySQL operator does not parse on MySQL (measured ER_PARSE_ERROR); printed only, never executed, so no runtime path breaks. overlay-index.ts's equivalent probe was measured on the same server and runs fine; partial-index-probe.ts builds no such statement."
 ]
}

---

Generated by Claude Code