MySQL Workbench: Error in query (1064): Syntax error near 'VISIBLE' at line 1

Question

Any ideas why VISIBLE below is causing an issue?

CREATE TABLE IF NOT EXISTS `setting` (   `uuid` INT(10) NOT NULL,   `type` VARCHAR(255) NOT NULL,   `code` VARCHAR(255) NOT NULL COMMENT 'An unique name.',   `value` MEDIUMTEXT NULL DEFAULT NULL,   `comment` LONGTEXT NULL DEFAULT NULL,   `created_on` INT UNSIGNED NOT NULL,   `updated_on` INT UNSIGNED NOT NULL,   PRIMARY KEY (`uuid`)) ENGINE = MyISAM DEFAULT CHARACTER SET = utf8;  CREATE UNIQUE INDEX `name_UNIQUE` ON `setting` (`code` ASC) VISIBLE;  CREATE UNIQUE INDEX `uuid_UNIQUE` ON `setting` (`uuid` ASC) VISIBLE; 

Errors:

CREATE UNIQUE INDEX name_UNIQUE ON setting (code ASC) VISIBLE Error in query (1064): Syntax error near 'VISIBLE' at line 1

CREATE UNIQUE INDEX uuid_UNIQUE ON setting (uuid ASC) VISIBLE Error in query (1064): Syntax error near 'VISIBLE' at line 1

No error if I remove VISIBLE but MySQL Workbench 8.0.12 auto generates that. How can I stop MySQL Workbench from doing that?

My MySQL info in my Ubuntu 18.04:

MySQL version: 5.7.23-0ubuntu0.18.04.1 through PHP extension MySQLi

Answer 1

The problem here is the difference in syntax across different MySQL server versions. It seems that MySQL Workbench 8.0.12 is auto-generating CREATE UNIQUE INDEX statement for the MySQL server version 8.0.

From the MySQL Server 8.0 Docs, the syntax for CREATE INDEX is:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name     [index_type]     ON tbl_name (key_part,...)     [index_option]     [algorithm_option | lock_option] ...  key_part: {col_name [(length)] | (expr)} [ASC | DESC]  index_option:     KEY_BLOCK_SIZE [=] value   | index_type   | WITH PARSER parser_name   | COMMENT 'string'   | {VISIBLE | INVISIBLE}  /* Notice the option of VISIBLE / INVISIBLE */  index_type:   USING {BTREE | HASH} 

However, this option of {VISIBLE | INVISIBLE} is not available in the MySQL Server 5.7. From Docs:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name     [index_type]     ON tbl_name (key_part,...)     [index_option]     [algorithm_option | lock_option] ...  key_part:     col_name [(length)] [ASC | DESC]  index_option:     KEY_BLOCK_SIZE [=] value   | index_type   | WITH PARSER parser_name   | COMMENT 'string'   /* No option of VISIBLE / INVISIBLE */  index_type:     USING {BTREE | HASH} 

---

If you are not looking to upgrade to latest version of MySQL; you can disable this feature of auto-generating with VISIBLE / INVISIBLE index:

In MySQL Workbench:

Go to:

Edit > Preferences > Modeling > MySQL.

Then, set the "Default Target MySQL Version" to 5.7

Check the screenshot below:

Answer 2

Alternative version to 5.7 or 6.3. I used 6.3. On Model>Model Options..>MySql>6.3