Bug #27968 On Duplicate Key Update Fails
Submitted: 20 Apr 2007 0:47 Modified: 20 Apr 2007 7:47
Reporter: Apolonio Valdovinos Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.36 OS:Any
Assigned to: CPU Architecture:Any
Tags: ON DUPLICATE KEY UPDATE, query, UPDATE

[20 Apr 2007 0:47] Apolonio Valdovinos
Description:
On duplicate key update not working, delayed?  This works on 5.0.27.

How to repeat:
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `contract`;

CREATE TABLE `contract` (
  `contractID` int(11) NOT NULL auto_increment,
  `personID` int(11) NOT NULL,
  `startdate` date NOT NULL,
  `enddate` date NOT NULL,
  `vehicleyear` int(11) NOT NULL,
  `vehiclemake` varchar(255) NOT NULL default '',
  `vehiclemodel` varchar(255) NOT NULL default '',
  `vin` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`contractID`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert into `contract` values('1','957','2007-05-01','2008-05-01','2007','BMW','740iL','111111'),
 ('2','957','2007-05-01','2007-06-01','2005','Audi','S8','HH11');

SET FOREIGN_KEY_CHECKS = 1;

/*The update below will fail*/
/*INSERT INTO `contract` (contractID, personID, startdate, enddate, vehicleyear, vehiclemake, vehiclemodel, vin)
VALUES (2, 957, '2007-05-01', '2007-06-01', '2005', 'Audi', 'S4', 'HH11')
ON DUPLICATE KEY UPDATE
personID = 957, 
startdate = '2007-05-01', 
enddate = '2007-06-01', 
vehicleyear = '2005', 
vehiclemake = 'Audi', 
vehiclemodel = 'S4', 
vin = 'HH11';*/
[20 Apr 2007 1:35] Miguel Solorzano
Thank you for the bug report. Could you please see if your case
is the same as bug: http://bugs.mysql.com/bug.php?id=27210. Thanks
in advance. Thanks in advance.
[20 Apr 2007 7:47] Sveta Smirnova
Thank you for the feedback.

Altough bug #27210 was originally reported for InooDB, it was repatable with MyOSAM too. But it existed only in 5.0.36 version.

Also I tried your queries with 5.0.38 and can not repeat the problem. So I mark this report as duplicate.