Can't add POINT NOT NULL column to MySQL table

Question

I recently upgraded to MySQL 5.7.9 & am using MyISAM for my table engine.

Adding a new POINT column like so:

ALTER TABLE <table> ADD centroid POINT NOT NULL;

gives this error:

ERROR 1138 (22004): Invalid use of NULL value

I can create the column without NOT NULL but then I am unable to create a spatial index on the column. This worked for me in MySQL 5.6, and I don't see anything relevant in the docs.

Answer 1

the problem was: you cannot create a not null column on a table with existing rows without providing a default (I was unable to do this).

the correct sequence:

1. alter table <table> add column centroid point (add the column, no constraint)
2. update <table> set centroid = ST_Centroid(shape) (i.e. populate the column with values)
3. alter table <table> modify centroid point not null (add the constraint)

Answer 2

I was hoping to use generated columns to work around this:

alter table apb add column geom POINT AS (POINT(lat,lon)) STORED NOT NULL;

but get the same error. This does work for plain arithmetic columns:

alter table apb add column total int as (lat+lon) STORED NOT NULL;

However you can use the former syntax at table creation time and then bulk load. The generation rule means that you've still avoided a painfully three painfully slow add/update/alter (not null) steps.