Structure Sync Tool Not Dropping All Fields

[GreenAlien]

I'm currently trialling SQLyog Enterprise 4.1 and I have an issue with the Structure Synchronization Tool.

I have made several changes to my local mysql database, among which includes dropping fields from several tables.

When I run the tool, it seems to realise that the fields need dropping, but the SQL it puts on the clipboard only includes the SQL to drop the first field that needs dropping. It seems to of skipped the rest!

I can clearly see this in the SQL that the tool spits out, and after applying that sql to the remote database I can confirm that only the first field that needed dropping was actually dropped.

In my case there are a total of five fields across four tables (one table has two that needs dropping), and each time I run the sync tool and apply it, the fields from one table only are dropped. So I end up running the tool (and applying the sql to the remote database) four times before the structures are in sync.

By the way, I did check that all the fields that needed to be dropped were ticked in the sync tool (they were by default). The fields that needed to be dropped were all shown in grey. So the sync tool knew they all needed dropping, but it just didnt reflect that in the SQL it put on the clipboard for me.

Eg, second time around, the SQL it generated to sync up looked like this:

---

/* Alter table in Second database */

alter table `fv`.`fv_misc`,

drop column `idbb_desc_misc`;

/* Alter table in Second database */

alter table `fv`.`fv_reg`;

/* Alter table in Second database */

alter table `fv`.`fv_vil`;

---

As you can see, the drop clause is missing from the second and third “alter” commands.

If I apply that to the remote database and run the sync tool again, the SQL looks like this:

---

/* Alter table in Second database */

alter table `fv`.`fv_reg`,

drop column `idbb_intro_reg`,

drop column `idbb_info_reg`;

/* Alter table in Second database */

alter table `fv`.`fv_vil`;

---

And if I apply that to the remote database and run the sync tool again, the SQL looks like this:

—-

/* Alter table in Second database */

alter table `fv`.`fv_vil`,

drop column `idbb_info_vil`;

—-

I apply that to the remote database and rerun the sync tool and it now shows the two databases are in sync. So it got there eventually.

Unless I'm doing something crazy (always possible!) I'd say this is a bug, and self contained within sqlyog because of the fact that all the relevant fields are shown in grey but dont end up in the SQL.

Rgds, Ant

[peterlaursen]

Quote:

and each time I run the sync tool and apply it, the fields from one table only are dropped

You were here too, weren't you ?

http://www.webyog.com/forums/index.php?sho…c=1622&hl=arrow

here we agreed that you should recognize the difference between “execute query” and “execute all queries”. Queries are seperated with “;”. F5 only executes ONE.”execute all queries” is available as the double green arrow icon or from the menu.

.. but now you write that it is the SQL generated that is buggy.

Can you paste in the table definitions (no data needed)?

[GreenAlien]

peterlaursen wrote on Aug 26 2005, 12:03 AM:

You were here too, weren't you ?

http://www.webyog.com/forums/index.php?sho…c=1622&hl=arrow

here we agreed that you should recognize the difference between “execute query” and “execute all queries”. Queries are seperated with “;”.  F5 only executes ONE.”execute all queries” is available as the double green arrow icon or from the menu.

.. but now you write that it is the SQL generated that is buggy. 

Can you paste in the table definitions (no data needed)?

[post=”6972″]<{POST_SNAPBACK}>[/post]

That's correct, but obviously I now know about the “execute all queries” option and I was using that on this occasion. It does indeed sound similar, but this is a completely different issue this time around. I since noticed someone else on this forum had exactly the same issue too FWIW. I can email/pm you the database (3k x 2) but would prefer not to post on here.

Thanks, Ant

[peterlaursen]

you will only need to export the structure of both DB.

An empty DB is OK. No data is needed for this!

Just copy from the OBJECTS pane like

CREATE TABLE `whertogo` (

`Id` int(10) unsigned NOT NULL,

`goname` varchar(50) collate latin1_danish_ci default NULL,

`towncity` varchar(50) collate latin1_danish_ci default NULL,

`type` varchar(50) collate latin1_danish_ci default NULL,

PRIMARY KEY (`Id`),

KEY `citieswhertogo` (`towncity`),

KEY `townix` (`towncity`),

CONSTRAINT `whertogo_ibfk_1` FOREIGN KEY (`towncity`) REFERENCES `cities` (`town_city`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci

[GreenAlien]

I created a test copy of the database on both local and remote servers and tried it out again and was able to reproduce the problem. I've put a sql dump of both databases here.

Rgds, Ant

edit:

steps to reproduce: Download the zip file (contains two files). apply old.sql to remote mysql server and new.sql to local mysql server. Run “Structure Synchronisation Tool”. Select the local database on the left-hand side, and select the remote database on the right-hand side. Click “Compare Now” (I use HTTP Tunneling so this step takes a few seconds). Observe that all four tables are automatically ticked. In the tree on the left-hand side, expand the nodes and observe that each table has one or two fields shown in grey (these are the fields to be dropped from the tables). Click “Sync (Clipboard)”, and paste into your favourite text editor. Observe that only the first “alter table” includes the “drop” clause!

[peterlaursen]

yes …

the “insert way” it is OK

/* Alter table in First database */

alter table `frogtest`.`fv_areas`,

add column `idbb_intro_area` int(11) NULL after `idreg_area`;

/* Alter table in First database */

alter table `frogtest`.`fv_miscinfo`,

add column `idbb_desc_misc` int(11) NULL DEFAULT '0' after `name_misc`;

/* Alter table in First database */

alter table `frogtest`.`fv_regions`,

add column `idbb_intro_reg` int(11) NULL after `name_reg`,

add column `idbb_info_reg` int(11) NULL after `idbb_intro_reg`;

/* Alter table in First database */

alter table `frogtest`.`fv_villas`,

add column `idbb_info_vil` int(11) NULL after `sleeps_total_vil`;

but not the “drop way”

/* Alter table in Second database */

alter table `cazantco_frogtest`.`fv_areas`,

drop column `idbb_intro_area`;

/* Alter table in Second database */

alter table `cazantco_frogtest`.`fv_miscinfo`;

/* Alter table in Second database */

alter table `cazantco_frogtest`.`fv_regions`;

/* Alter table in Second database */

alter table `cazantco_frogtest`.`fv_villas`;

It does not make any difference whether indexes are chosen or not.

What could it be that leeds the tool astray here .. ?

[GreenAlien]

Thanks for trying that out and confirming there's a bug. Hopefully that's enough for the developers to work with. In the meantime I'll just get into the habit of running the sync tool again after I've done a sync to confirm nothing is left outstanding.

Rgds, Ant

[peterlaursen]

I just reproduced it with another example.

Seems that it will only DROP one column.

But it does not affect dropping of tables as this shows:

/* Alter table in Second database */

alter table `test2`.`corp_profile`,

drop column `Billing`;

/* Drop in Second database */

drop table `test2`.`indv_profile`;

/* Alter table in Second database */

alter table `test2`.`name`;

/* Alter table in Second database */

alter table `test2`.`name_address`;

(here there should have been 'drop column' statements in the last two ALTERs too)

hmmm … it must be a recent “mal-tailoring” of the code.

A 'flag' that is not initialized/reset or something …

It has worked for me dozens of times earlier!

[peterlaursen]

I also tried a variation with your data: I deleted some rows so that there should be more 'add column' and 'drop column' statements boths ways.

Still there is only one drop statement

/* Alter table in Second database */

alter table `frogtest`.`fv_areas`,

add column `idbb_intro_area` int(11) NULL after `name_area`,

drop column `idreg_area`;

/* Alter table in Second database */

alter table `frogtest`.`fv_miscinfo`,

add column `idbb_desc_misc` int(11) NULL DEFAULT '0' after `id_misc`;

/* Alter table in Second database */

alter table `frogtest`.`fv_regions`,

add column `idbb_intro_reg` int(11) NULL after `name_reg`,

add column `idbb_info_reg` int(11) NULL after `idbb_intro_reg`;

/* Alter table in Second database */

alter table `frogtest`.`fv_villas`,

add column `idbb_info_vil` int(11) NULL after `sleeps_perm_vil`;

/* Alter table in First database */

alter table `cazantco_frogtest`.`fv_areas`,

add column `idreg_area` int(11) NULL DEFAULT '0' after `name_area`,

drop column `idbb_intro_area`;

/* Alter table in First database */

alter table `cazantco_frogtest`.`fv_miscinfo`,

add column `name_misc` varchar(25) NULL after `id_misc`;

/* Alter table in First database */

alter table `cazantco_frogtest`.`fv_regions`;

/* Alter table in First database */

alter table `cazantco_frogtest`.`fv_villas`,

add column `sleeps_total_vil` int(11) NULL DEFAULT '0' after `sleeps_perm_vil`;

[Ritesh]

Thanks for the report.

I will check it up from my office today. The bug will be fixed in v4.2 if there are any.

[Ritesh]

Bug fixed in 4.2 BETA 6 development tree.

[Author]

Posts