| Bug #21847 | Creating proceedure after a failed attemp crashes server. | ||
|---|---|---|---|
| Submitted: | 26 Aug 2006 2:43 | Modified: | 1 Oct 2006 11:37 |
| Reporter: | Van Stokes | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.0.24 | OS: | Windows (Win Server 2003) |
| Assigned to: | CPU Architecture: | Any | |
[26 Aug 2006 7:50]
Valeriy Kravchuk
Thank you for a problem report. Please, upload a dump or, at least, send the results of SHOW CREATE TABLE for that Inventory table, so that we'll have complete test case.
[26 Aug 2006 11:22]
Van Stokes
Error Message from Event Manager Application popup: mysqld-max-nt.exe - Application Error : The instruction at "0x00545ba7" referenced memory at "0x00000001". The memory could not be "read". Click on OK to terminate the program Click on CANCEL to debug the program
[26 Aug 2006 11:24]
Van Stokes
Another dump that occurred right before the previous one: Application popup: mysqld-max-nt.exe - Application Error : The instruction at "0x005459ea" referenced memory at "0x204e4f4d". The memory could not be "read". Click on OK to terminate the program Click on CANCEL to debug the program
[26 Aug 2006 11:34]
Van Stokes
CREATE TABLE `objecttype` ( `ObjectTypeKey` bigint(20) NOT NULL, `ObjectTypeName` varchar(64) NOT NULL, `GUIImage` varchar(512) default NULL, `RecLastModified` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`ObjectTypeKey`), UNIQUE KEY `ObjectTypeName` (`ObjectTypeName`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 'itemcategory', 'CREATE TABLE `itemcategory` ( `ItemCategoryKey` bigint(20) NOT NULL default '0', `ItemCategoryName` varchar(64) NOT NULL, `ItemCategoryDesc` varchar(1024) default NULL, `RecLastModified` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`ItemCategoryKey`), UNIQUE KEY `ItemCategoryName` (`ItemCategoryName`), UNIQUE KEY `ItemCategory_UIDX1` (`ItemCategoryName`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8' CREATE TABLE `itemcategorysub` ( `ItemCategoryKey` bigint(20) NOT NULL default '0', `ItemCategorySubKey` bigint(20) NOT NULL default '0', `ItemCategorySubName` varchar(64) NOT NULL, `ItemCategorySubDesc` varchar(1024) default NULL, `RecLastModified` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`ItemCategoryKey`,`ItemCategorySubKey`), KEY `ItemCategorySub_IDX1` (`ItemCategorySubName`), CONSTRAINT `itemcategorysub_ibfk_1` FOREIGN KEY (`ItemCategoryKey`) REFERENCES `itemcategory` (`ItemCategoryKey`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `item` ( `ItemKey` bigint(20) NOT NULL default '0', `ItemName` varchar(64) NOT NULL, `ItemDesc` varchar(1024) NOT NULL, `ItemCategoryKey` bigint(20) NOT NULL, `ItemCategorySubKey` bigint(20) NOT NULL, `ObjectTypeKey` bigint(20) NOT NULL, `ItemMass` float NOT NULL default '1', `ItemSize` float NOT NULL default '1', `IsContainer` tinyint(1) NOT NULL default '0', `IsEnabled` tinyint(1) NOT NULL default '1', `RecLastModified` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`ItemKey`), KEY `Item_IDX1` (`ItemName`), KEY `Item_IDX2` (`ItemCategoryKey`,`ItemCategorySubKey`), KEY `Item_IDX3` (`ObjectTypeKey`), CONSTRAINT `item_ibfk_1` FOREIGN KEY (`ItemCategoryKey`, `ItemCategorySubKey`) REFERENCES `itemcategorysub` (`ItemCategoryKey`, `ItemCategorySubKey`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `item_ibfk_2` FOREIGN KEY (`ObjectTypeKey`) REFERENCES `objecttype` (`ObjectTypeKey`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `inventory` ( `InventoryKey` bigint(20) NOT NULL default '0', `ItemKey` bigint(20) NOT NULL default '0', `ObjectTypeKey` bigint(20) NOT NULL default '0', `ContInvKey` bigint(20) NOT NULL default '0', `ContAs` tinyint(4) NOT NULL default '0', `OwnerType` tinyint(4) NOT NULL default '0', `OwnerKey` bigint(20) NOT NULL default '0', `Units` float NOT NULL default '1', `QuadrantX` double NOT NULL default '0', `QuadrantY` double NOT NULL default '0', `QuadrantZ` double NOT NULL default '0', `SectorX` double NOT NULL default '0', `SectorY` double NOT NULL default '0', `SectorZ` double NOT NULL default '0', `PositionX` double NOT NULL default '0', `PositionY` double NOT NULL default '0', `PositionZ` double NOT NULL default '0', `OrientationW` double NOT NULL default '1', `OrientationX` double NOT NULL default '0', `OrientationY` double NOT NULL default '0', `OrientationZ` double NOT NULL default '0', `DateTimeCreated` datetime default NULL, `RecLastModified` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`InventoryKey`), KEY `Inventory_IDX1` (`ItemKey`), KEY `Inventory_IDX2` (`ObjectTypeKey`), KEY `Inventory_IDX3` (`ContInvKey`), KEY `Inventory_IDX4` (`OwnerType`,`OwnerKey`), KEY `Inventory_IDX5` (`QuadrantX`,`QuadrantY`,`QuadrantZ`,`SectorX`,`SectorY`,`SectorZ`), CONSTRAINT `inventory_ibfk_1` FOREIGN KEY (`ItemKey`) REFERENCES `item` (`ItemKey`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `inventory_ibfk_2` FOREIGN KEY (`ObjectTypeKey`) REFERENCES `objecttype` (`ObjectTypeKey`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8
[26 Aug 2006 11:48]
Van Stokes
My appologies. I thought there was a dump in the MINIDUMP directory for these errors but I couldn't find one with the correct (near) timestamp on it. So I provided the SHOW TABLES for you and a copy of the error messages I get in the MS SEVER EVENT VIEWER.
[27 Aug 2006 13:02]
MySQL Verification Team
Hi Van, please upload a "user.dmp" file by running "drwtsn32", setting the options/path, then running "drwtsn32 -i". Then, get mysqld-max-nt.exe to crash and upload the .dmp file in the configured directory. Even better is if you can use mysqld-debug.exe instead. Thanks,
[30 Aug 2006 18:39]
MySQL Verification Team
Thank you for the feedback. Could you please provide what Shane asked ih his last comment? Thanks in advance.
[30 Aug 2006 21:04]
Van Stokes
mysqld-debug trace file
Attachment: mysqld.trace.20060830-1.zip (application/zip, text), 50.50 KiB.
[30 Aug 2006 21:07]
Van Stokes
I have sent a zipped trace file. I followed these http://dev.mysql.com/doc/refman/5.0/en/making-trace-files.html instructions for creating the trace. I ran the mysqld-debug as: c> mysqld-debug --debug --standalone I attempted to create a proceedure called "CreateInventoryStorage" with a KNOWN error. This is the KNOWN error in the proceedure: -- Validate DESTINATION Inventory Item SELECT ItemKey, ContAs, INTO mDestItemKey, mDestContAs FROM Inventory WHERE InventoryKey = pDestInventoryKey; NOTE the comma(,) after "ContAs". The mysqld-debug terminated on it's own with a "SIGNAL RECEIVED" dialog box.
[30 Aug 2006 21:21]
Van Stokes
Dump file (DR Watson) that goes with the trace file.
Attachment: mysqld.dump.20060830-1.zip (application/zip, text), 16.72 KiB.
[30 Aug 2006 21:22]
Van Stokes
I have also included a DR WATSON dump file in a zip that corresponds with the trace file I provided.
[1 Sep 2006 17:00]
Van Stokes
FYI. I noticed a quirk that appears to PREVENT a crash. If I get an error when attempting to create a proceedure, trigger or function and I disconnect and reconnect my client PRIOR to another attempt the server doesn't crash (i.e. I can't get it to crash - yet).
[4 Sep 2006 13:51]
MySQL Verification Team
stack trace from watson
Attachment: bug21847_stack.txt (plain/text, text), 1.93 KiB.
[1 Oct 2006 11:37]
Valeriy Kravchuk
I can not repeat the behaviour described neither with 5.0.26-BK on Linux, nor with 5.0.24a on Windows XP. So, maybe somebody will find something useful from traces uploaded...
Description: If you attempt to create a proceedure (or function or trigger) and your first attempt fails because of a syntax error and after you have fixed the error and attempt to create the proceedure the server will crash with a "READ MEMORY ERROR". It doesn't happen 100% of the time but I can repoduce it 80% of the time. If necessary, I can provide a dump. How to repeat: Example proceedure that crashes our server: --- CUT HERE --- CREATE PROCEDURE `CreateInventoryStorage`( IN pDestInventoryKey BIGINT, IN pItemKey BIGINT, IN pOwnerType TINYINT, IN pOwnerKey BIGINT, OUT pResult BOOL ) MAIN: BEGIN DECLARE mLockSequenceTable BOOL DEFAULT FALSE; DECLARE mDestContAs TINYINT DEFAULT 0; DECLARE mDestItemKey BIGINT DEFAULT 0; DECLARE mItemObjectType BIGINT DEFAULT 0; DECLARE mOwnerCount TINYINT DEFAULT 0; -- Default pResult to failed. SET pResult = FALSE; -- **************************************************************** -- VALIDATION IF pDestInventoryKey < 1 THEN LEAVE MAIN; END IF; IF pItemKey < 1 THEN LEAVE MAIN; END IF; IF pOwnerType < 1 THEN LEAVE MAIN; END IF; IF pOwnerKey < 1 THEN LEAVE MAIN; END IF; -- Validate DESTINATION Inventory Item -- *** ERROR ON THIS LINE PURPOSEFULLY FOR TEST (COMMA) *** SELECT ItemKey, ContAs, INTO mDestItemKey, mDestContAs FROM Inventory WHERE InventoryKey = pDestInventoryKey; IF mDestItemKey < 1 THEN LEAVE MAIN; END IF; -- Do not permit the adding of storage items to inventory items -- that are not contained as General/Cargo. IF mDestContAs > 0 THEN LEAVE MAIN; END IF; -- Validate the ItemKey being passed. SELECT ObjectTypeKey INTO mItemObjectType FROM Item WHERE ItemKey = pItemKey; IF ObjectTypeKey < 1 THEN LEAVE MAIN; END IF; -- Validate the OWNER Type and OWNER Key IF pOwnerType = 1 THEN -- Character SELECT COUNT(*) INTO mOwnerCount FROM Charactr WHERE CharacterKey = pOwnerKey; END IF; IF pOwnerType = 2 THEN -- Squad SELECT COUNT(*) INTO mOwnerCount FROM Squad WHERE SquadKey = pOwnerKey; END IF; IF pOwnerType = 3 THEN -- Faction SELECT COUNT(*) INTO mOwnerCount FROM Faction WHERE FactionKey = pOwnerKey; END IF; IF mOwnerCount <> 1 THEN LEAVE MAIN; END IF; -- VALIDATION -- **************************************************************** INSERT INTO Inventory ( ItemKey, ObjectTypeKey, ContInvKey, OwnerType, OwnerKey, Units, QuadrantX, QuadrantY, QuadrantZ, SectorX, SectorY, SectorZ, PositionX, PositionY, PositionZ ) SELECT Item.ItemKey, Item.ObjectTypeKey, Inventory.InventoryKey, pOwnerType, pOwnerKey, '1.0', Inventory.QuadrantX, Inventory.QuadrantY, Inventory.QuadrantZ, Inventory.SectorX, Inventory.SectorY, Inventory.SectorZ, Inventory.PositionX, Inventory.PositionY, Inventory.PositionZ FROM Item, Inventory WHERE Item.ItemKey = pItemKey AND Inventory.InventoryKey = pDestInventoryKey; SET pResult = TRUE; END MAIN;