The perfect programmer, living in a perfect world, would always write programs that anticipate every possible circumstance. Those programs would either always work correctly, or fail “gracefully” by providing comprehensive diagnostic information to the support team and very readable messages to the user.

For a certain class of applications—software supporting life support systems or the space shuttle, for instance—this level of perfection is actually a part of the requirements, because any unexpected failure of the software would be catastrophic. However, in the world of business applications, we usually make certain assumptions about our execution environment—we assume the MySQL server will be running, that our tables have not been dropped, that the host machine is not on fire, and so on. If any of these conditions occurs, then we accept that our application will fail. In many other circumstances, we can and should anticipate potential failures and write code to manage those situations. This is where exception handling comes into play.

When a stored program encounters an error condition, execution ceases and an error is returned to the calling application. That’s the default behavior. What if we need a different kind of behavior? What if, for example, we want to trap that error, log it, or report on it, and then continue execution of our application? For that kind of control, we need to define exception handlers in our programs.

When developing MySQL stored programs, a very common scenario—fetching to the end of a result set—also requires that we define an exception handler.

In this chapter we explain how to create various types of exception handlers and how to improve the readability of error handling by using “named” conditions. We also identify several gaps in exception-handling functionality in MySQL 5, and explore ways of compensating for these omissions.

Introduction to Error Handling

Let’s begin by looking at several examples of stored program error handling.

Consider a simple stored procedure that creates a location record, as shown in Example 6-1.

Example 6-1. Simple stored procedure without error handling

CREATE PROCEDURE sp_add_location
         (in_location VARCHAR(30), 
         in_address1 VARCHAR(30),
         in_address2 VARCHAR(30),
         zipcode     VARCHAR(10))
    MODIFIES SQL DATA
BEGIN
   INSERT INTO locations
     (location,address1,address2,zipcode)
    VALUES 
     (in_location,in_address1, in_address2,zipcode);
END$$

This procedure works fine when the location does not already exist, as shown in the following output:

  mysql> CALL sp_add_location('Guys place','30 Blakely Drv' ,
                    'Irvine CA','92618-20');

  Query OK, 1 row affected, 1 warning (0.44 sec)

However, if we try to insert a department that already exists, MySQL raises an error:

  mysql> CALL sp_add_location('Guys place','30 Blakely Drv',
                    'Irvine CA','92618-20');

  ERROR 1062 (23000): Duplicate entry 'Guys place' for key 1

If the stored procedure is called by an external program such as PHP, we could probably get away with leaving this program “as is.” PHP, and other external programs, can detect such error conditions and then take appropriate action. If the stored procedure is called from another stored procedure, however, we risk causing the entire procedure call stack to abort. That may not be what we want.

Since we can anticipate that MySQL error 1062 could be raised by this procedure, we can write code to handle that specific error code. Example 6-2 demonstrates this technique. Rather than allow the exception to propagate out of the procedure unhandled (causing failures in the calling program), the stored procedure traps the exception, sets a status flag, and returns that status information to the calling program.

The calling program can then decide if this failure warrants termination or if it should continue execution.

Example 6-2. Simple stored procedure with error handling

CREATE PROCEDURE sp_add_location
         (in_location    VARCHAR(30) ,
          in_address1    VARCHAR(30),
          in_address2    VARCHAR(30),
          zipcode        VARCHAR(10),
          OUT out_status VARCHAR(30))
    MODIFIES SQL DATA
BEGIN
  DECLARE CONTINUE HANDLER FOR 1062
     SET out_status='Duplicate Entry';

  SET out_status='OK';
  INSERT INTO locations
    (location,address1,address2,zipcode)
   VALUES 
 (in_location,in_address1,in_address2,zipcode); END;

We’ll review in detail the syntax of the HANDLER clause later in this chapter. For now, it is enough to understand that the DECLARE CONTINUE HANDLER statement tells MySQL that “if you encounter MySQL error 1062 (duplicate entry for key), then continue execution but set the variable p_status to
' Duplicate Entry '.”

As expected, this implementation does not return an error to the calling program, and we can examine the status variable to see if the stored procedure execution was successful. In Example 6-3 we show a stored procedure that creates new department records. This procedure calls our previous procedure to add a new location. If the location already exists, the stored procedure generates a warning and continues. Without the exception handling in sp_add_location , this procedure would terminate when the unhandled exception is raised.

Example 6-3. Calling a stored procedure that has an error handler

CREATE PROCEDURE sp_add_department
       (in_department_name VARCHAR(30),
        in_manager_id   INT,
        in_location     VARCHAR(30),
        in_address1     VARCHAR(30),
        in_address2     VARCHAR(30),
        in_zipcode      VARCHAR(10)
       )
    MODIFIES SQL DATA
BEGIN
    DECLARE l_status VARCHAR(20);

    CALL sp_add_location(in_location,in_address1,in_address2, 
                      in_zipcode, l_status);
      IF l_status='Duplicate Entry' THEN
            SELECT CONCAT('Warning: using existing definition for location ', 
                    in_location) AS warning;
    END IF;

    INSERT INTO departments (manager_id,department_name,location)
    VALUES(in_manager_id,in_department_name,in_location);

END;