[MDEV-6380] Inconsistent AUTO_INCREMENT With Insert Ignore Created: 2014-06-24  Updated: 2014-07-28  Due: 2014-07-22  Resolved: 2014-07-28

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.12
Fix Version/s: N/A

Type: Bug Priority: Trivial
Reporter: BELUGABEHR Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description    

DROP SCHEMA IF EXISTS `test`;
 
CREATE SCHEMA IF NOT EXISTS `test`;
 
 
 
USE `test`;
 
 
 
DROP TABLE IF EXISTS `test`.`test_name`;
 
 
 
CREATE TABLE IF NOT EXISTS `test`.`test_name` (
 
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 
	`name` VARCHAR(64) NOT NULL,
 
	PRIMARY KEY (`id`))
 
ENGINE = InnoDB;
 
 
 
CREATE UNIQUE INDEX `name_UNIQUE` ON `test`.`test_name` (`name`(8) ASC);
 
 
 
INSERT IGNORE INTO test_name (name) VALUES ('John');
 
INSERT IGNORE INTO test_name (name) VALUES ('John');
 
INSERT IGNORE INTO test_name (name) VALUES ('John');
 
INSERT IGNORE INTO test_name (name) VALUES ('Abe');
 
INSERT IGNORE INTO test_name (name) VALUES ('Abe');
 
INSERT IGNORE INTO test_name (name) VALUES ('Abe');
 
INSERT IGNORE INTO test_name (name) VALUES ('Zach');
 
 
 
SELECT * FROM test_name;

Result:

ID Name
1 John
4 Abe
7 Zach

However, when I set innodb_autoinc_lock_mode = 0

ID Name
1 John
2 Abe
3 Zach

I have a sense, based on the documentation, of the difference between 'traditional' lock mode and "consecutive" lock mode. I understand that if I were rolling-back the transaction, that I would loose IDs, but I would not expect to loose them in this case.

 Comments   
Comment by Elena Stepanova [ 2014-07-01 ]

I suppose INSERT IGNORE is another special case of "mixed-mode inserts", as described here: http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html

The article does not mention INSERT IGNORE specifically, but it includes INSERT .. ON DUPLICATE KEY UPDATE into the "mixed-mode" category.

In regard to auto-increment values, INSERT IGNORE seems to be similar to INSERT .. ON DUPLICATE KEY UPDATE, in the sense that there is no information in advance whether a row will be inserted (and thus the generated auto-increment value will be used), or not.

Further, it describes the operation for mixed-mode inserts like this:

For such inserts, InnoDB will allocate more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.

So, it seems to be somewhat consistent.

If you disagree or have doubts, I will get a second opinion.

Comment by Elena Stepanova [ 2014-07-28 ]

Closing for now as not a bug. Please feel free to comment if you disagree.

Generated at Thu Feb 08 07:11:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.