1

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?

Predrag Manojlovic
  • 379
  • 5
  • 16

1 Answers1

2

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.

Boris Pavlović
  • 63,078
  • 28
  • 122
  • 148
  • OK. That makes sense. I'll try that. I was hoping to do that without extending class, by making settings in sym_tables due to a practical reason. – Predrag Manojlovic May 17 '23 at 11:12
  • Actually, that doesn't work. As it is not possible to identify the parent table (A) ID after being updated. Do you have any idea or example of working SymmetricDS MySQL two-way sync? – Predrag Manojlovic May 17 '23 at 20:17
  • What about adding a table C in both the corp and field that will be having columns (name, node_id, id)? The filter would intercept each insert into table A and persist the source node_id, the name and the originating id that would later be used to resolve the name of table C’s foreign key referring table A. – Boris Pavlović May 18 '23 at 12:09
  • Thanks. Although that seems like the solution, it is far from practical. This A B was just a simplified example. We have ~50 tables in this system, a lot of FKs so adding a table for each would be overkill. I would expect that is out of the box handled by SymmetricDS and it should be just a matter of settings requiring minimal modification of the tables and programming especially if they are in normal forms. Otherwise, SymmetricDS would be quite unusable. There should be a more practical approach but there is a lack of examples for the two-way setup. – Predrag Manojlovic May 18 '23 at 14:51
  • I’m that case add a column table_name to the table C and have the filter persisting it on insert of all tables like A and querying it on every update of tables like B holding FKs to tables like A. – Boris Pavlović May 18 '23 at 17:14