The
latest MySQL 5.5 milestone release offers support for an ANSI/ISO standard feature called the
SIGNAL syntax. You can use this syntax inside stored routines (including triggers) to raise an error condition which can be used to invoke specific error handling, or otherwise abort the stored routine. In addition, you can use the
SIGNAL syntax to convey information about what went wrong, which may be used by the caller to handle the error.
I have written about MySQL data entry validation procedures in the past. At the time,
MySQL did not support any proper means to raise an error condition inside a stored routine, and one had to work around that by deliberatly causing a runtime error, for example by referring to a non-existent table, setting a non-nullable column to null, or
]Yting a specially crafted UDF. In this artcle, I'm taking a closer look at how to implement more robust data entry validation in MySQL using the
SIGNAL syntax.
Triggers
For those of you that are unfamiliar with the subject, MySQL offers support for
triggers as of version 5.0. Triggers are stored routines that are executed automatically right before or after data change events like a row being inserted, updated or deleted. Because triggers are executed as part of the SQL statement (and its containing transaction) causing the row change event, and because the trigger code has direct access to the changed row, you could in theory use them to correct or reject invalid data.
Example data validation problem
Let's take a quick look at the following example. Suppose you have a table called
person to store data about persons:
CREATE TABLE person (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, first_name VARCHAR(64) NOT NULL
, last_name VARCHAR(64) NOT NULL
, initials VARCHAR(8)
)
Let's consider a simple validation method for the initials column:
- We require the first letter of the value for the
initials column to match the first letter of the value of the first_name column.
- In addition, we require that the values for the
initials column consists of uppercase letters separated by periods.
In order to implement this, we design the following algorithm:
- If the value for
first_name is NULL, we do nothing. The NOT NULL table constraint will prevent the data from being entered anyway, so further attempts at validation or correction are pointless.
- If the value for
initials is NULL, we correct it by automatically filling in the first character of the value for first_name.
- If the values for
first_name as well as initials are both not NULL, we require that the first character of the value for first_name equals the first character of the value for initials.
- Finally, we use a regular expression to check if the value for
initials matches the desired pattern of uppercase letters separated by periods.
A data validation stored procedure
Let's start by creating a stored procedure to perform this algorithm. Here's the code for the
p_validate_initials procedure which validates and possibly corrects the
initials value based on the value for
first_name:
DELIMITER go
CREATE PROCEDURE p_validate_initials(
IN p_first_name VARCHAR(64)
, INOUT p_initials VARCHAR(64)
)
DETERMINISTIC -- same arguments yield same result, always
NO SQL -- does not execute SQL statements, only procedural logic
_main: BEGIN
DECLARE WARN_CORRECTED_INITIALS CONDITION FOR SQLSTATE '01000';
DECLARE ERR_INITIALS_DONT_MATCH_FIRSTNAME CONDITION FOR SQLSTATE '45000';
DECLARE ERR_INITIALS_ILLFORMATTED CONDITION FOR SQLSTATE '45000';
IF p_first_name IS NULL THEN
LEAVE _main; -- nothing to validate
ELSEIF p_initials IS NULL THEN -- initials are NULL, correct:
SET p_initials := CONCAT(LEFT(p_first_name, 1), '.'); -- take the first letter of first_name
SIGNAL WARN_CORRECTED_INITIALS -- warn about the corrective measure
SET MESSAGE_TEXT = 'Corrected NULL value for initials to match value for first_name.';
ELSEIF BINARY LEFT(p_first_name, 1) != LEFT(p_initials, 1) THEN -- initials don't match first_name
SIGNAL ERR_INITIALS_DONT_MATCH_FIRSTNAME -- raise an error
SET MESSAGE_TEXT = 'The first letter of the value for initials does not match the first letter of the value for first_name';
END IF;
IF NOT p_initials REGEXP '^([A-Z][.])+$' THEN -- if initials don't match the correct pattern
SIGNAL ERR_INITIALS_ILLFORMATTED -- raise an error
SET MESSAGE_TEXT = 'The value for initials must consist of upper case letters separated by periods.';
END IF;
END;
go
DELIMITER ;
Let's take a look at how this procedure works.
How to issue warnings
First, let's pass
NULL for the initials to see if they are properly corrected:
mysql> set @initials := null;
Query OK, 0 rows affected (0.00 sec)
mysql> call p_validate_initials('Roland', @initials);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @initials;
+-----------+
| @initials |
+-----------+
| R. |
+-----------+
1 row in set (0.00 sec)
Note that executing the procedure placed the correct value into the
@initials user-defined variable. Also note that a warning was issued.
In this case, the procedure ran through the following branch of the first
IF statement:
...
ELSEIF p_initials IS NULL THEN
SET p_initials := CONCAT(LEFT(p_first_name, 1), '.');
SIGNAL WARN_CORRECTED_INITIALS
SET MESSAGE_TEXT = 'Corrected NULL value for initials to match value for first_name.';
ELSEIF ...
The warning is caused by the
SIGNAL statement (which appears in bold text in the snippet above). The general syntax for the
SIGNAL statement is:
SIGNAL condition_value
[SET signal_information [, signal_information] ...]
condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name
signal_information:
condition_information_item = simple_value_specification
So, in this case, we used the condition name
WARN_CORRECTED_INITIALS as
condition_value. This
condition is declared in the top of the procedure:
DECLARE WARN_CORRECTED_INITIALS CONDITION FOR SQLSTATE '01000';
Basically, condition declarations like these serve simply to tie a human readable name to otherwise obscure
SQLSTATE values. As per the SQL standard, SQLSTATE values are strings of 5 digits. The prefix
01 indicates a warning.
(Condition declarations like these are not only useful to clarify the meaning of your
SIGNAL statements, you can also use them to declare error
HANDLERs)
An alternative syntax for
SIGNAL allows you to directly refer to the
SQLSTATE without explicitly declaring a
CONDITION. So, if you feel that declaring explicit conditions is too much trouble, you can also omit that and write:
SIGNAL SQLSTATE '01000'
...
(However, I like using explicit condition names better because it does a better job of explaining the intention of the code.)
Conveying SIGNAL context information
The
SIGNAL statement also features a
SET-clause which is used to convey signal information. In our example the set clause was:
SET MESSAGE_TEXT = 'Corrected NULL value for initials to match value for first_name.';
As you can see, the
SET-clause in the example contains an assignment. In the context of the
SIGNAL statemntt, such an assignment is referred to as
signal_information. The left hand side of the assignments must be one of the predefined
condition_information_items. The
SET-clause can have multiple of these
signal_information items which can be used to capture and communicate program state to the client program.
In the case of the example we can demonstrate how this works using the MySQL command-line client. By issuing a
SHOW WARNINGS statement, we can see the message text was conveyed with with the
signal_information item:
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------+
| Error | 1644 | The value for initials must consist of upper case letters separated by periods. |
+-------+------+---------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Unfortunately, MySQL does not currently support a way for any calling stored routines to capture the
signal_information items. They are currently only available in the APIs you use to communicate with MySQL, so you can capture them in your application code.
(
A bug has been filed to ask for the ability to refer to signal information items in stored routines. This should become available whenever MySQL implements a
DIAGNOSTICS feature)
Predefined condition information items
I just mentioned that the left-hand side of the
signal_information item assignment must be one of the predefined
condition_information_items. These are dictated by the standard, and although MySQL allows all of the standard
condition_information_items, only two of them are currently relevant:
MESSAGE_TEXT and
MYSQL_ERRNO. We already illustrated using
MESSAGE_TEXT. The
MYSQL_ERRNO is a non-standard condition information item that can be used to convey custom error codes.
This leaves currently three variables to convey information about the context of the
SIGNAL statement:
SQLSTATE: available in the C API as mysql_sqlstate()
MYSQL_ERRNO: available in the C API as mysql_errno()
MESSAGE_TEXT: available in the C API as mysql_error()
(You should be able to obtain the information also in PHP through the corresponding
mysqli_,
pdo_ and
mysql_ functions.)
How to issue errors
We just discussed how to cause your stored routine to issue warnings. Issuing errors is exactly the same process, it just relies on a different class of
SQLSTATE values (as determined by the code prefix). Let's see the errors in action:
mysql> set @initials := 'r';
Query OK, 0 rows affected (0.00 sec)
mysql> call p_validate_initials('Roland', @initials);
ERROR 1644 (45000): The first letter of the value for initials does not match the first letter of the value for first_name
In this case, the stored routine ran through the last branch of the first
IF statement:
ELSEIF BINARY LEFT(p_first_name, 1) != LEFT(p_initials, 1) THEN
SIGNAL ERR_INITIALS_DONT_MATCH_FIRSTNAME
SET MESSAGE_TEXT = 'The first letter of the value for initials does not match the first letter of the value for first_name';
END IF;
As you can see, the syntax for the actual
SIGNAL statement is exactly similar to what we saw in the example illustrating warnings. The most important difference is that in this case, the condition that is being signalled is declared with a
SQLSTATE value of 45000:
DECLARE ERR_INITIALS_DONT_MATCH_FIRSTNAME CONDITION FOR SQLSTATE '45000';
The
SQLSTATE code
45000 is a special and indicates a general user-defined exception.
Using the procedure in a trigger
All we need to do now is create our triggers on the
person table that call the procedure to perform the actual validation. We need to apply the validation when data is inserted into the table, but also when data is updated. If it turns out the data is invalid, we need to reject the change. For this reason, we want to create triggers that fire before the data change is applied to the table.
So, to enforce validation, we need two triggers: one that fires
BEFORE INSERT events, and one that fires
BEFORE UPDATE events. Because the validation process itself is the same regardless of the type of change event, both triggers can call the
p_validate_initials procedure to perform the actual validation. This allows us to write (and maintain!) the validation logic only once, and reuse it whenever we need it.
DELIMITER go
CREATE TRIGGER bir_person
BEFORE INSERT ON person
FOR EACH ROW
BEGIN
CALL p_validate_initials(
NEW.first_name
, NEW.initials
);
END;
go
CREATE TRIGGER bur_person
BEFORE UPDATE ON person
FOR EACH ROW
BEGIN
CALL p_validate_initials(
NEW.first_name
, NEW.initials
);
END;
go
DELIMITER ;
A quick check indicates that data validation is now enforced as intended:
mysql> INSERT INTO person (id, first_name, last_name, initials)
-> VALUES (2, 'Roland', 'Bouman', 'r');
ERROR 1644 (45000): The first letter of the value for initials does not match the first letter of the value for first_name
mysql> INSERT INTO person (id, first_name, last_name, initials)
-> VALUES (2, 'Roland', 'Bouman', 'R');
ERROR 1644 (45000): The value for initials must consist of upper case letters separated by periods.
mysql> INSERT INTO person (id, first_name, last_name, initials)
-> VALUES (2, 'Roland', 'Bouman', NULL);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------+
| Warning | 1642 | Corrected NULL value for initials to match value for first_name. |
+---------+------+------------------------------------------------------------------+
1 row in set (0.00 sec)
Final words
For more information on MySQL 5.5, check out
Giuseppe Maxia's article on
Getting Started with MySQL 5.5. Detailed information on the
SIGNAL syntax is available in the reference manual here:
http://dev.mysql.com/doc/refman/5.5/en/signal-resignal.html.
