Lost AUTO INCREMENT ids

Several ‘insert’ functions can “burn” ids. Here is an example, using InnoDB (other Engines may work differently):

CREATE TABLE Burn (
    id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    name VARCHAR(99) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(name)
        ) ENGINE=InnoDB;

INSERT IGNORE INTO Burn (name) VALUES ('first'), ('second');
SELECT LAST_INSERT_ID();          -- 1
SELECT * FROM Burn ORDER BY id;
  +----+--------+
  |  1 | first  |
  |  2 | second |
  +----+--------+

INSERT IGNORE INTO Burn (name) VALUES ('second');  -- dup 'IGNOREd', but id=3 is burned
SELECT LAST_INSERT_ID();          -- Still "1" -- can't trust in this situation
SELECT * FROM Burn ORDER BY id;
  +----+--------+
  |  1 | first  |
  |  2 | second |
  +----+--------+

INSERT IGNORE INTO Burn (name) VALUES ('third');
SELECT LAST_INSERT_ID();           -- now "4"
SELECT * FROM Burn ORDER BY id;    -- note that id=3 was skipped over
  +----+--------+
  |  1 | first  |
  |  2 | second |
  |  4 | third  |    -- notice that id=3 has been 'burned'
  +----+--------+

Think of it (roughly) this way: First the insert looks to see how many rows might be inserted. Then grab that many values from the auto_increment for that table. Finally, insert the rows, using ids as needed, and burning any left overs.

The only time the leftover are recoverable is if the system is shutdown and restarted. On restart, effectively MAX(id) is performed. This may reuse ids that were burned or that were freed up by DELETEs of the highest id(s).

Essentially any flavor of INSERT (including REPLACE, which is DELETE + INSERT) can burn ids. In InnoDB, the global (not session!) variable [innodb_autoinc_lock_mode] 1 can be used to control some of what is going on.

When “normalizing” long strings into an AUTO INCREMENT id, burning can easily happen. This could lead to overflowing the size of the INT you chose.