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 15:16	Modified:	11 Apr 2006 21:31
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.19	OS:	Linux (Linux 2.4.21 (SuSE Professional))
Assigned to:		CPU Architecture:	Any

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!