Bug #13102 MySQL/InnoDB does not execute trigger ON DELETE for cascade deleting rows
Submitted: 10 Sep 2005 10:00 Modified: 1 Dec 2006 10:04
Reporter: Nikolay Shestakov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Linux (Debian GNU/Linux)
Assigned to: Dmitry Lenev

[10 Sep 2005 10:00] Nikolay Shestakov
Description:
Don't execute trigger ON DELETE for cascade deleting rows

How to repeat:
CREATE TABLE `Documents` (
  `DocId` varchar(32) NOT NULL,
  `Date` date NOT NULL,
  `Comment` varchar(255) NOT NULL,
  PRIMARY KEY  (`DocId`),
  KEY `DocDate` (`DocId`,`Date`)
) ENGINE=innoDB DEFAULT CHARSET=utf8;

CREATE TABLE `StoreMove` (
  `DocId` varchar(32) NOT NULL,
  `Date` date NOT NULL,
  `Depot` varchar(32) NOT NULL,
  `Nomenclature` varchar(32) NOT NULL,
  `Number` double NOT NULL,
  `Amount` double NOT NULL,
  `Reserve` double NOT NULL,
  `Purchase` double NOT NULL,
  KEY `DocId` (`DocId`),
  KEY `Date` (`Date`,`Depot`,`Nomenclature`),
  FOREIGN KEY `DocKey` (`DocID`) REFERENCES `Documents` (`DocID`)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `StoreRest` (
  `Date` date NOT NULL,
  `Depot` varchar(32) NOT NULL,
  `Nomenclature` varchar(32) NOT NULL,
  `Number` double NOT NULL,
  `Amount` double NOT NULL,
  `Reserve` double NOT NULL,
  `Purchase` double NOT NULL,
  UNIQUE KEY `Date` (`Date`,`Depot`,`Nomenclature`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;       

delimiter //
CREATE TRIGGER `StoreMove_Insert` AFTER INSERT ON `StoreMove`
  FOR EACH ROW
  BEGIN
    INSERT INTO `StoreRest` 
    VALUES  (NEW.Date, NEW.Depot, NEW.Nomenclature, NEW.Number, NEW.Amount, NEW.Reserve, NEW.Purchase)
    ON DUPLICATE KEY UPDATE 
      `Number`   = `Number`   + NEW.Number, 
      `Amount`   = `Amount`   + NEW.Amount, 
      `Reserve`  = `Reserve`  + NEW.Reserve, 
      `Purchase` = `Purchase` + NEW.Purchase;
  END;//
delimiter ;    

delimiter //
CREATE TRIGGER `StoreMove_Delete` AFTER DELETE ON `StoreMove`
  FOR EACH ROW
  BEGIN
    UPDATE `StoreRest` 
    SET  
      `StoreRest`.`Number`   = `StoreRest`.`Number`   - OLD.Number, 
      `StoreRest`.`Amount`   = `StoreRest`.`Amount`   - OLD.Amount, 
      `StoreRest`.`Reserve`  = `StoreRest`.`Reserve`  - OLD.Reserve, 
      `StoreRest`.`Purchase` = `StoreRest`.`Purchase` - OLD.Purchase 
    WHERE 
        `StoreRest`.`Date`  = OLD.Date 
      AND 
        `StoreRest`.`Depot` = OLD.Depot 
      AND 
       `StoreRest`.`Nomenclature`  = OLD.Nomenclature;
  END;//
delimiter ;    

/////////////////////////////////////////////
INSERT INTO `Documents` ( `DocId` , `Date` , `Comment` ) 
VALUES (
'1', CURDATE( ) , '1'
);

INSERT INTO `StoreMove` ( `DocId` , `Date` , `Depot` , `Nomenclature` , `Number` , `Amount` , `Reserve` , `Purchase` ) 
VALUES (
'1', CURDATE( ) , 'D1', 'N1', '1', '2', '3', '4'
);

INSERT INTO `StoreMove` ( `DocId` , `Date` , `Depot` , `Nomenclature` , `Number` , `Amount` , `Reserve` , `Purchase` ) 
VALUES (
'1', CURDATE( ) , 'D1', 'N1', '10', '20', '30', '40'
);

////////////////////////////////////// StoreRest - Good
Date 	Depot 	Nomenclature 	Number 	Amount 	Reserve 	Purchase 	
2005-09-10	D1	N1	11	22	33	44
/////////////////////////////////////

DELETE FROM `StoreMove` WHERE CONVERT(`DocId` USING utf8) = '1' AND `Date` = '2005-09-10' AND CONVERT(`Depot` USING utf8) = 'D1' AND CONVERT(`Nomenclature` USING utf8) = 'N1' AND CONCAT(`Number`) = 1 AND CONCAT(`Amount`) = 2 AND CONCAT(`Reserve`) = 3 AND CONCAT(`Purchase`) = 4 LIMIT 1;

////////////////////////////////////// StoreRest - Good
Date 	Depot 	Nomenclature 	Number 	Amount 	Reserve 	Purchase 	
2005-09-10	D1	N1	10	20	30	40
/////////////////////////////////////

DELETE FROM `Documents` WHERE CONVERT(`DocId` USING utf8) = '1' LIMIT 1

//////////////////////////////////// StoreMove - Good
Is empty
///////////////////////////////////

////////////////////////////////////// StoreRest - Bag
Date 	Depot 	Nomenclature 	Number 	Amount 	Reserve 	Purchase 	
2005-09-10	D1	N1	10	20	30	40
/////////////////////////////////////

////////////////////////////////////// StoreRest - Require
Date 	Depot 	Nomenclature 	Number 	Amount 	Reserve 	Purchase 	
2005-09-10	D1	N1	0	0	0	0
/////////////////////////////////////
[11 Sep 2005 8:42] Heikki Tuuri
I think this shortcoming has already been reported in the bugs database. Marking this as a duplicate.
[11 Sep 2005 9:20] Heikki Tuuri
Looks like this has not been reported in the bugs database, even though this is a known shortcoming. I am reopening this bug report.
[25 Jan 2006 16:24] Martin Fernau
just want to say, that I can reproduce this bug with the latest Version of mysql 5.0.18
[11 Mar 2006 2:56] Scott Marlowe
This is another one of those things that should cause an error (when defining the cascading delete) or at the very least a warning.  While it's acceptable for myisam tables to just trundle along error free, innodb is what lets MySQL compete with the big boys.  Swallowing a cascading delete assignent without actually making it is the worst of options for innodb tables.

I think this points to a need to have contextual error handling based on the table handler in use at the time of a query being run.  Otherwise these problems will be a hairy nightmare to fix.
[11 Mar 2006 8:57] Heikki Tuuri
Scott,

right, MySQL-5.0 should give a warning if one creates a trigger on a table with a cascading FOREIGN KEY constraint and vice versa.

This bug is still in 5.1.

Regards,

Heikki
[6 Sep 2006 10:08] Heikki Tuuri
Changing the category to Triggers, because the MySQL interpreter needs to give the warning.

Or we can wait for MySQL foreign keys to be implemented for all table types, which will solve this problem.
[1 Dec 2006 10:04] Dmitry Lenev
Hi!

This bug is duplicate of bug#11472.
[8 Dec 2006 17:18] Xavier GUIBERT
Thanks for your comment Dmitri: you point out that this lack is open since june 2005, and we are nearly in 2007.
Yes a warning would be a good thing, but implementing the feature would be good too...

"OS" should be modified to "*", as it occurs on all OS I imagine.

About warnings concerning features not implemented, I'll try to open a separate request: things like "constraint ... check", "signal", ...
[16 Feb 2007 7:36] Prabha Vadlamani
I encountered this problem too with 5.0. Is this going to be fixed?