ID #1053

Can I synchronize across different MySQL versions?


The basic working of the Synchronization tool is that it generates a string using the concat_ws(s) function on the columns selected with the <columns> -option. Advanced checksum algorithms are the used on the strings generated. This is how SJA 'finds out' which rows must be INSERTed, UPDATEd and DELETEd to bring the databases in sync.

This means that the concatenation of columns and the checksum's generation is performed on the server side and any further calculation is done on the client side.

Unfortunately the implementation of the concat_ws() function has changed with various MySQL versions. If you try to sync across versions where this makes sync with the SJA impossible, SJA reports an error stating that concat_ws is used and this might not work with different versions of MySQL - and aborts.

We have no plans to implement the concatenation on the client side (because that would completely destroy the efficiency of the tool) and thus the sync tool cannot sync MySQL version 3.23.x with any higher version, However we have implemented a 'workaround' with SQLyog version 5.1 (a 'workaround' that simply is, that the SJA sync tool does not check for NULL-constraints) that makes it possible to sync across all 4.x.x and 5.x.x version, provided that:

1) There are no DATATYPE incompabilities affecting the data involved across the actual MySQL versions such as for instance implementations of date- and time- datatypes. That would typically affect sync'ing from 4.0 and higher versions where the storage format of TIMESTAMPS have changed. Here it is a workaround to convert data to strings before sync and back after sync. It can be done with <notify> -jobs running in the same batch as the <sync>-job. Also syncing BIT data types across 4.x and 5.x versions is not possible as 4.0 uses a TINYINT internally for BITs whereas 5.x has implemented a true binary representation for BITs. You can convert the 5.x data to TINYINT before sync and back afterwards or - simpler - use a TINYINT with 5.x.

2) The 'create statement for the table' is not different across the versions if the target tables are non-existent at sync time. In practice that simply means that if you want to sync to MySQL 4.0 from a higher version you must create the table's structures in advance at the target before sync.

Also Note that the SJA Import External Data Tool works across all MySQL versions. With the use of the trigger and sql-where -options of this tool you may be able to perform a sync using ODBC. It depends on the structure of data and the data itself.


Tags: -

Related entries:

You can comment this FAQ