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 Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: transaction, triggers

[27 May 2015 3:58] Jaime Sicam
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.