Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Trivial
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.12
-
Fix Version/s: N/A
-
Component/s: None
-
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Closing for now as not a bug. Please feel free to comment if you disagree.
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:
So, it seems to be somewhat consistent.
If you disagree or have doubts, I will get a second opinion.