2-way sync

[tapsboy]

the 2-way sync here works very funnily

it asks u for a source and a target ……

so first it updates the target based on the source…

and then the source is updated based on target……….

now consider a situation where a particular data is changed in the target table…

if we synchronize now….then by common logic this change in target table should happen in the source table as well if we perform 2-way sync..

but the reverse happens.

the sync updates the target from the source…

and thereby the change made on target is also lost

and no change happens on the source…….

ne solutions/suggestions????

[Ritesh]

The result you got is expected.

On similar row of data, SJA will always update target data with data from source. It does not take time in consideration while updating the data.

[tapsboy]

hey ritesh,

thanks for your prompt reply

newayz i believe ur from the company itself

firstly your product is certainly a good one..overall..

now SJA (i assume it is the core engine of SQLyog)

if it cannot take time into consideration then the overall sync objective is not achieved i guess..

wat it then actually does is very much user-dependant…..

i.e. depending on us specifying source and target…….

this scenario is highly dangerous…..

as new data last changed on the target would not change the source ……ideally this should happen in a 2-way sync….

and instead SJA change the new data on target with old data on source…

tats crazy at times…..

but anyway..if you have any other solutions..or ways to get around this problem……plz get back…

2-way sync becomes the main reason for us to purchase SQLyog if we do…….

thanks,

tapan

[peterlaursen]

Have you searched the forum for issues relating to yours ? There are lots of them! One of the latest:

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

And earlier this morning I wrote this one: http://www.webyog.com/forums/index.php?act…=ST&f=10&t=1423

Basically, in case the PK (or any other identification of the rows) are identical, the program needs af RULE to decide what to do. And that rule can only be described by a human. MySQL (or any Databaser Server) offers no mean to decide which of the rows are the “right one”. Timestamps are no good either since they with MySQL have only an “accuracy” of 1 (one) second. it's on the to do list at MySQL to introduce one ore more timestamp-formats with more accuracy (probably milliseconds)

You see that the SJA is constantly being discussed! But don't expect any computer program to do what a computer will never be able to do: to make human decisions and planning. It's your responsibily as a DB-Admin to organise data structure, procedures for updating the DB's etc.

As Ritesh wrote in the first thread: Any reasonable and practicable request can be implemented. What are your needs and your situation ?

[tapsboy]

hi peter, thanks for your inputs…

let me tell u more about us first

i m not a DBAdmin…..just a php/mysql programmer

we are around 50 employees…….

almost half at our head office in ahmedabad……and

the other half……spread all across India…….

now we have build certain applications tat help this ppl from outside make direct entries on the internet…site…..

also…a lot of work is done at the head office…..

but internet speed being relatively slow..and it seems unlikely any gr8 change is gonna happen in near future about the speed……….

the ppl at HO can't work on the internet directly…

thus we maintain 2 databases…1 at HO and 1 on website…..

and synchronize them manually (means cut, copy, paste) as the needs comes……but with a lot of pains……and data loss due to human error……

ok now the issue…..

well i fully understand the fact that the computer or the software will not be able to make human decisions……

but what i want is basically……is that:

if there is a change on both the machines for the record with the same primary key…

it can give a prompt and an interface..

saying that this record has changed on both places…

it will show records as on both the servers aswell……..

and the user will be able to see the difference clearly.and then …..

change the respective records there itself……and update them on server…

currently with 2-way sync…

the source updates the target…..irrespective of the fact..whether there is change..in the target records or not……

this will work fine for newly inserted records…and

it might work well for deleted records…if auto-increment is used in PK

but in case of update…..

if SQLyog can detect…using timestamp…(a few seconds inaccuracy is OK )

tat change on a particular record has happend on both the machines…..

and it leaves the user to decide…wat to do…

it will be gr8…….

expecting ur reply soon……

Regards,

tapan

[peterlaursen]

There are workarounds, but it's hard for others to tell what will work best in your situation.

1)

You can use timestamps (alone or in conjunction field other fields) as the PK – but then MySQL server versions must be identical (timestamp formats have changed with different version). Note that MySQL automatically sets DEFALT as CURRENT_TIMESTAMP for the FIRST timestamp in the table definition if it is defined as “NOT NULL”. You can then get the “last updated time” by setting it explicitly to NULL (the server will then automatically overwrite NULL with CURRENT_TIMESTAMP since it's defined as “NOT NULL”).

2)

You can build a PK from more fields, where one is server specific. Example: Create a new column with each table. Could be a TINYINT for instance. Define it as “NOT NULL” and let DEFAULT be “0” with one server and “1” with the other. Set it explicitly “NULL” from your code when updating or inserting (the server will then automatically overwrite NULL with DEFAULT since it's defined as “NOT NULL”). Build a PK with that column and one or more others. In case they have been updated at both servers you will then have two records resulting from the sync: one with value “0” and another with value “1” in the PK. You could find those “double entries” with some query and decide what to do with them – either manually or automated. After doing that you run a maintainance script that set all rows -1 (at both servers). Just one example of a workaround – you must use your imagination!

In this case “0” means that data have been updated (or inserted) at one server “1” that they have been updated(or inserted) at the other server and “-1” that they have not been updated since last sync. How to handle deletion of data with this case … hhmmmmm … find out for yourself 😉 !

But no matter what you do, it takes a lot of discipline to run two versions of the same database, if data shall not be corrupted!

[peterlaursen]

just want to add something …

it takes a lot of discipline to run two versions of the same database … AND it takes a lot of code !!

YOU will have to write a lot of code that does what the MySQL server would normally do itself!

[Shadow]

Prompting is not really a solution if you sync dbs in which thousands of records get inserted/updated…

[Author]

Posts