Sales dashboard stuck on numbers from 03:12

On-call incidentEasy15 min

Your task

It is 09:15. The shop's MySQL 8.0 primary, db-primary, has 2 replicas. db-replica-1 serves the sales dashboard, and db-replica-2 serves the API's reads. The sales team pages you:

DashboardStale: newest order on the sales dashboard is 6 h 3 min old (threshold 30 min)

What you see:

  • The dashboard's newest order was placed at 03:12. db-primary has orders up to the minute, and db-replica-2 is less than 1 second behind.
  • SHOW REPLICA STATUS on db-replica-1 shows Replica_IO_Running: Yes, Replica_SQL_Running: No and Seconds_Behind_Source: NULL. The replication delay alert, which fires above 60 seconds, stayed quiet all night.
  • Its error log, at 03:12, has Could not execute Write_rows event on table shop.refunds; Duplicate entry '88100' for key 'refunds.PRIMARY'.
  • At 03:10 a support engineer ran a script that inserted 40 refund records the app had failed to save. They took its database settings from the dashboard's config, and they say the script "worked fine".

How do you get the dashboard current this morning, and what has to happen before db-replica-1 serves anyone again?

It covers

  • Mysql replication
  • Read only replica
  • Replica rebuild