what is the timestamp problem?

[zzapper]

Hi,

I have to synchonise data beteen two versions of mySql

from

4.1.10a-nt

to

4.0.22-standard

However the Database Synchronisation Wizard will always bomb out on “incompatible timestamps” because of a MySQL specification change between the above two versions.

Now I ask your forgiveness because this is an “old chestnut” in this NG, but could anyone care to summarise

the timestamp problem and whether there is a work-around

[Ritesh]

SQLyog uses a checksum based algorithm to compare and sync two databases. In the algorithm, the MySQL function concat_ws() plays a very important part. The function is known to return different result across multiple MySQL versions.

To check for compatibility just execute the following query:

Code:

select md5(concat_ws('A','B',''))

You should get similar results for both your source and target server.

[zzapper]

Ritesh,

I've solved the problem by temporarily deleting the timestamp in both databases. I guess I could also change the column type to say char, do my sync and then change back ????!!!???.

Any other tricks??

[Ritesh]

The problem is how MySQL handles TIMESTAMP. There is a significant difference between versions prior to v4.1 and as of v4.1.

You can get more details at http://dev.mysql.com/doc/mysql/en/datetime.html

We plan to modify SJA to work around the incompatibility in one of the future versions of SJA.

Quote:

guess I could also change the column type to say char, do my sync and then change back

Its a valid workaround as of now. 😉

[Author]

Posts