MySQL 5.0 Reference Manual  /  ...  /  AUTO_INCREMENT Handling in InnoDB AUTO_INCREMENT Handling in InnoDB

To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column ai_col must be defined as part of an index such that it is possible to perform the equivalent of an indexed SELECT MAX(ai_col) lookup on the table to obtain the maximum column value. Typically, this is achieved by making the column the first column of some table index.

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:


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. 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 you do not explicitly specify a value for an AUTO_INCREMENT column, InnoDB increments the counter and assigns the new value to the column. If you insert 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.

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.

A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, which you can use with InnoDB tables as of MySQL 5.0.3 to set the initial counter value or alter the current counter 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.

User Comments
  Posted by Ap Muthu on October 20, 2011
Currently we cannot avoid the AUTO_INCREMENT=XXX in mysqldumps which is essential if only the schema is dumped for use in new dbs.

The following bug has yet to be addressed:

It is nicely summarised with a post operative fix:

mysqldump -u root -p -h <db-host> --opt <db-name> -d --single-transaction | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > <filename>.sql

  Posted by Thomas Mayer on January 20, 2012
As InnoDb forgets its highest auto_increment after server restart, you can set it again, if you have stored it anywhere. This happens often if you archive your data in an archive table and then delete it and then restart mysql. When archiving again this will result in duplicate key entries.

To work around this you can create a trigger which makes sure your auto_increment is higher than the auto_increment of your archive table:

delimiter //
drop trigger if exists trigger_autoinc_tbl;
CREATE TRIGGER trigger_autoinc_tbl BEFORE INSERT ON tbl
declare auto_incr1 BIGINT;
declare auto_incr2 BIGINT;
SELECT AUTO_INCREMENT INTO auto_incr1 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl';
SELECT AUTO_INCREMENT INTO auto_incr2 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl_archiv';
IF (auto_incr2 > auto_incr1 and<auto_incr2) THEN
SET = auto_incr2;
delimiter ;

Further reading:
  Posted by Stan Gor on June 12, 2012
work around for innodb auto_increment on multiple-column as last column auto_increment
Use additional counter table, and use BEFORE INSERT Trigger
also you can create Trigger on BEFORE update to watch for increment of `f2` if you do updates on `1test`.`f2`

CREATE TABLE `1test` (
`f1` int(10) unsigned NOT NULL,
`f2` int(10) unsigned NOT NULL default '0',
`data_f3` int(10) unsigned NOT NULL,
PRIMARY KEY (`f1`,`f2`)
CREATE TABLE `1test_counter` (
`f1` int(10) unsigned NOT NULL,
`f2` int(10) unsigned default NULL,


DROP TRIGGER `1test_c`///

IF NEW.`f2` IS NULL OR NEW.`f2` = 0 THEN
UPDATE `1test_counter`
SET `f2` = `f2` + 1, NEW.`f2` = `f2`
WHERE `f1` = NEW.`f1`

INSERT INTO `1test_counter`(`f1`, `f2`)
VALUES (NEW.`f1`, 1);
SET NEW.`f2` = 1;
INSERT INTO `1test_counter`(`f1`, `f2`)
VALUES (NEW.`f1`, NEW.`f2`)
`f2` = IF (VALUES(`f2`) > `f2`, VALUES(`f2`), `f2`);


Sign Up Login You must be logged in to post a comment.