📋

Description

This error indicates an attempt to create a Spatial Reference System (SRS) with a System Reference ID (SRID) that is already in use within the MySQL database. It typically occurs when defining or importing spatial data definitions that specify a pre-existing SRID.

💬

Error Message

There is already a spatial reference system with SRID %u.

🔍

Known Causes

3 known causes

⚠️

Manual SRID Conflict

An explicit `CREATE SPATIAL REFERENCE SYSTEM` statement specifies an SRID value that is already registered in the database.

⚠️

Conflicting Database Import

Occurs when executing SQL scripts or restoring a database backup that includes SRS definitions with SRIDs already present in the target database.

⚠️

Schema Migration Error

During schema updates or migrations, new SRS definitions are introduced which clash with existing SRID values.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate SRID medium

Find and delete the conflicting SRID entry from the spatial_ref_sys table.

1

Connect to your MySQL server using a client like `mysql` command-line tool or MySQL Workbench.

2

Query the `mysql.spatial_ref_sys` table to find existing entries for the SRID that is causing the error. Replace `%u` with the actual SRID number reported in the error message.

SELECT * FROM mysql.spatial_ref_sys WHERE SRID = %u;

3

Examine the results to confirm there's a duplicate. If you find an entry that you didn't intend to create or that is redundant, you can delete it. **Caution:** Ensure you understand the implications of deleting an SRID, especially if it's used by existing spatial data. If unsure, back up the table first.

DELETE FROM mysql.spatial_ref_sys WHERE SRID = %u;

4

Attempt the operation that previously failed (e.g., creating a spatial index or adding a spatial column) again.

2. Use a Different, Available SRID easy

If the SRID is not critical, choose an alternative SRID that is not currently in use.

1

When creating a spatial column or index, specify a different SRID value that is known to be available. You can check for existing SRIDs by querying `mysql.spatial_ref_sys`.

SELECT SRID FROM mysql.spatial_ref_sys;

2

Choose an SRID from the available list that is not causing conflicts. For example, if you were trying to use SRID 4326 and it's taken, try another common SRID like 2157 (if applicable to your region) or a user-defined SRID.

ALTER TABLE your_table ADD COLUMN your_geom_column GEOMETRY SRID 2157;

3

If you are creating a spatial index, specify the alternative SRID.

CREATE SPATIAL INDEX your_index_name ON your_table (your_geom_column) USING SRID 2157;

3. Correctly Insert SRID into spatial_ref_sys medium

If you are manually adding an SRID, ensure the entry is valid and not a duplicate.

1

Before attempting to use a new SRID, verify its existence in the `mysql.spatial_ref_sys` table.

SELECT SRID FROM mysql.spatial_ref_sys WHERE SRID = %u;

2

If the SRID does not exist, you can insert it. Ensure you provide all necessary details for the SRID. Refer to the MySQL documentation for the correct structure of `spatial_ref_sys` entries.

INSERT INTO mysql.spatial_ref_sys (SRID, Auth_Name, Auth_SRID, SRText, Notes) VALUES (%u, 'EPSG', %u, 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]]', 'WGS 84');

3

If the SRID already exists but with different parameters, and you intend to use this SRID, you might need to update the existing record. **Proceed with extreme caution** as this can affect existing spatial data. It's generally safer to use a new SRID or remove the conflicting one if it's incorrect.

-- Example: Updating an SRID (use with caution!)
UPDATE mysql.spatial_ref_sys SET SRText = 'your_new_srtext' WHERE SRID = %u;

4

After ensuring the SRID is correctly registered, retry your operation.

🔗

Related Errors

5 related errors

MySQL Error 3712: Duplicate SRID Exists