According to an interactive debugging session between myself and valerii, the SQL layer is delivering correct metadata (altered_table->found_next_number_field == NULL, and AUTO_INCREMENT_FLAG is not set on the field).

The fix should be in ha_innobase::check_if_supported_inplace_alter() and possibly ha_innobase::prepare_inplace_alter_table().

This should be a simple fix, because InnoDB does not store the AUTO_INCREMENT metadata in its own data dictionary. We will only have to update the in-memory data dictionary cache if this is the only change made in the ALTER TABLE statement.

I tried the following variation of this:

--source include/have_innodb.inc

create table t(id int auto_increment primary key) engine=InnoDB;

alter table t modify column id int, algorithm=inplace;

drop table t;

This will result in ha_alter_info->handler_flags = ALTER_COLUMN_DEFAULT | ALTER_COLUMN_TYPE.
To fix this, InnoDB would have to compare the types of existing columns when ALTER_COLUMN_TYPE is set. I think that this is feasible to fix in MariaDB 10.2, but not in earlier versions (theoretically it could be done already in 10.0).

What about TokuDB and RocksDB ? Is it possible to implement something generic ?

I think that the handler::inplace_alter_table() interface is already as generic as it gets. The storage engines that support AUTO_INCREMENT do need to be aware of the metadata changes. Theoretically, news flag could be introduced for the removal or addition of AUTO_INCREMENT attributes, but in the end each ALGORITHM=INPLACE capable storage engine would still have to do something about it.

This ticket will only cover the change for InnoDB.

kevg, can you fix this? I think that it is more feasible to fix in 10.3, which introduced the ALGORITHM=INSTANT keyword. Or only 10.4, if the code is too different from 10.3.

The changes are mostly outside InnoDB. The InnoDB changes look mostly OK to me.

According to serg review this ticket not only about INSTANT operation which changes metadata only, but also about converting non-unique index to unique and generating/fixing AUTO_INCREMENT values like in this test case:

  create table t1 (a int not null, index (a));

   insert t1 values (0),(0),(0);

   alter table t1 modify a int auto_increment;

   select * from t1;

No, this issue is only about the INSTANT operation that changes metadata only — namely about removing AUTO_INCREMENT from a column.

Adding AUTO_INCREMENT is not metadata-only operation, and it's beyond the scope of this issue.

I'm a bit confused now. What to do with https://github.com/MariaDB/server/pull/1125 ?
Adding AUTO_INCREMENT when data is not need to be changed can and thus should be INSTANT, right? No need to rebuild in that case.

If generating values along with adding AUTO_INCREMENT is a different task could you show me an issue? Or should I create it?

What to do with https://github.com/MariaDB/server/pull/1125

Change it to be in scope of this MDEV. Only handle removal of AUTO_INCREMENT, not adding.

Adding AUTO_INCREMENT when data is not need to be changed can and thus should be INSTANT, right?

Yes. But you need to know that the data does not need to be changed. It may be possible, but it's definitely more complex than "simply do it INSTANT" like for the removal of AUTO_INCREMENT. If you want to do it — sure, please, create an MDEV for INSTANT adding ot AUTO_INCREMENT and do it there. Make sure it corectly handles the test case from above.

Thanks for clarifications.

Yes. But you need to know that the data does not need to be changed. It may be possible, but it's definitely more complex than "simply do it INSTANT" like for the removal of AUTO_INCREMENT.

I can see now it's problematic even when interesting index is unique:

create table t1 (a int not null, unique index (a));

insert t1 values (0),(1),(2);

alter table t1 modify a int auto_increment;

select * from t1;

drop table t1;

query 'alter table t1 modify a int auto_increment' failed: 1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'a'