Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted August 10, 2015

MySQL Error Handling using the Signal and Resignal Statements

By Rob Gravelle

All programming languages incorporate some sort of error handling mechanism for handling unexpected occurrences and a graceful exit from the application if need be.  MySQL introduced the SIGNAL and RESIGNAL statements in version 5.5 (as per the SQL 2003 spec) to serve that purpose.  It allows you to raise your own error conditions from your stored procedures, triggers, and events. In today’s article we’ll learn some of the key differences between SIGNAL and RESIGNAL as well as how to utilize both.

Basic Syntax

Before MySQL 5.5, developers had to resort to workarounds such as deliberately referring to a nonexistent table to cause a routine to throw an error. Thankfully, SIGNAL (and RESIGNAL) may now be employed to provide error information to a handler and/or to the calling process. Moreover, SIGNAL provides some control over the error's attributes such as the error number, SQLSTATE value, and message. 

Here is the basic syntax for the SIGNAL statement and an explanation of each part:

SIGNAL SQLSTATE | condition_value
    [SET signal_information_item= value_1,
    [, signal_information_item] = value_2, etc;]

Following the SIGNAL keyword is an SQLSTATE value or a condition name declared by a DECLARE CONDITION statement. Notice that the SIGNAL statement must always specify an SQLSTATE value or a named condition that defined with an SQLSTATE value.

The SQLSTATE value for a SIGNAL statement consists of a five character alphanumeric code.  The full list of pre-defined codes are listed in the docs. Never start your own SQLSTATE code with '00' because such values indicate success and are not valid for signaling an error.  If your value is invalid, a Bad SQLSTATE error occurs.  For catch-all error handling, you should assign an SQLSTATE value of '45000', which signifies an “unhandled user-defined exception.”

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 may be any of the following:

  • CLASS_ORIGIN
  • SUBCLASS_ORIGIN
  • MESSAGE_TEXT
  • MYSQL_ERRNO
  • CONSTRAINT_CATALOG
  • CONSTRAINT_SCHEMA
  • CONSTRAINT_NAME
  • CATALOG_NAME
  • SCHEMA_NAME
  • TABLE_NAME
  • COLUMN_NAME
  • CURSOR_NAME

Each condition_information_item_name may be specified only once in the SET clause. Otherwise, a Duplicate condition information item error occurs.

Stored Procedure SIGNAL Example

The following stored procedure adds a video rental store to the Sakila sample database.  As it turns out, both of the required values act as foreign keys to other tables.  Hence, it’s a good idea to check that both of the associated input parameters contain valid values.  That’s easily accomplished by applying the count() function to each of the target columns.  In both cases, an SQLSTATE value, use '45000' is assigned to signify an “unhandled user-defined exception.”

CREATE PROCEDURE `add_new_store`(
     manager_staff_id tinyint, 
     address_id smallint)
 BEGIN
   DECLARE manager_staff_count INT;
   DECLARE address_count INT; 

  -- check if manager exists
   SELECT COUNT(*) INTO manager_staff_count
   FROM staff
   WHERE staff_id = manager_staff_id;

  IF manager_staff_count != 1 THEN 
     SIGNAL SQLSTATE '45000'
     SET MESSAGE_TEXT = 'manager_staff_id not found in staff table.';
   END IF;

  -- check if address exists
   SELECT COUNT(*) INTO address_count
   FROM address
   WHERE address.address_id = address_id;

  IF address_count != 1 THEN 
     SIGNAL SQLSTATE '45000'
     SET MESSAGE_TEXT = 'address_id not found in address table.';
   END IF; 

  -- proceed with insert...

END

Here are the corresponding error messages in the Workbench console:

Error Message

Error Message

Using the RESIGNAL Statement

The MySQL RESIGNAL statement is similar to the SIGNAL statement in terms of both functionality and syntax, except that RESIGNAL may be used to raise a warning or error condition.

RESIGNAL [SQLSTATE | condition_value]
    [SET signal_information_item = value_1,
    [, signal_information_item] = value_2, etc;]

Here’s our previous stored procedure rewritten to utilize the RESIGNAL statement.  This time we declare an exit handler for the SQLSTATE value of “45000”.  Inside the handler statement, we can set the MESSAGE_TEXT dynamically based on the values of the manager_staff and the address counts.  It might look strange that we are referencing the counts variables before they’ve been set but the exit handler does not execute until an error has occurred. 

CREATE PROCEDURE `add_new_store_with_resignal`(
     manager_staff_id tinyint, 
     address_id smallint)
 BEGIN
   DECLARE manager_staff_count INT;
   DECLARE address_count INT; 

  DECLARE invalid_value CONDITION FOR SQLSTATE '45000';
   DECLARE EXIT HANDLER FOR invalid_value 
     IF manager_staff_count != 1 THEN 
       RESIGNAL SET MESSAGE_TEXT = 'manager_staff_id not found in staff table.';
     ELSEIF address_count != 1 THEN 
       RESIGNAL SET MESSAGE_TEXT = 'address_id not found in address table.';
     END IF;

  -- check if manager exists
   SELECT COUNT(*) INTO manager_staff_count
   FROM staff
   WHERE staff_id = manager_staff_id;

  IF manager_staff_count != 1 THEN 
     SIGNAL invalid_value;
   END IF;

  -- check if address exists
   SELECT COUNT(*) INTO address_count
   FROM address
   WHERE address.address_id = address_id;

  IF address_count != 1 THEN 
     SIGNAL invalid_value;
   END IF; 

  -- proceed with insert...

END

Using the SIGNAL Statement in Triggers

Including the SIGNAL statement in your stored procedures is a great way to validate user input provided that the user is in fact using the procedure to insert or update the data.  Validation within stored procedures will provide no protection whatsoever for data that is added directly into the database via an insert or update statement.

For that reason it may be prudent to include some error handling within triggers.  For instance, here is an insert trigger that employs the same validation as the add_new_store procedure. 

CREATE TRIGGER before_insert_new_store
      BEFORE INSERT ON store FOR EACH ROW
      BEGIN
           IF (SELECT COUNT(staff_id) FROM staff  
                 WHERE staff_id = NEW.manager_staff_id) != 1
           THEN
                SIGNAL SQLSTATE '45000'
                  SET MESSAGE_TEXT = 'Cannot add or update row: manager_staff_id not found in staff table.';
            ELSEIF (SELECT COUNT(address_id) FROM address 
                   WHERE address_id = NEW.address_id) != 1
           THEN
                SIGNAL SQLSTATE '45000'
                  SET MESSAGE_TEXT = 'Cannot add or update row: address_id not found in address table.';
           END IF;
      END;

Once again, here is the corresponding error message in the Workbench console:

Error Message

Conclusion

The SIGNAL and RESIGNAL statements represent a giant leap forward in MySQL error handling, but they are not perfect.  The main drawback to using the SIGNAL and RESIGNAL statements right now is that they are still missing a mechanism for fetching the SQLCODE or SQLSTATE within your error handler.  Without a way to find out what the error was, you have to either code an error handler for every possible condition or be selective with which errors you catch.

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM