SymmetricDS双向同步下双表双查询的外键冲突解决方法

问题背景 #

我们有两个InnoDB表：

CREATE TABLE A
(
    id BINARY(16) PRIMARY KEY,
    name VARCHAR (128) NOT NULL UNIQUE,
)
ENGINE=InnoDB;

CREATE TABLE B
(
    id BINARY(16) PRIMARY KEY,
    A_id BINARY(16) NOT NULL,
    info VARCHAR (128) NOT NULL,

    FOREIGN KEY (A_id)
        REFERENCES A(id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
)
ENGINE=InnoDB;

SymmetricDS已配置为双向同步模式：

• 仅操作表A时，两端插入ID不同但name相同的记录，SymmetricDS会自动修改一端的ID，保持两端id和name一致，无冲突。
• 但如果两端同时插入表A和关联的表B记录（如下），会因表A的ID被自动修改，导致表B的A_id与修改后的A表ID不匹配，触发外键冲突：

-- 总部（Corp）操作
INSERT INTO A (id, name) VALUE (0x01, "X");
INSERT INTO B (id, A_id, info) VALUE (0xAA, 0x01, "X");

-- 现场端（Field）操作
INSERT INTO A (id, name) VALUE (0x02, "X");
INSERT INTO B (id, A_id, info) VALUE (0xAB, 0x02, "X");

解决方案 #

1. 基于业务唯一键配置SymmetricDS冲突解决 #

因为表A的name是唯一约束，可以把它作为SymmetricDS冲突检测与合并的核心键，替代默认的数据库主键id：

• 在SymmetricDS的表同步配置中，将sync.key.columns设置为name。
• 配置conflict.resolve.type为FIRST_WIN或LAST_WIN，同时开启id.generate.type为EXTERNAL，让SymmetricDS以name为基准合并记录，自动同步关联表B的A_id到合并后的正确ID。

2. 全局统一生成唯一ID #

从根源避免两端生成不同id引发的冲突：

• 统一使用全局唯一ID生成规则，比如两端都调用同一个UUID生成服务，或使用MySQL的UUID_TO_BIN(UUID())生成一致的BINARY(16) ID。
• 或者由总部统一分配ID，现场端插入表A前先从总部获取可用ID，确保两端插入的表A记录id完全一致，后续表B的A_id自然不会出现不匹配问题。

3. 配置SymmetricDS的级联同步与ID映射 #

利用SymmetricDS的ID映射功能，确保主表ID变更后关联表自动同步更新：

• 在SymmetricDS中为表A配置id_mapping，记录两端ID的对应关系。
• 配置表B的同步规则，在加载数据时自动将原A_id替换为映射后的目标端ID。
• 确保MySQL的ON UPDATE CASCADE外键约束在SymmetricDS同步中生效，主表ID修改时，关联表的A_id会自动级联更新，无需手动干预。

4. 调整同步顺序，确保主表优先同步 #

强制SymmetricDS先同步表A的记录，解决冲突后再同步表B的记录：

• 在SymmetricDS的路由配置中，设置表A的同步优先级高于表B。
• 配置sync.order参数，让表A的同步任务先执行，待表A的ID冲突解决完成后，再处理表B的同步，此时表B的A_id可以匹配到已经修正后的表A ID。

5. 临时禁用外键检查（仅应急场景） #

如果以上方案暂时无法实施，可临时在同步节点禁用外键检查（不推荐长期使用，可能导致数据不一致）：

• 在SymmetricDS的数据加载配置中，添加前置SQL：SET FOREIGN_KEY_CHECKS = 0;
• 同步完成后执行后置SQL：SET FOREIGN_KEY_CHECKS = 1;

内容的提问来源于stack exchange，提问作者Predrag Manojlovic