MySQL Bugs: #14520: Replication fails with error "Error 'Duplicate entry '-1' for key 2'"

Bug #14520	Replication fails with error "Error 'Duplicate entry '-1' for key 2'"
Submitted:	31 Oct 2005 20:31	Modified:	16 Dec 2005 17:51
Reporter:	Didier Duplech	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Windows (Windows XP Pro 2002 SP2)
Assigned to:		CPU Architecture:	Any

Description:
The replication fails with the following error:

051031 11:37:07 [ERROR] Slave: Error 'Duplicate entry '-1' for key 2' on query. Default database: 'imtprod'. Query: 'INSERT INTO `RelayTestsLog` (`DieId`, `JigId`, `PiggyBoardId`, `ActContext`, `ActCurrentModelId`, `ActTimingModelId`, `TestType`, `TTParamValueRev`, `TTParamNameId`, `ParentLogId`, `TestSeqNbr`,`ErrorNumber`, `ComputerName`, `UserName`, `NbCycles`, `SampleRate`, `LimitContext`, `EnvContext`, `AppLabel`, `TestedTime`, `FinishedTime`, `Status`) VALUES (581550, 1023, 34, -1, -1, -1, 41, -1, -1, 7493869, 6, 0, "IMTWP8", "pcarranza", -1, -1, "", "", "WPP-1.2.5", "2005-10-20 08:46:49", "2005-10-20 08:46:49", 0)', Error_code: 1062
051031 11:37:07 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'imtprod-bin.000068' position 503459831

I checked and those values are not already inserted in the table.
And in any case, there is no unique index on the table except for the primary key column.

Here's a SHOW TABLE STATUS
'relaytestslog', 'MyISAM', 9, 'Dynamic', 7489439, 120, 904397092, 4294967295, 840184832, 0, 7493874, '2005-07-25 05:30:05', '2005-10-20 09:34:38', '2005-09-12 07:19:04', 'latin1_swedish_ci', , '', ''

And a SHOW INDEX
'relaytestslog', 0, 'PRIMARY', 1, 'TestLogId', 'A', 7489439, , '', '', 'BTREE', ''
'relaytestslog', 1, 'iRelayTestsLogLotId', 1, 'LotId', 'A', 2, , '', 'YES', 'BTREE', ''
'relaytestslog', 1, 'iRelayTestsLogDieId', 1, 'DieId', 'A', 576110, , '', 'YES', 'BTREE', ''
'relaytestslog', 1, 'iRelayTestsLogTimestamp', 1, 'Timestamp', 'A', 2496479, , '', '', 'BTREE', ''
'relaytestslog', 1, 'iRelayTestsLogActTimingModelId', 1, 'ActTimingModelId', 'A', 115222, , '', 'YES', 'BTREE', ''
'relaytestslog', 1, 'iRelayTestsLogRelayId', 1, 'RelayId', 'A', 936179, , '', 'YES', 'BTREE', ''
'relaytestslog', 1, 'iRelayTestsLogWaferId', 1, 'WaferId', 'A', 1099, , '', 'YES', 'BTREE', ''
'relaytestslog', 1, 'iRelayTestsLogActCurrentModelId', 1, 'ActCurrentModelId', 'A', 129128, , '', 'YES', 'BTREE', ''
'relaytestslog', 1, 'iRelayTestsLogTestType', 1, 'TestType', 'A', 136, , '', '', 'BTREE', ''
'relaytestslog', 1, 'iRelayTestsLogTestedTime', 1, 'TestedTime', 'A', 3744719, , '', '', 'BTREE', ''
'relaytestslog', 1, 'iRelayTestsLogParentLogId', 1, 'ParentLogId', 'A', 468089, , '', '', 'BTREE', ''

How to repeat:
Don't know how to reproduce this, but it has happened at least 5 times in two months since putting the replication in place.

One time, I noticed that the table was locked because the SQL thread was running a similar INSERT statement at the same time as a big query was running on that table.

This replicated server is used for reporting, so there are often big queries running while the SQL thread locks tables to insert the replicated data.

The error occured again and failed replication, this time on a different table:

051101 10:19:51 [ERROR] Slave: Error 'Duplicate entry '73' for key 2' on query. Default database: 'memsprod'. Query: 'INSERT INTO `rctestrules` (`ModeID`, `DUTLevel`, `TestType`, `TestRuleDesp`, `RuleType`, `IsActive`, `RuleIsFatal`, `SubRule1TestType`, `SubRule1StatusCondition`, `SubRule2TestType`, `SubRule2StatusCondition`, `StatusEnforced`, `LocationEnforced`, `CheckSeq`, `FailedMessage`) VALUES (8, 1, 1146, '', 5, 1, 2, 1135, 1, -1, -2, -2, 0, 1, 'DS Wafer Pattern D1 HAS NOT BEEN EXECUTED or IT HAD FAILED!')', Error_code: 1062
051101 10:19:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'memsprod-bin.000022' position 719410897

The value 73 doesn't even appear in the INSERT statement !
Also, I can run the INSERT statement on the slave without getting this error.
But if I restart the slave, I always get this error.

Thank you for a problem report. Please, send your my.ini files from both master and slave, and the SHOW CREATE TABLE results for both tables.

Have you tried to use newer version of MySQL (4.1.15)? Your 4.1.11 is old enough...

Master my.ini file

Attachment: my.ini (application/octet-stream, text), 9.11 KiB.

Slave my.ini file

Attachment: my.ini (application/octet-stream, text), 9.07 KiB.

Hi Valeriy,

Thanks for the response.
I've attached both my.ini, files and here are the show create table are below.
I was thinking of upgrading only the slave to MySQL 5.x, hoping it would solve this issue.
What do you think ?

Thanks
Didier

Master:
CREATE TABLE `rctestrules` (
  `ModeID` smallint(6) NOT NULL default '-1',
  `DUTLevel` smallint(6) NOT NULL default '-1',
  `TestType` smallint(6) NOT NULL default '-1',
  `TestRuleID` mediumint(3) unsigned NOT NULL auto_increment,
  `TestRuleDesp` varchar(255) default NULL,
  `RuleType` smallint(6) NOT NULL default '-1',
  `IsActive` tinyint(1) unsigned NOT NULL default '1',
  `RuleIsFatal` tinyint(1) unsigned NOT NULL default '0',
  `SubRule1TestType` smallint(6) NOT NULL default '-1',
  `SubRule1StatusCondition` smallint(6) NOT NULL default '-2',
  `SubRule2TestType` smallint(6) NOT NULL default '-1',
  `SubRule2StatusCondition` smallint(6) NOT NULL default '-2',
  `StatusEnforced` smallint(6) NOT NULL default '-2',
  `LocationEnforced` int(11) NOT NULL default '0',
  `CheckSeq` tinyint(1) unsigned NOT NULL default '0',
  `FailedMessage` varchar(255) default NULL,
  `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`TestRuleID`,`DUTLevel`,`ModeID`,`TestType`),
  UNIQUE KEY `TestRuleID` (`TestRuleID`),
  KEY `TestRuleID_2` (`TestRuleID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Slave:
CREATE TABLE `rctestrules` (
  `ModeID` smallint(6) NOT NULL default '-1',
  `DUTLevel` smallint(6) NOT NULL default '-1',
  `TestType` smallint(6) NOT NULL default '-1',
  `TestRuleID` mediumint(3) unsigned NOT NULL auto_increment,
  `TestRuleDesp` varchar(255) default NULL,
  `RuleType` smallint(6) NOT NULL default '-1',
  `IsActive` tinyint(1) unsigned NOT NULL default '1',
  `RuleIsFatal` tinyint(1) unsigned NOT NULL default '0',
  `SubRule1TestType` smallint(6) NOT NULL default '-1',
  `SubRule1StatusCondition` smallint(6) NOT NULL default '-2',
  `SubRule2TestType` smallint(6) NOT NULL default '-1',
  `SubRule2StatusCondition` smallint(6) NOT NULL default '-2',
  `StatusEnforced` smallint(6) NOT NULL default '-2',
  `LocationEnforced` int(11) NOT NULL default '0',
  `CheckSeq` tinyint(1) unsigned NOT NULL default '0',
  `FailedMessage` varchar(255) default NULL,
  `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`TestRuleID`,`DUTLevel`,`ModeID`,`TestType`),
  UNIQUE KEY `TestRuleID` (`TestRuleID`),
  KEY `TestRuleID_2` (`TestRuleID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I noted the following in your table:

PRIMARY KEY  (`TestRuleID`,`DUTLevel`,`ModeID`,`TestType`),
UNIQUE KEY `TestRuleID` (`TestRuleID`),
KEY `TestRuleID_2` (`TestRuleID`)

And is it just this key 2 (`TestRuleID`) that has a duplicate... I think, that having all these 3 indexes (all with auto_incremented column!) is not a good idea. TestRuleID_2 is absolutely redundant, and, I think, PRIMARY key will be more than enough. So, please, can you try to drop both TestRuleID and TestRuleID_2, then restart the relication and try to work for a while. Inform about any errors you got after that.

Hi Valeriy,

We are also getting this error on other tables, for example relaytestslog, and the indexes seem to be OK on this one.
Do you think upgrading only the slave to 5.x is a good idea ?

CREATE TABLE `relaytestslog` (
  `TestLogId` int(11) NOT NULL auto_increment,
  `LotId` int(11) default '-1',
  `WaferId` int(11) default '-1',
  `DieId` int(11) default '-1',
  `RelayId` int(11) default '-1',
  `JigId` smallint(6) NOT NULL default '-1',
  `PiggyBoardId` smallint(6) NOT NULL default '-1',
  `ActContext` smallint(6) default '-1',
  `ActCurrentModelId` int(11) default '-1',
  `ActTimingModelId` int(11) default '-1',
  `TestType` smallint(6) NOT NULL default '-1',
  `TTParamValueRev` smallint(6) NOT NULL default '-1',
  `TTParamNameId` int(11) NOT NULL default '-1',
  `ParentLogId` int(11) NOT NULL default '-1',
  `TestSeqNbr` smallint(6) NOT NULL default '-1',
  `ErrorNumber` smallint(6) NOT NULL default '-1',
  `ComputerName` varchar(25) NOT NULL default 'Unknown',
  `UserName` varchar(25) NOT NULL default 'Unknown',
  `NbCycles` int(11) NOT NULL default '0',
  `SampleRate` int(11) default '-1',
  `LimitContext` varchar(64) default '',
  `EnvContext` varchar(64) default '',
  `AppLabel` varchar(32) default '',
  `TestedTime` datetime NOT NULL default '1970-01-01 00:00:00',
  `FinishedTime` datetime NOT NULL default '1970-01-01 00:00:00',
  `Status` smallint(6) NOT NULL default '-1',
  `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`TestLogId`),
  KEY `iRelayTestsLogLotId` (`LotId`),
  KEY `iRelayTestsLogDieId` (`DieId`),
  KEY `iRelayTestsLogTimestamp` (`Timestamp`),
  KEY `iRelayTestsLogActTimingModelId` (`ActTimingModelId`),
  KEY `iRelayTestsLogRelayId` (`RelayId`),
  KEY `iRelayTestsLogActCurrentModelId` (`ActCurrentModelId`),
  KEY `iRelayTestsLogWaferId` (`WaferId`),
  KEY `iRelayTestsLogTestedTime` (`TestedTime`),
  KEY `iRelayTestsLogTestType` (`TestType`),
  KEY `iRelayTestsLogParentLogId` (`ParentLogId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I see nothing bad in using 5.0.x slaves with 4.1.x master.

As for your relaytestslog table: have you got this CREATE TABLE from the master or from the slave? Please, check that the same engine and indexes are used on the slave, just to be sure.

Hi Valeriy,

Yes the SHOW TABLE is exactly the same on the slave and master.
I think I will upgrade the slave to 5.x and hope this issue will disappear.

Thanks
Didier

Please, inform if this problem will apear with 5.0.x slaves and/or newer version of master. But note some changes in the default sql_mode values on Windows, 
http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html
and http://dev.mysql.com/doc/refman/5.0/en/replication-upgrade-5-0.html

I see no possible reasons for these errors on your relaytestslog table (unlike the previous one, with redundant indexes)... Have you checked or analyzed it after that errors?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".