CREATE SCHEMA counts; 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 line-based replication slaves (RBRs), which should not contain the "accounts" schema. I'm using replicate_wild_ignore_table & # 39; accounts & # 39; & # 39;% & # 39; So that only the schema & # 39; data & # 39; exist.
The only problem is that the replication stops when we insert in data.project with: "Error_code: 1452; HA_ERR_NO_REFERENCED_ROW;" since the accounts.user.id does not exist on the slave.
Defining "set global set foreign_key_checks = 0" permanently on slaves seems like a good solution in this case. My thought is:
- Key constraints are applied to the master, where writes and updates occur. The rejected declarations do not appear in the binlog / log are not replicated to the slave.
- Values for each inserted / updated record are explicitly defined by line-based replication. If an FK causes an update, it will be captured in another binlog statement, which will be replicated to the slaves.
- Therefore, I do not have to worry that the slave checks the FK constraints.
Is there a fault in my thinking? Has anyone tried that? I would appreciate any idea.
Other options I've considered:
set slave-skip-errors = 1452
Master and slave – removes the foreign key constraint
- Not ideal; we want to ensure the integrity of the data on the master.
Slave – removes the foreign key constraint
- Also not ideal; There are several slaves to maintain, and others may be needed. There are several cross-schema FKs that can add more in the future. I fear that it will become difficult to manage.