Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
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. –  Craig Efrein Nov 30 '11 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 '11 at 16:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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