MySQL Bugs: #26287: savepoint in trigger

Bug #26287	savepoint in trigger
Submitted:	12 Feb 2007 14:28	Modified:	13 Feb 2007 11:07
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.36bk, 5.1 BK	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	savepoint, trigger

Description:
mysql accepts the "savpoint" sql, inside a trigger. However no savepoint is created.

(The variables are just inserted to show the trigger is executed)

How to repeat:
drop table if exists t1;
create table t1 (a int not null, b int not null, index (a,b)) engine=innodb;
insert into t1 values (1,2), (1,3), (2,3), (2,4);

drop trigger if exists tr1;
delimiter |
create trigger tr1 before update on t1 for each row begin
  set @a = 1;
  savepoint s1;
  set @b = 1;
end
|
delimiter ;

start transaction;
select @a, @b;
update t1 set a= 21 where b = 2;
select @a, @b;
rollback to savepoint s1;
#ERROR 1305 (42000): SAVEPOINT s1 does not exist

Suggested fix:
Should savepoint be allowed inside a trigger?

similiar with rollback to savepoint, which produces an error, even so the
savepoint does exist...

drop table if exists t1;
create table t1 (a int not null, b int not null, index (a,b)) engine=innodb;
insert into t1 values (1,2), (1,3), (2,3), (2,4);

drop trigger if exists tr1;
delimiter |
create trigger tr1 after update on t1 for each row begin
  set @a = 1;
  rollback to savepoint s1;
  set @b = 1;
end
|
delimiter ;

start transaction;
savepoint s1;
select @a, @b;
update t1 set a= 21 where b = 2;
#ERROR 1305 (42000): SAVEPOINT s1 does not exist
select @a, @b;

Thank you for the report.

Verified as described in second comment:

....
update t1 set a= 21 where b = 2;
ERROR 42000: SAVEPOINT s1 does not exist
select @a, @b;
@a      @b
1       NULL

See also attached file. All versions are affected.

Initial description is not a bug according to AUTOCOMMIT feature. See also Bug #26290

test case

Attachment: bug26287.test (application/octet-stream, text), 496 bytes.

About the 1st part (setting savepoint inside trigger)
I do not see where the autocommit (as described in Bug #26290) does apply in this case.

The trigger is executed inside a transaction.
On the auto commit page it says that "start transaction" turns off autocommit, so there is no autocommit here?

There also is no statement that cause an implicit commit (not inside the transaction).

Outside the transaction the "create trigger" has an implicit commit and autocommit. But the statement is not supposed to be executed at trigger creation.

actually both not a bug.

In both cases we are in a trigger, and therefore in a new savepoint isolation level.

In the 1st case: The savepoint is only valid till the end of the trigger

In the 2nd case: The savepoint would have needed to be created in a trigger.