Bug #95804 Synchornize Model always generates VISIBLE indexes no matter the server version
Submitted: 14 Jun 2019 8:54 Modified: 15 Jun 2019 6:23
Reporter: Georgi Sotirov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S4 (Feature request)
Version:8.0.16, 8.0.19, 8.0.21 OS:Microsoft Windows
Assigned to: CPU Architecture:x86

[14 Jun 2019 8:54] Georgi Sotirov
Description:
MySQL Workbench Synchronize Model feature (from menu Database -> Synchronize Model...) seems to always generate indexes with VISIBLE keyword no matter the server version against which the synchronization is done. This causes problem when synchronizing a model to server 5.7.x, because invisible indexes are only supported since 8.0.0 (see https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html#mysqld-8-0-0-optimizer). If I copy the synchronization script to the SQL Editor the VISIBLE keyword is well marked in error. I thus have to remove the VISIBLE keyword everywhere before applying the script manually. This is not practical especially in development phase when many changes are done to the database and have to be applied on servers.

How to repeat:
1. Create a table with any kind of index in MySQL Modeler.
2. Try to synchronize to MySQl Server 5.7.x (or older).
3. Synchronization fails with error, because VISIBLE keyword is unknown to the server.

Suggested fix:
The synchronization script should be generated with respect to server version and supported features, so when synchronizing to MySQL 5.7.x or older indexes should be generated without VISIBLE keyword.
[14 Jun 2019 10:19] MySQL Verification Team
Hello Georgi Sotirov,

Thank you for the report.
Could you please confirm the "Default Target MySQL Version" in Modeling Preferences? I tried on a dummy table with specified structure but observed that WB handles as per the settings in Modeling Preferences.

- Target MySQL server is 8.0.16, WB->preferences->Modelling -> default target MySQL Version set to 8.0.16

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;

CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT(11) NOT NULL,
  INDEX `dd` (`idtable1` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

- Target MySQL server is 5.7.26 , WB->preferences->Modelling -> default target MySQL Version set to 5.7

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;

CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT(11) NOT NULL,
  INDEX `dd` (`idtable1` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

- Setting details in here https://dev.mysql.com/doc/workbench/en/wb-preferences-modeling.html

regards,
Umesh
[14 Jun 2019 15:01] Georgi Sotirov
You're right about the setting (i.e. Default Target MySQL Version is 8.0 in my Workbench, which should be the default with Workbench 8.0.x). However, I normally synchronize to different servers with various MySQL versions (8.0, 5.7, 5.6 and sometimes even 5.5). And I do not think about what I'm synchronizing to until I see the errors. In the case with the index I also haven't explicitly selected VISIBLE or INVISIBLE and former is anyway the default (i.e. documentation says "Indexes are visible by default."), so it could be safely omitted.

So I do not think having a single setting at Workbench level is sufficient and practical. I would expect the Synchronize Model feature in Workbench to tell me upfront whether I have some unsupported features in my model compared to the server version I'm trying to synchronize to (e.g. if I try to synchronize and invisible index to MySQL 5.7 or virtual column to MySQL 5.6). At least I should be able to select the target version for each synchronization I'm doing. Could this be done? For example:

1. MySQL Workbench 8.0.x with Default Target MySQL Version set to 8.0;
2. I try to synchronize to MySQL 5.7.x, this is detected and warning is shown (e.g. Target MySQL Version [5.7] differs from default [8.0]).
3. I'm then offered with the option to enter (or select) the target MySQL version (e.g. 5.6, 5.7 or 8.0);
4. (Optional) When I select the target MySQL Version I'm given warnings about unsupported features (e.g. You have invisible indexes that cannot be synchronized to MySQL 5.7 or You have generated columns that cannot be synchronized to MySQL 5.6, etc.)

I would be quite happy if you implement at least points 1 to 3, so I do not have to change the global option for every synchronization I'm doing. This would be a lot more intuitive and practical.
[15 Jun 2019 6:23] MySQL Verification Team
Agree, thank you for the reasonable feature request!

regards,
Umesh
[13 May 2020 12:44] MySQL Verification Team
Bug #98987 marked as duplicate of this one.
[14 Oct 2020 19:11] Brian Plunkett
In support of Georgi Sotirov, this is a design flaw (i.e. bug) which should be identified as part of *regression testing*, *not* a feature request.

Additionally, I've identified almost all of the necessary places for the code changes required, and think I might have a workaround shortly.  If I complete the workaround before the release to fix these issues, I'll provide it to the MySQL community.
[14 Oct 2020 19:11] Brian Plunkett
In support of Georgi Sotirov, this is a design flaw (i.e. bug) which should be identified as part of *regression testing*, *not* a feature request.

Additionally, I've identified almost all of the necessary places for the code changes required, and think I might have a workaround shortly.  If I complete the workaround before the release to fix these issues, I'll provide it to the MySQL community.
[14 Oct 2020 19:24] Brian Plunkett
Please change the Severity of this matter to "S3" as it impacts the *entire* MySQL Workbench user community who supports MySQL Server 5.x (i.e. whether Community or Enterprise editions) as previously mentioned.
[28 Oct 2020 11:01] MySQL Verification Team
Bug #101270 marked as duplicate of this one.
[5 Nov 2020 18:25] Brian Plunkett
Bug #101270 is not a duplicate of this bug because in Bug #101270 the user is trying to use MySQL Workbench 8.0 with a MariaDB distribution.  The syntax is different for database column definitions.