MySQL 8 - All Parts of Spatial Index Must Be Null - But no nulls in column

Question

I've added a POINT column to a rather large existing table. I had to set it null initially as required by MySQL. I then executed successfully:

update geoloc set geoPoint = ST_SRID( POINT( 0, 0 ), 4326)

Next, when I tried to add a spatial index to this column, I got

ERROR 1252: All parts of a SPATIAL index must be NOT NULL

I tried "SELECT * from geoloc where geoPoint is null" and got 0 results.

So there are no null values in the column, but the index fails telling me I can't have a null. What am I missing?

player0 · Accepted Answer · 2024-06-02 20:35:42Z

1

try:

-- Step 1: Ensure no NULL values (already done)
UPDATE geoloc SET geoPoint = ST_SRID(POINT(0, 0), 4326) WHERE geoPoint IS NULL;

-- Step 2: Modify the column to NOT NULL
ALTER TABLE geoloc MODIFY COLUMN geoPoint POINT NOT NULL;

-- Step 3: Add the spatial index
ALTER TABLE geoloc ADD SPATIAL INDEX (geoPoint);

answered Jun 2, 2024 at 20:35

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jerry Malcolm Over a year ago

Thank you so much. That worked. A bit curious about the reason for having to do it this way. But it works now, and that's all that matters

Collectives™ on Stack Overflow

MySQL 8 - All Parts of Spatial Index Must Be Null - But no nulls in column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related