📋

Description

This error indicates an issue with the name provided for a Spatial Reference System (SRS) in MySQL. It occurs when the SRS name is either an empty string or contains leading or trailing whitespace, violating MySQL's naming conventions for SRS objects.

💬

Error Message

The spatial reference system name can't be an empty string or start or end with whitespace.

🔍

Known Causes

4 known causes

⚠️

Empty SRS Name Provided

The operation attempted to define or reference a Spatial Reference System using an empty string as its name.

⚠️

Whitespace in SRS Name

The supplied SRS name includes spaces or other whitespace characters at the beginning or end of the string.

⚠️

Typographical Error

A simple typo or accidental key press resulted in an extra space or an entirely empty string being passed as the SRS name.

⚠️

Programmatic Name Generation Issue

An application or script generated an SRS name that inadvertently contained leading/trailing whitespace or was an empty string.

🛠️

Solutions

3 solutions available

1. Correct SRS Name During Table Creation easy

Ensure spatial reference system (SRS) names are valid when creating or altering tables.

1

When creating a new table with a spatial column, or adding a spatial column to an existing table, provide a valid SRS name. A valid SRS name is a non-empty string that does not start or end with whitespace. The most common SRS is SRID 0, which is typically used for unprojected geographic coordinates (like WGS84 latitude/longitude).

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    geom GEOMETRY NOT NULL SRID 0
);

2

If you are adding a spatial column to an existing table, use the `ALTER TABLE` statement with a valid SRID.

ALTER TABLE existing_table
ADD COLUMN new_geom GEOMETRY NOT NULL SRID 4326; -- Example using SRID 4326 (WGS84)

2. Update Existing Invalid SRS Names medium

Modify existing spatial columns to use valid SRS names.

1

Identify tables and columns with invalid SRS names. You can query the `INFORMATION_SCHEMA.COLUMNS` table to find spatial columns and their associated SRIDs. Look for SRIDs that might be empty strings or have leading/trailing whitespace.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_TYPE LIKE '%SRID%';

2

If you find a column with an invalid SRID (e.g., an empty string or whitespace), you will need to alter the column definition. This might involve dropping and re-adding the column or, if supported and feasible, directly modifying the column definition. Dropping and re-adding is often safer and more straightforward if the column is not populated with critical data that needs immediate preservation.

-- Example: Dropping and re-adding a column with a valid SRID
-- Backup your data first if this is a production environment!
ALTER TABLE your_table DROP COLUMN your_spatial_column;
ALTER TABLE your_table ADD COLUMN your_spatial_column GEOMETRY NOT NULL SRID 0; -- Or another appropriate SRID

3

Alternatively, if you can insert data into the column, you might be able to update the SRID by re-inserting the geometry with a valid SRID. This is less direct and depends on how the invalid SRID was introduced.

-- This is a more complex scenario and might not be directly applicable for fixing the column definition itself.
-- It's more about ensuring data inserted into a corrected column has a valid SRID.
-- If the column definition itself is the problem, use the alter/drop method.

3. Validate SRS Input in Application Code medium

Prevent the error by validating SRS input before sending it to MySQL.

1

If your application code is responsible for defining spatial reference systems (e.g., when creating tables or inserting data), implement input validation. Ensure that any string intended to be an SRS name is not empty and does not contain leading or trailing whitespace.

// Example in JavaScript (Node.js with a MySQL driver)
function isValidSrsName(srsName) {
  if (!srsName) {
    return false; // Empty string
  }
  if (srsName.trim() !== srsName) {
    return false; // Contains whitespace
  }
  return true;
}

const srsNameFromUser = '  0  '; // Example input

if (isValidSrsName(srsNameFromUser)) {
  // Proceed with database operation
  console.log('Valid SRS name');
} else {
  console.error('Invalid SRS name provided.');
  // Handle error, e.g., inform the user
}

2

In your SQL queries generated by the application, always use parameterized queries to prevent SQL injection and ensure that values like SRIDs are correctly handled.

// Example using a hypothetical database connection object
const srsId = 0; // Or a validated string name
const query = 'CREATE TABLE IF NOT EXISTS my_spatial_table (geom GEOMETRY NOT NULL SRID ?)';
connection.execute(query, [srsId], (err, results) => {
  if (err) throw err;
  console.log('Table created or already exists.');
});

🔗

Related Errors

5 related errors

MySQL Error 3710: Invalid SRS Name Format