🚨 Troubleshooting

Troubleshooting: Think of database error messages as the warning lights on a car's dashboard. The light does not tell you what is broken — it tells you where to look.

Think of database error messages as the warning lights on a car's dashboard. The light does not tell you what is broken — it tells you where to look. When the oil-pressure light comes on, the problem is not the light; the mistake is ignoring it and driving on. So if the message already states the problem, why is root-cause analysis a separate job? Because the database reports the **symptom**, not the **cause**. `FOREIGN KEY constraint failed` says "you referenced a row that doesn't exist" — but the real cause is usually a parent record your own test deleted, or two tests sharing an ID. `database is locked` says "someone else is holding it"; the real cause is a setup function that forgot to `COMMIT`. Fix the symptom (drop the constraint, raise the timeout) and the error goes quiet while the cause grows — until it detonates in production instead. You already have this reflex in Java: seeing a `NullPointerException`, you don't just add a null check, you read the stack trace upward asking why that value was null. Do the same with database errors; the difference is that SQL gives you no stack trace — your evidence is the query itself, the affected row count, and whether a transaction was left open. For QA this section targets the nastiest class of failure in the field: the mistakes that raise NO error at all. `UNIQUE constraint failed` at least shouts; an UPDATE that silently touches 0 rows says nothing and your test goes green. Hence the rule: don't settle for catching exceptions — assert on the affected row count too.

SQL Error Dictionary

You are inserting a duplicate value into a column with a UNIQUE constraint (e.g., email, username). A PRIMARY KEY violation produces the same error.

You are trying to insert a row that references a value that does not exist in the parent table. E.g., adding an order with a non-existent user_id.

You are inserting NULL into a column with a NOT NULL constraint, or omitting a column that has no DEFAULT and is NOT NULL.

SQL syntax mistake: misspelled keyword (FORM instead of FROM), missing comma, extra parenthesis, or using a reserved word as a column/table name.

The query references a table that does not exist. The table may not have been created, misspelled, or created in a different database connection.

In a JOIN query, the same column name exists in multiple joined tables and it is not specified which table the column comes from.

The number of columns listed in the INSERT does not match the number of values supplied in VALUES.

Another active transaction holds a lock (usually an Exclusive lock from UPDATE/DELETE) on the target row/table, and your current query timed out waiting for it to release.

Database Troubleshooting and Constraints

QA engineers frequently encounter these common constraint and lock errors in automated pipelines: - **Lock Wait Timeout Exceeded**: Occurs when concurrent processes attempt to modify the same rows. If a previous test leaves a transaction open (fails to run COMMIT/ROLLBACK), subsequent tests will hang waiting for row locks and eventually time out. - **FOREIGN KEY Constraint Failed**: Triggered when attempting to write a child record referencing an ID that does not exist in the parent table. Fix by creating the parent record first.

🎬 Lock Wait Timeout: Who Is Holding the Lock?

Transaction A (no COMMIT)