MySQL Replication question

Question

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?

Answer 1

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