MySQL Bugs: #54182: Unique constraint make the replication crash

Bug #54182	Unique constraint make the replication crash
Submitted:	2 Jun 2010 13:33	Modified:	8 Sep 2016 5:54
Reporter:	Cyril SCETBON	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Cluster: Replication	Severity:	S2 (Serious)
Version:	mysql-5.1-telco-6.3	OS:	Linux (debian etch)
Assigned to:		CPU Architecture:	Any
Tags:	5.1.27-ndb-6.3.17-telco, cluster, crash, replication, unique
Triage:	Triaged: D2 (Serious) / R6 (Needs Assessment) / E6 (Needs Assessment)

Description:
when we do a lot of inserts, delete, updates into the following table :

CREATE TABLE ndrtc_credentials (
  ndrtc bigint NOT NULL,
  infranetaccount char(9),
  flag_trt boolean,
  ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (ndrtc) USING HASH,
  UNIQUE KEY idx_ndrtc_infranetaccount (infranetaccount)
) ENGINE=ndbcluster 

after a while (after 11794 requests) when we update the table with the following request :
update ndrtc_credentials set ndrtc = NNN where infranetaccount = 'SSS';

The replication fails :

100528 13:06:11 [ERROR] Slave SQL: Error in Write_rows event: commit of row events failed, table `spp`.`ndrtc_credentials`, Error_code: 1
100528 13:06:11 [ERROR] Slave SQL: It was not possible to update the positions of the relay log information: the slave may be in an inconsistent state. Stopped in ./replication01-relay-bin.000003 position 4911868, Error_code: 1105
100528 13:06:11 [ERROR] Slave (additional info): Can't write; duplicate key in table '' Error_code: 1022
100528 13:06:11 [Warning] Slave: Got error 893 'Constraint violation e.g. duplicate value in unique index' from NDB Error_code: 1296
100528 13:06:11 [Warning] Slave: Can't write; duplicate key in table ''
Error_code: 1022
100528 13:06:11 [Warning] Slave: Got error 121 during COMMIT Error_code: 1180
100528 13:06:11 [ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'replication01-bin.000001' position 4911714

we tried to replace the update with an delete+sleep(x)+insert and it works only for x>0

How to repeat:
no easy

Suggested fix:
none

when we remove the unique index, we've no more problem with our update request and we don't need to use the delete+sleep(x)+insert temporary fix

Thanks for the bug report.

Can you please tell us which settings you are using for replication? (Idempotent?)
http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#sysvar_slave_exec_mo...

Please also specify which nodes replicate to which other nodes.

Also: which storage engine is the slave running?

Also: Please attach the binlog file (for us to reproduce).

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

I'm having the same problem. Master had processed all queries without problems, but slave stop sync with error code 1452

Last_Errno: 1452
                 Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (`indt/diagnosticoFallecido`, CONSTRAINT `diagnosticoFallecido_2` FOREIGN KEY (`persona_id`) REFERENCES `persona` (`persona_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)' on query. Default database: 'indt'. Query: 'INSERT INTO diagnosticoFallecido (tipoDiagnostico_id,persona_id,orden) VALUES ('833','1090447','1')'

The problem is that it's trying to insert a record referencing a non-existant person.

Both servers are running InnoDB engine with cloned configs(excecpt master/slave config obviously). Any suggestions? thanks!

both are using ndbcluster to answer your question.

Thank you for the feedback.

Which other queries do you use? Do you use INSERT ON DUPLICATE UPDATE queries?

Please send us also mysqld's configuration files for both master and slave and problem binary log file.

I think yes

Thank you for the feedback.

Please send us mysqld's configuration files for both master and slave and problem
binary log file if possible.

can't reproduce on 7.4.11 (reproducible on old versions)