Why am I getting a MySQL Workbench Error (1064) with 'VISIBLE' syntax in CREATE INDEX statements?

MySQL Workbench Error (1064): Understanding VISIBLE Syntax

In MySQL Workbench 8.0.12, users encounter an error when encountering the VISIBLE syntax in CREATE INDEX statements. To address this issue, we need to examine the syntax variations across MySQL server versions.

Syntax Differences

MySQL Server 8.0 introduced the ability to mark indexes as VISIBLE or INVISIBLE. This option controls whether the index is visible to the optimizer. In contrast, MySQL Server 5.7 lacks this feature.

MySQL Workbench Configuration

The issue arises when MySQL Workbench auto-generates CREATE INDEX statements incompatible with the server version. In this case, it is generating statements with VISIBLE for a MySQL Server 5.7 environment.

Solution: Disable VISIBLE

To resolve this, disable MySQL Workbench from automatically generating VISIBLE indexes:

1. Navigate to Edit > Preferences > Modeling > MySQL.
2. Set the "Default Target MySQL Version" to 5.7.

Troubleshooting

Additionally, ensure that your MySQL server version is compatible with the workbench version. If not, it is recommended to upgrade or downgrade your components to maintain compatibility.

Conclusion

Understanding the syntax differences between MySQL server versions is crucial when using MySQL Workbench. Fine-tuning the workbench configurations to match the target MySQL environment can mitigate errors and ensure the smooth execution of queries.

The above is the detailed content of Why am I getting a MySQL Workbench Error (1064) with 'VISIBLE' syntax in CREATE INDEX statements?. For more information, please follow other related articles on the PHP Chinese website!