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

[2 Jun 2010 13:33] Cyril SCETBON
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
[2 Jun 2010 13:34] Cyril SCETBON
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
[7 Jun 2010 13:33] Jørgen Austvik
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.
[7 Jun 2010 13:33] Jørgen Austvik
Also: which storage engine is the slave running?
[7 Jun 2010 13:34] Jørgen Austvik
Also: Please attach the binlog file (for us to reproduce).
[7 Jul 2010 23:00] Bugs System
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".
[24 Aug 2010 13:17] Gustavo Boksar
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!
[24 Aug 2010 14:23] Cyril SCETBON
both are using ndbcluster to answer your question.
[24 Sep 2010 18:11] Sveta Smirnova
Thank you for the feedback.

Which other queries do you use? Do you use INSERT ON DUPLICATE UPDATE queries?
[24 Sep 2010 18:14] Sveta Smirnova
Please send us also mysqld's configuration files for both master and slave and problem binary log file.
[27 Sep 2010 16:28] Cyril SCETBON
I think yes
[27 Sep 2010 17:23] Sveta Smirnova
Thank you for the feedback.

Please send us mysqld's configuration files for both master and slave and problem
binary log file if possible.
[8 Sep 2016 5:54] MySQL Verification Team
can't reproduce on 7.4.11 (reproducible on old versions)