Twoway Sync With Sja(linux) Doesn´t Work

[SQLyogTester]

HI

Am using version 8.12 of sja(linux) to twowaysync two mysql databases (v5.0.32 on Debian_7etch10) with identical structure.

Sync appears to work fine, but new or altered records are not being synced correct. I´ve tried a lot of options but it´s always the same: The sync process works only from source to target, like an ordinary replication. Newer changes on the target will be overridden and new records on the target will be deleted.

I having the same problem on windows!

Any idea?

Greetings

[peterlaursen]

This is as per design, has been asked dozens of time (please search before posting) and it is well documented too: Rows on either host having the same value for a Primary Key will be compared and for suchwill overwrite (or UPDATE using a SQL-term). What should else be the purpose of a Primary Key if it is not a unique identifcation of a row?

The solution is to define the PK so that same value for the PK will not be created on both hosts. Two ways (at least) to do this:

* use the autoincrement_increment and autoincrement_offset server variables to ensure that on one host PK's are EVEN and on the other ODD (you may use this if you have a PK on an autoincrement value)

* or add to the current PK a constant column with different defaults on either host.

[SQLyogTester]

Thx for your reply!

I´ve searched before, but i couldn´t find the right solution or hint for my problem. Maybe i haven´t searched hard enough or my knowledge is insufficient!

Server 2 is like a Mirror of Server 1 and vice versa. So when i alter for example the title of a page with the uid=13 (col uid is the PK on autoincrement) on server 2, the mirrored page-record (with the same uid) on server 1 should be updated during the sync process, because the timestamp (another col of all my tables) on server 2 are newer then the one on server 1.

table PAGES on Server 1

uid pid tstamp(unix) title …

13 2 1243948375 home

table PAGES on Server 2

uid pid tstamp(unix) title …

13 2 1246540375 start

So how would your first suggestion solve this problem and how can i realize in detail your second one?

[peterlaursen]

“Server 2 is like a Mirror of Server 1 and vice versa” .. *vice versa* is the problem!

SQLyog data sync is not based on timestamps. It compares rows on both hosts (and considers rows with same PK-value identical). Only solution is ensure that the same PK-value is not created on both hosts. I described two ways to do this (one using server configuration options, another to define the PK on more columns where one has a (different for each host) constant value).

[Author]

Posts