MySQL Bugs: #43485: workbench synchronization problems

Bug #43485	workbench synchronization problems
Submitted:	8 Mar 2009 15:33	Modified:	22 Jun 2009 13:30
Reporter:	Roland Kaber	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.0.30	OS:	Windows
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	CHECKED, synchronization

Description:
Hi

On synchronizing a database I regularly encounter the following problems.

I only want to synchronize a few tables, so I deselect all tables which are not required. Despite this the generated script will apply changes to all tables even though none were made to these tables in the model. In particular, all tables appear in the script and the table comment is selected each time in the ALTER TABLE statements. Moreover for a few tables a primary key is added despite its existence in the table. While synchronizing the table comments is annoying but does not cause any damage, the attempt to add the same primary key results in "error 1068 - multiple primary key defined" and the script stops execution. After manually editing the script it works fine.

Ideally I would like to see in the synchronisation script only the tables which I originally selected.

My database uses InnoDB and has some 240 tables.

How to repeat:
I dont't know since on two different executions of the synchronization, different tables are affected, but I hope this report is of some use to improve the synchronisation feature.

Many thanks for writing a bug report. But you are confusing me with version number and OS.

We don't have a 5.0 SE version for MAC OS X.

We offer SE just for Windows at the moment. We will offer soon SE also for Linux and Mac. Also there is none 5.0 version for Mac and Linux. We started here with 5.1.

So my question is:
Are you using 5.0 SE under Windows or are you using 5.1 OSS under Mac?

Sorry for the confusion caused by mentioning OS X. I am using windows XP running in a virtual machine, Parallels Desktop, installed in Mac OS X. So, to answer your question I am using 5.0 under windows.

I just made a test with 12 tables and it only synchronise the selected tables.

Please would you give me a step by step "how to repeat".

Here is what I have done:
- In the Database Synchronisation window I connect to the MySQL server and the schemata are retrieved.
- Next I select the schema to be synchronised and the schema objects are successfully retrieved.
- In the Select Objects page I unselect all views, routines and triggers. I then click on Detailed Selection in the Table Objects part.
- Next I put all tables on the "Exclusion Masks side"; I then place, say, 12 tables I want to synchronise on the "Objects To Process" side. 
- Next I run the validation successfully and I accede to the "SQL Diff Tree" page.
- I just want to synchronise the changes from the model to the database, so the arrows are correct. For most tables I read N/A on the "Target" side except for thoise I selected and a few mor I didn't select. The ALTER TABLE statemen ts for these tables will cause the error.
- On the "SQL Sync Script" there are many CREATE TABLE IF NOT EXISTS statements and at the end the ALTER TABLE statements in which I see the tables I selected among those I didn't. Here is one ALTER TABLE statement of a table languages:

ALTER TABLE `f2cdb_vttt_test`.`languages` ADD COLUMN `IsActivated` TINYINT(1) UNSIGNED NOT NULL COMMENT 'Has this language been activated?'  AFTER `LanguageCode` , ADD COLUMN `IsApplicationLanguage` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Is this a language used by the applications?'  AFTER `IsActivated` , CHANGE COLUMN `LanguageCode` `LanguageCode` CHAR(2) NOT NULL COMMENT 'The ISO 639-1 language code'  , COMMENT = 'Records language details such as ISO 639-1 codes and names.' , COLLATE = latin1_general_cs 
, ADD PRIMARY KEY (`LanguageCode`) , PACK_KEYS = 0 , ROW_FORMAT = DEFAULT ;

The ADD PRIMARY KEY clause will cause the error because the primary key already exists. Here is the corresponding CREATE TABLE statement:

CREATE TABLE `languages` (
  `LanguageCode` char(2) COLLATE latin1_general_cs NOT NULL COMMENT 'The ISO 639-1 language code',
  `IsActivated` tinyint(1) unsigned NOT NULL COMMENT 'Has this language been activated?',
  `IsApplicationLanguage` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Is this a language used by the applications?',
  PRIMARY KEY (`LanguageCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

- If I don't correct the ALTER TABLE statements manually, the script will cause error 1068.

What can I say more? The database hase some 250 tables some 50 views, a few triggers and a couple of procedures.

Thank you for your help. I hope this is of some use.

Dump file of my test datab ase

Attachment: bug43485.dump (application/octet-stream, text), 13.51 KiB.

Verified as described using the test database above.

Bug 42328 is related to this bug here

the problem has been addressed in version 5.1
- problem with re-creating the PK is fixed
- the filter page has been removed in 5.1, so objects that should be excluded must be set to "ignore" in the diff-tree. (which has been made easier by allow multiselection and buttons to set the sync-policy for the objects)

fix will be in 5.1.14

An entry was added to the 5.1.14 changelog:

When synchronizing a model with a database, rather than synchronizing only the selected tables, all tables were synchronized.