Functional testing: Tier B — Failover test with ProxySQL hook validation

[renecannao]

Tier B: Failover Test

Using the Tier A infrastructure, test actual failover and validate ProxySQL hooks work end-to-end.

Pre-requisites

• Functional testing: Tier A — Core smoke test with real services #43 (Tier A) passes — all smoke tests green

Failover Tests

• Kill master: Stop the MySQL master container
• Detection: Orchestrator detects DeadMaster within configured interval
• Recovery: Orchestrator promotes one of the replicas to master
• ProxySQL pre-hook: Old master set to OFFLINE_SOFT in ProxySQL (verify via SELECT * FROM runtime_mysql_servers)
• ProxySQL post-hook: New master appears in writer hostgroup 10 (verify via SELECT * FROM runtime_mysql_servers)
• ProxySQL runtime: LOAD MYSQL SERVERS TO RUNTIME was executed (traffic routes to new master)
• Traffic validation: Queries through ProxySQL (port 6033) reach the new master
• Replication: Remaining replica replicates from the new master

Graceful Takeover Test

• Graceful takeover: Run orchestrator -c graceful-master-takeover to switch master to a specific replica
• ProxySQL updated: Writer hostgroup points to new master after graceful takeover
• No downtime: Traffic through ProxySQL continues during graceful takeover

Recovery Audit

• Recovery log: /api/v2/recoveries shows the recovery event
• Audit trail: Orchestrator audit table records all steps

Findings

(To be updated as tests run)

Bugs Found

(To be updated — each bug gets its own issue)

Depends on #43 (Tier A). Prerequisite for #45 (Tier C).

[coderabbitai]

🔗 Related PRs

#36 - Add built-in ProxySQL failover hooks [closed]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[renecannao]

Tier B Results — 2026-03-24

Bug Found & Fixed

ProxySQL Admin doesn't support prepared statements (COM_STMT_PREPARE). Our Go MySQL driver sent parameterized queries (? placeholders) as prepared statements, which ProxySQL Admin rejects with "Error 1045: Command not supported".

Fix: Added interpolateParams=true to the DSN in go/proxysql/client.go. This tells the driver to interpolate parameters client-side. Committed directly to master as daceb670.

Graceful Takeover Test: PASS

After fixing the prepared statement issue:

Pre-failover hook:

• ✅ Old master (mysql1) set to OFFLINE_SOFT in ProxySQL
• ✅ LOAD MYSQL SERVERS TO RUNTIME executed

Post-failover hook:

• ✅ New master (mysql2) inserted into writer hostgroup 10
• ✅ Old master moved to reader hostgroup 20 as OFFLINE_SOFT
• ✅ New master removed from reader hostgroup
• ✅ LOAD MYSQL SERVERS TO RUNTIME + SAVE MYSQL SERVERS TO DISK executed

ProxySQL state after takeover:

HG 10: mysql2.infra-mysql84:3306  ONLINE       (new writer)
HG 20: mysql1.infra-mysql84:3306  OFFLINE_SOFT (demoted)
HG 20: mysql3.infra-mysql84:3306  ONLINE       (reader)

MySQL topology after takeover:

mysql2 (master, read_only=0)
├── mysql1 (replica, read_only=1)
└── mysql3 (replica, read_only=1)

Remaining Tier B Tests

• Kill master container (hard failover, not graceful)
• Traffic validation through ProxySQL during failover
• Recovery audit via /api/v2/recoveries

Infrastructure Notes

• ProxySQL admin user is localhost-only by default — had to add radmin user for remote access
• Docker hostnames need /etc/hosts entries when running orchestrator on the host
• MySQL 8.4 uses SHOW REPLICA STATUS (not SHOW SLAVE STATUS)

[renecannao]

Tier B Final Results

Hard Failover: PASS

Stopped mysql1 container (master). Within 1 second:

1. Orchestrator detected DeadMaster
2. Promoted mysql3 via GTID recovery
3. ProxySQL post-hook: mysql3 → writer HG 10, mysql1 → reader HG 20 (OFFLINE_SOFT)
4. mysql2 regrouped under new master
5. Recovery recorded and visible via /api/v2/recoveries

Bug Fixed

interpolateParams=true added to ProxySQL client DSN — committed to master as daceb670.

All Tier B Tests Complete

• Graceful takeover with ProxySQL hook — PASS
• Hard failover with ProxySQL hook — PASS
• Recovery audit via API — PASS

Closing.