issue with SavePoint in mysql

Question

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

MySQL transaction conundrum

I get an error as shown below:

ERROR 1305 (42000): SAVEPOINT sp_prc_work does not exist

My procedures with or without savepoints works exactly the same. What I expected was that the value '4', 'pqr' should not appear in the table since the entire transaction will be rolled back. But both, 3 and 4 ID's are inserted. I understand why does the entry '3', 'pqr' is there, but I guess the id '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;

That should fix the problem, you are telling mysql to rollback to a non-existent savepoint

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

DC

I have reworked your example into what I think you actually want

please note the begin transaction

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