2-way sync

This topic is empty.

Viewing 7 reply threads

Author

Posts
- May 16, 2005 at 1:42 pm #8982
  
  tapsboy
  Member
  
  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????
- May 16, 2005 at 2:47 pm #17817
  
  Ritesh
  Member
  
  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.
- May 17, 2005 at 12:14 pm #17818
  
  tapsboy
  Member
  
  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
- May 21, 2005 at 6:37 am #17819
  
  peterlaursen
  Participant
  
  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 ?
- May 25, 2005 at 9:17 am #17820
  
  tapsboy
  Member
  
  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
- May 25, 2005 at 9:54 am #17821
  
  peterlaursen
  Participant
  
  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!
- May 25, 2005 at 10:20 am #17822
  
  peterlaursen
  Participant
  
  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!
- May 25, 2005 at 11:16 am #17823
  
  Shadow
  Member
  
  Prompting is not really a solution if you sync dbs in which thousands of records get inserted/updated…
Author

Posts