MySQL Bugs: #39155: Replication error duplicate entry

Bug #39155	Replication error duplicate entry
Submitted:	1 Sep 2008 11:29	Modified:	1 Oct 2008 14:50
Reporter:	Andres Oviedo	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.1.23	OS:	Windows (2000 sp4)
Assigned to:		CPU Architecture:	Any
Tags:	Duplicate entry, replication

Description:
Hi,

I'm having problems with replication on slave. Replication breaks because of a duplicate entry error (INSERT sentence):

080805  9:13:51 [ERROR] Slave SQL: Error 'Duplicate entry '578835' for key 'PRIMARY'' on query. Default database: 'ttm_master'. Query: 'INSERT INTO users (telephoneNr, idOperator, tsStart) VALUES ( NAME_CONST('pTelephoneNr',_utf8'+34771574621'),  NAME_CONST('pIdOperator',21407),  NAME_CONST('pTsStart',_binary'2008-08-04 10:59:53'))', Error_code: 1062

I have searched the key '+34771574621' that failed to insert on slave table but it doesn't exists so i don't understand why is failing the insertion :(

Thanks in advance for your help.

Info
---------
Slave version 5.1.23
Master version 5.0.51

Table creation query
--------------------
DROP TABLE IF EXISTS `ttm_master`.`users`;
CREATE TABLE  `ttm_master`.`users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `telephoneNr` varchar(45) NOT NULL,
  `acceptPush` tinyint(1) NOT NULL DEFAULT '1',
  `idOperator` int(10) unsigned DEFAULT NULL,
  `tsStart` datetime DEFAULT NULL,
  `tsOperatorChange` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_TELEPHONENR` (`telephoneNr`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=608031 DEFAULT CHARSET=utf8;

Table status
------------
Name, Engine, Version, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Collation, Checksum, Create_options, Comment
'users', 'InnoDB', 10, 'Compact', 614219, 60, 37289984, 0, 22609920, 0, 608031, '2008-02-26 12:12:28', '', '', 'utf8_general_ci', , '', ''

How to repeat:
The problem repeats frequently but i don't know the cause.

my.ini, error.log

Attachment: 2008-09-01.zip (application/x-zip, text), 10.11 KiB.

Thank you for the report.

But version 5.1.23 is a bit old. Please upgrade to current version 5.1.26 and inform us if problem still exists.

Also your slave is not read-only. Do you  do updates on slave? And duplicate key error is not about telephone number, but primary key which generates automatically for query like in the error log. So, please, also check if problem is with your setup and not MySQL server itself.

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".