Bug #17260 Multiple invocations of triggers or stored functions hog memory
Submitted: 9 Feb 2006 3:32 Modified: 22 May 2006 1:50
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0 OS:Linux (SUSE 10.0)
Assigned to: Dmitry Lenev CPU Architecture:Any

[9 Feb 2006 3:32] Peter Gulutzan
Description:
Running as user=root,
I created a 1,000,000-row table containing random dates and numbers.
I did a few selects, created an index, and created an update trigger.
I said "update ..." in a way that will activate the trigger, although in
fact the intent of the trigger is to produce a net effect of nothing.
After a long long time, the server crashes.

How to repeat:
delimiter //
drop table tp2//
drop procedure pp2//
create table tp2 (column1 date, column2 int)//
create procedure pp2 () begin declare v int default 0; while v < 1000000 do insert into tp2 values (adddate('1995-01-01',(rand(v)*36520) mod 3652),v); set v = v + 1; end while; end// 
call pp2()//
select count(*) from tp2 where column1 = date '2000-01-01';//
select count(*) from tp2 where column1 > date '2000-01-01';//
select count(*) from tp2 where column1 < date '2000-01-01';//
select count(*) from tp2 where year(column1) = 1997//
create index tp2_i1 on tp2 (column1)//
select count(*) from tp2 where column1 = date '2000-01-01'//
select count(*) from tp2 where column1 > date '2000-01-01'//
select count(*) from tp2 where column1 < date '2000-01-01'//
select count(*) from tp2 group by year(column1)//
create trigger tp2_t before update on tp2 for each row
begin declare v int; set v = old.column2; set new.column1 = adddate('1995-01-01',(rand(v)*36520) mod 3652); end//
update tp2 set column1 = column1 + 1//
[9 Feb 2006 7:26] Jorge del Conde
Tested under FC4
[9 Feb 2006 11:32] Dmitry Lenev
It seems that usage of local stored routine variable forces trigger to hog memory (just look at the "top" output for the mysqld while update statement is running). Once you will rewrite trigger body in such way that it will not use "v" variable speed of memory consumption will dramatically decrease.
[29 Mar 2006 9:50] 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/commits/4265
[29 Mar 2006 11:51] 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/commits/4270
[31 Mar 2006 12:32] Dmitry Lenev
A bit simplier test case for this problem:

------------------------------
drop table if exists t1;
create table t1 (i int);
# Populate table with big number of rows
insert into t1 values (1), (1);
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
insert into t1 select 1 from t1;
# create trigger which does nothing but contains declaratio of local variable
delimiter |
create trigger t1_bu before update on t1 for each row begin declare v int; end|
# This statement will hog memory
update t1 set i = 100|
--------------------------------------

This problem also present for stored functions. For example:

---------------------------------------
delimiter |
drop function if exists f1|
create function f1 () returns int begin declare v int; return 0; end|
# This will hog memory as well
select benchmark(1000000, f1())|
[11 Apr 2006 21:24] Dmitry Lenev
Bug #18961 was marked as duplicate of these one.
[12 Apr 2006 10:54] 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/commits/4848
[5 May 2006 14:11] Konstantin Osipov
A review done over email.
[6 May 2006 9:52] 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/commits/6045
[6 May 2006 10:15] Konstantin Osipov
The second patch is approved over email.
[18 May 2006 21:03] Dmitry Lenev
Fixed in 5.0.22 and 5.1.11
[22 May 2006 1:50] Paul Dubois
Noted in 5.022, 5.1.11 changelogs.

Revised memory allocation for local objects within stored
functions and triggers to avoid memory leak for repeated
function or trigger invocation.