Bug #79555 savepoint name can not reuse in function or triigger

• Submitted: 8 Dec 2015 9:23
• Modified: 14 Dec 2015 12:05
• Reporter: zhang simon (OCA)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.5,5.6
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any
• Tags: savepoint function triigger

[8 Dec 2015 9:23] zhang simon

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

A new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored.

How to repeat:
delimiter //
drop procedure if exists p1//
create procedure p1()
begin
release savepoint a;
end//
delimiter ;

begin;
savepoint a;
call p1();
rollback to savepoint a;
ERROR 1305 (42000): SAVEPOINT a does not exist

[14 Dec 2015 12:05] MySQL Verification Team

Hi Zhang,

Thank you for a submission but this is not a bug.

mysql [localhost] {msandbox} (test) > delimiter //
mysql [localhost] {msandbox} (test) > drop procedure if exists p1//
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > create procedure p1()
    -> begin
    ->  savepoint a;
    ->  release savepoint a;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > delimiter ;
mysql [localhost] {msandbox} (test) > begin;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > savepoint a;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > call p1();
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > rollback to savepoint a;
ERROR 1305 (42000): SAVEPOINT a does not exist
mysql [localhost] {msandbox} (test) > commit;
Query OK, 0 rows affected (0.00 sec)

This is what you reported but this is expected behavior.
The documentation, as you properly cited, states that:
A new savepoint level is created when a stored *function* is invoked or a trigger is activated....

So a stored function, not a stored procedure as your test uses.

If we try the function as per documentation:

mysql [localhost] {msandbox} (test) > DROP FUNCTION IF EXISTS fx1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > DELIMITER //
mysql [localhost] {msandbox} (test) > CREATE FUNCTION fx1(d double) RETURNS VARCHAR(10)
    ->     DETERMINISTIC
    -> BEGIN
    ->     DECLARE ret varchar(10);
    ->     IF d > 100 THEN
    ->       SET ret = 'YEY';
    ->     ELSE
    ->       SET ret = 'NEY';
    ->     END IF;
    ->
    ->     -- this one should not exist
    -> RELEASE SAVEPOINT a;
    ->
    ->  RETURN (ret);
    -> END//

Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > DELIMITER ;
mysql [localhost] {msandbox} (test) > begin;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > savepoint a;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > -- we should get error here as a does not exist inside function
mysql [localhost] {msandbox} (test) > select fx1(10);
ERROR 1305 (42000): SAVEPOINT a does not exist

mysql [localhost] {msandbox} (test) > -- this one now should be ok
mysql [localhost] {msandbox} (test) > rollback to savepoint a;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > commit;
Query OK, 0 rows affected (0.00 sec)

So as you see, the behavior is exactly as documented.

kind regards
Bogdan Kecman