« Pivot performance in 11g | Main | Oracle disk IO latency on EC2 »
Monday
Jul132009

SIGNAL and RESIGNAL in MySQL 5.4 and 6.0

 

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.

 

 

References (3)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    When Steven and I wrote MySQL Stored Procedure programming our biggest reservation about the new stored
  • Response
    When Steven and I wrote MySQL Stored Procedure programming our biggest reservation about the new stored
  • Response
    When Steven and I wrote MySQL Stored Procedure programming our biggest reservation about the new stored

Reader Comments (1)

They also allow us to effectively simulate CHECK constraints with TRIGGERS. Which is very good.

April 3, 2012 | Unregistered CommenterPhilip Petrov

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>