7

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.

Improve this question

2 Answers 2

Reset to default
7
+50

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:

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.

Improve this answer
0
0

Conversion to InnoDB covers the issue:

A way to simulate the MyISAM 'feature' might be something like: What you want is this, but it won't work because it is referencing the table twice:

  INSERT INTO foo
     (other, id, ...)
     VALUES
     (123, (SELECT MAX(id)+1 FROM foo WHERE other = 123), ...);

Instead, you need some variant on this. (You may already have a BEGIN...COMMIT.)

  BEGIN;
  SELECT @id := IFNULL(MAX(id),0) + 1 FROM foo WHERE other = 123 FOR UPDATE;
  INSERT INTO foo
     (other, id, ...)
     VALUES
     (123, @id, ...);
  COMMIT;

Having a transaction is mandatory to prevent another thread from grabbing the same id.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.