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
'03'. It is an
EXIT handler, so it will run
ROLLBACK and quit.
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 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
From MariaDB documentation: