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

[15 Nov 2014 17:07] Denis Nedelyaev
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 ;
[15 Nov 2014 17:07] Denis Nedelyaev
Source schema DDL

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

[15 Nov 2014 17:08] Denis Nedelyaev
Target schema DDL

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

[2 Dec 2014 8:30] MySQL Verification Team
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
[2 Dec 2014 9:42] Denis Nedelyaev
Modified source schema DDL

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

[2 Dec 2014 9:42] Denis Nedelyaev
Modified target schema DDL

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

[2 Dec 2014 9:47] Denis Nedelyaev
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.
[2 Dec 2014 11:28] MySQL Verification Team
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
[2 Dec 2014 12:37] Denis Nedelyaev
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;
[2 Dec 2014 13:03] MySQL Verification Team
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
[12 Feb 2015 1:32] Philip Olson
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.