Missing support for AUTO_INCREMENT columns in multiple-column index for InnoDB tables is a widely known limitation, as is the trigger workaround for when it is needed (see e.g this post). Yesterday, however, I ran into this thread claiming support for this feature was added as early as 5.1. I tried the following statement on my 5.7 install (mysql-community-server-5.7.19-1.el7.x86_64), and it appears to work flawlessly:
CREATE TABLE `tbl1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`data` datetime NOT NULL,
PRIMARY KEY (`id`,`data`)
) ENGINE=InnoDB;
My problem is that, for the life of me, I cannot find a positive statement of support of the feature on MySQL's documentation site - or elsewhere. I am rather wary of deploying in production something unannounced (which might be experimentally present) so I hope somebody can point me to the official status of this.
Edit: It turns out that the manual contains, in parts unrelated, this rather oblique statement:
“…an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.”
which suggests, but does not state, support.
Edit 2: Using information from @rolandoMYSQLDBA below, I also located this additional snippet: "An AUTO_INCREMENT column must appear as the first column in an index on an InnoDB table.". I am therefore accepting his answer.
And in case someone is wondering, the multiple column index thing is needed for table partitioning - the customer has auto_increment PK on all tables and they want to partition on a date column.