Error 1064: You have an error in your SQL syntax "VISIBLE"

Question

I am actually just trying & experimenting with MySQL Workbench and Azure Database for the first. So I have modeled a simple model on MySQL Workbench and when I tried to sync with the azure database I got the below error.

MySQL Workbench 8.0 Azure MySQL version:5.7

RROR: 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,
  PRIMARY KEY (`emp_id`),
  UNIQUE INDEX `employee_id_UNIQUE` (`emp_id`' at line 20
SQL Code:
        CREATE TABLE IF NOT EXISTS `azcare_schema`.`employee` (
          `id` INT(11) NOT NULL AUTO_INCREMENT,
          `emp_id` VARCHAR(10) NOT NULL,
          `first_name` VARCHAR(20) NULL DEFAULT NULL,
          `middle_name` VARCHAR(30) NULL DEFAULT NULL,
          `last_name` VARCHAR(20) NULL DEFAULT NULL,
          `date_of_birth` VARCHAR(45) NULL DEFAULT NULL,
          `gender` CHAR(6) NULL DEFAULT NULL,
          `marital_status` VARCHAR(20) NULL DEFAULT NULL,
          `nationality` VARCHAR(30) NULL DEFAULT NULL,
          `passport_number` VARCHAR(20) NULL DEFAULT NULL,
          `sponser` VARCHAR(40) NULL DEFAULT NULL,
          `joining_date` DATE NULL DEFAULT NULL,
          `department` CHAR(100) NULL DEFAULT NULL,
          `education_degree` VARCHAR(45) NULL DEFAULT NULL,
          `religion` VARCHAR(30) NULL DEFAULT NULL,
          `blood_group` CHAR(4) NULL DEFAULT NULL,
          `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
          `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
          PRIMARY KEY (`emp_id`),
          UNIQUE INDEX `employee_id_UNIQUE` (`emp_id` ASC) VISIBLE)
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8

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

Fetching back view definitions in final form.
Nothing to fetch

Thank you @ErgestBasha for the comment. I have seen other posts to disable Visible in MySQL Workbench. I think that would be the best option for now, not sure what is the impact — Unis, Jul 15 '22 at 10:29
By default indexes are visible, you don't need to specify visible keyword. There is no impact, just remove the keyword VISIBLE. Learn more here on [MySQL VISIBLE INVISIBLE](https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html) indexes — Ergest Basha, Jul 15 '22 at 10:43
Thank you @ErgestBasha I did update the version in MySQL Workbench Target MySQL version to 5.7. And it seems that it removed the 'VISIBLE'. However, now I am getting another issue: 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 ') ENGINE = InnoDB DEFAULT CHARACTER SET = utf8' at line 2 SQL Code: CREATE TABLE IF NOT EXISTS `azcare_schema`.`emp_bankdetail` ( ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 — Unis, Jul 15 '22 at 10:48
Check the fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=9507b4703ffeb70a469cc190a8047bdf — Ergest Basha, Jul 15 '22 at 10:58

score 0 · Answer 1 · answered Jul 15 '22 at 11:10

0

As advised by @ErgestBasha. VISIBLE is introduced on MySQL 8.0+ and Azure MySQL version is 5.7

I fixed the Error by just updating MySQL Workbench Preference-> MYSQL -> Default Target MySQL Version to 5.7

answered Jul 15 '22 at 11:10

Unis

365
1
4
13

Error 1064: You have an error in your SQL syntax "VISIBLE"

1 Answers1