Bug #62331 Error "duplicate entry" in command REPLACE INTO

• Submitted: 2 Sep 2011 15:56
• Modified: 2 Sep 2011 21:08
• Reporter: Genilton Barbosa
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 5.0.77-log
• OS: Windows
• CPU Architecture: Any

[2 Sep 2011 15:56] Genilton Barbosa

Description:
After installing MySQL 5.0.77-log, we are facing a problem with the command REPLACE INTO table.

When we use the command REPLACE INTO table, many records are inserted / updated correctly, but a few cases are provoking error:

Error Code: 1062
Duplicate entry 'xxxxxx' for key 1

Any tips?

How to repeat:

CREATE TABLE

CREATE TABLE `bcr_pessoa` (
  `codpessoa` mediumint(8) unsigned NOT NULL default '0',
  `nomerazao` varchar(40) NOT NULL default '',
  `fantasia` varchar(30) default NULL,
  `sexo` char(1) default NULL,
  `fisicajuridica` char(1) default NULL,
  `cnpjcpfnumero` bigint(12) unsigned default NULL,
  `cnpjcpfdigito` smallint(2) default NULL,
  `inscricaorg` varchar(20) default NULL,
  `email` varchar(50) default NULL,
  `enderrua` varchar(35) default NULL,
  `endernumero` varchar(10) default NULL,
  `endercomplemento` varchar(10) default NULL,
  `enderbairro` varchar(30) default NULL,
  `endercidade` varchar(30) default NULL,
  `enderuf` char(2) default NULL,
  `endercep` varchar(8) default NULL,
  `codclientefora` int(15) unsigned default NULL,
  `status` char(1) NOT NULL default '',
  `datainclusao` datetime default NULL,
  `codusuinclusao` mediumint(8) unsigned NOT NULL default '0',
  `dataalteracao` datetime default NULL,
  `codusualteracao` mediumint(8) unsigned default NULL,
  `foneddd1` varchar(5) default NULL,
  `fonenro1` varchar(8) default NULL,
  `fonecmpl1` varchar(12) default NULL,
  `foneddd2` varchar(5) default NULL,
  `fonenro2` varchar(8) default NULL,
  `fonecmpl2` varchar(12) default NULL,
  `foneddd3` varchar(5) default NULL,
  `fonenro3` varchar(8) default NULL,
  `fonecmpl3` varchar(12) default NULL,
  `faxddd` varchar(5) default NULL,
  `faxnro` varchar(8) default NULL,
  `indcontribicms` varchar(1) default NULL COMMENT 'Indica cliente contribuinte de ICMS (S - Sim / N - Nao)',
  PRIMARY KEY  (`codpessoa`),
  FULLTEXT KEY `nome_fantasia` (`nomerazao`,`fantasia`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

EXECUTE COMMAND

REPLACE
   INTO bcr_pessoa
        (CODPESSOA,NOMERAZAO,FANTASIA,SEXO,FISICAJURIDICA,CNPJCPFNUMERO,CNPJCPFDIGITO,INSCRICAORG,EMAIL,ENDERRUA,ENDERNUMERO,ENDERCOMPLEMENTO,ENDERBAIRRO,ENDERCIDADE,ENDERUF,ENDERCEP,
         CODCLIENTEFORA,STATUS,DATAINCLUSAO,CODUSUINCLUSAO,DATAALTERACAO,CODUSUALTERACAO,FONEDDD1,FONENRO1,FONECMPL1,FONEDDD2,FONENRO2,FONECMPL2,FONEDDD3,FONENRO3,FONECMPL3,FAXDDD,FAXNRO,
         INDCONTRIBICMS)
 VALUES (126925,'WEBER WELINGTON ANTONIO SANTANA ME','WAI UTILIDADES','J','J',76539010001,21,0624021120024,'[e-mail]','RUA BEATRIZ RANIERI LOJA A',40,'	','FERNAO DIAS','BELO HORIZONTE','MG',31920180,
         213058,'A','2008-07-02 00:00:00',0,'2011-09-01 00:00:00',0,31,34836072,'',31,92135155,'',31,34836072,'','','',
         'S')

[2 Sep 2011 21:08] Genilton Barbosa

Problem solved.

In fact, it was not a MySQL error. There were duplicate records in other table accessed in a trigger after insert on the original table. Another user have created the trigger with some problems and I didn't realize that.

Sorry for my fault on reporting this as a bug.