theory theory - 7 months ago 20
SQL Question

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

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?

Answer

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.