Actually, the same error message will be issued even for ALGORITHM=COPY. It makes sense: the table definition includes a virtual column:

CREATE TABLE AttendDet (

  AttendDet_ID INT AUTO_INCREMENT PRIMARY KEY,

  Counter INT GENERATED ALWAYS AS (AttendDet_ID) VIRTUAL,

) ENGINE=InnoDB;

This would work in 10.1, where virtual columns cannot be indexed, but in 10.2 after MDEV-5800, it makes sense to block this.

So, the only problem with the mentioned dataset is that the following is refused:

alter table AttendDet drop column Counter, algorithm=inplace;

I am testing the following kind of a fix:

1. Remove the crude check from ha_innobase::check_if_supported_inplace_alter().
2. Do not invoke prepare_inplace_drop_virtual() if the table was created before 10.2.2.
3. In prepare_inplace_add_virtual(), if the table was created before 10.2.2, always add all virtual columns inside InnoDB.

The last part is needed so that ADD COLUMN…VIRTUAL will actually add the metadata to the InnoDB internal dictionary table SYS_VIRTUAL if there was no intermediate ALTER TABLE that would have dropped all virtual columns from the old table.

Failure reported in MDEV-17199 makes most of relevant upgrade tests fail.

Note: The following scenario should be covered as well:

create table t1(a int, b int as (a) virtual, c int as(a+a) virtual) engine=innodb;

--echo # upgrade from 10.0 or 10.1 to 10.2 or later

alter table t1 drop b,algorithm=inplace;

alter table t1 add d int as (-a) virtual,algorithm=inplace;

--echo # up to this point, no changes to the InnoDB dictionary are necessary

alter table t1 add index(c), algorithm=inplace;

Without any fix, we would refuse anything else than ALGORITHM=COPY. With the fix, InnoDB would not be aware of any virtual column metadata. As long as we are dropping virtual columns, nothing would need to be done inside InnoDB, because the virtual columns never existed in the first place, as far as InnoDB is concerned. We could also allow adding non-indexed virtual columns while not updating the InnoDB data dictionary. But, as soon as we add an index on a virtual column, we must make InnoDB aware of all virtual columns.

While this could have been useful to implement, I think that the ship has already sailed, because everyone should already have upgraded from MariaDB Server 10.1, which reached its end of life in October 2020.

Hi marko
With your last comment, are you saying that since MariaDB 10.2 we can ALTER TABLE with ALGORITHM=INPLACE ?

I'm on MariaDB 10.5, and I cannot use ALGORITHM=INPLACE if the table has a virtual generated column (indexed).

Thanks much.

nunop, before MDEV-5800 MariaDB Server did not support indexed virtual columns, but it did support virtual non-indexed columns. This ticket would have been about facilitating native ALTER TABLE on such tables that had been created before MariaDB 10.2.

There are several restrictions around native ALTER TABLE and virtual columns. Most of them were inherited from the implementation in MySQL 5.7, and the work-around of MDEV-13795 and MDEV-14332 added one more: online table rebuild is disallowed if indexed virtual columns exist.

marko Thank you very much for the clarification!

Do you know if there are plans to make Online ALTER TABLE in these scenarios possible?
Or are there limitations that totally make that impossible?

I might need to stop using indexed VIRTUAL columns completely, due to this limitation, unfortunately
(unless in the future it will be possible to do Online ALTER TABLE)

Thank you very much again. Good day to you.