Bidirectional Sync

[Rafael Viana]

Hi,

I've been testing SQLyog with bidirection sync. I've the following scenario:

One database installed on an internet server and one installed on each customer computer to allow them work without internet.

When internet come back that database needs to sync with internet's database.

Considered I create a customer on internet server named “Rafael” with id 1. If someone create a customer locally with name = “Test” with id 1 (database wasn't sync with internet, so it didn't see online change). When database syncs, The “Rafael” created on internet will be updated to “Test”.

Why wasn't created customer id 2 called “Test”?

[peterlaursen]

You should understan 2 things:

1) A bidirectional/two-way sync is simple a one-ways sync in one direction (source –> target) followed by another onw-way sync in the other direction (source and target now reverted).

2) For either sync's rows with the same PK-value target will be updated (or 'overwritten) wiht the soruce row.

Consider you have data

source table:

Code:

PK other_column

1 a

target table:

Code:

PK other_column

1 b

Note that they have the same PK-value. The first sync will overwrite 'b' wiht 'a' on target. Nnext sync does nothing as tables are identical.

If you want to insert on two databases between sync the PK must be defined in such a way that the same PK-value will not be created on boths host. One way to achieve this is to add a column one ach table having different defaults on the two and include this column in the PK.

Did I get the point/problem?

[Rafael Viana]

'peterlaursen' wrote:

You should understan 2 things:

1) A bidirectional/two-way sync is simple a one-ways sync in one direction (source –> target) followed by another onw-way sync in the other direction (source and target now reverted).

2) For either sync's rows with the same PK-value target will be updated (or 'overwritten) wiht the soruce row.

Consider you have data

source table:

Code:

PK other_column

1 a

target table:

Code:

PK other_column

1 b

Note that they have the same PK-value. The first sync will overwrite 'b' wiht 'a' on target. Nnext sync does nothing as tables are identical.

If you want to insert on two databases between sync the PK must be defined in such a way that the same PK-value will not be created on boths host. One way to achieve this is to add a column one ach table having different defaults on the two and include this column in the PK.

Did I get the point/problem?

Yes, I got it. Using in this way, will it work with INSERT/UPDATE/DELETE?

I already didn't receive an answer from sales team… Do you know how many days do I need to wait for an answer?

[Rafael Viana]

'Rafael wrote:

Yes, I got it. Using in this way, will it work with INSERT/UPDATE/DELETE?

I already didn't receive an answer from sales team… Do you know how many days do I need to wait for an answer?

I was testing it with composite primary key. However, I can't use it.

Is there another way?

[peterlaursen]

I can think of 2 more ways (if the PK is an auto_increment integer).

1) use the auto_increment_offset and auto_increment_increment server variables so that *EVEN* PK values will be created on one server and *ODD* values on another

2) start incrementing from “1” on one server and from “10.000.000” (or whatever) on the other (use a BIGINT).

The imporant thing is that the same PK value should never be created on both hosts for the same table. This also indirectly replies to your questions about INSERT, UPDATE and DELETE.   There are no issues with  INSERT and UPDATE .  But if you want to DELETE you will need to DELETE on both hosts.  If you only DELETE on one host it will be INSERTED again by the sync job. 

[Author]

Posts