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.

The storage engine must increment the AUTO_INCREMENT value before inserting a row. It does not know if the insert will fail yet at that point. It cannot simply roll back the increment because there may be other inserts happening on other connections concurrently. This is normal behavior and not something you should (or can) change. The purpose of AUTO_INCREMENT is to provide unique identifiers, not an unbroken sequence of numbers.

Unfortunately the solution is at the application level if you don't want to affect auto-increment id's. Do a SELECT first and count the result rows. If 0 results, INSERT the data. If more than 0, UPDATE that row.