Error
Error Code:
3760
MySQL Error 3760: Unsupported Spatial Functional Index
Description
Error 3760 indicates that MySQL does not support creating a functional index on expressions involving spatial data types or spatial functions. This error typically occurs during DDL operations, specifically when attempting to define an index that calculates or transforms spatial data using a function within the index definition itself.
Error Message
Spatial functional index is not supported.
Known Causes
3 known causesDirect Spatial Function Indexing
Attempting to create an index directly on the output of a spatial function (e.g., `ST_X(geometry_column)`) rather than on the base spatial column.
Misunderstanding Functional Index Scope
Assuming that MySQL's general support for functional indexes extends to spatial functions, which is a specific limitation.
MySQL Version Limitation
This restriction is a fundamental design choice in current MySQL versions and is not specific to a particular patch or minor release.
Solutions
3 solutions available1. Remove Spatial Functional Index easy
The most direct solution is to remove the unsupported spatial functional index.
1
Identify the table and column(s) involved in the spatial functional index. You can usually find this information in the error message or by examining your schema.
2
Drop the existing spatial functional index using the ALTER TABLE statement.
ALTER TABLE your_table_name DROP INDEX index_name;3
If the functional index was created implicitly as part of a spatial index definition that uses a function, you might need to recreate the spatial index without the function. For example, if you had `CREATE SPATIAL INDEX idx_geom ON geom (ST_GeomFromWKB(geometry_column))`, you would change it to `CREATE SPATIAL INDEX idx_geom ON geom (geometry_column)` (assuming `geometry_column` is of a spatial type).
ALTER TABLE your_table_name DROP INDEX index_name;
CREATE SPATIAL INDEX new_spatial_index ON your_table_name (your_geometry_column);2. Upgrade MySQL Version medium
Newer MySQL versions support spatial functional indexes.
1
Check your current MySQL server version. Spatial functional indexes were introduced in MySQL 8.0.13.
SELECT VERSION();2
If your MySQL version is older than 8.0.13, plan and execute an upgrade to a supported version. This typically involves backing up your data, installing the new MySQL version, and restoring your data.
3
After upgrading, you should be able to create spatial functional indexes as intended.
3. Recreate Spatial Index Without Function medium
If the function was used to prepare data for indexing, consider indexing the raw spatial data.
1
Identify the spatial column and the function used in the functional index. For example, if you tried to index `ST_GeomFromWKB(wkb_column)` and got this error, `wkb_column` is the raw data, and `ST_GeomFromWKB` is the function.
2
Drop the problematic functional index.
ALTER TABLE your_table_name DROP INDEX index_name;3
Create a standard spatial index on the raw spatial data column. MySQL's spatial index implementation can often handle different spatial formats directly.
CREATE SPATIAL INDEX new_spatial_index ON your_table_name (your_raw_spatial_column);4
Modify your queries to use spatial functions directly on the indexed column where needed, rather than relying on a pre-indexed functional representation.
SELECT ... FROM your_table_name WHERE ST_Intersects(your_raw_spatial_column, ST_GeomFromWKB(...));