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 11:38]
David Hammink
[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.