InnoDB provides an optimization that
significantly improves scalability and performance of SQL
statements that insert rows into tables with
AUTO_INCREMENT columns. 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.
This section provides background information on the original
(“traditional”) implementation of auto-increment
locking in InnoDB, explains the configurable
locking mechanism, documents the parameter for configuring the
mechanism, and describes its behavior and interaction with
replication.
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 FOR EACH ROW BEGIN 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 NEW.id<auto_incr2) THEN SET NEW.id = auto_incr2; END IF; END;// delimiter ;
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`
DROP TRIGGER `1test_c`/// CREATE TRIGGER 1test_c BEFORE INSERT ON `1test` FOR EACH ROW BEGIN
IF NEW.`f2` IS NULL OR NEW.`f2` = 0 THEN UPDATE `1test_counter` SET `f2` = `f2` + 1, NEW.`f2` = `f2` WHERE `f1` = NEW.`f1` LIMIT 1;
IF ROW_COUNT() = 0 THEN INSERT INTO `1test_counter`(`f1`, `f2`) VALUES (NEW.`f1`, 1); SET NEW.`f2` = 1; END IF; ELSE INSERT INTO `1test_counter`(`f1`, `f2`) VALUES (NEW.`f1`, NEW.`f2`) ON DUPLICATE KEY UPDATE `f2` = IF (VALUES(`f2`) > `f2`, VALUES(`f2`), `f2`); END IF; END; ///
© 2013, Oracle Corporation and/or its affiliates
User Comments
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:
http://bugs.mysql.com/bug.php?id=20786
It is nicely summarised with a post operative fix:
http://melikedev.com/2011/06/01/mysql-remove-auto_increment-from-schema-dumps-mysqldump/
mysqldump -u root -p -h <db-host> --opt <db-name> -d --single-transaction | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > <filename>.sql
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
FOR EACH ROW
BEGIN
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 NEW.id<auto_incr2) THEN
SET NEW.id = auto_incr2;
END IF;
END;//
delimiter ;
Further reading: http://www.slicewise.net/index.php?id=82
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`)
) ENGINE=InnoDB;
####################################
CREATE TABLE `1test_counter` (
`f1` int(10) unsigned NOT NULL,
`f2` int(10) unsigned default NULL,
PRIMARY KEY (`f1`)
) ENGINE=InnoDB;
####################################
DELIMITER ///
DROP TRIGGER `1test_c`///
CREATE TRIGGER 1test_c
BEFORE INSERT ON `1test`
FOR EACH ROW BEGIN
IF NEW.`f2` IS NULL OR NEW.`f2` = 0 THEN
UPDATE `1test_counter`
SET `f2` = `f2` + 1, NEW.`f2` = `f2`
WHERE `f1` = NEW.`f1`
LIMIT 1;
IF ROW_COUNT() = 0 THEN
INSERT INTO `1test_counter`(`f1`, `f2`)
VALUES (NEW.`f1`, 1);
SET NEW.`f2` = 1;
END IF;
ELSE
INSERT INTO `1test_counter`(`f1`, `f2`)
VALUES (NEW.`f1`, NEW.`f2`)
ON DUPLICATE KEY UPDATE
`f2` = IF (VALUES(`f2`) > `f2`, VALUES(`f2`), `f2`);
END IF;
END;
///
DELIMITER ;
Add your own comment.