SIGNAL and RESIGNAL in MySQL 5.4 and 6.0
Monday, July 13, 2009 at 2:34PM One of the most glaring omissions in the MySQL Stored procedure implementation was the lack of the ANSI standard SIGNAL and RESIGNAL clauses. These allow a stored procedure to conditionally return an error to the calling program.
When Steven and I wrote MySQL Stored Procedure programming we lamented this omission, and proposed an admittedly clumsy workaround. Our workaround involved creating and procedure in which dynamic SQL in which the error message was embedded in the name of a non-existent table. When the procedure was executed, the non-existing table name at least allowed the user to see the error. So for instance, here is the my_signal procedure:
When you want to raise an error, you call MY_SIGNAL and pass the text of the error you want to throw:
This causes a 'Table doesn't exist error', but the name of the table is quoted, which contains the error text:
Clumsy in the extreme, but this was about the best one could do in MySQL 5.0 and 5.1. At last, relief is in sight: MySQL 5.4 and 6.0 have implemented the SIGNAL and RESIGNAL clauses. SIGNAL and RESIGNAL are available in the latest drop of 6.0 (6.0.11) and - I believe - in 5.4.4. The SIGNAL clause is documented here; the RESIGNAL clause is documented in the same chapter. The examples below are from 6.0.11, but I believe the behavior in 5.4.4 is the same.
With SIGNAL, we can get rid of the clumsy MY_SIGNAL procedure, and simply code the error we want to send directly:
The error message is cleaner, and we don't get the misleading mysql error code:
You can use RESIGNAL in a similar way to send on an error message that you have already caught. So let's say that in the event of a duplicate key on index error you want to insult the user before returning the MySQL error. Here's how you'd do it:
When the duplicate insert is attempted the EXIT handler from line 8 fires and executes the SELECT and ROLLBACK statements (lines 10-12). Finally the RESIGNAL clause on line 13 causes the original error to be raised:
SIGNAL and RESIGNAL make a huge difference to the usability and reliability of Stored procedures. In fact, I beleive that MySQL should never have released a stored procedure implementation without them. Oh well, better late than never.
However, stored procedures are still missing a mechanism for catching the SQLCODE or SQLSTATE within the stored procedure code. THis means that in a general purpose error handler, you can't know what the error was. Instead, you have to code an error handler for every possible condition. As Roland Bourman has shown, this has a measurable performance impact and also leads to verbose and messy code. The correct implementation is to implement something along the lines of the ANSI GET DIAGNOSTICS command, and this is a low (!) priority enhancement probably not going to be seen before MySQL 7.0. That's a drag.
mysql
Reader Comments (1)
They also allow us to effectively simulate CHECK constraints with TRIGGERS. Which is very good.