The upsert that reports success and then explodes
RowsAffected() counts the rows an UPDATE actually changed, not the rows its WHERE clause matched. So writing the same value again reports 0, and any code shaped as if affected == 0 then INSERT takes the "row is missing" branch and hits Error 1062 on the unique key. There are four fixes; the cheapest is a SELECT COUNT before the UPDATE, and the most dangerous is clientFoundRows=true in the DSN, because it silently changes every other RowsAffected() call in the process.
The code looks defensive. It checks whether the update worked before inserting. It is the opposite of defensive.
res, _ := db.Exec("UPDATE sites SET site_domain = ? WHERE account_id = ?", domain, accountID)
if n, _ := res.RowsAffected(); n == 0 {
_, err := db.Exec("INSERT INTO sites (account_id, site_domain) VALUES (?, ?)", accountID, domain)
// Error 1062 (23000): Duplicate entry '42' for key 'uk_account_id'
}
What actually returned zero
Four statements against a real server, 2026-11-18:
+---------------+
| affected_rows |
+---------------+
| 0 | <- UPDATE ... SET site_domain='example.com' WHERE account_id=42 (same value)
+---------------+
+--------------+
| matched_rows |
+--------------+
| 1 | <- SELECT COUNT(*) WHERE account_id=42
+--------------+
+---------------+
| affected_rows |
+---------------+
| 1 | <- same UPDATE with a genuinely different value
+---------------+
ERROR 1062 (23000) at line 36: Duplicate entry '42' for key 'sites.uk_account_id'
The row is there — matched_rows = 1 — and the UPDATE still says 0. The INSERT that follows fails because the unique key is already held by the very row that “wasn’t updated”.
Why the count is about changes
MySQL’s own wording, via the C API: without CLIENT_FOUND_ROWS, the affected-rows value for an UPDATE is the number of rows actually changed; with the flag, it becomes the number of rows matched by the WHERE. Go’s driver exposes it as clientFoundRows=true.
This is not a bug and not a Go quirk. It is the semantics your if is reading.
Four fixes, and what each costs
| Fix | Right when | Cost |
|---|---|---|
SELECT COUNT(*) first, then UPDATE |
You must tell “missing” from “unchanged” | One extra round trip per save |
INSERT … ON DUPLICATE KEY UPDATE |
Pure upsert; the unique key is the identity | Loses “created vs updated” unless you read ROW_COUNT() carefully |
Add updated_at = NOW() to SET |
“Touched” semantics are acceptable | Writes on every call, including no-ops |
clientFoundRows=true in the DSN |
The whole service should mean “matched” | Changes every other RowsAffected() at once |
The second row hides a subtlety worth knowing: ROW_COUNT() for an upsert is 1 for insert, 2 for update, 0 for “matched but unchanged” — which is a third meaning again.
The fix that bites twice
clientFoundRows=true is the tempting one, because it is one line and it makes the failing branch disappear. It also silently rewrites the meaning of RowsAffected() everywhere else: cache invalidation keyed on “0 rows touched”, audit rows that should only appear on real changes, retry logic that treats 0 as a lost update.
We have not measured that blast radius on our own service — see the last section. The honest description is: it is a global semantic change disguised as a DSN parameter.
How you know it worked
- Write the same value twice, assert success both times, then assert
COUNT(*) = 1. The first half fails against the original bug; the second half fails if someone “fixes” it by dropping the unique key. - Force the collision. In a transaction: insert the row, run the update path with the same value, roll back. A green run with no duplicate-key possibility proves nothing.
- Grep for the shape, not the call.
RowsAffected()is fine;RowsAffected() == 0followed byINSERTis the bug. Count the sites before and after the change. - If you switched the DSN flag, re-run every test that touches affected-row counts. That flag has no local scope.
How to undo it
- Wrong branch logic: revert the code, not the schema. No data changed, so the rollback is the previous binary and nothing else.
- Duplicate rows already written: they can only exist if the unique key was also missing. Add the index after de-duplicating, and keep the pre-cleanup dump — the diff is your evidence of how many rows were affected.
clientFoundRowsturned on by mistake: revert the DSN string. It requires a process restart, so plan the window; in-flight connections keep the old behaviour until they are recycled.updated_atwritten on every call: harmless to data, noisy in binlogs and audit trails. If you added it as a fix and then reverted the logic, remove the column write too, or every save keeps looking “changed”.
What we have not verified
- How many call sites in a large Go service carry the
affected == 0 ⇒ INSERTshape. We found it by incident, not by audit; a real count would need an AST pass, not a grep. - The production blast radius of
clientFoundRows. We describe it as risky from reading the driver docs and our own call sites; we have not measured it on a running system. - Whether
ON DUPLICATE KEY UPDATEinteracts badly with generated columns. Our soft-delete table now has one, and we have not re-run the upsert form against it.
The short version, if you only read one line: RowsAffected() answers “did this change anything”, and the upsert you wrote needed “does this exist”. Those are different questions, and the database will not tell you which one you asked. The runnable reproduction lives in the repo.
Related
Questions people actually ask
Why does RowsAffected return 0 when the row exists?
Because MySQL counts rows modified, not rows matched. Writing the same value changes nothing, so the count is zero even though the WHERE clause found the row. With CLIENT_FOUND_ROWS, the same call returns the matched count instead.
Is INSERT … ON DUPLICATE KEY UPDATE always better?
It is better when the operation really is "insert or overwrite" and the unique key is the identity you want. It is worse when you need to distinguish "created" from "updated" for audit or events — the upsert form loses that information.
What breaks if I turn on clientFoundRows?
Every RowsAffected() call in the process changes meaning at once. Any place that used 0 to mean "no rows touched" — soft flags, cache invalidation, audit logging — silently starts firing on no-op writes.
How do I know the fix actually works?
Write the same value twice and assert the second save still returns success, then assert exactly one row exists. The first half catches RowsAffected semantics; the second catches a duplicate insert. One test, both failure modes.
Sources
Last checked: 2026-11-18 · Reproduced on MySQL 8.4.13 (Ubuntu 26.04, WSL) on 2026-11-18: the same-value UPDATE reports affected_rows 0 while COUNT(*) is 1, a real change reports 1, and the duplicate INSERT raises ERROR 1062 with the table-qualified key name.