PDO Error Handling #php #mysql

PDO Error Handling.md

PDO Error Handling

The first step is to switch on exceptions. When bootstrapping PDO, make sure to do:

$opts = [
  PDO::ATTR_ERRMODE                  => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE       => PDO::FETCH_BOTH,
  PDO::ATTR_EMULATE_PREPARES         => false,
  PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
];

$dbh = new PDO(
  "mysql:host=$host;port=$port;dbname=$name;charset=utf8",
  $user,
  $pass,
  $opts
);

Every time you execute a PDO statement using PDOStatement::execute.

There is a possiblity of an exception being raised.

use PDOException;

try {
  $sth = $dbh->prepare('...');
  $sth->execute();
} catch (PDOExcepton $e) {
  var_dump($e->getCode());
  var_dump($e->getMessage());
  var_dump($e->errorInfo);
}

In the above we demonstrate the 3 most important methods and properties of the exception object.

The returned getCode() gives back a 5 character SQLSTATE code string. This is defined as part of the SQL standard and is meant to be generic across databases. For more information on this code: https://en.wikibooks.org/wiki/Structured_Query_Language/SQLSTATE

The returned getMessage() is a string construced by PDO which has a format of:

SQLSTATE[XXXXX]: DESCRIPTION: YYYY MESSAGE

Where XXXXX is the SQLSTATE code, DESCRIPTION is a PHP supplied description like Syntax error or access violation or <<Unknown error>>, YYYY is the driver specific code, and finally the MESSAGE is the driver returned message.

Driver specific codes are usually more specific than the SQLSTATE code, so you can pattern match on driver specific code in order to dig down to more specific errors. For example this is the list of MySQL driver error codes and how they match to SQLSTATE codes: https://dev.mysql.com/doc/refman/en/error-messages-server.html and here's the one for MariaDB: https://mariadb.com/kb/en/mariadb/mariadb-error-codes/

It is possible to create custom SQLSTATE codes to use for schema-specific errors that your application should handle. In MySQL, you should use the DECLARE ... CONDITION syntax to give human readable names to your custom error codes. Custom error codes should avoid conflicting with existing error codes. The standard SQL specification says that classes beginning with 0, 1, 2, 3, 4, A, B, C, D, E, F and G are reserved for standard-defined classes, while other classes are vendor-specific. It also says that, when the class is standard-defined, subclasses starting with those characters (except for '000') are standard-defined subclasses, while other subclasses are vendor-defined. So depending on your database, look for a range that isn't used now and unlikely to be used in the future. Here's an example use: 50000 for general application errors, and then start at 50001 for specific errors.

I have not found good documentation on what kind of PHP supplied descriptions are available. You can use it as kind of summary description of the error. However you have to use regex to extract it from the message string as it is not discretely provided by other means.

When you use database triggers to enforce constraints, you will probably use the 45000 SQLSTATE code, you can pattern match on 45000 to know whether it's a trigger error or otherwise.

In order to do pattern matching easily, rather than using getMessage() or getCode(), you should instead use the errorInfo array. This provides the SQLSTATE code (still in string type), the driver specific code (in my MySQL tests, the type was integer), and the driver specific message. If you do pattern matching on the SQLSTATE code, your codebase will be somewhat more portable than pattern matching on the driver specific code.

Here is an example of pattern matching against the PDO errors. Make sure to read your database manual and know what kind of errors you're interested in. Note that in PHP it's possible to compare strings and integers for equality.

try {
  // ...
} catch (PDOException $e) {
  switch ($e->errorInfo[1]) {
  case 1062:
    error_log('Violating unique key constraint: ' . $e->errorInfo[2]);
  case 1644:
    // for mysql this is the same as matching on getCode() or errorInfo[0] using '45000'
    error_log('User Defined Error!: ' . $e->errorInfo[2]);
  default:
    throw $e;
  }
}

If the above code exists in some model, you can then rethrow and wrap the exception in a model specific exception, or handle the exception there exactly.

If you're using an ORM that wraps the PDOException, you should always be able to get it using getPrevious().

Handling PDO exceptions properly, can allow a clean propagation of errors from the database to the frontend of a web application, as this allows you to keep the constraints on data where the data lives and is ultimately managed. Then we avoid having to repeat validation logic from database to application.

However it may be even cleaner if the schema and constraints on the data were a commonly understood interface that was imported by frontend, backend and database, and validation code and database schema and database model operations can be automatically generated from the common schema. (Perhaps JSON Schema or schema.org or PostgREST might rescue us from CRUD hell?)