MySQL Transaction in Stored Procedure does not ROLLBACK

Question

This is my stored procedure:

CREATE PROCEDURE `procedureName`
(
    IN var1 VARCHAR(45),
    IN var2 VARCHAR(100),
    IN var3 VARCHAR(45),
    IN var4 DATE,
    IN someMoreVars VARCHAR(100)
)
COMMENT 'just a comment'
BEGIN
    DECLARE tempVar INT DEFAULT -1;

    DECLARE exit handler for sqlexception
      BEGIN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'SQLError occured. Triggered ROLLBACK';
        -- ERROR
      ROLLBACK;
    END;

    DECLARE exit handler for sqlwarning
      BEGIN
      SIGNAL SQLSTATE '35000' SET MESSAGE_TEXT = 'SQLWarning occured. Triggered ROLLBACK';
        -- WARNING
      ROLLBACK;
    END;

    START TRANSACTION;
        INSERT INTO `schemaName`.`table1` (`column1`, `column2`, `moreColumns`)
            VALUES (var1, var2, moreVars);
        SELECT LAST_INSERT_ID() INTO tempVar;
        INSERT INTO `schemaName`.`table2` (`column1`, `column2`, `evenMoreColumns`)
            VALUES (tempVar, var3, var4);
    COMMIT;

END

Why does that stored procedure not rollback? When I use parameters that work for the first insert but raise an Exception on the 2nd insert this procedure does not rollback and I end up with data in the first table but no data in the 2nd table.

I am using InnoDB engine.

I have tested this behavior with MySQL Workbench and since it has this weird Autocommit behaviour built into the GUI I have also tested it by calling the procedure from an C# script.

I have also checked if the LAST_INSERT_ID() function is mentioned in the documentation as a function which causes an implicit commit. But have not found it in the list. Or does that function call another function which does cause an implicit commit? And if yes, is there any workaround? I need to call this function to use its PRIMARY KEY as a FOREIGN KEY in the second table. If this information helps: I am also using Triggers.

This is the insert trigger of one of the tables (they are in almost all tables on insert/update/delete):

CREATE DEFINER = CURRENT_USER TRIGGER `schema`.`table1_AFTER_INSERT` AFTER INSERT ON `table1` FOR EACH ROW
BEGIN
    INSERT INTO `schema`.`table1_audit` (`table1_audit_action`, `table1_audit_timestamp`, `table1_audit_user`, `table1_val1`, `table1_some_more_columns`)
    VALUES ('insert', CURRENT_TIMESTAMP(), SESSION_USER(), NEW.table1_val1, NEW.table1_some_more_columns);
END

Thanks :)

take a look in my answer here to sqlexecepotions https://stackoverflow.com/a/60351232/5193536 — nbk, Mar 27 '20 at 22:35
Thanks, that is definitely interesting. So you think it might be a Bug in the current MySQL Server version? But what I've done to cause an exception in the second insert was, setting one of the values to null in a column that is configured to be NOT NULL. I would say that this is not really a special exception, but probably one of the most common one. Do you really think that even this exception does not get handled correctly? — Evals, Mar 27 '20 at 22:43
It's likely the code in your trigger is causing a commit as the triggers on the first `INSERT` will run before the second `INSERT` — Nick, Mar 27 '20 at 22:48
As i not all errors trigger a sqlexecption, it depends where the error occurs. So get the correct number and then programm like i did in the second code block — nbk, Mar 27 '20 at 22:51
@Nick Ohh that makes sense to me. Is there any workaround to prevent that? I am going to try to explicitely set autocommit off in my procedure. But I guess that won't help because it should not make a difference in a transaction. Any other suggestions? — Evals, Mar 27 '20 at 23:11
It would depend on the code in your trigger. Can you [edit] that into the question? — Nick, Mar 27 '20 at 23:12
@Nick My own try with setting the autocommit inside of the stored procedure did not really work out. I've now edited my question and added one of the triggers. It's just a typical trigger to audit all changes. — Evals, Mar 27 '20 at 23:48

score 1 · Accepted Answer · answered Apr 13 '20 at 23:37

Just to keep this post up to date with the newest information: I found a solution to fix this error myself a few days/weeks ago.

As it turned out, the problem was that "ROLLBACK" has to be called before "SIGNAL". If someone has a detailed explanation why that is the case, feel free to add that information. But it is simply not possible to rollback after signaling an exception. So the following code should work:

DECLARE exit handler for sqlexception
BEGIN
    -- ERROR
  ROLLBACK;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'SQLError occured. Triggered ROLLBACK';
END;

And of course the same for SQLWarning.

MySQL Transaction in Stored Procedure does not ROLLBACK

1 Answers1