Why does mysql skip some auto increment ids?

Question

I have seen many explanations why mysql skips some auto increment values (and that it is not a bug as mysql never states that they should be consecutive). I am not sure why any of them would be applicable to this simple test case. I don't know if the results would be the same on all recent versions of mysql or not. Also adding:

ALTER TABLE test_table2 AUTO_INCREMENT = 1;

between the 2 INSERT INTO test_table2 lines makes the order as expected.

Does anyone know why this simple case would skip ids 6 and 7?

CREATE TABLE test_table1 (
  `id` INT NOT NULL AUTO_INCREMENT,
  `test` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO test_table1(`test`) VALUES('value 1');
INSERT INTO test_table1(`test`) VALUES('value 2');
INSERT INTO test_table1(`test`) VALUES('value 3');
INSERT INTO test_table1(`test`) VALUES('value 4');
INSERT INTO test_table1(`test`) VALUES('value 5');
CREATE TABLE test_table2 (
  `id` INT NOT NULL AUTO_INCREMENT,
  `test` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO test_table2(`test`) SELECT `test` FROM test_table1;
INSERT INTO test_table2(`test`) SELECT `test` FROM test_table1;
SELECT * FROM test_table2;

Results on my version of mysql:

'1', 'value 1'
'2', 'value 2'
'3', 'value 3'
'4', 'value 4'
'5', 'value 5'
'8', 'value 1'
'9', 'value 2'
'10', 'value 3'
'11', 'value 4'
'12', 'value 5'

Thanks in advance.

What is the next autoincrement value after the first `INSERT ... SELECT`? — zerkms, Jul 16 '13 at 05:06
It doesn't reproduce on my config: http://codeone.pl/ss/ss-2013-07-16_07-08-10-001.png MySQL 5.1.56-log — Arkadiusz 'flies' Rzadkowolski, Jul 16 '13 at 05:09
@fliespl: you have English broken in your database management tool (PMA?) though ;-P — zerkms, Jul 16 '13 at 05:10
@zerkms: naah, I am just learning multiple languages at once hence "selective" translations ;) — Arkadiusz 'flies' Rzadkowolski, Jul 16 '13 at 05:12
For what reason do you need to insert the values from test_table1 two times in test_table2? You should insert them only once. — Gimmy, Jul 16 '13 at 05:35

cerd · Answer 1 · 2013-07-20T07:32:53.217

11

It's an example of auto increment locking in InnoDB: As you are executing 2 statements concurrently in the same session: the auto inc lock is obtained by the first query, and the autoincrement value generation is not interleaved between the statements - that is the whole point of transaction.

This will always happen by design: If it didn't, the way the transactions in InnoDB work, well, wouldn't work. Scalability under OLTP type loads would be horrible as every insert would have to wait for every other insert to finish, be committed, or worse - rolled back.

I.e.: If your first insert runs 5x longer than your second, and fails and is rolled back, the second one still completes and has been committed. Otherwise, you would have to wait for ea. query to be complete after the other.

If you require sequential and absolutely unique ID #s, pull them from another source. AutoInc columns simply guarantee a unique value - not necessarily a monosequence - that is a point of serialization and a bottleneck.

One way around this if otherwise required:

set innodb_autoinc_lock_mode = 0 in your my.cnf/mysql.ini

Description of auto inc locking in InnoDB and Options

edited Jul 20 '13 at 07:32

answered Jul 16 '13 at 05:23

cerd

2,171
1
18
28

I tested the other day on windows, today I am testing on Ubuntu with mysql 5.5.31-0ubuntu0.12.04.2, but it seems that all the results will be the same. You are right that setting `innodb_autoinc_lock_mode = 0` does avoid the problem (both modes 1 and 2 share the problem). However I don't believe that I am executing 2 statements at once as I only have one connection, and even changing to temporary tables does not avoid the problem. Waiting between statements makes no difference. – Yoseph Jul 17 '13 at 11:04
The doc says 'For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible.', so what I don't understand is how it overestimates the first select by 2 when doing a SELECT all rows. My workaround also works but doesn't explain why I even need a workaround. – Yoseph Jul 19 '13 at 00:41
@Yoseph - Updaed answer. – cerd Jul 20 '13 at 06:13
2

You say 'avoid the problem', but there is no problem here. If a contiguous sequence IS required DO NOT use auto_increment. Despite its name that's not what it's for! – Strawberry Jul 20 '13 at 06:36
Assuming this is one statement: `INSERT INTO test_table2(test) SELECT test FROM test_table1;`, then it is only doing one statement concurrently which can be demonstrated by placing `SELECT * FROM test_table2;` between the two statements showing that all 5 values have been inserted before the 2nd insert is even started. I could understand missing ids if there were some complicated where clause, but it seems trivial that a select all will return 5 rows. I am more interested in understanding why this produces gaps. – Yoseph Jul 22 '13 at 11:48
1

MySQL allows replication from a master on an old version to a slave a new version. With `innodb_autoinc_lock_mode = 1` on both versions, the ids may end up different as some versions of mysql don't have this problem. That could break replication. Our actual db has about 60 million records in one table alone, so I would prefer to understand what is going on, instead of using a slower workaround. – Yoseph Jul 22 '13 at 11:54
@Yoseph - So I assume you are using statement based replication? – cerd Jul 22 '13 at 15:10
Currently we are not using replication as something else is apparently stopping the replication from working properly, but I do believe that whoever set it up was using statement based replication. I feel like a replication discussion will take us on a tangent. – Yoseph Jul 24 '13 at 13:06
@Yoseph - I understand. But by all the literature out there this is standard functionality for innodb and this is the best answer I have seen: http://stackoverflow.com/questions/7087869/mysql-insert-on-duplicate-update-adds-one-to-the-autoincrement – cerd Jul 25 '13 at 21:26
@cerd - That other question seems to be talking about using on duplicate, which I don't have an issue with as in that case as with rolling back it would not be possible to know how many rows will be inserted, and so a gap would be understandable. In my example it seems clear that at most 5 rows could be inserted as there are just 5 rows. 'innodb_autoinc_lock_mode = 1' is meant to be safe for statement based replication which implies that the auto inc should be deterministic. Maybe others reporting different results had 'innodb_autoinc_lock_mode' set to something else other than the default. – Yoseph Jul 28 '13 at 12:35
@cerd - thanks for trying, maybe I have to accept that I wont be convinced of an answer to this. – Yoseph Jul 28 '13 at 12:41
I have the same behavior on MySql 5.5.49. Gaps occurs even if I use `LIMIT` statement. – Turako May 15 '16 at 12:20

score 2 · Answer 2 · answered May 19 '20 at 16:47

2

One reason is that you have a UNIQUE INDEX that is triggered.

An id is skipped when you try to insert a field that is already inside your unique index.

answered May 19 '20 at 16:47

kintsukuroi

1,332
16
15

I think you are referring to ON DUPLICATE KEY UPDATE, otherwise that would cause an error. In the question it is not inserting a duplicate into a field used by a unique index. – Yoseph May 21 '20 at 10:41
1

@Yoseph Not all people have the exact same situation as the question right? I'm talking about a regular INSERT into InnoDB that will cause a skipped id in the circumstance I described. – kintsukuroi May 21 '20 at 14:36

Why does mysql skip some auto increment ids?

2 Answers2

Linked