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:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.11 OS:
Assigned to: Per-Erik Martin CPU Architecture:Any

[22 Aug 2005 6:59] Alexey Kopytov 
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)
[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.