October 22, afternoon. An UPDATE went out without its WHERE.
What Happened
The table was system configuration: 400-plus rows of store parameters. The intent was to change one row's value; the statement got pasted into the client and executed. Then the result appeared: Rows matched: 417, Changed: 417. My hands went cold.
Thankfully it was the config table, not orders. Pages started misbehaving immediately — several stores' parameters reset to defaults — and the first messages appeared in the support group chat.
Those Two Hours of Recovery
No backup. That was the killer. In the end we reverse-engineered the batch of UPDATEs from the binlog, exported reverse SQL by position, and ran it back. Two people spent forty minutes cross-checking all 417 rows one by one, restoring the values that should have stayed — twelve of them were genuinely meant to change and had to be picked out separately.
Two hours, start to finish. Before the pages recovered, we posted one line in the group: 'working on it, please hold orders.' Better than fixing in silence.
The Three Things We Added Afterward
- Master-slave replication. One replica; no read/write splitting yet — purely to have a copy we could fail over to instantly. Replication lag is monitored, with an alert past five seconds.
- A full backup at 3 a.m. daily, binlogs stored separately, seven-day retention. After each backup a verification row is written to a table — a script's exit code isn't proof; you need to be able to check the backup's file size and row count.
- Every UPDATE and DELETE goes through a wrapper script that prints the expected affected-row count first and demands one manual confirmation. Direct production clients were revoked; day-to-day queries use a read-only account.
The Rule We Never Wrote Down
Nobody scolded the person who ran the query. The meeting had one line: anyone can slip, so make slips impossible.
Afterward we started asking one more question in SQL reviews: 'what happens if this statement ran without its conditions?' Six months of asking turned it into habit. Once, a new colleague paused right after writing a statement and added the WHERE himself. Nobody prompted him.
The screenshot of those 417 rows is still in the internal docs, titled 'Two Hours.'
