Handler Precedence - MySQL

As we’ve described, MySQL lets you define handler conditions in terms of a MySQL error code, a SQLSTATE error, or a named condition such as SQLEXCEPTION. It is possible, therefore, that you could define several handlers in a stored program that would all be eligible to fire when a specific error occurred. Yet only one handler can fire in response to an error, and MySQL has clearly defined rules that determine the precedence of handlers in such a situation.

To understand the problem, consider the code fragment in Example 6-8. We have declared three different handlers, each of which would be eligible to execute if a duplicate key value error occurs. Which handler will execute? The answer is that the most specific handler will execute.

Example 6-8. Overlapping condition handlers

DECLARE EXIT HANDLER FOR 1062 SELECT 'MySQL error 1062 encountered';
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered';
DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000';

INSERT INTO departments VALUES (1, 'Department of Fred',22,'House of Fred');

Handlers based on MySQL error codes are the most specific type of handler, since an error condition will always correspond to a single MySQL error code.SQLSTATEcodes can sometimes map to many MySQL error codes, so they are less specific. General conditions such asSQLEXCEPTIONandSQLWARNINGare not at all specific. Therefore, a MySQL error code takes precedence over aSQLSTATEexception, which, in turn, takes precedence over aSQLEXCEPTIONcondition.

If multiple exception handlers are eligible to fire upon an error, the most specific handler will be invoked. This means that a MySQL error code handler fires before aSQLSTATE handler, which, in turn, fires before aSQLEXCEPTIONhandler.

This strictly defined precedence allows us to define a general-purpose handler for unexpected conditions, while creating a specific handler for those circumstances that we can easily anticipate. So, for instance, in Example 6-9, the first handler will be invoked if something catastrophic happens (perhaps a jealous colleague drops your database tables), while the second will fire in the more likely event that someone tries to create a duplicate row within your database.

Example 6-9. Example of overlapping condition handling

DECLARE EXIT HANDLER FOR 1062
    SELECT 'Attempt to create a duplicate entry occurred';
DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT 'Unexpected error occurred –
                 make sure Fred did not drop your tables again';

Note, however, that we generally don’t advise creatingSQLEXCEPTIONhandlers until MySQL implements theSIGNALstatement; see “Missing SQL:2003 Features” later in this chapter.