How the MariaDB Converge Script Failed to Self-Heal

MHA Manager broke on a MariaDB upgrade, and when it failed it failed quietly… skipping transactions during GTID gap recovery instead of stopping. The last commit to the MHA project was 2013. We replaced it with a deterministic convergence script that runs every 20 seconds, and the testing process uncovered six bugs that would have caused silent data loss in production. Then, a month later, that same convergence script failed to self-heal across 20 clusters during a week-long outage, and we had to recover every database manually.

Why We Replaced MHA

The homelab runs MariaDB in 2-node active-passive clusters across every service… ct0 is primary by default, ct1 is the replica, and Keepalived handles VIP failover. For years, MHA Manager sat on top of that stack handling failover and replica promotion. It worked, mostly, until a MariaDB upgrade broke it.

The failure mode was worse than “it stopped working.” When the primary went down, the replica couldn’t promote. When GTID gaps appeared (binlogs purged on the primary before the replica caught up), MHA’s recovery path silently skipped the missing transactions. No error, no alert, just data loss. KB#8076: Nextcloud database keeps failing networking tracked the symptoms… Nextcloud databases kept dropping, and we weren’t even collecting the MHA logs (/var/log/mha/manager.log) into Loki to diagnose it. andrewcz-org nextcloud was also split-brained.

The decision was to replace MHA entirely. MHA was overkill for a 2-node active-passive setup, the failback scripts were bespoke and fragile, and unmaintained software handling database failover is a liability. KB#8053: Transition to MariaDB convergence script captured the work: a deterministic, GTID-based replication model where a convergence script enforces roles every 20 seconds.

First deploy: three problems

The first deployment went to andrewcz-org firefly, and it immediately surfaced three issues.

The converge timer never fired. The mariadb-converge.timer systemd unit had LastTriggerUScMonotonic=0, which meant systemd couldn’t compute the OnUnitActiveSec next fire time. The timer sat at next_elapse=0 and NextElapseUSecMonotonic=infinity for over a week. ct0 was running read-only the entire time and nobody noticed. The fix was adding an OnCalendar = "*:0/1:0" fallback trigger and AccuracySec = "1s" so the timer fires on a calendar schedule regardless of the monotonic clock state.

GTID error 1236 on the replica. ct1’s gtid_slave_pos was at 0-1-9047 but ct0 had purged binlogs beyond that point. The IO thread errored:

Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged.'

The old approach to error 1236 was SET GLOBAL gtid_slave_pos to skip ahead. But that silently skips transactions… which is exactly the data-loss failure mode we were trying to escape. We decided to implement a full database rebuild path instead: rebuild_from_peer() stream-copies the entire database via mysqldump --all-databases, then re-establishes replication from a known-good position. No silent skips.

IPv6 link-local resolution. getent hosts <hostname> resolved to fe80::... (the IPv6 link-local address), but MariaDB can’t connect to link-local addresses (EINVAL). The fix was adding --protocol=TCP to force IPv4 in the mysql_peer function.

Testing: six bugs that would have caused data loss

With the rebuild path in place, testing across 15 scenarios uncovered six bugs. Fourteen of fifteen scenarios passed; the split-brain scenario (S7) was partially covered and tracked separately in KB#8153.

Bug 1: The replica could never promote. The converge script only allowed ct0 (marked is_primary_eligible) to promote. ct1 could never become primary, so if ct0 went down, the database was read-only until ct0 came back. Fix: removed the is_primary_eligible gate from the peer-unreachable path and added local_was_caught_up() to verify the replica had consumed all available transactions before promoting.

Bug 2: --silent stripped error labels. mysql_local --silent --skip-column-names stripped the Key: prefixes from SHOW SLAVE STATUS\G output, so $GREP "Last_IO_Errno:" matched nothing. Error 1236 was never detected. This was a pre-existing bug from the MHA era. Fix: added mysql_local_labeled() (without --silent) for any query that needs to parse output labels.

Bug 3: Wrong GTID variable for split-brain detection. After promotion, @@gtid_current_pos only shows the single active domain (e.g. 0-2-635, no comma), making comma-detection useless for identifying multi-domain GTID state. Fix: switched to @@gtid_binlog_state which shows all domains (e.g. 0-1-632,0-2-635).

Bug 4: Missing “local writable, peer read-only” branch. After ct0 demoted, ct1 saw ct0 as read-only and fell into the “both nodes read-only” branch, trying to become a replica of a read-only node. Fix: added a new branch… when local is writable and peer is read-only, stay primary and clean up residual slave config.

Bug 5: Error 1451 not handled during rebuild. Only errors 1236 and 1950 triggered the rebuild path. Error 1451 (foreign key constraint) during mysqldump restore was unhandled. Fix: added 1451 as a rebuild trigger.

Bug 6: Second RESET MASTER needed after mysqldump restore. During rebuild_from_peer(), the mysqldump restore writes create local GTID domain entries. On the next converge cycle, these conflict with the primary’s domain, triggering error 1950 in a loop. Fix: added “step 5.5”… a second RESET MASTER after the restore but before re-establishing replication.

The empty-database bug

The most insidious bug wasn’t in the convergence logic. It was in the replicator user’s privileges. mysqldump --all-databases --master-data=2 --single-transaction needs LOCK TABLES for the initial table lock on non-InnoDB metadata. Without it, mysqldump produces DDL-only output (CREATE TABLE statements, zero INSERTs) and returns a non-zero exit code. But the pipe into mysql swallowed the exit code, so rebuild_from_peer() reported success with an empty database.

Fix: added LOCK TABLES to the replicator user’s GRANT. Also added SHOW VIEW and expanded SELECT ON mysql.user to SELECT ON mysql.* because mysqldump --all-databases --master-data=2 failed on system tables without those privileges.

Split-brain: the worst case

KB#8153: MariaDB converge script: handle split-brain where both nodes are writable tracked the scariest scenario. When both ct0 and ct1 can reach each other and both are writable (e.g., after a network partition heals where ct1 promoted itself), both enter ensure_replica_of_peer and create a mutual replication loop. With the rebuild path active, both could execute RESET MASTER simultaneously… catastrophic data destruction.

The local_read_only guard in rebuild_from_peer() prevents the worst case: a writable node never enters the rebuild path. The replication loop self-heals within one converge cycle (20 seconds). The remaining gap (which node demotes first is not deterministic) we documented as a known limitation rather than an active data-loss risk, and we closed the ticket in favor of continued work on KB#8053.

Production deploy and migration

The production deploy to andrewcz-com firefly surfaced one more issue: nixos-rebuild switch doesn’t restart MariaDB unless the X-Restart-Triggers content changes. The triggers file only contained my.cnf, not the ExecStartPost script that creates the replicator user. After deploy, both nodes kept running old MHA-era MySQL with no replicator user, causing split-brain. Fix: added restartTriggers = [ config.services.mysql.postStart ] so any change to the postStart script triggers a MariaDB restart.

Then the migration: Vaultwarden (2 environments) and Ghost (3 environments) transitioned from MHA to the converge template. All 5 came up healthy with GTID replication. andrewcz-com vaultwarden had inode exhaustion on ct0 (888k vector log files from the old MHA template, 100% inodes), which required cleanup before the migration could proceed. All services across all environments now use the converge template. MHA is no longer running anywhere.

What Happened Next: The Converge Script’s Own Failure

When proxmini01 went zombie, it didn’t just take down a hypervisor. It took down one node of every two-node MariaDB replication pair across all five domains. When containers came back up, the converge script that was supposed to heal replication had a bug that prevented it from ever escalating past a failed CHANGE MASTER TO. The result: every database cluster stuck in a loop, unable to self-heal, for over a week.

Recovering all 20 clusters meant understanding four distinct root causes and developing a 15-step playbook we ran manually for each one.

The proxmini crash week took down one node of every MariaDB replication pair. When the containers came back online, the mariadb-converge script was supposed to detect broken replication and either re-establish it or escalate to a full rebuild. Instead, it looped indefinitely on CHANGE MASTER TO and never recovered.

KB#8197: MariaDB converge script bugs + cluster recovery across all domains tracks the root cause analysis, the converge script fixes, and the manual recovery of every cluster across all five domains.

Here’s what the converge script was doing on every cycle:

Detect that replication is broken (GTID positions don’t match)
Try CHANGE MASTER TO to re-establish replication from the peer
Write state file at end of cycle, resetting stuck_count to 0
Next cycle, detect replication is still broken
Try CHANGE MASTER TO again… forever

The script could never escalate from CHANGE MASTER TO to a full rebuild because stuck_count was being wiped on every cycle. And even if it could escalate, the binlog_gap_detected() function would have skipped the check because it only ran when a slave was already configured, which was never the case after RESET SLAVE ALL.

Root Cause

Why MHA had to go

MHA Manager targeted multi-node MySQL replication topologies with complex failover requirements. For a 2-node active-passive cluster, it was overkill… and overkill that nobody maintains is worse than simple code you can read and fix yourself. The bespoke failback scripts we layered on top of MHA added more moving parts without adding reliability.

The specific failure modes all traced back to MHA’s design assumptions no longer matching reality:

GTID gap recovery silently skipped transactions. MHA’s approach to error 1236 was to advance gtid_slave_pos past the gap. In a world where binlogs get purged (which they do, automatically, via binlog_expire_logs_seconds), this means silently losing any transactions that were in the purged binlogs. No error, no alert, just a replica that’s behind and doesn’t know it.
Failover didn’t work. When the primary went down, the replica couldn’t promote because MHA’s promotion logic depended on conditions that weren’t met in a 2-node setup with purged binlogs.
No observability. MHA’s logs weren’t being collected. The /var/log/mha/manager.log file existed but wasn’t shipped to Loki, so when things went wrong, we were debugging blind.

The convergence script approach fixes all three by being simple enough to understand, deterministic enough to trust, and observable because it logs every decision it makes.

Why the converge script failed to self-heal

Four distinct failures compounded into one week-long outage:

1. stuck_count reset by end-of-cycle write. The two-cycle escalation mechanism depended on stuck_count accumulating across cycles. If CHANGE MASTER TO failed on cycle 1, stuck_count would be set to 1, and on cycle 2, the script would see stuck_count >= 1 and escalate to a full rebuild. But main()’s end-of-cycle state file write always set stuck_count=0, overwriting the escalation trigger before it could ever fire. The two-cycle mechanism was completely non-functional.

2. binlog_gap_detected() skipped the check when no slave was configured. The function was supposed to detect when a replica needed binlogs that had already been purged on the primary. But after RESET SLAVE ALL, no slave is configured, so the function’s if check for slave status would always skip the gap detection. This made 160 lines of gap detection code dead on arrival.

3. Last_IO_Errno only populated after a 60-second timeout. The old code had an immediate rebuild path triggered by Last_IO_Errno != 0. But Last_IO_Errno only gets populated after the IO thread times out waiting for a response from the master. With the default slave_net_timeout of 60 seconds and convergence checks running every ~20 seconds, the check would see Last_IO_Errno = 0 for three consecutive cycles before the timeout fired. By then, the stuck_count reset bug had already cleared any escalation state.

4. Binlogs had already been purged before gap detection could catch them. Even if the gap detection had worked, the binlogs the replica needed had already been rotated away during the week-long outage. The replica was trying to replicate from a position that no longer existed on the primary.

The cascade: proxmini01 zombie state caused VIP flapping, which triggered repeated failovers, which created 284 stale Litestream generations, which meant the restore picked the wrong generation, which left databases in a split state, which the converge script couldn’t self-heal because stuck_count was broken and binlog_gap_detected() was a no-op.

The Fix

Replacing MHA: the convergence script

The replacement is a single NixOS module: common_components/mariadb.service.nix. It defines the MariaDB service, the replicator user, the convergence timer, and the convergence script itself.

Architecture: - ct0 is primary by default, ct1 is the replica - Keepalived handles VIP failover - The convergence script runs every 20 seconds via a systemd timer - On error 1236 (GTID gap), error 1451 (FK constraint), or error 1950 (GTID conflict), the script triggers a full database rebuild via mysqldump --all-databases --master-data=2 --single-transaction streamed from the peer

Timer fix:

OnCalendar = "*:0/1:0";
AccuracySec = "1s";

The OnCalendar fallback ensures the timer fires on a calendar schedule regardless of the monotonic clock state. AccuracySec = "1s" prevents systemd from coalescing the timer with other nearby timers.

IPv6 fix:

mysql_peer() {
  mysql --protocol=TCP -h "$peer_host" ...
}

--protocol=TCP forces IPv4, avoiding the EINVAL error on IPv6 link-local addresses.

Replicator privileges:

GRANT REPLICATION SLAVE, REPLICATION CLIENT, LOCK TABLES, SHOW VIEW,
  SELECT ON mysql.* TO 'replicator'@'%';

LOCK TABLES is required for mysqldump --single-transaction to lock non-InnoDB metadata. SHOW VIEW and SELECT ON mysql.* are required for mysqldump --all-databases --master-data=2 to dump system tables.

Split-brain guard:

rebuild_from_peer() {
  if [ "$(mysql_local 'SELECT @@read_only')" != "0" ]; then
    log "Local node is read-only, safe to rebuild"
    # ... rebuild logic ...
  else
    log "Local node is writable, refusing to rebuild (split-brain guard)"
    return 1
  fi
}

The local_read_only check ensures a writable node never enters the rebuild path. This prevents the catastrophic scenario where both nodes execute RESET MASTER simultaneously.

NixOS restart triggers:

restartTriggers = [ config.services.mysql.postStart ];

This ensures any change to the ExecStartPost script (which creates the replicator user) triggers a MariaDB restart. Without it, nixos-rebuild switch leaves the old MySQL running with no replicator user.

Stale replication channel cleanup:

ExecStartPre = [
  "${pkgs.coreutils}/bin/rm -f /var/lib/mysql/master.info /var/lib/mysql/relay-log.info"
];

Stale master.info and relay-log.info files from the old MHA replication channel cause MySQL to auto-start broken replication on boot, hitting error 1950. Removing them in ExecStartPre ensures a clean start.

Fixing the converge script (1894 to 1669 lines)

Bug 1: stuck_count reset. The end-of-cycle write now reads and preserves the current stuck_count value instead of always writing 0. It validates the value with a regex and defaults to 0 if empty or non-numeric.

Bug 2: Malformed state file. The sed/append logic for setting stuck_count=1 could create a state file with only stuck_count=1 and no gtid_io_pos when the file didn’t exist yet. Replaced with a heredoc that always writes both fields. The CHANGE MASTER TO path now queries MySQL for the current Gtid_IO_Pos after START SLAVE and writes both fields together.

Simplification: Removed binlog_gap_detected() (160 lines). The function skipped gap checks when no slave was configured, which was always the case after RESET SLAVE ALL. Since stuck_count escalation already handles binlog gaps (cycle 1: CHANGE MASTER TO fails; cycle 2: stuck_count >= 1 triggers rebuild; total time ~2 minutes), the function was redundant and buggy. Removed entirely.

Simplification: Removed IO/SQL error-specific rebuild paths (45 lines). All failures now go through CHANGE MASTER TO + stuck_count escalation. One path for everything.

Cleanup: 9 dead code items removed, including a fail() function that was never called, general_log = "1" that was enabling logging when the comment said “disable,” and deprecated query_cache settings.

Testing all 6 error scenarios

After the fixes, all 6 error scenarios were tested against the andrewcz-org nextcloud-mariadb cluster:

stuck_count escalation: Stopped replication on ct1, let converge run twice. First cycle: stuck_count=1, CHANGE MASTER TO attempted. Second cycle: stuck_count >= 1, rebuild triggered. After rebuild: stuck_count=0, healthy. PASSED.
Network partition: Stopped MariaDB on ct0. ct1 promoted itself to writable primary. Restarted ct0’s MariaDB. ct0 demoted to replica, replication established. PASSED.
IO thread stop: STOP SLAVE IO_THREAD on ct1. Converge detected stuck replication. stuck_count=1, CHANGE MASTER TO attempted. After START SLAVE: healthy, stuck_count=0. PASSED.
Split-brain: Set ct1 read_only=0 while ct0 was also writable. ct0 demoted to replica. Hit SQL error 1950 (GTID strict mode conflict). Auto-rebuilt from ct1. PASSED.
Replication lag burst: Wrote 103 rows to ct0. ct1 replicated immediately. PASSED.
Binlog gap (stale GTID): Set ct1 gtid_slave_pos to a position before ct0’s oldest binlog. CHANGE MASTER TO failed on first cycle. Second cycle detected IO error and triggered rebuild. PASSED.

Cluster recovery across all domains

All 20 MariaDB clusters across 5 domains were recovered manually using a 15-step process:

Assess, don’t act; start one node, inspect its GTID state
SELECT @@server_id, @@read_only, @@gtid_current_pos, @@gtid_slave_pos, @@gtid_binlog_state
Decide whether to start the second node
Start second node with converge disabled
Compare GTID positions between nodes
Choose which data wins (accept data loss on the node with less recent data)
Set winner read_only=1
Reset loser: STOP SLAVE; RESET SLAVE ALL; RESET MASTER;
Dump winner: mysqldump --all-databases --gtid --master-data=1 --single-transaction --flush-logs
Transfer dump via scp -3 through battlestation (the Proxmox host firewall blocks all outbound except specific known traffic patterns, so direct container-to-container scp on non-standard ports is blocked; scp -3 routes through battlestation which has the needed access)
Restore dump on loser: gunzip -c dump.sql.gz | sudo mysql
Configure replication: CHANGE MASTER TO MASTER_HOST='...', MASTER_USER='replicator', MASTER_PASSWORD='...', MASTER_USE_GTID=slave_pos; START SLAVE;
Set winner read_only=0
Start converge timers
Verify 4+ clean convergence cycles on both nodes

Notable data at stake: vaultwarden (andrewcz-org): ct1 diverged by 160 transactions. ghost (curatingcollections-com): ct1 had all data in both domains, ct0 rebuilt from ct1. kanboard (thepipeline-tv): ct0 was 649K transactions ahead in domain 0-1.

What We Learned

Unmaintained software handling database failover is a liability, not an asset. MHA’s last commit was 2013. Every MariaDB upgrade was a gamble. The complexity it added (bespoke failback scripts, SSH key management, MHA-specific monitoring) was complexity we didn’t need for a 2-node active-passive cluster.
Silent data loss is worse than loud failure. MHA’s GTID gap recovery skipped transactions quietly. The convergence script’s rebuild path is loud… it dumps the entire database, resets replication, and logs every step. When something goes wrong, you know.
Testing uncovered six bugs that would have caused data loss in production. Every one of those bugs was in a code path that MHA either didn’t exercise or silently failed on. The 15-scenario coverage analysis (14/15 fully covered) was worth the time it took.
The local_read_only guard is the single most important line of code in the script. Without it, a split-brain scenario could result in both nodes executing RESET MASTER simultaneously. With it, a writable node refuses to enter the rebuild path. Simple guard, catastrophic consequence if missing.
NixOS restartTriggers only fires when the referenced content changes. If your ExecStartPost script changes but the restartTriggers list doesn’t reference it, nixos-rebuild switch won’t restart the service. This is a footgun for any service that depends on ExecStartPost state.
mysqldump exit codes are swallowed by pipes. mysqldump | mysql reports success even if mysqldump fails, because the exit code of the last command in the pipe is what $? captures. The LOCK TABLES privilege bug produced an empty database and reported success. Always check the exit code of every command in a pipeline, or use set -o pipefail.
Deterministic beats clever. The convergence script doesn’t try to be smart about failover. It runs every 20 seconds, checks the state of both nodes, and enforces the correct role. If something is wrong, it fixes it on the next cycle. No state machines, no event-driven complexity, just a loop that converges.
A self-healing mechanism that can’t self-heal is worse than no mechanism at all. The stuck_count bug meant the converge script would loop forever on CHANGE MASTER TO without ever escalating. If it had simply failed and stopped, we’d have noticed the outage immediately. Instead, it appeared to be “trying” while making zero progress for a week.
Test the failure paths, not just the happy path. The binlog_gap_detected() function was 160 lines of code that never executed under the conditions where it was supposed to run. We had unit tests for replication; we didn’t have tests for “replication breaks and no slave is configured,” which is the exact state that occurs after RESET SLAVE ALL.
Last_IO_Errno has a 60-second reporting delay. The default slave_net_timeout means the IO thread won’t report an error for 60 seconds, but our convergence check runs every ~20 seconds. Lowering slave_net_timeout to 15 seconds means the IO thread times out within one convergence cycle. But the better fix was removing error-specific rebuild paths entirely and relying on stuck_count escalation, which detects failure by comparing GTID positions rather than waiting for error codes.
scp -3 is essential for cross-cluster transfers. The Proxmox host firewall blocks all outbound traffic except specific known patterns, so direct container-to-container scp on non-standard ports is blocked. scp -3 routes the transfer through the local machine (battlestation), which has the needed access to both containers.
Start one node, inspect, then decide. When recovering from a multi-node outage, don’t start both nodes at once. Start one, check its GTID state, then decide whether to start the second. Starting both simultaneously risks split-brain and data loss.