Custom Two-way Synchronization?

[PGn]

Hello,

 

I’ve two remote production mysql databases, let’s call them West and East, that contain unique player data.  When a player joinds the West server, his data will be updated on that db.  If at some point that same player joins the East server, his player data will be updated there as well, but now the East data is more current than the West data.  Hence the need to regularly synchronize West and East bi-directionally so that both dbs have the latest data for every player, regardless of which server they last played on.

 

The Database Synchronization Wizard was used to create a sync job scheduled to run every 10 minutes between West and East DBs, which works except for one problem: player data is not getting updated accuratly on the target DB (West) when a player plays there.  It appears that the data from East (Source) is overwriting the more current data on West.

 

Both dbs use the same unique player ID as the primary key.

 

Is this type of two-way synchronization not possible with the sync wizard?  Is it possible to create a custom job to do this type of sync and if so, how?

 

Thank you,

 

PGn

[peterlaursen]

What you have observed is per design.  And I/we have replied the same many times here already. 

 

Data Sync uses the Primary Key of every table to identify if rows are identical or not.  If the value of the Primary Key for a specific row is the same they are considered identical and source will overwrite target for that row. That is the purpose of the Primary Key in relational databases really: it is a unique identification of a row of data. 

 

Consequently, if you want to INSERT randomly to both databases between sync jobs, you will have to define the Primary Key in such a way that the same PK-value is not created on both source and target.  There are at least two ways to do this:

1) Configure servers to use auto_increment_increment and auto_increment_offset values so that (for instance) *odd* PK-values are created on one server and *even* values on the other (this requires that the Primary Key is an integer, of course, and can be difficult to do if you already have data that need to be reindexed asitmay break Foreign keys to do so). auto_increment_increment and auto_increment_offset options are available in MySQL for use with master-master replication primarily, but work equally well with data sync. The scenarios are also quite similar.

2) Add a ‘dummy’ column to every table with different default value on both servers and add this ‘dummy’ column to the Primary Key.

[Author]

Posts