Bug #104125 id must be first in some index
Submitted: 27 Jun 2021 19:03 Modified: 21 Jul 2021 18:11
Reporter: Rick James Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:all OS:Any (N/A)
Assigned to: CPU Architecture:Any (N/A)
Tags: auto_increment, INDEX, primary key

[27 Jun 2021 19:03] Rick James 
Description:
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html
says

"... an AUTO_INCREMENT column must be defined as part of an index"

That is true, but not adequate.

How to repeat:
CREATE TABLE ai2a ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, s VARCHAR(22) NOT NULL,
PRIMARY KEY(s,id)
) ENGINE=InnoDB;

-- That matches the documentation, but gives an error.
-- Swapping the columns "works".

Suggested fix:
Reword to 

"... an AUTO_INCREMENT column must the first column of some index"

Then add something like "Such an index does not need to be UNIQUE (or PRIMARY), the only drawback being the prevention of checking for an INSERT of a deliberately duplicate id."
[27 Jun 2021 19:24] MySQL Verification Team 
Thank you for the bug report.
[21 Jul 2021 18:11] Daniel Price 
Posted by developer:
 
The referenced documentation has been updated as suggested. The change should appear online soon.
Thank you for the bug report.