Incident Library

IL-0392 Wikimedia Foundation · 2024-09-18database

MariaDB replication of section x1 between eqiad and codfw broke on a missing heartbeat row during a cookbook run

Before the next week's datacenter switchover, Wikimedia ran for the first time a cookbook that changes core databases from eqiad -> codfw replication to circular replication, so data flows back to eqiad when codfw becomes the only active datacenter. On statement-based sections pt-heartbeat writes with REPLACE, an upsert, and the step worked. On the row-based x1 and es sections the REPLACE became an update of an existing row, and because heartbeat rows had been cleaned up, that row existed on codfw servers but not on eqiad ones, so replication on eqiad x1 and es6 stopped with a missing-row error.

The first fix, deleting all heartbeat table rows, moved the x1 problem to codfw and started the user impact at 21:42:19 UTC. At 21:48:19 replication flowed again after heartbeat replication was skipped with SET GLOBAL Replication_wild_ignore_table='heartbeat.%'. The proper fix was to insert the missing heartbeat row on each affected host individually with binary logging off (set sql_log_bin=0): it was tested on es6, applied to es7 before that section was set up, and finally applied on the x1 codfw hosts in place of the ignore rule. The cookbook finished without further problems.

The later investigation traced the cause to a configuration change that, together with the cleanup of extra pt-heartbeat rows set up after Orchestrator, left the x1 and read-write es hosts using ROW format without making pt-heartbeat-wikimedia use STATEMENT for its own session. That was fixed in a later deploy.

Severity
–
Scope
For 6 minutes replication of the x1 section between eqiad and codfw stopped; x1-based services such as reading lists and echo could have served stale data, with no visible errors
Users affected
Clients in the Americas
Region
codfw
Time to detect
–
Time to resolve
6m

Symptoms

On 18 September 2024, while a cookbook was reconfiguring Wikimedia's core MariaDB databases from one-way eqiad -> codfw replication to circular replication ahead of a datacenter switchover, replication in the codfw -> eqiad direction broke on the x1 and es6 sections. Engineers watching the run noticed at 21:28 UTC that the x1 primary was not replicating.

The replication error was "Could not execute Update_rows_v1 event on table heartbeat.heartbeat; Can't find record in 'heartbeat'". The statement-based sections s1 to s8 had completed without problems; x1 and the es hosts used row-based replication, and the row that pt-heartbeat updated did not exist on the eqiad servers.

Deleting the heartbeat table rows fixed eqiad but moved the break to codfw, in the eqiad -> codfw direction that carries real traffic. At 21:42:19 Icinga reported "PROBLEM - MariaDB Replica SQL: x1 on db2196 is CRITICAL: CRITICAL slave_sql_state Slave_SQL_Running: No, Errno: 1062, Errmsg: Could not execute Write_rows_v1 event on table heartbeat.heartbeat: Duplicate entry 180360966 for key PRIMARY". For 6 minutes x1 data could have been served stale to clients in the Americas by x1-based services such as reading lists and echo; no visible errors were produced.

The automation's check after setup was green, because replication broke only after pt-heartbeat was restarted on codfw, and monitoring of circular replication had not been enabled yet, so the first break in the codfw -> eqiad direction was found by people on orchestrator rather than by alerts.

Symptom Replication lag

Root cause

Root cause Configuration change

Component DatabaseDatabase replication
Contributing factor Monitoring blind spotGap in automated testsHarmful manual action
Trigger
Running the cookbook sre.switchdc.databases.prepare, which set up codfw -> eqiad replication and started pt-heartbeat, on the row-based x1 and es sections.

Detection

Engineers watching the first run of the automation saw on orchestrator that the x1 primary was not replicating, because monitoring of circular replication was not yet enabled. The break in the eqiad -> codfw direction was reported by Icinga.

Remediation

Action items

Lessons

Circular replication is not a normal working mode, so the first fix only moved the break to the other side; TRUNCATE would have worked better than DELETE because it always replicates as a statement, but it was not considered because it is more dangerous. Running sql_skip_slave_counter was avoided, since it could have made things worse or lost data. The check of the replication channel should also run once all tasks are complete, not only right after setup. The combination of row-based replication, pt-heartbeat and differing heartbeat rows was not described in earlier maintenance or documentation.

Sources

References

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database, Database replication among them, read-only, from Claude Code or Codex. Its checks cite public reports like this one.

Open kubegap

For AI tools and scripts

The API needs no key. The MCP server offers search_incidents, get_incident, list_incidents, get_topic and vocab; both are explained on API and MCP.