Additional languages
« 14.5.6.2 Converting MyISAM Tables to InnoDB
14.5.6.4 FOREIGN KEY Constraints »
Section Navigation      [Toggle]

14.5.6.3. How AUTO_INCREMENT Columns Work in InnoDB

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

InnoDB uses the following algorithm to initialize the auto-increment counter for a table T that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table T, InnoDB executes the equivalent of this statement: 

SELECT MAX(ai_col) FROM T FOR UPDATE;

InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. If the table is empty, InnoDB uses the value 1. If a user invokes a SHOW TABLE STATUS statement that displays output for the table T and the auto-increment counter has not been initialized, InnoDB initializes but does not increment the value and stores it for use by later inserts. Note that this initialization uses a normal exclusive-locking read on the table and the lock lasts to the end of the transaction.

InnoDB follows the same procedure for initializing the auto-increment counter for a freshly created table.

After the auto-increment counter has been initialized, if a user does not explicitly specify a value for an AUTO_INCREMENT column, InnoDB increments the counter by one and assigns the new value to the column. If the user inserts a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value.

You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.

If a user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, InnoDB treats the row as if the value had not been specified and generates a new value for it.

The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type.

When accessing the auto-increment counter, InnoDB uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an AUTO_INCREMENT column. Nevertheless, two transactions cannot have the AUTO-INC lock on the same table simultaneously, which can have a performance impact if the AUTO-INC lock is held for a long time. That might be the case for a statement such as INSERT INTO t1 ... SELECT ... FROM t2 that inserts all rows from one table into another.

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

InnoDB supports the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, to set the initial counter value or alter the current counter value. The effect of this option is canceled by a server restart, for reasons discussed earlier in this section.


User Comments

Posted by Jeremy Perkins on November 9 2003 8:51am[Delete] [Edit]

WARNING ...

The auto-increment value of a deleted record will only not be reused until the server is restarted, then it will be reused. In other words, if you insert 10 rows into an InnoDB table and then delete them the AUTO_INCREMENT value will stay at 11 until the server is shutdown at which point it will revert to 1!!!


Posted by [name withheld] on December 9 2003 1:08am[Delete] [Edit]

AUTO_INCREMENT IN InnoDB PRIMARY KEYS

To define a multi-column primary key in an InnoDB table, one of them beeing AUTO_INCREMENTed , the 'primary key()' statement must be given, as first argument, the AUTO_INCREMENTed column.

For example, the following doesn't work :

Create table sampling_site (
site_id Char(3) NOT NULL,
site_sub_id Tinyint UNSIGNED NOT NULL AUTO_INCREMENT,
Primary Key (site_id,site_sub_id)
) TYPE = InnoDB;

You get the message : "ERROR 1075 at line 5: Incorrect table definition; There can only be one auto column and it must be defined as a key"


But this does works :

Create table sampling_site (
site_id Char(3) NOT NULL,
site_sub_id Tinyint UNSIGNED NOT NULL AUTO_INCREMENT,
Primary Key (site_sub_id,site_id)
) TYPE = InnoDB;



Posted by [name withheld] on December 22 2003 1:26am[Delete] [Edit]

In reply to the remark on auto_increment in InnoDB primary keys:

It is possible to still use the original primary key order as long as an additional key is created for the auto_increment column. Here is an example derived from the 3.6.9 example (http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html):

CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
key (id),
PRIMARY KEY (grp,id)
) type=innodb;

INSERT INTO animals (grp,name) VALUES("mammal","dog"),("mammal","cat"),
("bird","penguin"),("fish","lax"),("mammal","whale"),
("bird","ostrich");

SELECT * FROM animals ORDER BY grp,id;

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  4 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  5 | whale   |
| bird   |  3 | penguin |
| bird   |  6 | ostrich |
+--------+----+---------+
6 rows in set (0.00 sec)

Note that the auto increment values are not group specific. (in this case or the previous post's case) See 3.6.9 for an example of what would happen in a MyISAM table type (obviously the preferred behavior in some cases).

(Debian MySQL 4.0.16)

Posted by Mike Owen on August 7 2004 12:57pm[Delete] [Edit]

But why else would you use auto_increment in that way? The InnoDB method would seem to require larger variable types to handle what could be small groupings.

For instance, if you never expect more that 20 entries for the first part of the combined key, then tinyint(2) would be fine. If, however, you expect that the table will eventually contain more that 254 rows, you have to adjust the constraint of the auto-increment part of the key to handle the maximum table size. Most programmers don't think that way, and while it may produce the type of natural groupings that the programmer intended, it forces ineffecient use of storage.

Posted by James Day on December 8 2004 7:32pm[Delete] [Edit]

InnoDB clusters records in index leaf nodes by primary key. Consider a version control system where you want to quickly get to different revisions of the same file. You'd want the file ID to be the first part of the primary key, so adjacent revisions are likely to be stored in the same index leaf node. Use the autoincrement field first and you can end up with one disk seek per revision.

You may care about unique sequences for each file when it comes to jumping back 20 revisions. Whether you want both that file-unique value and a table-unique value depends on the application. I have one where I want both the autoincrement as a permanent and unique identifier for a specific document and revision, while I also need fast indexed access to revisions 10,000-10,020 of one specific file. So, both the per-file sequence and the per-table sequence.

Posted by Hubidubi on May 25 2005 1:36pm[Delete] [Edit]

You can reuse your InnoDB keys after you've truncated the table:

TRUNCATE TABLE test;
ALTER TABLE test AUTO_INCREMENT=0;

Posted by Derek on June 9 2005 4:35pm[Delete] [Edit]

So is there a way to not have auto_increment revert back to 1 if the server is restarted?

Posted by Toby Thain on March 31 2006 6:15am[Delete] [Edit]

Derek, it seems to me that the described policy for AUTO_INCREMENT is only meant to guarantee:
* all rows have a unique value
* new INSERTs get a higher value than existing rows

If you need a stronger guarantee - such as a monotonically increasing value (no reuse) - then I suspect the answer is to implement it outside the db itself?

Posted by Stephen Dewey on August 14 2006 11:24pm[Delete] [Edit]

Going off of what Derek is saying, the policy of resetting auto_increment to the lowest existing value in the table +1 whenever MySQL is restarted is all that is necessary for ensuring that every row is unique and that later rows have higher auto_incremented values.

However, the main drawback here is if you want to link together tables using a primary key of one table as the foreign key for other tables (e.g. if I have a table of users and other tables that contain various records related to them by their primary key), and if I wish to preserve orphaned records in other tables if a copy in the primary table is deleted.

This behavior of InnoDB's auto_increment values mean that doing so is no longer feasible. With auto-increment values being reset this way, if a record in the primary table were deleted (e.g. a user account were deleted) and then the server were reset, the next user account created would inherit all of the orphaned records in the other tables that had not been deleted.

In the example I gave, this could lead to serious security issues, namely access by the new user to the old user's personal data that they had stored in the database. Not to mention confusion for the new user.

The only real solution as I see it is to delete orphaned records as well, and store them in alternate tables if you want to preserve them.

This seems to be a sub-optimal behavior, and hopefully MySQL AB will give us the option in the future of preserving InnoDB's auto_increment values on disk rather than in RAM.

Posted by Joseph Libson on August 17 2006 6:28pm[Delete] [Edit]

I completely agree with Mr. Dewey above.

We are archiving records in one of our core OLTP tables, but we want the ability to restore old transactions in an ad-hoc manner.

We will only be safe as long as we *never* archive the most recently inserted record. Now it is very unlikely that would happen, but it is awful to have to rely on that.

Posted by Daniel Ardelian on September 6 2006 9:30am[Delete] [Edit]

As a previous poster said, create a table with an autoinc primary key, insert 10 records, delete them, restart the server. Subsequent inserts will restart the autoincrement counter at 1.

You could solve this issue by creating another auxiliary table (tablename_GEN_ID) with a single integer autoinc primary key column. Before inserting data into the main table, insert a record into this table and get the resulting autogenerated value (PHP: mysql_insert_id(...)). Use this value as a primary key for the main table.

If necessary, you can also implement a garbage collection mechanism to delete really old records from the auxiliary table.

Posted by dan bloch on December 27 2006 4:37pm[Delete] [Edit]

i agree with mr dewey. mysql says this is not a bug. my response was to switch to myISAM where this doesn't happen. does that mean that this is a bug in myISAM? i think not. the 'old school' myISAM wins this issue hands down. i hope that myISAM holds up in production.

dan bloch

Add your own comment.