This tutorial shows you how to use MySQL handler to handle exceptions or errors encountered in stored procedures.
When an error occurs inside a stored procedure, it is important to handle it appropriately, such as continuing or exiting the current code block’s execution, and issuing a meaningful error message.
MySQL provides an easy way to define handlers that handle from general conditions such as warnings or exceptions to specific conditions e.g., specific error codes.
Declaring a handler
To declare a handler, you use the
DECLARE HANDLER statement as follows:
DECLARE action HANDLER FOR condition_value statement;
If a condition whose value matches the
condition_value, MySQL will execute the
statement and continue or exit the current code block based on the
action accepts one of the following values:
CONTINUE: the execution of the enclosing code block (
EXIT: the execution of the enclosing code block, where the handler is declared, terminates.
condition_value specifies a particular condition or a class of conditions that activates the handler. The
condition_value accepts one of the following values:
- A MySQL error code.
- A standard
SQLSTATEvalue. Or it can be an
SQLEXCEPTIONcondition, which is shorthand for the class of
NOTFOUNDcondition is used for a cursor or
SELECT INTO variable_liststatement.
- A named condition associated with either a MySQL error code or
statement could be a simple statement or a compound statement enclosing by the
MySQL error handling examples
Let’s look into several examples of declaring handlers.
The following handler means if an error occurs, set the value of the
has_error variable to 1 and continue the execution.
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;
The following is another handler; it means that in case any error occurs, rollback the previous operation, issue an error message and exit the current code block. If you declare it inside the
BEGIN END block of a stored procedure, it will terminate stored procedure immediately.
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated'; END;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;
If a duplicate key error occurs, MySQL error 1062 is issued. The following handler issues an error message and continues execution.
DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Error, duplicate key occurred';
MySQL handler example in stored procedures
First, we create a new table named
article_tags for the demonstration:
CREATE TABLE article_tags( article_id INT, tag_id INT, PRIMARY KEY(article_id,tag_id) );
article_tags table stores the relationships between articles and tags. Each article may have many tags and vice versa. For the sake of simplicity, we don’t create
tags tables, as well as the foreign keys in the
Second, we create a stored procedure that inserts a pair of ids of article and tag into the
DELIMITER $$ CREATE PROCEDURE insert_article_tags(IN article_id INT, IN tag_id INT) BEGIN DECLARE CONTINUE HANDLER FOR 1062 SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg; -- insert a new record into article_tags INSERT INTO article_tags(article_id,tag_id) VALUES(article_id,tag_id); -- return tag count for the article SELECT COUNT(*) FROM article_tags; END
Third, we add tag id 1, 2 and 3 for the article 1 by calling the
insert_article_tags stored procedure as follows:
CALL insert_article_tags(1,1); CALL insert_article_tags(1,2); CALL insert_article_tags(1,3);
Fourth, let’s try to insert a duplicate key to see if the handler is really invoked.
We got an error message. However, because we declared the handler as a
CONTINUE handler, the stored procedure continued execution. As the result, we got the tag count for the article as well.
If we change the
CONTINUE in the handler declaration to
EXIT, we will get only the error message.
DELIMITER $$ CREATE PROCEDURE insert_article_tags_2(IN article_id INT, IN tag_id INT) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException invoked'; DECLARE EXIT HANDLER FOR 1062 SELECT 'MySQL error code 1062 invoked'; DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000 invoked'; -- insert a new record into article_tags INSERT INTO article_tags(article_id,tag_id) VALUES(article_id,tag_id); -- return tag count for the article SELECT COUNT(*) FROM article_tags; END
Now, we can try to add a duplicate key to see the effect.
MySQL handler precedence
In case there are multiple handlers that are eligible for handling an error, MySQL will call the most specific handler to handle the error.
An error always maps to one MySQL error code so a MySQL it is the most specific. An
SQLSTATE may map to many MySQL error codes therefore it is less specific. An
SQLEXCPETION or an
SQLWARNING is the shorthand for a class of
SQLSTATES values so it is the most generic.
Based on the handler precedence’s rules, MySQL error code handler,
SQLSTATE handler and
SQLEXCEPTION takes the first, second and third precedence.
Suppose we declare three handlers in the
insert_article_tags_3 stored procedure as follows:
DELIMITER $$ CREATE PROCEDURE insert_article_tags_3(IN article_id INT, IN tag_id INT) BEGIN DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered'; DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered'; DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000'; -- insert a new record into article_tags INSERT INTO article_tags(article_id,tag_id) VALUES(article_id,tag_id); -- return tag count for the article SELECT COUNT(*) FROM article_tags; END
We now try to insert a duplicate key into the
article_tags table by calling the stored procedure:
As you see the MySQL error code handler is called.
Using named error condition
Let’s start with an error handler declaration.
DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first'; SELECT * FROM abc;
What does the number 1051 really mean? Imagine you have a big stored procedure polluted with those numbers all over places; it will become a nightmare for the maintenance developers.
Fortunately, MySQL provides us with the
DECLARE CONDITION statement that declares a named error condition, which associates with a condition. The syntax of the
DECLARE CONDITION statement is as follows:
DECLARE condition_name CONDITION FOR condition_value;
condition_value can be a MySQL error code such as 1015 or a
SQLSTATE value. The
condition_value is represented by the
After declaration, you can refer to the
condition_name instead of the
So we can rewrite the code above as follows:
DECLARE table_not_found CONDITION for 1051; DECLARE EXIT HANDLER FOR table_not_found SELECT 'Please create table abc first'; SELECT * FROM abc;
This code is obviously more readable than the previous one.
Notice that the condition declaration must appear before handler or cursor declarations.