MySQL Bugs: #47980: Memory usage DOS against Mysql Server using dependent sub query;

Bug #47980	Memory usage DOS against Mysql Server using dependent sub query;
Submitted:	11 Oct 2009 16:47	Modified:	12 Oct 2009 15:03
Reporter:	Kal McFate	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.77	OS:	Linux (Rhel 5.4)
Assigned to:		CPU Architecture:	Any
Tags:	denial of service, DoS, Memory

Description:
Specially crafted query executed by anyone with read access to two tables with a good number of rows (10000 or so for 1GB ram + 2GB swap) can exhaust all swap and memory on a server causing server to crash.

How to repeat:
Create database and two tables:

CREATE DATABASE bugtest;
USE bugtest; 

CREATE TABLE `A` (
  `id` int(11) NOT NULL default '0',
  `val` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `B` (
  `id` int(11) NOT NULL default '0',
  `val` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Populate:

for x in `seq 1 10000`; do mysql -e "insert into A values ($x,$x); insert into B values ($x,$x);" bugtest; done

Query:

SELECT *,count(*) count FROM A,B WHERE B.id IN( SELECT DISTINCT id FROM A WHERE A.id=B.id) GROUP BY A.id ORDER BY count DESC;

Sit back and watch memory usage climb till death.

Suggested fix:
Prevent mysql from creating excessive in memory tmp tables. Even with tmp_table_size very low none of this is pushed out to disk.

FYI: It is the 'distinct' tag in the subquery that causes this behavior. Without it, the query is fine and does not use temp table in the subquery;

mysql> describe SELECT *,count(*) count FROM A,B WHERE B.id IN( SELECT DISTINCT id FROM A WHERE A.id=B.id) GROUP BY A.id ORDER BY count DESC;
+----+--------------------+-------+--------+---------------+---------+---------+--------------+-------+-------------------------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref          | rows  | Extra                                     |
+----+--------------------+-------+--------+---------------+---------+---------+--------------+-------+-------------------------------------------+
|  1 | PRIMARY            | A     | ALL    | NULL          | NULL    | NULL    | NULL         | 10000 | Using temporary; Using filesort           | 
|  1 | PRIMARY            | B     | ALL    | NULL          | NULL    | NULL    | NULL         | 10000 | Using where                               | 
|  2 | DEPENDENT SUBQUERY | A     | eq_ref | PRIMARY       | PRIMARY | 4       | bugtest.B.id |     1 | Using where; Using index; Using temporary | 
+----+--------------------+-------+--------+---------------+---------+---------+--------------+-------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> describe SELECT *,count(*) count FROM A,B WHERE B.id IN( SELECT id FROM A WHERE A.id=B.id) GROUP BY A.id ORDER BY count DESC;
+----+--------------------+-------+--------+---------------+---------+---------+--------------+-------+---------------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref          | rows  | Extra                           |
+----+--------------------+-------+--------+---------------+---------+---------+--------------+-------+---------------------------------+
|  1 | PRIMARY            | A     | ALL    | NULL          | NULL    | NULL    | NULL         | 10000 | Using temporary; Using filesort | 
|  1 | PRIMARY            | B     | ALL    | NULL          | NULL    | NULL    | NULL         | 10000 | Using where                     | 
|  2 | DEPENDENT SUBQUERY | A     | eq_ref | PRIMARY       | PRIMARY | 4       | bugtest.B.id |     1 | Using where; Using index        | 
+----+--------------------+-------+--------+---------------+---------+---------+--------------+-------+---------------------------------+

Please, try to repeat with a newer version, 5.0.86, and tmp_table_size + max_heap_table_size explicitly limited to, say, 100000. I do not see any increase in memory usage in Linux with 5.0.87 started with:

mysqld_safe --tmp_table_size=100000 --max_heap_table_size=100000 &

Confirm not affecting 5.0.86. RHEL5's 5.0.77-3 however is affected and not currently patched.
https://bugzilla.redhat.com/show_bug.cgi?id=528512

Then I think you should complain to RedHat and not to MySQL.  MySQL cannot fix issues *backwards*. All fixes will have to go into future releases of course. So asking for a fix in 5.0.77 leads nowhere. MySQL 5.0.77 is *HISTORY* - it is *frozen as it is*.

Why don't you simply upgrade the server with with 5.0.86 binary from the MySQL download page if you are concerned about this?

Peter
(not a MySQL person)

Not repeatable with current versions.