Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.12, 5.5.59
Description
Basically, it's a request to implement upstream feature request:
https://bugs.mysql.com/bug.php?id=72109
Copying the table to just drop auto_increment attribute:
MariaDB [test]> create table t(id int auto_increment not null, key(id)) engine=InnoDB;
|
Query OK, 0 rows affected (0.22 sec)
|
|
|
MariaDB [test]> alter table t modify column id int not null, algorithm=inplace;
|
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY
|
is awful in production. Workarounds exist, but why not to do this in-place and without copying all the data?
Attachments
Issue Links
- blocks
-
-
- Closed
-
- causes
-
MDEV-19272 virtual enum_alter_inplace_result ha_innobase::check_if_supported_inplace_alter(TABLE*, Alter_inplace_info*): Assertion `((((*af)->unireg_check) & 127U) == Field::NEXT_NUMBER) == (((f->unireg_check) & 127U) == Field::NEXT_NUMBER)' failed.
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
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'
|
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: