Today, I received an email from a user of the udf_init_error UDF (which resides in the lib_mysqludf_udf library). The purpose of this UDF is to generate an error condition, which can be used to abruptly terminate a trigger or stored procedure. As such it is a workaround for bug #11661. This is all described extensively in my now ancient article here.
The user wrote me because of a problem experienced in MySQL 5.5:
...callingI explained that the UDF still works like it should; it's just that at some point during the 5.0 lifecycle, the format of the error message was changed. (I can't recall exactly which version that was, but I did file bug #38452 that describes this issue).select udf_init_error('Transaction Cannot Be Done Because....');will return user friendly error message:Transaction Cannot Be Done Because..... But in MySQL 5.5, it returnsCan't initialize function 'udf_init_error; Transaction Cannot Be Done Because....The
Can't initialize function 'udf_init_error;bit is so annoying! How can I get rid of that?
Anyway, I suggested to move away from using the
udf_init_error()UDF, and port all dependent code to use the
SIGNALsyntax instead, which was introduced in MySQL 5.5. (For a friendly introduction to using the
SIGNALsyntax, please check out one of my prior articles).
Unfortunately, for this particular user this would not be an easy task:
The use of SIGNAL did come to my mind, but the implementation is not easy. I have thousands of stored routines to modify. Besides, I'm already satisfied with what the UDF does.On the one hand, It makes me happy to hear the
udf_init_error()UDF served him so well that he wrote so many routines that rely on it; on the other hand, I feel bad that this is holding him back from upgrading to MySQL 5.5.
For everybody that is in this same position, I'd like to suggest the following solution: simply re-implement
udf_init_error()as a stored SQL function that uses the
SIGNALfunctionality instead. The error message returned to the client will not be exactly the same as in the olden MySQL 5.0 days, but at least there will not be an annoying complaint about a UDF that cannot be initialized.
Here's a very simple example that illustrates how to do it:
CREATE FUNCTION udf_init_error(I hope this helps.
DECLARE err CONDITION FOR SQLSTATE '45000';
SIGNAL err SET MESSAGE_TEXT = p_message;