EC
1411
MySQLerrorddlhigh confidence

All parts of a SPATIAL index must be NOT NULL

Production Risk

Low — DDL fails; no data affected.

What this means

A SPATIAL INDEX was created on a column that allows NULL values; spatial indexes require NOT NULL columns.

Why it happens
  1. 1CREATE SPATIAL INDEX on a nullable GEOMETRY column (MySQL 5.7.5+)
  2. 2ALTER TABLE ADD SPATIAL INDEX without making the column NOT NULL first
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (g GEOMETRY); CREATE SPATIAL INDEX idx ON t(g);

expected output

ERROR 1411 (42000): All parts of a SPATIAL index must be NOT NULL

Fix

Make the geometry column NOT NULL

Make the geometry column NOT NULL
ALTER TABLE t MODIFY g GEOMETRY NOT NULL; CREATE SPATIAL INDEX idx ON t(g);

Why this works

Spatial indexes require NOT NULL constraint.

Version notes

Sources
Official documentation ↗

MySQL 8.0 — 1411 ER_SPATIAL_CANT_HAVE_NULL

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors