A database can reject a valid-looking request because a row already exists, deny a connection, time out, or encounter a deadlock. Those failures need different responses. Showing a raw exception to the visitor reveals internal details, while ignoring it can claim success for work that never happened. This reference explains explicit error modes, SQLSTATE and driver codes, safe logs, transaction cleanup, and bounded retries. PDO is the main example API; MySQLi error handling is an optional comparison. You will learn to return a useful application result while preserving enough private evidence to diagnose the actual problem.
Set a consistent error mode
Configure PDO::ERRMODE_EXCEPTION explicitly so query failures enter your exception path. Contemporary PDO defaults differ from historical tutorials, and explicit options make the code's intent clear. MySQLi can use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) for exceptions; its defaults also changed across PHP versions. Avoid mixing unchecked false returns and exceptions without documenting which operations use each. Syntax errors, permission failures, and connection errors belong to a controlled application boundary. A missing row from a successful SELECT is a normal result, not necessarily an exception. Keep those outcomes separate in repository methods so callers do not confuse no data with failed database access.
Classify failures by stable information
PDOException exposes code and errorInfo details, with SQLSTATE plus driver-specific information where available. MySQLi exceptions expose their own code and SQLSTATE support under the runtime API. Database vendors differ, so classify errors according to the supported engine rather than matching an English message fragment. A uniqueness violation can become a 'This code is already used' conflict, while a network outage should produce a temporary service response. Not every integrity violation means duplicate email: foreign-key and check failures have different meanings. Translate only recognized, expected cases into friendly domain results and treat unknown cases as unexpected. Keep the original exception available in private diagnostics after translation.
Do not expose raw exception messages
A database message may reveal table names, query fragments, hostnames, usernames, file paths, and submitted values. Return a generic server failure or a carefully translated field conflict rather than echoing getMessage(). Attach a request or incident identifier so support can find the corresponding private log entry. Logs should include the operation name, classified error, safe code, and timing. Avoid logging passwords, connection DSNs containing secrets, complete authentication queries, or confidential field values. Redaction should be deliberate, not a belief that logs are automatically safe because they are on the server. Control log access, retention, and export destinations according to the application's data sensitivity.
Preserve transaction cleanup and original failures
When a transaction fails, roll back if it remains active. A broken connection can cause cleanup to fail too. Production code should preserve the original operation error while separately recording a rollback problem. Do not replace the original error with a vague 'rollback failed' message that hides the first cause. Avoid committing in a finally block; finally runs after failures too and is better suited to resource cleanup. Close or free statement resources under their lifecycle policy. If the commit response is lost, the outcome may be uncertain rather than definitely rolled back. An idempotency key or later reconciliation helps resolve that business state without blindly duplicating the operation.
Recognize conflicts under concurrency
Checking whether an email exists before inserting can improve ordinary messages, but another request can insert the same address between the check and your INSERT. Keep the unique constraint and handle its expected violation. The same principle applies to stock, bookings, and other shared resources. A validation pass is not a reservation. Translate the recognized constraint failure into the feature's conflict response and preserve the database invariant. For public account operations, the message may need to avoid exposing whether a user exists, depending on the threat model. That privacy policy should be consistent across both pre-check and database-conflict paths. Do not relax constraints merely because concurrency makes them occasionally reject requests.
Retry only errors and operations that permit it
A transient deadlock or serialization failure may justify rerunning the complete transaction, while a missing table or wrong password will not improve through repetition. Use bounded attempts, backoff, and a request deadline. Record retries so increasing contention is observable instead of hidden. Ensure the business operation has safe duplicate handling, particularly when the previous commit outcome is unknown. Never retry an external side effect casually. A connection pool or persistent session may also need discarded state after failures; follow its documented behavior. Test failure paths in an isolated database with known fixtures rather than relying on production errors to discover whether cleanup works.
Practice task and worked solution
Create a repository result contract for inserting a customer. The solution distinguishes success, an expected duplicate under the application's privacy policy, and unexpected failure. Configure exception behavior, classify the supported engine's codes, and attach a request identifier to private logs without storing the password or full submitted record. Then wrap a two-write operation in a transaction and test a forced second-write failure. Verify the first write is undone and the original error remains diagnosable. Add a bounded deadlock retry only if the complete operation is safe to repeat. Explain why a syntax error should not be retried.
Continue learning
Review exceptions, PDO, and security.
The PDO error-handling guide describes modes and error information.
Database Error Handling Examples
Each example shows the code, the output it produces and what each part does.
Translate a recognized unique conflict
This PDO MySQL example assumes products.code has a unique constraint and MySQL's duplicate-key driver code 1062. It demonstrates classification, not a portable code for every database.
With a new code the result is Saved. A recognized duplicate code receives a useful conflict message. Other failures receive a generic response and a limited private diagnostic. Real code should distinguish relevant unique constraints if the table has several.
<?php
try {
$stmt = $pdo->prepare('INSERT INTO products (code, name) VALUES (?, ?)');
$stmt->execute(['P-12', 'Pen']);
$result = ['ok' => true];
} catch (PDOException $error) {
$driverCode = $error->errorInfo[1] ?? null;
if ((string) $driverCode === '1062') {
$result = ['ok' => false, 'error' => 'That product code is already used.'];
} else {
error_log('product_insert failed; SQLSTATE=' . $error->getCode());
$result = ['ok' => false, 'error' => 'Unable to save the product.'];
}
}
echo $result['ok'] ? 'Saved' : $result['error'];New code: Saved
Duplicate code: That product code is already used.
Unexpected failure: Unable to save the product.Database Error Handling FAQ
Should I echo PDOException::getMessage()?
No. The message can reveal table names, SQL and server details to visitors and attackers. Translate expected errors, such as a duplicate email, into friendly messages, and keep diagnostics private by logging the full exception for developers.
Is a missing row a database failure?
Not if the SELECT succeeded. A query that runs correctly and finds no row is a normal result, usually shown as a 404 page or a "not found" message. Reserve error handling for real failures, such as a lost connection or invalid SQL.
Does pre-checking uniqueness remove races?
No. Two requests can both check that an email is free and then both insert it. Keep and handle the unique constraint: let the database reject the second insert, catch the duplicate-key error and show a friendly message. A pre-check can still give earlier feedback in the form.
Are SQLSTATE codes identical in every useful detail?
Not quite. SQLSTATE gives a standard five-character class, such as 23000 for integrity constraint violations, but that class covers several different problems. Driver-specific classification still matters: MySQL's error code in errorInfo[1], such as 1062 for a duplicate entry, tells you exactly which one occurred.
Database Error Handling Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
A rollback fails after an insert exception. What should logging preserve?
Database Error Handling Quiz
2 questions to check what you learned.
Why handle a unique constraint even after a pre-check?
- Another request can insert between the check and write
- SELECT always changes data
- Constraints disappear during PHP requests
- HTML escaping replaces uniqueness
Which failure usually should not be retried unchanged?
- A reviewed transient deadlock
- A fixed SQL syntax error
- A classified serialization failure
- An explicitly safe transient operation