Source Target Conflicts And Precedence.

[tjmcd]

Hey All,

We've been running SQLyog Enterprise v5.19 for about 6 months now.

With the understanding that when a conflict arises the Source data always

takes precedence over the Target data, we have evolved a 3 step work

around that allows us to COMPARE our MASTER db to our mirror or SLAVE

db's on tablet pc's in the field — BEFORE a final sync.

On each tablet (SLAVE) we run two complete CRM dbs — crm and crm2

• Step 1 we archive MASTER crm to SLAVE crm2 executing a

one way synchronization with the MASTER as Source (ArchiveHost.xml)

• Step Two we run a little php script that connects to both SLAVE_crm

AND SLAVE_crm2 setting all columns in SLAVE_crm = columns in SLAVE_crm2

WHERE SLAVE_crm.id = SLAVE_crm2.id

AND SLAVE_crm date_modified < SLAVE_crm2 date_modified //now the SLAVEcrm has all the UPDATED records that MASTER_crm has

• And finally, we execute a TWO-WAY synchronization with SLAVE as source (SyncHost.xml)

// NEW RECORDS are exchanged and the source (SLAVE_CRM) overwrites conflicts on Target (MASTER_crm).

//now MASTER_crm has all the remaining UPDATED records FROM SLAVE_crm

Trouble is… well… there are admittedly numerous pitfalls to the strategy but the biggest

is that we're just managing one too many db's per field installation,

Ideally, we would run the bridge script between the MASTER and SLAVE_crm directly

(eliminating the need for SLAVE_crm2) if only we could figure out how to employ tunneling

to make the simultaneous connection to both remote MASTER and local SLAVE and so run the script.

Somewhat less ideally, we could automate the three step process (still using crm2)

if only we could figure a way to ensure that SyncHost.xml ran ONLY AFTER bridgeScript.php

and that bridgeScript.php would be called and run ONLY AFTER ArchiveHost.xml

Is this feasable with some combination of xml, WindowsScuduler, and .bat files????

As is, this three step process is managed by a little HTA shell application AproSync.hta

which naturally requires user interaction. Should a field user ever fall behind in keeping

up with their syncronization… well it's just plain BAD!

Any thoughts on how to implement either solution?

Is it possible I have overlooked simpler alternatives?

[peterlaursen]

I am not sure I understand every detail (or rather : I am sure I do not ..)

the terms MASTER and SLAVE do not make sense with SQLyog synchronisation. Synchronisation is not replication! We use the terms SOURCE and TARGET – NOT MASTER and SLAVE.

The only 'conflict rule' currently supported by SQLyog/SJA is 'source overwrites target'.

[tjmcd]

peterlaursen wrote on Jul 6 2007, 07:47 PM:

I am not sure I understand every detail (or rather : I am sure I do not ..)

the terms MASTER and SLAVE do not make sense with SQLyog synchronization. Synchronization is not replication! We use the terms SOURCE and TARGET – NOT MASTER and SLAVE.

The only 'conflict rule' currently supported by SQLyog/SJA is 'source overwrites target'.

Sorry,

MASTER and SLAVE are arbitrary designations — for explanatory purposes only — denoting … I'll try again …

• Our primary db hosted (remotely) at ourdomain.com [MASTER],
• AND numerous mirror dbs hosted locally on numerous tablet pc's in the field (SLAVES)

with intermittent connectivity which precludes them from working from primary or MASTER exclusively

so necessitating frequent synchronization

And yes, that is my understanding 'source overwrites target' which is the root of the problem.

Would be nice to be able to indicate SOURCE overwrites TARGET WHERE … else TARGET overwrites SOURCE

Otherwise, updated records on target WILL BE OVERWRITTEN by those that have NOT been updateed on source.

[tjmcd]

Or… might one run 2 two-way syncjobs …

One with MASTER as socurce AND something like

`soruce.date_modified`>`target.date_modified`

for each table in the job …

AND One with SLAVE as socurce AND something like

`soruce.date_modified`>`target.date_modified`

????? 🙄

[peterlaursen]

We are considering various other 'conflict resolving rules' – including one based on a 'most recent TIMESTAMP on update CURRENT_TIMESTAMP'. We are not able to do it overnight! It is complicated actually!

[tjmcd]

peterlaursen wrote on Jul 7 2007, 08:22 AM:

We are considering various other 'conflict resolving rules' – including one based on a 'most recent TIMESTAMP on update CURRENT_TIMESTAMP'. We are not able to do it overnight! It is complicated actually!

Okay, Great News.

Meanwhile, you avoid my inquiry regarding a work-around.

Please,

any suggestions on how we might make use of http-tunneling (SQLyog's or

third party php compatible ) to facilitate automation of our little

work-around would be greatly appreciated.

Surely I'm not the only one to have suggested such a work around,

which is quite viable especially given a more flexible/workable tunneling

solution which would eliminate the need for AN ADDITIONAL and otherwise

SUPERFLUOUS DATABASE (as originally described).

Regards

TJ

[peterlaursen]

it I knew a workaround out of my head, I would have told of corse.

But I do not understand how this is related to HTTP-tunnel. I think the connection type (direct. SSH or HTTP) does not matter?

there have been lot of requests about improving DATA SYNC. Including several requests that we should implement a magician in SQLyog. Unfortunately MySQL AB provides no API for this ….

By saying so I did not mean to be 'nosy', but basically traditional SQL databases like MySQL are NOT designed as distributed databases!

An option you could consider is to connect to a (central) server using a connection from every tablet based on 3G Mobile Phone technology.

[Author]

Posts