MySQLWorkbench forward engineering error

Question

I'm working on a model in MySql Workbench 8.0, when I click on forward engineering and try to generate the script of my model I get

Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VISIBLE,
  CONSTRAINT `fk_Compras_Personas`
    FOREIGN KEY (`persona_id`)
    R' at line 9
SQL Code:
        -- -----------------------------------------------------
        -- Table `bd_inventario2018_2`.`compras`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `bd_inventario2018_2`.`compras` (
          `nmcompra` INT(11) NOT NULL,
          `persona_id` INT(11) NOT NULL,
          `fecompra` DATE NOT NULL,
          PRIMARY KEY (`nmcompra`, `persona_id`),
          INDEX `fk_Compras_Personas_idx` (`persona_id` ASC) VISIBLE,
          CONSTRAINT `fk_Compras_Personas`
            FOREIGN KEY (`persona_id`)
            REFERENCES `bd_inventario2018_2`.`personas` (`id`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8

SQL script execution finished: statements: 6 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

Or when i try to syncronize the model I get

Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VISIBLE' at line 4
SQL Code:
        ALTER TABLE `bd_inventario2018_2`.`productos` 
        ADD COLUMN `anchetas_id` INT(11) NOT NULL AFTER `psventa`,
        ADD COLUMN `productoscol` VARCHAR(45) NOT NULL AFTER `anchetas_id`,
        ADD INDEX `fk_productos_Anchetas1_idx` (`anchetas_id` ASC) VISIBLE

SQL script execution finished: statements: 3 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

Someone knows what's happening? This is an automatic process, I'm not overwritting anything,

Thanks

VISIBLE / INVISIBLE option for INDEX constraints are not supported on MySQL 5.x, only on MySQL 8+. Just remove it and SQL script will execute. — Jan, May 08 '20 at 06:13

score 15 · Accepted Answer · answered Aug 15 '18 at 13:20

15

MySQL Workbench is generating the script for MySQL 8 (which supports the new invisible indexes), which you likely do not have.

You need to specify the MySQL version you are using, either in Model\Model Options\MySQL\Target MySQL Version or, globally, in Edit\Preferences\Modelling\MySQL\Target MySQL Version.

answered Aug 15 '18 at 13:20

Solarflare

10,721
2
18
35

2

Same issue for me. I checked MAMP and changed the Workbench settings to match. – Dean Sep 25 '18 at 13:06

score 3 · Answer 2 · answered Mar 18 '19 at 19:31

3

Alternatively, you can go on the MySQL Workbench GUI, Edit->Preferences then Modeling->Mysql and change the default target MySQL.

answered Mar 18 '19 at 19:31

Felipe Volpato

334
1
5

score 2 · Answer 3 · answered Sep 17 '19 at 22:34

2

I had the same problem, using version = 8.0.17.

The error originates when a relationship is formed if No action is selected in the "Foreign Key Options" section. Ensure that the Skip in SQL generation is checked (When No action is selected).

This should solve the problem!

answered Sep 17 '19 at 22:34

Johan Fick

375
2
14

1

No, that's probably related to using MyISAM storage engine. You need to use InnoDb to enable fk constraints and/or the Foreign Key Options you used. – Jan May 07 '20 at 15:36

MySQLWorkbench forward engineering error

3 Answers3

Linked