Bug #77163 "ROLLBACK TO SAVEPOINT" is allowed inside a trigger

• Submitted: 27 May 2015 3:58
• Modified: 4 Jun 2015 13:26
• Reporter: Jaime Sicam
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any
• Tags: transaction, triggers

[27 May 2015 3:58] Jaime Sicam

Description:
According to https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html:

The trigger cannot use statements that explicitly or implicitly begin or end a transaction, such as START TRANSACTION, COMMIT, or ROLLBACK. 

However, "ROLLBACK TO SAVEPOINT" allowed inside a trigger

How to repeat:

mysql> delimiter //
mysql> create trigger ins1 after update on t1 for each row begin rollback; end//
ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.

mysql> create trigger ins1 after update on t1 for each row begin rollback to savepoint s1; end//
Query OK, 0 rows affected (0.01 sec)

[27 May 2015 5:40] MySQL Verification Team

also: http://bugs.mysql.com/bug.php?id=76727

[28 May 2015 11:32] MySQL Verification Team

Please check for duplicate: http://bugs.mysql.com/bug.php?id=26287. Thanks.

[28 May 2015 12:21] Valeriy Kravchuk

This is NOT a duplicate. 

We do know that savepoints can be set inside trigger and ROLLBACK TO SAVEPOINT can be used (and they even work as expected most of the time). At least (as test case proved) there is no error messages upon trigger creation when one tries to explicitly(!) use ROLLBACK TO SAVEPOINT in trigger code.

If this is by design, manual should clearly explain this, to clarify doubts. If this is wrong, manual still should be clarified and code should be changed to give proper error message upon trigger creation and/or at trigger run time.

[28 May 2015 14:54] MySQL Verification Team

I have to agree with Valeriy on this one. This is not a duplicate at all.

We do allow this particular command in the trigger(s), because ROLLBACK TO SAVEPOINT doesn't rollback the whole transaction. Or to put it more concisely, it does not end the transaction.

This is, simply, intended behavior.

However, we have not explicitly documented it, which makes this a documentation bug.

Verified as the documentation bug.

[4 Jun 2015 13:26] Paul DuBois

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.