Tjyylli

I have installed and configured the database replication software from http://www.symmetricds.org/ on the client and the server. I have followed the directions to setup the example replication, and everything works as advertised.

I am interested in is bi-directional Replication on a single table. That means that each database on the client and server can be inserted/updated/deleted and changes occur on the other database. Each table is both an originator and destination of content for the other.

I've read the entire manual for symmetricDS and there is no example of how a bi-directional table should be configured. There is a single paragraph in the manual that says it can be done, but not how.

Where are the instructions to create bi-directional database replication in symmetricDS? The default example they provide is single directional replication pumps.

My System:

Client: Fedora 17 Linux with postgresql

Server: Windows 8 with mysql

My presumptuous attempt at bi-directional pumps failed:

The trigger sym_trigger_router is where you define the direction the data pumps. I create a pump in both directions. But this creates a problem with key-enforced conflicts. If an insert or update or delete is performed at the same moment on the same key, the database will have to take remedial action to restore sanity.

Are there any instructions for how to do this or has anybody done this?

Bi directional table replication in symmetricDS

Bi directional table replication is not trivial because you must deal with every kind of conflict that could arise. One example conflict is when a row is inserted on both the server and client that would together violate a unique key. Both the client and the server both allow the insert/update to occur because they do not know that there is an incoming update that would violate the key.

The synchronization engine on both sides think: "Oh no, we both told our users that it was OK to add this row based on what we knew, but we can't synchronize now because it would violate uniqueness."

The synchronization engine has a choice:

1.  Take the last insert/update by timestamp and destroy the request that came 

    first.  This is undesirable because the first person thought they committed,

    successfully when in reality their transaction was erased without anyone's 

    consent.



2.  Reject both rows, if I can't make everyone happy, I'm not letting anything

    through, and log the conflict to an external table to be dealt with later.

    The two users who thought they submitted data will find their transaction

    in a queue.



3.  Merge the rows, take a little from one, and a little from the other, and 

    create a hybrid row.  Or take one or the other based on some priority system

    or based on how filled out it is.

This is just one example of a conflict. There are hundreds of conceivable circumstances where a conflict would arise that you must plan for.

The hundreds of conceivable circumstances where a conflict could happen must have a remedial action defined in the configuration table: sym_conflict.

You could direct symmetricDS to merge the rows according to rules, deny the transactions until a human has reviewed the rows, or even program it to throw out the baby with the bathwater. This is defined in Chapter 3.8 of the user guide, configuring data conflict and resolution.

The number of potential conflicts are based upon the configuration and limitations of your database table. As you add conditions and limitations on your table, the number of potential conflicts increases exponentially. If you have unique keys, you need to prepare for unique key conflicts and define resolutions. If you have primary/foreign keys on other tables, you need conflict resolutions for those. If you get 90% of the conflicts but miss a few, then when the conflicts occur, the databases will not be identical on client and server.