Structure Sync Tool Not Dropping All Fields

[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