Bug #85550 "Synchronize Model" skips certain tables
Submitted: 20 Mar 2017 19:18 Modified: 21 Mar 2017 7:55
Reporter: Erwin de Haan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S2 (Serious)
Version:6.3.9 build 10960321 CE OS:Microsoft Windows (10 x64)
Assigned to: CPU Architecture:Any

[20 Mar 2017 19:18] Erwin de Haan
Description:
The synchronization wizard seems to miss some obvious differences between the same table. Or not "see" the table at all, won't give you the option to "force" the table with table mapping. (Screenshot attached)

MySQL Server 5.7.17-1ubuntu16.04 running on Ubuntu 16.04.1 LTS (GNU/Linux 4.4.0-57-generic x86_64) sourced from the MySQL apt repository.

Connected over SSH using Workbench

My original issue was to do that no matter the changes to a table it would not show changes or give the option to change the server table. But while making a minimal working example, this issue popped up first. There is so far not anyway around it. The Synchronize Model function does not guarantee the model is exactly as on the server.

How to repeat:
1. Open the included model file.

2. Synchronize with a random MySQL server (5.7.x)

3. It generates the script as attached.

4. Apply

5. Open the testtable_problem table and add a column 'testtable_new'

6. It will now either want to create a new table like so:

CREATE TABLE IF NOT EXISTS `testdb_bug_2`.`testtable_problem` (
  `testtable_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `testtable_text` VARCHAR(45) NOT NULL DEFAULT 'text',
  `testtable_num` INT(11) NOT NULL DEFAULT 1234567890,
  `testtable_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `testtable_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `testtable_virtual` INT(11) GENERATED ALWAYS AS (SUBSTRING(`testtable_num`,6)) VIRTUAL,
  `testtable_new` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`testtable_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci

or not want to sync anything at all. (Have seen both, the not syncing anything was my first problem.)

7. Cancel as this is clearly wrong.

8. Change the column back.

9. If you change the same column in the testtable table it syncs fine (although it still doesn't see the other table some times).
[20 Mar 2017 19:19] Erwin de Haan
Table Mapping Window

Attachment: MySQLWorkbench_2017-03-20_20-11-40.png (image/png, text), 21.23 KiB.

[20 Mar 2017 19:19] Erwin de Haan
The generated script to create the database. (Using sync function)

Attachment: testdb_bug.sql (application/octet-stream, text), 1.66 KiB.

[20 Mar 2017 19:20] Erwin de Haan
The corresponding model file.

Attachment: testdb_bug.mwb (application/octet-stream, text), 7.25 KiB.

[20 Mar 2017 19:24] Erwin de Haan
The original problem table in the model.

Attachment: model_table.png (image/png, text), 17.67 KiB.

[20 Mar 2017 19:25] Erwin de Haan
The server table (from an older version of the model)

Attachment: server_table.png (image/png, text), 22.79 KiB.

[20 Mar 2017 19:25] Erwin de Haan
The sync result (nothing to sync)

Attachment: sync_result.png (image/png, text), 56.55 KiB.

[20 Mar 2017 19:53] Erwin de Haan
The correct script, the other one had the wrong quotes in the generated column.

Attachment: testdb_bug.sql (application/octet-stream, text), 1.67 KiB.

[21 Mar 2017 7:55] Umesh Shastry
Hello Erwin de Haan,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh
[20 Nov 2017 3:25] Y R
I am seeing the exactly same problem.  Updating Workbench to the latest version (6.3.10, Windows version) didn't help.

It would be good to add the ability to force a synchronization on individual tables, so that there is a consistent and reliable workaround to such problems.
[16 Jan 2018 11:48] Ronny H.
Same problem here. For me it’s a showstopper. I can’t design the database if synchronization doesn’t work reliable.
[24 Feb 2018 20:35] Fredrik Motin
Same here, but what is not mentioned so far in this bug, is that the problem seems to be specific to tables that have a GENERATED column. 

A suggested rename of this bug report is: "Synchronize Model" skips tables that contain generated columns

Also, this is not specific to version 6.3.9 nor Windows. Reproduces with 6.3.10 (latest available) on OSX. 

To be clear, it IS possible to create generated columns using Workbench and they will be synchronized after comparing schemas, but once there are generated columns in a table, those tables will not be detected by Workbench, showing "N/A" in the comparison view, as if they don't exist, and consequently generating a CREATE TABLE script in the synchronization even though the table is already there. 

A workaround is to drop all generated columns from the target database before the schema comparison. The comparison will then complete and generate alter scripts to synchronize with the model, including re-adding the recently dropped generated columns. 

A bummer to see that this bug is 9 months old without any fix being deployed :/
[13 Apr 2018 11:52] Marius Bjugan
I have the same problem with tables using datatype JSON.
[19 Jul 2018 14:14] Mathew Holder
I have the same problem with tables using datatype JSON too! (just registered to leave this comment)
[4 Oct 2018 13:20] Jamie Wilson
Same problem here