| Bug #12712 | SET AUTOCOMMIT should fail within SP/functions/triggers | ||
|---|---|---|---|
| Submitted: | 22 Aug 2005 6:59 | Modified: | 14 Sep 2005 18:19 |
| Reporter: | Alexey Kopytov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.11 | OS: | |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[7 Sep 2005 15:33]
Per-Erik Martin
Why not?
[7 Sep 2005 17:50]
Dmitry Lenev
Hi, Per-Erik! Implicit or explicit commits are disallowed in stored functions, triggers and in stored procedures which are called from functions or triggers. All statements in such routines are executed in the same transaction as upper-level statement which calls them. In other words with auto-commit "turned off" (well at least for statements in routine). So there is not much sense in doing SET AUTOCOMMIT in stored function or trigger, moreover it is dangerous since SET AUTOCOMMIT=1 will commit current transaction and thus should be prohibited (BTW currently execution of function containing "SET AUTOCOMMIT=1" statement may easily crash server because of assertion failure). It is probably OK to do change auto-commit mode in stored procedure which is not called from function or trigger.
[13 Sep 2005 11:59]
Konstantin Osipov
Reviewed and discussed on IRC: the current patch disallows set autocommit in stored procedures, while BEGIN and COMMIT are currently allowed in stored procedures. Although both features are not in the standard, it seems reasonable to be consistent and allow SET autocommit in stored procedures, while giving an error in a trigger or stored functions.
[13 Sep 2005 15:49]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/29757
[14 Sep 2005 9:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/29813
[14 Sep 2005 9:38]
Konstantin Osipov
Approved by email.
[14 Sep 2005 9:49]
Per-Erik Martin
Fixed in bk 5.0.13. 'set autocommit' is not allowed in stored functions or triggers. (Detected at creation time) It's allowed in procedures, but might generate a run-time error when called from a function or trigger.
[14 Sep 2005 18:19]
Paul DuBois
Noted in 5.0.13 changelog.
Description: MySQL server should not allow to change a value of AUTOCOMMIT from stored procedures, functions and triggers. How to repeat: mysql> DELIMITER // mysql> CREATE FUNCTION f1() RETURNS INTEGER -> BEGIN -> SET AUTOCOMMIT=1; -> RETURN 100; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> select f1(); +------+ | f1() | +------+ | 100 | +------+ 1 row in set (0.01 sec)