Custom MariaDB warning doesn't get raised, but identical error does

Question

I am attempting to create a custom warning that can be thrown by a trigger in a MariaDB database, but for some reason I can't get it to display. The relevant code (simplified and obfuscated) is as follows:

CREATE TABLE my_table (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(255),
);

DELIMITER //
CREATE TRIGGER warn_bad_cause
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    IF (NEW.data LIKE 'bad data')
    THEN
        SIGNAL SQLSTATE '01000' 
        SET MESSAGE_TEXT = 'bad data';
    END IF;
END //
DELIMITER ;

When attempting to insert a value that should trigger the warning, nothing happens. E.g.,

> INSERT INTO my_table (data) VALUES ('bad data');
Query OK, 1 row affected (0.013 sec)

> SHOW WARNINGS;
Empty set (0.000 sec)

But if I change the SQLSTATE to an error (e.g., 45000), I get an error using the same query:

> INSERT INTO my_table (data) VALUES ('bad data');
Query OK, 1 row affected (0.013 sec)
ERROR 1644 (45000): bad data
Error (Code 1644): bad data

> SHOW WARNINGS;
+-------+------+----------+
| Level | Code | Message  |
+-------+------+----------+
| Error | 1644 | bad data |
+-------+------+----------+

Things I have tried:

• \W to display warnings
• Checking the logs with the sql_errlog plugin
• Increasing the log_warnings level from 2 (default) all the way to 9
• Setting specific mysql_errno values in the trigger

Help would be greatly appreciated.

Answer 1

It looks like that you've hit a bug. SIGNAL SQLSTATE starting with 01 (warnings) raises a warning when executed in or outside a stored procedure, but not inside a trigger as shown in your example.

MariaDB [test]> SIGNAL SQLSTATE '01000';
Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [test]> DELIMITER $$
MariaDB [test]> CREATE OR REPLACE PROCEDURE p1()
    -> BEGIN
    ->   SIGNAL SQLSTATE '01000';
    -> END $$
Query OK, 0 rows affected (0.013 sec)

MariaDB [test]> call p1()$$
Query OK, 0 rows affected, 1 warning (0.000 sec)

I filed an issue in MariaDB's tracking system: MDEV-31940