Description:
'INSERT ... ON DUPLICATE KEY UPDATE' does not work properly. After the first query execution, it will not update the record with other 'INSERT ... ON DUPLICATE KEY UPDATE' queries if there is another query besides a 'INSERT ... ON DUPLICATE KEY UPDATE' executed on the table before the subsequent 'INSERT ... ON DUPLICATE KEY UPDATE' queries.
How to repeat:
'INSERT ... ON DUPLICATE KEY UPDATE'
CREATE TABLE `preferences` (
`username` varchar(25) NOT NULL,
`preference` varchar(100) NOT NULL,
`value` varchar(50) default NULL,
PRIMARY KEY (`username`,`preference`),
UNIQUE KEY `preference` (`username`,`preference`),
KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `preferences` VALUES ('cstone', 'confirmedquoteRows', '75');
INSERT INTO `preferences` VALUES ('cstone', 'confirmedquoteSort', 'firstname');
INSERT INTO `preferences` VALUES ('cstone', 'confirmedquoteSortOrder', 'DESC');
INSERT INTO `preferences` VALUES ('cstone', 'confirmedquoteValidity', 'all');
INSERT INTO `preferences` VALUES ('cstone', 'contactQuery', null);
INSERT INTO `preferences` VALUES ('cstone', 'contactSort', 'lastname');
INSERT INTO `preferences` VALUES ('cstone', 'contactSortOrder', 'DESC');
INSERT INTO authentication.preferences (username,preference,value) VALUES ('cstone', 'contactSort', 'firstname') ON DUPLICATE KEY UPDATE value='firstname';
SELECT * from authentication.preferences WHERE preference='contactSort';
INSERT INTO authentication.preferences (username,preference,value) VALUES ('cstone', 'contactSort', 'email') ON DUPLICATE KEY UPDATE value='email';
contactSort should contain email, but does not. It contains firstname.
Suggested fix:
Unknown fix.
Description: 'INSERT ... ON DUPLICATE KEY UPDATE' does not work properly. After the first query execution, it will not update the record with other 'INSERT ... ON DUPLICATE KEY UPDATE' queries if there is another query besides a 'INSERT ... ON DUPLICATE KEY UPDATE' executed on the table before the subsequent 'INSERT ... ON DUPLICATE KEY UPDATE' queries. How to repeat: 'INSERT ... ON DUPLICATE KEY UPDATE' CREATE TABLE `preferences` ( `username` varchar(25) NOT NULL, `preference` varchar(100) NOT NULL, `value` varchar(50) default NULL, PRIMARY KEY (`username`,`preference`), UNIQUE KEY `preference` (`username`,`preference`), KEY `username` (`username`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `preferences` VALUES ('cstone', 'confirmedquoteRows', '75'); INSERT INTO `preferences` VALUES ('cstone', 'confirmedquoteSort', 'firstname'); INSERT INTO `preferences` VALUES ('cstone', 'confirmedquoteSortOrder', 'DESC'); INSERT INTO `preferences` VALUES ('cstone', 'confirmedquoteValidity', 'all'); INSERT INTO `preferences` VALUES ('cstone', 'contactQuery', null); INSERT INTO `preferences` VALUES ('cstone', 'contactSort', 'lastname'); INSERT INTO `preferences` VALUES ('cstone', 'contactSortOrder', 'DESC'); INSERT INTO authentication.preferences (username,preference,value) VALUES ('cstone', 'contactSort', 'firstname') ON DUPLICATE KEY UPDATE value='firstname'; SELECT * from authentication.preferences WHERE preference='contactSort'; INSERT INTO authentication.preferences (username,preference,value) VALUES ('cstone', 'contactSort', 'email') ON DUPLICATE KEY UPDATE value='email'; contactSort should contain email, but does not. It contains firstname. Suggested fix: Unknown fix.