MySQL Bugs: #88845: AUTO_INCREMENT persistence obviates need for INDEX

Bug #88845	AUTO_INCREMENT persistence obviates need for INDEX
Submitted:	10 Dec 2017 19:04	Modified:	9 Feb 2018 6:51
Reporter:	Rick James	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment

Description:
Before Version 8, an auto_increment column needed to be specified as the first column on some index.  This was so that a restart would be able to deduce the next value after a restart.

The drawback was that deleting the highest id just before a restart could lead to reuse of that id.  Although auto_increment does not disallow such, it has been an annoyance to some users.

8.0 persists the MAX(id) some other way.  In doing so, it seems that there is no longer a necessity for some INDEX to exist.

How to repeat:
CREATE TABLE foo ( id INT AUTO_INCREMENT ) ENGINE=InnoDB;

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Suggested fix:
* Change the code so that the index is not required.
* Change the documentation to reflect this relaxation.

https://dev.mysql.com/doc/refman/8.0/en/create-index.html is a likely place for the doc change, but there may be more.

Hello Rick,

Thank you for the feature request!

Thanks,
Umesh