📋

Description

This error indicates that a MySQL function, typically a spatial function like `ST_Buffer`, was invoked with a radius parameter that is zero or negative. MySQL spatial functions require a strictly positive radius for valid geometric operations, as a zero or negative radius is mathematically undefined or nonsensical in this context.

💬

Error Message

Invalid radius provided to function %s: Radius must be greater than zero.

🔍

Known Causes

3 known causes

⚠️

Directly Provided Invalid Radius

The SQL statement explicitly passed a literal value of zero or a negative number (e.g., `0`, `-5.0`) as the radius argument to a function.

⚠️

Variable or Column Contains Invalid Radius

A variable, parameter, or a value retrieved from a table column used as the radius argument holds a non-positive number (zero or negative).

⚠️

Calculated Radius is Non-Positive

The radius argument is the result of an arithmetic expression or subquery that, due to its logic or underlying data, evaluates to zero or a negative number.

🛠️

Solutions

3 solutions available

1. Correcting Radius Values in Stored Procedures or Functions medium

Identify and fix the non-positive radius value within your SQL code.

1

Locate the specific stored procedure or function that is triggering the error. The error message often includes the name of the function (`%s`).

SHOW ERRORS;

2

Examine the code within the identified procedure/function for any calls to spatial functions that require a radius (e.g., `ST_Buffer`).

-- Example of problematic code:
SELECT ST_Buffer(geometry_column, 0) FROM your_table;
SELECT ST_Buffer(geometry_column, -10) FROM your_table;

3

Modify the code to ensure that any radius argument passed to spatial functions is a positive number. If a radius is intended to be zero for specific logic, this will need to be re-evaluated as it's not allowed.

-- Corrected example:
SELECT ST_Buffer(geometry_column, 10) FROM your_table;

4

Re-compile or re-create the stored procedure/function after making the correction.

DELIMITER //
CREATE PROCEDURE your_procedure() BEGIN
  -- corrected spatial function call
END //
DELIMITER ;

-- Or for a function:
DELIMITER //
CREATE FUNCTION your_function(radius_val INT) RETURNS ... BEGIN
  -- corrected spatial function call
END //
DELIMITER ;

2. Validating Input Parameters Before Spatial Operations medium

Add checks to ensure radius values are positive before they are used in spatial functions.

1

Within your application code (e.g., backend service, script) that interacts with MySQL, implement validation for any values that will be used as radii in spatial queries.

const radius = getRadiusFromUserInput(); // Assume this function gets the radius

if (radius <= 0) {
  throw new Error('Radius must be a positive value.');
}

// Proceed with building and executing the SQL query

2

Alternatively, if the radius is being passed directly to a stored procedure, add a check inside the procedure itself.

DELIMITER //
CREATE PROCEDURE process_geometry(IN geo GEOMETRY, IN radius_val DOUBLE)
BEGIN
  IF radius_val <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Radius must be greater than zero.';
  ELSE
    -- Proceed with spatial operation using radius_val
    SELECT ST_Buffer(geo, radius_val);
  END IF;
END //
DELIMITER ;

-- Call the procedure:
CALL process_geometry(your_geometry_data, 0); -- This will now raise an error within the procedure

3. Reviewing and Correcting Data in Spatial Columns advanced

Ensure that any data generated or inserted into spatial columns doesn't inadvertently lead to radius calculations of zero or less.

1

If the error is occurring during a query that involves calculations on existing spatial data, investigate the data itself. Sometimes, degenerate geometries or specific data configurations can lead to unexpected results when spatial functions are applied.

-- Example: Check for geometries that might result in a zero buffer radius if not handled carefully
SELECT ST_AsText(your_geometry_column) FROM your_table WHERE ST_Length(your_geometry_column) = 0;

2

If you identify problematic data, consider data cleaning or transformation. This might involve using functions like `ST_MakeValid` or re-creating geometries.

-- Example of attempting to make a geometry valid (may not always resolve radius issues directly but is good practice)
UPDATE your_table SET your_geometry_column = ST_MakeValid(your_geometry_column) WHERE ST_IsRectified(your_geometry_column) = 0;

3

When inserting or updating spatial data, ensure that any associated parameters used for spatial operations are correctly validated and positive.

-- During INSERT or UPDATE statements, ensure positive radius values are used if applicable in triggers or default values
INSERT INTO your_table (geometry_column, other_data) VALUES (ST_GeomFromText('POINT(1 1)'), 'some_data'); -- No radius here, but if a trigger or function used it, it would be validated.

🔗

Related Errors

5 related errors

MySQL Error 3706: Non-Positive Radius Value