Thursday, May 13, 2021

JSON Validation and ALGORITHM = Instant

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.