Bug #90459 Synchronization: ALTER TABLE statement is invalid when both adding & changing
Submitted: 17 Apr 2018 2:08 Modified: 31 May 2018 0:13
Reporter: Simon East Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench: Modeling Severity:S2 (Serious)
Version:6.3.10 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[17 Apr 2018 2:08] Simon East
Reproduction Steps

1. Have a set of tables that have been synchronized between MySQL and MySQL Workbench previously.
2. Add a new column to a table
3. Modify an existing column by dragging it after the new column
4. Synchronise the tables with the database. Unfortunately the resulting SQL is invalid because the CHANGE COLUMN statement comes *before* the ADD COLUMN statement within the script.

Example Output:

ALTER TABLE `tracks` 
CHANGE COLUMN `DEPRECATED_project_id` `DEPRECATED_project_id` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'Refers to projects table' AFTER `final_report_email_sent`,
ADD COLUMN `assignment_email_sent` TINYINT(1) NOT NULL DEFAULT 0 AFTER `actual_product_country`,
ADD COLUMN `final_report_email_sent` TINYINT(1) NOT NULL DEFAULT 0 AFTER `assignment_email_sent`;

You'll notice that the CHANGE COLUMN statement refers to `final_report_email_sent` although that column does not exist yet, so it produces an error.

How to repeat:
[17 Apr 2018 4:59] Umesh Shastry
Hello Simon East,

Thank you for the report and steps.

[30 May 2018 19:47] Christine Cole
Posted by developer:
Fixed as of the upcoming MySQL Workbench 8.0.12 release, and here's the changelog entry:

The operation that synchronizes tables with the MySQL database produced an
error message when an existing column was moved to a position after a
newly added column. This fix ensures that ADD COLUMN always precedes
CHANGE COLUMN within the script when both are used together.

Thank you for the bug report.
[31 May 2018 0:13] Simon East
Thanks for the fix Christine!

Hopefully you've made sure that this doesn't introduce another bug - such as if you rename one field and add a new field immediately after the renamed one. Will the ALTER statement contain a valid "AFTER" reference?

Thanks again,