The MySQL Stored Procedure, trigger and function syntax supports
HANDLERs to react to on or more CONDITIONs, should they arise. The syntax and the semantics of these are compliant with the 2003 version of the SQL Standard (this is both an ANSI as well as an ISO standard).A condition can take several forms: it might be:
- a particular SQLSTATE (these are defined in the SQL Standard)
- a particular mysql specific error code
- the name of a previously declared, user defined condition (right now, a condition declaration must be bound to a SQLSTATE or a mysql specific errorcode, effectively aliasing it)
NOT FOUNDSQLEXCEPTION or SQLWARNING I think the concept of declaring separate handlers for capturing specific error conditions is both powerful and elegant: It allows you to deal with errors in a generic manner, and it separates the code that deals with errors entirely from the code that is supposed to do the 'business-as-usual' work. So far, so good. However, just being able to react to errors is not good enough:
- In order to sucessfully handle the general conditions
NOT FOUND,SQLWARNINGandSQLEXCEPTION, the handler need to be able to retrieve some details concerning the specific error. - One needs to be able to explicitly raise a (user-defined)condition using a special statement
- When a handler finds it is not suitable to handle the condition fully itself after all, it should be able to pass the condition on to the outer blocks to give other handlers a chance
Getting information on the condition being handled
The lack of this feature has been the subject of questions and confusion in the mysql forums for quite some time, at least as long as I am on them (which is about july 2005 - we're talking mysql 5.0.7 here. There are older ones too: check out this one).
Especially when declaring a handler for something as general as
SQLEXCEPTION or SQLWARNING, both of which act as a sort of 'wildcard' conditions, one needs to be able to find out the nature of the actual problem that occurred. A snipppet might explain this:
begin
declare continue handler for not found
--handle not found
;
--
--other specific handlers go here
--
-- catch all warnings: log what's left unhandled sofar
declare exit handler for sqlwarning
--
-- log the error
--
-- call p_log_condition(
-- sqlstate
-- , message_text
-- );
call p_log_condition(
'?'
, 'some problem occurred'
);
end;
The snippet does it's best to handle all the conditions it can. However, it is anticipated that there might be some condition that is unforseen, or that cannot be handled here. So, it declares a handler for
SQLWARNING in order to log the occurrence of the warning. But alas! Theres no way we can store the message text or error number for that matter, because we have no way to access it. But is this really true? Can't we access it? We know the mysql command line client is capable of showing us the message text using a
show warnings statement. Also, the libraries that connect to mysql do have methods to get to the message text and error number. For example, in php we can use mysql_error() to get the message and mysql_errno() to get the error number.Well, I don't know why, but has far as I have looked I keep concluding that the mysql stored procedure language has no construct whatsoever to get to the error number or message. And, no, I really do not want to build all the generic error handling in my applications.
Other rdbms-es I work with do have facilities to do this. For example, in Oracle you can use the global variables
SQLERRM and SQLCODE to get to the error message and number of the last executed statement respectively. In MS SQL, some similar device is available. There, you can use the @@ERROR global variable to retrieve the error number caused by the last executed statement. For example, take a look at how this snippet would look in Oracle PLSQL:
begin
--statements go here
exception
when no_data_found then
--handle not found
when others then
p_log_exception(
sqlcode
, sqlerrm
);
end;
Some people might argue that I shouldn't compare mysql to those rdbms-es, but I make a point of continuing to do so. A lot of my interest in mysql has to do with how well it can do whatever I do now with Oracle and MS SQL. I really think mysql is competitive with those rdbms-es in a lot of ways, and I regret to say that error handling from inside stored procedures is not one of the things where mysql is winning that battle - not right now anyway.
What can be said of the way Oracle and MS SQL have solved this is that they use proprietary feature to do it. From a practical point of view, I do feel that any feature, proprietary or otherwise, is a lot better than having no such feature at all. However, having said that, the SQL Standard that MySQL strives to adhere too does describe a construct that can be used: the
GET DIAGNOSTICS statement.I don't want to get into all the details and underlying concepts but I trust that a simple snippet will explain most of the functionalities of this statement:
begin
declare continue handler for not found
--handle not found
;
--
--other specific handlers go here
--
-- catch all warnings: log what's left unhandled sofar
declare exit handler for sqlwarning
begin
get diagnostics condition 1
set v_sqlstate := RETURNED_SQLSTATE
, v_message_text := MESSAGE_TEXT
;
call p_log_condition(
v_sqlstate
, v_message_text
);
end;
end;
So, the
get diagnostics statement means we are ordering some info from the diagnostics area. You can think of that as a piece of memory that stores all kinds of characteristics associated with statement execution. The condition keyword means we are requesting one of the detail sections of the diagnostics area, and the 1 means we want to examine the first of these detail sections (as statement execution maybe associated with several details). Then, an ordinary SET statement follows, assigning the values of the predefined fields (MESSAGE_TEXT, RETURNED_SQL_STATE) of the diagnostics area detail section to our locally declared variables.I must say that I didn't examine the standard thoroughly enough to know when to expect multiple detail sections in the diagnostics area. If the number of detail sections would be limited to just one, you could just as well have global variables for all the predefined fields in the diagnostics area. Anyway, whatever the syntax will be, I think this feature should be implemented in mysql.
Explicitly raising a condition
This keeps coming back in the forums. And I've seen these too from the start (well, my start) on the forums. In fact, a forum search found me one that dates back more than a year ago (see: http://forums.mysql.com/read.php?98,55535; http://forums.mysql.com/read.php?20,15856; http://forums.mysql.com/read.php?99,22523; http://forums.mysql.com/read.php?98,24044).
The idea is quite simple: normally, conditions arise as a side effect of executing statements. Conditions that arise as a result of some runtime error are especially interesting, as they will probably need to be handled (the procedure or trigger or function simply exits if the condition is serious enough and is left unhandled). In some cases a procedure, function or trigger needs to deliberately cause a condition because it detects some state of affairs that it violate some business rule.
A simple case is parameter checking. SUppose you have some kind of procedure to encapsulate some piece of business logic. You really must check if the parameters have valid values before doing all kinds of work inside the procedure, but you certainly don't want to go beyond that. The cleanest way to solve this would be to raise a user defined exception, and set up the diagnostics area in the process so that the caller can extract usefule information from there when it handles the condition.
Another case are triggers. Especially in the absence of check constraints, triggers receive a great deal of interest. In principle, triggers allow one to guard complex
business rules and integrity constraints. However, what to do when the tigger detects a possible violation of the business rule? You'd want the trigger to raise a condition, so that the triggering statement fails because of it. Of course, the diagnostics area would be enriched prior to allow proper handling.
Now, some people might shrug and say something like "..well, I can deal with the procedure parameter problem just fine, because I can check the parameters and exit the procedure when they aren't valid." But this is besides the point. The point is that the caller should have a clear, clean way of determining whether the procedure did the job it was supposed to do, and if not, why it failed.
Of course, you can work around all that, and that's what's been happening for some time now. It's always possible to deliberately provoke a condition. Some of the flavours are presented here:
- insert a duplicate to cause a unique constraint violation
- call a procedure that does not exist
- call an UDF of which the xxx_init() function always returns 1
- assign a string value to an TINYINT variable
You can find a lively exchange of these methods here, along with some links and more explanations. Lately, the discussion there is also about which method is best.
In my opinion, neither of them is very good. Andrew Gilfrin too has pointed out in several locations that the real problem is not so much raising the condition - the real problem is finding out afterwards what the real problem is. Or - the other way around, distinguishing the cases where a non-deliberate unique constraint violation occurrs, when a non-existant procedure was called undeliberatley (maybe someone dropped the procedure? ) etc. - you'll get the picture.
Again, other rdbms-es have solved this in their own way. In oracle you can use the
raise statement, or the RAISE_APPLICATION_ERROR procedure. In MS SQL, a similar device exists in the form of the RAISEERROR procedure. Again, the SQL standard also provides the syntax and the semantics in the form of the
SIGNAL statement:
create procedure p_myproc(
p_id int unsigned
, p_name varchar(64)
)
begin
declare condition BUSINESS_RULE_VIOLATED;
-- do some processing
if not check_parameters(
p_id
, p_name
) then
SIGNAL BUSINESS_RULE_VIOLATED
SET MESSAGE_TEXT := 'Invalid parameter values'
;
end;
end;
Here, the
SIGNAL statement tells the server to propagate the (user defined) condition with the name BUSINESS_RULE_VIOLATED to the calling environment. It also sets the value of the MESSAGE_TEXT field in the diagnostics area, allowing the caller to examine it's contents.Passing the condition on to the outer blocks
Sometimes, a handler can't take care of all the actions that should be done to properly handle the error. For example, a procedure that obtained a lock with
GET_LOCK should release it when it encounters some condition that makes further processing futile. But the procedure should generally not take the responsibility to perform a ROLLBACK, as the call to the current procedure might be a small part of a large transaction that was started long before the current procedure was hit. So, after cleaning up, the current procedure might want to convey the fact that it encountered a condition to it's caller so that has a chance to perform thier cleanup. What could be more convenient than to reuse the condition that was already captured by the handler?The SQL standard also provides this in the form of the
RESIGNAL statement. It can be used just as a plain statement, propagating the currently handled condition, without altering the diagnostics area:
begin
declare exit handler for not found
begin
release_lock('my_lock');
RESIGNAL;
end;
end;
It can also be used in the same manner as
SIGNAL, giving a chance to alter the diagnostics area.Final thoughts
I put up a few feature requests regarding the lack of these features (11660 and 11661). So far, they've barely been touched. Now, I don´t want to blame anyone for that. Maybe It's my own fault, and I didnt give em a snappy title.
However, I do sense that the need for proper, robust error handling is increasing. Now that the basic functionality of stored procedures and triggers has sunken in, and people are starting to make serious use of it, this error handling issue poses a real problem that people need to solve. And they are, that is, they're working around it, all in their own way. However, as long as there's no standard that everyone can use in a durable way, things like building big applications using stored procedures will be a big pain. Also, exchange of stored procedures might be hampered.
So, I did something I (as far as I can remember) never did before: I made out a new request, but labeled it immediately as bug, severity 2. I'm hoping this could put this thing on the agenda. Of course, I'm also hoping not to offend anyone. So, if you feel you need this functionality, please add a comment to the bug report. It might just help. Thank you.
