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 3:32]
Peter Gulutzan
[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.