Error Handling

In this first article in a three-part series, you will learn how to create various types of exception handlers. It is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O’Reilly; ISBN: 0596100892). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

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.

A Simple First Example

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;

{mospagebreak title=Handling Last Row Conditions}

One of the most common operations in a MySQL stored program involves fetching one or more rows of data. You can do this in a stored program through the use of a cursor (explained in Chapter 5). However, MySQL (and the ANSI standard) considers an attempt to fetch past the last row of the cursor an error. Therefore, you almost always need to catch that particular error when looping through the results from a cursor.

Consider the simple cursor loop shown in Example 6-4. At first glance, you might worry that we might inadvertently have created an infinite loop, since we have not coded any way to leave the dept_loop loop.

Example 6-4. Cursor loop without a NOT FOUND handler

CREATE PROCEDURE sp_fetch_forever()
 
READS SQL DATA
BEGIN
   
DECLARE l_dept_id INT;
   
DECLARE c_dept CURSOR FOR
           
SELECT department_id
             
FROM departments;

    OPEN c_dept;
   
dept_cursor: LOOP
       
FETCH c_dept INTO l_dept_id;
   
END LOOP dept_cursor;
   
CLOSE c_dept;
END

Bravely, we run this program and find that the seemingly infinite loop fails as soon as we attempt to fetch beyond the final row in the result set:

  mysql> CALL sp_fetch_forever();
 
ERROR 1329 (02000): No data to FETCH

Since we likely want to do something with the data after we’ve fetched it, we cannot let this exception propagate out of the procedure unhandled. So we will add a declaration for a CONTINUE HANDLER in the procedure, setting a flag to indicate that the last row has been fetched. This technique is shown in Example 6-5.

Example 6-5. Cursor loop with a NOT FOUND handler

1 CREATE PROCEDURE sp_not_found( )
2     READS SQL DATA
3 BEGIN
4     DECLARE l_last_row INT DEFAULT 0;
5     DECLARE l_dept_id INT;
6     DECLARE c_dept CURSOR FOR
7           SELECT department_id
8             FROM departments;
9    /* handler to set l_last_row=1 if a cursor returns no more rows */
10   DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1;
11
12   OPEN c_dept ;
13   dept_cursor: LOOP
14        FETCH c_dept INTO l_dept_id;
15         IF (l_last_row=1) THEN
16             LEAVE dept_cursor;
17         END IF;
18         /* Do something with the data*/
1 9
20   END LOOP dept_cursor ;
21   CLOSE c_dept;
2 2
23 END ;

In plain English, the handler on line 10 says “When a fetch from a cursor returns no more rows, continue execution, but set the variable l_last_row to 1.” After retriev ing each row, we check the l_last_row variable and exit from the cursor loop if the last row is returned. Without this handler, our cursor loop will fetch too many times and raise an exception.

Now that you have seen two simple examples of declaring handlers for error situations that you can anticipate, let’s explore this functionality in more detail.

{mospagebreak title=Condition Handlers}

A condition handler defines the actions that the stored program is to take when a specified event—such as a warning or an error—occurs.

Here is the syntax of the DECLARE HANDLER command:

  DECLARE {CONTINUE | EXIT} HANDLER FOR
      {SQLSTATE sqlstate_code| MySQL error code| condition_name}
     
handler_actions

Note that handlers must be defined after any variable or cursor declarations, which makes sense, since the handlers frequently access local variables or perform actions on cursors (such as closing them). They must also be declared before any executable statements. Chapter 4 includes more details on the rules governing the positioning of statements within a block.

The hander declaration has three main clauses;

  1. Handler type ( CONTINUE , EXIT )
  2. Handler condition ( SQLSTATE , MySQL error code, named condition) 
  3. Hander actions

 Let’s look at each of these clauses in turn.

Types of Handlers

Condition handlers can be one of two types:

EXIT

When an EXIT handler fires, the currently executing block is terminated. If this block is the main block for the stored program, the procedure terminates, and control is returned to the procedure or external program that invoked the proce dure. If the block is enclosed within an outer block inside of the same stored program, control is returned to that outer block.

CONTINUE

With a CONTINUE handler, execution continues with the statement following the one that caused the error to occur.

In either case, any statements defined within the hander (the handler actions) are run before either the EXIT or CONTINUE takes place.

Let’s look at examples of both types of handlers. Example 6-6 shows a stored procedure that creates a department record and attempts to gracefully handle the situation in which the specified department already exists.

Example 6-6. Example of an EXIT handler

1 CREATE PROCEDURE add_departmen t
2       (in_dept_name VARCHAR(30),
3        in_location VARCHAR(30),
4        in_manager_id INT)
5     MODIFIES SQL DATA
6 BEGIN
7     DECLARE duplicate_key INT DEFAULT 0;
8     BEGIN
9          DECLARE EXIT HANDLER FOR 1062 /* Duplicate key*/ SET duplicate_key=1;
1 0
11         INSERT INTO departments (department_name,location,manager_id )
12         VALUES(in_dept_name,in_location,in_manager_id);
1 3
14         SELECT CONCAT(‘Department ‘,in_dept_name,’ created’) as "Result" ;
15     END;
1 6
17    IF duplicate_key=1 THE N
18         SELECT CONCAT(‘Failed to insert ‘,in_dept_name,
19           ‘: duplicate key’) as "Result";
20    END IF;
21 END$$

Let’s examine the logic for Example 6-6:

Lines(s)

Explanation

7

Declare a status variable that will record the status of our insert attempt.

8-15

This BEGIN-ENDblock encloses the INSERTstatement that will attempt to create the department row. The block includes the EXIThandler that will terminate the block if a 1062 error occurs.

11

Attempt to insert our row—if we get a duplicate key error, the handler will set the variable and terminate the block.

14

This line executes only if the EXIThandler did not fire, and reports success to the user. If the handler fired, then the block was terminated and this line would never be executed.

17

Execution will then continue on this line, where we check the value of the variable and—if the hander has fired—advise the user that the insert was unsuccessful.

Following is the output from this stored procedure for both unsuccessful and successful execution:

  MySQL> CALL add_department(‘OPTIMIZER RESEARCH’,’SEATTLE’,4) //

  +—————————————-+
  | Result                                 |
  +—————————————-+
  | Failed to insert OPTIMIZER RESEARCH: duplicate key                              |
  +—————————————-+
 
1 row in set (0.02 sec)

  MySQL> CALL add_department(‘CUSTOMER SATISFACTION’,’DAVIS’,4);

  +—————————————-+
  | Result                                 |
  +—————————————-+
  | Department CUSTOMER SATISFACTION
created                                    |
  +—————————————-+
  1 row in set (0.00 sec)

Example 6-7 provides an example of the same functionality implemented with a CONTINUE handler. In this example, when the handler fires, execution continues with the statement immediately following the INSERT statement. This IF statement checks to see if the handler has fired, and if it has, it displays the failure message. Other wise, the success message is displayed.

Example 6-7. Example of a CONTINUE handler

CREATE PROCEDURE add_department
       (in_dept_name VARCHAR(30),
        in_location VARCHAR(30),

        in_manager_id INT)
  MODIFIES SQL DATA
BEGIN
    DECLARE duplicate_key INT DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR 1062 /* Duplicate key* /
            SET duplicate_key=1;

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

    IF duplicate_key=1 THEN
         SELECT CONCAT(‘Failed to insert ‘,in_dept_name,
             ‘: duplicate key’) as "Result";
    ELSE
              SELECT CONCAT(‘Department ‘,in_dept_name,’ created’) as "Result";
    END IF;
END$$


EXIT or CONTINUE?

The choice between creating an EXIT handler and creating a CONTINUE handler is based primarily on program flow-of-control considerations.

An EXIT handler will exit from the block in which it is declared, which precludes the possibility that any other statements in the block (or the entire procedure) might be executed. This type of handler is most suitable for catastrophic errors that do not allow for any form of continued processing.

A CONTINUE handler allows subsequent statements to be executed. Generally, you will detect that the handler has fired (through some form of status variable set in the han dler) and determine the most appropriate course of action. This type of handler is most suitable when you have some alternative processing that you will execute if the exception occurs.


{mospagebreak title=Handler Conditions}

The handler condition defines the circumstances under which the handler will be invoked. The circumstance is always associated with an error condition, but you have three choices as to how you define that error:

  1. As a MySQL error code.
  2. As an ANSI-standard SQLSTATE code.
  3. As a named condition. You may define your own named conditions (described in the later section “Named Conditions”) or use one of the built-in conditions SQLEXCEPTION , SQLWARNING , and NOT FOUND .

MySQL has its own set of error codes that are unique to the MySQL server. A handler condition that refers to a numeric code without qualification is referring to a MySQL error code. For instance, the following handler will fire when MySQL error code 1062 (duplicate key value) is encountered:

  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1;

SQLSTATE error codes are defined by the ANSI standard and are database-independent, meaning that they will have the same value regardless of the underlying database. So, for instance, Oracle, SQL Server, DB2, and MySQL will always report the same SQLSTATE value (23000) when a duplicate key value error is encountered. Every MySQL error code has an associated SQLSTATE code, but the relationship is not one-to-one; some SQLSTATE codes are associated with many MySQL codes; HY000 is a general-purpose SQLSTATE code that is raised for MySQL codes that have no specific associated SQLSTATE code.

The following handler will fire when SQLSTATE 23000 (duplicate key value) is encountered:

  DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SET duplicate_key=1;


SQLSTATE or MySQL Error Code?

In theory, using the SQLSTATE codes will make your code more portable to other database platforms and might therefore seem to be the best choice. However, there are a number of reasons to use MySQL error codes rather than SQLSTATE codes when writing MySQL stored programs:

  • In reality, it is unlikely that you will move your stored programs to another RDBMS. The Oracle and SQL Server stored program languages are totally incompatible with MySQL. The DB2 stored program language is somewhat compatible (both are based on the SQL:2003 standard). It is very likely, however, that you will use MySQL-specific syntax as you write your application, which will prevent your stored code from being portable.
  • Not all MySQL error codes have SQLSTATE equivalents. Although every MySQL error code is associated with some SQLSTATE error code, often it will be a general-purpose SQLSTATE that is not specific (such as HY000). Therefore, you will almost certainly have to code some handlers that refer directly to MySQL error codes. You’ll probably find that the advantages of using a consistent handler format will outweigh the theoretical portability advantage of SQLSTATE error codes.

We will, for the most part, use MySQL error codes in this book.


When the MySQL client encounters an error, it will report both the MySQL error code and the associated SQLSTATE code, as in the following output:

  mysql> CALL nosuch_sp();

  ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist

In this case, the MySQL error code is 1305 and the SQLSTATE code is 42000.

Table 6-1 lists some of the error codes you might expect to encounter in a MySQL stored program together with their SQLSTATE equivalents. Note, again, that many MySQL error codes map to the same SQLSTATE code (many map to HY000, for instance), which is why you may wish to sacrifice portability and use MySQL error codes— rather than SQLSTATE codes—in your error handlers.

Table 6-1. Some common MySQL error codes and SQLSTATE codes

MySQL error code SQLSTATE code Error message
1011 HY000 Error on delete of ‘%s’ (errno: %d)
1021 HY000 Disk full (%s); waiting for someone to free some space . . .
1022 23000 Can’t write; duplicate key in table ‘%s’
1027 HY000 ‘%s’ is locked against change
1036 HY000 Table ‘%s’ is read only
1048 23000 Column ‘%s’ cannot be null
1062 23000 Duplicate entry ‘%s’ for key %d
1099 HY000 Table ‘%s’ was locked with a READ lock and can’t be updated
1100 HY000 Table ‘%s’ was not locked with LOCK TABLES
1104 42000

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

1106 42000 Incorrect parameters to procedure ‘%s’
1114 HY000 The table ‘%s’ is full
1150 HY000 Delayed insert thread couldn’t get requested lock for table %s
1165 HY000 INSERT DELAYED can’t be used with table ‘%s’ because it is locked with LOCK TABLES
1242 21000 Subquery returns more than 1 row
1263 22004

Column set to default value; NULL supplied to NOT NULL column ‘%s’ at row %ld

1264 22003 Out of range value adjusted for column ‘%s’ at row %ld
1265 1000 Data truncated for column ‘%s’ at row %ld
1312 0A000 SELECT in a stored program must have INTO
1317 70100 Query execution was interrupted
1319 42000 Undefined CONDITION: %s
1325 24000 Cursor is already open

Table 6-1. Some common MySQL error codes and SQLSTATE codes (continued)

MySQL error code 1326 1328

SQLSTATE code 24000 HY000

Error message Cursor is not open Incorrect number of FETCHvariables

1329

2000

No data to FETCH

1336 1337

42000 42000

USEis not allowed in a stored program Variable or condition declaration after cursor or handler declaration

1338

42000

Cursor declaration after handler declaration

1339

20000

Case not found for CASE statement

1348 1357

HY000 HY000

Column ‘%s’ is not updatable Can’t drop a %s from within another stored routine

1358 1362

HY000 HY000

GOTOis not allowed in a stored program handler

1363

HY000

Updating of %s row is not allowed in %s trigger There is no %s row in %s trigger

You can find a complete and up-to-date list of error codes in Appendix B of the MySQL reference manual, available online at http://dev.mysql.com/doc/.

Please check back next week for the continuation of this article.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye