Why Does MySQL Workbench Throw a 'Syntax error near 'VISIBLE' at line 1' When Creating Unique Indexes?

MySQL Workbench Error "Syntax error near 'VISIBLE' at line 1": Understanding Index Syntax Differences

In MySQL Workbench, you encountered an error while attempting to create unique indexes with the VISIBLE option. The issue stems from the differing syntax across MySQL server versions.

Syntax Variations

MySQL Server 8.0 allows for the use of VISIBLE and INVISIBLE options in the CREATE INDEX statement to control index visibility. However, these options are not supported in MySQL Server 5.7 and earlier.

Causes of the Error

MySQL Workbench 8.0.12 inadvertently generates CREATE INDEX statements compatible with MySQL Server 8.0, which includes the VISIBLE option. When attempting to execute these statements against MySQL Server 5.7.23, which you're using in your Ubuntu environment, the syntax error "Syntax error near 'VISIBLE' at line 1" is encountered.

Solution 1: Disable VISIBLE Index Auto-Generation

If upgrading to MySQL Server 8.0 is not an option, you can disable the auto-generation of VISIBLE indexes in MySQL Workbench.

1. In MySQL Workbench, go to "Edit" > "Preferences" > "Modeling" > "MySQL."
2. Set the "Default Target MySQL Version" to "5.7."

Solution 2: Upgrade MySQL Server

Alternatively, consider upgrading your MySQL server to version 8.0 or later to gain support for the VISIBLE index option. This upgrade will allow MySQL Workbench to generate the index statements correctly.

Additional Note

If you encounter the same error when manually creating indexes with VISIBLE in MySQL Server 5.7, remove the VISIBLE option from the statement to make it compatible.

The above is the detailed content of Why Does MySQL Workbench Throw a 'Syntax error near 'VISIBLE' at line 1' When Creating Unique Indexes?. For more information, please follow other related articles on the PHP Chinese website!