Description:
I have two instances of 4.1.9 running on two identical machines. they are configured with the recommended my-large.cnf files included in the distribution, with the appropriate fields uncommented for replication and InnoDB tables.
I have one application writing synchronously over one JDBC connection to the following table:
CREATE TABLE `systemQuery` (
`queryId` int(10) unsigned NOT NULL auto_increment,
`MMPQueryId` int(10) unsigned NOT NULL default '0',
`dateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
`dateModified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`umda` varchar(75) default NULL,
`queryString` text NOT NULL,
`userId` int(10) unsigned default NULL,
`firstTimeUser` tinyint(3) unsigned NOT NULL default '0',
`channelId` int(10) unsigned NOT NULL default '0',
`carrierId` int(10) unsigned default NULL,
`success` tinyint(3) unsigned NOT NULL default '0',
`staticResponse` tinyint(3) unsigned NOT NULL default '0',
`advertisementId` int(10) unsigned default NULL,
`outputServiceId` int(10) unsigned NOT NULL default '0',
`timeReceived` timestamp NOT NULL default '0000-00-00 00:00:00',
`timeSent` timestamp NOT NULL default '0000-00-00 00:00:00',
`processingTime` int(10) unsigned NOT NULL default '0',
`locationId` int(10) unsigned default NULL,
`brokerGuid` varchar(35) default NULL,
`sentStatus` varchar(35) NOT NULL default '',
`serviceCode` varchar(20) default NULL,
PRIMARY KEY (`queryId`),
KEY `userId` (`userId`),
KEY `channelId` (`channelId`),
KEY `carrierId` (`carrierId`),
KEY `advertisementId` (`advertisementId`),
KEY `locationId` (`locationId`),
CONSTRAINT `systemQuery_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`),
CONSTRAINT `systemQuery_ibfk_2` FOREIGN KEY (`channelId`) REFERENCES `channel` (`channelId`),
CONSTRAINT `systemQuery_ibfk_3` FOREIGN KEY (`carrierId`) REFERENCES `carrier` (`carrierId`),
CONSTRAINT `systemQuery_ibfk_4` FOREIGN KEY (`advertisementId`) REFERENCES `advertisement` (`advertisementId`),
CONSTRAINT `systemQuery_ibfk_5` FOREIGN KEY (`locationId`) REFERENCES `location` (`locationId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The replication slave will run fine for several minutes, up to several days, but fails intermittently when sending an insert to the table above. The following error appears in the slave database's error log:
050216 21:58:17 [ERROR] Slave: Error 'Duplicate entry '5651' for key 1' on query. Default database: 'transaction'. Query: 'INSERT INTO systemQuery (MMPQueryId,
queryString, umda, brokerGuid, sentStatus, success, staticResponse, timeReceived, timeSent, processingTime, firstTimeUser, userId, channelId, carrierId, outputServiceId, dateCreated) VALUES (413,'AKS','web:sess:F42955251D55393341ED6F2BA3AE89BB','','SUCCESS',1,0,'2005-02-16 17:40:17','2005-02-16 17:40:17',18.0,1,1354,5,999,5,NOW())', Error_code: 1062
050216 21:58:17 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'dbmaster-bin.000001' position 536730297
How to repeat:
Create the table given, and run inserts on it until it fails (like the insert from the error log).
I've also been able to recreate the problem another way:
-Create this table on the master db:
CREATE TABLE `ts` (
`tsId` int(10) unsigned NOT NULL auto_increment,
`val` varchar(75) default NULL,
PRIMARY KEY (`queryId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-Run this query against the master db many times (I wrote a simple bash script to do it):
INSERT INTO ts (val) VALUES ('blah');
The slave will eventually fail with the same error as above. Sometimes it takes a few minutes, sometimes several days.
Suggested fix:
I don't know.