Exception Handling

Even if you write such amazing code that it contains no errors and never acts inappropriately, your users might still use your program incorrectly. The result? Situations that cause programs to fail. MySQL provides exceptions to help you catch and handle error conditions.

EXC-01: Handle exceptions that cannot be avoided but can be anticipated

---

If you are writing a program in which you can predict that a certain error will occur, you should include a handler in your code for that error, allowing for a graceful and informative failure.

Example

This recommendation is easily demonstrated with a simple, single-row lookup cursor. An error that often occurs is No data to FETCH, which indicates that the cursor didn't identify any rows. Consider the following function that returns the name of a department for its ID:

 CREATE FUNCTION department_name(in_dept_id INT) RETURNS VARCHAR(30)
 READS SQL DATA
 BEGIN
 DECLARE v_dept_name VARCHAR(30);

 DECLARE dept_csr CURSOR FOR
 SELECT department_name
 FROM departments
 WHERE department_id=in_dept_id;

 OPEN dept_csr;
 FETCH dept_csr INTO v_dept_name;
 CLOSE dept_csr;

 RETURN v_dept_name;
 END;

As currently coded, this function will raise the No data to FETCH error if an invalid department ID is passed in.

 mysql> SELECT department_name(1);
 +--------------------+
 | department_name(1) |
 +--------------------+
 | DUPLIN |
 +--------------------+
 1 row in set (0.00 sec)

 mysql> SELECT department_name(60);
 ERROR 1329 (02000): No data to FETCH

That may be fine for some scenarios, but in this particular case, we simply want to return a special string (No such Department). The program that calls department_name can then decide for itself if it wants or needs to raise an error or simply proceed. In this case, the solution is to add a simple CONTINUE handler:

 CREATE FUNCTION department_name(in_dept_id INT) RETURNS VARCHAR(30)
 READS SQL DATA
 BEGIN
 DECLARE v_dept_name VARCHAR(30);

 DECLARE dept_csr CURSOR FOR
 SELECT department_name
 FROM departments
 WHERE department_id=in_dept_id;

 DECLARE CONTINUE HANDLER FOR NOT FOUND
 SET v_dept_name='No such Department';

 OPEN dept_csr;
 FETCH dept_csr INTO v_dept_name;
 CLOSE dept_csr;

 RETURN v_dept_name;
 END;

EXC-02: Use named conditions to improve code readability

---

Any MySQL programmer worth her salt knows all the MySQL error codes by heart, right? Wrong!

Exception handlers defined against MySQL error codes might work, but they will almost never be easy to read.

The best way to improve the readability of your exception handling routines is to define a named condition for every MySQL error code that you might be anticipating. So instead of the following declaration:

 DECLARE CONTINUE HANDLER FOR 1216 mysql_statements;

you should use the following, more readable pair of declarations:

 DECLARE foreign_key_error CONDITION FOR 1216;

 DECLARE CONTINUE HANDLER FOR foreign_key_error mysql_statements;

EXC-03: Be consistent in your use of SQLSTATE and MySQL error codes in exception handlers

---

You often have the choice between a MySQL error code and an ANSI-standard SQLSTATE code when creating your exception handler. Be as consistent as possible in your choice between the two. In some cases, an explicit SQLSTATE code might not be available for the error you are trying to catch, and you will want to use a MySQL error code. Unless portability is your primary concernand in reality, this will rarely be the casewe recommend that you use MySQL error codes exclusively in your stored programs.

EXC-04: Avoid global SQLEXCEPTION handlers until MySQL implements SIGNAL and SQLCODE features

---

In the initial 5.0 release of MySQL, it is not possible to access the MySQL error code or SQLSTATE code that caused a handler to be invoked. You also can't raise your own exceptions (the SIGNAL/RESIGNAL statements are not yet supported). What this means is that unless your handler is very specific, you won't know exactly why it was raised. Furthermore, you won't have a reliable mechanism for propagating the exception to the calling program.

Under normal circumstances, it can be very helpful to implement a general-purpose exception handler. This handler would acquire all kinds of handy information about the current state. If, however, you are unable to determine the error that was raised, this kind of general-purpose handler is of little use, and it can even cause a loss of useful information. For instance, in the following example, a general-purpose hander is invoked but cannot report accurately the reason it fired:

 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 BEGIN
 SET v_status=-1;
 SET v_message='Some sort of error detected somewhere in the application';
 END;

Given these restrictions, it is best not to create general SQLEXCEPTION handlers. Rather, you should handle only specific, foreseeable errors, and let the calling program handle any unexpected errors.