2

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.

2

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)
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.