How to force MySQL to throw a conditional exception at runtime in SQL?

Question

I am writing a migration test to ensure that the user was created by migration. If the user does not exist, the test should throw an error. At first I thought I could just use division by zero error to get what I wanted:

SET SESSION sql_mode = 'error_for_division_by_zero'; SELECT 1/COUNT(*) FROM mysql.user WHERE user = 'foo'; 

However, this does not cause an error if foo does not exist. It turns out that error_for_division_by_zero only affects INSERT and UPDATE .

Then I thought, maybe I could just call some function with the wrong number of arguments:

 SELECT IF(COUNT(*) = 1, 1, date_format(1, 2, 3)) FROM mysql.user WHERE user = 'foo'; 

But this dies even when foo exists, apparently because the parser notices an invalid parameter counter.

I could write a function that emulates the creation of an exception , but I tried to avoid this. There is no way to force MySQL to conditionally throw an exception at runtime?

Answer 1

This cannot be done without a stored procedure or function, unfortunately. However, I figured out how to maintain the function in my application. Borrowing the basic idea of ​​the procedure from this answer , I came up with the following:

 DELIMITER | CREATE FUNCTION checkit(doit INTEGER, message VARCHAR(256)) RETURNS INTEGER DETERMINISTIC BEGIN IF doit IS NULL OR doit = 0 THEN SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = message; END IF; RETURN doit; END; | 

The idea is that this function can be used in triggers as a CHECK or inline constraint in SQL statements. Returning to my original need for an error if the user does not exist, I now use the checkit() function as follows:

 SELECT checkit(COUNT(*), 'User "foo" does not exist') FROM mysql.user WHERE user = 'foo'; 

If user foo exists, this query returns an integer. If the user does not exist, he throws an error with the message defined there.

Would you like to use the function for a control limit? He is an example (imitation of this answer ), with the tip of the hat to @ rouland-bouman:

 CREATE TRIGGER mytabletriggerexample BEFORE INSERT FOR EACH ROW BEGIN SET @dummy := checkit( NEW.important_value) >= (fancy * dancy * calculation), 'Your meaningful error message goes here' ); END; 

I would rather use DO rather than setting a dummy variable, but a MySQL error prevents this from working, alas.

Answer 2

David

why do i need a procedure? You can use the SIGNAL inside the function (and you can still call the function in the trigger)

 delimiter go create function f_raise(p_message_text varchar(255)) returns int begin signal sqlstate '45000' set message_text = p_message_text; return null; end; go select f_raise('bla'); go ERROR 1644 (45000): bla 

Answer 3

You can throw an exception without a function / procedure by simply getting a column from a table with multiple rows. Usually the database has a table for this already, but here I have included a temporary table for this purpose:

 drop temporary table if exists t; create temporary table t ( select ('a')); insert into t values('b'); select * from t; select case when 2*2 = 4 then 'OK' else (select a from t) end; select case when 2*2 = 5 then 'OK' else (select a from t) end; 

See also: https://dba.stackexchange.com/questions/78594/how-to-conditionally-raise-an-error-in-mysql-without-stored-procedure