MySQL Insert ....On Duplicate Update - Adds One to the Autoincrement

MySQL INSERT ....ON DUPLICATE UPDATE - Adds one to the autoincrement

INSERT ... ON DUPLICATE KEY UPDATE is described as a "mixed-mode insert" for the purposes of InnoDB's AUTO_INCREMENT handling. Mixed-mode inserts are basically ones where the maximum number of required AUTO_INCREMENT values is known, but the amount that will actually be needed is not.

Mixed-mode inserts get handled specially by default, as described in the MySQL docs:

...for “mixed-mode 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.

If you're using InnoDB, your alternatives are:

1. Avoid INSERT ... ON DUPLICATE KEY UPDATE.
2. Set the innodb_autoinc_lock_mode parameter to 0, for "traditional" autoincrement lock mode, which guarantees that all INSERT statements will assign consecutive values for AUTO_INCREMENT columns. However, this is accomplished by locking during the statement, so there's a performance loss associated with this setting.
3. (Recommended) Ignore the gaps in the AUTO_INCREMENT column.

Note: AUTO_INCREMENT handling is totally different under MyISAM, which does not exhibit this behavior.

ON DUPLICATE KEY + AUTO INCREMENT issue mysql

This behavior is documented (paragraph in parentheses):

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that
would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL
performs an UPDATE of the old row. For example, if column a is
declared as UNIQUE and contains the value 1, the following two
statements have similar effect:

    INSERT INTO table (a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;

    UPDATE table SET c=c+1 WHERE a=1;

(The effects are not identical for
an InnoDB table where a is an auto-increment column. With an
auto-increment column, an INSERT statement increases the
auto-increment value but UPDATE does not.)

Here is a simple explanation. MySQL attempts to do the insert first. This is when the id gets auto incremented. Once incremented, it stays. Then the duplicate is detected and the update happens. But the value gets missed.

You should not depend on auto_increment having no gaps. If that is a requirement, the overhead on the updates and inserts is much larger. Essentially, you need to put a lock on the entire table, and renumber everything that needs to be renumbered, typically using a trigger. A better solution is to calculate incremental values on output.

Insert into on duplicate key - auto increment id skipped

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.

What if `auto_increment` gaps caused by MySQL `INSERT...ON DUPLICATE KEY UPDATE` cannot be ignored?

We work in a large table and we have tables with 100s millions of records in some table. We repeatedly use INSERT IGNORE or INSERT.. ON DUPLICATE KEY. Making the column as unsigned bigint will avoid the id issue.

But I would suggest you to think of long term solution as well. With some known facts.

• SELECT and INSERT/UPDATE is quite often faster than INSERT..ON DUPLICATE KEY, again based on you data size and other factors
• If you have two unique keys ( or one primary and one unique key), your query might not always predictable. It gives replication error if you use statement based replication.
• ID is not the only issue with large tables. If you have table with more than some 300M records, performances degrades drastically. You need to think of partitioning/clustering/sharding your database/tables pretty soon

Personally I would suggest not to use INSERT.. ON DUPLICATE KEY. Read extensively on its usage and performance impact if you are planning for a highly scalable service

MySQL ON DUPLICATE KEY UPDATE and auto_increment index

Problem is when you do the insert you do not include the auto_incremented index.

For Example:

INSERT INTO users (`username`, `password`, `salt`, `email`)
VALUES('$username', '$password', '$salt', '$email')

This is the reason why your column count doesn't match the table because it is not expecting your auto_index to be in the insert statement and therefore looking for another column

Too many auto increments with ON DUPLICATE KEY UPDATE

I don't think there is a way to bypass this behaviour of INSERT ... ON DUPLICTE KEY UPDATE.

You can however put two statements, one UPDATE and one INSERT, in one transaction:

START TRANSACTION ;

UPDATE pages
SET etc = 'randomness'
WHERE name = 'bob' ;

INSERT INTO pages (name, etc)
SELECT 
      'bob' AS name
    , 'randomness' AS etc 
FROM dual 
WHERE NOT EXISTS
      ( SELECT *
        FROM pages p
        WHERE p.name = 'bob'
      ) ;

COMMIT ;

insert into ... on duplicate key' with auto increment

ON DUPLICATE KEY requires a UNIQUE KEY if you are not matching to the PRIMARY KEY. You can add a UNIQUE KEY by using an ALTER TABLE query

ALTER TABLE position ADD UNIQUE KEY (a,b)

---

Related Topics