The Upsert That Forgot to Add

Easy

· Database

Incident

SEV-2

BUG-UPSERT

Priority: High · reported by analytics

Time to fix: 10 min

Failing checks on the shipped build

  • deltas accumulate on existing keys

    add, not replace: expected 40312 but got 312

How to work this incident

  1. Read the report: write down the expected behaviour and the actual one in a sentence each.
  2. Reproduce it: run the visible tests and find the failing check that matches the report.
  3. Read the logs and the failing checks for the first value that is wrong — the earliest wrong value is nearer the cause than the loudest error.
  4. Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
  5. Fix the cause rather than the symptom, run the visible tests again, and submit against the hidden ones — then write the root cause down.

Inspired by

Analytics counters

Inspired by analytics counters everywhere: the "ON CONFLICT ADD" upsert that actually does "ON CONFLICT REPLACE". Every flush OVERWRITES the day's count with the latest batch, and the dashboards saw only the last five minutes of every day.

CounterUpdater.java merges a delta into the store. The store (locked) is a plain map.

Code map

class CounterUpdater {
    static void applyDelta(CounterStore store, String key, long delta) {
        store.put(key, delta);
    }
}

Sign in to edit the file, run the tests and submit a fix

A free account — no card, no trial clock. You come straight back here.

The Upsert That Forgot to Add — Java Bug Hunt

Inspired by analytics counters everywhere: the "ON CONFLICT ADD" upsert that actually does "ON CONFLICT REPLACE".

  • Language: Java
  • Layer: Database
  • Difficulty: Easy
  • Concepts: Upserts, Counters
  • Modelled on: Analytics counters
  • Visible tests: deltas accumulate on existing keys; new keys initialise with the delta
  • Reward: 50 XP for a complete fix

Incident

  • Severity: SEV-2 (priority: High)
  • Ticket: BUG-UPSERT
  • Reported by: analytics
  • Time to fix: 10 minutes, the target for an easy incident

Failing checks on the shipped build

  • deltas accumulate on existing keys — add, not replace: expected 40312 but got 312

How to work this incident

  1. Read the report: write down the expected behaviour and the actual one in a sentence each.
  2. Reproduce it: run the visible tests and find the failing check that matches the report.
  3. Read the logs and the failing checks for the first value that is wrong — the earliest wrong value is nearer the cause than the loudest error.
  4. Follow the data through the query or the repository code; compare the rows it returns with the rows the report expects.
  5. Fix the cause rather than the symptom, run the visible tests again, and submit against the hidden ones — then write the root cause down.

Briefing

Inspired by analytics counters everywhere: the "ON CONFLICT ADD" upsert that actually does "ON CONFLICT REPLACE". Every flush OVERWRITES the day's count with the latest batch, and the dashboards saw only the last five minutes of every day.

CounterUpdater.java merges a delta into the store. The store (locked) is a plain map.

Bug report

BUG-UPSERT · Priority: High · Reported by: analytics

applyDelta(store, key, delta):

  • existing key -> ADD delta to the stored value
  • new key -> initialise with delta

Observed: page_views was 40,000 at 23:55 and 312 at midnight — each batch replaced the total.

Logs

[flush] page_views <- 312 (was 40188)

The code as shipped

CounterUpdater.java (editable)

class CounterUpdater {
    static void applyDelta(CounterStore store, String key, long delta) {
        store.put(key, delta);
    }
}

Read-only context: CounterStore.java.

Open the hunt to start the incident clock, edit the files, run the visible tests and submit against the hidden ones, then write the root cause down. More Java bug hunts.