Detailed explanation of error handling examples in MySQL stored procedures

This article uses an example to describe the error handling of MySQL stored procedures. Share with you for your reference, the details are as follows:

When an error occurs in a stored procedure, it is important to handle it appropriately, for example: continuing or exiting the execution of the current code block and issuing a meaningful error message. MySQL provides an easy way to define handlers that handle conditions ranging from general conditions, such as warnings or exceptions, to specific conditions, such as specific error codes. Now let's use the DECLARE HANDLER statement to try to declare a handler. Let's look at the syntax first:

DECLARE action HANDLER FOR condition_value statement;

In the above SQL, if the value of the condition matches condition_value, MySQL executes statement and continues or exits the current code block based on the operation. Where action accepts one of the following values:

• CONTINUE: Continues execution of the enclosing code block (BEGIN ... END).
• EXIT: Handler declares termination of execution of the enclosed code block.

condition_value specifies a specific condition or a class of conditions that activates the handler. condition_value accepts one of the following values:

• A MySQL error code.
• A standard SQLSTATE value or it can be a SQLWARNING, NOTFOUND, or SQLEXCEPTION condition, which is a shorthand for a class of SQLSTATE values. The NOTFOUND condition is used with a cursor or a SELECT INTO variable_list statement.
• A named condition associated with a MySQL error code or SQLSTATE value.

Most importantly, the above sql can be a simple statement or a compound statement surrounded by BEGIN and END keywords. Now that we have that out of the way, let's look at a few examples of declaring handlers. First, when an error occurs in the program, the value of the has_error variable is set to 1 and execution continues:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

Now let's look at when an error occurs, roll back the previous operation, issue an error message, and exit the current code block. If you declare it within the BEGIN END block of a stored procedure, it terminates the stored procedure immediately:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;

The following handler means that if there are no more rows to fetch, in case of a cursor or select into statement, set the value of the no_row_found variable to 1 and continue execution:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;

The following handler issues MySQL error 1062 if a duplicate key error occurs. It issues an error message and continues executing:

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

The examples above may be a bit abstract, so let's create a new table called article_tags and do the following:

USE testdb;
CREATE TABLE article_tags(
  article_id INT,
  tag_id INT,
  PRIMARY KEY(article_id,tag_id)
);

Among them, the article_tags table stores the relationship between articles and tags. Each article may have many tags and vice versa. For simplicity, we will not create the article and tags tables and foreign keys in the article_tags table.

To finish, let's create a stored procedure to insert the article id and tag id into the article_tags table:

USE testdb;
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$$
DELIMITER ;

Then, we add tag IDs 1, 2, and 3 to article ID 1 by calling the insert_article_tags stored procedure, as shown below:

CALL insert_article_tags(1,1);
CALL insert_article_tags(1,2);
CALL insert_article_tags(1,3);

Let's try inserting a duplicate key to check if the handler is actually called:

CALL insert_article_tags(1,3);

Execute the above query statement and get the following results:

mysql> CALL insert_article_tags(1,3);
+----------------------------+
| msg |
+----------------------------+
| duplicate keys (1,3) found |
+----------------------------+
1 row in set
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
1 row in set
Query OK, 0 rows affected

After execution you will receive an error message. However, because we declared the handler as a CONTINUE handler, the stored procedure continues to execute. Therefore, the final tag count value of the article is: 3. Let’s look at a picture:

But if you change CONTINUE in the handler declaration to EXIT, you will just get an error message. The following query statement:

DELIMITER $$
CREATE PROCEDURE insert_article_tags_exit(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 $$
DELIMITER ;

Execute the above query statement and get the following results:

mysql> CALL insert_article_tags_exit(1,3);
+-------------------------------+
| MySQL error code 1062 invoked |
+-------------------------------+
| MySQL error code 1062 invoked |
+-------------------------------+
1 row in set
Query OK, 0 rows affected

Let’s take a look at a picture:

If we use more than one handler to handle an error, MySQL calls the most specific handler to handle the error. This involves the issue of priority, let's take a closer look.

We know that errors are always mapped to a MySQL error code because it is the most specific in MySQL. SQLSTATE can be mapped to many MySQL error codes, so it is not too specific. SQLEXCPETION or SQLWARNING is an abbreviation for the SQLSTATES type value, so it is the most general. Assume that three handlers are declared 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 $$
DELIMITER ;

We then try to insert the duplicate key into the article_tags table by calling a stored procedure:

CALL insert_article_tags_3(1,3);

As shown below, you can see that the MySQL error code handler is called:

mysql> CALL insert_article_tags_3(1,3);
+----------------------------------+
| Duplicate keys error encountered |
+----------------------------------+
| Duplicate keys error encountered |
+----------------------------------+
1 row in set
Query OK, 0 rows affected

Now that we've finished, let's look at using named error conditions. Start with the error handler declaration, as follows:

DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first';
SELECT * FROM abc;

What does the number 1051 mean? Imagine you have a large stored procedure code that uses many numbers like this; it will become a nightmare to maintain the code. Fortunately, MySQL provides us with the DECLARE CONDITION statement to declare a named error condition, which is associated 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 an SQLSTATE value. The condition_value is represented by the condition_name. After the declaration, you can refer to the condition_name instead of the condition_value. So the above code can be rewritten 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 code, but we should note that the condition declaration must appear before the handler or cursor declaration.

Okay, that’s all for this time.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.