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.
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?
“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).
peterlaursen
ParticipantSQLyogTester
Member
