mysql replication - missing table for foreign key on slave

Question

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.

Answer 1

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.

• omr: Thanks Rolando, however skipping the error will not insert the new row to the slave, which was the intention in the first place. in my case we wish to keep the slave size as small as possible, without redundant tables so it doesn't need to be a true copy of the master, only for the replicated tables.

Answer 2

The exact reason of why this happens, is because SESSION variables are replicated explicitly in the binary log. Here is an example of an entry header in the binary log:

# at 368008153
#160624 9:15:24 server id xxxxx end_log_pos 368008235 Query thread_id=1097746 exec_time=0 error_code=0
SET TIMESTAMP=1466774124/*!*/;
SET @@session.pseudo_thread_id=1097746/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=2097152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;

This behaviour was added in MySQL version 5.0.

The way to ignore this, is through slave-skip-errors=1452, which I do not recommend as data consistency can be damaged.

Answer 3

On the slave ALTER TABLE A to drop the foreign key constraint to table B. This will stop the errors and you will be able to insert data into table A.

You have chosen not to replicate table B so you must be OK with the data integrity implications. Actually if you are careful to never alter data on the slave (i.e. revoke all insert and update permissions) this should not be a problem since the master will enforce the data integrity.