Insert into on duplicate key - auto increment id skipped

Question

I currently have an SQL execution script which updates the row on duplicate key which looks like this.

    $stmt = $dbCon->prepare("INSERT INTO videos_rating (videos_rating_video_fk, "
            . " videos_rating_user_fk, "
            . " videos_rating_rating) "
            . " VALUES (:video_id, "
            . " :user_id, "
            . " :video_rating) "
            . " ON DUPLICATE KEY UPDATE videos_rating_rating = :video_rating");

The script works fine but is there a way to prevent the auto increment column of getting out of sync?

Lets assume we start with an empty table, i then rate a video which then creates a row which will get the id of 1, then the user execute the SQL again by rating the same video a lower or higher rating and the row will be updated because its now a duplicate key, sure no problem.

The problem is this.

Next time another user rates a new new video the row will now begin at id 3 and not 2?

The table will then look like this

id | videos_rating_user_fk | videos_rating_rating
1  | 1                     | 4
3  | 2                     | 5

I were not able to find a similar question even tho i find it higly unlikely that no one else has been bothered with this, if so please refer me over to that post.

I know ids are not supposed to 'look good' but it is very annoying that ids jump from 30 - 51 - 82 - 85 - 89 etc and would there not be a problem at some point when the maximum UNSIGNED big int number is reached? im not saying i will ever go that high but still.

I'm confused. Your concern is that your manually modified `AUTO_INCREMENT` logic is not linearly sequential? The max of an `UNSIGNED BIG INT` is 2^64-1, and you shouldn't rely on sequential IDs anyway... — kchason, May 16 '18 at 14:34
Possible duplicate of [ON DUPLICATE KEY + AUTO INCREMENT issue mysql](https://stackoverflow.com/questions/23516958/on-duplicate-key-auto-increment-issue-mysql) — Nick, May 16 '18 at 22:09

score 5 · Accepted Answer · answered May 16 '18 at 17:50

I assume that you are using the default InnoDB engine. In that case the "problem" is that the engine will "reserve" the id before it knows if it's a duplicate or not. Once the id is "reserved" it cannot be released, because another thread (another user) might perform an insert into the same table at the "same" time. There are also other ways to get gaps in the AUTO_INCREMENT column without deleting any rows. One is when you roll back a transaction.

You can try to "reset" the next AUTO_INCREMENT value after every insert with

alter table videos_rating auto_increment = 1;

But I can't say what problems you might run in executing this statement in a running live environment. And I'm not going to find that out.

Note that this is usually not an issue, because tables on which you run IODKU statemts (usually) don't need an AUTO_INCREMENT column. As Cid wrote in his answer, you can just drop the id column and define your unique key as primary key.

Is right, you can "beautify" the primary key in dev environment, but never do that in production. This can provide unexpected behaviour. — Cid, May 17 '18 at 06:39

Cid · Answer 2 · 2018-05-17T06:37:30.537

Let's assume your table is built this way :

videos_rating_video_fk | videos_rating_user_fk | videos_rating_rating
-----------------------+-----------------------+----------------------

The first key videos_rating_video_fk should be a foreign key and not a primary key with autoincrement.

If users 1 and 2 vote for the video that has the id 1, your table should looks like this :

videos_rating_video_fk | videos_rating_user_fk | videos_rating_rating
-----------------------+-----------------------+----------------------
1                      | 1                     | 4
1                      | 2                     | 5

For that kind of table, the primary key should be the combination of both foreign keys and will be unique. A user can vote only once for a video (unique vote = unique key). A video can be voted by multiples users and users can vote for multiples videos.

I suggest you to take a look at the Merise Method for building tables with integrity constraints and creation of primary keys.

"concatenation" - I'd rather say "composition" or "combination". — Paul Spiegel, May 16 '18 at 17:52
@PaulSpiegel Thank you, we use concatenation in french, I thought it was the same in english. Editing now. — Cid, May 17 '18 at 06:36

score 1 · Answer 3 · answered May 25 '18 at 19:45

Live with the "burning" of ids. AUTO_INCREMENT guarantees not to allow duplicate values, not does not provide any other guarantees.

There are about 4 other ways where ids may be 'burned': REPLACE, Multi-Master / Galera, IGNORE, DELETE, and possibly more.

IODKU quickly grabbed an id before discovering that the statement would turn into an UPDATE and not need the id. To do otherwise would probably be a significant performance hit.

score 0 · Answer 4 · answered Aug 09 '19 at 10:05

To confirm, Paul Spiegel's answer helped my to resolve the issue. I had some an 'Upsert' SQL query that used ON DUPLICATE KEY UPDATE to determine whether to create a new row or update an existing row. Where a row was updated frequently, the jumps in assigned Id's where large.

the "problem" is that the engine will "reserve" the id before it knows if it's a duplicate or not.

I resolved the problem by breaking the SQL code into separate INSERT and UPDATE statements. I'm no longer seeing the issue.

Insert into on duplicate key - auto increment id skipped

4 Answers4