Bug #61962 'ON DUPLICATE KEY UPDATE' bug incrementing ID (auto increment field) on Updates
Submitted: 23 Jul 2011 21:43 Modified: 6 Aug 2011 12:44
Reporter: Luc L. Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Version:5.5.14 OS:Any (Tested on 5.5.8 and 5.5.14)
Assigned to: CPU Architecture:Any
Tags: auto increment, on duplicate, ON DUPLICATE KEY UPDATE

[23 Jul 2011 21:43] Luc L.
When using "INSERT INTO ... ON DUPLICATE KEY UPDATE" on MySQL versions 5.5.8 and 5.5.14 (latest) the auto_increment field in the table is incremented by 1 when an UPDATE is performed (and insert). 

This means that doing multiple "INSERT INTO .. ON DUPLICATE" will result in the table having its ID (auto-increment) column incremented without any actual inserts, forever, and eventually cause the table to run out of IDs (if small data type such as int or small int is used). 

I originally discovered this on 5.5.8 and then upgraded to 5.5.14 to see if it's still there, and it is. Running on InnoDB table with a fresh (new) MySQL install from RPM.

This is a really serious bug. Tables that have a lot of ON DUPLICATE updates will experience very high auto_increment field sizes and eventually return errors.

How to repeat:
Fresh install MySQL Server 5.5.14 from RPM on Red Hat Enterprise:

CREATE TABLE `server_bots_status` (
  `server_bot_id` int(11) NOT NULL,
  `server_version` varchar(15) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_index` (`server_bot_id`)

mysql> INSERT INTO server_bots_status SET server_bot_id=1,server_version="2.0";

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO server_bots_status SET server_bot_id=1,server_version="2.1" ON DUPLICATE KEY UPDATE server_version=VALUES(server_version);

Query OK, 2 rows affected (0.00 sec)

mysql> INSERT INTO server_bots_status SET server_bot_id=1,server_version="2.2" ON DUPLICATE KEY UPDATE server_version=VALUES(server_version);

Query OK, 2 rows affected (0.00 sec)

INSERT INTO server_bots_status SET server_bot_id=2,server_version="3.0";

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM server_bots_status;
| id | server_bot_id | server_version | mod_date            |
|  1 |             1 | 2.2            | 2011-07-23 16:40:39 |
|  4 |             2 | 3.0            | 2011-07-23 16:40:51 |

As you can see, the auto-increment (id) field was set to 4 on the insertion of the new item, even though no items were inserted between 1 and 4 (only updated).
[24 Jul 2011 7:42] Peter Laursen
What is your setting for the server variable 'innodb_autoinc_lock_mode' ?

In 5.5.x the default is "1" - before it was "0" (implicitly or explicitly). Refer: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

"... To describe the way auto-increment works in InnoDB, the following discussion defines some terms, and explains how InnoDB behaves using different settings of the new innodb_autoinc_lock_mode configuration parameter. Additional considerations are described following the explanation of auto-increment locking behavior. ..."

(not a MySQL person)
[24 Jul 2011 7:46] Peter Laursen
One correction: it seems that even MySQL 5.1 (from 5.1.22 probably) also has "1" as default for this variable.
[25 Jul 2011 15:51] Luc L.

mysql> show global variables like "innodb_autoinc_lock_mode";
| Variable_name            | Value |
| innodb_autoinc_lock_mode | 1     |
1 row in set (0.00 sec)

So you are saying that even though there were no inserts, only updates, the auto-increment value will still be incremented if innodb_autoinc_lock_mode != 0?

I just want to make sure I understand this correctly and that it is in fact not a bug. 

Also, is it safe to change this variable on an active database?

Thank You,
[6 Aug 2011 12:44] 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

As Peter already wrote this is not a bug. Closing as such.