MySQL Bugs: #18961: Memory for stored function calls not released in time in INSERT INTO ... SELECT

Bug #18961	Memory for stored function calls not released in time in INSERT INTO ... SELECT
Submitted:	10 Apr 2006 17:16	Modified:	11 Apr 2006 23:31
Reporter:	Beat Vontobel (Silver Quality Contributor) (SCA)
Status:	Duplicate
Category:	Server	Severity:	S1 (Critical)
Version:	5.0.19	OS:	Linux (Linux 2.4.21 (SuSE Professional))
Assigned to:		Target Version:

Description:
INSERT/REPLACE INTO ... SELECT statements with even the most simple stored function in
the SELECT part fail due to immense memory needs if more than just a few rows are
affected.

(The server seems to allocate memory for the stored function call for every row but not
to free or reuse it again after that row has been processed.)

How to repeat:
On a default install execute these commands to prepare the test scenario:

-- --------

use test;

CREATE TABLE IF NOT EXISTS t (i INT);

delimiter //

CREATE PROCEDURE fill_t(IN e INT)
  MODIFIES SQL DATA
  DETERMINISTIC
BEGIN
  WHILE e > 0 DO
    INSERT INTO t (i) VALUES (e);
    SET e := e - 1;
  END WHILE;
END//

delimiter ;

CALL fill_t(1000000);

CREATE FUNCTION f(i INT) RETURNS INT
NO SQL DETERMINISTIC
RETURN i - 1;

CREATE TABLE IF NOT EXISTS t2 (i INT, `f(i)` INT);

-- --------

The number of rows in table t (argument to fill_t) maybe needs some adjustment depending
on the speed/memory of your machine. 1'000'000 rows was way enough for me to show the
effects on a 3 GHz machine with 1 GB of physical RAM and 512 MB of swap space.

Now run the following statement and have a look at the output of top or a similar tool to
see mysqld's memory needs increase steadily while the statement is executing (until the
machine starts to swap, eventually runs out of swap space - and maybe MySQL gives an out
of memory error message or the OS becomes unresponsive, the machine crashes... I've seen
different outcomes on different setups):

INSERT INTO t2 SELECT i, f(i) FROM t;

Suggested fix:
Free the allocated memory in time.

Locks up 5.0.19-max on Intel Mac, locked up the entire system, no messages in error log.

Getting latest build to verify and debug.

Yes, I really like OS X, but when it comes to memory management... ;-) Maybe you better
try to debug it on a Linux machine. On OS X the whole system will probably always lock up
before MySQL does - and you'll never get any error messages. I was very lucky when I
experienced this bug for the first time on our Linux production server that the machine
didn't crash. It became absolutely unresponsive, swapping like crazy, (couldn't even log
in to the shell) - but MySQL was still handling connections, slowly, but it did. And it
aborted the query finally with an out of memory error and the server recovered before I
could reboot!

Hi, Beat!

I think that you are experiencing problem which was already described in report for bug
#17260. So I will mark this bug report as duplicate and update bug #17260 to make it more
clear.

Thank you for your continued interest in MySQL (and particularly in its stored routines
and triggers)!

Thanks to you, Dmitri, I'm very happy to see that this is already in progress!