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

[31 Jan 2009 10:51] Thomas Böhme
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.
[31 Jan 2009 10:55] Thomas Böhme
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)
[2 Feb 2009 8:04] Sveta Smirnova
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)
[2 Feb 2009 19:34] Thomas Böhme
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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.