| Bug #37560 | Autoincrement should not increment on duplicate key condition | ||
|---|---|---|---|
| Submitted: | 20 Jun 2008 22:56 | Modified: | 31 Jul 2008 18:01 |
| Reporter: | Nathan Christiansen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0.45-community-log | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | autoincrement, innodb | ||
[20 Jun 2008 23:09]
MySQL Verification Team
Thank you for the bug report. I can't repeat with 5.0.51b released version, please try with latest released version.
mysql> CREATE TABLE `test_autoincrement` (
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `text` varchar(255) NOT NULL default '',
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `test_text` (`text`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> insert into test_autoincrement (text) values('1') on duplicate key
-> update text='1';
Query OK, 1 row affected (0.03 sec)
mysql> insert into test_autoincrement (text) values('2') on duplicate key
-> update text='2';
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_autoincrement (text) values('3') on duplicate key
-> update text='3';
Query OK, 1 row affected (0.05 sec)
mysql> insert into test_autoincrement (text) values('1') on duplicate key
-> update text='1';
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test_autoincrement (text) values('1') on duplicate key
-> update text='1';
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test_autoincrement (text) values('1') on duplicate key
-> update text='1';
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test_autoincrement (text) values('1') on duplicate key
-> update text='1';
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test_autoincrement (text) values('1') on duplicate key
-> update text='1';
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test_autoincrement (text) values('1') on duplicate key
-> update text='1';
Query OK, 0 rows affected (0.06 sec)
mysql> insert into test_autoincrement (text) values('1') on duplicate key
-> update text='1';
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test_autoincrement (text) values('1') on duplicate key
-> update text='1';
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test_autoincrement (text) values('4') on duplicate key
-> update text='4';
Query OK, 1 row affected (0.05 sec)
mysql> select * from test_autoincrement;
+----+------+
| id | text |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.00 sec)
mysql>
[20 Jun 2008 23:17]
Nathan Christiansen
Unfortunately I can't try it out on the new version. I have no access to upgrade the MySQL server, nor do I have permission to install it on a test box. I will have to request an upgrade to the latest version of MySQL from our technical operations team.
[31 Jul 2008 18:01]
Nathan Christiansen
Confirmed bug does not happen on version: 5.0.51a-community
[28 Apr 2009 12:36]
Michael Davies
Hi, I am seeing this problem as reported by Nathan on 5.1.31-community, if I run his SQL I get his exact results with the ID field jumping to 12 instead of staying on 4. Has it managed to get back into the system somehow? Thanks
[8 Jun 2009 8:47]
Sveta Smirnova
Michael, please read at http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html how AUTO_INCREMENT Handling in InnoDB changed in version 5.1
[6 Feb 2013 16:00]
Pezhvak IMV
I've tested with "5.5.11 - MySQL Community Server" on MacOSX, autoincrement value is increases...
[25 Jul 2013 22:02]
Alexey Vesnin
mysql> status -------------- mysql Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using EditLine wrapper Connection id: 1343213 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.12 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 15 days 11 hours 16 min 16 sec Threads: 3 Questions: 12145678 Slow queries: 2 Opens: 3405 Flush tables: 1 Open tables: 184 Queries per second avg: 9.087 # uname -a Linux Ubuntu-1304-raring-64-minimal 3.8.13.4 #2 SMP Mon Jul 8 23:59:05 CEST 2013 x86_64 x86_64 x86_64 GNU/Linux Still reproduced, compiled from source
[25 Jul 2013 22:15]
Alexey Vesnin
innodb_autoinc_lock_mode=0 in my.cnf fixes an issue
Description: I am using the sql clause "on duplicate key update" to check for errors in inserting text into a table. (If the number of rows changed is 0, then there was an error and I log it.) I have a table with two columns one is the auto_increment primary key and the other is text with a unique key on it. When the "insert ... on duplicate key update ..." statement is executed and there is a duplicate key, no row is inserted however the autoincrement value is incremented. If no new row is created, then the autoincrement value for the table should not be incremented. How to repeat: CREATE TABLE `test_autoincrement` ( `id` int(10) unsigned NOT NULL auto_increment, `text` varchar(255) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `test_text` (`text`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; insert into test_autoincrement (text) values('1') on duplicate key update text='1'; insert into test_autoincrement (text) values('2') on duplicate key update text='2'; insert into test_autoincrement (text) values('3') on duplicate key update text='3'; insert into test_autoincrement (text) values('1') on duplicate key update text='1'; insert into test_autoincrement (text) values('1') on duplicate key update text='1'; insert into test_autoincrement (text) values('1') on duplicate key update text='1'; insert into test_autoincrement (text) values('1') on duplicate key update text='1'; insert into test_autoincrement (text) values('1') on duplicate key update text='1'; insert into test_autoincrement (text) values('1') on duplicate key update text='1'; insert into test_autoincrement (text) values('1') on duplicate key update text='1'; insert into test_autoincrement (text) values('1') on duplicate key update text='1'; insert into test_autoincrement (text) values('4') on duplicate key update text='4'; mysql> select * from test_autoincrement; +----+------+ | id | text | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 12 | 4 | +----+------+ 4 rows in set (0.02 sec) Suggested fix: Do not increment the autoincrement column on a "on duplicate key update" condition regardless if the duplicate key was on the primary key or not.