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;
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.
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:
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).
Master & slave - remove foreign key constraint
- Not ideal; we want to ensure data integrity on the master.
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.