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?

score 6 · Answer 1 · answered Nov 13 '18 at 16:07

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

Great hint. In my case was a `DROP TEMPORARY TABLE` changed to `DROP TABLE`. Very useful link. Thank you. — Niki Romagnoli, Nov 17 '21 at 09:52

udog · Accepted Answer · 2013-10-30T01:42:41.777

3

You have to use START TRANSACTION instead of BEGIN to start a transaction in a stored procedure.

Also, you may need to move the SAVEPOINT statement to be after the DECLARE (depending upon where you put the START TRANSACTION)

Note

Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. Begin a transaction in this context with START TRANSACTION instead.

Cf: http://dev.mysql.com/doc/refman/5.6/en/commit.html

edited Oct 30 '13 at 01:42

answered Oct 30 '13 at 01:37

udog

1,490
2
17
30

I tried to add some syntax highlight in the answer but the edit queue is full now – Valerio Bozz May 17 '22 at 09:01

score 1 · Answer 3 · answered Oct 30 '13 at 11:17

This worked at the end. Thanks udog.

DROP PROCEDURE IF EXISTS doOrder;

DELIMITER $$

CREATE PROCEDURE doOrder(IN orderUUID VARCHAR(40))
  BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_order;

    START TRANSACTION;
    SAVEPOINT sp_order;

    -- doing my updates and selects here...

    COMMIT;

  END $$

DELIMITER ;

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

3 Answers3

Linked