Search code examples
mysqlsqlstored-procedureshandler

How to rollback all transaction statements from handler when one of them throws an error?

I have the following SQL stored procedure:

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK; 
    END;

    START TRANSACTION;
        INSERT INTO users (user_id) VALUES (55556666);
        DROP TABLE xaxa;
    ROLLBACK; 
END

The DROP TABLE xaxa; statement throws an error because such DB does not exist, however, the INSERT statement is executed and in phpmyadmin I can see the inserted user. However, if I remove the troublesome statement DROP TABLE xaxa; the INSERT is rolled back. Can someone help me rollback the insert from the handler if it is possible?

Solution

  • DROP TABLE statement can't be rolled back and it causes an implicit commit. https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html