MySQL Bugs: #85550: "Synchronize Model" skips certain tables

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:	Windows (10 x64)
Assigned to:		CPU Architecture:	Any

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).

Table Mapping Window

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

The generated script to create the database. (Using sync function)

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

The corresponding model file.

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

The original problem table in the model.

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

The server table (from an older version of the model)

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

The sync result (nothing to sync)

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

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.

Hello Erwin de Haan,

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

Thanks,
Umesh

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.

Same problem here. For me it’s a showstopper. I can’t design the database if synchronization doesn’t work reliable.

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 :/

I have the same problem with tables using datatype JSON.

I have the same problem with tables using datatype JSON too! (just registered to leave this comment)

Same problem here