Incident Library

IL-0331 Razorpay · 2020-06-08 (published)databaseaws

An RDS MySQL Multi-AZ failover lost 5 seconds of committed data and broke replication on all five read replicas

In an RDS Multi-AZ setup, the RDS endpoint points to a primary instance and a standby in another availability zone keeps MySQL shut down while the primary's EBS volume is copied synchronously to the standby's EBS volume. On failover, networking on the primary is cut, MySQL starts on the standby, the endpoint switches to it and the application reconnects; the old primary is then hard-rebooted onto a new host and EBS sync starts toward the new standby. Data reaches the standby only once it is on the primary's EBS volume. Razorpay had set innodb_flush_log_at_trx_commit to 2, so the InnoDB log was written at every commit but flushed to disk only once a second; the RDS default of 1, which flushes at every commit, is required for full ACID compliance. sync_binlog was set to 1, so every commit was flushed to the binary log at once.

When the master failed over in December 2019, transactions from the last 5 seconds had been committed and written to the binary log but not flushed to the primary's EBS volume, so the new primary never had them. The five read replicas had already applied those rows from the binary log. The application then wrote new rows on the new primary and reused auto-increment ids such as X+1 that the replicas already held, and replication on every replica stopped on duplicate-key errors. Razorpay moved replica traffic to the master and recreated all five replicas, waiting six hours for the first, full snapshot from the new machine.

The next day, trace logs showed records missing from the database. Razorpay pulled the exact queries from the binary logs, followed the sequence of events in the application trace logs and corrected the data in RDS after an 8-hour meeting. After a week of calls with RDS solution architects, snapshots kept from the old replicas confirmed that the lost data had been on the replicas, and innodb_flush_log_at_trx_commit on the master was changed from 2 to 1.

Severity
–
Scope
The API application could not reach its RDS MySQL database for about 110 seconds and returned 5xx errors; replication failed on all five read replicas, whose traffic ran on the master for about six hours; about 5 seconds of transactional data were missing from RDS until corrected by hand
Users affected
–
Region
–
Time to detect
–
Time to resolve
–

Symptoms

On a Friday evening in December 2019, Razorpay's internal monitoring alerted on an unusually high number of 5xx errors. The SRE team found that the main application, called API, could not connect to its RDS MySQL database, but within about 110 seconds the application recovered by itself and the alerts stopped. The RDS logs showed that the master instance had gone through a Multi-AZ failover.

Right after the application recovered, a second set of alerts reported that replication had failed on all five read replicas of the API master, each with a duplicate-key error. To keep stale or wrong data away from users, all replica traffic was moved to the master, whose CPU load rose by 50% because the heavier selects now ran there too.

Recreating the replicas starts from a snapshot of the master. Incremental snapshots of the API database had taken about 15-20 minutes before, but this one was still running after about an hour. AWS support explained that the failover had replaced the old master with a new machine, so the first snapshot from it was a full snapshot; it took six hours.

The next day, AWS support said replication does not usually crash during a Multi-AZ failover. Comparing the database with the application trace logs, Razorpay found records that were in the trace logs but not in the database: about 5 seconds of data, just before the 5xx errors began, were missing from RDS.

Symptom High error rateReplication lagData loss

Root cause

Root cause Configuration change

Component DatabaseDatabase replicationDatabase backups and snapshots
Contributing factor –
Trigger
The master instance of the API application's RDS MySQL database went through a Multi-AZ failover to its standby in another availability zone.

Detection

An internal monitoring alert on unusually high 5xx errors, then alerts that replication had failed on all replicas. The data loss was found the next day by matching the database against the application trace logs.

Remediation

Action items

–

Lessons

With innodb_flush_log_at_trx_commit set to 2, Razorpay had been open to data loss on any failover all along, even though RDS Multi-AZ copies everything on the primary's disk to the standby. The incident also taught the team how RDS runs a Multi-AZ setup and its failovers.

Sources

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database, Database replication, Database backups and snapshots 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.