MySQL Forums
Forum List  »  InnoDB

Adding new virtual column with instant algorithm
Posted by: Lawrence Marigold
Date: December 09, 2020 08:58AM


I'm trying to add a generated column using the instant algorithm on an InnoDB table on MySQL 8.0.22 on Windows Server 2012.

Whatever I try however it gives me the same error:

"Error Code: 1846. ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE."

The original (simplified) query is like this:

"alter table contacts add column (test boolean generated always as (if(data->>'$.test' is null or data->>'$.test' = 'false',false,true)) virtual), ALGORITHM INSTANT" is a json column.

I thought the issue might be to do with accessing the json, so I tried the most simple genrated column I can imagine with the exact same error:

"alter table contacts add column (test boolean generated always as (false) virtual), ALGORITHM INSTANT;"

I don't understand what other actions are being performed which are causing the error, and as I understood it, virtual columns are one of the actions that are specifically supposed to be supported by the instant algorithm.

If I exclude the algorithm part of the command it does work, but is quite slow, which is not ideal for what we are trying to achieve.

Am I missing something?

Thanks - Lawrence

Options: ReplyQuote

Written By
Adding new virtual column with instant algorithm
December 09, 2020 08:58AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.