ERROR 1305 (42000): SAVEPOINT ... does not exist

Question

I have this SQL in my MYSQL DB (sproc with empty body so I guess no implicit commits ?).

DROP PROCEDURE IF EXISTS doOrder;

DELIMITER $$

CREATE PROCEDURE doOrder(IN orderUUID VARCHAR(40))
  BEGIN
    SAVEPOINT sp_doOrder;

    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_doOrder;

      -- doing my updates and selects here...

    END;

    RELEASE SAVEPOINT sp_doOrder;
  END $$

DELIMITER ;

When I

call doOrder('some-unique-id');

I get: ERROR 1305 (42000): SAVEPOINT sp_doOrder does not exist.

I might overlook something... Any idea?

Answer 1

Since this is the top answer on Google when searching for "savepoint does not exist", I'll add my solution here as well.

I had a TRUNCATE statement within the code executed in my transaction, which caused an implicit commit and thus ended the transaction. Creating a savepoint outside of a transaction does not cause an error, it will just not be executed. This means the first time you'll notice something is wrong is when you try to release your savepoint / rollback it back.

This is the full list of statements that cause an implicit commit: https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html