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