MySQL Bugs: #95804: Synchornize Model always generates VISIBLE indexes no matter the server version

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:	Windows
Assigned to:		CPU Architecture:	x86

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.

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

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.

Agree, thank you for the reasonable feature request!

regards,
Umesh

Bug #98987 marked as duplicate of this one.

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.

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.

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.

Bug #101270 marked as duplicate of this one.

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.