Today I was presenting on JSON Schema validation and covering how MySQL implemented the JSON-Schema.org ideas on making sure JSON data meets criteria before being allowed into the database. I was asked by Percona Live attendee Nickolay Ihalainin if the JSON_SCHEMA_VALID function worked with ALTER TABLE's ALGORITHM = INSTANT option.
I admitted that I did not know as a I had not tested. While I was answering questions, Nickolay did this test.
alter table foo17 add CHECK
(json_schema_valid(
_utf8mb4'{ "type":"object",
"properties":{
"latitude":{"type":"number", "minimum":-90, "maximum":90},
"longitude":{"type":"number", "minimum":-180, "maximum":180}
},
"required": ["latitude", "longitude"]\n }',doc)
),
ALGORITHM=INSTANT;
And the answer (drum roll here):
ERROR: 1845: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY.
Dang!
And yes, it does work with ALGORITHM=COPY.
