Synchronising Databases
Good afternoon,
I am trying to develop a protocol which will allow a stand alone laptop running OpenVpms to synchronise its database with the primary server so that all records are kept in one location eg the laptop is used at remote location X with some new records being added and possible sales taking place, etc, etc - Upon return to the primary practice the core database is updated with the changes from the laptop database. It is then refreshed so that on the next journey both databases are synchronised and we start from the same common position. (It is not possible at present to get the laptop to connect via the Internet to update the primary database.)
I have tried the Binary Log option in MySQL to see if I can track logs on the laptop and then ship the log file to the main database but it has problems with record keys I think?? So now I am looking at an application called 'pervasync' which apparently will do what I want for MySQl or Oracle. I was wondering if anyone had found a simple solution or had experience with this product in the OpenVpms environment?
Right now I am on a steep learning curve getting to grips with said product!!
Thanks for your help with this
Des
PS I have no relationship with the product mentioned or its sale in Australia .... I just discovered it on the Web during a search so I thought I would see if it can solve my problem.
Synchronising Databases
I don't think you can achieve what you want solely with binary logging.
For starters, both the primary and laptop databases need to use different id allocation to avoid clashes.
You can do this by configuring the auto_increment_increment and auto_increment_offset variables on each database so they both allocate different ids.
A description of this can be found here: htp://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
(change htp->http - Drupal's spam filter sucks...)
This same page describes how to set up circular replication - this *should* do what you want.
You will have issues where the laptop and primary database both change the same record simulatenously, but that is a problem of any replication system.
Pervasync is interesting, but I think you would be using to synchronize the entire database, rather than use it to subset data.
Not sure what it adds over straight mysql replication in this case.
-Tim
Synchronising Databases
Thanks very much Tim for your suggestion ..... I will have a look at your link.
Regards
Des
PS Pervasync requires that you set the tables with an appropriate
hierarchy
so that parent tables are mentioned first then child tables. I am struggling with this because of the complex structure of the database. If I solve it just for interest I will publish the result for others interest.