global foreign_key_checks = 0 on RBR slave

Question

Example schema:

CREATE SCHEMA account;
CREATE TABLE `user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    `address` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE SCHEMA data;
CREATE TABLE `project` (
    `id` INT(11) NOT NULL,
    `account_user_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_user` (`user_id`),
    CONSTRAINT `fk_user` FOREIGN KEY (`account_user_id`) REFERENCES 
`accounts`.`user` (`id`)
) ENGINE=InnoDB;

Problem Background

I have row based replication (RBR) slaves, which should not contain the 'accounts' schema. I use replicate_wild_ignore_table 'accounts'.'%' so that only the 'data' schema exists.

The only problem is, replication breaks when we insert to data.project with: "Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW;" since the accounts.user.id doesn't exist on the slave.

Question:

Setting "set global set foreign_key_checks = 0" permanently on the slaves seems like a good fix in this case. My thinking is:

• The key constraints are enforced on the master, where are writes/updates occur; rejected statements do not appear in the binlog / do not replicate to the slave.
• Values for each record inserted/updated are explicitly set by the row-based replication. If a FK causes an update, it will be captured in another binlog statement, which will replicate to the slaves.
• Therefore, I don't need to worry about having the slave check FK constraints.

Is there a flaw in my thinking? Has anyone tried this? I would appreciate any insights.

Other options I've considered:

1. set slave-skip-errors = 1452

   • inserts to data.project won't occur (bad), since the statement that generates the error gets skipped (per MySQL: What happens to the row when skip-errors=1062 is set).

2. Master & slave - remove foreign key constraint

   • Not ideal; we want to ensure data integrity on the master.

3. Slave - remove foreign key constraint

   • Also not ideal; There are multiple slaves to maintain, and more may be required. There are multiple cross-schema FKs, and likely to add more in the future. I fear it will become unwieldy to manage.

Answer 1

To answer my own question, I tried 'set global foreign_key_checks=0' on the slave. This didn't work, as the statements generated for my RBR binlog explicitly set foreign_key_checks=1.

So, I may try option 3.