[SOLVED] ERROR 1305 (42000): SAVEPOINT ... does not exist

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

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?

Solution

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