- MySQL Error Handling in Stored Procedures
- Declaring a handler
- MySQL error handling examples
- MySQL handler example in stored procedures
- MySQL handler precedence
- Using a named error condition
- How to try and catch errors in MySQL stored procedures.
- Error Handling in MySQL Stored Procedure
- Introduction to MySQL Error Handling
- Declare an Error Handler
- Stored Procedure Error Handling Examples
- Creating a stored procedure to insert data
- Writing an exit handler for error code 1062
- Inserting duplicate data
- Switching the exiting handler
- MySQL Error Handler Precedence
- Raising Error Conditions with MySQL SIGNAL / RESIGNAL Statements
- MySQL SIGNAL statement
- MySQL RESIGNAL statement
MySQL Error Handling in Stored Procedures
Summary: in this tutorial, you will learn how to use MySQL handler to handle 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:
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 .
The action accepts one of the following values:
- CONTINUE : the execution of the enclosing code block ( BEGIN … END ) continues.
- EXIT : the execution of the enclosing code block, where the handler is declared, terminates.
The condition_value specifies a particular condition or a class of conditions that activate the handler. The condition_value accepts one of the following values:
- A MySQL error code.
- A standard SQLSTATE value. Or it can be an SQLWARNING , NOTFOUND or SQLEXCEPTION condition, which is shorthand for the class of SQLSTATE values. The NOTFOUND condition is used for a cursor or SELECT INTO variable_list statement.
- A named condition associated with either a MySQL error code or SQLSTATE value.
The statement could be a simple statement or a compound statement enclosing by the BEGIN and END keywords.
MySQL error handling examples
Let’s take some examples of declaring handlers.
The following handler set the value of the hasError variable to 1 and continue the execution if an SQLEXCEPTION occurs
The following handler rolls back the previous operations, issues an error message, and exit the current code block in case an error occurs. If you declare it inside the BEGIN END block of a stored procedure, it will terminate the stored procedure immediately.
The following handler sets the value of the RowNotFound variable to 1 and continues execution if there is no more row to fetch in case of a cursor or SELECT INTO statement:
If a duplicate key error occurs, the following handler issues an error message and continues execution.
MySQL handler example in stored procedures
First, create a new table named SupplierProducts for the demonstration:
The table SupplierProducts stores the relationships between the table suppliers and products. Each supplier may provide many products and each product can be provided by many suppliers. For the sake of simplicity, we don’t create Products and Suppliers tables, as well as the foreign keys in the SupplierProducts table.
Second, create a stored procedure that inserts product id and supplier id into the SupplierProducts table:
The following exit handler terminates the stored procedure whenever a duplicate key occurs (with code 1062). In addition, it returns an error message.
This statement inserts a row into the SupplierProducts table. If a duplicate key occurs, the code in the handler section will execute.
Third, call the InsertSupplierProduct() to insert some rows into the SupplierProducts table:
Fourth, attempt to insert a row whose values already exist in the SupplierProducts table:
Here is the error message:
Because the handler is an EXIT handler, the last statement does not execute:
If you change the EXIT in the handler declaration to CONTINUE , you will also get the number of products provided by the supplier:
Finally, call the stored procedure again to see the effect of the CONTINUE handler:
Here is the output:
MySQL handler precedence
In case you have multiple handlers that handle the same error, MySQL will call the most specific handler to handle the error first based on the following rules:
- An error always maps to a MySQL error code because in 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 rules, MySQL error code handler, SQLSTATE handler and SQLEXCEPTION takes the first, second and third precedence.
Suppose that we have three handlers in the handlers in the stored procedure insert_article_tags_3 :
Call the stored procedure to insert a duplicate key:
Here is the output:
As you see the MySQL error code handler is called.
Using a named error condition
Let’s start with an error handler declaration.
What does the number 1146 really mean? Imagine you have stored procedures polluted with these numbers all over places; it will be difficult to understand and maintain the code.
Fortunately, MySQL provides you with the DECLARE CONDITION statement that declares a named error condition, which associates with a condition.
Here is the syntax of the DECLARE CONDITION statement:
The condition_value can be a MySQL error code such as 1146 or a SQLSTATE value. The condition_value is represented by the condition_name .
After the declaration, you can refer to condition_name instead of condition_value .
So you can rewrite the code above as follows:
As you can see, the code is more obviously and readable than the previous one. Notice that the condition declaration must appear before handler or cursor declarations.
In this tutorial, you have learned how to use MySQL handlers to handle exception or errors occurred in stored procedures.
How to try and catch errors in MySQL stored procedures.
MySQL is a bit of a different beast when it comes to its procedural scripting language. While at times it may feel like an outdated pattern to create business logic in the data store, there are ways to implement modern best practices.
In general, you want to avoid spaghetti business logic across the stack. However, there are times where for security or stability reasons its important to have some “critical” functions in stored procedures. Don’t avoid them simply because you don’t like them especially when you need them for architectural reasons. Be sure to verify that they are not too tightly coupled with business logic that may change as your application requirements change.
Let’s start off by saying there aren’t traditional TRY CATCH statements in MySQL, but there are Conditions which are almost synonymous with Exceptions. Conditions contain both a code and description.
Instead of wrapping all of your code in a try catch block you basically add what’s called a handler to your procedure. Typically you would want to stop executing, and in MySQL that is managed through an exit handler . Depending on the use case you may want to rollback or log the event when the handler is called.
Here a sample procedure skeleton that has an exit handler for SQLEXCEPTION.
If you run CALL my_procedure(‘1234’) you will see the error number, the sql state code, and a human-readable description. There are also other actions and conditions you can explore for building your handlers.
If you would like throw your own exceptions you can do so by changing SQLSTATE via the SIGNAL keyword.
Have fun with stored procedures and let me know if I’ve missed anything!
Error Handling in MySQL Stored Procedure
In this tutorial, we will learn how we can handle the errors effectively in the MySQL stored procedures. We will see multiple aspects and possibilities while creating the error conditions, handlers and handling those errors. So, let’s get started!
Introduction to MySQL Error Handling
It is very important to handle the errors in MySQL stored programs such as procedures, functions, triggers and events. If you do not handle the errors in a proper way, the end user will see some system-generated error message that is not understandable to everyone. Also, it is a good practice which helps programmers for debugging the programs as well.
In MySQL, we can easily create handlers that can handle the warnings, exceptions and errors.
In simple terms, the handler is a statement that handles the error when a specific condition meets.
Now let’s see the syntax to define a handler.
Declare an Error Handler
We can declare an error handler using the DECLARE … HANDLER statement.
Here, you can specify one of the three handler actions which having meaning as of the following-
- CONTINUE- The present program is still being run.
- EXIT – The BEGIN… END compound statement in which the handler is declared ends execution. Even if the condition occurs in an inner block, this is still true.
- UNDO – MySQL does not support it.
A specific condition or a group of circumstances that activate the handler are specified by the condition value. One of the following values may be used with the condition value:
- mysql_error_code – A MySQL error code represented by an integer literal, such as 1051 for “unknown table”:
- SQLSTATE sqlstate_value – a literal 5-character string specifying an SQLSTATE value, such as ’42S01′ to denote ‘unknown table’:
- condition_name – previously defined condition name using DECLARE… CONDITION. A condition name may be connected to an SQLSTATE value or a MySQL error number.
- NOT FOUND –
- SQLEXCEPTION – abbreviation for the group of SQLSTATE values that start with the number “02”. It is used to control what occurs when a cursor reaches the end of a data collection, making it significant in the context of cursors.
We have covered most of the theory parts about error handling. Let’s now take some examples to demonstrate how error handling is actually done.
Stored Procedure Error Handling Examples
Here, we will create a table and write a stored procedure to insert data into it. If the record is already present, we will issue an error message.
Let’s create a table for demonstration.
Creating a stored procedure to insert data
Now, we will create a stored procedure to insert the data into the table.
Inside the stored procedure, we will create an exit error handler for the error code 1062 which is for duplicate entries in the table. If the handler is invoked, we will show the message that a duplicate entry has been made and the exit handler will terminate the stored procedure execution.
Here, we take three parameters which are employee id, department id and city.
Writing an exit handler for error code 1062
Then we write an exit handler for the error code 1062. When it gets invoked, the message will be shown and the procedure terminates.
If the employee id is unique then the insert statement will get executed and the data that is inserted will be displayed as it is.
Note that, if the error handler is invoked, the SELECT * FROM statement at the end will not execute because as soon as the handler is activated, the message will be displayed and it terminates the procedure.
Let’s call the stored procedure now.
Call Stored Procedure
As you can see, the data that we have inserted into the table is shown in the output.
Inserting duplicate data
Let’s insert the same data that we have inserted just now. It should show the message that we defined in the handler.
As you can see here, we got the error message.
Switching the exiting handler
Now, let’s change the error handler from EXIT to CONTINUE and see the change.
Duplicate Entry With Continue Handler
As you can see, we first got the error message and then the record which is having the given employee id is displayed.
MySQL Error Handler Precedence
There might be possibilities that you have multiple handlers for the same error.
If you have several handlers for the same error, MySQL will call the one that can handle it the most specifically first based on the guidelines below:
- Because a MySQL error code is the most specific, it always corresponds to an error.
- An SQLSTATE is less precise since it can map to several MySQL error codes.
- It is the most general sense, an SQLEXCPETION or SQLWARNING is the abbreviation for a group of SQLSTATES data.
For example, let’s take the previously created stored procedure example.
We will slightly modify that procedure and add two more handlers for the same error.
So, the new stored procedure will look like this-
Now let’s try inserting the duplicate data.
As you can see, even if we have three handlers for the same error, the one which is specified with the error code is encountered.
In this tutorial, we have learned how to handle errors in MySQL stored procedures. This topic is very vast but I have tried to make it as simple as possible without adding unnecessary things which you can learn later yourself. I hope you find this tutorial helpful. If you do, don’t forget to share it with your friends.
Raising Error Conditions with MySQL SIGNAL / RESIGNAL Statements
Summary: in this tutorial, you will learn how to use SIGNAL and RESIGNAL statements to raise error conditions inside stored procedures.
MySQL SIGNAL statement
You use the SIGNAL statement to return an error or warning condition to the caller from a stored program e.g., stored procedure, stored function, trigger or event. The SIGNAL statement provides you with control over which information for returning such as value and message SQLSTATE .
The following illustrates syntax of the SIGNAL statement:
Following the SIGNAL keyword is a SQLSTATE value or a condition name declared by the DECLARE CONDITION statement. Notice that the SIGNAL statement must always specify a SQLSTATE value or a named condition that defined with an SQLSTATE value.
To provide the caller with information, you use the SET clause. If you want to return multiple condition information item names with values, you need to separate each name/value pair by a comma.
The condition_information_item_name can be MESSAGE_TEXT , MYSQL_ERRORNO , CURSOR_NAME , etc.
The following stored procedure adds an order line item into an existing sales order. It issues an error message if the order number does not exist.
First, it counts the orders with the input order number that we pass to the stored procedure.
Second, if the number of order is not 1, it raises an error with SQLSTATE 45000 along with an error message saying that order number does not exist in the orders table.
Notice that 45000 is a generic SQLSTATE value that illustrates an unhandled user-defined exception.
If we call the stored procedure AddOrderItem() and pass a nonexistent order number, we will get an error message.
MySQL RESIGNAL statement
Besides the SIGNAL statement, MySQL also provides the RESIGNAL statement used to raise a warning or error condition.
The RESIGNAL statement is similar to SIGNAL statement in term of functionality and syntax, except that:
- You must use the RESIGNAL statement within an error or warning handler, otherwise, you will get an error message saying that “RESIGNAL when the handler is not active”. Notice that you can use SIGNAL statement anywhere inside a stored procedure.
- You can omit all attributes of the RESIGNAL statement, even the SQLSTATE value.
If you use the RESIGNAL statement alone, all attributes are the same as the ones passed to the condition handler.
The following stored procedure changes the error message before issuing it to the caller.
Let’s call the Divide() stored procedure.
In this tutorial, we have shown you how to raise error conditions inside stored programs using SIGNAL and RESIGNAL statements.