Saving Without Changes Returns 404? RowsAffected Fooled Me
A no-op form save got a 404 because MySQL counts changed rows, not matched rows. The fix: an existence check only on the zero path.
TL;DR
Hitting Save without changes returned 404 because MySQL reports zero affected rows when nothing actually changes. The default driver setting means zero is ambiguous and doesn't reliably indicate a missing record. The fix adds a quick existence check on zero instead of changing the global DSN setting.
The QA ticket came in around four in the afternoon. MUDE-74 finding number 3: open an entity edit page, hit Save without changing anything, and the API answers 404 Not Found. I refreshed the DB; the row was right there. Saving again with a one-letter title change? 200, success.
Bizarre. The data exists but gets told it doesn't, only when nothing was changed.
Two PUTs That Sent Me Down the Wrong Path
My first guess went straight to the gallery. The QA log showed two PUT requests fired almost together, a few hundred milliseconds apart. That module has a debounced gallery auto-save, so every drag or image pick hits the API on its own.
I thought it was a race condition. In my head, the two PUTs were racing to write the same row, one got skipped, and the code decided the row was gone.
Once I opened the network tab and traced the code, it turned out the two PUTs touch different tables. One hits media, the other entities. No collision at all. The gallery debounce was just a red herring.
What actually tipped me off came from reading the files: api/internal/entityadmin/repository.go and api/internal/announcementadmin/repository.go both have an Update function with the exact same pattern. And both do the same thing after the UPDATE.
A Zero From the Update Doesn't Mean Not Found
Here is the old code, from both files:
res, err := r.db.ExecContext(ctx, query, args...)
if err != nil {
return err
}
n, _ := res.RowsAffected()
if n == 0 {
return ErrNotFound
}
return nilIt looks logical. An UPDATE ... WHERE id = ? that matches no rows means the id doesn't exist. Return a 404.
The problem: in MySQL, affected rows by default counts rows that actually changed, not rows matched by the WHERE clause [1]. Update a row with values identical to what's stored and MySQL considers nothing changed, so the result is 0. The mysql_info() report keeps matched and changed explicitly separate [3].
In the Go driver I use, this is controlled by the clientFoundRows DSN parameter. The default is false [2], and my DSN never sets it. So RowsAffected() follows MySQL's default rule: 0 when the values are identical.
That's what happened when QA hit Save without changing anything. The payload matched the DB exactly, the UPDATE fired, but MySQL reported 0 rows changed. My code translated that straight into ErrNotFound.
There is a subtler trigger too. The updated_at column has second resolution. If a user double-saves within the same second, the timestamp doesn't move. RowsAffected() comes back 0 again, even though the row obviously exists. My regression test that saves twice within one second covers this case.
So zero is ambiguous. It can mean the id genuinely doesn't exist, or the id exists but no column changed.
Pay One SELECT, Only on the Zero Path
The easy option crossed my mind: just add clientFoundRows=true to the DSN. With that flag on, UPDATE returns the number of rows matched by WHERE, not the number changed [1][2]. One DSN change and every zero check instantly becomes an accurate existence check.
I didn't take it.
Changing the DSN changes the semantics of UPDATE for the entire codebase, not just these two repositories. Every other piece of code that relies on the default meaning of "how many rows actually changed" would silently shift behavior. For me that's too much risk to fix two spots.
I personally stayed on the default DSN and paid one extra SELECT, only on the zero path. More explicit, and the blast radius is local.
The fix looks like this in both modules' Update:
n, _ := res.RowsAffected()
if n == 0 {
var one int
err := r.db.GetContext(ctx, &one, "SELECT 1 FROM entities WHERE id = ?", id)
if err != nil {
if errors.Is(err, sql.ErrNoRows) {
return ErrNotFound
}
return err
}
}
return nilThe same pattern went into announcementadmin/repository.go, just with a different table name.
Then I added regression tests for four scenarios: saving unchanged values must return 200, not 404; double-saving within one second must still return 200 because of the tight updated_at resolution; saving with a real change must return 200; and a genuinely missing id must keep returning 404.
QA ran the scenario again: saving without changes now returns 200. A bogus id still gets 404, as it should.
The lesson for me is simple: the zero from RowsAffected() is ambiguous while the DSN stays default. Don't trust it blindly for 404 decisions. Check whether the row actually exists first.