How to raise an error within a MySQL function

Question

I've created a MySQL function and would like to raise an error if the values passed for the parameters are invalid. What are my options for raising an error within a MySQL function?

Shirlyshiroma

Answer 1

MySQL 5.5 introduces signals, which are similar to exceptions in other languages:

http://dev.mysql.com/doc/refman/5.5/en/signal.html

For example, in the mysql command line client:

mysql> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error';
ERROR 1644 (45000): Custom error

Eyesight

Answer 2

It's actually a combination of all three answers. You call a non-existent procedure to raise the error, and then declare an exit handler that catches the error you generated. Here's an example, using SQLSTATE 42000 (procedure does not exist) to throw an error before deletion if the row to be deleted has a foreign key id set:

DROP PROCEDURE IF EXISTS decount_test;

DELIMITER //

CREATE DEFINER = 'root'@'localhost' PROCEDURE decount_test ( p_id bigint )
DETERMINISTIC MODIFIES SQL DATA
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    SELECT 'Invoiced barcodes may not have accounting removed.';
  IF (SELECT invoice_id 
       FROM accounted_barcodes
       WHERE id = p_id
    ) THEN
    CALL raise_error;
 END IF;
 DELETE FROM accounted_barcodes WHERE id = p_id;
END //

DELIMITER ;

Output:

call decount_test(123456);
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. |
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. | 
+----------------------------------------------------+

Anyplace

It should be noted that this only works for stored procedures, not functions. In a function, the SELECT within the handler will cause a cryptic error about not being able to return a set from a function. – Worthless

It works great only for one error message. In reality you would have multiple validations and would require to raise different errors, but that single exit handler may not be the way to go. – Tumbrel

Answer 3

Why not just store a VARCHAR in a declared INTEGER variable?

DELIMITER $$ DROP FUNCTION IF EXISTS `raise_error` $$
CREATE FUNCTION `raise_error`(MESSAGE VARCHAR(255)) 
RETURNS INTEGER DETERMINISTIC BEGIN
  DECLARE ERROR INTEGER;
  set ERROR := MESSAGE;
  RETURN 0;
END $$ DELIMITER ;
-- set @foo := raise_error('something failed'); -- or within a query

Error message is:

Incorrect integer value: 'something failed' for column 'ERROR' at row 1

It's not perfect, but it gives a pretty descriptive message and you don't have to write any extension DLLs.

Moir

This is very clever. By far the simplest and clearest method. – Serious

@Serious After you've had to report enough errors... Finding a better way becomes a bit of an obsession. (first it was call raise_error_life_sucks();, then it gradually evolved as I needed it places I couldn't just call a proc) – Moir

@Isblsb, you need to set sql_mode to strict_trans_tables for an error to be raised. strict_trans_tables needs to be in effect when you create a function. – Dithyrambic

Answer 4

In MySQL 5 you may raise an error by calling a stored procedure that does not exist (CALL raise_error) or passing an invalid value to a query (like null to a NOT NULL contrained field). Here is an interesting post by Roland Bouman on raising errors from within a MySQL function:

http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html

Photomechanical

Answer 5

You can also call an existing function with an invalid number of arguments.

Pyrogenic

Answer 6

You have to define exception handlers . Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Amar

Exception handlers are for catching exception. The question is about raising an exception. – Helaine