Description:
An assertion (indicated below) fails in ha_rollback_trans() in file 'handler.cc', if the entire transaction is aborted in a stored function or trigger:
if (thd->in_sub_stmt)
{
/*
If we are inside stored function or trigger we should not commit or
rollback current statement transaction. See comment in ha_commit_trans()
call for more information.
*/
if (!all)
DBUG_RETURN(0);
DBUG_ASSERT(0); // <-------- FAILS
my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
DBUG_RETURN(1);
}
However, this can occur when a DEADLOCK or some other lock error occurs in a stored procedure. This is demonstrated by the script below.
How to repeat:
# Run the following script using mysql-test-run:
drop table if exists t1;
create table t1 (a smallint primary key) engine=innodb;
insert into t1 (a) values (40);
insert into t1 (a) values (50);
select * from t1;
delimiter |;
--disable_warnings
drop procedure if exists t3_update_conn2|
--enable_warnings
create function t3_update_conn1() returns int
begin
update t1 set a = 4 where a = 40;
return 100;
end|
create function t3_update_conn2() returns int
begin
update t1 set a = 5 where a = 50;
return 200;
end|
delimiter ;|
connect (conn1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connect (conn2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
connection conn1;
select * from t1;
begin;
update t1 set a = 5 where a = 50;
select * from t1;
connection conn2;
begin;
update t1 set a = 4 where a = 40;
select * from t1;
connection conn1;
-- Hangs waiting for conn2.
-- When the deadlock occurs, an assertion fails in ha_rollback_trans()
send select t3_update_conn1();
connection conn2;
-- This will cause a deadlock:
select t3_update_conn2();
select * from t1;
connection conn1;
reap;
select * from t1;
disconnect conn1;
disconnect conn2;
Suggested fix:
As the comment in ha_commit_trans(), states, rollback of a statement would require nested statement transactions.
However, it is probably necessary to handle the situation that an error occurs in a stored procedure, that leads to a rollback of the entire transaction.
This can be implemented without nested transactions. In other words:
if (!all)
DBUG_RETURN(0); // <---- This is OK
// This part should be implemented, by rollback
// of all engine transactions and aborting all procedure
// calls...
DBUG_ASSERT(0);
my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
DBUG_RETURN(1);
This change would only affect ha_rollback_trans(), ha_commit_trans() can remain as is.
Description: An assertion (indicated below) fails in ha_rollback_trans() in file 'handler.cc', if the entire transaction is aborted in a stored function or trigger: if (thd->in_sub_stmt) { /* If we are inside stored function or trigger we should not commit or rollback current statement transaction. See comment in ha_commit_trans() call for more information. */ if (!all) DBUG_RETURN(0); DBUG_ASSERT(0); // <-------- FAILS my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0)); DBUG_RETURN(1); } However, this can occur when a DEADLOCK or some other lock error occurs in a stored procedure. This is demonstrated by the script below. How to repeat: # Run the following script using mysql-test-run: drop table if exists t1; create table t1 (a smallint primary key) engine=innodb; insert into t1 (a) values (40); insert into t1 (a) values (50); select * from t1; delimiter |; --disable_warnings drop procedure if exists t3_update_conn2| --enable_warnings create function t3_update_conn1() returns int begin update t1 set a = 4 where a = 40; return 100; end| create function t3_update_conn2() returns int begin update t1 set a = 5 where a = 50; return 200; end| delimiter ;| connect (conn1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connect (conn2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection conn1; select * from t1; begin; update t1 set a = 5 where a = 50; select * from t1; connection conn2; begin; update t1 set a = 4 where a = 40; select * from t1; connection conn1; -- Hangs waiting for conn2. -- When the deadlock occurs, an assertion fails in ha_rollback_trans() send select t3_update_conn1(); connection conn2; -- This will cause a deadlock: select t3_update_conn2(); select * from t1; connection conn1; reap; select * from t1; disconnect conn1; disconnect conn2; Suggested fix: As the comment in ha_commit_trans(), states, rollback of a statement would require nested statement transactions. However, it is probably necessary to handle the situation that an error occurs in a stored procedure, that leads to a rollback of the entire transaction. This can be implemented without nested transactions. In other words: if (!all) DBUG_RETURN(0); // <---- This is OK // This part should be implemented, by rollback // of all engine transactions and aborting all procedure // calls... DBUG_ASSERT(0); my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0)); DBUG_RETURN(1); This change would only affect ha_rollback_trans(), ha_commit_trans() can remain as is.