Error
Error Code:
3714
MySQL Error 3714: SRID 0 Immutability
Description
This error occurs when an operation attempts to modify the properties or definition of Spatial Reference System Identifier (SRID) 0. SRID 0 is a special, reserved identifier in MySQL, often representing an undefined or Cartesian system, and its characteristics are fixed and cannot be altered by users.
Error Message
SRID 0 is not modifiable.
Known Causes
3 known causesAttempting to Define SRID 0
You tried to use `CREATE SPATIAL REFERENCE SYSTEM` or `ALTER SPATIAL REFERENCE SYSTEM` to define or change SRID 0, which is a reserved identifier.
Incorrect Geometry Data Insertion
Inserting or updating geometry data while explicitly specifying SRID 0, then attempting to modify its spatial properties, which is not allowed for this special SRID.
Misuse in Spatial Functions
Using SRID 0 in spatial functions or operations that expect a valid, user-definable, or standard SRID other than the immutable SRID 0.
Solutions
3 solutions available1. Recreate Spatial Index with Correct SRID medium
Drop and recreate the spatial index, ensuring a valid SRID is specified.
1
Identify the table and column with the spatial index causing the error. You can usually find this by looking at your `CREATE TABLE` statements or by querying `INFORMATION_SCHEMA.STATISTICS`.
SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND NON_UNIQUE = 0 AND INDEX_TYPE = 'SPATIAL';2
Drop the existing spatial index.
ALTER TABLE your_table_name DROP INDEX your_spatial_index_name;3
Recreate the spatial index, specifying a valid SRID (e.g., 0 for Cartesian or a specific SRID like 4326 for WGS84). SRID 0 is often problematic as it signifies an undefined or Cartesian coordinate system which might not be intended for spatial operations.
ALTER TABLE your_table_name ADD SPATIAL INDEX your_spatial_index_name (your_spatial_column_name) WITH SYSTEM VERSIONING SRID 0;4
If the error persists and you intended to use a specific spatial reference system, use its corresponding SRID instead of 0. For example, for WGS84 (latitude/longitude), use SRID 4326.
ALTER TABLE your_table_name ADD SPATIAL INDEX your_spatial_index_name (your_spatial_column_name) WITH SYSTEM VERSIONING SRID 4326;2. Update Spatial Data to a Valid SRID advanced
If your spatial data is intended for a specific coordinate system, update the SRID of the spatial column and its data.
1
Determine the correct SRID for your spatial data. This depends on the geographic area and the type of spatial data you are working with (e.g., WGS84 is common for GPS coordinates).
text2
Modify the spatial column to use the desired SRID. This might involve altering the table definition.
ALTER TABLE your_table_name MODIFY COLUMN your_spatial_column_name GEOMETRY SRID 4326;3
If the SRID was incorrectly set to 0, you may need to re-insert or update your spatial data to reflect the new SRID. MySQL's `ST_SRID()` function can be used to set the SRID for existing geometries.
UPDATE your_table_name SET your_spatial_column_name = ST_SRID(your_spatial_column_name, 4326);4
After updating the data, rebuild or recreate the spatial index as described in the previous solution to ensure it's using the correct SRID.
ALTER TABLE your_table_name DROP INDEX your_spatial_index_name;
ALTER TABLE your_table_name ADD SPATIAL INDEX your_spatial_index_name (your_spatial_column_name) WITH SYSTEM VERSIONING SRID 4326;3. Remove Spatial Index if Not Needed easy
If the spatial index is not essential for your application, simply remove it.
1
Identify the table and the spatial index name.
SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND INDEX_TYPE = 'SPATIAL';2
Drop the spatial index.
ALTER TABLE your_table_name DROP INDEX your_spatial_index_name;