Bug #16449 mysqld crash during big delete query
Submitted: 12 Jan 2006 12:22 Modified: 2 Feb 2006 8:14
Reporter: kost kost Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.18 OS:FreeBSD (FreeBSD 5.1-RELEASE)
Assigned to: CPU Architecture:Any

[12 Jan 2006 12:22] kost kost
Description:
I'm running phpbb forum on MySQL 5.0.18 server. I've noticed that mysql server crashes while executing big(more tahn 245K length) delete statement like this:
DELETE FROM phpbb_search_results                                               
                                    WHERE session_id NOT IN ('da9887b89083ffff8eac99f6cc937865', '02c017dbc195e7b031ad97d1fe3a5df2'52b50863ff3', '36998d079ac1df5b1237b5cf69914db8', 'f35113a9eb0757296c3a2b596fcd0e00', 'cc17322771ad2c3bafd59bb069f44eb6', '40a5629a3a42e087607b4ff1ba64fe7d2b5cda8'...) and etc.

In this case `phpbb_search_results` table is totaly empty!!! So I can suppose that problem is in mysqld.

p.s. MySQL 4.0 works fine with this statement

How to repeat:
I'm running phpbb forum on MySQL 5.0.18 server. I've noticed that mysql server crashes while executing big(more tahn 245K length) delete statement like this:
DELETE FROM phpbb_search_results                                               
                                    WHERE session_id NOT IN ('da9887b89083ffff8eac99f6cc937865', '02c017dbc195e7b031ad97d1fe3a5df2'52b50863ff3', '36998d079ac1df5b1237b5cf69914db8', 'f35113a9eb0757296c3a2b596fcd0e00', 'cc17322771ad2c3bafd59bb069f44eb6', '40a5629a3a42e087607b4ff1ba64fe7d2b5cda8'...) and etc.

In this case `phpbb_search_results` table is totaly empty!!! So I can suppose that problem is in mysqld.

p.s. MySQL 4.0 works fine with this statement
[12 Jan 2006 13:46] Valeriy Kravchuk
Thank you for a problem report. Please, send the SHOW CREATE TABLE phpbb_search_results results. Is there anything unusual in the error log (should be, if it is a crash)?
[1 Feb 2006 18:50] Guy Baconnière
I have added a new ticket Bug #17019 in order to publish files sample of this problem.

Today we have one server crashed out of memory because of this bug
the memory allowed to mysql at this point was 2G !!
[1 Feb 2006 23:19] Jorge del Conde
Thanks for your bug report
[1 Feb 2006 23:20] Jorge del Conde
This bug seems to be the same as the one reported here:

http://bugs.mysql.com/bug.php?id=15872
[2 Feb 2006 8:14] kost kost
Here is table structure:

CREATE TABLE `phpbb_search_results` (
  `search_id` int(11) unsigned NOT NULL default '0',
  `session_id` varchar(32) NOT NULL default '',
  `search_array` text NOT NULL,
  PRIMARY KEY  (`search_id`),
  KEY `session_id` (`session_id`)
) TYPE=MyISAM
[2 Feb 2006 16:26] Guy Baconnière
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

I hope you will fix this as soon as possible