Two Way Synchronization

[soatley]

Hello,

I am currently using SQLyog Enterprise 4.05 on a Trial and I am trying to get Synchronization working. Here is my example:

I have two seperate servers, MySQL 4.1.11 on both with a table called menulayout on both. If I have a row on the source server that needs to be updated on the target server AND I have a row on the target server that needs to be updated on the source server, they won't update. The source server will send the data to the target server, but it won't take the row that needs to be updated from the target and update the source. I have two way synchronization turned on but it doesnt' seem to work.

In a real situation, I could have a table that has different data on each server that would need to be synched so that both are identical.

Can this be done?

[peterlaursen]

have you looked back in the forum ?

The important thing is that you understand how Sqlyog uses the “Primary Key” of the table for syncronization.

Please read through this (long) thread and come back with more questons then:

http://www.webyog.com/forums/index.php?act…=ST&f=10&t=1199

[indy]

The Webyog software does not SYNCHRONIZE two tables as it claims. The English language definition of synchronization suggests that two objects are made identical through a process that does not remove from either of them. What Webyog does is it UPDATES the target table with the source table. Do not use Webyog for synchronisation or you will be sorry. Webyog simply compares the primary keys and if the data is different in one table it updates it from the source table, overwriting the data on the target table. So watch it! This ain't synchronisation by a long shot. You could lose data if two tables are being written to separately. The Oracle database syn facility is a true sync facility and you can use tablers in different locations and truly syn them without worrying about lost data.

[peterlaursen]

It won't make much sense to discuss the correct meaning of a word. But I actually just checked a few high quality english dictionaries and found no definition like yours. The original meaning of the word relates to TIME and SPEED only (The word “synkron” is originally greek and means “at the same time”) and there is in my opinion no autoritative meaning of the word when referring to DATA.

The problem is I think,

1) that records can be deleted from one server ON PURPOSE. In that case syncronisation should also delete that record on the other server. So when there is a record existing on one server and there is no corresponding record on the other, there is no way for a computer to tell if it is because one has been deleted or if one has been added!

2) and when originally identical data have been changed at both servers while hey have been disconnected no computer can tell either, which of the two updates are the “right one”.

I might be possible to have an option added like “keep records from both servers”, so that when records with identical keys but different content are met, a new record is created at the server. But that would make the syncronisation tool VERY slow, because it would have to compare every bit of data and not only an index. And could in case 2) erroneously create two records where there should only be one!

So what are your practical needs? There might be more options that can be built in the program, workarounds for common situations …

[soatley]

When I first downloaded the software, I too thought that synchronization would be where the tables would be checked by individual records and updated both ways. After reading the forums and determining my needs more, I am going to write a custom script that will handle what I need.

Thanks,

Shawn

[Ritesh]

Two way synchronization updates the target row from the source row ONLY in case of conflicting primary keys.

Do you have an alternate solution for managing conflicting primary keys? All Forums members are encouraged to provide their suggestions. If we reach a general consensus we will put it in our development calendar.

[peterlaursen]

I think I wrote this before … but it could be very long time ago.

Would it be possible to have an option use a unique index (that is not a PK) for syncronization ?

It would (sometimes – depending on the data) be quite simple to create one on both servers before syncronization runs!

Another workaround would be to create a sql-dump with the deleted (and maybe even the updated) data. That dump could easily be edited and imported if it contains data that should be kept.

I think a common situation is that people use DB's on their websites (that are user INSERTable and UPDATEable) and have a copy of the DB on their laptop and work on it when they are not connected. I can even see some idea with it … That could easily result in conflicting PK's.

But still I think we need a description of the situations where the sync tool as it is now doesn't meet the needs and expectations of users …

[peterlaursen]

And one thing more …

It could be possible to SIMULATE a sync. That would display the number of deleted records, and if people expect 0 (zero) they have been warned. That could be combined with the idea of an SQL-dump.

[Author]

Posts