MySQL Bugs: #61962: 'ON DUPLICATE KEY UPDATE' bug incrementing ID (auto increment field) on Updates

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:	None
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

Description:
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` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `server_bot_id` int(11) NOT NULL,
  `server_version` varchar(15) NOT NULL DEFAULT '0',
  `mod_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_index` (`server_bot_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

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).

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. ..."

Peter
(not a MySQL person)

One correction: it seems that even MySQL 5.1 (from 5.1.22 probably) also has "1" as default for this variable.

Hello, 

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,
Luc

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.