[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

[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

[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

[upeters]

peterlaursen wrote on May 25 2006, 05:42 AM:

@ritesh

both upeters and I tested with a remote 4.0.26 Linux server without tunnel.

As I wrote the data are available at surftown.dk ..

Server here is '4.0.26-standard-log' on GNU/Linux

It does not happen with tunnelling …

Hello,

I was using HTTP/PHP tunneling on the source/remote server (4.0.26) and a direct connection on the destination/local server (4.1.19) when it first happened. I still can reproduce the error under these conditions. I get the exact same result when I use a direct connection to the remote server, so it seems that it happens always, with or without tunnel.

I have setup the readonly user on the remote database, in case you want to run a test with it I could send you the connection details.

Ulrich

[upeters]

peterlaursen wrote on May 24 2006, 11:56 AM:

Issue confirmed:

4.0.26 remote and 4.1.19 local, no tunnelling!

Great, so it seems that you got the same symptom now. Thank you for your help! Hopefully this will help to nail down this error… without having to change databases. 🙂

Cheers,

Ulrich

[upeters]

peterlaursen wrote on May 24 2006, 11:25 AM:

So what do we do from here ??

Hi,

it might be possible that this error shows only if using different versions of the MySQL database, like in my situation. The remote/source is 4.0.26 and my local servers are 4.1.19 and 4.1.10a-classic-nt (got the same error on both, using different computers)… I could set up a temporary readonly user for you at the remote, and you could see if you get the error (or anything else wrong) then when trying to sync with the local database. May I contact you per e-mail?

Ulrich

[upeters]

peterlaursen wrote on May 24 2006, 10:32 AM:

To me it looks as something went wrong with that upload.

I get a weird file down!

Are you sure that the upload finished?

I downloaded it from the forum, and got a zip file, 16997 bytes, containing two files, “ferrets-remote.sql” and “ferrets-local.sql”, both about 40 kB. It seems to me that the upload worked correctly, but if you still are unable to get the two files, I placed them here:

http://www.ferrets.com.br/files/databases.zip

http://www.ferrets.com.br/files/ferrets-remote.sql

http://www.ferrets.com.br/files/ferrets-local.sql

[upeters]

sarat wrote on May 24 2006, 02:17 AM:

I am not able to reproduce this error

can you please paste here those source and destination table structures

Hello,

I am attaching both databases to this post. The “remote” database is the source, the “local” database should be the destination.

Ulrich

[upeters]

Hi again,

it seems that I wrote too early. Now there is a new error in v5.13 beta 2 (at least in Enterprise not-trial):

Here is the sync file created with stable version 5.12:

/* Alter table in Second database */

alter table `ferrets`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_incrementafter `descr`,

add PRIMARY KEY (`id`);

/* Alter table in Second database */

alter table `ferrets`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_incrementfirst,

change `dhora` `dhora` timestamp(14) NULL after `dolar`,

add PRIMARY KEY (`id`);

Here is the sync file made with version 5.13 beta 2, downloaded a few minutes ago:

/* Alter table in Second database */

alter table `ferrets`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_increment after `descr`,

add PRIMARY KEY (`id`),

/* Alter table in Second database */

alter table `ferrets`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_increment first,

change `dhora` `dhora` timestamp(14) NULL after `dolar`,

add PRIMARY KEY (`id`),

Please note that the missing space after “auto_increment” is gone, but now the statements are finished with a comma, where it should be a semicolon. Can you reproduce this error?

Ulrich

[upeters]

peterlaursen wrote on May 23 2006, 06:13 PM:

I recommend 5.13 beta2. Compared to 5.12 it is only bugfixes.

Hello,

just to make sure, I performed a new structure comparison, and saved the file with version 5.12. The same error I reported before was again present in the sync file. Then I installed the latest beta, and ran the same comparison again and saved in a new sync file. The latter sync file looks fine, so it seems that this error will be already corrected in the next stable version. Thank you for your assistance.

Cheers,

Ulrich

[upeters]

peterlaursen wrote on May 23 2006, 05:37 PM:

Are you sure you use the latest version?

With 5.13 beta2 I get …

Hello,

I am using the latest non-beta version (5.12), I purchased a license for the Enterprise less than a week ago. Is this beta stable and can be installed over the current version, or will I likely break something? What do you reccomend?

Ulrich

[Author]

Posts