MySQL Bugs: #17019: Hudge DELETE queries consume all memory and cpu resources

Bug #17019	Hudge DELETE queries consume all memory and cpu resources
Submitted:	1 Feb 2006 18:29	Modified:	2 Feb 2006 16:22
Reporter:	Guy Baconnière	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Linux (Debian Sarge/Kernel 2.6.15/Libc)
Assigned to:		CPU Architecture:	Any

Description:
Same issue described on Bug #16449

Confirmed on both MySQL 5.0.15 and 5.0.18

New ticket in order to post archive with a sample
because I was unable to post on original Bug ticket.

How to repeat:
See attachment files

bug.sql part1

Attachment: bug.sql-part1 (text/plain), 200.00 KiB.

bug.sql part2

Attachment: bug.sql-part2 (text/plain), 200.00 KiB.

bug.sql part3

Attachment: bug.sql-part3 (text/plain), 31.82 KiB.

data and structure

Attachment: data.sql (text/plain), 13.02 KiB.

mysql config file

Attachment: my.cnf (text/plain), 5.01 KiB.

The query is such huge even compressed with bzip2 I am unable to upload it (>200k limitation on your web site)

to join splited parts do this (you know ;-)

$ cat bug.sql-part{1,2,3} > bug.sql
-or-
$ cat bug.sql-part{1..3} > bug.sql

PHPBB
http://www.phpbb.com/bugs/viewreport.php?b=939

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

When do plan to resolve this critical issue ?

PHPBB a very popular forum can DoS any MySQL 5.0 server
using NOT IN (... 400k of IDs ...). By the way anyone flooding
a PHPBB sessions table online can make MySQL 5.0 use all
memory / resources... 

I have proposed to PHPBB Team a SQL alternative to NOT IN ()
see http://www.infomaniak.ch/phpbb2/phpBB2-MySQL5.txt
http://www.infomaniak.ch/phpbb2/phpBB2-MySQL5.patch