Incident Library

IL-0589 Kickstarter · 2013-03-07databaseaws

Every Kickstarter MySQL replica on Amazon RDS stopped replicating on a duplicate-key error in the backings table

Kickstarter runs managed MySQL on Amazon RDS, whose default replication mode is MIXED: MySQL replicates the statements themselves and switches to row-based replication when it recognises an unsafe query. To give each backer of a project a unique, incremental sequence number, a background job runs an UPDATE on the backings table that uses a user-defined counter variable and is ordered by pledged_at and id.

In the period when the data became inconsistent, a set of transactions that had written to the backings table hung waiting for locks and then committed out of order. MySQL writes a transaction's queries to the binlog at COMMIT, so the replicas replayed the writes in a different order than the primary had written the records to disk. With an explicit ORDER BY this should not have mattered, but a MySQL bug sometimes ignores the ORDER BY clause, so the primary and the replicas each used their own implicit row order for the sequencing UPDATE and gave backings different sequence numbers. Under statement-based replication the difference stayed silent until a later unsafe query was replicated as a full row, which set a sequence value on a replica that already held it in another row and stopped replication with error 1062 on every replica.

Over the next hours the team kept the site stable, limited the effects of the stale replicas, told users about the problem and recovered, and then the whole cycle repeated.

Severity
–
Scope
All MySQL replicas stopped updating; most of Kickstarter's traffic is served from the replicas
Users affected
–
Region
–
Time to detect
–
Time to resolve
–

Symptoms

On Thursday, March 7th, all of Kickstarter's MySQL replicas on Amazon RDS stopped applying updates, not just one replica that could be replaced. Most of Kickstarter's traffic is served from these replicas, so the site ran on stale copies of the database.

SHOW SLAVE STATUS on the replicas showed an error the team had not seen before: "Last_Error: Could not execute Update_rows event on table kickstarter.backings; Duplicate entry '123456-789' for key 'index_backings_on_project_id_and_sequence', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin-changelog.169933, end_log_pos 12969124".

The error led to a range of rows in the backings table where the replicas differed from the primary: backer sequence numbers had been assigned in a different order on the replicas. That data had been inconsistent for days, and a wider search found inconsistent rows more than a month old. Replication broke only when a later unsafe query was replicated as full rows and hit the unique index. After the team recovered and gave the all clear, the same cycle happened again.

Symptom Replication lag

Root cause

Root cause Software defect

Component DatabaseDatabase replication
Contributing factor –
Trigger
A later unsafe query on the backings table made MySQL switch to row-based replication for that query, and replaying the full row on the replicas collided with sequence values that already differed from the primary.

Detection

The team saw that the replicas had stopped updating, and SHOW SLAVE STATUS showed the duplicate-key replication error.

Remediation

–

Action items

–

Lessons

Databases are intricate. In the Rails community they are sometimes treated as simple data stores hidden behind ActiveRecord and ActiveRelation, and Kickstarter's engineers concluded that it pays to learn how the database carries out what it is asked to do.

Sources

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.