[Maria-developers] Error 1422 - Explicit or implicit commit is not allowed in stored function or trigger
Hello Alexander, I'm facing to a new issue with my stored procedures. Some of them are called directly or from trigger. When these procedures are called directly, they manage the transaction and when they are called from trigger, they doesn't manage the transaction. This works fine on Oracle, SQLServer , ... but fail on Mariadb because the check is done in is_not_allowed_in_function() before the call. use test; set sql_mode=oracle; create or replace table t1 (c1 int); insert into t1 values (1); delimiter / create or replace procedure p1(manage_transactionnal int) is begin if manage_transactionnal = 1 then commit; end if; end; / create or replace trigger tg1 after delete on t1 for each row begin call p1(0); end; / delimiter ; start transaction; delete from t1; rollback; What do you think about this ? Regards, Jérôme.
Hi Jerome, On 11/28/2017 02:33 PM, jerome brauge wrote:
Hello Alexander, I'm facing to a new issue with my stored procedures. Some of them are called directly or from trigger. When these procedures are called directly, they manage the transaction and when they are called from trigger, they doesn't manage the transaction. This works fine on Oracle, SQLServer , ... but fail on Mariadb because the check is done in is_not_allowed_in_function() before the call.
Sorry, this part is out of scope of my knowledge. Can you please discuss with Sergei?
use test; set sql_mode=oracle; create or replace table t1 (c1 int); insert into t1 values (1); delimiter / create or replace procedure p1(manage_transactionnal int) is begin if manage_transactionnal = 1 then commit; end if; end; / create or replace trigger tg1 after delete on t1 for each row begin call p1(0); end; / delimiter ; start transaction; delete from t1; rollback;
What do you think about this ?
Regards, Jérôme.
Hello Alexander, No problem. Sergei, do you think that we could throw this error only when commit or rollback statement is really executed ? (and with the call stack as notes, like others errors in stored procedure)
-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.org] Envoyé : mercredi 29 novembre 2017 08:14 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: Error 1422 - Explicit or implicit commit is not allowed in stored function or trigger
Hi Jerome,
On 11/28/2017 02:33 PM, jerome brauge wrote:
Hello Alexander, I'm facing to a new issue with my stored procedures. Some of them are called directly or from trigger. When these procedures are called directly, they manage the transaction and when they are called from trigger, they doesn't manage the transaction. This works fine on Oracle, SQLServer , ... but fail on Mariadb because the check is done in is_not_allowed_in_function() before the call.
Sorry, this part is out of scope of my knowledge. Can you please discuss with Sergei?
use test; set sql_mode=oracle; create or replace table t1 (c1 int); insert into t1 values (1); delimiter / create or replace procedure p1(manage_transactionnal int) is begin if manage_transactionnal = 1 then commit; end if; end; / create or replace trigger tg1 after delete on t1 for each row begin call p1(0); end; / delimiter ; start transaction; delete from t1; rollback;
What do you think about this ?
Regards, Jérôme.
participants (2)
-
Alexander Barkov -
jerome brauge