| 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: | |
| 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 12:21]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.
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;