MySQL and AUTO_INCREMENT columns in multiple-column index for InnoDB tables: support status?

Under normal circumstances, I would have closed this question as a duplicate. There are already close votes. In this particular instance, since you put a bounty on this question, I'll try to answer it as best as I can.

Look at the first post you mentioned. Note the table definition

CREATE  TABLE `issue_log` (
`sr_no` INT NOT NULL AUTO_INCREMENT ,
  `app_id` INT NOT NULL ,
  `test_id` INT NOT NULL ,
  `issue_name` VARCHAR(255) NOT NULL ,
primary key (app_id, test_id,sr_no)
);

The auto_increment column is sr_no. This is neither the only column nor the leftmost column in the primary key definition. Now, if the primary key was written

primary key (sr_no, app_id, test_id)

then sr_no would be leftmost. No error would have occurred with InnoDB.

When you ran the command

CREATE TABLE `tbl1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `data` datetime NOT NULL,
  PRIMARY KEY (`id`,`data`)
) ENGINE=InnoDB;

your auto_increment column id was leftmost.

You made reference to a second post. Please note that in the answer to that second post, it clearly notes that MyISAM and BDB storage engines support auto_increment ids. When it comes to MyISAM, the auto_increment behavior allows an auto_increment number to be paired with one or more keys in such a way that the number 1 can appear multiple times but be associated uniquely with the columns. I have discussed this a few times over the years:

• Apr 21, 2012 : How can you have two auto-incremental columns in one table?
• Jun 10, 2012 : MySQL get next unique value without auto increment
• Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin
• Aug 28, 2013 : How to go about modeling the main object of relationship?

Keep in mind that while InnoDB support for an auto_increment column in a PRIMARY KEY exists, it does not have the same auto_increment behavior as MyISAM. MyISAM can bind with other columns to allow a number to exist multiple times, InnoDB does not do that. Once an auto_increment value is used, it is unique for the whole table.

The excerpt you found

an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.

comes from a page in the MySQL Documentation that has to do with how replication sees and handles auto_increment values. This is a vital issue since the order data are written on a Master may get serialized in a different order on a Slave.

Just recently (back on Jun 16, 2017) I answered Does it ever make sense to create an index with additional columns after the primary key?, where I mentioned from the MySQL Documentation that when there is an auto_increment column in a partitioned table, it is mandatory that the column is included in the PRIMARY KEY along with the columns that define the sharding of the table rows.

To conclude, let me just say this: support for auto_increment columns in a PRIMARY KEY in InnoDB does not come with any fringe benefits or additional bell-and-whistles like it does with MyISAM. That's why the MySQL Documentation does not have a lot written about it.

Support for this was mentioned long ago. If you look in the MySQL 5.1 Documentation on InnoDB Restrictions, you see bulletpoint #15

For an AUTO_INCREMENT column, you must always define an index for the table, and that index must contain just the AUTO_INCREMENT column. In MyISAM tables, the AUTO_INCREMENT column may be part of a multi-column index.

There is more extensive stuff in the MySQL 5.1 Documentation AUTO_INCREMENT Handling in InnoDB. It is also available in the MySQL 5.5 Documentation. Unfortunately, these are in the old Documentation format. This is probably why you can't find anything now. Just be assured that the support is there.