Problem with SavePoint in mysql

Question

I am trying to use savepoints in MySQL and it seems like something is going wrong.

MySQL Transaction Protocol

I get an error as shown below:

ERROR 1305 (42000): SAVEPOINT sp_prc_work does not exist

My procedures with or without savepoints work exactly the same. I expected the value "4", "pqr" should not appear in the table, as the whole transaction will be rejected. But both, 3 and 4 identifiers are inserted. I understand why there is an entry "3", "pqr", but I think that the identifier "4" should not be there.

drop table if exists test.savepoint_test;
drop procedure if exists second_fail;
drop procedure if exists prc_work;

CREATE TABLE test.savepoint_test (
id int not null default '0',
name varchar(100),
primary key (id)
)engine=InnoDB;

insert into test.savepoint_test values ('1', 'abc');
insert into test.savepoint_test values ('2', 'xyz');

select * from test.savepoint_test;

delimiter $$

CREATE PROCEDURE second_fail()
BEGIN
        INSERT  into test.savepoint_test values ('3', 'pqr');
        INSERT  into test.savepoint_test values ('2', 'mnp');
END;

$$

CREATE PROCEDURE prc_work()
BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;
        SAVEPOINT sp_prc_work;
        INSERT  into test.savepoint_test values ('4', 'pqr');
        INSERT  into test.savepoint_test values ('2', 'mnp');
END;

$$

delimiter ;


call second_fail();

select * from test.savepoint_test;

call prc_work();

select * from test.savepoint_test;

Answer 1

Change this line

    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;
    SAVEPOINT sp_prc_work;

to

    SAVEPOINT sp_prc_work;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;

This should fix the problem, you tell mysql to roll back to a nonexistent save point

http://dev.mysql.com/doc/refman/5.0/en/savepoint.html

DC

, ,

drop table if exists test.savepoint_test;
drop procedure if exists second_fail;
drop procedure if exists prc_work;

CREATE TABLE test.savepoint_test (
id int not null default '0',
name varchar(100),
primary key (id)
)engine=InnoDB;

delimiter $$

CREATE PROCEDURE second_fail()
BEGIN
        INSERT  into test.savepoint_test values ('3', 'pqr');
        INSERT  into test.savepoint_test values ('4', 'mnp');
END;

$$

CREATE PROCEDURE prc_work()
BEGIN
        SAVEPOINT sp_prc_work;
        BEGIN
                DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK TO sp_prc_work;
                INSERT  into test.savepoint_test values ('5', 'cat');
                INSERT  into test.savepoint_test values ('2', 'dog');
        END;
        RELEASE SAVEPOINT sp_prc_work;
END;

$$

delimiter ;

START TRANSACTION;

select 'test point 1' as ``;

insert into test.savepoint_test values ('1', 'abc');      
insert into test.savepoint_test values ('2', 'xyz');

select * from test.savepoint_test;

select 'test point 2' as ``;

call second_fail();    

select * from test.savepoint_test;

select 'test point 3'  as ``;

call prc_work();

select * from test.savepoint_test;

select 'test point 4' as ``;

COMMIT;  

DC