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: | |
| 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
[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.