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

  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.
  • 1
    I sometimes think that FKs are more trouble than they are worth. – Rick James Nov 30 at 1:02

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.