149 lines
13 KiB
Markdown
149 lines
13 KiB
Markdown
# Debugging — Symptom to Cause
|
||
|
||
Work symptom-first. Each chain is ordered by probability, and every step is a check you can run, not a guess. Wrong results outrank slow results: a fast query returning the wrong rows never raises an error.
|
||
|
||
Contents: First Three · Wrong Row Count · Wrong Totals · Query Slow · Was Fast Yesterday · Index Ignored · Hangs · Deadlock · Connection Errors · Constraint Violations · Disk Full · Encoding · Works In One Client · Migration Failed · Replica Disagrees
|
||
|
||
## The Universal First Three
|
||
|
||
1. **Read the actual statement, not the intent.** For ORM-generated SQL, turn on query logging and copy the emitted text; for dynamic SQL, log the final string with parameters bound.
|
||
2. **Count before you optimize.** `SELECT COUNT(*)` on each base table with only its own filters. If a base count already surprises you, the bug is upstream of the join.
|
||
3. **`EXPLAIN (ANALYZE, BUFFERS)`** the real statement with real parameters. A plan for `user_id = 1` can differ from `user_id = 99` when one value is a most-common-value in the stats.
|
||
|
||
## Wrong Row Count (too many, too few, duplicates)
|
||
|
||
1. Strip the query to one table plus its `WHERE`. Add joins back one at a time and re-count after each — the join that changes the count names the bug.
|
||
2. Count grew → 1:N fan-out. Check the join key's uniqueness: `SELECT key, COUNT(*) FROM t GROUP BY key HAVING COUNT(*) > 1`. Fix by pre-aggregating, not by `DISTINCT` (SKILL.md Traps).
|
||
3. Count shrank → a `LEFT JOIN` with a predicate in `WHERE` (now an inner join, with no error), or a `NOT IN` against a subquery containing NULL (SKILL.md rule 6).
|
||
4. Count differs between two "equivalent" queries → collation or case sensitivity: MySQL's `_ci` collations match `'A' = 'a'`, PostgreSQL does not.
|
||
5. Trailing-space equality: `CHAR(n)` pads, and some engines ignore trailing spaces in comparison. `'a ' = 'a'` is true on MySQL `CHAR`, false on PostgreSQL `TEXT`.
|
||
6. Count changes between runs of the same query → concurrent writes, or a non-deterministic `LIMIT` without `ORDER BY`.
|
||
|
||
## Wrong Totals (sums, averages, percentages)
|
||
|
||
- Sum too high → fan-out (above). Verify by comparing `SUM(x)` against `SUM(x) / COUNT(DISTINCT join_key)`-shaped intuition, then rewrite with a pre-aggregated CTE.
|
||
- Average is off → `AVG` skips NULLs, so the denominator is not the row count. `SUM(x) / COUNT(*)` and `AVG(x)` differ whenever `x` is nullable.
|
||
- Money off by cents → `FLOAT`/`REAL` storage. `0.1 + 0.2 != 0.3` accumulates over aggregation; the schema needs `NUMERIC`/`DECIMAL` (SKILL.md rule 7).
|
||
- Integer division: `SELECT 1/2` is `0` in PostgreSQL, MySQL, and SQL Server for integer operands. Cast one side: `x * 100.0 / y`.
|
||
- Percentages don't sum to 100 → rounding each row instead of the total; round once at the end.
|
||
- Totals differ from the dashboard → different timezone boundary for "today" or a soft-delete filter one query applies and the other doesn't.
|
||
|
||
## Query Slow
|
||
|
||
1. Is this query even the problem? Rank by total cost first (SKILL.md rule 9).
|
||
2. `EXPLAIN (ANALYZE, BUFFERS)`: find the node with the largest actual time, not the largest estimate.
|
||
3. Estimated vs actual rows off by more than 10× → stale statistics; `ANALYZE` the table and re-plan.
|
||
4. Sequential scan with a selective filter → the index is missing or disabled (→ Index Ignored).
|
||
5. Correct index, still slow → the query returns too many rows to matter. A million rows through the network is slow no matter the plan; paginate or aggregate server-side.
|
||
6. Fast standalone, slow in the app → parameter sniffing, a different search_path/session setting, or lock waiting rather than work (→ Hangs).
|
||
7. Everything is slow, not one query → server-level: cache hit ratio, connection saturation, or another workload.
|
||
|
||
## "It Was Fast Yesterday"
|
||
|
||
| Cause | Check |
|
||
|---|---|
|
||
| Data crossed a size threshold and the plan flipped | Compare current plan against the shape you remember; look for a switch from index scan to seq scan |
|
||
| Statistics went stale after a bulk load | `ANALYZE`, re-plan; PostgreSQL: `last_analyze` in `pg_stat_user_tables` |
|
||
| An index was dropped or left INVALID by a failed concurrent build | PostgreSQL: `SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid` |
|
||
| Bloat from a long-running transaction | Oldest `xact_start` in `pg_stat_activity` |
|
||
| A new index made writes slow, not reads | Compare index count against write latency; every index is written on every insert |
|
||
| Cache no longer holds the working set | Cache hit ratio dropped below the 99% OLTP threshold |
|
||
| Someone added a `LEFT JOIN` for one column | Read the diff of the query, not just the plan |
|
||
| Version upgrade changed the planner | Compare plans across the versions before blaming data |
|
||
|
||
## Index Exists But Is Not Used
|
||
|
||
Check in this order; the first four cover most cases.
|
||
|
||
1. **Function or cast on the column.** `WHERE lower(email) = ?`, `WHERE created_at::date = ?`, or an implicit cast from a type mismatch all hide the column. Fix the predicate or add an expression index that matches the query text exactly.
|
||
2. **Wrong column order.** A composite `(a, b)` cannot serve `WHERE b = ?` (SKILL.md rule 3).
|
||
3. **Low selectivity.** Matching more than roughly 5-10% of rows, a scan is genuinely cheaper. Confirm with `SELECT COUNT(*) FILTER (WHERE <predicate>) * 100.0 / COUNT(*) FROM t`.
|
||
4. **Stale stats** making the planner think the predicate is unselective. `ANALYZE`, retry.
|
||
5. **`OR` across columns**, leading wildcard `LIKE '%x'`, or a non-C locale with `LIKE 'x%'` in PostgreSQL (SKILL.md Index Strategy).
|
||
6. **Different collation** between the index and the query's comparison (MySQL joins across `utf8mb4_general_ci` and `utf8mb4_0900_ai_ci` cannot use the index).
|
||
7. **The index is invalid or being built.** PostgreSQL `indisvalid = false`; MySQL `SHOW INDEX` `Comment` column.
|
||
8. Prove it before rewriting: force the choice temporarily (`SET enable_seqscan = off` in PostgreSQL, `FORCE INDEX` in MySQL) and compare actual times. If forcing the index is slower, the planner was right — never ship the hint as the fix.
|
||
|
||
## Query Hangs (no result, no error)
|
||
|
||
1. Is it running or waiting? PostgreSQL `pg_stat_activity.wait_event_type = 'Lock'` means waiting; MySQL `SHOW PROCESSLIST` state `Waiting for table metadata lock`.
|
||
2. Waiting → find the blocker (PostgreSQL `pg_blocking_pids(pid)`, MySQL `sys.innodb_lock_waits`) and decide: cancel it, or wait if it is nearly done.
|
||
3. Running with no output → it may be working correctly on too much data; `EXPLAIN` without `ANALYZE` returns instantly and shows the plan it chose.
|
||
4. Client shows nothing while the server is idle → the result is buffered by the driver, or the app never fetched; check the driver's cursor/fetch mode.
|
||
5. `ALTER TABLE` hangs → the lock queue, not the DDL. Every new query is now queued behind it: cancel, set `lock_timeout`, retry.
|
||
6. An open transaction in a REPL or notebook is the most common self-inflicted hang: an uncommitted `BEGIN` in another window holds the lock.
|
||
|
||
## Deadlock Detected
|
||
|
||
- The engine already picked a victim and rolled it back; the error is the report, not the failure. Read the log: PostgreSQL logs both statements, MySQL exposes the last one in `SHOW ENGINE INNODB STATUS`.
|
||
- Root cause is almost always inconsistent lock order across two code paths. Fix by ordering (`ORDER BY id FOR UPDATE`), not by retrying blindly.
|
||
- Deadlocks that appear only under load with no obvious two-row pattern → gap/next-key locks in MySQL REPEATABLE READ, or foreign-key locks taken on the parent row.
|
||
- Every application that writes concurrently needs a bounded retry on deadlock and serialization errors regardless.
|
||
|
||
## Connection Errors
|
||
|
||
| Error | Real cause | Move |
|
||
|---|---|---|
|
||
| "too many connections" / "remaining slots reserved" | App pools × instances exceed the server limit | Size pools from cores, add PgBouncer instead of raising `max_connections` |
|
||
| "connection refused" | Server not listening on that address, or wrong port | Check bind address and port before credentials |
|
||
| "no pg_hba.conf entry" / "Host is not allowed" | Host-based auth rules, not a bad password | Fix the auth rule for the client's network |
|
||
| "password authentication failed" for one app only | Different role than you tested with, or a rotated secret | Compare the role, not the password |
|
||
| "SSL required" / cert errors | Managed providers force TLS | Set the driver's SSL mode; do not disable verification to move on |
|
||
| Connections work then die after minutes | Idle timeout in a proxy/load balancer below the pool's `max_lifetime` | Set pool `max_lifetime` under the infrastructure timeout |
|
||
| Intermittent failures under load only | Pool exhaustion — waiters timing out, not the database refusing | Instrument pool wait time before touching the server |
|
||
|
||
## Constraint Violations
|
||
|
||
- Unique violation on insert that "should not exist" → a soft-deleted row still occupies the value, or the unique index is on a different column set than assumed.
|
||
- Unique violation under concurrency despite a check-then-insert → the check-then-insert race; use `INSERT ... ON CONFLICT`/`ON DUPLICATE KEY` or catch the violation.
|
||
- Foreign key violation on delete → children exist; decide `RESTRICT` vs `CASCADE` deliberately, and confirm the child FK column is indexed (SKILL.md rule 4).
|
||
- Foreign key violation on insert with a valid-looking id → different tenant, or the parent row was inserted in another uncommitted transaction.
|
||
- NOT NULL violation only in production → a default exists in one environment's schema and not the other; diff the schemas, don't reason about them.
|
||
- CHECK violation after a data import → the import brought values the constraint never saw; the constraint is right.
|
||
|
||
## Disk Full / Database Won't Accept Writes
|
||
|
||
1. Locate the consumer before deleting anything: table and index sizes ranked descending.
|
||
2. Common consumers in order: an unpartitioned events/audit/log table, bloat from dead tuples pinned by a long transaction, WAL retained by an inactive replication slot, an abandoned temp/sort spill.
|
||
3. `DELETE` does not return space to the filesystem on PostgreSQL or MySQL/InnoDB — it creates dead rows the vacuum must clean. Dropping a partition does.
|
||
4. Never `DROP` to free space during an incident until the backup is verified restorable.
|
||
5. PostgreSQL specifically: an inactive replication slot retains WAL forever and is the classic unannounced disk killer — check `pg_replication_slots` for `active = false`.
|
||
|
||
## Encoding and Character Problems
|
||
|
||
- `?` or `????` in place of accents/emoji → MySQL `utf8` (3-byte) instead of `utf8mb4`; the column, the table default, AND the client connection charset must all be `utf8mb4`.
|
||
- Mojibake (`é` for `é`) → UTF-8 bytes read as Latin-1 somewhere in the chain; find the single wrong layer rather than re-encoding twice.
|
||
- Import fails on "invalid byte sequence" → the file is not the encoding you declared; detect and convert before loading.
|
||
- Sorting looks wrong → collation, not encoding. `ORDER BY` follows the column's collation; changing it after the fact requires reindexing.
|
||
- Invisible characters (zero-width space, non-breaking space) pasted into data make equality fail with identical-looking strings; compare `length()` against the visible character count.
|
||
|
||
## Works In One Client, Fails In Another
|
||
|
||
| Difference | Check |
|
||
|---|---|
|
||
| Autocommit on/off | The GUI committed; your script left an open transaction |
|
||
| Session settings (`search_path`, `sql_mode`, timezone) | Compare `SHOW ALL` / `SHOW VARIABLES` between sessions |
|
||
| Different role or database | Same host, different privileges: diff the `GRANT`s and any RLS policy on the table |
|
||
| Statement/lock timeouts set per role | A short `statement_timeout` on the app role only |
|
||
| Prepared vs literal statements | The generic plan for a prepared statement can differ from the plan for literals |
|
||
| Driver-side type conversion | The driver casts a parameter to a type the index can't use |
|
||
|
||
## Migration Failed Halfway
|
||
|
||
1. Check the runner's state table first — a "dirty" flag means it knows it stopped mid-way; resolve that before rerunning anything.
|
||
2. MySQL has no transactional DDL: half the statements are already committed. Determine what actually applied by inspecting the schema, not the migration file.
|
||
3. Re-running a partially applied migration usually fails on "already exists". Write the repair as a NEW migration; never edit an applied one.
|
||
4. A migration that timed out may still be running server-side — kill the backend before retrying, or the retry deadlocks against it.
|
||
5. Prevention: run every migration against a restored copy of production-shaped data before it reaches production.
|
||
|
||
## Replica Returns Different Data
|
||
|
||
- Read-after-write on an async replica misses the write that just committed; route read-your-own-writes traffic to the primary.
|
||
- A long analytics query on a replica gets canceled by WAL replay conflicts — that is not a query bug.
|
||
- Replica lag rises with zero write traffic on the primary: the time-based lag metric grows unbounded when there is nothing to replay; alert only while writes are flowing.
|
||
- Counts differ permanently → the replica broke and is no longer replaying, or someone wrote to it. Compare replay position, not row counts.
|
||
|
||
## When You Are Truly Stuck
|
||
|
||
Reduce to the smallest reproduction: one table, minimal columns, the fewest rows that still show the behavior, on a scratch copy. Most "impossible" SQL bugs resolve at the moment the reproduction stops reproducing — the step you removed is the cause.
|