MySQL Bugs: #26480: on update cascade crashes when a foreign key belongs to the primary key

Bug #26480	on update cascade crashes when a foreign key belongs to the primary key
Submitted:	19 Feb 2007 17:31	Modified:	12 Apr 2007 14:20
Reporter:	Juan Luis Pérez Estrella	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	foreign key, on update cascade

Description:
The crash occurs when a table has two or more columns defined as primary key and one of them is a foreign key (updating and deleting on cascade) of other column defined as a primary key in the same table. When client tries to update a field belonging to the primary key referenced by the foreign key, and table has rows that depends of the value to be changed, query fails.

How to repeat:
CREATE TABLE `reflexiva` (
  `id1` varchar(10) NOT NULL,
  `id2` varchar(10) NOT NULL,
  `timestamp` bigint(20) NOT NULL,
  PRIMARY KEY  (`id1`,`id2`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `reflexiva`
  ADD CONSTRAINT `reflexiva_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `reflexiva` (`id1`) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO `reflexiva` (`id1`, `id2`, `timestamp`) VALUES 
('', '', 859380001171904385),
('1', '', 859380001171904385),
('2', '1', 859380001171904385);

UPDATE `reflexiva` SET `id1` = '3' WHERE `reflexiva`.`id1` = '1' AND `reflexiva`.`id2`= '';

Suggested fix:
cascade updating must be careful whith relationships between the own table.

Thank you for the bug report. Which exactly server 5.0 are you using? and 
what you meant with 'on update cascade crashes' the server crashes?.
Thanks in advance.

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".

Thjis is not a bug, according to the manual (http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html):

"Deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE  or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep."