Bug #72510 SELECT leaks memory
Submitted: 2 May 2014 2:15 Modified: 4 May 2014 15:39
Reporter: Hanno Foest Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.5.37-0ubuntu0.12.04.1 OS:Linux (Ubuntu 12.04)
Assigned to: CPU Architecture:Any
Tags: myisam, SELECT

[2 May 2014 2:15] Hanno Foest
Description:
After migrating a web forum from Ubuntu 8.04 (MySQL 5.0.96-0ubuntu3) to Ubuntu 12.04 (MySQL 5.5.37-0ubuntu0.12.04.1) I noticed that the memory allocation of MySQL was very high, the daemon grows about 6MB per forum page displayed and it grew to about 13 GB resident size in a day. With MySQL 5.0.96, I never noticed any problems.

The admin of the web forum was able to create a test case, see below. The circumstances of the bug apparently involve a certain kind of correlated subquery and a table with multiple indexes and a particular combination of row data, but the exact cause still remains mysterious to me.

The amount of memory leaked is proportional to the number of rows in table tab1; with 256 rows as created in the test case, it leaks about 1 MB per query.

How to repeat:
mysql -u root -p

create database test;
use test;

create table tab1 (col1 int) engine=MyISAM;

insert into tab1 (col1) values (1);
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;
insert into tab1 select * from tab1;

create table tab2 (col1 int, col2 int, key col1 (col1), key col2
(col2)) engine=MyISAM;

insert into tab2 (col1, col2) values (1, 1);
insert into tab2 (col1, col2) values (1, 2);
insert into tab2 (col1, col2) values (2, 1);
                                                                                
# And here's a query that trips the bug:

select curtime() as x from tab1 where (select 1 from tab2 where
col1=tab1.col1 and col2 in (3,4) order by col2 limit 1)=0;

Suggested fix:
Converting the database (or just tab2) to InnoDB seems to help, but I'm not sure how reliable that is as a workaround.
[4 May 2014 15:25] MySQL Verification Team
valgrind output

Attachment: bug72510_5.5.37_valgrind.txt (text/plain), 16.11 KiB.

[4 May 2014 15:39] MySQL Verification Team
Thanks for the bug report.
5.1 and 5.5 do leak memory with the testcase.
5.6 and 5.7 are already fixed by previous fixes, have you considered upgrade?
I'm not aware of any plans to backport the fix to 5.5.

See:  http://bugs.mysql.com/bug.php?id=71849