| 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: | |
| 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 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.
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//