Sja Fails To Sync Databases

[upeters]

Hello,

I have two MySQL databases (remote 4.0.27-log, local 4.1.10a-classic-nt) with almost the same database. I say “almost” because on the remote system timestamp fields are implemented as timestamp(14) while on the local system ther are plain timestamp. It is impossible to synchronize the databases with the script generated:

Code:

/* Alter table in Second database */
alter table `myferret`.`forums_auth`
change `dhora` `dhora` timestamp(14) NULL after `signature`;

/* Alter table in Second database */
alter table `myferret`.`leilao_bid`
change `lance` `lance` timestamp(14) NULL after `currentbid`,
change `criacao` `criacao` timestamp(14) NULL DEFAULT '00000000000000' after `lance`;

/* Alter table in Second database */
alter table `myferret`.`leilao_main`
change `recentbid` `recentbid` timestamp(14) NULL after `html`,
change `startdate` `startdate` timestamp(14) NULL DEFAULT '00000000000000' after `recentbid`,
change `enddate` `enddate` timestamp(14) NULL DEFAULT '00000000000000' after `startdate`;

Please note that the only fields that differ between the databases are the timestamps. When I execute the script locally, I get no error, but I keep having timestamp fields, not timestamp(14) fields. This is maybe because of the different platforms (remote is Linux, local is WinXP), but this is causing an error when I run SJA, forcing me to always have to run the script twice. The first time my records are erased, then at the following run they are put back again. This is very annoying…

I am attaching the output while syncing the same database several times with version 5.14 of SQLyog Enterprise.

Cheers,

Ulrich

[peterlaursen]

OK ..

1) Structure sync has nothing to do with the SJA. Structure sync is implemented in SQLyogEnt.exe itself and not sja.exe.

2) I do not understand the attachment. It looks like some binary format ??

3) I also do not think the platform matters. But could be TIMESTAMP implementations with MySQL 4.0 and 4.1 respectively

4 Please copy the 'create statements for the table' for the table on both databases. We will then easily be able to spot the issue. (be carefull to tell which statement belongs to which version)

5) In what direction are you sync'ing? 4.0 >> 4.1 or 4.1 >> 4.0?

I think that if the length of a TIMESTAMP then TIMESTAMP(14) is default. So when “but I keep having timestamp fields, not timestamp(14) fields” I am not sure that it really is a problem ?! A TIMESTAMP(14) is a YYYYMMDDHHMMSS timestamp just like a TIMESTAMP with-no-length-specification is (with MySQL 5.x the TIMESTAMP specification is different- but no issue here!). So what is the PROBLEM, except for differing MySQL terminilogy across versions ?? What does not work?

“forcing me to always have to run the script twice. The first time my records are erased, then at the following run they are put back again” .. Please do 4) and we will check this!

[upeters]

peterlaursen wrote on Jun 13 2006, 05:41 PM:

1) Structure sync has nothing to do with the SJA. Structure sync is implemented in SQLyogEnt.exe itself and not sja.exe.

Yes, I am aware of that. The fact is that I am unable to sync two databases, where the local database (on 4.1) was created using a dump of the remote database using (4.0), and both structures should be the same. However, SJA is clearly not able to sync the databases in one single pass – first it deletes all records with timestamp columns, and on a second pass SJA populates the same records again. Only to have them dropped again on the third pass and so on. Somehow SJA “believes” that the structures differ, and it is not able to perform a sync in a single pass.

peterlaursen wrote on Jun 13 2006, 05:41 PM:

2) I do not understand the attachment. It looks like some binary format ??

Sure. The forum showed me a message that it is not allowed to upload the text file, so I zipped it (as the name shows) and it became binary. After unzipping you'll get the original sja.log text file to help you understand what is happening.

peterlaursen wrote on Jun 13 2006, 05:41 PM:

5) In what direction are you sync'ing? 4.0 >> 4.1 or 4.1 >> 4.0?

Remote (4.0) to local (4.1).

peterlaursen wrote on Jun 13 2006, 05:41 PM:

I think that if the length of a TIMESTAMP then TIMESTAMP(14) is default. So when “but I keep having timestamp fields, not timestamp(14) fields” I am not sure that it really is a problem ?! A TIMESTAMP(14) is a YYYYMMDDHHMMSS timestamp just like a TIMESTAMP with-no-length-specification is (with MySQL 5.x the TIMESTAMP specification is different- but no issue here!). So what is the PROBLEM, except for differing MySQL terminilogy across versions ?? What does not work?

What does not work is the synchronization. Why should I have to run SJA twice to actually HAVE a valid backup? As you said, if both fields are the same size, why doesn't SJA understand that and just updates the records that changed?

peterlaursen wrote on Jun 13 2006, 05:41 PM:

“forcing me to always have to run the script twice. The first time my records are erased, then at the following run they are put back again” .. Please do 4) and we will check this!

I am sending you a new zip file with both structures in it.

Cheers,

Ulrich

[peterlaursen]

OK .. thanks

1) pretty stange that the .zip extension gets lost when downloading your files. But after renaming manually I can unzip and read the files.

2) sja.log does not tell anything about the STRUCTURE SYNC tool! I still do not understand what that file should tell? I see that all rows get INSERTED on target. Was that the point?

3) But there is an issue with sync'ing of TIMESTAMPs form 4.0. to 4.1. On MySQL it is a TIMESTAMP(14) and on MySQL 4.1 it is a TIMESTAMP only. It is undoubtly a server issue, but the SYNC tool should 'spot' this and see that there is nothing to sync.

… Now it is late here. We will look into it tomorrow!

[upeters]

peterlaursen wrote on Jun 13 2006, 07:06 PM:

2) sja.log does not tell anything about the STRUCTURE SYNC tool! I still do not understand what that file should tell? I see that all rows get INSERTED on target. Was that the point?

3) But there is an issue with sync'ing of TIMESTAMPs form 4.0. to 4.1. On MySQL it is a TIMESTAMP(14) and on MySQL 4.1 it is a TIMESTAMP only. It is undoubtly a server issue, but the SYNC tool should 'spot' this and see that there is nothing to sync.

I am very sorry, but you write as if I am providing useless info, and that it would have been easier to just tell you that SJA does not work. 🙁

Again, step by step, to help you understand what is happening. It is not just a difficulty in sync'ing timestamps, as ALL RECORDS are deleted locally during the sync, while trying to backup the remote database. I would say that this is a serious flaw of SJA.

1) What you see in the SJA.LOG file is the output of the synchronization tool, of course. If you look at the file carefully, you can see that I execute the same sync job four times in a row. You can see that three tables have all their contents DELETED during the first sync (15:37).

2) Now observe the second run of the same job (15:42), still in the same log. All three tables are again fed with the data from the remote server. I have now a valid backup of the remote server.

3) I run the sync job again for a third time at 15:48. The three tables are again emptied and I have no backup.

4) Fourth and last run of the same script at 15:53 in the log file. The three tables are again saved with the data. Were you able to follow?

5) Ok, now to the analysis. Why does this happen, and always to the same three tables, and never to the other tables in the same database? To see what was happening I ran the structure sync tool between the servers.

6) You say that this info is not related to the problem but I beg to disagree. The comparison tool shows that these three tables which are always emptied by SJA feature different structures, and that the supposed differences are the timestamps. I gave you the output of the structure comparison. It can't be a coincidence that on two different databases I am unable to sync a total of four tables, and all of these four tables are those which feature timestamps.

7) In a need to fix the problem, I tried to apply the structure synchronization script to my local database. Of course, the timestamp field does not change from timestamp to timestamp(14), no matter what I do. And keep in mind that one database was created from a dump of the first, so they should be identical.

8) Unable to fix the “difference” between the databases, I am unable to sync them with one single run of SJA, and, as you already quoted me, forces me to run the script twice. And this is my problem here. I hope now you see my point.

Ulrich

[Ritesh]

Changing from TIMESTAMP(14) to TIMESTAMP is due to the MySQL server which silently changes TIMESTAMP with any range to just TIMESTAMP in v4.1. This is a known issue.

SJA takes TIMESTAMP into consideration but your case looks strange.

If the data is not confidential then can you send me sample data so that we can reproduce the error at our end?

We created the table with sample data at our end but could not reproduce the problem.

[peterlaursen]

I am sorry .. maybe it was too late yesterday! But I was confused by you talking about SJA and providing an output from the structure sync tool.

So I think we have two problems

1) DATA SYNC deletes and recreates data every second time (as the log file tells)

2) STRUCTURE SYNC finds differences. After running the sync script STRUCTURE SYNC keep finding the same diferences forever …

agreed?

about 1)

Please read this about DATA SYNC: http://webyog.com/faq/11_68_en.html

Note that data sync was not originally designed to work across versions. The 'workaround' described in the FAQ will not work with TIMESTAMPS. But of course it should not do like this – first deleting and next recreating data. It should instead tell that the databases cannot be sync'ed, if it cannot. (however converting to strings .. syncing .. converting to timestamp again should work)

about 2)

Structure sync is designed to work across versions.

I think MySQL 4.1 only operates TIMESTAMPS in YYYYMMDDHHMMSS -format. That is the same as a TIMESTAMP(14) of MySQL 4.0. The tool should see that. Specifying a length of a TIMESTAMP with 4.1 has no effect. It is simply ignored. So it does no harm. But confusing that the tools keeps telling that structures are not in sync, when they are!

Ritesh/Manoj/Sarat .. I am uncertain who does what from here .. ??

I think I'll create a simpler test case?

[peterlaursen]

The simple test case comes here:

Attached a small MySQL 4.0 and a similar 4.1 example.

Structure sync:

==========

It is very co0mplicated actually due to the different meaning of NULL/NOT NULL with the different versions!

Data sync:

=======

verified!

running DATA sync to times yields:

SQLyog Job Agent Version 5.13

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Wed Jun 14 10:28:24 2006

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`t1` 1 1 0 0 1

Total time taken – 0 sec(s)

SQLyog Job Agent Version 5.13

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Wed Jun 14 10:29:18 2006

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`t1` 1 0 1 0 0

Total time taken – 0 sec(s)

[Ritesh]

Thank Peter.

Regarding structure sync, it has been reproduced and Sarat will be working on it in the next version. We basically don't change for TIMESTAMP length in Structure Sync. The problem lies in the issue that between MySQL 4.0 and 4.1, the implementation of TIMESTAMP has changed so we need to take care of the multiple MySQL versions.

Regarding data sync, we are working on it. Expect a reply by evening.

[upeters]

peterlaursen wrote on Jun 14 2006, 05:25 AM:

So I think we have two problems

1) DATA SYNC deletes and recreates data every second time (as the log file tells)

2) STRUCTURE SYNC finds differences. After running the sync script STRUCTURE SYNC keep finding the same diferences forever …

agreed?

Hello Peter,

yes, I agree. Actually, the structure sync problem is not that important for me, as long as I get valid backups after the data sync. 😛

I know that I am using two different versions of MySQL (4.0 and 4.1), but as one does import a data dump from the other version without complains, I was expecting that SQLyog would allow me to work with them without problems as well.

So for now it seems that I have to rely on full backups instead of just syncing my servers. I hope that a fix for this glitch is not too difficult.

Cheers,

Ulrich

[peterlaursen]

@ritesh – BTW:

As far as remember it was the same symptoms we had (deletion .. recreation) before we fixed the issue with the INTEGER type being too short to hold changes in the SECONDS-range. Wasn't it?

[Ritesh]

I think I understood the problem. It happens because we have a TIMESTAMP column with ON UPDATE CURRENT_TIMESTAMP in the table.

Now two timestamp will never be same for two tables and thus checksum will fail. As checksums fail to be similar, it deletes the ROWS from the target in the second parse. In this case all the rows are deleted.

To get around this issue as of now, dont select the TIMESTAMP column in checksum selection for the effected table.

We are working on a more elegant way to solve this issue but it will take some time.

[peterlaursen]

No Ritesh!

Check my 'test case'. There is no “on update”. (There was not in upeters' example either).

4.0

==

create database if not exists `test`;

USE `test`;

/*Table structure for table `t1` */

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (

`id` bigint(20) NOT NULL auto_increment,

`t` varchar(20) default NULL,

`ts` timestamp(14) NOT NULL,

PRIMARY KEY (`id`)

) TYPE=MyISAM;

/*Data for the table `t1` */

insert into `t1` (`id`,`t`,`ts`) values (1,'a',20060101010101);

4.1

==

SET NAMES utf8;

SET SQL_MODE='';

create database if not exists `test`;

USE `test`;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

/*Table structure for table `t1` */

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (

`id` bigint(20) NOT NULL auto_increment,

`t` varchar(20) default NULL,

`ts` timestamp NULL default NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `t1` */

insert into `t1` (`id`,`t`,`ts`) values (1,'b','2006-01-01 01:01:01');

SET SQL_MODE=@OLD_SQL_MODE;

===================================

4.0 is 'NOT NULL' 4.1 is 'NULL default NULL'

but there is no NULL in the data!

I wonder if the 20060101010101 (number) and '2006-01-01 01:01:01' (formatted string) fail to compare?

That could be why it deletes in the first run.

In the second run target is empty and nothing can fail to compare, as there is NOTHING to compare !???

[Author]

Posts