Thesql replication – missing table for foreign key on slave

MySQLreplication

i have the following situation:
We're running a simple master slave replication with table filters, so the slaves doesn't have all the master tables.

  • Master has table A with FK to table B
  • Slave has table A with FK to table B which doesn't exists (since it's filtered out)

When a new record is inserted to table A on the master, the SQL_THREAD on the slave is failing due to FK error.

Could not execute Write_rows event on table DB.A; Cannot add or update a child row: a foreign key constraint fails (DB.A, CONSTRAINT FK_name FOREIGN KEY (TypeId) REFERENCES B (TypeId) ON DELETE NO ACTION ON UPDATE NO ACTION), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW;

I've tried to set session and global foreign_key_checks=0
and also tried setting @@global.init_slave='SET foreign_key_checks=0'

none of them seems have effect and i still get the above error.

Is there a way to make the slave ignore FK checks?

Thanks.

Best Answer

Since error 1452 breaks the SQL thread, you can make MySQL Replication skip that error by adding the slave-skip-errors option under the [mysqld] group header in the Slave's my.cnf as follows:

[mysqld]
slave-skip-errors=1452

You will have to restart mysql for this to take effect.

Give it a Try !!!

CAVEAT : Your data integrity will deteriorate when added new data because many INSERT queries or multiple-row INSERTs will fail because of just one row not having parent keys.

You should really consider loading all referenced tables into the slave to have good, clean data. Otherwise, it's not a true copy of the Master.

Related Question