MySQL Bugs: #72109: Avoid table rebuild when adding or removing of auto

Bug #72109	Avoid table rebuild when adding or removing of auto_increment settings
Submitted:	24 Mar 2014 12:25	Modified:	24 Mar 2014 13:13
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.6.14	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment

Description:
When removing or adding an auto_increment definition from a column MySQL rebuilds the table. This should not be necessary and for large tables is obstructive and time-consuming.

How to repeat:
root@myserver [mydb]> create table mytable ( `id` int(11) NOT NULL AUTO_INCREMENT, `t` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ;
Query OK, 0 rows affected (0.13 sec)

root@myserver [mydb]> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@myserver [mydb]> insert into mytable values ( 1, 'hehhh' ), (2, 'hhh') , ( 3, 'swwww' ), ( 4, 'xxxx'), ( null, 'stuff'), (null, 'stuff2'), ( null, 'stuff3'), (null,'stuff4') ;
Query OK, 8 rows affected (0.04 sec)
Records: 8  Duplicates: 0  Warnings: 0

root@myserver [mydb]> select * from mytable;
+----+--------+
| id | t      |
+----+--------+
|  1 | hehhh  |
|  2 | hhh    |
|  3 | swwww  |
|  4 | xxxx   |
|  5 | stuff  |
|  6 | stuff2 |
|  7 | stuff3 |
|  8 | stuff4 |
+----+--------+
8 rows in set (0.00 sec)

root@myserver [mydb]> alter table mytable modify id int not null ;
Query OK, 8 rows affected (0.45 sec)
Records: 8  Duplicates: 0  Warnings: 0

root@myserver [mydb]> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.14-log |
+------------+
1 row in set (0.00 sec)

Suggested fix:
This is a definition change and should not require a table rebuild.
So don't rebuild the table but just change the definition.

Note: this should work both ways: adding or removing an auto_increment column setting.

I guess this is too late for 5.7 (and not checked if this works in 5.7)  but would be a nice to have.

Thank you for this reasonable feature request :

The operation is not listed here :

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

Verified by this command :

mysql>  alter table mytable modify id int not null,algorithm=inplace,lock=none;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

This feature request has got a duplicate in the bug # 78119.