MySQL Bugs: #31898: 16M memory allocations for user variables in stored procedure

Bug #31898	16M memory allocations for user variables in stored procedure
Submitted:	28 Oct 2007 18:58	Modified:	14 Dec 2007 19:40
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.23	OS:	Any
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any
Tags:	bfsm_2007_11_01, Memory

Description:
The attached stored procedure uses around 80+ MB of memory, which is not freed until the client closes the connection.  5.1 has this bug, 5.0 doesn't appear to suffer from the huge memory consumption.

Problem seems to occur because 16MB chunks of memory are allocated, see here:

T@5    :   item_func.cc:  4072:  >Item_func_set_user_var::check
T@5    :   item_func.cc:  4400:  | >Item_func_get_user_var::val_str
T@5    :   item_func.cc:  4403:  | <Item_func_get_user_var::val_str
T@5    :   safemalloc.c:   126:  | >_mymalloc
T@5    :   safemalloc.c:   127:  | | enter: Size: 8
T@5    :   safemalloc.c:   196:  | | exit: ptr: 0x2b3d408
T@5    :   safemalloc.c:   201:  | <_mymalloc
T@5    :   safemalloc.c:   126:  | >_mymalloc
T@5    :   safemalloc.c:   127:  | | enter: Size: 16777224
T@5    :   safemalloc.c:   196:  | | exit: ptr: 0x4f00058
T@5    :   safemalloc.c:   201:  | <_mymalloc

071028 20:37:10 [ERROR] /home/sbester/server/5.1/mysql-5.1.23-beta-linux-i686/bin/mysqld: Out of memory at line 51, 'sql_string.cc'
071028 20:37:10 [ERROR] /home/sbester/server/5.1/mysql-5.1.23-beta-linux-i686/bin/mysqld: needed 16777224 byte (16385k), memory in use: -1311277068 bytes (2913760k)

How to repeat:
see attached sql.

problem is significantly compounded when many variables are used (in a larger stored procedure) and many concurrent threads are running.

Suggested fix:
don't allocate too much memory.

testcase for memory usage which is too high on 5.1.22

Attachment: testcase_82MB_5.1.22.sql (application/octet-stream, text), 1.08 KiB.

simpler testcase to show the excessive memory usage:

drop procedure if exists `p1`;
delimiter //
create procedure `p1`()
begin
declare varb char(255) default 'abcdef_id';  	
set @varf="";
set @varf = concat(@varf,"......",varb);
end//
delimiter ;
call `p1`();

Approved over email.

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/37569

ChangeSet@1.2608, 2007-11-12 14:44:17+03:00, anozdrin@station. +1 -0
  Fix for a BUG#31898: 16M memory allocations for user variables
  in stored procedure.
  
  The problem was that MySQL used unnecessarily large amounts of
  memory if user variables were used as an argument to CONCAT or
  CONCAT_WS -- 16M per each user variable used.
  
  Technically, it happened because MySQL used the following
  allocation strategy for string functions to avoid multiple
  realloc() calls: in the virtual operation fix_length_and_dec()
  the attribute max_length was calculated as a sum of max_length
  values for each argument.
  
  Although this approach worked well for small (or fixed) data types,
  there could be a problem if there as a user variable among
  the arguments of a string function -- max_length of the function
  would be 16M (as the max_length of a user variable is 16M).
  
  Both CONCAT() and CONCAT_WS() functions suffer from this problem.
  
  The fix is to do not use meta-data for allocating memory.
  The following strategy is proposed instead: allocate the exact
  length of the result string at the first record, double the amount
  of memory allocated when it is required.
  
  No test case for this bug because there is no way to test memory
  consumption in a robust way with our test suite.

Pushed into 5.1-rt.

That would be nice to move this code into some Item_xxx method,
to avoid duplicate code and to make it possible to reuse it
in other functions. But I guess I'm too late with my review :)

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted in 5.1.23, 6.0.5 changelogs.

The server used unnecessarily large amounts of memory when user
variables were used as an argument to CONCAT() or CONCAT_WS().