0

I have setup a master-slave replication between two MySQL servers using mysql Version 14.12 Distrib 5.0.51a, for debian-linux-gnu.

Recently the replication errored out with this in the logs:

111128 17:41:12 [ERROR] Slave: Error 'Cannot add or update a child row: a foreign key constraint fails (test/bookmap_item, CONSTRAINT bookmapitem_bookmap FOREIGN KEY (map_id) REFERENCES bookmap (map_id) ON DELETE CASCADE ON UPDATE CASCADE)' on query. Default database: 'test'. Query: 'INSERT INTO bookmap_item (map_id, ezcontentobject_id, ezcontentnode_id, section, sub_section) VALUES(21514, 20205, 27601, 252, 291)', Error_code: 1452

My question is, when using MySQL's replication, is there anyway to keep the slave from enforcing foreign keys? Or is there something else I could try?

1 Answer 1

4

On the slave, you can configure MySQL to skip certain errors by using the slave-skip-errors configuration item.

In the case above, you'd add the following to your my.cnf;

slave-skip-errors = 1452

But I'd be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave could not be satisfactorily promoted to take the place of the master in the case of something going wrong with your master hardware...

http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_slave-skip-errors

2
  • Thank you for the quick response. The slave server would only be used as a read only server that takes the place of the master server in case of maintenance or an unexpected failure. Nov 30, 2011 at 15:17
  • That is what slaves are usually used for. But, as Dave pointed out, this removes many data integrity checks on the slave. This means that what a query returns on master can be totally different on that slave since you are now allowing duplicates on tables master does not. And, of course, business needs can always change down the line and bring a need for using the slave as master, even if temporarily and then you'll realize the difference in the dataset prohibiting.
    – TechieGurl
    Nov 30, 2011 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.