0

I've a master and slave (different machines) and got the slave stopped with one duplicate entry error. If I run the query that causes the error (I can see that query via SHOW SLAVE STATUS) on the slave (when the replication is stopped) there is no error, but the slave is stopped by this query (in fact, if I try to restart the replication with START SLAVE, the error appears again). Why a right query (I can run it on the slave without error message) can stop the replication?

TABLE STRUCTURE

CREATE TABLE `table` (
  `a` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(255) NOT NULL,
  `c` varchar(25) DEFAULT NULL,
  `d` char(40) NOT NULL,
  `e` char(64) DEFAULT NULL,
  `f` varchar(255) NOT NULL,
  `g` varchar(100) NOT NULL,
  `h` varchar(25) NOT NULL,
  `i` enum('disabled','enabled') DEFAULT 'disabled',
  `j` enum('disabled','enabled') DEFAULT 'disabled',
  `k` enum('disabled','enabled') DEFAULT 'disabled',
  `l` enum('production','sandbox') CHARACTER SET latin1 NOT NULL DEFAULT 'production',
  `m` enum('active','uninstalled') NOT NULL DEFAULT 'active',
  `n` datetime NOT NULL,
  `o` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `p` int(11) DEFAULT NULL,
  `q` char(40) DEFAULT NULL,
  `r` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `s` char(40) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `b_d_index` (`b`,`d`),
  KEY `e` (`e`),
  KEY `f` (`f`),
  KEY `g` (`g`),
  KEY `h` (`h`),
  KEY `i` (`i`),
  KEY `j` (`j`),
  KEY `k` (`k`),
  KEY `l` (`l`),
  KEY `m` (`m`),
  KEY `n` (`n`),
  KEY `o` (`o`),
  KEY `p` (`p`),
  KEY `d` (`d`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

COMPLETE ERROR

Error 'Duplicate entry 'B-D' for key 'b_d_index'' on query. Default database: 'database_name'. Query:
INSERT INTO `table` 
SET `b` = 'B',`c` = 'C',`d` = 'D',`e` = 'E',`f` = 'F',`g` = 'G',`h` = 'H',`i` = 'I',`j` = 'J',`k` = 'K',`l` = 'L',`m` = 'M',`n` = NOW(),`o` = NOW(),`p` = NULL,`q` = 'Q',`r` = NOW() 
ON DUPLICATE KEY UPDATE `c` = 'C',`d`= 'D',`e`='E',`f`='F',`g`='G',`h`='H',`i`='I',`j`='J',`k`='K',`l`='L',`m`='M',`o`='2012-12-19 14:06:34'

Thanks in advance.

Felipe Alonso
  • 413
  • 3
  • 9
  • Are you sure that the slave has all the same indexes? – ethrbunny Dec 19 '12 at 15:51
  • Different checksum (and cardinality of some indexes) for the table in master and slave. I've "resynced" following http://www.mysqlperformanceblog.com/2008/06/29/resyncing-table-on-mysql-slave/ Maybe the query was right but the inconsistency caused the error in some cases. I'll check the checksum and the replication for a couple of weeks and I'll be back to comment about it. – Felipe Alonso Dec 20 '12 at 08:10
  • If the unique keys it was relying on to do the updates were missing it would explain this error. – ethrbunny Dec 20 '12 at 14:58
  • No problem since I "resynced" the tables in master and slave (7 days ago). I don't know exactly what you mean, ethrbunny; you're talking about change the query adding the field `b` in the UPDATE part? – Felipe Alonso Dec 27 '12 at 08:45

0 Answers0