Read-only reader sites: first-party non-promotable replicas (app RO pool + customer ETL/CDC/OLAP)

[colinmollenhour]

Read-only reader sites: first-party non-promotable replicas

Motivation

Two consumers need read-only replicas that reliably follow the active primary across failovers:

1. App read pool — read-only replicas exposed to the WMS via externalTrafficPolicy: Local so client source IPs are preserved (see platform TODO, "External MySQL access for customer ETL/BI" — the ETP: Local requirement applies to any externally exposed MySQL Service because ipBlock NetworkPolicy rules match SNAT'd node IPs otherwise).
2. Customer reader — a third read-only server per group for customer ETL / CDC / OLAP workloads. No backup for this instance: if it is lost, we reschedule it onto another node and it does a fresh clone from the primary. SLA target ≈ 1 hour.

Both are the same operator feature — a non-promotable follower site — differing only in Service exposure and MySQL accounts, which stay platform-owned (GitOps NetworkPolicy allowlist, host-scoped accounts, DNS). The topology mechanics (follow the master, re-clone on loss, health-labeled endpoints) must live in Bloodraven: nothing outside the operator can repoint replication or trigger clones without racing it.

What already exists (~80%)

• role: dr-only (api/v1alpha1/types.go:254-283) — passive follower: replicates from the active primary, never auto-promoted, rejected from splitBrainPolicy.sitePriorities, cloned after primary-candidates during bootstrap.
• Auto-clone of empty sites — startBootstrap / detectEmptySite (internal/controller/topology.go:1991-2042): a site that comes up with an empty data dir is cloned from the primary on a subsequent poll cycle. This is the "reschedule + fresh clone" recovery path; no code needed for the no-backup story. kubectl bloodraven reclone covers manual day-2 forcing.
• Services — per-site mysql-<group>-<site> (reconcileSiteService), group-wide mysql-<group>-primary (selector role=primary) and mysql-<group>-replicas (selector role=replica, healthy=yes) (internal/controller/reconciler.go:1234-1352). The healthy=yes label gating is exactly what ETP: Local needs.
• Unplanned-failover repoint — checkRecovery (topology.go:2240) scans all non-active read-only sites with broken replication and repoints them at the current primary, not just the old primary. A follower whose source died gets repointed automatically.
• credentials.readOnlySecret — managed read-only application account.

Gaps (the actual feature work)

1. Planned-switchover repoint — correctness gap, exists today for dr-only

replicaStatusHealthy (topology.go:2497) checks only IORunning && SQLRunning && SourceHost != "" — it never compares SourceHost to the active primary. After a planned switchover the demoted site keeps serving binlogs, so a third follower stays chained (new-primary → demoted-site → reader) indefinitely: doubled lag, and the reader dies with the demoted site.

Fix — follower source convergence: once a promotion is confirmed writable (and as a periodic invariant in the poll loop), for every non-active site with running replication whose Source_Host ≠ active primary host: STOP REPLICA; CHANGE REPLICATION SOURCE TO ...; START REPLICA. Guards: active site confirmed writable, no split-brain, GTID subset check before repointing (reuse the divergence detection from old-primary recovery). This also fixes the same latent bug for existing dr-only DR sites.

2. Role semantics — new read-only role

dr-only is close but is a DR site: SiteSpec requires lbIP (DNS steering) and taintNodeSelector (app-pod eviction), both meaningless for a reader. Rather than overloading dr-only, add a third SiteRole:

spec:
  sites:
    - name: dc1
      role: primary-candidate
      # ...
    - name: dc2
      role: primary-candidate
      # ...
    - name: rdr
      role: read-only            # NEW
      zone: iad
      storage: { storageClassName: local-path, size: 200Gi }
      # lbIP / taintNodeSelector optional for this role
      serviceTemplate:           # NEW: per-site override (gap 3)
        type: NodePort
        externalTrafficPolicy: Local
      mysqlConf:                 # NEW, optional: per-site overrides (gap 4)
        innodb-buffer-pool-size: "8G"

Semantics (mostly shared with dr-only via IsPromotable() == false):

• Never auto-promoted; never a planned-failover target; rejected from splitBrainPolicy.sitePriorities; fenced if anomalously writable (same as dr-only).
• Does not count toward the ≥2 primary-candidate CEL minimum (unchanged rule).
• Does not participate in DNS steering or the db-readonly taint choreography.
• Never selected as a backup source or clone donor.
• Replication lag / unavailability of a read-only site must not degrade the group Ready condition — surface per-site status (and optionally a separate ReadersHealthy condition + metric) instead. A wedged OLAP reader must not page like a broken standby.
• CRD validation: relax lbIP/taintNodeSelector to optional when role == read-only (XValidation), keep them required for primary-candidate/dr-only for compatibility.

3. Per-site Service exposure

spec.serviceTemplate currently applies one Type to every Service in the group and has no externalTrafficPolicy at all. Needed:

• sites[].serviceTemplate override: type, externalTrafficPolicy, nodePort, annotations — applied to that site's mysql-<group>-<site> Service only.
• externalTrafficPolicy added to the group-level ServiceTemplate too (for the -replicas Service when the app read pool is exposed).
• Primary/site Services keep current defaults (ClusterIP) unless overridden.

4. Nice-to-haves

• sites[].mysqlConf overrides merged over spec.mysqlConf (bigger buffer pool on an OLAP box; longer binlog_expire_logs_seconds if CDC ever tails the reader).
• Per-role replication.maxLagSeconds override so reader lag alerting can be looser than standby lag.
• Document clone-donor impact: a reader re-clone reads the primary at full speed; note expectation (or add a throttle knob later).

Reader loss / recovery flow (target behavior)

Out of scope (platform-owned)

• NetworkPolicy IP allowlists from tenants.git (GitOps), host-scoped ETL accounts ('acme_etl'@'<ip>', REQUIRE SSL), customer DNS names, node provisioning.
• Connection-kill on demote (offline_mode=ON) — separate platform TODO item; may later hook Bloodraven's demotion path.
• Backups of reader sites — explicitly none; recovery is always re-clone.
• Proxy layer (ProxySQL/HAProxy) — rejected in platform decision 2026-07-17.
• Per-tenant filtered readers (replica-side replication filters / partial single-schema replicas) — evaluated and rejected (2026-07-17). The reader is always a full replica; MySQL grant-scoped accounts are the tenant isolation boundary (consistent with the platform ETL/BI decision). A tenant requiring hard data isolation gets their own dedicated failover group instead — filtering never enters the operator.

Acceptance criteria

• CRD accepts role: read-only; lbIP/taintNodeSelector optional for that role; generated CRDs synced to charts/bloodraven/crds/; Helm clusterrole.yaml untouched or mirrored per pre-PR gate.
• Follower source convergence: after a planned switchover, a read-only (and dr-only) site is repointed to the new primary within one poll cycle of writable-confirm (component test + envtest where applicable).
• Unplanned failover: reader repoints via existing checkRecovery path (regression test).
• Empty reader site auto-clones from primary without affecting group Ready; kubectl bloodraven reclone <site> works against a reader.
• Per-site serviceTemplate renders NodePort + externalTrafficPolicy: Local on mysql-<group>-<site> only.
• Reader lag/outage does not flip group Ready; per-site status and metrics still report it.
• Playground: 3rd read-only site in the k3d playground; chaos scenario "kill reader node → auto re-clone → healthy behind Services" (release profile); planned-switchover scenario asserts reader follows the new primary (not chained).
• Docs: docs/ topology + failover pages updated; any new structured-log msg strings added to docs/docs/log-schema.mdx.

---

Diagram sources (.tldr, editable on tldraw.com) live next to the PNGs on the issue-assets/read-only-readers branch — keep that branch alive while this issue is open.

[colinmollenhour]

Proposed chaos scenarios for reader sites

Chaos-engineering pass over this feature. The existing 39 scenarios all assert two-candidate outcomes; none exercise a third non-promotable site, so every scenario below is net-new coverage. Numbering is provisional (R1…R9 → s40+ when automated). Common steady state for all: 3-site group (dc1,dc2 primary-candidates + rdr reader), group Ready=True, replication ON on both followers, healthy=yes on dc2+rdr, counter-app writing.

Blast-radius rule: every injection targets only the reader or only the control plane — except R2/R3 which reuse the existing failover injections and add reader assertions. Abort path for all: chaos.sh recover + kubectl bloodraven reclone rdr is the universal reader reset (≤30s to trigger, clone time to converge).

R1 — Reader loss → auto re-clone (the SLA scenario)

• Inject: scale mysql-main-rdr deployment to 0, delete its PVC, scale back up (simulates node loss + reschedule; pod-kill alone recreates in <5s and tests nothing).
• Hypothesis: operator detects the empty site next poll cycle and re-clones from the primary with zero effect on the writer path.
• Assert: group Ready never flips; no failover event; no db-readonly taint churn; clone completes < cloneTimeout; rdr returns with healthy=yes and correct Source_Host; counter-app row count monotonic throughout.
• Measure (for the 1h SLA): record wall-clock loss→healthy and clone throughput; this is the number the SLA promise rests on.

R2 — Planned switchover: reader must not stay chained

• Inject: annotated planned failover dc1→dc2 (existing s02 path) with reader attached.
• Hypothesis: the follower-convergence step repoints rdr to dc2 within one poll cycle of writable-confirm; without the fix this fails — rdr keeps replicating from demoted dc1 (the gap diagrammed in the issue).
• Assert: rdr Source_Host == dc2 within ≤2 poll cycles; replication never enters RecoveryBlocked; rdr GTID catches up to promotion GTID; negative assertion: at no point does rdr apply events relayed through dc1 after promotion (check Source_Host history, not just final state).
• This scenario is the regression gate for gap Add complete GitHub Actions CI/CD system #1 and should also run with a dr-only site since the fix covers it too.

R3 — Unplanned failover: reader availability + repoint

• Inject: hard-kill dc1 (existing s01 injection, sustained via scale-to-0).
• Hypothesis: during the ~37s failover window the reader keeps serving (stale) reads; after promotion, checkRecovery repoints it to dc2.
• Assert: reader answers SELECTs continuously during the window (staleness allowed, availability required — this is the customer-facing promise); post-failover Source_Host == dc2; healthy=yes drops while replication is broken and returns after repoint; -replicas Service endpoints never go empty (dc2 remains).

R4 — Reader stall must not degrade the group

• Inject: STOP REPLICA SQL_THREAD on rdr only (clean, reversible lag injection; alternative: toxiproxy latency on its replication channel).
• Hypothesis: a wedged OLAP reader is invisible to the failover machinery — lag grows unbounded with zero group-level effect.
• Assert: group Ready stays True for the full soak (≥3× maxLagSeconds); no failover attempt, no anti-flap cooldown consumed; healthy=yes removed → rdr leaves -replicas endpoints while dc2 remains; per-site status + reader-lag metric report the stall (alertable, not page-able). Rollback: START REPLICA SQL_THREAD.

R5 — Anomalously writable reader gets fenced, never promoted

• Inject: SET GLOBAL super_read_only=OFF; read_only=OFF on rdr and write one errant row (reader-scoped split-brain).
• Hypothesis: role semantics hold under the worst input: the reader is fenced like a dr-only loser, flagged divergent, and is never a promotion candidate even when it's the freshest GTID.
• Assert: operator fences rdr (back to super_read_only=ON); errant GTID detected → RecoveryBlocked + re-clone required; split-brain resolution never selects rdr as winner; planned failover targeting rdr is rejected with the role error. Cleanup: reclone rdr.

R6 — Bootstrap ordering with reader present

• Inject: full fresh deploy (reset-mysql path) with the 3-site spec.
• Hypothesis: candidate-first clone ordering holds — the group reaches failover-capable (dc2 cloned, replicating) before the reader is cloned; reader bootstrap is strictly best-effort.
• Assert: clone order dc2 → rdr; Ready=True timestamp precedes reader-clone completion; a reader clone failure (kill it mid-clone once) retries without blocking group Ready or bootstrap phase.

R7 — Primary dies mid-reader-clone

• Inject: trigger R1's re-clone, then hard-kill dc1 while CLONE INSTANCE is in flight.
• Hypothesis: the in-flight clone (donor = dc1) dies with its donor; failover proceeds un-wedged; the clone state machine retries against the new primary rather than wedging on the dead donor (interlock parallel to s19, but for the donor side).
• Assert: failover completes in normal time (~37s); no bootstrap-phase wedge (isFreshDeploy/clone metadata cleaned — this is the known stuck-metadata trap from the playground notes); reader eventually clones from dc2 and goes healthy. This is the most likely scenario to find a real bug.

R8 — externalTrafficPolicy: Local endpoint semantics

• Inject: with the reader Service as NodePort+Local, kill the reader pod; separately, probe the NodePort on a non-reader node.
• Hypothesis: Local semantics are what the NetworkPolicy allowlist design assumes — only the reader's node answers, source IPs are preserved, and a dead reader fails fast (connection refused) instead of silently SNAT-routing to another replica.
• Assert: non-reader nodes refuse the NodePort at all times; during pod-down the reader node refuses (no blackhole/hang > TCP timeout); client source IP visible in performance_schema connections (not node IP); endpoint restored only when healthy=yes returns.

R9 — Convergence as an invariant, not an event

• Inject: manually CHANGE REPLICATION SOURCE on rdr to point at dc2 (the standby) while dc1 is primary — simulating operator drift, a partially-applied runbook, or the pre-fix chained state left over from an upgrade.
• Hypothesis: source convergence is a periodic invariant of the poll loop, so any wrong-source state heals, not just the one created by a switchover.
• Assert: operator repoints rdr back to dc1 within a bounded number of cycles, with a structured log event (new msg → log-schema doc); GTID subset check runs before the repoint (a diverged wrong-source reader must go to RecoveryBlocked, not silently repoint).

Profile mapping & measurement

• smoke: R2, R4 (fast, no clone wait).
• release: + R1, R3, R5, R9.
• full: + R6, R7, R8 (clone-heavy / infra-dependent).
• New metrics worth emitting for these: reader lag gauge, clone duration histogram, source-convergence counter — R1/R2/R4 assert on them, which keeps the metrics honest.

Each automated scenario should land in internal/playground/scenarios/ with the standard Precheck/forensics contract; R2 and R5 double as the regression tests for gaps #1 and #2 in the spec.