MySQL Bugs: #74878: Column mapping functionality of the schema synchranization wizard is broken

Bug #74878	Column mapping functionality of the schema synchranization wizard is broken
Submitted:	15 Nov 2014 17:07	Modified:	12 Feb 2015 1:32
Reporter:	Denis Nedelyaev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	6.2.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	column mapping, column renaming, model synchronization

Description:
During setting up model synchronization MySQL Workbench 6 allows us to set mapping of tables and columns between source and target schemas to make renaming of tables and columns possible. It is very useful feature. Unfortunately, the synchronization wizard produces an incorrect synchronization SQL-script for mapped columns, i.e. columns requiring renaming.

How to repeat:
1) Open a model document.
2) Choose Database -> "Synchronize with Any Source..."
3) Go forward to the "Select Sources" screen.
4) Select "a.sql" as a source script file and "b.sql" as a target (both files are attached to this bug report).
5) Proceed to the "Select Changes to Apply" screen.
6) Open column mapping for table1.
7) Map column "a" of the source to column "b" of the target.
8) Note that an incorrect synchronization script is produced:

ALTER TABLE `test`.`table1` 
CHANGE COLUMN `a` `a` INT(11) NOT NULL ;

Whereas the correct one is:

ALTER TABLE `test`.`table1` 
CHANGE COLUMN `b` `a` INT(11) NOT NULL ;

Source schema DDL

Attachment: a.sql (application/octet-stream, text), 1.04 KiB.

Target schema DDL

Attachment: b.sql (application/octet-stream, text), 1.04 KiB.

Hello Denis,

Thank you for the bug report and steps.
This is most likely duplicate of Bug #73644 and which is fixed in 6.2.4. 
Could you please check with latest GA 6.2.4 and confirm from your end?

WB GA can be downloaded from - http://dev.mysql.com/downloads/workbench/

Thanks,
Umesh

Modified source schema DDL

Attachment: a_v2.sql (application/octet-stream, text), 1.16 KiB.

Modified target schema DDL

Attachment: b_v2.sql (application/octet-stream, text), 1.16 KiB.

Hello Umesh,

In 6.2.4 the problem doesn't appear with the original schema files attached to this issue. But when I try slightly modified version of them, the problem occurs again. In the modified version synchronization requires not only column renaming but also reordering of the same column.

I have attached modified version of the schema files (a_v2.sql and b_v2.sql). Steps to reproduce the issue are the same.

Thanks.

Thank you for the feedback.
Could you please paste the output of the generated diff from "Compare and report Differences" wizard for before mapping, and after? 

Thanks,
Umesh

I don't quite understand what you meant by generating diff for before mapping and after, but if I simply compare a_v2.sql and b_v2.sql the output of Compare and Report Differences in Catalogs wizard is:

+--------------------------------------------+
| Catalog Diff Report                        |
+--------------------------------------------+
Table `test`.`table1` was modified
  columns:
  - added column b of type INT(11)
  - removed column a
  __
  indices:
  - added index PRIMARY with columns: b
  - removed index PRIMARY
  __
----------------------------------------------
End of MySQL Workbench Report

May be you meant Synchronize with Any Source wizard? Then the output before column mapping is:

ALTER TABLE `test`.`table1` 
DROP COLUMN `b`,
ADD COLUMN `a` INT(11) NOT NULL FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`a`);

And the output after mapping column "a" of the source to column "b" of the target is:

ALTER TABLE `test`.`table1` 
CHANGE COLUMN `a` `a` INT(11) NOT NULL FIRST;

While the correct output should be:

ALTER TABLE `test`.`table1` 
CHANGE COLUMN `b` `a` INT(11) NOT NULL FIRST;

Thank you for the feedback.
Verifying after confirming with Dev's.
Also, I'll request you further if any info required in this regard.

Thanks,
Umesh

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.2.5 release, and here's the changelog entry:

The model synchronization wizard would produce an incorrect
synchronization SQL script if table or column mappings were modified. The
generated script did not contain all of these modifications.

Thank you for the bug report.