MySQLWorkbench forward engineering error

Sebastian Luna picture Sebastian Luna · Aug 15, 2018 · Viewed 7.6k times · Source

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

Answer

Solarflare picture Solarflare · Aug 15, 2018

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.