How do I get MySQL to throw a conditional runtime exception in SQL

Question

I am writing a migration test to ensure that a user was created by the migration. If the user does not exist, the test should throw an error. At first, I thought I could just use a 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 throw an error if foo does not exist. Turns out that error_for_division_by_zero affects only INSERT and UPDATE statements.

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 does exist, presumably because the parser notices the incorrect parameter count.

I could write a a function that emulates raising an exception, but I was trying to avoid that. Is there no way to coerce MySQL into conditionally throwing a runtime exception?

Sounds like middle tier functionality to me. I wouldn't have the database do such a thing, because it's hardly "exceptional". Users that don't exist in the database aren't errors; they're potential new users. — duffymo, Jul 01 '13 at 14:19
Check [this question](http://stackoverflow.com/questions/9131379/mysql-how-to-throw-exception-in-stored-procedure) — Vatev, Jul 01 '13 at 14:20
@Vatev I cannot see how to get `SIGNAL` to run only in the event of a SQL condition (`IF()`, `CASE`, or the like). Do you know of an example? — theory, Jul 01 '13 at 14:28
There is a link to [the manual](http://dev.mysql.com/doc/refman/5.5/en/signal.html) there too. — Vatev, Jul 01 '13 at 19:45

score 11 · Accepted Answer · edited May 23 '17 at 12:18

It cannot be done without a stored procedure or function, unfortunately. I figured out how to support a function in my app, though. Borrowing the basic procedure idea from this answer, I've come up with this:

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 the function can be used in triggers like a CHECK constraint, or inline in SQL statements. Getting back to my original need to throw an error if a user does not exist, I now use the checkit() function like this:

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, it throws an error with the message defined there.

Want to use the function for a check constraint, too? He's an example (mimicking this answer), with a 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 bug prevents that from working, alas.

riskop · Answer 2 · 2020-10-01T08:07:49.123

You can raise an exception without function / procedure by simply running a subquery returning MORE THAN ONE row but EXPECTING ONLY ONE row. Usually in a database there is a table for that already, but here I included a table for that purpose:

create table t (a int);
insert into t values(1);
insert into t values(1);
select * from t;

The below select raises exception because the outer select EXPECTS one value BUT the inner select returns MORE THAN ONE value.

select (select a from t);

You can apply that for your case:

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

score 0 · Answer 3 · answered Jul 02 '13 at 11:45

0

David,

why do you need the procedure? You can SIGNAL from within a function (and you can still call the function in a 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

answered Jul 02 '13 at 11:45

Roland Bouman

31,125
6
66
67

Per our Twitter chat, and for the information of others, a trigger would need to `DO f_raise()`, but `DO` apparently swallows the exception. Appears to be [a bug](http://bugs.mysql.com/bug.php?id=69647). Perfect if you don't need it in a trigger, but that kind of defeats using it to mimic a `CHECK` constraint. – theory Jul 02 '13 at 12:19
One remark: one could still use the function in a trigger, as long as it is assigned to a variable. For instance: SET v_dummy = f_raise('bla'); – Roland Bouman Jul 02 '13 at 12:21
What is it with MySQL and the word "dummy"? ;-P – theory Jul 02 '13 at 13:19
Updated my answer to eliminate the procedure. Thanks! – theory Jul 03 '13 at 16:13

How do I get MySQL to throw a conditional runtime exception in SQL

3 Answers3