beads/internal/storage/schema/migrations/README.md
Steve Yegge f56632adcf
perf(storage): stop burning two wasted MySQL sessions per bd invocation (wy-s8ytnw) (#6122)
* perf(storage): stop burning two wasted MySQL sessions per bd invocation (wy-s8ytnw)

Every bd store-open against a shared dolt sql-server cost two sessions that
did no work:

1. readAndDial's data-port probe (internal/storage/dbproxy/proxy/endpoint.go)
   dialed the proxy data port and closed it to "prove" it accepts. The proxy's
   handleConn dials the BACKEND for every accepted client connection before a
   byte flows, so the zero-byte probe was a full backend MySQL session on every
   invocation from a proxied clone — and it proved nothing the authenticated
   control-port identity reply had not: ListenAndServe binds the data listener
   before the control listener exists. Drop the probe; the identity reply is
   the liveness proof. (Deferring the backend dial until the first client byte
   was considered and rejected: MySQL is server-speaks-first, so a real client
   waits for the greeting and would deadlock.)

2. The no-database init connection. On the proxied/server uow path
   (openAndInitSchema) and the dolt.New server path (openServerConnection) bd
   opened a second, database-less pool per store-open purely to probe
   existence (SHOW DATABASES / the schema-init preparation) before opening
   the real pool. Both sites now try the database-scoped connection FIRST —
   keyed the way the Gateway case already is, not by deleting the probe: a
   successful connect is the existence proof, so the steady-state open (every
   open but the very first) costs one session. Any connect failure (1049
   Unknown database, server down, bad credentials) falls through to the
   historical no-database path, which still owns creation, the #5042
   ownership signal that arms FreshBootstrapHeal, databaseNotFoundError, and
   every existing error message. `created` is honestly false on the fast
   path; the uow provider reuses the init pool (nothing in the migration path
   mutates session state).

New test TestReadAndDialAdoptsWithoutDialingDataPort pins the probe removal;
it fails (1 accept) against the previous endpoint.go and passes now.

Why the live server still saw a bare CREATE DATABASE per open despite
openServerConnection's SHOW DATABASES guard (the question on wy-s8ytnw): that
guard is not on the proxied path at all — the CREATE came from the uow
schema-init preparation, already fixed upstream by #6022 (alreadyConverged);
the deployed binary (71e5ad700) predates it (wy-7sx6cj).

* fix(storage): cap the fast-path probe budget; correct the pool-reuse premise

Maintainer review fixes for PR #6122. All four are non-gating minors (plus
one nit) from the review synthesis; the quality scorecard's decision was
approve with no required changes.

- uow fast path no longer costs a second retry budget (finding 1). openDB's
  30s transient-ping budget was spent twice against an unreachable server:
  once on the fast-path probe, whose error was discarded, and again on the
  fall-through open. Measured 0s + 19.8s where it was 19.8s twice; the
  returned "uow: ping db: ..." text is unchanged. The probe is now a single
  bounded ping, mirroring the sibling fast path in internal/storage/dolt,
  and the historical open keeps the retry budget it owned at base. Nothing
  is lost by not retrying here: the endpoint is already live when
  openAndInitSchema runs (GetCreateDatabaseProxyServerEndpoint), so the
  probe was never the thing waiting out a starting server.
- Replace the pool-reuse comment's false premise (finding 2). "Nothing in
  the migration path mutates session state" is wrong: six shipped
  migrations clear FOREIGN_KEY_CHECKS and restore it, and the guarded ones
  leave user variables set. State the real invariant instead — one pinned
  session per attempt, paired restores inside each script, pool closed on
  every initSchema failure — and record the forward requirement in
  migrations/README.md, where migration authors will meet it.
- Log why either fast path fell through (finding 3), matching lock.go's
  alreadyConverged idiom, so a fast path that has quietly stopped firing is
  visible under BD_DEBUG instead of silently costing the second session.
- Amend both stale empty-DSN premise comments (finding 5, nit):
  selectProbeDatabase and alreadyConverged now describe both entry shapes.

Finding 4 (a server-gated assertion on open-path selection) is left as the
follow-up that the synthesis and the scorecard both marked optional.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

---------

Co-authored-by: plato <plato@ghosttrack.com>
Co-authored-by: Eddie the Engineer <julianknutsen@users.noreply.github.com>
Co-authored-by: Claude Opus 5 <noreply@anthropic.com>
2026-09-12 09:38:01 -07:00

5.1 KiB

Writing migrations against Dolt

Migration files here are frozen once merged (scripts/check-migration-hygiene.sh check C): editing one forks fresh clones from upgraded clones through the recorded content hash. A migration that FAILS on drifted databases cannot be fixed forward with a new migration either, because the failing file aborts the pass before any higher version runs. The escape hatch is a pre-migration repair in Go, keyed to the pending version — see the header of ../migration_repairs.go and the preMigrationRepairs registry.

Measured Dolt behaviours

These were measured against real Dolt (2.1.2 and 2.2.3) and confirmed against Dolt/GMS source while fixing migration 0058. Each one has already cost a redesign; they are recorded here so the next author does not re-derive them.

DDL is not transactional across statements. Each DDL statement implicitly commits, so START TRANSACTION … ALTER … ROLLBACK leaves the table altered. A destructive multi-statement rebuild cannot be made atomic by wrapping it. Make each step individually guarded and resumable instead.

A foreign key on the base column of a stored generated column is rejected — on ADD and at CREATE TABLE time.

Error 1105 (HY000): Cannot add foreign key on the base column of a stored
generated column.

The restriction is asymmetric: key-then-generated-column is accepted, generated-column-then-key is not. That asymmetry is the only reason a table can legitimately hold both, and it dictates the order of any rebuild. This is what blocked 0058, whose two ADD CONSTRAINTs target the columns feeding depends_on_id's COALESCE.

Foreign key constraint names are database-scoped, not table-scoped. Dolt keeps a root-wide collection and duplicate-name lookup ignores the declaring table, so two tables cannot hold the same constraint name even transiently. A build-alongside-and-swap rebuild cannot reuse the real constraint names on its replacement table.

A foreign key holds its backing index hostage.

Error 1553 (HY000): can't drop index 'PRIMARY': needed in foreign key
constraint fk_wisp_dep_issue

Drop the foreign keys before DROP PRIMARY KEY and re-add them last. On the legacy wisp_dependencies shape the primary key is the only issue_id-leading index, so fk_wisp_dep_issue alone blocks the rebuild.

Updating a base column rewrites a stored generated column, and therefore any key over it. Nulling one target column to satisfy a "exactly one target" check recomputes a COALESCE generated column, which can land the row on another row's value in a composite primary key that includes it:

duplicate primary key given: [w2,external:e1]

Normalize after dropping the generated column and its key, not before. (0058 normalizes before, and reaches the abort on any store whose rows collide.)

Restore any session variable you set, in the script that set it. Six migrations here clear FOREIGN_KEY_CHECKS and restore it (0041, 0043, 0047, 0050, 0053, 0058); keep that shape. Since wy-s8ytnw the steady-state open in uow.openAndInitSchema hands the pool that ran the migration straight to the provider for live queries, so a system session variable still changed when a script reports success leaks into ordinary queries. Pairing the SETs inside one script is what makes that safe — initSchemaAttempt pins one session per attempt, so both statements land on the same session, and an attempt that dies between them returns its connection to the pool, which is why the restore cannot be the caller's job. User variables (SET @sql, SET @needs_add) are exempt: nothing outside these scripts reads one, and each assigns before it reads.

The dolt CLI is not a safe harness for guarded migrations, twice over. dolt sql -f/-q/piped-stdin silently no-ops PREPARE/EXECUTE-driven ALTER TABLE — every guarded migration here uses exactly that shape — and dolt sql -q "USE db; <stmt>" exits 0 when the statement fails, so the schema change is refused while the exit code reports success. Verify DDL outcomes by reading INFORMATION_SCHEMA, never an exit code, and test guarded migrations through the embedded (cgo) engine, where PREPARE/EXECUTE behaves normally. See internal/storage/embeddeddolt/migrate_frozen_guard_convergence_test.go.

Testing

sqlmock statement-echo tests cannot exercise any of the above; they assert the statements you wrote, not what Dolt does with them. Anything touching generated columns, constraint validation, or key rebuilds needs a real-Dolt test in internal/storage/embeddeddolt (BEADS_TEST_EMBEDDED_DOLT=1, -tags cgo).

Assert the resulting shape, not SHOW CREATE TABLE text. A legacy table healed by the shipped chain keeps its original column order and its original secondary index names, because nothing reorders columns and Dolt reuses an existing index to back a new foreign key rather than creating a constraint-named one. Text equality against a fresh store fails on correctly healed databases. Compare columns, primary key, unique keys, foreign keys with their referenced tables and actions, checks, and index coverage — see migrate_wisp_dep_forward_repair_test.go.