[Triple Rename]
Legacy MySQL Forensics
Read-only investigations on a legacy MySQL whose failures were silent: the log said OK while the numbers went wrong.
Most legacy dashboards are rebuilt by scheduled events and stored procedures on a managed MySQL, fed by external ETL jobs and spreadsheets owned by other teams.
Investigation and documentation. I diagnosed and measured, and delivered fixes as requests and runbooks to the teams that own the database — I didn't own it.
Revenue columns zeroed across history, half of the hourly refreshes aborting, front-end sales doubling overnight — always with a green log.
01 · Map the machinery
Hourly incremental queues and a daily full rebuild, serialized by an advisory lock, logged step by step, published with a stage table and a triple RENAME swap.
02 · Use _stage as evidence
After each swap, _stage holds the previous build: the fastest proof of a regression and, in practice, the only backup.
03 · Correlate, don't guess
Index-guard outcome vs abort rate; the external job's swap times vs the refresh clock; one load vs exactly twice the rows.
04 · Blast radius first
Read-only simulations of each proposed fix before anything is applied.
05 · Leave a runbook
Diagnosis queries, restore from _stage, how to release a stuck metadata lock.
what you're seeing
Tab A: a timeline with the window when an external ETL leaves a source table empty, the rebuild query, and the three tables of the RENAME swap. Tab B: 24 hourly runs of a 17-step refresh, where an external swap drops an index and a guard has to win a lock race.try this
- 1.Tab A: move the rebuild into the amber window and run it — two columns turn to zero and the log still says OK. Then restore from _stage.
- 2.Tab B: watch which hours abort, then turn on the fixes one at a time.
- 3.Compare the abort rate between “guard ok” and “guard gave up” in the correlation table.
real vs simplified
Both mechanisms happened on the real database and were proven this way. Tables, times and rates are synthetic.! brokeA rebuild ran while an external ETL had emptied a source. LEFT JOIN + COALESCE turned the gap into zeros across all history, and the log said OK.
→ changedStage-then-swap plus a sanity check that refuses a build whose key columns suddenly go to zero.
! brokeAbout half of the hourly runs aborted. An external job replaced a table every two hours without its index; the guard lost the metadata-lock race, a step did a full scan and an EXIT handler killed the remaining steps.
→ changedProved it (about 20× more aborts when the guard lost) and specified four fixes for the job's owner: ship the index with the table, a single-writer handoff, a timeout on the daily job and a day window on a backfill.
! brokeA rule that rebuilds orders in 4-hour windows inherited the offer name from an unrelated earlier purchase, moving re-contact sales out of the SMS channel.
→ changedFixed in the gold procedures with a deterministic tie-break, measured before and after.