MySQL Bugs: #42497: V5.1 AUTO_INCREMENT gaps with InnoDB when using INSERT IGNORE

Bug #42497	V5.1 AUTO_INCREMENT gaps with InnoDB when using INSERT IGNORE
Submitted:	31 Jan 2009 10:51	Modified:	2 Feb 2009 19:34
Reporter:	Thomas Böhme	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.30-log	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	5.1, auto_increment, innodb, INSERT IGNORE

Description:
Using MySQL 5.1.30-log and InnoDB the following statement from documentation ist wrong:

(http://dev.mysql.com/doc/refman/5.1/en/information-functions.html)

"If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and ...."

For MyISAM it works and also with InnoDB-tables on MySQL 5.0.

How to repeat:
mysql> CREATE TABLE `value2` (
    ->   `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    ->   `value` varchar(255) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `value` (`value`)
    -> ) ENGINE=myisam;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table value2\G
*************************** 1. row ***************************
       Table: value2
Create Table: CREATE TABLE `value2` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `value` (`value`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO value2 (value) values('11');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT IGNORE INTO value2 (value) values('11');
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT IGNORE INTO value2 (value) values('11');
Query OK, 0 rows affected (0.00 sec)

mysql> show create table value2\G
*************************** 1. row ***************************
       Table: value2
Create Table: CREATE TABLE `value2` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `value` (`value`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO value2 (value) values('11');
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT IGNORE INTO value2 (value) values('11');
Query OK, 0 rows affected (0.00 sec)

mysql> show create table value2\G
*************************** 1. row ***************************
       Table: value2
Create Table: CREATE TABLE `value2` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `value` (`value`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO value2 (value) values('12');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO value2 (value) values('13');
Query OK, 1 row affected (0.00 sec)

mysql> show create table value2\G
*************************** 1. row ***************************
       Table: value2
Create Table: CREATE TABLE `value2` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `value` (`value`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from value2;
+----+-------+
| id | value |
+----+-------+
|  1 | 11    |
|  2 | 12    |
|  3 | 13    |
+----+-------+
3 rows in set (0.00 sec)

mysql> alter table value2 engine=innodb;
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table value2\G
*************************** 1. row ***************************
       Table: value2
Create Table: CREATE TABLE `value2` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO value2 (value) values('11');
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT IGNORE INTO value2 (value) values('11');
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT IGNORE INTO value2 (value) values('11');
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT IGNORE INTO value2 (value) values('11');
Query OK, 0 rows affected (0.03 sec)

mysql> show create table value2\G
*************************** 1. row ***************************
       Table: value2
Create Table: CREATE TABLE `value2` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from value2;
+----+-------+
| id | value |
+----+-------+
|  1 | 11    |
|  2 | 12    |
|  3 | 13    |
+----+-------+
3 rows in set (0.00 sec)

Suggested fix:
If no row is inserted when using INSERT IGNORE the AUTO_INCREMENT of the table shouldn't increment.

I forgot this:

mysql> INSERT IGNORE INTO value2 (value) values('14');
Query OK, 1 row affected (0.03 sec)

mysql> show create table value2\G
*************************** 1. row ***************************
       Table: value2
Create Table: CREATE TABLE `value2` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from value2;
+----+-------+
| id | value |
+----+-------+
|  1 | 11    |
|  2 | 12    |
|  3 | 13    |
|  8 | 14    |
+----+-------+
4 rows in set (0.00 sec)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Since version 5.1 InnoDB has configurable  Auto-Increment Locking. See also http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc...

Workaround: use option innodb_autoinc_lock_mode=0 (traditional)

Hello Sveta,

thank you very much for your help. I'll change my server config. I know its hard to keep all parts of a doc of this size allways up-to-date on every page, but maybe you want to add a link to chapter 13.6.4.3. in sections for "INSERT IGNORE" and "AUTO_INCREMENT".

Thanks again!! :)

KR, Thomas