MySQL Bugs: #50102: AUTO_INCREMENT increases when using UPDATE ON DUPLICATE

Bug #50102	AUTO_INCREMENT increases when using UPDATE ON DUPLICATE
Submitted:	6 Jan 2010 12:52	Modified:	6 Jan 2010 15:15
Reporter:	Alex Judd	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
One weird behaviour appears to be with AUTO_INCREMENT.

If no new entries are made, just UPDATE ON DUPLICATE to existing records, I would expect the AUTO_INCREMENT to not increase as there are no new records INSERTed.

However AUTO_INCREMENT does exactly the opposite and actually _increases_ by the same number of UPDATEd records I have made.

Bug or deliberate behaviour (and if so why?)? 

How to repeat:
Create a table with AUTO_INCREMENT enabled.

View current value of AUTO_INCREMENT

Run some ON DUPLICATE KEY UPDATE statements

View updated value of AUTO_INCREMENT

What exact server version, x.y.z, do you use? Please, provide also complete test case that demonstrates the problem, not just a general description.

Test Case

CREATE TABLE IF NOT EXISTS `auto_inc_test` (
  `auto_inc_test_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_id` int(11) NOT NULL DEFAULT '0',
  `auto_inc_test_attributes` varchar(255) CHARACTER SET utf8 NOT NULL,
  `auto_inc_test_quantity` int(11) NOT NULL DEFAULT '0',
  `products_attributes_model_code` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`auto_inc_test_id`),
  UNIQUE KEY `idx_auto_inc_test_attributes` (`products_id`,`auto_inc_test_attributes`)
)

-> Set the Storage Engine to be InnoDB
-> At this point AUTO_INCREMENT = 1

INSERT INTO auto_inc_test (`products_id`,`auto_inc_test_attributes`,`auto_inc_test_quantity`,`products_attributes_model_code`) VALUES (7, '4-2,5-3,6-6', 0, '') ON DUPLICATE KEY UPDATE `products_id` = 7;

-> At this point AUTO_INCREMENT = 2

INSERT INTO auto_inc_test (`products_id`,`auto_inc_test_attributes`,`auto_inc_test_quantity`,`products_attributes_model_code`) VALUES (7, '4-2,5-3,6-6', 0, '') ON DUPLICATE KEY UPDATE `products_id` = 7;

-> At this point AUTO_INCREMENT = 3 even though there is only one row in the database

INSERT INTO auto_inc_test (`products_id`,`auto_inc_test_attributes`,`auto_inc_test_quantity`,`products_attributes_model_code`) VALUES (7, '4-2,5-3,6-6', 0, '') ON DUPLICATE KEY UPDATE `products_id` = 7;

-> At this point AUTO_INCREMENT = 4 even though there is only one row in the database

etc.

MySQL version mysql.exe  Ver 14.14 Distrib 5.1.37, for Win32 (ia32)

Also - only reproducable if Storage Engine is set to InnoDB, setting it to MyISAM shows different (the correct?) behaviour where it doesn't increment...

If this is 5.1.x and InnoDB-related only, then it is NOT a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html:

"A similar situation exists if you use INSERT ... ON DUPLICATE KEY UPDATE. This statement is also classified as a “mixed-mode insert” since an auto-increment value is not necessarily generated for each row. Because InnoDB allocates the auto-increment value before the insert is actually attempted, it cannot know whether an inserted value will be a duplicate of an existing value and thus cannot know whether the auto-increment value it generates will be used for a new row. Therefore, if you are using statement-based replication, you must either avoid INSERT ... ON DUPLICATE KEY UPDATE or use innodb_autoinc_lock_mode = 0  (“traditional” lock mode)."

innodb_autoinc_lock_mode is 1 by default in 5.1.x...