4

I'm actually developing a synchronization tool in vb.net, I have two database that have on each table records field GUID, this field help to have the same PK on both database. On each record there is also a field called lastUpdated, this field have a milliseconds value, so prevent two user to update the record in the same time. My question is, how I can compare the records of the same table from different db? For example:

ONLINE_DATABASE

TABLE_1

| ID |   GUID                             | NAME  |    LASTUPDATED              |
| 5  | 054ba092-b476-47ed-810b-32868cc95fb| John  | 06-01-2016 17:01:12.472438  |

CLIENT_DATABASE

TABLE_1

| ID |   GUID                             | NAME  |    LASTUPDATED              |
| 9  | 054ba092-b476-47ed-810b-32868cc95fb| Jack  | 06-01-2016 18:01:12.472438  |

How you can see I've update the record from client application, so I need to apply the same change to online database. Now I've a thousand records to check in about ten tables. So my question is, how I can made a system that do this? Actually I tough to read each row with a MySqlCommand reader but I also think that this procedure is slow... Suggest?

NOTICE THAT: the table have the same name in both db

  • If the uuid is the primary key, then the "id" column seems superfluous. Your timestamps are microseconds, not milliseconds, but this is only as useful as the accuracy of the respective system clocks, and contrary to your assertion, actually does nothing to prevent simultaneous -- or, more importantly -- conflicting updates. If the records on both systems have changed, why should the one changed first or last be automatically considered the "correct" one. What about deletions? If a row is missing from A, delete it from B? or copy B to A? – Michael - sqlbot Jan 7 '16 at 3:02
  • There are many ways to compare data, such as generating a cryptographic hash (in SQL) of all the concatenated column values and sending that, along with the pk for each row, across the wire to the far end; mismatched rows will have a mismatched hash, and much less data needs to traverse the wire this way, because then only rows with known or suspected mismatches need be transferred on their entirety... but this is an implementation detail you needn't handle, yet, until you solve some much larger problems you likely have not yet identified. This is no minor undertaking. – Michael - sqlbot Jan 7 '16 at 3:06
  • For the moment I need to push the data only from client to web so certain checks are usefullness. Anyway, I'm looking for a system that allow me in VB.NET simply to recognize the record to update, pushing it into web DB or remove it. Only this. – Dillinger Jan 7 '16 at 8:20
3

If the GUID is the absolute truth about what is supposed to match, then the GUID must be the PRIMARY KEY. Get rid of id.

If you want semi-automated "conflict resolution based on time", you want MySQL's NDB Cluster.

  • I already set alla pk as GUID, this is not a problem, now the problem is made the sync system in VB.net – Dillinger Jan 7 '16 at 8:20
  • 2
    That's code -- read everything from each machine and compare. Maybe the "sync" concept needs to be rethought? – Rick James Jan 7 '16 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.