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

mysql savepoints
uthomas picture uthomas · Oct 29, 2013 · Viewed 13.3k times · Source

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

Bart picture Bart · Nov 13, 2018

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

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More