Bug #1046 delete... order by crashes the server
Submitted: 13 Aug 2003 18:19 Modified: 15 Aug 2003 5:43
Reporter: Matthias Urlichs Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.1.0 OS:Linux (Linux 2.4.21)
Assigned to: CPU Architecture:Any

[13 Aug 2003 18:19] Matthias Urlichs 
Description:
A delete statement with order-by crashes the server.

How to repeat:
DROP TABLE IF EXISTS ispdomain;
CREATE TABLE ispdomain (
  LFD int(11) NOT NULL default '0',
  TSTAMP int(11) default NULL,
  VERTRAG varchar(20) default NULL,
  ID varchar(20) NOT NULL default '',
  AKTIV enum('0','1') default '1',
  NAME varchar(65) NOT NULL default '',
  SUPER int(11) NOT NULL default '0',
  MODUS int(11) default '1080060',
  UNIQUE KEY ISPDOMAIN4 (SUPER,NAME),
  UNIQUE KEY ISPDOMAIN5 (ID),
  UNIQUE KEY LFD (LFD),
  KEY ISPDOMAIN3 (VERTRAG),
  KEY ISPDOMAIN_SUPER (SUPER),
  KEY ISPDOMAIN_VERTRAG (VERTRAG),
  FOREIGN KEY (`SUPER`) REFERENCES `ispdomain` (`LFD`) ON UPDATE CASCADE,
  FOREIGN KEY (`VERTRAG`) REFERENCES `vrtk` (`VTNR`) ON UPDATE CASCADE
) TYPE=InnoDB CHARSET=latin1;

--
-- Dumping data for table 'ispdomain'
--
SET FOREIGN_KEY_CHECKS=0;

/*!40000 ALTER TABLE ispdomain DISABLE KEYS */;
LOCK TABLES ispdomain WRITE;
INSERT INTO ispdomain VALUES (0,0,NULL,'DOM-ROOT','1','.',0,1080060),(1,1060822628,NULL,'d_ex','0','example',0,1080060),(2,1060822628,'Vertrag1','d_k1','0','test1',1,1080061),(3,1060822628,'Vertrag2','d_k2','0','test2',1,1080061),(4,1060822628,NULL,'d_wk1','0','www',2,1080062),(5,1060822628,NULL,'d_wk2','0','www',3,1080062);
UNLOCK TABLES;

SET FOREIGN_KEY_CHECKS=1;

delete from ispdomain where id>0 order by super desc;

< server crashes >
[14 Aug 2003 5:47] MySQL Verification Team 
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

Actually, for 4.1.1 it is not available for download, but is fetchable from
our BK public repository.

I do not get crash, as this is fixed in 4.1.1.

But I get a correct error message:

  { 150,"Foreign key constraint is incorrectly formed"},
[15 Aug 2003 3:21] Matthias Urlichs 
Please remove the

   FOREIGN KEY (`VERTRAG`) REFERENCES `vrtk` (`VTNR`) ON UPDATE CASCADE

part, it refers to a table you don't have. (The error message, by the way, could be a whole lot better than "incorrectly formed".)

If that shows that the problem is solved, good. Your message didn't really say whether you actually got to the last line of my SQL commands.
[15 Aug 2003 5:43] MySQL Verification Team 
I did ran it to the end and it worked like a charm.

Not a bug.