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

by

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:

MySQL WorkBench Modeling

More Related Contents:

  1. Calculate date range between two rows
  2. Authentication plugin ‘caching_sha2_password’ cannot be loaded
  3. Error Code: 2013. Lost connection to MySQL server during query
  4. String concatenation in MySQL
  5. MySQL: How to allow remote connection to mysql
  6. Access Denied for User ‘root’@’localhost’ (using password: YES) – No Privileges?
  7. MySQL Foreign Key Error 1005 errno 150 primary key as foreign key
  8. MySQL error code: 1175 during UPDATE in MySQL Workbench
  9. Cannot connect to Database server (mysql workbench)
  10. How to connect mysql workbench to running mysql inside docker?
  11. MySQL Workbench: How to keep the connection alive
  12. How do I retrieve my MySQL username and password?
  13. MySQL 8.0.11 error connect to caching_sha2_password the specified module could not be found
  14. MySQL error 1022 when creating table
  15. Is there a naming convention for MySQL?
  16. Can’t connect to MySQL server on ‘127.0.0.1’ (10061) (2003)
  17. MySQL Errno 150
  18. error code 1292 incorrect date value mysql
  19. Order by FIELD in MYSQL
  20. SELECT INTO Variable in MySQL DECLARE causes syntax error?
  21. Accessing a mysql database from external host/ip? (ie: mysql workbench)
  22. Error Code: 1062. Duplicate entry ‘1’ for key ‘PRIMARY’
  23. mySQL query to search all tables within a database for a string?
  24. Create a new database with MySQL Workbench
  25. Errno 121, duplicate key on write or update?
  26. How to change max_allowed_packet size
  27. Use MySQL spatial extensions to select points inside circle
  28. How to find non-existing data from another Table by JOIN?
  29. Pass parameter to MySQL script command line
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment