MySQL Bugs: #17023: Memory overwlow on big simple request

Bug #17023	Memory overwlow on big simple request
Submitted:	1 Feb 2006 21:05	Modified:	3 Feb 2006 9:45
Reporter:	Artem Prysyazhnuk	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Linux (RHEL R3 (Kernel 2.6.15.1))
Assigned to:		CPU Architecture:	Any

Description:
Hello, 

I migrate from 4.1.11 to 5.0.18 and found one problem with mysql server. I install precompiled  rpm version of Mysql using next packages:

MySQL-client-standard-5.0.18-0.rhel3.i386.rpm  MySQL-server-standard-5.0.18-0.rhel3.i386.rpm
MySQL-devel-standard-5.0.18-0.rhel3.i386.rpm   MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm

I have table with 500 000 records. When I run simple but big (about 2Mb) request like this

select min(event_id) FROM shedule WHERE event_id <= 218134277 AND event_id NOT IN (218134244,218134111,218134110,218134108, ......... , 612,110,80,76,7,5,4);

Server get all memory from system and after memory out kernrl kill mysqld:

Feb  1 14:34:45 mado kernel: Out of Memory: Killed process 10891 (mysqld).

I try this query on two machines with 512Mb and 1Gb Memory resultat same.

On MySQL 4.1.11 before upgrade request executed without any problems.

How to repeat:
Bzipped dump of my table and problematic script you can find here:

http://beta.host-tracker.com/shedule.sql.bz2  - 15 MB
http://beta.host-tracker.com/bad_query.sql.bz2 - 1 MB

Only restore table and run query:

bzcat shedule.sql.bz2 | mysql test
bzcat bad_query.sql.bz2 | mysql test

In my config file I add only max_allowed_packet=16M parametr:

# cat /etc/my.cnf 
[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log=/var/log/mysql.queries
max_allowed_packet=16M
     
[mysql.server]
user=mysql
#basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Thanks for your bug report

This bug seems to be the same as the one reported here:
http://bugs.mysql.com/bug.php?id=15872

May be this bug is simmilar to http://bugs.mysql.com/bug.php?id=15872 but with two exceptions:

1) It's about SELECT statement not UPDATE as in 15872;
2) On empty table my statement execute OK without memeory owerflow, exception raised only on big table.

Send, please, the results of 

EXPLAIN select min(event_id) FROM shedule WHERE event_id <= 218134277 AND event_id NOT IN (218134244,218134111,218134110,218134108, ......... , 612,110,80,76,7,5,4);

and SHOW CREATE TABLE shedule results. How many items are there in that NOT IN list?

After running full query 
EXPLAIN select min(event_id) FROM shedule WHERE event_id <= 218134277 AND
event_id NOT IN (218134244,218134111,218134110,218134108, ......... ,
612,110,80,76,7,5,4);

Server died - same as without EXPLAIN. 

If I cut query to several event_id in not in statement, resultat:

EXPLAIN select min(event_id) FROM shedule WHERE event_id <= 218134277 AND event_id NOT IN (218134244,218134111,218134110,218134108);
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | shedule | range | PRIMARY       | PRIMARY | 4       | NULL | 320879 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.16 sec)

SHOW CREATE TABLE shedule;

CREATE TABLE `shedule` (
  `event_id` int(11) NOT NULL auto_increment,
  `task_id` int(11) NOT NULL default '0',
  `event_user_id` int(11) NOT NULL default '0',
  `first_fail_event_id` int(11) NOT NULL default '-1',
  `fail_event_ord` int(11) NOT NULL default '0',
  `shost_id` int(11) NOT NULL default '0',
  `request_start_time` double NOT NULL default '-1',
  `prev_event_id` int(11) NOT NULL default '-1',
  `prev_request_start_time` double NOT NULL default '-1',
  `request_done_time` double NOT NULL default '-1',
  `response_time` double NOT NULL default '-1',
  `status` enum('skipped','wait','ok','error') NOT NULL default 'wait',
  `ext_status_id` int(11) NOT NULL default '-1',
  `size` int(11) NOT NULL default '-1',
  `check_backward_link` enum('true','false') NOT NULL default 'false',
  `notify_sent` enum('error_notify','recovery_notify','none') NOT NULL default 'none',
  PRIMARY KEY  (`event_id`),
  KEY `task_id_time` (`task_id`,`request_start_time`),
  KEY `request_start_time` (`request_start_time`,`task_id`),
  KEY `request_start_time_3` (`request_start_time`,`prev_request_start_time`),
  KEY `status` (`status`),
  KEY `task_id` (`task_id`,`first_fail_event_id`),
  KEY `first_fail_event_id` (`first_fail_event_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Length of NOT IN statement is 306535 elements.

As UPDATE also search rows (perform SELECT internally), I think, this bug report is a duplicate of bug #15872 (or vice versa), but that bug is already verified and still not yet fixed.