MDEV-6076 introduces persistent AUTO_INCREMENT for InnoDB, InnoDB does not really require an index to exist on AUTO_INCREMENT columns.
The reason for requiring an index to exist was that before
MDEV-6076, InnoDB used to execute the equivalent of SELECT MAX(autoinc_column) when loading a table definition.
MDEV-6076, there only are two cases where we need MAX(autoinc_column). The first one is upgrading an old dataset where the persistent AUTO_INCREMENT is not present (but the AUTO_INCREMENT column is necessarily indexed, because up to now, InnoDB always required the index to exist).
The second reason is the following use case:
By default, ALGORITHM=INPLACE would reset the sequence to 1, while after ALGORITHM=COPY, the last INSERT would use a value larger than 100.
For compatibility with ALGORITHM=COPY and MyISAM, the user-requested AUTO_INCREMENT=1 should be adjusted to be at least MAX(autoinc_column)+1.
If we want to maintain the ALTER TABLE compatibility, we could execute MAX(autoinc_column) on the SQL layer, using an index if one is available. The InnoDB internal code that requires the index to exist can be removed. Even if an index is not available, a table scan would be a lot faster than executing ALGORITHM=COPY.
Most of this work should be outside InnoDB. Maybe there should be a handler::table_flags() bit for specifying whether an index on AUTO_INCREMENT column is required.