MySQL Bugs: #10822: Server hangs or crash with DELETE FROM table WHERE EXISTS(...)

Bug #10822	Server hangs or crash with DELETE FROM table WHERE EXISTS(...)
Submitted:	24 May 2005 11:32	Modified:	4 Aug 2005 14:03
Reporter:	Manfred Wiedemeier	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.12/4.1.14 BK	OS:	Windows (win xp sp2/Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
MySQL Server 4.1.12 hangs or crashs to following command:

DELETE FROM `D02S` 
WHERE EXISTS(SELECT * FROM `D02` WHERE (`D02`.`IDE`='erd' AND (`D02`.`AUTONUM` = `D02S`.`D02_AUTONUM`)))

In Version 4.1.11 this command works.
If you need a database just contact me at: wiedemeier.m@rmi.ch

Best regards

Manfred Wiedemeier

How to repeat:
Table structs:

CREATE TABLE `d02` (
  `RECID` int(11) NOT NULL auto_increment,
  `AUTONUM` int(11) default '0',
  `IDE` varchar(10) collate latin1_general_ci default '',
  `DATUM` datetime default '0000-00-00 00:00:00',
  `ENDDATUM` datetime default '0000-00-00 00:00:00',
  `DAUER` varchar(10) collate latin1_general_ci default '',
  `DIRKUNDE` varchar(30) collate latin1_general_ci default '',
  `DIRPERSON` varchar(30) collate latin1_general_ci default '',
  `ADRESSE` varchar(30) collate latin1_general_ci default '',
  `PLZ` smallint(6) default '0',
  `ORT` varchar(25) collate latin1_general_ci default '',
  `ARBEIT` mediumtext collate latin1_general_ci,
  `ANWESEND` tinyint(4) default '0',
  `MUTNUM` int(11) default '0',
  `PROJEKTNUM` varchar(15) collate latin1_general_ci default '',
  `PROJEKTBEZ` varchar(60) collate latin1_general_ci default '',
  `DELETED` tinyint(4) default '0',
  `ERF_TIME` datetime default '0000-00-00 00:00:00',
  `ERFASST` varchar(10) collate latin1_general_ci default '',
  `MUT_TIME` datetime default '0000-00-00 00:00:00',
  `MUTATOR` varchar(10) collate latin1_general_ci default '',

  PRIMARY KEY  (`RECID`),
  UNIQUE KEY `AUTONUM` (`AUTONUM`),
  KEY `KEY` (`IDE`),
  KEY `KEY1` (`DATUM`),
  KEY `MUTNUM` (`MUTNUM`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=FIXED;

CREATE TABLE `d02s` (
  `RECID` int(11) NOT NULL auto_increment,
  `D02_AUTONUM` int(11) default '0',
  `MAPI_ENTRY_ID` char(255) collate latin1_general_ci default '',
  `DELETED` tinyint(4) default '0',
  `D02_MUT_TIME` datetime default '0000-00-00 00:00:00',
  `MAPI_MUT_TIME` datetime default '0000-00-00 00:00:00',
  `ERF_TIME` datetime default '0000-00-00 00:00:00',
  `ERFASST` char(10) collate latin1_general_ci default '',
  `MUT_TIME` datetime default '0000-00-00 00:00:00',
  `MUTATOR` char(10) collate latin1_general_ci default '',

  PRIMARY KEY  (`RECID`),
  UNIQUE KEY `KEY` (`D02_AUTONUM`),
  KEY `MAPI_ID` (`MAPI_ENTRY_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=FIXED;

Yesy please provide the database. You can upload it at:

ftp://ftp.mysql.com/pub/mysql/upload/

into a zip file and its name making reference to this bug report
number.

Thanks in advance.

Could not upload the database, so i attached it.

I am experiencing a similar problem with a query like this:

DELETE FROM test_P_Cookie WHERE ViewID IN (    SELECT ViewID  FROM test_x_RM_4508_48875 )

test_x_RM_4508_48875 contains 180139 rows. test_P_Cookie contains 22,957,702 rows. MySQL appears to hang when running this query - no data is ever deleted as far as I can tell. There does not appear to be any iowait problem and CPU is about 50 % in use if top is to be believed.

Table structures:

CREATE TABLE `test_x_RM_4508_48875` (
  `ViewID` int(11) NOT NULL default '0',
  `VisitID` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

CREATE TABLE `test_P_Cookie` (
  `ViewID` int(11) NOT NULL default '0',
  `P_CookieID` int(11) NOT NULL default '0',
  UNIQUE KEY `test_P_Cookie1` (`ViewID`,`P_CookieID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

Tested in MySQL 4.1.8 and 4.1.12

Cannot attach a file to the bug - please contact me to get a dump of the database. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

reopened as wrongly closed

Sorry...but I can't see attached database. Could you please send the database to my e-mail ?

How can i attach my database? My zip-file is 3MB. 
But I send the file again to your upload server: ftp://ftp.mysql.com/pub/mysql/upload/.
The filename is: bug_10822.zip.

Thanks for database.
Tested on Win 2003 Sp4, MySQL server 4.1.13

Tested on linux 2.6.11-gentoo-r7 x86_64 AMD, Windows XP SP2

I can duplicate this. Would you like the test case I mentioned in my earlier post? I cannot attach it.