MySQL Bugs: #17724: Proper Runtime Error Management for SP's, Triggers and Functions

Bug #17724	Proper Runtime Error Management for SP's, Triggers and Functions
Submitted:	26 Feb 2006 10:54	Modified:	27 Feb 2006 13:48
Reporter:	Roland Bouman	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1, 5.0	OS:	NA
Assigned to:		CPU Architecture:	Any

Description:
The introduction of triggers, stored procedures and functions in MySQL 5 makes a rigourous framework for handling runtime errors indispensible.

At present, such a framework is present only in part. Conditions maybe handled using the ANSI 9075:2003 compliant HANDLER syntax. Also, built-in error conditions maybe aliased using the CONDITION syntax.

Things that are, from a functional point of view, still missing, are:

1) the ability to obtain detailed information about a condition that was raised.
Without this functionality, it is next to useless to have the general conditions such as SQLEXCEPTION, SQLWARNING and NOT FOUND.
2) the ability to generate error conditions in a clean and targeted manner.
Without this functionality, it is cumbersome at best to guard business rules with triggers and procedures. IMO, to guard a businessrule inside a trigger, one needs to be able to have the triggering action fail with a user-defined runtime error when a possible violation of a businessrule is detected.

The reason why I think these matters should be adressed as quickly as possible is that the number of devices that people are using to achieve this functionality anyway is quickly expanding. For example, see: http://forums.mysql.com/read.php?99,55108,72394

Especially with regard to the second point, there is a lively exchange of tips on 'the best way' to deliberately cause some error (violate a unique constraint, call a non-existing procedure, value assignment type mismatch, udf) in order to have a trigger fail. Of course, this is bad programming practice, but given the posibilities, it is the best one can do in many cases. It would be a good thing if the MySQL development team would address these requirements before these practices get a chance to infest more and more applications.
As a final point, I'd like to note that the lack of these features will also hamper exchange of stored procedures.

How to repeat:
NA

Suggested fix:
1) Add functionality to obtain information on error conditions. ISO 9075:2003 (book 2, ch 22 par 1; book 4, ch 16 p 1) does provide a description of the syntax and semantics in the form of the

GET DIAGNOSTICS

statement, e.g.

GET CURRENT DIAGNOSTICS CONDITION 1
v_condition_number := CONDITION_NUMBER
, v_message_text := MESSAGE_TEXT
;

Of course, in the meanwhile, I'd gladly settle for a non-standard solution like @@sqlstate or @@mysql_errorno and @@mysql_errormsg.

2) Add functionality to raise a condition from inside a stored procedures, triggers and functions. ISO 9075:2003 (16.2) does provide the necessary semantics and syntax in the form of the SIGNAL statement:

SIGNAL PURCHASE_ORDER_MUST_HAVE_ITEM SET
v_message_text := 'A purchase order must contain at least 1 item to be purchased'
;

I already mentioned bug 11660 in and earlier comment. I also put upt feature request 11661 (support for the SIGNAL syntax), which has not even been verified.

I also think that the implementation of error handling would be very important.

Thank you for a problem report. I agree that all these features are needed (and our developers are already working on them). Please, wait for 5.2 and you'll see the results. 

In the meantime, I have to mark this report as a duplicate of your Bug #11661 and Bug #11660 (both are verified already, and being worked on for some time).

This is extremely needed feature.  It exists in Oracle and MS SQL.  I am very surprised that MySQL doesn't have it.  Right now as a work around - we had to create a table, fill it with MySQL Error Codes and corresponding messages and get the messages by the code in a huge CASE statement (one for each of the possible errors).  This is insane, but that's the only way we could find to preserve the error message if we were to preserve transaction integrity.  Please fix this!!!  Give me the @@ERROR and @@ERROR_MESSAGE and we can rule the world with MySQL! :)