Handling Errors in MYSQL Procedures with Rollback

f you have mysql stored procedure that changes database tables — you probably want to enclose the whole procedure in a single transaction and add error handling to roll back the work if anything fails.

Here is how to do it:
Key points:
Use START TRANSACTION at top after BEGIN
Use COMMIT at end

Turn on Transactions with START TRANSACTION after the BEGIN

And end the transaction with COMMIT at the end of your procedure

Add an error handling block that calls ROLLBACK work if there is any SQL EXCEPTION.

This is example SENDs a custom crafted ERROR message ( similar to RAISE_APPLICATION_ERROR in ORACLE ) which our PHP code recognizes and displays / logs.

Here is the magic:
CREATE
DEFINER = CURRENT_USER
PROCEDURE bocs_create_budget(psource_bud_id INT(11), ptitle VARCHAR(200), pfy INT(4), pout_years INT(11))
BEGIN

DECLARE new_bud_id INT(11);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT(“ERROR “, @errno, ” (“, @sqlstate, “): “, @text);
ROLLBACK;
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = @full_error, MYSQL_ERRNO = 1001;
END;

START TRANSACTION;

SET @TRIGGER_CHECKS = FALSE;

— Procedure work that may raise errors is done here

COMMIT;

SET @TRIGGER_CHECKS = TRUE;
END $$
DELIMITER ;