Bug #19634 Re-execution of multi-delete which involve trigger/stored function crashes MySQL
Submitted: 9 May 2006 11:38 Modified: 6 Jul 2006 22:10
Reporter: David Hammink Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.21/5.0-bk OS:Linux (Fedora 5 X64/FC4x86)
Assigned to: Dmitry Lenev CPU Architecture:Any

[9 May 2006 11:38] David Hammink
Description:
The following stored procedure works one time the scond time it is called (with a dfferent serviceID it crashes the server. (does it in in 5.0.20 and 5.0.21)

PROCEDURE `TP5v003`.`sp_DeleteService`(IN P_ServiceID INT)
BEGIN
START TRANSACTION;
  BEGIN
    DELETE SC FROM tblServiceNumber SN,tblService_Company SC WHERE SC.ServiceID=P_ServiceID AND SN.ServiceID=SC.ServiceID;
    DELETE SC FROM tblServiceNumber SN,tblService_Tariff SC WHERE SC.ServiceID=P_ServiceID AND SN.ServiceID=SC.ServiceID;
    DELETE SC FROM tblServiceNumber SN,tblService_Contact SC WHERE SC.ServiceID=P_ServiceID AND SN.ServiceID=SC.ServiceID;
    DELETE SC FROM tblServiceNumber SN,tblService_Version SC WHERE SC.ServiceID=P_ServiceID AND SN.ServiceID=SC.ServiceID;
    DELETE SC FROM tblServiceNumber SN,tblKeyword_Service SC WHERE SC.ServiceID=P_ServiceID AND SN.ServiceID=SC.ServiceID;
    DELETE SC,SN FROM tblServiceNumber SN,tblService SC WHERE SC.ServiceID=P_ServiceID AND SN.ServiceID=SC.ServiceID;
   END;
 COMMIT;
END

Tables do have triggers ON Update and ON INsert (not in ON delete)

Version: '5.0.21-max-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Edition - Experimental (GPL)
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=1073741824
read_buffer_size=2093056
max_used_connections=14
max_connections=75
threads_connected=12
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3659475 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Number of processes running now: 0
060509 13:23:28  mysqld restarted

How to repeat:
run the stored procedure twice.

Tables :
CREATE TABLE `tblServiceNumber` (
  `ServiceID` int(11) NOT NULL,
  `MachineID` tinyint(3) NOT NULL default '1',
  `DDI` bigint(20) default NULL,
  `GeographicNumber` varchar(30) default NULL,
  `TerminationID` tinyint(3) NOT NULL default '1',
  `SecurityCode` varchar(10) default NULL,
  `LanguageID` tinyint(3) unsigned NOT NULL default '2',
  `PremiumRateID` int(11) default NULL,
  PRIMARY KEY  (`ServiceID`),
  KEY `tblMachine_tblServiceNumber_FK1` (`MachineID`),
  KEY `DDI` (`DDI`),
  KEY `DDI_Service` (`DDI`,`ServiceID`),
  KEY `PremiumRateID` (`PremiumRateID`),
  KEY `GeographicNumber` (`GeographicNumber`),
  KEY `LanguageID` (`LanguageID`),
  CONSTRAINT `ServiceID_FK1` FOREIGN KEY (`ServiceID`) REFERENCES `tblService` (`ServiceID`) ON DELETE CASCADE,
  CONSTRAINT `tblMachine_tblServiceNumber_FK1` FOREIGN KEY (`MachineID`) REFERENCES `tblMachine` (`MachineID`),
  CONSTRAINT `tblServiceNumber_ibfk_1` FOREIGN KEY (`PremiumRateID`) REFERENCES `tblPremiumRateNumbers` (`PremiumRateID`),
  CONSTRAINT `tblServiceNumber_ibfk_2` FOREIGN KEY (`LanguageID`) REFERENCES `tblLanguage` (`LanguageID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tblService` (
  `ServiceID` int(11) NOT NULL auto_increment,
  `RedirectID` int(11) default '0',
  `Created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `PreviousID` int(11) default NULL,
  `Active` bit(1) default '\0',
  `bChild` bit(1) default NULL,
  `ServiceStatusID` tinyint(3) unsigned default NULL,
  `ServiceStatusFixed` bit(1) default NULL,
  `Updated` datetime default '0000-00-00 00:00:00',
  `CreatedBy` varchar(32) NOT NULL,
  `UpdatedBy` varchar(32) default '',
  PRIMARY KEY  (`ServiceID`),
  KEY `tblService_tblService_FK1` (`RedirectID`),
  KEY `Created` (`Created`),
  KEY `ServiceID_Created` (`ServiceID`,`Created`),
  KEY `ServiceStatusID` (`ServiceStatusID`),
  KEY `PreviousID` (`PreviousID`),
  CONSTRAINT `Service_ibfk_1` FOREIGN KEY (`ServiceStatusID`) REFERENCES `tblServiceStatus` (`ServiceStatusID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `tblService_Company` (
  `Service_CompanyID` int(11) NOT NULL auto_increment,
  `ServiceID` int(11) NOT NULL,
  `CompanyID` smallint(5) unsigned NOT NULL,
  `Created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `Updated` datetime default '0000-00-00 00:00:00',
  `CreatedBy` varchar(32) NOT NULL,
  `UpdatedBy` varchar(32) default '',
  `KeywordID` int(11) NOT NULL default '479275',
  PRIMARY KEY  (`Service_CompanyID`),
  UNIQUE KEY `Se_Co_Key` (`ServiceID`,`CompanyID`,`KeywordID`),
  KEY `ServiceID` (`ServiceID`),
  KEY `CompanyID` (`CompanyID`),
  KEY `ServiceCompany` (`ServiceID`,`CompanyID`),
  KEY `KeywordID` (`KeywordID`),
  CONSTRAINT `CompanyID_FK1` FOREIGN KEY (`CompanyID`) REFERENCES `tblCompany` (`CompanyID`),
  CONSTRAINT `KeywordID_FK3` FOREIGN KEY (`KeywordID`) REFERENCES `tblKeyword` (`KeywordID`),
  CONSTRAINT `ServiceID_FK2` FOREIGN KEY (`ServiceID`) REFERENCES `tblService` (`ServiceID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tblService_Contact` (
  `Service_ContactID` int(11) NOT NULL auto_increment,
  `ServiceID` int(11) NOT NULL,
  `ContactID` smallint(6) NOT NULL,
  `Created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `Updated` datetime default NULL,
  `CreatedBy` varchar(32) NOT NULL,
  `UpdatedBy` varchar(32) default NULL,
  PRIMARY KEY  (`Service_ContactID`),
  KEY `tblService_tblService_Contact_FK1` (`ServiceID`),
  KEY `tblContact_tblService_Contact_FK1` (`ContactID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tblService_Product` (
  `Service_ProductID` int(11) NOT NULL auto_increment,
  `ServiceID` int(11) NOT NULL,
  `KeywordID` smallint(6) default NULL,
  `ProductID` tinyint(4) NOT NULL,
  `Created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `CreatedBy` varchar(32) NOT NULL,
  `Updated` datetime default NULL,
  `UpdatedBy` varchar(32) default NULL,
  PRIMARY KEY  (`Service_ProductID`),
  UNIQUE KEY `SKP_UN` (`ServiceID`,`KeywordID`,`ProductID`),
  KEY `tblService_tblServiceProduct_FK1` (`ServiceID`),
  KEY `tblProduct_tblServiceProduct_FK1` (`ProductID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tblService_Tariff` (
  `Service_TariffID` int(11) NOT NULL auto_increment,
  `ServiceID` int(11) NOT NULL,
  `MenuChoice` tinyint(4) default NULL,
  `Caption` varchar(20) default NULL,
  `TariffID` tinyint(4) default NULL,
  `Created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `Updated` datetime default NULL,
  `CreatedBy` varchar(32) NOT NULL,
  `UpdatedBy` varchar(32) default NULL,
  PRIMARY KEY  (`Service_TariffID`),
  KEY `tblService_tblMenu_FK1` (`ServiceID`),
  KEY `tblTariff_tblMenu_FK1` (`TariffID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tblService_Version` (
  `Service_VersionID` int(11) NOT NULL auto_increment,
  `VersionID` int(11) NOT NULL default '0',
  `ServiceID` int(11) NOT NULL,
  `StartingDate` datetime NOT NULL,
  `Created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `CreatedBy` varchar(32) NOT NULL,
  `Updated` datetime default NULL,
  `UpdatedBy` varchar(32) default NULL,
  PRIMARY KEY  (`Service_VersionID`),
  UNIQUE KEY `VersionID` (`VersionID`,`ServiceID`),
  KEY `tblVersion_tblAd_Version_FK1` (`VersionID`),
  KEY `tblService_tblService_Version_FK1` (`ServiceID`),
  KEY `VS_date` (`VersionID`,`ServiceID`,`StartingDate`),
  CONSTRAINT `tblService_Version_ServiceID_FK1` FOREIGN KEY (`ServiceID`) REFERENCES `tblService` (`ServiceID`),
  CONSTRAINT `tblService_Version_VersionID_FK2` FOREIGN KEY (`VersionID`) REFERENCES `tblVersion` (`VersionID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[9 May 2006 12:21] Miguel Solorzano
Thank you for the bug report. For to run that procedure claims for a table
called tblKeyword_Service also if needed data could you please provide
besides the create of that table a dump for to insert data?

Thanks in advance.
[9 May 2006 13:56] David Hammink
Hi,

Here is the structure already . How large can the dump be ?
These tables are pretty big (50000 + records ...)

I will see what I can produce.

CREATE TABLE `tblKeyword_Service` (
  `Keyword_ServiceID` int(11) NOT NULL auto_increment,
  `ServiceID` int(11) NOT NULL,
  `KeywordID` int(11) NOT NULL default '0',
  `ProductID` tinyint(4) default NULL,
  `LongNumberID` tinyint(4) default NULL,
  `Created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `Updated` datetime NOT NULL,
  `CreatedBy` varchar(32) NOT NULL,
  `UpdatedBy` varchar(32) NOT NULL,
  PRIMARY KEY  (`Keyword_ServiceID`),
  UNIQUE KEY `KeywordID_ServiceID` (`KeywordID`,`ServiceID`),
  KEY `tblProduct_tblKeyword_Service_FK1` (`ProductID`),
  KEY `tblTrigger_tblKeyword_Service_FK1` (`LongNumberID`),
  KEY `ServiceID_KeywordID` (`ServiceID`,`KeywordID`),
  KEY `ServiceID_IDX` (`ServiceID`),
  KEY `KeywordID_IDX` (`KeywordID`),
  CONSTRAINT `tblKeyword_Service_ibfk_1` FOREIGN KEY (`ServiceID`) REFERENCES `tblService` (`ServiceID`),
  CONSTRAINT `tblKeyword_Service_ibfk_3` FOREIGN KEY (`ProductID`) REFERENCES `tblProduct` (`ProductID`),
  CONSTRAINT `tblKeyword_Service_ibfk_4` FOREIGN KEY (`KeywordID`) REFERENCES `tblKeyword` (`KeywordID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[9 May 2006 14:32] David Hammink
Posted and except of each file (Service < 8000 ) Should be consistent (I have not checked that )
[12 May 2006 14:25] Shane Bester
Here is my reduced testcase, originally trimmed down from your data. More info follows shortly.

######
DROP DATABASE IF EXISTS bug19634;
CREATE DATABASE bug19634;
USE bug19634;
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (`id1` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (`id` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE DEFINER=``@`` TRIGGER `mytrigger` AFTER INSERT ON `t1` FOR EACH ROW INSERT IGNORE INTO t1 SELECT 1;
DROP PROCEDURE IF EXISTS `bug19634`;
DELIMITER //
CREATE PROCEDURE `bug19634`(IN id INT)
BEGIN
  BEGIN
    DELETE t1 FROM t2,t1 WHERE t1.id1=id AND t2.id1=t1.id1;
   END;
END//
DELIMITER ;
CALL bug19634(1);
CALL bug19634(2);
########
[12 May 2006 14:33] Shane Bester
The cause appears to be a NULL tables object passed in to function "check_table_access" (sql_parse.cc).

Notice there's a trigger on the table too.
[12 May 2006 15:50] Shane Bester
Lastly, here's the simplest testcase I can come up with to crash:

#####
DROP DATABASE IF EXISTS bug19634;
CREATE DATABASE bug19634;
USE bug19634;
CREATE TABLE `t`(`i` INT);
CREATE TRIGGER `d` AFTER INSERT ON `t` FOR EACH ROW INSERT IGNORE INTO t SELECT 1;
DROP PROCEDURE IF EXISTS `p`;
DELIMITER //
CREATE PROCEDURE `p`()
BEGIN
  BEGIN
    DELETE t FROM t;
   END;
END//
DELIMITER ;
CALL `p`();
CALL `p`();
#######
[20 Jun 2006 21:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7973
[6 Jul 2006 22:10] Paul Dubois
Noted in 5.0.23, 5.1.12 changelogs.

Re-execution of a multiple-table DELETE statement that involves a
trigger or stored function can result in a server crash.