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:
None 
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 22:56] Nathan Christiansen
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.
[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