Data Synchronisation Script?

[Victoria1971]

Hi,

Just wanted to say that so far this application is great, and is extremely useful. When you use the Database Synchronization tool, though, to synchronize data from one table to another, will it create the actual MySQL commands used to perform this whole operation and save it to a script for you to look at?

If anyone knows the answer to this question, or knows of a way to generate this kind of script through sqlyog, I just want to say thanks in advance for letting us know.

Just curious 🙂

in Victoria, BC

[peterlaursen]

It does not genereate a complete script for the sync job.

It compares a  large number of rows on both hosts using checksum algoritms.  If there is a difference between the two, the beforementioned routine is executed recursively and the individual rows that need to be INSERTed, DELETEd or UPDATED are located.  Next the operations are performed on the rows where it is required, and everything starts over with the next rows until the end of that table has been reached.

Did that answer your question?

[Alistair Moffatt]

Peter

Any reason why script generation could not be added as a feature?

Alistair

[peterlaursen]

well ..

of course all SQL commands could be written to a file instead of being executed at once.  However it could then easily occur that some commands would not run (or worse: create unwanted results) because the database(s) had changed between the calculation/script building and the execution time.

The SQL commands generated are 'primitive' INSERTS, UPDATES and DELETES only.

Do you request such feature?  and why?

[Alistair Moffatt]

Your reply is valid where you are talking about live databases, but what about test environments.

The scripting ability would be really useful in test environments, as it would allow the easy creation of test databases without the need to dump out the complete database every time.

For example, I could have a database with base level data and then a series of scripts to bring the database up to different known states.

Does this make sense?

Alistair

[peterlaursen]

yes .. it does!

We plan a bigger 'overhaul' of the data sync once we get finished the new features that we started for the 6.0 tree.

So this we will consider too.  Actually a similar request has been raised before regarding Migration/ODBC import.

[frankf]

Regarding your comment below, how reliable are your checksum algorithms?

Our database has many updates where the size stays the same (i.e. toggle options, or switching values that are the same length)

For exmaple, if you are only checking if the length of a value changed… changing “positive” to “negative” or 0 to 1 still has the same number of characters, and these are important changes that would be missed.

Would it be something like md5?

'peterlaursen' wrote:

It does not genereate a complete script for the sync job.

It compares a  large number of rows on both hosts using checksum algoritms.  If there is a difference between the two, the beforementioned routine is executed recursively and the individual rows that need to be INSERTed, DELETEd or UPDATED are located.  Next the operations are performed on the rows where it is required, and everything starts over with the next rows until the end of that table has been reached.

Did that answer your question?

[peterlaursen]

The checkssum algorithm is basically an MD5() on a string concat'ed from all the columns used for calculation of checksums.

We had no reports of failure with this till now, but there is a microscopic chance of finding two rows identical that are not what would then mean failure to UPDATE. There is no chance of INSERT and DELETE that should not happen does (as long as tables have a primary key at least),. checksums are only used for UPDATES, not INSERTS adn DELETES.

See

SELECT MD5('aaaa'); — returns '74b87337454200d4d33f80c4663dc5e5'

SELECT LENGTH(MD5('aaaa')); — returns 32

It is 32 hex digits (0-f) what means that an md5 checksum may hold 16^32 distint values.

It equals 340282366920938463463374607431768211456 if you you want it in that format. It is a huge number exceeding what the human brain can understand.

With such large 'room of outcome' from the calculation it is very very unlikely that the checksum will collide even if you have billions or more of rows. Decisions on life and death are taken on much much lesser secure basis every day (in medicine, aviation, nuclear power production etc.).

Also note that a UID/GUID (in the format “2e7d53db-9bd4-4673-a0ff-937e172d0c5a') is used a lot for unique identifiication (and it has also 16^32 different possible outcomes). It is in the internals of most Operating systems for instance.

[Author]

Posts