CODEWITHSUNDEEP
mysqlsymmetricds
Predrag Manojlovic
Predrag Manojlovic

Reputation: 378

SymmetricDS two way sync conflict resolution with two tables and two queries

Let's say we have two MySQL tables.

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 is set to work in two-way sync mode.

If execute conflicting queries on table one, both, from corp and the field, it will get resolved:

-- Corp
INSERT INTO A (id, name) VALUE (0x01, "X");

-- Field
INSERT INTO A (id, name) VALUE (0x02, "X");
--

This will sync successfully, without errors despite having different IDs and having the same 'name'. SymmetricDS will make sure FIeld and Corp will have the same id and name for that row by changing the id of the Field or Corp entry.

But, if we execute two queries in a row, like below, we will have a conflict as the ID of one A-table entry is updated to the other one:

-- 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");
--

What would be the approach to resolve such FK issues?

Upvotes: 1

Views: 139

Answers (1)

Boris Pavlović
Boris Pavlović

Reputation: 64632

For practical purposes the identity of table A is the column name. I would create a filter that extends the class org.jumpmind.symmetric.io.data.writer.DatabaseWriterFilterAdapter and implements the interface org.jumpmind.symmetric.ISymmetricEngine overriding the method public boolean beforeWrite(DataContext, Table, CsvData) configured both in Corp and Field that will intercept each syncing payload of tables A and B analyze it and replace primary and foreign key values with the ones corresponding to the primary key of each row identified by the value in column name.

Upvotes: 2

Related Questions