MariaDB: Rollback the transaction if a warning occurs

In MariaDB most errors cause the current transaction to rollback, but warnings don’t. This article shows how to rollback when a warning happens.

Suppose we want to insert a row into a table, and rollback the transaction if any error or warning occurs. Here’s the SQL code:

BEGIN NOT ATOMIC
    DECLARE EXIT HANDLER
        FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;
    START TRANSACTION;
    INSERT INTO client (name, surname) VALUES ('John', 'Smith');
    COMMIT;
END;

MariaDB procedural SQL allows to use DECLARE HANDLER construct to handle errors and warnings. Because this construct is only available with procedural SQL, we have to start a BEGIN NOT ATOMIC block – unless we are already in a stored procedure, a stored function, a trigger, or an event.

SQLEXCEPTION means that the handler will handle any error or warning condition with an SQLSTATE starting with '01', '02', or '03'. It is an EXIT handler, so it will run ROLLBACK and quit.

MySQL

The same technique works in MySQL. However MySQL does not support BEGIN NOT ATOMIC blocks, so this can only be done in stored procedures, stored functions, triggers and events.

SQL_MODE

The SQL_MODE variable determines whether certain anomalies will cause a warning or an error. The available flags vary depending on your MariaDB or MySQL version. Check the documentation for your MariaDB or MySQL version for the details.

If you are worried about a particular warnings and want to turn it into a fatal error, check if it is possible to do so using SQL_MODE.

Reference

From MariaDB documentation:

• DECLARE HANDLER
• BEGIN END