Description:
On a table with a five column composite primary key, using INSERT...ON DUPLICATE KEY UPDATE or REPLACE cases the server to crash.
Minimal test case appears below.
How to repeat:
delimiter $$
CREATE TABLE `MechGainedXP` (
`PlayerID` varchar(255) NOT NULL,
`MatchID` varchar(255) NOT NULL,
`CurrencyType` varchar(255) NOT NULL,
`Amount` int(11) NOT NULL,
`GainReason` varchar(255) NOT NULL,
`MechInstanceID` varchar(255) NOT NULL,
`MechType` varchar(255) NOT NULL,
`MechSubType` varchar(255) NOT NULL,
`Timestamp` bigint(20) NOT NULL,
`Timestamp_ts` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`PlayerID`,`Timestamp`,`MechInstanceID`,`CurrencyType`,`GainReason`),
KEY `IDX_PlayerID` (`PlayerID`),
KEY `IDX_MechInstanceID` (`MechInstanceID`),
KEY `IDX_Timestamp` (`Timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
CREATE
TRIGGER `TR_MechGainedXP_INS`
BEFORE INSERT ON `MechGainedXP`
FOR EACH ROW
BEGIN SET NEW.Timestamp_ts = FROM_UNIXTIME(ROUND(NEW.Timestamp / 1000)); END
$$
CREATE
TRIGGER `TR_MechGainedXP_UPD`
BEFORE UPDATE ON `MechGainedXP`
FOR EACH ROW
BEGIN SET NEW.Timestamp_ts = FROM_UNIXTIME(ROUND(NEW.Timestamp / 1000)); END
$$
delimiter ;
REPLACE INTO MechGainedXP (`PlayerID`,`MatchID`,`CurrencyType`,`Amount`,`GainReason`,`MechInstanceID`,`MechType`,`MechSubType`,`Timestamp`) VALUES ('12b2529b-f23b-447f-8b2d-dc11f4eef5d2','B1F7ADA046A1AB145197B0BC61BBF36C','XP',51,'Kill','37a93db9-517c-4042-a9d3-957048858da1',0,0,1354756790292);
REPLACE INTO MechGainedXP (`PlayerID`,`MatchID`,`CurrencyType`,`Amount`,`GainReason`,`MechInstanceID`,`MechType`,`MechSubType`,`Timestamp`) VALUES ('12b2529b-f23b-447f-8b2d-dc11f4eef5d2','B1F7ADA046A1AB145197B0BC61BBF36C','XP',51,'Kill','37a93db9-517c-4042-a9d3-957048858da1',0,0,1354756790292);
Description: On a table with a five column composite primary key, using INSERT...ON DUPLICATE KEY UPDATE or REPLACE cases the server to crash. Minimal test case appears below. How to repeat: delimiter $$ CREATE TABLE `MechGainedXP` ( `PlayerID` varchar(255) NOT NULL, `MatchID` varchar(255) NOT NULL, `CurrencyType` varchar(255) NOT NULL, `Amount` int(11) NOT NULL, `GainReason` varchar(255) NOT NULL, `MechInstanceID` varchar(255) NOT NULL, `MechType` varchar(255) NOT NULL, `MechSubType` varchar(255) NOT NULL, `Timestamp` bigint(20) NOT NULL, `Timestamp_ts` timestamp NULL DEFAULT NULL, PRIMARY KEY (`PlayerID`,`Timestamp`,`MechInstanceID`,`CurrencyType`,`GainReason`), KEY `IDX_PlayerID` (`PlayerID`), KEY `IDX_MechInstanceID` (`MechInstanceID`), KEY `IDX_Timestamp` (`Timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8$$ CREATE TRIGGER `TR_MechGainedXP_INS` BEFORE INSERT ON `MechGainedXP` FOR EACH ROW BEGIN SET NEW.Timestamp_ts = FROM_UNIXTIME(ROUND(NEW.Timestamp / 1000)); END $$ CREATE TRIGGER `TR_MechGainedXP_UPD` BEFORE UPDATE ON `MechGainedXP` FOR EACH ROW BEGIN SET NEW.Timestamp_ts = FROM_UNIXTIME(ROUND(NEW.Timestamp / 1000)); END $$ delimiter ; REPLACE INTO MechGainedXP (`PlayerID`,`MatchID`,`CurrencyType`,`Amount`,`GainReason`,`MechInstanceID`,`MechType`,`MechSubType`,`Timestamp`) VALUES ('12b2529b-f23b-447f-8b2d-dc11f4eef5d2','B1F7ADA046A1AB145197B0BC61BBF36C','XP',51,'Kill','37a93db9-517c-4042-a9d3-957048858da1',0,0,1354756790292); REPLACE INTO MechGainedXP (`PlayerID`,`MatchID`,`CurrencyType`,`Amount`,`GainReason`,`MechInstanceID`,`MechType`,`MechSubType`,`Timestamp`) VALUES ('12b2529b-f23b-447f-8b2d-dc11f4eef5d2','B1F7ADA046A1AB145197B0BC61BBF36C','XP',51,'Kill','37a93db9-517c-4042-a9d3-957048858da1',0,0,1354756790292);