Structure Syncronization dropping keys

[marty]

When using the structure syncronization tool, keys that exist in the master table but DON'T exist in the target table are not getting copied. In fact, the sync tool is issuing a DROP KEY command instead of an ADD KEY command.

For example, I have a table `Opportunity` that I am copying between two databases, crm and mp15

#############################

##### INDEXES FOR MASTER (crm) ####

#############################

Index Information For – crm.Opportunity

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

---

---

---

---

---

---

---

---

---

---

---

---

Opportunity 0 PRIMARY 1 Id A 8 (NULL) (NULL) BTREE

Opportunity 1 ContactId 1 ContactID A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 CreatedBy 1 CreatedBy A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 DateCreated 1 DateCreated A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 DateInStage 1 DateInStage A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 DateInStatus 1 DateInStatus A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 Leadsource 1 Leadsource A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 Objection 1 Objection A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 OppTitle 1 OpportunityTitle A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 PercentChance 1 PercentChance A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 StageID 1 StageID A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 StatusID 1 StatusID A (NULL) (NULL) (NULL) YES BTREE

Opportunity 1 UserID 1 UserID A (NULL) (NULL) (NULL) YES BTREE

#############################

##### INDEXES FOR TARGET (mp15) ###

#############################

Index Information For – mp15.Opportunity

---

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

---

---

---

---

---

---

---

---

---

---

---

---

Opportunity 0 PRIMARY 1 Id A 15 (NULL) (NULL) BTREE

---

Notice that the master database has 10 or so indexes that the target database does not have, so when syncing from the first to the second, you should get a sql statement with a bunch of ADD KEY statements. Instead, here is what you get:

---

#############################

##### SQL GENERATED BY SQLYOG ####

#############################

/* Alter table in Second database */

alter table `mp15`.`Opportunity`,

drop key `ContactId`,

drop key `CreatedBy`,

drop key `DateCreated`,

drop key `DateInStage`,

drop key `DateInStatus`,

drop key `Leadsource`,

drop key `Objection`,

drop key `OppTitle`,

drop key `PercentChance`,

drop key `StageID`,

drop key `StatusID`,

drop key `UserID`;

It is attempting to drop all the keys, and of course it will generate an error because the keys don't exist at all! What complicates matters more is that it doesn't do this ALL the time. It look like it's about half the time that it 'switches'.

We purchased a SQLYog license mainly for this function. I need to know either how to work around it, I need to get it fixed, or we need to find a different piece of software.

Thanks

Eric Martineau

[email protected]

[Ritesh]

We are trying to duplicate the scenario. We will get back to you ASAP.

[Ritesh]

I think you are selecting the wrong “Sync Clipboard…” button.

If you click on the right one i.e. the first database, then scripts are generated to bring the second database in sync with the first and vice versa.

What you should do is –

select crn.opportunity as the first database.

select mp15.opportunity as the second database.

click on “Sync Clipboard… >>” on the left side i.e. the first database.

Quote:

What complicates matters more is that it doesn't do this ALL the time. It look like it's about half the time that it 'switches'.

This is because you are selecting the wrong context.

HTH

[marty]

Thanks for your quick reply:

Please review my original post. I am positive that I am clicking the right Sync Clipboard >> buttons.

You will notice that the sql that was generated is being run on mp15:

/* Alter table in Second database */

alter table `mp15`.`Opportunity`,

drop key `ContactId`,

drop key `CreatedBy`,

drop key `DateCreated`,

drop key `DateInStage`,

drop key `DateInStatus`,

drop key `Leadsource`,

drop key `Objection`,

drop key `OppTitle`,

drop key `PercentChance`,

drop key `StageID`,

drop key `StatusID`,

drop key `UserID`;

mp15 IS the second database, the one WITHOUT all the keys. If mp15 doesn't have keys that the first table does, these commands should be add key commands

If you need, I can send you my mysql data files for testing.

Thanks again,

Eric

[marty]

OK, here are the sql scripts

MySQL 4.0.12-nt

I don't know if it's going to let me post both scripts here. The first one will be the master (the one I am copying the structure FROM)

[marty]

Here is the slave (The one I am copying structure TO)

[Ritesh]

Thanks I have downloaded them.

Can you mail me a screenshot of the Sync dialog in SQLyog ( part showing the table that is giving error will be helpful )?

Just mark out the “Sync Clipboard…” button that you are clicking to generate the script.

[marty]

Screenshot 1

Here is my object browser. Take note that test2 DOES NOT have a CompanyName index

[marty]

Screenshot 2

Note, that the sync tool suddenly thinks that Opportunity table on test2 has a CompanyName index, while test1 does not. This is incorrect. However, there are other tables within this same sync that are pulling up just fine

[marty]

Now, when I run this sync, I get an error!

[Ritesh]

Thank you!!

Give me some time to work on it 😀

[Ritesh]

Greetings

Registered users get “high priority” fixes to critical bugs!

The bug has been fixed in SQLyog 3.62 RC and is available at http://www.webyog.com/sqlyog/SQLyog362.exe

Pls. check it out and confirm.

[Author]

Posts